--- title: "오라클 DBMS 정리 11" date: "2018-11-08" tags: ["오라클DBMS"] status: archived verified: false --- ## 오라클 DBMS 정리 11 - PL/SQL with DML, CURSOR --- ### PL/SQL에서 Query문 사용하기 * DDL, DCL, DML 사용할 수 있다. * 다른건 다 같으나 SELECT만 좀 다르다. * **SELECT는 INTO절이 들어가며 한 행만 조회되어야 한다.** * **FUNCTION, PROCEDURE가 매개변수를 가지고 Query를 사용하면 매개변수명과 WHERE절에서 사용하는 컬럼명이 달라야 한다.** * 가능하면 변수명이 컬럼명과 같지 않도록 이름을 짓는다. * INSERT, UPDATE, DELETE는 수행한 행의 수를 반환하는 **암시적(묵시적) 커서**를 사용할 수 있다. ```sql -- 암시적(묵시적) 커서 SQL(커서명) % 속성명 -- 포스트 아래에 커서정리 참고 SQL%속성 ``` ```sql -- PL/SQL에서 DML 사용 BEGIN INSERT INTO cp_emp3(empno, ename, deptno, job, sal) VALUES(3333, '유재석', 10, '부장', 8000); COMMIT; END; / ``` ```sql -- 암시적(묵시적) 커서 ROWCOUNT속성을 이용하면 -- 수행한 행의 수를 얻을 수 있다. DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'행 추가'); END; / ``` ![11-01](/md/legacy-assets/oracle/11-01.webp) ```sql ... -- job의 데이터크기보다 큰 데이터를 넣기때문에 error! -- PL/SQL에서 INSERT는 성공 또는 실패(예외) INSERT INTO cp_emp3(empno, ename, deptno, job, sal) VALUES(3333, '유재석', 10, '부장님최고', 8000); COMMIT; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'행 추가'); -- 예외처리는 여기서 EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('문제발생'); END; / ``` ![11-02](/md/legacy-assets/oracle/11-02.webp) ```sql -- PL/SQL은 값을 제어한 후에 SQL문을 수행한다 -- PL/SQL을 사용하면 입력값에 따라 다른 테이블에 INSERT 가능하다 -- 사원번호, 사원명, 연봉을 사용자에게 입력받아 cp_emp2테이블에 -- 연봉이 5000이상이면 cp_emp3테이블에 추가하고 5000미만이라면 -- cp_emp2 테이블에 추가하는 PL/SQL 작성 -- 단, cp_emp3 테이블에 추가되는 사원의 부서 번호는 10번부서로 추가 SET serveroutput ON SET verify OFF ACCEPT empno PROMPT '사원번호 : ' ACCEPT ename PROMPT '사원명 : ' ACCEPT sal PROMPT '연봉 : ' DECLARE empno cp_emp2.empno%TYPE; ename cp_emp2.ename%TYPE; sal NUMBER := &sal; BEGIN empno := &empno; ename := '&ename'; IF sal >= 5000 THEN INSERT INTO cp_emp3(empno, ename, sal) VALUES(empno, ename, sal, 10); DBMS_OUTPUT.PUT_LINE('연봉이 5000이상이므로 cp_emp3 테이블에 추가'); ELSE INSERT INTO cp_emp2(empno, ename, sal, hiredate) VALUES(empno, ename, sal, SYSDATE); DBMS_OUTPUT.PUT_LINE('연봉이 5000미만이므로 cp_emp2 테이블에 추가'); END IF; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'행 입력성공'); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Exception!'); END; / ``` ```sql -- 업데이트는 수행 횟수를 알아야 한다(0~n개의 레코드가 바뀜) -- PL/SQL에서 UPDATE 사용 SET serveroutput ON DECLARE cnt NUMBER; BEGIN UPDATE cp_emp2 SET sal = 3000 WHERE empno = 2221; -- 암시적(묵시적) 커서 SQL을 사용하여 변경한 행의 수를 얻는다 cnt := SQL%ROWCOUNT; -- 변경된 사원이 1명일때만 Transcation을 완료하고 그렇지 않다면 -- 작업을 취소. IF cnt = 1 THEN DBMS_OUTPUT.PUT_LINE(cnt||'건이 변경되었습니다'); COMMIT; ELSE DBMS_OUTPUT.PUT_LINE('조회된 사원이 여러명이거나 없으므로 변경작업을 취소합니다..'); ROLLBACK; END IF; END; / ``` ```sql -- 삭제 SET serveroutput ON BEGIN DELETE FROM cp_emp2 WHERE empno = 2222; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'건이 삭제되었습니다.'); END; / ``` --- ### SELECT * 조회된 결과를 저장할 **INTO**절이 정의됨 * 조회된 컬럼값이 변수명에 들어감 * **변수와 컬럼의 데이터형과 갯수는 일치해야 한다.** * 부분이 나눠지기 때문에 컬럼명과 변수명이 같아도 된다(INSERT 처럼) * **조회결과가 한행이 아니라면 error가 발생!** * 에러발생 시 EXCEPTION 절로 넘어감 ```sql SELECT 컬럼명, ... INTO 변수명, ... FROM 테이블명 ... ``` ```sql -- cp_emp2에서 사원번호가 7566인 사원의 -- 사원명, 입사일, 연봉을 조회하여 출력하는 PL/SQL SET serveroutput ON DECLARE ename cp_emp2.ename%TYPE; input_date VARCHAR2(10); sal cp_emp2.sal%TYPE; BEGIN SELECT ename, TO_CHAR(hiredate, 'yyyy-mm-dd'), sal INTO ename, input_date, sal FROM cp_emp2 WHERE empno = 7566; DBMS_OUTPUT.PUT_LINE(ename||' / ' ||input_date||' / ' ||sal||' / '||SQL%ROWCOUNT); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('문제발생'); END; / ``` ```sql -- SELECT와 INSERT를 수행하는 PL/SQL 예제 -- 사원번호, 사원명, 부서번호, 직무를 입력받아 -- cp_emp3 테이블에 추가하는 PL/SQL -- 단, 사원의 연봉은 부서번호에 따라 동일 연봉으로 추가한다. -- 10번 부서 - emp 테이블의 사원번호가 7566인 사원과 같은 연봉, -- 20번 부서 - emp 테이블의 사원번호가 7698인 사원과 같은 연봉, -- 30번 부서 - emp 테이블의 사원번호가 7782인 사원과 같은 연봉, -- 그 외 부서번호가 입력되면 7788 사원의 연봉으로 추가 SET serveroutput ON ACCEPT empno PROMPT '사원번호 입력 : ' ACCEPT ename PROMPT '사원명 입력 : ' ACCEPT deptno PROMPT '부서번호 입력 : ' ACCEPT job PROMPT '직무 입력 : ' DECLARE i_empno cp_emp3.empno%TYPE := &empno; i_ename cp_emp3.ename%TYPE := '&ename'; i_deptno cp_emp3.deptno%TYPE := &deptno; i_job cp_emp3.job%TYPE := '&job'; sal cp_emp3.sal%TYPE; select_empno cp_emp.empno%TYPE := 7788; BEGIN -- 입력되는 부서번호별로 참조할 연봉을 조회하기 위한 -- 사원번호를 설정 IF i_deptno = 10 THEN select_empno := 7566; ELSIF i_deptno = 20 THEN select_empno := 7698; ELSIF i_deptno = 30 THEN select_empno := 7782; END IF; -- 설정된 사원번호별로 연봉을 조회 SELECT sal INTO sal FROM emp WHERE empno = select_empno; -- 조회된 연봉으로 추가 작업 INSERT INTO cp_emp3(empno, ename, deptno, job, sal) VALUES(i_empno, i_ename, i_deptno, i_job, sal); DBMS_OUTPUT.PUT_LINE(i_empno||'번 사원정보를 추가하였습니다.'); DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'건 입력 성공'); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('에러발생'); END; / ``` --- ### CURSOR * 다른 언어로 보면 **포인터** * **암시적(묵시적) 커서와 명시적 커서로 제공됨** * 암시적 커서 * 개발자가 정의하지 않아도 쿼리문을 실행하면 자동으로 생성되는 커서. * 커서명 : SQL * 명시적커서 * 개발자가 정의해서 사용하는 커서 * 생명주기를 가진다 * 선언->열기->인출->닫기 * 사용할 수 있는 속성이 많다 * 커서는 인라인뷰를 사용, 메모리를 계속 사용한다. * 꼭 닫기로 닫아줘야 메모리 누수를 잡을 수 있다. ```sql -- 암시적(묵시적)커서 SQL%속성명 -- 명시적커서 커서명%속성명 -- 커서 속성사용 커서명%속성명 -- 커서 동작 제어 동작명 커서명 ``` * 커서의 동작과 속성 ![11-03](/md/legacy-assets/oracle/11-03.webp) * 커서 사용법 1. 선언 : 커서가 실행할 쿼리문을 정의 2. 열기 3. 인출 4. 닫기 ```sql DECLARE -- 1 CURSOR 커서명 IS SELECT ... BEGIN -- 2 OPEN 커서명; -- 3 LOOP FETCH 커서명 INTO 변수명,... ; -- 커서에서 인출한 값이 없다면 반복문을 빠져나가야한다 EXIT WHEN (커서명%NOTFOUND); -- 값이 있었을 때 처리할 코드들.. END LOOP; -- 4 CLOSE 커서명; END; / ``` ```sql SET serveroutput ON DECLARE -- 1. 선언 CURSOR cur_dept IS SELECT deptno, dname, loc FROM dept; dept_data dept%ROWTYPE; BEGIN -- 2. 열기 OPEN cur_dept; LOOP -- 3. 인출(커서가 움직이며 레코드의 컬럼값을 얻기) FETCH cur_dept INTO dept_data; EXIT WHEN(cur_dept%NOTFOUND); DBMS_OUTPUT.PUT_LINE(dept_data.deptno||' '||dept_data.dname ||' '||dept_data.loc); END LOOP; -- 4. 닫기 CLOSE cur_dept; END; / ``` ```sql --- 데이터는 가져오는부분(처리하는부분)과 보여주는 부분(VIEW)으로 나눔 -- 부서번호를 입력받아 해당부서에 근무하는 -- 사원번호, 사원명, 연봉, 입사일을 조회하고, -- 조회한 후 총 사원 수와, 연봉합을 구하시오. SET serveroutput ON SET verify OFF ACCEPT deptno PROMPT '부서번호 : ' DECLARE CURSOR cur_emp IS SELECT empno, ename, sal, TO_CHAR(hiredate, 'yyyy-mm-dd') hiredate FROM emp WHERE deptno = &deptno; TYPE rec_emp IS RECORD ( empno emp.empno%TYPE, ename emp.ename%TYPE, sal emp.sal%TYPE, hiredate VARCHAR2(10) ); -- 테이블 생성 (데이터 처리와 보여주는 부분을 분리시키기 위해) TYPE tab_emp IS TABLE OF rec_emp INDEX BY BINARY_INTEGER; emp_data rec_emp; arr_emp tab_emp; total_sal NUMBER := 0; i NUMBER := 0; BEGIN IF cur_emp%ISOPEN THEN CLOSE cur_emp; END IF; OPEN cur_emp; -- 데이터 처리하는 부분 LOOP FETCH cur_emp INTO emp_data; EXIT WHEN cur_emp%NOTFOUND; i := i+1; arr_emp(i) := emp_data; END LOOP; -- 화면 구성하는 부분 IF i != 0 THEN DBMS_OUTPUT.PUT_LINE('사원번호 사원명 연봉 입사일'); FOR idx IN 1 .. arr_emp.COUNT LOOP DBMS_OUTPUT.PUT_LINE(arr_emp(idx).empno|| ' '||arr_emp(idx).ename||' '||arr_emp(idx).sal|| ' '||arr_emp(idx).hiredate); total_sal := total_sal + arr_emp(idx).sal; END LOOP; DBMS_OUTPUT.PUT(&deptno||'번 부서 사원수 : '||cur_emp%ROWCOUNT); DBMS_OUTPUT.PUT_LINE(', 연봉합 : '||total_sal); ELSE DBMS_OUTPUT.PUT_LINE(&deptno||'번 부서는 사원이 없습니다.'); END IF; CLOSE cur_emp; END; / ``` --- ### 헷갈리는 내용 추가복습 * TABLE TYPE * 테이블변수명(인덱스) := 레코드변수명 * 레코드할당 * 테이블변수명.COUNT * 테이블 레코드 수를 반환 * 커서명 속성 * 커서명%ROWCOUNT * 커서로 패치한 수를 반환 * 커서명%NOTFOUND * 커서가 패치할 레코드가 없으면 FALSE를 반환 * LOOP문 빠져나갈때 EXIT 조건으로 사용 --- [숙제풀이](https://github.com/younggeun0/SSangYoung/blob/master/dev/query/date181105_pl/homework1.sql)