Chapter 6 PL SQL(Procedural Language/SQL) - 9

PLT 6.9 SUBPROGRAM

PL/SQL을 지원하는 어떤 툴이나 언어에서도 SUBPROGRAM(프로시저와 함수)을 실행할 수 있다. PL/SQL내부에서 식의 일부로서 함수를 실행할 수 있다. EXECUTE는 명령 다음에 입력되는 Stored Procedure를 실행한다.

SUBPROGRAM의 개요

PL/SQL 프로시저와 함수는 3GL의 프로시저 및 함수와 매우 비슷하게 동작된다. 모듈화를 통해 관리가 용이하고 적절히 논리적 단위로 나누어진 프로그래밍을 할 수 있다. 즉, 잘 정의된 논리적인 단위로 코드를 분할할 수 있다. PL/SQL에서 이들 단위를 단위 프로그램 또는SUBPROGRAM이라 부른다. PL/SQL에는 프로시저와 함수라는 두 가지 유형의 SUBPROGRAM이 있다. SUBPROGRAM은 컴파일된 상태로 데이터베이스에 저장되어 있어 Performance가 향상된다.

SUBPROGRAM 작성 단계

구문 작성

TEXT 편집기를 이용하여 SCRIPT FILE에 CREATE PROCEDURE나 CREATE FUNCTION문을 작성한다.

SQL> ed emp_up CREATE OR REPLACE PROCEDURE emp_sal_update(                                   p_empno IN emp.empno%TYPE, p_sal IN emp.sal%TYPE)                 IS                 BEGIN                                   UPDATE emp                                                     SET sal = p_sal                                                     WHERE empno = p_empno;                                   IF SQL%NOTFOUND THEN                                                     DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_empno) ||                                                                      '는 없는 사원번호입니다.');                                   ELSE                                                     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) ||                                                                      '명의 자료를 수정하였습니다.');                                   END IF;                 END emp_sal_update; /

코드 컴파일

SCRIPT FILE을 실행 시켜 컴파일하여 컴파일된 코드를 데이터베이스에 저장한다.

SQL> @emp_up Procedure created.

에러 수정

코드 컴파일시 에러가 발생하면 에러를 확인하고 수정하여 코드를 다시 컴파일한다.

SQL> @emp_up Warning: Procedure created with compilation errors. SQL> ed emp_up                -- emp_up를 수정한 후 저장하고 종료한다. SQL> @emp_up Procedure created.

실행

SQL*Plus에서 EXECUTE명령으로 SUBPROGRAM을 실행한다.

SQL> EXECUTE emp_sal_update(7788,3500) PL/SQL procedure successfully completed. SQL> SELECT empno,ename,job,sal  2 FROM emp  3 WHERE empno = 7788;    EMPNO ENAME      JOB SAL --------- ---------- --------- ---------     7788 SCOTT      ANALYST 3500

PROCEDURE 생성

나중에 실행할 일련의 동작을 저장하기 위해 PL/SQL프로시저를 작성한다. 프로시저는 실행할 때 사용하는 Parameter가 없거나 여러 개를 가질 수도 있다. 프로시저에서는 DECLARE절이 생략되고 IS와 BEGIN사이에 필요한 변수를 선언하여 사용한다

CREATE  [OR  REPLACE]  PROCEDURE  procedure_name             [(argument1 [mode1]  datatype [{:= | DEFAULT} ｅxpression]             [,argument2 [mode2]  datatype [{:= | DEFAULT} ｅxpression], . . .])] {IS | AS} BEGIN             pl/sql_block; END; OR  REPLACE : procedure_name이 존재할 경우 PROCEDURE의 내용을 지우고 다시 생성 procedure_name : PROCEDURE 명 argument : 매개변수의 이름 mode : 3가지가 있다 - IN : 입력 매개변수로 상용 - OUT : 출력 매개변수로 사용 - IN OUT : 입력, 출력 매개변수로 상용 pl/sql_block : PROCEDURE를 구성하는 코드를 구성하는 PL/SQL의 블록

