--- title: "오라클 DBMS 정리 12" date: "2018-11-09" tags: ["오라클DBMS"] status: archived verified: false --- ## 오라클 DBMS 정리 12 - PL/SQL CURSOR(using FOR), EXCEPTION --- ### FOR을 사용한 CURSOR * **생명주기를 개발자가 관리하지 않음** * 선언 -> 열기 -> 인출 -> 닫기 * 두가지 형태(선언, 비선언)로 커서에서 FOR가 사용됨 * **선언하여 커서 사용** * 입력값으로 조건을 사용하면 값의 검증을 수행할 수 없다 * **선언없이 커서 사용** * 선언하여 사용 1. 선언 2. 인출(FETCH 사용 X) ```sql DECLARE CURSOR 커서명 IS SELECT ... ; ... BEGIN ... FOR 레코드변수명 IN 커서명 LOOP 레코드변수명.컬럼명으로 사용 END LOOP; ... END; / ``` ```sql -- 사원번호, 사원명, 입사일, 연봉을 조회 -- 입사일은 년-월-일 분기의 형태 SET serveroutput ON DECLARE -- 1.선언 CURSOR cur_emp IS SELECT empno, ename, TO_CHAR(hiredate,'yyyy-mm-dd q') hiredate, sal FROM emp; BEGIN -- FOR가 시작되며 커서 열리고 자동으로 FETCH, CLOSE까지 수행 -- 2. FOR를 사용한 인출 -- (FETCH를 사용하지 않는다.) FOR emp_data IN cur_emp LOOP DBMS_OUTPUT.PUT_LINE(emp_data.empno||' ' ||emp_data.ename||' ' ||emp_data.hiredate||' ' ||emp_data.sal); END LOOP; -- LOOP가 끝나면서 커서 닫힘 END; / ``` * 선언하지 않고 커서 사용 * FOR의 IN에서 조회쿼리 작성 ```sql BEGIN ... FOR 레코드변수명 IN (SELECT ... ) LOOP 레코드변수명 : 조회된 결과를 얻을 수 있다 END LOOP; ... END; ``` ```sql -- 부서 번호를 입력받아 해당 부서에 따른 -- 사원번호, 사원명, 입사일, 연봉을 조회 -- 입사일은 년-월-일 분기의 형태 -- 부서번호가 10,20,30이 아니라면 -- 10번 부서로 조회하여 출력할 것 SET serveroutput ON SET verify OFF ACCEPT deptno PROMPT '부서번호 : ' DECLARE -- 입력값에 대한 IF문을 사용한 제약설정(값 검증)은 -- DECLARE-BEGIN사이에서 할 수 없다 i_deptno NUMBER(2) := &deptno; BEGIN IF &deptno NOT IN (10, 20, 30) THEN i_deptno := 10; END IF; FOR emp_data IN (SELECT empno, ename, TO_CHAR(hiredate,'yyyy-mm-dd q') hiredate, sal FROM emp WHERE deptno = i_deptno) LOOP DBMS_OUTPUT.PUT_LINE(emp_data.empno||' ' ||emp_data.ename||' '||emp_data.hiredate||' ' ||emp_data.sal); END LOOP; END; / ``` --- ### SYS_REFCURSOR * **주로 PROCEDURE에서 커서의 제어권을 외부로(실행하는 쪽) 넘길 때 사용** * sqlplus에서 PROCEDURE를 실행한 경우 REFCURSOR로 저장 * JAVA에서 PROCEDURE를 실행한 경우 ResultSet으로 저장 * 값에대한 검증을 한 후 쿼리를 수행할 수 있는 특징이 있다 * 사용법 * 3, 4번은 외부에서 호출하는 경우에는 작성하지 않는다 1. 커서 선언 2. 커서 열기 3. 인증 4. 닫기 ```sql DECLARE 커서명 SYS_REFCURSOR; BEGIN OPEN 커서명 FOR SELECT쿼리.. ; ... LOOP FETCH 커서명 INTO 변수명, ... END LOOP; CLOSE 커서명; END; ``` ```sql -- 사원테이블에서 매니저번호를 입력받아 매너지가 관리하는 -- 사원의 사원번호, 사원명, 입사일, 부서번호, 연봉을 조회 -- 입력된 매니저번호가 emp테이블에 존재하는 매니저 번호일때만 -- 검색을 수행한다. (7839, 7782, 7698, 7902, 7566, 7788) SET serveroutput ON SET verify OFF ACCEPT mgr PROMPT '매니저번호 : ' DECLARE i_mgr NUMBER; -- 1. SYS_REFCURSOR 선언 : 커서명 SYS_REFCURSOR; -- 데이터형처럼 선언 cur_mgr SYS_REFCURSOR; -- 조회결과를 저장할 레코드형 선언 TYPE rec_emp IS RECORD ( empno emp.empno%TYPE, ename emp.ename%TYPE, hiredate emp.hiredate%TYPE, deptno emp.deptno%TYPE, sal emp.sal%TYPE ); emp_rec rec_emp; BEGIN -- 입력된 매니저 번호가 존재하는지 검색 -- 커서 돌리면 받아오는 값은 레코드 변수! -- '.'를 사용해서 컬럼명을 참조한다.** FOR temp_rec IN (SELECT DISTINCT mgr FROM emp WHERE mgr = &mgr) LOOP i_mgr := temp_rec.mgr; END LOOP; IF i_mgr IS NOT NULL THEN -- 2. 커서 열기 : 조회 쿼리문 작성 OPEN cur_mgr FOR SELECT empno, ename, hiredate, deptno, sal FROM emp WHERE mgr = i_mgr; LOOP -- 3. 인출 FETCH cur_mgr INTO emp_rec; EXIT WHEN cur_mgr%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_rec.empno||' ' ||emp_rec.ename||' ' ||TO_CHAR(emp_rec.hiredate,'yyyy-mm-dd')||' ' ||emp_rec.deptno||' '||emp_rec.sal); END LOOP; IF cur_mgr%ISOPEN THEN -- 4. 닫기 CLOSE cur_mgr; DBMS_OUTPUT.PUT_LINE('커서를 닫습니다..'); END IF; ELSE DBMS_OUTPUT.PUT_LINe('&mgr 은 없는 매니저번호입니다.'); END IF; END; / ``` --- ### EXCEPTION Handling(예외 처리) * 코드에서 비정상적인 상황이 발생했을 때 그에대한 대비코드를 작성해 놓는 것. * 예외처리의 최상위 객체는 **\'OTHERS\'** * **예외처리는 PL/SQL 코드의 가장 마지막줄에 정의** * **예외가 발생하면 코드의 실행을 멈추고 예외처리코드가 실행된다.** * **RAISE**를 사용하면 개발자가 예외로 강제로 발생시켜 처리할 수 있다. ```sql ... ... EXCEPTION WHEN 예외처리객체명 THEN 예외가 발생 시 동작할 코드; ... END; / ``` * 예외처리객체 * **DUP_VAL_ON_INDEX** * INSERT 시 유일 인덱스(PK)에 중복값을 입력했을 때 * **NO_DATA_FOUND** * SELECT가 조회된 결과가 없을 때 * **TOO_MANY_ROWS** * SELECT가 여러행을 조회할 때 * ZERO_DIVIDE * 0으로 나눴을 때 * INVALID_CURSOR * 커서가 잘못된 연산을 수행했을 때 * INVALID_NUMBER * 숫자로 변경하지 못할 때 * **OTHERS** * 모든 예외를 다 처리할 수 있음 * 최상위 예외이기 때문에 EXCEPTION에서 가장 먼저 정의하게 되면 하위 예외처리 객체가 예외 처리하기 전에 OTHER가 처리하게 됨 ```sql -- 아이디, 이름, 비밀번호를 입력받아 table_primary -- 테이블에 레코드를 삽입하는 PL/SQL 작성 SET serveroutput ON SET verify OFF ACCEPT name PROMPT '이름 : ' ACCEPT id PROMPT '아이디 : ' ACCEPT pw PROMPT '비밀번호 : ' DECLARE name table_primary.name%TYPE; id table_primary.id%TYPE := '&id'; pw table_primary.pw%TYPE := '&pw'; BEGIN -- 문자열을 입력받아 입력받은 문자열의 길이가 -- 저장할 수 있는 크기가 아니라면 예외로는 처리할 수 없고 -- 코드로 처리해야 됨 IF LENGTH('&name') > 10 THEN DBMS_OUTPUT.PUT_LINE('입력가능한 글자수를 초과하였습니다'); ELSE name := '&name'; INSERT INTO table_primary(name, id, pw) VALUES(name, id, pw); DBMS_OUTPUT.PUT_LINE('추가 성공했습니다.'); COMMIT; END IF; EXCEPTION -- 무결성 제약조건 위배 시(PK에 중복값 입력 시) WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('입력하신 '||id||'는 이미 사용중입니다.'); -- 예외처리 객체가 제공하는 메시지 : sqlerrm DBMS_OUTPUT.PUT_LINE( sqlerrm ); -- 예외처리 객체가 제공하는 에러코드 : sqlcode DBMS_OUTPUT.PUT_LINE( sqlcode ); END; / ``` ![12-01](/md/legacy-assets/oracle/12-01.webp) * sqlerrm는 ORA-00001 출력 * sqlcode는 -1출력 * 에러코드는 ORA메시지와 안맞을 수도 있음 ```sql -- SELECT로 여러 행을 조회하거나 한 행을 조회할 때 예외처리 -- 사원번호를 입력받아 cp_emp3테이블에서 해당 사원의 -- 사원명, 부서번호, 직무, 연봉 조회하는 PL/SQL SET serveroutput ON SET verify OFF ACCEPT empno PROMPT '사원번호 : ' DECLARE i_empno number := &empno; TYPE rec_emp IS RECORD( ename cp_emp3.ename%TYPE, deptno cp_emp3.deptno%TYPE, job cp_emp3.job%TYPE, sal cp_emp3.sal%TYPE ); emp_rec rec_emp; BEGIN SELECT ename, deptno, job, sal INTO emp_rec FROM cp_emp3 WHERE empno = i_empno; DBMS_OUTPUT.PUT_LINE(emp_rec.ename||' ' ||emp_rec.deptno||' ' ||emp_rec.job||' ' ||emp_rec.sal); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(i_empno||'번 사원이 한 건 이상 존재합니다.'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(i_empno||'번 사원이 존재하지 않습니다.'); WHEN OTHERS THEN -- OTHERS에서 모든 예외를 다 처리할 수 있다. -- 최상위 예외이기 때문에 EXCEPTION에서 가장 먼저 정의하게되면 -- 하위 예외처리 객체가 예외를 처리하기 전에 OTHERS가 처리 DBMS_OUTPUT.PUT_LINE('인식하지 못한 예외'); END; / ``` ```sql ... EXCEPTION WHEN OTHERS THEN -- OTHERS안에서 비교구문으로 -- sqlcode를 이용해서 에러처리도 가능! IF sqlcode = 100 THEN DBMS_OUTPUT.PUT_LINE('사원정보가 조회되지 않습니다..'); END IF; IF sqlcode = -1422 THEN DBMS_OUTPUT.PUT_LINE('사원번호로 여러명의 사원이 조회됩니다.'); END IF; ... ``` --- ### 사용자 정의 예외처리 * RAISE를 사용하면 개발자가 예외를 발생시켜 처리할 수 있다 * **개발자가 오라클에서 제공하는 예외처리 객체를 사용하지 않고 에러코드와 메시지를 정의하고 제공하여 예외를 발생시키고 처리하는 방법** * 에러코드 * -20000 ~ -20999 중 하나의 코드를 사용 ```sql RAISE_APPLICATION_ERROR(에러코드, '제공할 메시지'); ``` ```sql EXCEPTION -- 예외가 발생했을 때 ORACLE에서 제공하는 형식의 예외메시지를 출력하고 -- 싶을 때 사용자 정의 예외처리를 한다. -- RAISE_APPLICATION_ERROR(에러코드, '제공할 메세지'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, '사용자 정의 예외'); END; ``` ![12-02](/md/legacy-assets/oracle/12-02.webp) --- ### FUNCTION * 사용자 정의 함수 * **자주 사용될 코드를 미리 작성하고 필요한 곳에서 사용하기 위해 만드는 것** * **쿼리문안에서 사용 (간접실행)** * 단독으로 실행될 수 없는 상태 * **반환형 존재** * 처리한 결과를 내보낼 때 사용 * **module로 구분이 된다** * FUNCTION, PROCEDURE, PACKAGE, TRIGGER는 모듈로 구분됨 * 모듈 당 SQL 파일 하나로 생각하면 됨 * 컴파일하면 user_procedures 딕셔너리에 추가됨 * user_procedures, user_objects 딕셔너리에서 확인 가능 * FUNCTION은 객체기 때문에 백업하면 PL/SQL과 다르게 백업이 가능하다 * 작성법 * 매개변수(parameter) : 함수 외부의 데이터를 함수 내부로 전달할 때 사용 * 매개변수를 넣고 연산 후 반환형을 갖는 결과가 반환됨 * 매개변수는 여러개 받을 수 있지만 반환형은 한개 ```sql CREATE OR REPLACE FUNCTION 함수명(매개변수 ...) RETURN 반환형 IS 변수 선언 BEGIN 코드 작성 RETURN 결과; END; / ``` * **Parameter와 Argument.** * 함수에 넣는값 - **Argument(인수)** * 함수로 받는 값 - **Parameter(매개변수)** * 컴파일 * 성공 또는 실패함 * show error - 실패시 발생한 에러를 출력(sqlplus문) ```sql SQL> @경로\파일명.sql ``` * 실행 * 간접실행 : 쿼리문에 넣어 실행 * **반환형이 반드시 있어야 된다(없어도 컴파일 되므로 주의!)** ```sql -- SELECT에서 함수 사용 SELECT 함수명(값) FROM 테이블명; -- INSERT에서 함수 사용 INSERT 테이블명(컬럼명) VALUES (함수명(값),..) ``` --- [숙제풀이](https://github.com/younggeun0/SSangYoung/blob/master/dev/query/date181106_pl/homework1.sql)