(Microsoft Word - PLSQL\300\314\266\365.doc)

Transcription

1 오라클 PL/SQL 제를다른홈페이지에기재할경우에는출처를꼭밝혀주세요. ( 1 PL/SQL 의개요 1.1 PLSQL 이란? [ 2002/01/20 ] 1.2 PL/SQL Block 구조 [ 2002/05/09 ] 1.3 PL/SQL 블럭의유형 [ 2002/05/09 ] 2 프로시저 (PROCEDURE) 와함수 (FUNCTION) 2.1 프로시저 (PROCEDURE) [ 2002/01/20 ] 2.2 함수 (FUNCTION) [ 2002/01/20 ] 3 PL/SQL 데이터타입 3.1 스칼라데이터타입 [ 2002/01/20 ] 3.2 복합데이터타입 3.3 %ROWTYPE [ 2002/01/20 ] 3.4 PL/SQL 테이블 [ 2002/01/20 ] 3.5 PLSQL 레코드 [ 2002/01/20 ] 3.6 PL/SQL Table of Record [ 2002/01/20 ] 4 PL/SQL 내의 SQL 문 4.1 INSERT [ 2002/01/20 ] 4.2 UPDATE [ 2002/01/20 ] 4.3 DELETE [ 2002/01/20 ] 5 PL/SQL 제어문 5.1 반복제어 FOR LOOP [ 2002/01/20 ] LOOP 문, WHILE 문 [ 2002/01/20 ] 5.2 조건제어 (IF) [ 2002/01/20 ] 6 SQL 커서 6.1 암시적커서 (Implicit Cursor) [ 2002/01/20 ] 6.2 명시적커서 (Explicit Cursor) 7 예외절처리 Explicit Cursor [ 2002/01/20 ] FOR 문에서커서사용 (Cursor FOR Loops) [ 2002/01/20 ] 명시적커서의속성 (Explicit Cursor Attributes) [ 2002/01/20 ] 파라미터가있는커서 (Cursors with Parameters) [ 2002/01/20 ] The WHERE CURRENT OF Clause [ 2002/01/20 ] PL/SQL 1

2 7.1 예외 (Exception) [ 2002/01/20 ] 7.2 미리정의된예외 (Predefined Exceptions) [ 2002/01/20 ] 7.3 미리정의되지않은예외 (Non-Predefined Exception) [ 2002/01/20 ] 7.4 사용자정의예외 (User-Defined Exceptions) [ 2002/01/20 ] 7.5 SQLCODE, SQLERRM [ 2002/01/20 ] 8 Package( 패키지 ) [ 2002/01/20 ] 9 Trigger( 트리거 ) [ 2002/01/20 ] PL/SQL 2

3 PL/SQL 이란? - PL/SQL 은 Oracle's Procedural Language extension to SQL. 의약자입니다. - SQL 문장에서변수정의, 조건처리 (IF), 반복처리 (LOOP, WHILE, FOR) 등을지원하며, 오라클자체에내장되어있는 Procedure Language 입니다 - DECLARE 문을이용하여정의되며, 선언문의사용은선택사항입니다. - PL/SQL 문은블록구조로되어있고 PL/SQL 자신이컴파일엔진을가지고있습니다. PL/SQL 의장점 - PL/SQL 문은 BLOCK 구조로다수의 SQL 문을한번에 ORACLE DB 로보내서처리하므로수행속도를향상시킬수있습니다. - PL/SQL 의모든요소는하나또는두개이상의블록으로구성하여모듈화가가능하다. - 보다강력한프로그램을작성하기위해서큰블록안에소블럭을위치시킬수있습니다. - Variable, Constant, Cursor, Exception 을정의하고, SQL 문장과 Procedural 문장에서사용합니다. - 단순, 복잡한데이터형태의변수를선언합니다. - 테이블의데이터구조와 DataBase 의컬럼럼에준하여동적으로변수를선언할수있습니다. - Exception 처리루틴을이용하여 Oracle Server Error 를처리합니다. - 사용자정의에러를선언하고 Exception 처리루틴으로처리가능합니다. PL/SQL Block Structure - PL/SQL 은프로그램을논리적인블록으로나누는구조화된블록언어입니다. - PL/SQL 블록은선언부 ( 선택적 ), 실행부 ( 필수적 ), 예외처리부 ( 선택적 ) 로구성되어있고, 과 END 키워드는반드시기술해주어야합니다. - PL/SQL 블록에서사용하는변수는블록에대해논리적으로선언할수있고사용할수있습니다. Declarative Section( 선언부 ) - 변수, 상수, CURSOR, USER_DEFINE Exception 선언 Executable Section( 실행부 ) - SQL, 반복분, 조건문실행 - 실행부는 으로시작하고 END 로끝납니다. - 실행문은프로그램내용이들어가는부분으로서필수적으로사용되어야합니다. Exception Handling Section( 예외처리 ) - 예외에대한처리. - 일반적으로오류를정의하고처리하는부분으로선택사항입니다. PL/SQL 3

4 DECLARE - Optional - Variables, cursors, user-defined exceptions - Mandatory - SQL Statements - PL/SQL Statements EXCEPTION - Actions to perform when errors occur END; - Mandatory PL/SQL 프로그램의작성요령 - PL/SQL 블록내에서는한문장이종료할때마다세미콜론 (;) 을사용합니다.. - END 뒤에 ; 을사용하여하나의블록이끝났다는것을명시합니다. - PL/SQL 블록의작성은편집기를통해파일로작성할수도있고, SQL 프롬프트에서바로작성할수도있습니다. - SLQ*PLUS 환경에서는 DELCLARE 나 이라는키워드로 PL/SQL 블럭이시작하는것을알수있습니다. - 단일행주석 : -- - 여러행주석 : /* */ - PL/SQL 블록은행에 / 가있으면종결됩니다. PL/SQL 4

5 프로시져란.. - 특정작업을수행할수있고, 이름이있는 PL/SQL 블록으로서. 매개변수를받을수있고.. 반복적으로사용할수있는거죠.. 보통연속실행또는구현이복잡한트랜잭션을수행하는 PL/SQL 블록을데이터베이스에저장하기위해생성합니다. CREATE OR REPLACE 구문을사용하여생성합니다. IS 로 PL/SQL 의블록을시작합니다. LOCAL 변수는 IS 와 사이에선언합니다. [Syntax] CREATE OR REPLACE procedure name IS IN argument OUT argument IN OUT argument [ 변수의선언 ] --> 필수 [PL/SQL Block] -- SQL 문장, PL/SQL 제어문장 [EXCEPTION] --> 선택 -- error 가발생할때수행하는문장 END; --> 필수 프로시저작성예제 SQL>CREATE OR REPLACE PROCEDURE update_sal /* IN Parameter */ (v_empno IN NUMBER) IS UPDATE emp SET sal = sal * 1.1 WHERE empno = v_empno; COMMIT; END update_sal; / 프로시져가생성되었습니다. PL/SQL 5

6 설명.. 프로시저의이름은 update_sal 이고.. 프로시저 update_sal 은사번 (v_empno) 를입력받아서급여를 update 시켜주는 sql 문입니다. 프로시저를끝마칠때에는항상 "/" 를지정합니다. 프로시저의실행 EXECUTE 문을이용해프로시저를실행합니다. SQL> execute update_sal(7369); PL/SQL 처리가정상적으로완료되었습니다 번사원의급여가 10% 인상됐습니다. SELECT 문을실행시켜보면데이터가수정된것을확인할수있습니다. Parameter 란 실행환경과 program 사이에값을주고받는역할을합니다. 블록안에서의변수와똑같이일시적으로값을저장하는역할을합니다. Parameter 의타입 - IN : 실행환경에서 program 으로값을전달 - OUT : program 에서실행환경으로값을전달 - INOUT : 실행환경에서 program 으로값을전달하고, 다시 program 에서실행환경으로변경된값을전달 PL/SQL 6

7 Block Type(PL/SQL 블럭의유형 ) [ Anonymous ] [ Procedure ] [ Function ] Anonymous Block( 익명블록 ) 이름이없는블록을의미합니다. 실행하기위해프로그램안에서선언되고실행시에실행을위해 PL/SQL 엔진으로전달됩니다. 선행컴파일러프로그램과 SQL*Plus 또는서버관리자에서익명의블록을내장할수있습니다. Procedure( 프로시저 ) 특정작업을수행할수있는이름이있는 PL/SQL 블록으로서. 매개변수를받을수있고.. 반복적으로사용할수있는거죠.. 보통연속실행또는구현이복잡한트랜잭션을수행하는 PL/SQL블록을데이터베이스에저장하기위해생성합니다. Function( 함수 ) 보통값을계산하고결과값을반환하기위해서함수를많이사용합니다. 대부분구성이프로시저와유사하지만 IN 파라미터만사용할수있고, 반드시반환될값의데이터타입을 RETURN 문에선언해야합니다. 또한 PL/SQL 블록내에서 RETURN 문을통해서반드시값을반환해야합니다. PL/SQL 7

8 함수 (Function) - 보통값을계산하고결과값을반환하기위해서함수를많이사용합니다. - 대부분구성이프로시저와유사하지만 IN 파라미터만사용할수있습니다. - 반드시반환될값의데이터타입을 RETURN 문에선언해야합니다. - 또한 PL/SQL 블록내에서 RETURN 문을통해서반드시값을반환해야합니다. [Syntax] CREATE OR REPLACE FUNCTION function name [(argument...)] RETURN datatype IS -- Datatype 은반환되는값의 datatype 입니다. [ 변수선언부분 ] [PL/SQL Block] -- PL/SQL 블록에는적어도한개의 RETURN 문이있어야합니다. -- PL/SQL Block 은함수가수행할내용을정의한몸체부분입니다. END; PL/SQL 8

9 SQL> CREATE OR REPLACE FUNCTION FC_update_sal (v_empno IN NUMBER) -- 리턴되는변수의데이터타입을꼭정의해야합니다 RETURN NUMBER. IS v_sal emp.sal%type; UPDATE emp SET sal = sal * 1.1 WHERE empno = v_empno; COMMIT; SELECT sal INTO v_sal FROM emp WHERE empno = v_empno; -- 리턴문이꼭존재해야합니다 RETURN v_sal; END; 함수가생성되었습니다. 설명.. 이함수에는 v_sal 이라는 %type 변수가사용되고있습니다. 스칼라데이터타입을참고하세요. 프로지저와마찬가지로세미콜론 (;) 으로블록을종료한뒤 "/" 를붙여코드를끝마칩니다. 함수의실행먼저함수의반환값을저장할변수를선언합니다. SQL> VAR salary NUMBER; EXECUTE 문을이용해함수를실행합니다. SQL>EXECUTE :salary := FC_update_sal(7900); PL/SQL 처리가정상적으로완료되었습니다. 오라클 SQL 에서선언된변수의출력은 PRINT 문을사용합니다. PRINT 문으로함수의반환값을저장한 salary 의값을확인하면됩니다. SQL>PRINT salary; SALARY 결과가이렇게나옵니다. PL/SQL 9

10 변수선언방법 [Syntax] Identifier 의이름은 sql 의 object 명과동일한규칙을따릅니다. Identifier 를상수로지정하고싶은경우는 CONSTANT 라는 KEYWORD 를명시하고반드시초기화를할당합니다. NOT NULL 이정의되어있으면초기값을반드시지정하고, 정의되어있지않을때는생략가능합니다. 초기값은할당연산자 (:=) 를사용하여정의합니다. 초기값을정의하지않으면 Identifier 는 NULL 값을가지게됩니다. 일반적으로한줄에한개의 Identifier 를정의합니다. 스칼라데이터타입은단수데이터형으로한가지의데이터값만가집니다. BINARY_INTEGER NUMBER[(P, S)] 에서 사이의정수 고정및부동소숫점수에대한기본유형 CHAR[( 최대길이 )] 고정길이문자에대한기본형은 바이트까지입니다. 지정하지않는다면디폴트길이는 1 로설정됩니다. LONG 고정길이문자에대한기본형은 바이트까지입니다. LONG 데이터베이스열의최대폭은 바이트입니다. LONG RAW 이진데이터와바이트문자열에대한기본형은 32760Byte 까지입니다. LONG RAW 데이터는 PL/SQL 에의해해석되지않습니다. VARCHAR2( 최대길이 ) 3 변수길이문자데이터에데한기본형은 32767Byte 까지입니다. VARCHAR2 변수와상수에대한디폴트크기는없습니다. DATE BOOLEAN 날짜와시간에대한기본형. DATE 값은지정이후의초단위로날에대한시간을포함합니다. 날짜의범위는 BC 4712 년 1 월 1 일부터 AD 9999 년 12 월 31 일사이입니다. 논리연산에사용되는세가지값 (TRUE, FALSE, NULL) 중하나를저장하는데이터유형 PL/SQL 10

11 선언예제 v_price CONTANT NUMBER(4,2) := ; -- 상수숫자선언 ( 변할수없다 ) v_name VARCHAR22(20) ; v_bir_type CHAR(1) ; v_flag BOOLEAN NOT NULL := TRUE ; -- NOT NULL 값 TRUE 로초기화 v_birthday DATE; %TYPE 데이터형 %TYPE 데이터형은기술한데이터베이스테이블의컬럼데이터타입을모를경우사용할수있고, 또. 코딩이후데이터베이스컬럼의데이터타입이변경될경우다시수정할필요가없습니다. 이미선언된다른변수나데이터베이스컬럼의데이터타입을이용하여선언합니다. 데이터베이스테이블과컬럼그리고이미선언한변수명이 %TYPE 앞에올수있습니다. %TYPE 속성을이용하여얻을수있는장점 - 기술한 DB column definition 을정확히알지못하는경우에사용할수있습니다. - 기술한 DB column definition 이변경되어도다시 PL/SQL 을고칠필요가없습니다. 예제 v_empno emp.empno%type := 7900 ; v_ename emp.ename%type; PL/SQL 11

12 예제프로시져.. SQL>CREATE OR REPLACE PROCEDURE Emp_Info ( p_empno IN emp.empno%type ) IS -- %TYPE 데이터형변수선언 v_empno emp.empno%type; v_ename emp.ename%type; v_sal emp.sal%type; DBMS_OUTPUT.ENABLE; -- %TYPE 데이터형변수사용 SELECT empno, ename, sal INTO v_empno, v_ename, v_sal FROM emp WHERE empno = p_empno ; -- 결과값출력 DBMS_OUTPUT.PUT_LINE( ' 사원번호 : ' v_empno ); DBMS_OUTPUT.PUT_LINE( ' 사원이름 : ' v_ename ); DBMS_OUTPUT.PUT_LINE( ' 사원급여 : ' v_sal ); END; / 프로시져가생성되었습니다. SQL>SET SERVEROUTPUT ON; -- DBMS_OUTPUT 결과값을화면에출력하기위해 실행결과 SQL> EXECUTE Emp_Info(7369); 사원번호 : 7369 사원이름 : SMITH 사원급여 : 880 PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 12

13 복합데이터타입 하나이상의데이터값을갖는데이터타입으로배열과비슷한역할을하고재사용이가능합니다. %ROWTYPE 데이터형과, PL/SQL 테이블과레코드가복합데이터타입에속합니다. %ROWTYPE 테이블이나뷰내부의컬럼데이터형, 크기, 속석등을그대로사용할수있습니다. %ROWTYPE 앞에오는것은데이터베이스테이블이름입니다. 지정된테이블의구조와동일한구조를갖는변수를선언할수있습니다. 데이터베이스컬럼들의수나 DATATYPE 을알지못할때편리합니다. 테이블의데이터컬럼의 DATATYPE 이변경될경우프로그램을재수정할필요가없습니다. %ROWTYPE 예제프로시져.. SQL>CREATE OR REPLACE PROCEDURE RowType_Test ( p_empno IN emp.empno%type ) IS v_emp emp%rowtype ; DBMS_OUTPUT.ENABLE; -- %ROWTYPE 변수사용 SELECT empno, ename, hiredate INTO v_emp.empno, v_emp.ename, v_emp.hiredate FROM emp WHERE empno = p_empno; DBMS_OUTPUT.PUT_LINE( ' 사원번호 : ' v_emp.empno ); DBMS_OUTPUT.PUT_LINE( ' 사원이름 : ' v_emp.ename ); DBMS_OUTPUT.PUT_LINE( ' 입사일 : ' v_emp.hiredate ); END; / 프로시져가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 SQL> EXECUTE RowType_Test(7900); 사원번호 : 7900 사원이름 : JAMES 입사일 : 81/12/03 PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 13

14 PL/SQL 테이블 PL/SQL 에서의테이블은오라클 SQL 에서의테이블과는다릅니다. PL/SQL 에서의테이블은일종의일차원배열이라고생각하시면이해하기쉬울겁니다. 테이블은크기에제한이없으면그 ROW 의수는데이터가들어옴에따라자동증가합니다. BINARY_INTEGER 타입의인덱스번호로순서가정해집니다. 하나의테이블에한개의컬럼데이터를저장합니다. 예제 TYPE prdname_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; -- prdname_table 테이블타입으로 prdname_tab 변수를선언해서사용 prdname_tab prdname_table ; PL/SQL 14

15 PL/SQL 테이블예제프로시져.. SQL>CREATE OR REPLACE PROCEDURE Table_Test (v_deptno IN emp.deptno%type) IS TYPE empno_table IS TABLE OF emp.empno%type INDEX BY BINARY_INTEGER; TYPE ename_table IS TABLE OF emp.ename%type INDEX BY BINARY_INTEGER; TYPE sal_table IS TABLE OF emp.sal%type INDEX BY BINARY_INTEGER; -- 테이블타입으로변수를선언해서사용 empno_tab empno_table ; ename_tab ename_table ; sal_tab sal_table; i BINARY_INTEGER := 0; DBMS_OUTPUT.ENABLE; FOR emp_list IN(SELECT empno, ename, sal FROM emp WHERE deptno = v_deptno) LOOP /* emp_list 는자동선언되는 BINARY_INTEGER 형변수로 1 씩증가합니다. emp_list 대신다른문자열사용가능 */ i := i + 1; -- 테이블변수에검색된결과를넣습니다 empno_tab(i) := emp_list.empno ; ename_tab(i) := emp_list.ename ; sal_tab(i) := emp_list.sal ; END LOOP; -- 1 부터 i 까지 FOR 문을실행 FOR cnt IN 1..i LOOP -- TABLE 변수에넣은값을뿌려줌 DBMS_OUTPUT.PUT_LINE( ' 사원번호 : ' empno_tab(cnt) ); DBMS_OUTPUT.PUT_LINE( ' 사원이름 : ' ename_tab(cnt) ); DBMS_OUTPUT.PUT_LINE( ' 사원급여 : ' sal_tab(cnt) ); END LOOP; END; / PL/SQL 15

16 프로시져가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE Table_Test(10); 사원번호 : 7782 사원이름 : CLARK 사원급여 : 2450 사원번호 : 7839 사원이름 : KING 사원급여 : 5000 사원번호 : 7934 사원이름 : MILLER 사원급여 : 1300 PL/SQL 처리가정상적으로완료되었습니다. emp 테이블에있는데이터의입력한부서에해당하는사원번호, 사원이름, 사원급여를뿌려주는프로시져입니다 PL/SQL 16

17 PL/SQL 레코드여러개의데이터타입을갖는변수들의집합입니다. 스칼라, RECORD, 또는 PL/SQL TABLE datatype 중하나이상의요소로구성됩니다. 논리적단위로서필드집합을처리할수있도록해줍니다. PL/SQL 테이블과다르게개별필드의이름을부여할수있고, 선언시초기화가가능합니다. 예제 TYPE record_test IS RECORD ( record_empno NUMBER, record_ename VARCHAR2(30), record_sal prd_record NUMBER); record_test; PL/SQL RECORD 예제프로시져.. SQL> CREATE OR REPLACE PROCEDURE Record_Test ( p_empno IN emp.empno%type ) IS TYPE emp_record IS RECORD (v_empno NUMBER, v_ename VARCHAR2(30), v_hiredate DATE ); emp_rec emp_record ; DBMS_OUTPUT.ENABLE; SELECT empno, ename, hiredate INTO emp_rec.v_empno, emp_rec.v_ename, emp_rec.v_hiredate FROM emp WHERE empno = p_empno; END; DBMS_OUTPUT.PUT_LINE( ' 사원번호 : ' emp_rec.v_empno ); DBMS_OUTPUT.PUT_LINE( ' 사원이름 : ' emp_rec.v_ename ); DBMS_OUTPUT.PUT_LINE( ' 입사일 : ' emp_rec.v_hiredate ); / 프로시져가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE Record_Test(7369); 사원번호 : 7369 사원이름 : SMITH 입사일 : 80/12/17 PL/SQL 처리가정상적으로완료되었습니다. %ROWTYPE 예제와비교해보세요 PL/SQL 17

18 PL/SQL Table of Record PL/SQL TABLE 변수선언과비슷하며데이터타입을 %ROWTYPE 으로선언하면됩니다. PL/SQL TABLE 과 RECORD 의복합기능을합니다. DECLARE TYPE dept_table_type IS TABLE OF dept%rowtype INDEX BY BINARY_INTEGER; -- Each element of dept_table is a record dept_table dept_table_type ; PL/SQL TABLE OF RECORD 예제프로시져.. SQL> CREATE OR REPLACE PROCEDURE Table_Test IS i BINARY_INTEGER := 0; -- PL/SQL Table of Record 의선언 TYPE dept_table_type IS TABLE OF dept%rowtype INDEX BY BINARY_INTEGER; dept_table dept_table_type; / FOR dept_list IN (SELECT * FROM dept) LOOP i:= i+1; dept_table(i).deptno := dept_list.deptno ; dept_table(i).dname := dept_list.dname ; dept_table(i).loc := dept_list.loc ; END LOOP; FOR cnt IN 1..i LOOP DBMS_OUTPUT.PUT_LINE( ' 부서번호 : ' dept_table(cnt).deptno END LOOP; END; ' 부서명 : ' dept_table(cnt).dname ' 위치 : ' dept_table(cnt).loc ); PL/SQL 18

19 실행결과 SQL>set serveroutput on; SQL>exec Table_test; 부서번호 : 10 부서명 : ACCOUNTING 위치 : NEW_YORK 부서번호 : 20 부서명 : RESEARCH 위치 : DALLAS 부서번호 : 30 부서명 : 인사과위치 : CHICAGO 부서번호 : 40 부서명 : OPERATIONS 위치 : BOS%TON PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 19

20 Insert 문 PL/SQL 에서의 INSERT 문은 SQL 과비슷합니다. 사원등록예제프로시져.. SQL> CREATE OR REPLACE PROCEDURE Insert_Test ( v_empno IN emp.empno%type, v_ename IN emp.ename%type, v_deptno IN emp.deptno%type ) IS DBMS_OUTPUT.ENABLE; INSERT INTO emp(empno, ename, hiredate, deptno) VALUES(v_empno, v_ename, sysdate, v_deptno); DBMS_OUTPUT.PUT_LINE( ' 사원번호 : ' v_empno ); DBMS_OUTPUT.PUT_LINE( ' 사원이름 : ' v_ename ); DBMS_OUTPUT.PUT_LINE( ' 사원부서 : ' v_deptno ); DBMS_OUTPUT.PUT_LINE( ' 데이터입력성공 ' ); END ; / 프로시져가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE Insert_Test(1000, 'brave', 20); 사원번호 : 1000 사원이름 : brave 사원부서 : 20 데이터입력성공 PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 20

21 UPDATE 상품수정예제프로시저.. 특정사원의급여를일정 % 센트인상 / 인하하는프로시져 SQL>CREATE OR REPLACE PROCEDURE Update_Test ( v_empno IN emp.empno%type, -- 급여를수정한사원의사번 v_rate IN NUMBER ) -- 급여의인상 / 인하율 IS v_emp emp%rowtype ; DBMS_OUTPUT.ENABLE; UPDATE emp SET sal = sal+(sal * (v_rate/100)) -- 급여를계산 WHERE empno = v_empno ; DBMS_OUTPUT.PUT_LINE( ' 데이터수정성공 ' ); -- 수정된데이터확인하기위해검색 SELECT empno, ename, sal FROM emp WHERE empno = v_empno ; INTO v_emp.empno, v_emp.ename, v_emp.sal DBMS_OUTPUT.PUT_LINE( ' **** 수정확인 **** '); DBMS_OUTPUT.PUT_LINE( ' 사원번호 : ' v_emp.empno ); DBMS_OUTPUT.PUT_LINE( ' 사원이름 : ' v_emp.ename ); DBMS_OUTPUT.PUT_LINE( ' 사원급여 : ' v_emp.sal ); END ; / 프로시저가생성되었습니다. 프로시저실행 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE Update_Test(7900, -10); 데이터수정성공 **** 수정확인 **** 사원번호 : 7900 사원이름 : JAMES 사원급여 : 855 PL/SQL 처리가정상적으로완료되었습니다 번사원의급여를 10% 인하했습니다. PL/SQL 21

22 DELETE 사원삭제예제프로시저.. SQL> CREATE OR REPLACE PROCEDURE Delete_Test ( p_empno IN emp.empno%type ) IS -- 삭제데이터를확인하기레코드선언 TYPE del_record IS RECORD ( v_empno emp.empno%type, v_ename emp.ename%type, v_hiredate emp.hiredate%type) ; v_emp del_record ; DBMS_OUTPUT.ENABLE; -- 삭제된데이터확인용쿼리 SELECT empno, ename, hiredate INTO v_emp.v_empno, v_emp.v_ename, v_emp.v_hiredate FROM emp WHERE empno = p_empno ; DBMS_OUTPUT.PUT_LINE( ' 사원번호 : ' v_emp.v_empno ); DBMS_OUTPUT.PUT_LINE( ' 사원이름 : ' v_emp.v_ename ); DBMS_OUTPUT.PUT_LINE( ' 입사일 : ' v_emp.v_hiredate ); -- 삭제쿼리 DELETE FROM emp WHERE empno = p_empno ; DBMS_OUTPUT.PUT_LINE( ' 데이터삭제성공 ' ); END; / 프로시저가생성되었습니다. 프로시저실행 ( 결과화면 ) SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE Delete_Test(7900); 사원번호 : 7900 사원이름 : JAMES 입사일 : 81/12/03 데이터삭제성공 PL/SQL 처리가정상적으로완료되었습니다 사원을삭제했습니다. PL/SQL 22

23 FOR LOOP 문 - index 는자동선언되는 binary_integer 형변수이고. 1 씩증가합니다. - reverse 옵션이사용될경우 index 는 upper_bound 에서 lower_bound 로 1 씩감소합니다. - IN 다음에는 coursor 나 select 문이올수있습니다. FOR 문예제 DECLARE -- 사원이름을출력하기위한 PL/SQL 테이블선언 TYPE ename_table IS TABLE OF emp.ename%type INDEX BY BINARY_INTEGER; -- 사원급여를출력하기위한 PL/SQL 테이블선언 TYPE sal_table IS TABLE OF emp.sal%type INDEX BY BINARY_INTEGER; ename_tab sal_tab ename_table; sal_table; i BINARY_INTEGER := 0; DBMS_OUTPUT.ENABLE; FOR emp_list IN (SELECT ename, sal FROM emp WHERE deptno = 10) LOOP i := i +1 ; ename_tab(i) := emp_list.ename; -- 테이블에사원이름을저장 sal_tab(i) := emp_list.sal; -- 테이블에사원급여를저장 END LOOP; FOR cnt IN 1..i LOOP -- 화면에출력 DBMS_OUTPUT.PUT_LINE(' 사원이름 : ' ename_tab(cnt)); DBMS_OUTPUT.PUT_LINE(' 사원급여 : ' sal_tab(cnt)); END LOOP; END; / 사원이름 : CLARK 사원급여 : 2450 PL/SQL 23

24 사원이름 : KING 사원급여 : 5000 사원이름 : MILLER 사원급여 : 1300 PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 24

25 Loop 문 while 문 LOOP 문예제 EXIT 문이사용되었을경우, 무조건 LOOP 문을빠져나갑니다, EXITH WHEN 이사용될경우 WHEN 절에 LOOP 를빠져나가는조건을제어할수있습니다. SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> DECLARE / v_cnt number(3) := 100; DBMS_OUTPUT.ENABLE ; LOOP INSERT INTO emp(empno, ename, hiredate) VALUES(v_cnt, 'test' to_char(v_cnt), sysdate); v_cnt := v_cnt+1; EXIT WHEN v_cnt > 110; END LOOP; DBMS_OUTPUT.PUT_LINE(v_cnt-100 ' 개의데이터가입력되었습니다 '); END; 11 개의데이터가입력되었습니다 PL/SQL 처리가정상적으로완료되었습니다. WHILE LOOP 문 WHILE LOOP 문은 FOR 문과비슷하며조건이 TRUE 일경우만반복되는 LOOP 문입니다. 예제 WHILE cnt < 10 LOOP INSERT INTO emp(empno, ename, hiredate) VALUES(emp_seq.nextval, 'test', sysdate); cnt := cnt + 1 ; END LOOP ; cnt 가 10 이면반복 While Loop 를탈출 EXIT WHEN 조건 => 조건이만족할때반복 loop 를탈출합니다.. PL/SQL 25

26 조건제어 IF 문예제프로시저.. SQL>CREATE OR REPLACE PROCEDURE Dept_Search (p_empno IN emp.empno%type ) IS v_deptno emp.deptno%type ; DBMS_OUTPUT.ENABLE; SELECT deptno INTO v_deptno FROM emp WHERE empno = p_empno ; IF v_deptno <= 7000 THEN DBMS_OUTPUT.PUT_LINE( ' ACCOUNTING 부서사원입니다. ' ); ELSIF v_deptno < 7900 THEN ELSE DBMS_OUTPUT.PUT_LINE( ' RESEARCH 부서사원입니다. ' ); DBMS_OUTPUT.PUT_LINE( ' 부서가없네요... ' ); END IF ; END ; / 프로시저가생성되었습니다. 프로시저실행 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE Dept_Search(7900); 부서가없네요... PL/SQL 처리가정상적으로완료되었습니다. SQL> EXECUTE Dept_Search(7369); RESEARCH 부서사원입니다. PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 26

27 암시적인커서는오라클이나 PL/SQL 실행메커니즘에의해처리되는 SQL 문장이처리되는곳에대한익명의에드레스입니다. 오라클데이터베이스에서실행되는모든 SQL 문장은암시적인커서이며그것들과함께모든암시적인커서속성이사용될수있습니다. - 암시적커서의속성 SQL%ROWCOUNT : 해당 SQL 문에영향을받는행의수 SQL%FOUND : 해당 SQL 영향을받는행의수가 1 개이상일경우 TRUE SQL%NOTFOUND : 해당 SQL 문에영향을받는행의수가없을경우 TRUE SQL%ISOPEN : 항상 FALSE, 암시적커서가열려있는지의여부검색 ( 암시적커서는 SQL 문이실행되는순간자동으로열림과닫힘실행 ) 암시적커서예제 CREATE OR REPLACE PROCEDURE Implicit_Cursor (p_empno emp.empno%type) is v_sal emp.sal%type; v_update_row NUMBER; SELECT sal INTO v_sal FROM emp WHERE empno = p_empno ; -- 검색된데이터가있을경우 IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(' 검색한데이터가존재합니다 : ' v_sal); END IF; UPDATE emp SET sal = sal*1.1 WHERE empno = p_empno; -- 수정한데이터의카운트를변수에저장 v_update_row := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE(' 급여가인상된사원수 : ' v_update_row); END; 프로시저가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE Implicit_Cursor(7369); 검색한데이터가존재합니다 : 880 급여가인상된사원수 : 1 PL/SQL 처리가정상적으로완료되었습니다 PL/SQL 27

28 커서란무엇인가? 명시적커서 (Explicit Cursor) 커서는 Private SQL 의작업영역입니다. 오라클서버에의해실행되는모든 SQL 문은연관된각각의커서를소유하고있습니다. 커서의종류 - 암시적커서 : 모든 DML 과 PL/SQL SELECT 문에대해선언됩니다. - 명시적커서 : 프로그래머에의해선언되며이름이있는커서입니다. Explicit Cursor 의흐름도? 문법 (Syntax) 커서열기 (OPEN) 커서의열기는 OPEN 문을사용합니다. 커서안의검색이실행되며아무런데이터행을추출하지못해도에러가발생하지않습니다. OPEN cursor_name; 커서패치 (FETCH) 커서의 FETCH 는현재데이터행을 OUTPUT 변수에반환합니다. 커서의 SELECT 문의컬럼의수와 OUTPUT 변수의수가동일해야합니다. 커서컬럼의변수의타입과 OUTPUT 변수의데이터타입도동일해야합니다. 커서는한라인씩데이터를패치합니다. FETCH cursor_name INTO variable1, variable2 ; 커서닫기 (CLOSE) 사용을마친커서는반드시닫아주어야합니다. 필요하다면커서를다시열수있습니다. 커서를닫은상태에서 FETCH 를할수없습니다. CLOSE cursor_name; PL/SQL 28

29 Explicit Cursor 예제특정부서의평균급여와사원수를출력.. SQL>CREATE OR REPLACE PROCEDURE ExpCursor_Test (v_deptno dept.deptno%type) IS CURSOR dept_avg IS SELECT b.dname, COUNT(a.empno) cnt, ROUND(AVG(a.sal),3) salary FROM emp a, dept b WHERE a.deptno = b.deptno AND b.deptno = v_deptno GROUP BY b.dname ; -- 커서를패치하기위한변수선언 v_dname dept.dname%type; emp_cnt sal_avg NUMBER; NUMBER; -- 커서의오픈 OPEN dept_avg; -- 커서의패치 FETCH dept_avg INTO v_dname, emp_cnt, sal_avg; DBMS_OUTPUT.PUT_LINE(' 부서명 : ' v_dname); DBMS_OUTPUT.PUT_LINE(' 사원수 : ' emp_cnt); DBMS_OUTPUT.PUT_LINE(' 평균급여 : ' sal_avg); -- 커서의 CLOSE CLOSE dept_avg; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM ' 에러발생 '); END; / PL/SQL 29