• SQL*Plus에서 프로시저를 작성할 때 CREATE OR REPLACE를 사용합니다.

• 어떠한 Parameter라도 사용 가능합니다.

• IS로 PL/SQL블록을 시작합니다.

• Local변수 선언은 IS와 BEGIN사이에 선언 합니다.

PROCEDURE 실행

PL/SQL을 지원하는 어떤 툴이나 언어에서도 프로시저를 실행할 수 있다. SQL*Plus에서 프로시저 호출은 Stored Procedure를 참조하는 PL/SQL 문을 실행하기 위해 EXECUTE 명령을 사용할 수 있다. EXECUTE는 명령 다음에 입력되는 Stored Procedure를 실행한다.

procedure_name[(argument1[,argument2, . . . .])]

SQL> EXECUTE emp_sal_update(7902,4000) PL/SQL procedure successfully completed.

CREATE OR REPLACE PROCEDURE emp_input(                  v_name IN      emp.ename %TYPE,                  v_job IN        emp.job %TYPE,                  v_mgr IN       emp.mgr %TYPE,                  v_sal IN         emp.sal %TYPE) IS                  v_comm        emp.comm%TYPE;                  v_deptno                          emp.deptno%TYPE;                  manager_error     EXCEPTION; BEGIN                  IF UPPER(v_job) NOT IN ('PRESIDENT','MANAGER','ANALYST',                                                                       'SALESMAN','CLERK') THEN                                   RAISE manager_error;                  ELSIF UPPER(v_job) = 'SALESMAN' THEN                                   v_comm := 0;                  ELSE                                   v_comm := NULL;                  END IF;                  SELECT deptno                                   INTO v_deptno                                   FROM emp                                   WHERE empno = v_mgr;                  INSERT INTO emp                                   VALUES (empno_sequence.NEXTVAL,v_name,UPPER(v_job),                                                     v_mgr,SYSDATE,v_sal,v_comm,v_deptno); EXCEPTION                  WHEN manager_error THEN                                   DBMS_OUTPUT.PUT_LINE('담당 업무가 잘못 입력되었습니다.');                  WHEN NO_DATA_FOUND THEN                                   DBMS_OUTPUT.PUT_LINE('입력한 MANAGER는 없습니다.');                  WHEN OTHERS THEN                                   DBMS_OUTPUT.PUT_LINE('기타 에러입니다.'); END; / SQL> SET SERVEROUTPUT on SQL> EXECUTE emp_input('YOONJB','MANAGER',7788,2500)

FUNCTION 생성

실행 환경에 반드시 하나의 값을 Return하기 위해 PL/SQL 함수를 사용한다. 함수 선언에서Datatype이 있는 RETURN 절을 추가하고 PL/SQL 블록에 적어도 한 개의 이상의 RETURN 문을 포함한다. PARAMETER에서 사용하는 IN,OUT,IN OUT는 PROCEDURE에서 사용한 것과 동일하게 사용 가능하나 대부분 IN을 사용한다.

CREATE  [OR  REPLACE]  FUNCTION  function_name             [(argument1 [mode1]  datatype [{:= | DEFAULT} ｅxpression]             [,argument2 [mode2]  datatype [{:= | DEFAULT} ｅxpression], . . .])] RETURN  data_type {IS | AS} BEGIN             pl/sql_block; END; OR  REPLACE : function_name이 존재할 경우 FUNCTION의 내용을 지우고 다시 생성 function_name : Function의 이름은 표준 Oracle 명명법에 따른 함수이름 argument : 매개변수의 이름 mode : 3가지가 있다 - IN : 입력 매개변수로 상용 - OUT : 출력 매개변수로 사용 - IN OUT : 입력, 출력 매개변수로 상용 data_type : 반환되는 값의 datatype pl/sql_block : FUNCTION를 구성하는 코드를 구성하는 PL/SQL의 블록

RETURN 문

• PL/SQL 블록에는 RETURN문이 있어야 한다.

• 함수는 RETURN 절에 지정된 것과 동일한 datatype으로 RETURN 값을 설정해야 한다.

