2011년 6월 1일 수요일

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} expression]
            [,argument2  [mode2]  datatype [{:= | DEFAULT} expression], . . .])]
{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} expression]
            [,argument2  [mode2]  datatype [{:= | DEFAULT} expression], . . .])]
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리스트를 가질 수 있지만 값 반환이 필수적 이지는 않다.
  • 함수는 다음과 같은 두 가지 점에서 프로시저와 다르다.
  • 식(expression)의 일부로서 함수를 사용한다.
  • 함수는 값을 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

댓글 없음:

댓글 쓰기

ETL 솔루션 환경

ETL 솔루션 환경 하둡은 대용량 데이터를 값싸고 빠르게 분석할 수 있는 길을 만들어줬다. 통계분석 엔진인 “R”역시 하둡 못지 않게 관심을 받고 있다. 빅데이터 역시 데이터라는 점을 볼때 분산처리와 분석 그 이전에 데이터 품질 등 데이...