30 프로시져가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE ExpCursor_Test(30); 부서명 : SALES 사원수 : 6 평균급여 : PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 30

31 FOR 문에서커서사용 (Cursor FOR Loops) FOR 문을사용하면커서의 OPEN, FETCH, CLOSE 가자동발생하므로따로기술할필요가없습니다 레코드이름도자동선언되므로따로선언할필요가없습니다. FOR 문에서커서사용예제부서별사원수와급여합계를구하는프로시저입니다. SQL> CREATE OR REPLACE PROCEDURE ForCursor_Test IS CURSOR dept_sum IS SELECT b.dname, COUNT(a.empno) cnt, SUM(a.sal) salary FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY b.dname; -- Cursor 를 FOR 문에서실행시킨다 FOR emp_list IN dept_sum LOOP DBMS_OUTPUT.PUT_LINE(' 부서명 : ' emp_list.dname); DBMS_OUTPUT.PUT_LINE(' 사원수 : ' emp_list.cnt); DBMS_OUTPUT.PUT_LINE(' 급여합계 : ' emp_list.salary); END LOOP; EXCEPTION WHEN OTHERS THEN END; DBMS_OUTPUT.PUT_LINE(SQLERRM ' 에러발생 '); / 프로시져가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) PL/SQL 31

32 SQL> EXECUTE ForCursor_Test; 부서명 : ACCOUNTING 사원수 : 3 급여합계 : 8750 부서명 : RESEARCH 사원수 : 6 급여합계 : 부서명 : SALES 사원수 : 6 급여합계 : 9305 PL/SQL 처리가정상적으로완료되었습니다. 명시적커서의속성 (Explicit Cursor Attributes) %ISOPEN - 커서가 OPEN 되어있으면 TRUE - %ISOPEN 속성을이용하여커서가열려있는지알수있습니다. %NOTFOUND - 패치한데이터가행을반환하지않으면 TRUE - %NOTFOUND 속성을이용하여루프를종료할시점을찾습니다. %FOUND - 패치한데이터가행을반환하면 TRUE %ROWCOUNT - 현재까지반환된모든데이터행의수 - %ROWCOUNT 속성을이용하여정확한숫자만큼의행을추출합니다. 커서의속성예제 SQL>CREATE OR REPLACE PROCEDURE AttrCursor_Test IS v_empno emp.empno%type; PL/SQL 32

