Oracle - 동적/정적SQL + 프로시저(2)

SELECT USER
FROM DUAL;
-- SCOTT

--○ INSERT 프로시저 실습을 위한 테이블 생성
-- 테이블명 : TBL_STUDENTS
CREATE TABLE TBL_STUDENTS
(   ID      VARCHAR2(10)
,   NAME    VARCHAR2(40)
,   TEL     VARCHAR2(20)
,   ADDR    VARCHAR2(100)
,   CONSTRAINT STUDENTS_ID_PK PRIMARY KEY(ID) 
); 

--==>> Table TBL_STUDENTS이(가) 생성되었습니다.

-- 테이블명 : TBL_IDPW
CREATE TABLE TBL_IDPW
(   ID      VARCHAR2(10)
,   PW      VARCHAR2(20)
,   CONSTRAINT IDPW_ID_PW PRIMARY KEY(ID)
);

--==>> Table TBL_IDPW이(가) 생성되었습니다.

-- 한 명의 학생 정보 등록 → 두 테이블 모두에 데이터 입력 
INSERT INTO TBL_STUDENTS(ID, NAME, TEL, ADDR)
VALUES('superman', '김', '010-1111-1111', '제주도 서귀포시'); 
--==>> 1 행 이(가) 삽입되었습니다.

INSERT INTO TBL_IDPW(ID, PW)
VALUES('superman', 'java006$');
--==>> 1 행 이(가) 삽입되었습니다.

SELECT *
FROM TBL_STUDENTS;
--==>> superman	김	010-1111-1111	제주도 서귀포시

SELECT *
FROM TBL_IDPW; 
--==>> superman	java006$

--※ 위의 업무를 프로시저를 생성하여 처리하게 되면
EXEC PRC_STUDENTS_INSERT('happyday', 'PW', '이', '010-2222-2222', '서울 마포구');
-- 이와 같은 구문 한 줄로 양쪽 테이블에 데이터를 모두 제데로 입력할 수 있다. 

-- 다시 1번 시트로 프로시저 생성 후

-- 생성한 프로시저(PRC_STUDENTS_INSERT)가 제데로 작동하는지 여부 확인
-- → 프로시저 호출 
EXEC PRC_STUDENTS_INSERT('happyday', 'PW', '이', '010-2222-2222', '서울 마포구');
--==>>PL/SQL 프로시저가 성공적으로 완료되었습니다.

-- 확인

-- 프로시저 한 번 더 호출 테스트 
EXEC PRC_STUDENTS_INSERT('happytest', 'PW', '최', '010-3333-3333', '서울 은평구');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

-- 확인
SELECT *
FROM TBL_STUDENTS;
/*
superman	김	010-1111-1111	제주도 서귀포시
happyday	이	010-2222-2222	서울 마포구
happytest	최	010-3333-3333	서울 은평구
*/

SELECT *
FROM TBL_IDPW; 
/*
superman	pw
happyday	pw
happytest	pw
*/

--○ INSERT 프로시저 실습 
-- 테이블명 : TBL_SUNGJUK
CREATE TABLE TBL_SUNGJUK
(   HAKBUN  NUMBER
,   NAME    VARCHAR2(40)
,   KOR     NUMBER(3)
,   ENG    NUMBER(3)
,   MAT    NUMBER(3)
,   CONSTRAINT SUNGJUK_HAKBUN_PK PRIMARY KEY(HAKBUN) 
); 
--== Table TBL_SUNGJUK이(가) 생성되었습니다.

-- 학번, 이름, 국어, 영어, 수학 점수 데이터를 
-- 입력받을 수 있는 실습 테이블 생성

-- 생성된 테이블의 컬럼 구조 변경
-- (총점 → TOT, 평균→ AVG, 등급→GRADE)
ALTER TABLE TBL_SUNGJUK 
ADD (TOT NUMBER(3), AVG NUMBER(4,1), GRADE CHAR); 
--==>> Table TBL_SUNGJUK이(가) 변경되었습니다.

--※ 여기서 추가한 컬럼에 대한 항목들은
--   프로시저 실습을 위해 추가하는 것일 뿐
--   실제 테이블 구조에 적합하지도, 바람직하지도 않은 내용이다. 

--   변경된 테이블 구조 확인
DESC TBL_SUNGJUK;
/*
이름     널?       유형           
------ -------- ------------ 
HAKBUN NOT NULL NUMBER       
NAME            VARCHAR2(40) 
KOR             NUMBER(3)    
ENG             NUMBER(3)    
MAT             NUMBER(3)    
TOT             NUMBER(3)    
AVG             NUMBER(4,1)  
GRADE           CHAR(1)      
*/

-- 위의 업무 프로-----------------
EXEC PRC_SUNGJUK_INSERT(1, '조', 90,80,70);
--이와 같은 구문 한 줄로 총점, 평균, 등급 데이터를 모두 제데로 입력할 수 있다. 

EXEC PRC_SUNGJUK_INSERT(1, '조', 90,80,70);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_SUNGJUK;
--==>> 1	조	90	80	70	240	80	B
--2	유	87	96	73	256	85.3	B

-- 생성한 프로시저(PRC_SUNGJUK_INSERT)가 제데로 작동하는지 여부 확인
-- → 프로시저 호출 
EXEC PRC_SUNGJUK_INSERT(1, '조', 90,80,70);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_SUNGJUK;
-- 1	조	90	80	70	240	80	B

EXEC PRC_SUNGJUK_INSERT(2, '유', 87,96,73);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.