• 다중 RETURN 문은 사용할 수 있지만 한 번의 호출로는 한 개의 RETURN 문만 실행된다.

• 일반적으로 다중 RETURN 문은 IF 문에서 사용한다.

FUNCTION 실행

PL/SQL을 지원하는 어떤 툴이나 언어에서도 함수를 실행할 수 있고 PL/SQL내부에서 식의 일부로서 함수를 실행할 수 있다. SQL*Plus에서 FUNCTION 호출은 Stored Function를 참조하는PL/SQL 문을 실행하기 위해 EXECUTE 명령을 사용할 수 있다. EXECUTE는 명령 다음에 입력되는 Stored Function를 실행한다.

output_variable := function_name[(argument1[,argument2, . . . . .])]

SQL> EXECUTE :g_deptno := ename_deptno('ALLEN') PL/SQL procedure successfully completed.

CREATE OR REPLACE FUNCTION ename_deptno(                  v_ename IN     emp.ename%TYPE) RETURN NUMBER IS                  v_deptno        emp.deptno%TYPE; BEGIN                  SELECT deptno                                   INTO v_deptno                                   FROM emp                                   WHERE ename = UPPER(v_ename);                  DBMS_OUTPUT.PUT_LINE('부서번호 : ' || TO_CHAR(v_deptno));                  RETURN v_deptno; EXCEPTION                  WHEN NO_DATA_FOUND THEN                                   DBMS_OUTPUT.PUT_LINE('입력한 MANAGER는 없습니다.');                  WHEN TOO_MANY_ROWS THEN                                   DBMS_OUTPUT.PUT_LINE('자료가 2건 이상입니다.');                  WHEN OTHERS THEN                                    DBMS_OUTPUT.PUT_LINE('기타 에러입니다.'); END; / SQL> SET SERVEROUTPUT on SQL> VAR g_deptno NUMBER SQL> EXECUTE :g_deptno := ename_deptno('SCOTT') 부서번호 : 10 PL/SQL procedure successfully completed. SQL> PRINT g_deptno G_DEPTNO ---------       10

함수와 프로시저 비교

프로시저	함수
PL/SQL 문으로서 실행	식의 일부로서 사용
RETURN Datatype이 없음	RETURN Datatype이 필수
값을 Return할 수 있음	값을 Return하는 것이 필수

• 프로시저는 parameter리스트를 가질 수 있지만 값 반환이 필수적 이지는 않다.

• 함수는 다음과 같은 두 가지 점에서 프로시저와 다르다.

• 식(ｅxpression)의 일부로서 함수를 사용한다.

• 함수는 값을 return하는 것이 필수적이다.

TRIGGER

특정 테이블에 DML(INSERT,UPDATE,DELETE)문장이 수행되었을 때 데이터베이스에서 자동적으로 PL/SQL 블록을 수행 시키기 위해서 데이터베이스 TRIGGER를 사용한다. TRIGGER는 트리거링 이벤트가 일어날 때마다 암시적으로 실행된다. 트리거링 이벤트에는 데이터베이스 테이블에서 INSERT, UPDATE, DELETE 오퍼레이션이다.

TRIGGER가 사용되는 경우

• 테이블 생성시 CONSTRAINT로 선언 제한이 불가능하고 복잡한 무결성 제한을 유지

• DML문장을 사용한 사람,변경한 내용,시간 등을 기록함으로써 정보를 AUDIT하기

• 테이블을 변경할 때 일어나야 할 동작을 다른 테이블 또는 다른 프로그램들에게 자동적으로 신호하기

TRIGGER에 대한 제한

• TRIGGER는 트랜잭션 제어 문(COMMIT,ROLLBACK,SAVEPOINT)장을 사용하지 못한다.

• TRIGGER 주요부에 의해 호출되는 프로시저나 함수는 트랜잭션 제어 문장을 사용하지 못한다.

• TRIGGER 주요부는 LONG또는 LONG RAW변수를 선언할 수 없다.