33 v_ename v_sal emp.ename%type; emp.sal%type; / CURSOR emp_list IS SELECT empno, ename, sal FROM emp; DBMS_OUTPUT.ENABLE; OPEN emp_list; LOOP FETCH emp_list INTO v_empno, v_ename, v_sal; -- 데이터를찾지못하면빠져나갑니다 EXIT WHEN emp_list%notfound; END LOOP; DBMS_OUTPUT.PUT_LINE(' 전체데이터수 ' emp_list%rowcount); CLOSE emp_list; EXCEPTION WHEN OTHERS THEN END; DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' SQLERRM); 프로시져가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE AttrCursor_Test; 전체데이터수 15 PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 33

34 파라미터가있는커서 (Cursors with Parameters) 커서가열리고질의가실행되면매개변수값을커서에전달한다. 다른 active set 을원할때마다 explicit 커서를따로선언해야한다 문법 (Syntax) 파라미터가있는커서예제 SQL> CREATE OR REPLACE PROCEDURE ParamCursor_Test (param_deptno emp.deptno%type) IS v_ename emp.ename%type; -- Parameter 가있는커서의선언 CURSOR emp_list(v_deptno emp.deptno%type) IS SELECT ename FROM emp WHERE deptno = v_deptno; DBMS_OUTPUT.ENABLE; DBMS_OUTPUT.PUT_LINE(' ****** 입력한부서에해당하는사람들 ****** '); -- Parameter 변수의값을전달 (OPEN 될때값을전달한다 ) FOR emplst IN emp_list(param_deptno) LOOP DBMS_OUTPUT.PUT_LINE(' 이름 : ' emplst.ename); END LOOP; EXCEPTION WHEN OTHERS THEN END; DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' SQLERRM); / 프로시져가생성되었습니다. 실행결과 PL/SQL 34

