[본문스크랩] PL/SQL (3) - 블럭 구조

###################################################################################################

#   source site : blog.naver.com/tangamjaelt

#   master : 강용운

#   email : tangamjaelt@korea.com

#   last release : 2007.05.04

###################################################################################################

 

PL/SQL을 마스터하기 위해서는 블럭 구조와 블럭의 다양한 종류, 블럭의 사용법을 이해하는 것이 필수적이다.

 

 

Anonymous Block (이름 없는 블럭)

- 아무런 이름이 없는 블럭으로 프로시저나 함수, 트리거의 본문을 형성하지 않는 블럭

- 이는 SQL*Plus 스크립트의 일부로 인라인으로 사용될 수 있으며, 오류 처리 목적으로 프로시저와 함수 블럭 내부에 포함될 수도 있다.

- DECLARE 라는 예약어를 사용하여 시작하고 다음 예약어인 BEGIN 사이에 변수를 선언한다.

- BEGIN 예약어는 블럭의 절차적 부분이 시작된다는 것을 알려준다. 프로그램 코드가 이 부분에 온다.

- EXCEPTION 예약어는 블럭에서 예외 처리 코드를 갖는다.

 

*******************************************************

DECLARE

      변수 선언문;

BEGIN

      프로그램 코드

EXCEPTION

      오류처리 코드

END;

*******************************************************

 

 

Function Block (함수 블럭)

- DECLARE 예약어 대신 그 자리에 함수 헤더가 온다.

- 함수 헤더는 함수 이름과 파라미터들을 기술하고, 반환값의 형을 나타낸다.

- 인수목록 : 함수에 대한 입력 파라미터와 출력 파라미터 목록을 기술한다.

- RETURN : 반환값에 대한 데이터형을 기술한다.

*******************************************************

FUNCTION 이름 [(인수목록)] 

RETURN 데이터형 {IS, AS}

      변수 선언문;

BEGIN

      프로그램 코드

(EXCEPTION

      오류처리 코드)

END;

********************************************************************************************************************

DECLARE
        temp NUMBER;

 

        FUNCTION iifn(boolean_expression IN BOOLEAN,
                        true_number IN NUMBER,
                        false_number IN NUMBER)
        RETURN NUMBER IS

 

        BEGIN
                IF boolean_expression THEN
                        RETURN true_number;

                ELSIF NOT boolean_expression THEN
                        RETURN false_number;

                ELSE
                        RETURN NULL;

                END IF;
        END;

BEGIN
        DBMS_OUTPUT.PUT_LINE(iifn(2>1,1,0));
        DBMS_OUTPUT.PUT_LINE(iifn(2>3,1,0));

        temp := iifn(null, 1, 0);

        IF temp IS NULL THEN
                DBMS_OUTPUT.PUT_LINE('NULL');
        ELSE
                DBMS_OUTPUT.PUT_LINE(temp);
        END IF;
END;
/

********************************************************************************************************************

 

 

 

Procedure Block (프로시저 블럭)

- 함수와 비슷하다.

- 함수와 다른점은 함수는 값을 반환하고 식에 사용할 수 있지만, 프로시저는 값을 반환하지 않으며 식에 활용할 수 없다.

*******************************************************

PROCEDURE 이름 [(인수목록)] {IS, AS}

      변수 선언문;

BEGIN

      프로그램 코드

(EXCEPTION

      오류처리 코드)

END;

********************************************************************************************************************

DECLARE
        first_number NUMBER;
        second_number NUMBER;

 

        PROCEDURE swapn(num_one IN OUT NUMBER,
                        num_two IN OUT NUMBER) IS
                temp_num NUMBER;
        BEGIN
                temp_num := num_one;
                num_one := num_two;
                num_two := temp_num;
        END;

 

BEGIN
        first_number := 10;
        second_number := 20;
        DBMS_OUTPUT.PUT_LINE('First Number =' || TO_CHAR(first_number));
        DBMS_OUTPUT.PUT_LINE('Second Number =' || TO_CHAR(second_number));

 

        DBMS_OUTPUT.PUT_LINE('Swapping...');
        swapn(first_number, second_number);

 

        DBMS_OUTPUT.PUT_LINE('First Number =' || TO_CHAR(first_number));
        DBMS_OUTPUT.PUT_LINE('Second Number =' || TO_CHAR(second_number));
END;
/
********************************************************************************************************************

 중첩 블럭

- Block 내에 Block 이 들어감

- 변수는 블럭 내부에서만 사용 가능하다. 상위 레벨의 변수는 하위 레벨에서 모두 사용 가능하다.

- BEGIN 내에 정의

********************************************************************************************************************

DECLARE
        error_flag BOOLEAN := false;

BEGIN
        DBMS_OUTPUT.PUT_LINE('100~1000 counting');

        DECLARE
                hundreds_counter NUMBER(1,-2);
        BEGIN
                hundreds_counter := 100;

                LOOP
                        DBMS_OUTPUT.PUT_LINE(hundreds_counter);
                        hundreds_counter := hundreds_counter + 100;

                        IF hundreds_counter > 1000 THEN
                                EXIT;
                        END IF;
                END LOOP;
        EXCEPTION
        WHEN OTHERS THEN
                error_flag := true;
        END;

        IF error_flag THEN
                DBMS_OUTPUT.PUT_LINE('Can not count.');
        ELSE
                DBMS_OUTPUT.PUT_LINE('Count Done.');
        END IF;
END;
/
********************************************************************************************************************

 

 

 

 

Trigger Block (트리거 블럭)

- 트리거란 어떤 동작이나 사건이 발생했을 때 샐행되는 코드를 정의하는데 사용된다.

- 처음에 나타나는 구문은 트리거의 종류, 트리거와 연결된 테이블, 트리거가 발생해야 할 때를 말해주는 것이다.

- 예를 들어 특정 User가 CREATE, DROP, ALTER 등의 DDL 문을 실행하게 되면 트리거에서 사용을 막게 할 수 있다.

***************************************************************

CREATE [OR REPLACE] TRIGGER 트리거이름

{BEFORE|AFTER} 동사목록 ON 테이블이름

[[REFERENCING 상호관계이름] FOR EACH ROW [WHEN (조건)]]

DECLARE

      선언문

BEGIN

      PL/SQL코드

END;

****************************************************************

- 동사목록 : 트리거를 발생시키는 SQL 동사

- 테이블이름 : 트리거가 정의되는 테이블

- 조건 : 트리거 실행 조건

- 선언문 : 변수, 레코드, 커서 선언문으로 구성

- PL/SQL코드 : 트리거가 발생할 때 실행되는 PL/SQL 코드