ORACLE SUBPROGRAM INTRODUCTION PLSQL 은오라클에서제공하는프로그래밍언어이다. 이는데이터베이스언어인 SQL 과함께효과적으로데이터베이스에접근할수있는방법을제공하고있다. Procedural LanguageSQL 의약자에서볼수있듯이절차적인기능을기본적으로가지는프로그래밍언어이다. PLSQL 은기본적으로블록 (BLOCK) 구조를가지고있다. 블록의기본적인구성은선언부 (DECLARE), 실행부 (), 예외처리부 (EXCEPTION) 로구성되어있다. PLSQL 블록은그블록안에블록을포함할수있는데포함된블록을 Nested Block 이라한다. PLSQL 블록은데이터베이스에오브젝트로서저장될수있는데, 이를서브프로그램또는프로그램단위라부르며, 그유형은프로시저 (Procedure), 함수 (Function), 패키지 (Package), 트리거 (Trigger) 로저장될수있다. 이장에서는프로시저와함수에대해서만다루기로한다. 서브프로그램단위로절차적인처리를하게되면, 중앙집중적인유지관리를할수있고데이터보안또는무결성을강화할수있으며, 데이터베이스내에저장되어재번역없이도실행할수있으므로성능또한높일수있다. 모듈화개발이가능하여개발의용이함을갖게된다. SOURCE Complie PARSE RUN Code Code Compliation Error Run-time Error(Exception) Written by AngelaLEE 5-1 www.muhanedu.net
PROCEDURE 프로시저는특정처리를실행하는서브프로그램의한유형으로단독 (standalone) 으로실행되거나다른프로시저또는다른툴 (Oracle Developer ) 또는다른환경 (Pro*C ) 등에서호출되어실행될수있다. 오브젝트로서데이터베이스내에저장되며, 호출되어실행될때별도의컴파일없이생성된 p-code 로바로실행할수있다. 오브젝트로 DDL 문으로처리된다. 프로시저를생성하고자한다면 CREATE PROCEDURE 문, 삭제하고자한다면 DROP PROCEDURE 문을사용한다. 프로시저를다시컴파일하고자한다면 ALTER PROCEDURE 문을사용한다. 프로시저를생성하는문법은아래와같다. > SYNTAX: CREATE [OR REPLACE] PROCEDURE procedure_name [( parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, )] AS PLSQL Block ; OR REPLACE 생성하고자하는프로시저가기존에동일명으로존재할경우, 기존의내용을현재의내용으로수정하는옵션.( 만약기존에존재하지않는다면큰의미는없다.) procedure_name 생성하고자하는프로시저명으로스키마내에서는유일하게명명되어야한다. Parameter 프로시저를실행할때호출환경과프로시저간에값을주고받을때사용되는파라미터 ( 매개변수 ) 로모드에따라역할이다르게수행된다. 파라미터를선언할때는데이터형만을기술한다. Written by AngelaLEE 5-2 www.muhanedu.net
( 데이터의크기는기술하지않음에유의한다.) 프로시저를생성할때선언된매개변수를형식매개변수 (Formal Parameter) 라고하며, 프로시저를실행할때형식매개변수에실제값이나변수를할당 대응하는데이때이를실매개변수 (Actual Parameter) 라고한다. 매개변수를선언할때 DEFAULT 키워드를사용하여기본값을부여할수있다.( 즉, 디폴트파라미터를지정할수있다 ) Mode 모드는매개변수의역할을결정짓는것으로 3가지가있다. IN 모드는호출환경으로부터값을전달받는매개변수로설정하며, IN 모드로설정된매개변수는프로시저내에서는읽기전용지역변수처럼취급된다. 기본 (Default) 모드이므로생략이가능하다. OUT 모드는프로시저에서호출환경으로값을전달하는매개변수로설정하며, 이모드로설정된매개변수는프로시저내에서는읽을수없으며, 값을저장하기만하는지역변수처럼사용된다. 호출환경에서는이매개변수로부터값을전달받기위해환경변수가선언되어야한다. IN OUT 모드로설정된매개변수는호출환경과프로시저간에값을주고받는지역변수로사용된다. 읽기도쓰기도가능하다. Written by AngelaLEE 5-3 www.muhanedu.net
FUNCTION 함수 (FUNCTION) 는프로시저와마찬가지로특정처리를위한서브프로그램의한유형이며, 데이터베이스내에오브젝트로서저장된다. 하지만함수는함수의이름으로반드시하나의값을리턴해야한다. 즉함수는실행이종료되면리턴데이터유형에맞는값으로대치되기때문에, 호출하고자한다면반드시수식의일부로서실행된다. 다른프로시저 함수또는다른툴 (Oracle Developer ) 또는다른환경 (Pro*C ) 등에서호출되어실행될수있다. 생성된함수 (User-defined Function) 는오라클단일행함수처럼 SQL 문과함께사용된다. 그러나, 만약 DML 문을가지는함수가동일한테이블에대한 UPDATEDELETE 문에포함되어처리될때, 상호참조 (Mutating Error) 가발생되므로제대로실행되지못하게된다. 또한 SQL 문에포함된함수는트랜잭션을종료시키는명령을포함하지못한다. 오브젝트로서 DDL 문으로처리된다. 함수를생성하고자한다면 CREATE FUNCTION 문, 삭제하고자한다면 DROP FUNCTION 문을사용한다. 함수를다시컴파일하고자한다면 ALTER PROCEDURE 문을사용한다. 함수를생성하는문법은아래와같다. > SYNTAX: CREATE [OR REPLACE] FUNCTION function_name [( parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, )] RETURN datatype AS PLSQL Block ; OR REPLACE 생성하고자하는함수가기존에동일명으로존 Written by AngelaLEE 5-4 www.muhanedu.net
재할경우, 기존의내용을현재의내용으로수정하는옵션.( 만약기존에존재하지않는다면큰의미는없다.) function_name 생성하고자하는함수명으로스키마내에서는유일해야한다. Parameter 함수를실행할때호출환경과함수간에값을주고받을때사용되는파라미터 ( 매개변수 ) 로모드에따라역할이다르게수행된다. ( 프로시저의파라미터와동일한의미이므로앞부분을참조하기바란다.) 매개변수를선언할때 DEFAULT 키워드를사용하여기본값을부여할수있다.( 즉, 디폴트파라미터를지정할수있다 ) Mode 모드는매개변수의역할을결정짓는것으로 3가지가있다. ( 자세한내용은앞부분프로시저의 mode 를참조하기바란다.) 한가지주지해야할사항은 ( 함수정의를확실히이해했다면 ) 함수의매개변수를사용할때, OUT 또는 IN OUT 모드는지양하는것이바람직하다는것이다. RETURN datatype 함수명으로리턴할데이터 ( 값 ) 의형을선언하는절. PLSQL 블록내에서는반드시 RETURN 문을포함해야하며, 그때리턴되는데이터는이부분에서선언한데이터형과일치되어야한다. Written by AngelaLEE 5-5 www.muhanedu.net
실습 ORACLE SUBPROGRAM 0. 사전준비사항 아래의실습예제들은각각스크립트파일에저장한다. ( 예를들면, 5 장의 1 번예제라면, p05_01.sql 로저장한다.) 저장된스크립트화일을실행하기위해서는 start 또는 @ 를사용한다. 만약 compile(parse) 후에러가있었다면, show errors 명령으로확인하여적절한조치를취하도록한다. 해당에러부분을수정한후에는다시스크립트를실행하여결과를확인한다. 1. [PROCEDURE I ] 사번을입력받아그사원의이름과급여를출력하는 EMP_INFO_01 프로시저. CREATE OR REPLACE PROCEDURE EMP_INFO_01 (P_ENO EMP.EMPNO%TYPE) V_ENAME EMP.ENAME%TYPE ; V_SAL EMP.SAL%TYPE ; SELECT INTO FROM ENAME, SAL V_ENAME, V_SAL EMP WHERE EMPNO = P_ENO ; DBMS_OUTPUT.PUT_LINE ( 사원의이름은 V_ENAME ) ; DBMS_OUTPUT.PUT_LINE ( 사원의급여는 V_SAL ) ; END ; 위의 CREATE PROCEDURE 문장은프로시저를생성한다. 생성된프로시저를 SQL*Plus(iSQL*Plus) 에서실행하기위해서는 EXECUTE 문을사용한다. SQL> EXECUTE EMP_INFO_01(7654) 참고적으로실행전에 SQL> SET SERVEROUTPUT ON 을실행한다. Written by AngelaLEE 5-6 www.muhanedu.net
2. [PROCEDURE II ] 1 번 EMP_INFO 프로시저를사원의이름과급여를화면출력하지않고매개변수를통해실행환경으로전달할수있도록수정한 EMP_INFO_02 프로시저. CREATE OR REPLACE PROCEDURE EMP_INFO_02 (P_ENO IN EMP.EMPNO%TYPE, P_ENAME OUT EMP.ENAME%TYPE, P_SAL OUT EMP.SAL%TYPE ) SELECT INTO FROM ENAME, SAL P_ENAME, P_SAL EMP WHERE EMPNO = P_ENO ; END ; 생성된프로시저를 SQL*Plus(iSQL*Plus) 에서실행 확인하는스크립트의내용 ( 별도로저장한다. 예를들면,p0502_run.sql ) VARIABLE G_ENAME VARCHAR2(30) VARIABLE G_SAL NUMBER EXEC EMP_INFO_02(7654, :G_ENAME, :G_SAL) PRINT G_ENAME PRINT G_SAL 참고적으로실행전에 SQL> SET SERVEROUTPUT ON 을실행한다. Written by AngelaLEE 5-7 www.muhanedu.net
3. [PROCEDURE III ] 전화번호문자열을받아들여포맷출력하는 PRN_TEL 프로시저. CREATE OR REPLACE PROCEDURE PRN_TEL (P_TEL_NO IN OUT VARCHAR2) P_TEL_NO := ( SUBSTR(P_TEL_NO,1,INSTR(P_TEL_NO,,1,1) 1 ) END ; ) SUBSTR(P_TEL_NO, INSTR(P_TEL_NO,,1,1)+1, INSTR(P_TEL_NO,,1,2)-1) - SUBSTR(P_TEL_NO, INSTR(P_TEL_NO,,1,2)+1) ; 생성된프로시저를 SQL*Plus(iSQL*Plus) 에서실행 확인하는스크립트의내용 ( 별도로저장한다. 예를들면,p0503_run.sql ) VARIABLE G_TEL_NO VARCHAR2(30) EXECUTE :G_TEL_NO := 012 345 6789 PRINT G_TEL_NO EXEC PRN_TEL(:G_TEL_NO) PRINT G_TEL_NO 결과가 012 345 6789 (012)345-6789 로출력되는가? ( 만약, 아니라면어떻게수정하나요?) Written by AngelaLEE 5-8 www.muhanedu.net
4. [PROCEDURE IV ] 디폴트형식파라미터를가지는 DFT_PARAM 프로시저. CREATE OR REPLACE PROCEDURE DFT_PARAM (P_DNAME IN DEPT.DNAME%TYPE DEFAULT Unknown, P_LOC IN DEPT.LOC%TYPE DEFAULT Unknown ) INSERT INTO DEPT VALEUS (SEQ_DNO.NEXTVAL, P_DNAME, P_LOC) ; COMMIT ; END ; 생성된프로시저를 SQL*Plus(iSQL*Plus) 에서실행 확인하는스크립트의내용. ( 별도로저장한다. 예를들면,p0504_run.sql ) DFT_PARAM ; DFT_PARAM( HAPPINESS, CIEL ) ; DFT_PARAM(P_LOC => SEOUL, P_DNAME => SMILE ) ; DFT_PARAM(P_LOC => PEACE ) ; END ; SELECT DEPTNO, DNAME, LOC FROM EMP ; SELECT COUNT(*) FROM EMP WHERE LOC = Unknown ; 문장의결과는? * 참고적으로 SEQ_DNO 는시퀀스이며, 없는경우에는 50 부터 10 씩증가하도록시퀀스를생성한후프로시저를실행한다. CREATE SEQUENCE SEQ_DNO INCREMENT BY 10 START WITH 50 MAXVALUE 99 ; Written by AngelaLEE 5-9 www.muhanedu.net
5. [PROCEDURE V ] 선언부에선언된지역프로시저를가지는 SAM_LOCAL 프로시저. CREATE OR REPLACE PROCEDURE sam_local (P_NO IN EMP.EMPNO%TYPE) PROCEDURE log_exec INSERT INTO log_table(user_id, log_date) VALUES (USER, SYSDATE) ; END log_exec ; DELETE EMP WHERE EMPNO = P_NO ; log_exec ; END sam_local ; 참고 : log_table 은두개의컬럼을갖는테이블임. log_exec 프로시저는 sam_local 내에선언된지역서브프로그램으로외부에서는호출할수없다. 지역서브프로시저는선언부에마지막에오도록한다. 다른프로시저에서 sam_local 프로시저를호출하든지또는 sam_local 내에서 log_exec 프로시저를호출하든지간에별도의명령없이프로시저명을기술하면된다.( 위의예에서는 log_exec ; ) Written by AngelaLEE 5-10 www.muhanedu.net
6. [FUNCTION I ] 해당부서의현재사원수를. 구하는 EMP_CNT 함수. CREATE OR REPLACE FUNCTION EMP_CNT (P_DNO IN DEPT.DEPTNO%TYPE) RETURN NUMBER V_CNT NUMBER ; SELECT COUNT(*) INTO V_CNT FROM EMP WHERE DEPTNO = P_DNO ; RETURN V_CNT ; END EMP_CNT ; 생성된함수를 SQL*Plus(iSQL*Plus) 에서실행 확인하는스크립트의내용. ( 별도로저장한다. 예를들면,p0506_run.sql ) VARIABLE G_CNT VARCHAR2(30) EXECUTE :G_CNT := EMP_CNT(20) PRINT G_CNT 생성된함수는오라클단일행함수와비슷하게사용된다. 예를들면, SELECT DEPTNO, DNAME, EMP_CNT(DEPTNO) FROM DEPT ; Written by AngelaLEE 5-11 www.muhanedu.net
7. [FUNCTION II ] 해당사원의급여와세율을입력받아세금을구하는 TAX 함수. CREATE OR REPLACE FUNCTION TAX (P_SAL IN EMP.SAL%TYPE, P_RATE IN NUMBER ) RETURN NUMBER RETURN ( P_SAL * P_RATE ) ; END EMP_CNT ; 생성된함수를 SQL*Plus(iSQL*Plus) 에서실행 확인하는스크립트의내용. ( 별도로저장한다. 예를들면,p0507_run.sql ) VARIABLE G_TAX NUMBER EXECUTE :G_TAX := TAX(4000, 0.05) PRINT G_CNT 생성된함수는오라클단일행함수와비슷하게사용된다. 예를들면, SELECT EMPNO, ENAME, SAL, TAX(SAL, 0.03) FROM EMP ; Written by AngelaLEE 5-12 www.muhanedu.net
8. [FUNCTION III ] 부서코드의유효여부를판단하는 VALID_DNO 함수. CREATE OR REPLACE FUNCTION VALID_DNO (P_DNO DEPT.DEPTNO%TYPE ) RETURN BOOLEAN V_CHAR CHAR(1) ; SELECT INTO FROM X V_CHAR DEPT WHERE DEPTNO = P_DNO ; RETURN ( TRUE ) ; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN ( FALSE ) ; END EMP_CNT ; 생성된함수를 SQL*Plus(iSQL*Plus) 에서실행 확인하는스크립트의내용. ( 별도로저장한다. 예를들면,p0508_run.sql ) SET SERVEROUTPUT ON IF VALID_DNO(50) THEN DBMS_OUTPUT.PUT_LINE( 존재하는부서 ) ; ELSE DBMS_OUTPUT.PUT_LINE( 존재하지않는부서 ) ; END IF ; END ; Written by AngelaLEE 5-13 www.muhanedu.net