35 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE ParamCursor_Test(10); ****** 입력한부서에해당하는사람들 ****** 이름 : CLARK 이름 : KING 이름 : MILLER PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 35

36 WHERE CURRENT OF - ROWID 를이용하지않고도현재참조하는행을갱신하고삭제할수있게합니다. - 추가적으로 FETCH 문에의해가장최근에처리된행을참조하기위해서 "WHERE CURRENT OF 커서이름 " 절로 DELETE 나 UPDATE 문작성이가능합니다.. - 이절을사용할때참조하는커서가있어야하며, FOR UPDATE 절이커서선언 query 문장안에있어야합니다. 그렇지않으면 error 가발생합니다.. WHERE CURRENT OF 예제 SQL> SET SERVEROUTPUT ON ; -- DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 SQL>CREATE OR REPLACE PROCEDURE where_current IS CURSOR emp_list IS SELECT empno FROM emp WHERE empno = 7934 FOR UPDATE; --DBMS_OUTPUT.PUT_LINE 명령을사용하기위해서 DBMS_OUTPUT.ENABLE; FOR emplst IN emp_list LOOP --emp_list 커서에해당하는사람의직업을 SALESMAN 으로업데이트시킵니다. UPDATE emp SET job = 'SALESMAN' WHERE CURRENT OF emp_list; DBMS_OUTPUT.PUT_LINE(' 수정성공 '); END LOOP; EXCEPTION WHEN OTHERS THEN -- 에러발생시에러메시지출력 DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' SQLERRM); END; PL/SQL 36