• TRIGGER 주요부가 액세스하게 될 테이블에 대한 제한이 있다.

TRIGGER생성

CREATE TRIGGER문장에 의해 TRIGGER를 생성할 수 있다.

CREATE  [OR  REPLACE]  TRIGGER  trigger_name             {BEFORE | AFTER}  triggering_event [OF  column1, . . .] ON table_name             [FOR  EACH ROW  [WHEN  trigger_condition] trigger_body; trigger_name : TRIGGER의 식별자 BEFORE | AFTER : DML문장이 실행되기 전에 TRIGGER를 실행할 것인지 실행된 후에TRIGGER를 실행할 것인지를 정의 triggering_event : TRIGGER를 실행하는 DML(INSERT,UPDATE,DELETE)문을 기술한다. OF column : TRIGGER가 실행되는 테이블에서 COLUMN명을 기술한다. table_name : TRIGGER가 실행되는 테이블 이름 FOR EACH ROW : 이 옵션을 사용하면 행 레벨 트리거가 되어 triggering문장에 의해 영향받은 행에 대해 각각 한번씩 실행하고 사용하지 않으면 문장 레벨 트리거가 되어 DML문장 당 한번만 실행된다.

TRIGGER에서 OLD와 NEW

행 레벨 TRIGGER에서만 사용할 수 있는 예약어로 트리거 내에서 현재 처리되고 있는 행을 액세스할 수 있다. 즉 두개의 의사 레코드를 통하여 이 작업을 수행할 수 있다. :OLD는 INSERT문에 의해 정의되지 않고 :NEW는 DELETE에 대해 정의되지 않는다. 그러나 UPDATE는 :OLD와:NEW를 모두 정의한다. 아래의 표는 OLD와 NEW값을 정의한 표이다.

문장	:OLD	:NEW
INSERT	모든 필드는 NULL로 정의	문장이 완전할 때 삽입된 새로운 값
UPDATE	갱신하기 전의 원래 값	문장이 완전할 때 갱신된 새로운 값
DELETE	행이 삭제되기 전의 원래 값	모든 필드는 NULL이다.

TRIGGER 술어 사용하기

트리거 내에서 오퍼레이션이 무엇인지를 결정하기 위해 사용할 수 있는 3가지 BOOLEAN함수가 있다.

술 어	설     명
INSERTING	트리거링 문장이 INSERT이면 TRUE를 그렇지 않으면 FALSE를 RETURN
UPDATING	트리거링 문장이 UPDATE이면 TRUE를 그렇지 않으면 FALSE를 RETURN
DELETING	트리거링 문장이 DELETE이면 TRUE를 그렇지 않으면 FALSE를 RETURN

TRIGGER 삭제와 억제하기

DROP TRIGGER명령어로 트리거를 삭제할 수 있고 TRIGGER를 잠시 disable할 수 있다.

DROP  TRIGGER  trigger_name; ALTER  TRIGGER  trigger_name  {DISABLE | ENABLE};

TRIGGER와 DATA DICTIONARY

TRIGGER가 생성될 때 소스 코드는 데이터 사전 VIEW인 user_triggers에 저장된다. 이 VIEW는TRIGGER_BODY, WHERE절, 트리거링 테이블, TRIGGER 타입을 포함 한다.

SQL> SELECT trigger_type,table_name,triggering_event  2 FROM user_triggers; TRIGGER_TYPE     TABLE_NAME              TRIGGERING_EVENT ---------------- ------------------------------ -------------------------- AFTER STATEMENT  EMP              INSERT OR UPDATE OR DELETE BEFORE STATEMENT EMP                            INSERT OR UPDATE OR DELETE BEFORE EACH ROW  EMP              UPDATE

CREATE OR REPLACE TRIGGER emp_sal_chk BEFORE UPDATE OF sal on emp FOR EACH ROW WHEN (NEW.sal < OLD.sal                           OR NEW.sal > OLD.sal * 1.1) BEGIN             raise_application_error(-20502,                'May not decrease salary. Increase must be < 10%'); END; / SQL> @emp_sal