37 -- 먼저데이터를확인해보세용 SQL> SELECT job FROM emp WHERE empno = 7934; JOB CLERK --PLSQL 을실행시키고.. SQL> EXECUTE where_current; 수정성공 --DBMS_OUTPUT.PUT_LINE 명령으로출력한거.. PL/SQL 처리가정상적으로완료되었습니다. -- 다시데이터를확인하면변경된것을볼수있습니다. SQL> SELECT job FROM emp WHERE empno = 7934; JOB SALESMAN PL/SQL 37

38 예외 (Exception) 란? 오라클 PL/SQL 의오류를예외라고부릅니다. 오류는 PL/SQL 을컴파일할때문법적인오류로발생하는컴파일타임오류와, 프로그램을실행할때발생하는실행타임오류로구분할수있습니다. PL/SQL 오류의종류 예외설명처리 미리정의된오라클서버오류 (Predefined Oracle Server) 미리정의되지않은오라클서버오류 (Non-Predefined Oracle Server) 사용자정의오류 (User- Defined) PL/SQL 에서자주발생하는약 20 개의오류 미리정의된오라클서버오류를제외한모든오류 개발자가정한조건에만족하지않을경우발생하는오류 선언할필요도없고, 발생시에예외절로자동트랩 (Trap) 된다. 선언부에서선언해야하고발생시자동트랩된다. 선언부에서선언하고실행부에서 RAISE 문을사용하여발생시켜야한다 Execption 문법 (Syntax) WHEN OTHERS 절은맨마지막에옵니다. 예외처리절은 EXCEPTION 부터시작합니다. 허용합니다. 예외가발생하면여러개의예외처리부중에하나의예외처리부에트랩 (Trap) 됩니다. PL/SQL 38

39 미리정의된예외 (Predefined Exceptions) 오라클 PL/SQL 은자주일어나는몇가지예외를미리정의해놓았으며, 이러한예외는개발자가따로선언할필요가없습니다. 미리정의된예외의종류? NO_DATA_FOUND : SELECT 문이아무런데이터행을반환하지못할때 TOO_MANY_ROWS : 하나만리턴해야하는 SELECT 문이하나이상의행을반환할때 INVALID_CURSOR : 잘못된커서연산 ZERO_DIVIDE : 0 으로나눌때 DUP_VAL_ON_INDEX : UNIQUE 제약을갖는컬럼에중복되는데이터가 INSERT 될때이외에도몇개가더있습니다. 미리정의된예외예제 SQL> CREATE OR REPLACE PROCEDURE PreException_test IS (v_deptno IN emp.empno%type) v_emp emp%rowtype; DBMS_OUTPUT.ENABLE; SELECT empno, ename, deptno INTO v_emp.empno, v_emp.ename, v_emp.deptno FROM emp WHERE deptno = v_deptno ; DBMS_OUTPUT.PUT_LINE(' 사번 : ' v_emp.empno); DBMS_OUTPUT.PUT_LINE(' 이름 : ' v_emp.ename); DBMS_OUTPUT.PUT_LINE(' 부서번호 : ' v_emp.deptno); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE(' 데이터가존재합니다.'); DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러발생 '); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS 에러발생 '); WHEN NO_DATA_FOUND THEN PL/SQL 39

40 DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND 에러발생 '); WHEN OTHERS THEN END; DBMS_OUTPUT.PUT_LINE(' 기타에러발생 '); / 프로시저가생성되었습니다. 프로시저실행 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE PreException_Test(20); TOO_MANY_ROWS 에러발생 PL/SQL 처리가정상적으로완료되었습니다. TOO_MANY_ROWS 에러를타는이유? - SELECT 문의결과가 1 개이상의행을리턴하기때문이다.. - TOO_MANY_ROWS 를피하기위해서는 FOR 문이나 LOOP 문으로 SELECT 문을처리해야합니다. 아래와같이바꾸면에러가발생하지않습니다. FOR emp_list IN (SELECT empno, ename, deptno FROM emp WHERE deptno = v_deptno) LOOP DBMS_OUTPUT.PUT_LINE(' 사번 : ' emp_list.empno); DBMS_OUTPUT.PUT_LINE(' 이름 : ' emp_list.ename); DBMS_OUTPUT.PUT_LINE(' 부서번호 : ' emp_list.deptno); END LOOP; PL/SQL 40

41 미리정의되지않은예외 (Non-Predefiend Exceptions) STEP 1 : 예외의이름을선언 ( 선언절 ) STEP 2 : PRAGMA EXCEPTION_INIT 문장으로예외의이름과오라클서버오류번호를결합 ( 선언절 ) STEP 3 : 예외가발생할경우해당예외를참조한다 ( 예외절 ) 미리정의되지않은예외예제 SQL> CREATE OR REPLACE PROCEDURE NonPreException_Test IS not_null_test EXCEPTION; -- STEP 1 /* not_null_test 는선언된예외이름 Error 처리번호는표준 Oracle7 Server Error 번호 */ PRAGMA EXCEPTION_INIT(not_null_test, -1400); -- STEP 2 DBMS_OUTPUT.ENABLE; -- empno 를입력하지않아서 NOT NULL 에러발생 INSERT INTO emp(ename, deptno) VALUES('tiger', 30); EXCEPTION WHEN not_null_test THEN -- STEP 3 DBMS_OUTPUT.PUT_LINE('not null 에러발생 '); END; / PL/SQL 41

42 프로시져가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE NonPreException_Test; not null 에러발생 PL/SQL 처리가정상적으로완료되었습니다. PL/SQL 42

43 사용자정의예외 (User-Defined Exceptions) 오라클저장함수 RAISE_APPLICATION_ERROR 를사용하여오류코드 부터 의범위내에서사용자정의예외를만들수있습니다. STEP 1 : 예외의이름을선언 ( 선언절 ) STEP 2 : RAISE 문을사용하여직접적으로예외를발생시킨다 ( 실행절 ) STEP 3 : 예외가발생할경우해당예외를참조한다 ( 예외절 ) 사용자정의예외예제 Procedure 입력한부서의사원이 5 명보다적으면사용자정의예외가발생하는예제입니다. SQL>CREATE OR REPLACE PROCEDURE User_Exception (v_deptno IN emp.deptno%type ) IS -- 예외의이름을선언 user_define_error EXCEPTION; -- STEP 1 cnt NUMBER; DBMS_OUTPUT.ENABLE; SELECT COUNT(empno) INTO cnt FROM emp WHERE deptno = v_deptno; IF cnt < 5 THEN -- RAISE 문을사용하여직접적으로예외를발생시킨다 RAISE user_define_error; -- STEP 2 END IF; EXCEPTION -- 예외가발생할경우해당예외를참조한다. WHEN user_define_error THEN -- STEP 3 PL/SQL 43

44 RAISE_APPLICATION_ERROR(-20001, ' 부서에사원이몇명안되네요..'); / END; 프로시져가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE user_exception(10); user_exception(10); END; * 1 행에오류 : ORA-20001: 부서에사원이몇명안되네요.. ORA-06512: "SCOTT.USER_EXCEPTION", 줄 17 에서 ORA-06512: 줄 1 에서 10 부서의사원이 5 보다적기때문에사용자정의예외가발생했습니다. SQL> EXECUTE user_exception(20); PL/SQL 처리가정상적으로완료되었습니다. 20 부서로실행을하면에러가발생하지않는것을알수있습니다.. PL/SQL 44

45 SQLCODE, SQLERRM WHEN OTHERS 문으로트랩 (Trap) 되는오류들의실제오류코드와설명을볼때사용한다. SQLCODE : 실행된프로그램이성공적으로종료하였을때는오류번호 0 을포함하며, 그렇지못할경우에는해당오류코드번호를포함한다. SQLERRM : SQLCODE 에포함된오라클오류번호에해당하는메시지를가진다. SQLCODE Value Description 0 오류없이성공적으로종료 1 사용자정의예외번호 +100 NO_DATA_FOUND 예외번호음수위에것을제외한오라클서버에러번호 SQLCODE, SQLERRM 예제프로시저 SQL> CREATE OR REPLACE PROCEDURE Errcode_Exception (v_deptno IN emp.deptno%type ) IS v_emp emp%rowtype ; DBMS_OUTPUT.ENABLE; -- ERROR 발생 for 문을돌려야됨 SELECT * INTO v_emp FROM emp WHERE deptno = v_deptno; DBMS_OUTPUT.PUT_LINE(' 사번 : ' v_emp.empno); DBMS_OUTPUT.PUT_LINE(' 이름 : ' v_emp.ename); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERR CODE : ' TO_CHAR(SQLCODE)); DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' SQLERRM); PL/SQL 45

46 END; / 프로시져가생성되었습니다. 실행결과 SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) SQL> EXECUTE Errcode_Exception(30); RR CODE : ERR MESSAGE : ORA-01422: 실제인출은요구된것보다많은수의행을추출합니다 PL/SQL 처리가정상적으로완료되었습니다. 위와같이 SQLCODE, SQLERRM 을사용하면에러코드와에러메시지를볼수있습니다. PL/SQL 46

47 package? 패키지 (package) 는오라클데이터베이스에저장되어있는서로관련있는 PL/SQL 프로지져와함수들의집합입니다 패키지는선언부와본문두부분으로나누어집니다. 패키지선언부 - 선언절은패키지에포함될 PL/SQL 프로시저나, 함수, 커서, 변수, 예외절을선언합니다. - 패키지선언부에서선언한모든요소들은패키지전체에적용됩니다. - 즉선언부에서선언한변수는 PUBLIC 변수로사용됩니다. 패키지본문 - 패키지본문은패키지에서선언된부분의실행을정의합니다. - 즉실제프로시져나함수의내용에해당하는부분이옵니다. 아주간단한패키지예제입니다. 4 개의프로시저가존재하고있습니다. 프로시저명프로시저기능보기 all_emp_info 모든사원의사원정보 ( 사번, 성명, 입사일 ) 프로시저보기 all_sal_info 모든사원의급여정보 ( 평균급여, 최고급여, 최소급여 ) 프로시저보기 PL/SQL 47

48 dept_emp_info 특정부서의사원정보 ( 사번, 성명, 입사일 ) 프로시저보기 dept_sql_info 특정부서의급여정보 ( 평균급여, 최고급여, 최소급여 ) 프로시저보기 위 4 개의프로시저를가지고패키지를생성하겠습니다. 선언부를먼저생성합니다. package 예제 ( 선언부 ) SQL>CREATE OR REPLACE PACKAGE emp_info AS PROCEDURE all_emp_info; PROCEDURE all_sal_info; PROCEDURE dept_emp_info (v_deptno IN NUMBER) ; PROCEDURE dept_sal_info (v_deptno IN NUMBER) ; END emp_info; Package created. -- 모든사원의사원정보 -- 모든사원의급여정보 -- 특정부서의사원정보 -- 특정부서의급여정보 선언부를생성하고나서본문부분을생성합니다. package 예제 ( 본문 ) SQL>CREATE OR REPLACE PACKAGE BODY emp_info AS -- 모든사원의사원정보 PROCEDURE all_emp_info IS CURSOR emp_cursor IS SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate FROM emp ORDER BY hiredate; FOR aa IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(' 사번 : ' aa.empno); DBMS_OUTPUT.PUT_LINE(' 성명 : ' aa.ename); DBMS_OUTPUT.PUT_LINE(' 입사일 : ' aa.hiredate); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM ' 에러발생 '); END all_emp_info; -- 모든사원의급여정보 PROCEDURE all_sal_info PL/SQL 48

49 IS CURSOR emp_cursor IS SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal FROM emp; FOR aa IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(' 전체급여평균 : ' aa.avg_sal); DBMS_OUTPUT.PUT_LINE(' 최대급여금액 : ' aa.max_sal); DBMS_OUTPUT.PUT_LINE(' 최소급여금액 : ' aa.min_sal); END LOOP; EXCEPTION WHEN OTHERS THEN END all_sal_info; DBMS_OUTPUT.PUT_LINE(SQLERRM ' 에러발생 '); -- 특정부서의사원정보 PROCEDURE dept_emp_info (v_deptno IN NUMBER) IS CURSOR emp_cursor IS SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate FROM emp WHERE deptno = v_deptno ORDER BY hiredate; FOR aa IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(' 사번 : ' aa.empno); DBMS_OUTPUT.PUT_LINE(' 성명 : ' aa.ename); DBMS_OUTPUT.PUT_LINE(' 입사일 : ' aa.hiredate); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM ' 에러발생 '); END dept_emp_info; -- 특정부서의급여정보 PROCEDURE dept_sal_info (v_deptno IN NUMBER) IS CURSOR emp_cursor IS PL/SQL 49

50 SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal FROM emp WHERE deptno = v_deptno; FOR aa IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(' 전체급여평균 : ' aa.avg_sal); DBMS_OUTPUT.PUT_LINE(' 최대급여금액 : ' aa.max_sal); DBMS_OUTPUT.PUT_LINE(' 최소급여금액 : ' aa.min_sal); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM ' 에러발생 '); END dept_sal_info; END emp_info; / Package body created. 패키지의실행패키지의실행은패키지명다음에. 을찍고프로시저냐함수명을써줍니다. 먼저 set serveroutput on 을실행한후.. SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) 다음명령들을실행해보세요.. SQL> exec emp_info.all_emp_info; SQL> exec emp_info.all_sal_info; SQL> exec emp_info.dept_emp_info(10); SQL> exec emp_info.dept_sal_info(10); PL/SQL 50

51 트리거란? INSERT, UPDATE, DELETE 문이 TABLE 에대해행해질때묵시적으로수행되는 PROCEDURE 입니다. Trigger 는 TABLE 과는별도로 DATABASE 에저장됩니다. Trigger 는 VIEW 에대해서가아니라 TABLE 에관해서만정의될수있습니다. - BEFORE : INSERT, UPDATE, DELETE 문이실행되기전에트리거가실행됩니다. - AFTER : INSERT, UPDATE, DELETE 문이실행된후트리거가실행됩니다. - trigger_event : INSERT, UPDATE, DELETE 중에서한개이상올수있습니다. - FOR EACH ROW : 이옵션이있으면행트리거가됩니다. -- 행트리거 : 컬럼의각각의행의데이터행변화가생길때마다실행되며, 그데이터행의실제값을제어할수있습니다. -- 문장트리거 : 트리거사건에의해단한번실행되며, 컬럼의각데이터행을제어할수없습니다. 간단한행트리거예제 SQL>CREATE OR REPLACE TRIGGER triger_test BEFORE UPDATE ON dept FOR EACH ROW DBMS_OUTPUT.PUT_LINE(' 변경전컬럼값 : ' : old.dname); DBMS_OUTPUT.PUT_LINE(' 변경후컬럼값 : ' : new.dname); END; / SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) -- UPDATE 문을실행시키면.. PL/SQL 51

52 SQL>UPDATE dept SET dname = ' 총무부 ' WHERE deptno = 트리거가자동실행되어결과가출력됩니당. 변경전컬럼값 : 인사과변경후컬럼값 : 총무부 1 행이갱신되었습니다. 간단한행트리거예제 2 (PLSQL BLOCK 이있는트리거 ) SQL>CREATE OR REPLACE trigger sum_trigger BEFORE INSERT OR UPDATE ON emp FOR EACH ROW DECLARE -- 변수를선언할때는 DECLARE 문을사용해야합니다 avg_sal NUMBER; SELECT ROUND(AVG(sal),3) INTO avg_sal FROM emp; DBMS_OUTPUT.PUT_LINE(' 급여평균 : ' avg_sal); END; / 트리거가생성되었습니다. SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을출력하기위해사용 ) -- INSERT 문을실행합니다.. SQL> INSERT INTO EMP(EMPNO, ENAME, JOB, HIREDATE, SAL) VALUES(1000, 'LION', 'SALES', SYSDATE, 5000); -- INSERT 문을실행되기전까지의급여평균이출력됩니다. 급여평균 : 개의행이만들어졌습니다. PL/SQL 52