개요

Stored procedure는 사용자 업무 절차를 SQL을 통해 작성하여 메모리에 저장한 후 이를 호출하여 결과를 만들어내는 방식을 의미한다.

기본적으로 원격 서버에서 한 개의 트랜잭션 내에 다수의 API를 호출하여 발생하는 network I/O 비용을 고려할 때 procedure를 통해 호출하는 방법이 유리한 경우에 stored procedure를 사용할 것을 권장한다. 
지역 서버 내에서도 procedure를 호출할 수 있으나 내부적으로 SQL을 처리하는 과정에서 변수와 결과에 대한 많은 expression 처리 비용이 실제 API를 호출하는 비용보다 클 수 있기 때문에 단순한 operation에 대해서는 API에 비해 빠르지 않을 수 있음을 고려해야 한다.

기능

Procedure 내에 변수를 선언하거나 parameter를 사용할 수 있으며 다음과 같은 데이터 타입이 제공된다.

GOLDILOCKS LITE는 다음 표와 같이 procedure 내의 기능 구문을 지원한다.

GOLDILOCKS LITE에서 SQL과 cursor attribute 변수를 사용하기 위해 다음 표의 변수가 자동으로 생성되어 있다.

PROCEDURE DDL

본 장에서는 procedure를 생성하는 방법에 대해 설명한다. GOLDILOCKS LITE에서는 수행 중인 procedure에 대해 DDL을 수행할 수 있는데, 이 때 기존 procedure를 실행 계획으로 하여 이미 구동된 프로세스는 변경된 procedure 내용을 감지하지 않기 때문에 관련 응용 프로그램들을 모두 정지시킨 후에 해당 DDL을 수행해야 한다.

Create [or replace] Procedure

create_or_replace_procedure ::= CREATE [ OR REPLACE ] PROCEDURE proc_name
                                param_declare_section
                                IS
                                declare_section
                                proc_block
                                /
                                ;


proc_name ::= Procedure 이름


param_declare_section ::= ( variable_name [IN|OUT|INOUT] data_type [, ... ] )


declare_section ::= ( variable_name declare_type; [ ... ] )


variable_name ::= Procedure 내에서 사용할 변수의 이름

declare_type ::=    INT 
                  | SHORT  
                  | DOUBLE
                  | LONG
                  | FLOAT
                  | CHAR (size)
                  | table_name.column_Name%TYPE
                  | variable_name%TYPE
                  | table_name%ROWTYPE
                  | EXCEPTION


proc_block ::= BEGIN
                   proc_stmt
               END


proc_stmt ::= GOLDILOCKS LITE가 제공하는 procedure 구문들

다음은 procedure를 생성하는 예이다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1 (a1 in int )
IS
c1 INT;
BEGIN
   a1 := c1;
END;
/
success
dbmMetaManager(DEMO)>

Procedure가 정상적으로 생성되었을 경우, 다음과 같은 방법을 사용하여 dictionary instance가 있는지 여부를 확인해 볼 수 있다.

dbmMetaManager(DEMO)> set instance dict;
success
dbmMetaManager(DICT)> select * from dic_procedure;
---------------------------------------------------------------------------------
INST_NAME            : DEMO
PROC_NAME            : PROC1
PIECE_COUNT          : 1
---------------------------------------------------------------------------------

1 row selected

Drop Procedure

기존에 있던 procedure를 제거한다. Drop procedure에 의해 제거된 procedure를 수행 중인 세션이 감지할 수 없기 때문에 응용 프로그램을 중지한 후에 procedure를 제거하는 것이 좋다.

DROP PROCEDURE ::= DROP PROCEDURE <procedure_name>;

procedure_name := 삭제할 procedure 이름

dbmMetaManager(DEMO)> DROP PROCEDURE proc1;
success

Procedure View

Procedure의 정보를 확인하기 위해 다음의 두 view들을 참조할 수 있다.

dbm_procedure

Procedure의 기본 정보를 담고 있다.

dbm_procedure_text

Procedure를 생성한 시점에 사용된 구문 전체를 저장한다.

PROCEDURE Language Elements

본 장에서는 procedure에서 제공되는 각 구성 요소와 기능에 대해 설명한다.

Declare Section

Procedure의 block 단위에서 사용할 수 있는 element들의 선언부이다.

Variable

SCALAR TYPE

GOLDILOCKS LITE에서 사용되는 변수 중에 scalar data type에 대한 변수를 선언한다.

scalar typed variable ::= variable_name data_type ( := init_value_expr );

variable_name := 선언하려는 변수의 이름 (동일 block 내에 중복된 이름을 허용하지 않음)

data_type :=   INT 
             | SHORT 
             | LONG 
             | FLOAT 
             | DOUBLE 
             | CHAR (size)

init_value_expr := 초기값을 지정할 경우 상수나 수식을 기술할 수 있다.

%TYPE

이미 생성된 테이블의 column이나 앞서 선언된 변수의 타입을 그대로 사용하기 위해 사용된다.

Typed variable ::=   table_name.column_name % TYPE 
                   | variable_name % TYPE
                   | variable_name.field_name %TYPE
                   ;

다음과 같이 사용할 수 있는데 테이블이나 변수의 타입을 사용하려면 해당 테이블이나 변수가 미리 생성되어 있거나 선언되어 있는 상태여야 한다.

dbmMetaManager(DEMO)> CREATE TABLE T1 (C1 INT, C2 INT);
success
dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE PROC1
IS
  V1    INT;
  V2    V1%TYPE;
  V3    T1.C1%TYPE;
BEGIN
  NULL;
END;
/
success

%ROWTYPE

하나 이상의 필드를 갖는 변수를 선언하기 위해 사용하는데 이 때 필드는 테이블의 구조와 동일하게 구성된다.

Row typed variable ::= table_name % ROWTYPE;

다음은 테이블 구조와 동일한 ROWTYPE 변수를 선언하는 예이다.

dbmMetaManager(DEMO)> CREATE TABLE T1 (C1 INT, C2 INT);
success
dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE PROC1
IS
  V1    T1%ROWTYPE;
BEGIN
  NULL;
END;
/
success

위의 예제에서 V1 변수는 각각 두 개의 field (C1, C2)를 가진 대표 변수로 선언되며 각 field는 다음과 같이 표기하여 사용할 수 있다.

dbmMetaManager(DEMO)> CREATE TABLE T1 (C1 INT, C2 INT);
success
dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE PROC1
IS
  V1    T1%ROWTYPE;
BEGIN
  V1.C1 := V1.C2;
END;
/
success

dbmMetaManager(DEMO)> CREATE TABLE T1 (C1 INT, C2 INT);
success
dbmMetaManager(DEMO)> CREATE TABLE T2 (C1 INT, C2 INT);
success
dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE PROC1
IS
  V1    T1%ROWTYPE;
  V2    T1%ROWTYPE;
BEGIN
  V2 := V1;
END;
/
success

변수의 유효 범위

변수의 유효 범위는 선언된 block 내부이다. 상위 block에 선언된 변수는 하위 block에서도 참조할 수 있지만 하위 block의 변수는 상위 block에서 참조할 수 없다. 또한, 동일한 변수명이 여러 개 존재할 경우 먼저 탐색된 block 내의 변수를 참조하므로 다른 상위 block의 변수를 이용하려면 해당 block의 label을 통해 접근해야 한다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1
IS
  V1 INT;
BEGIN
     DECLARE
         V1 INT;
     BEGIN
         V1 := 1;
         DBMS_OUTPUT.PUT_LINE( 'SCOPE1: V1 = ' || V1 );
     END;

     DBMS_OUTPUT.PUT_LINE( 'SCOPE2: V1 = ' || V1 );
END;
/
success
dbmMetaManager(DEMO)> exec proc1
SCOPE1: V1 = 1
SCOPE2: V1 = 0
success
dbmMetaManager(DEMO)>

위의 예제에서 V1은 가장 안쪽 block 내에서만 유효하며 해당 block 밖으로 나와 두 번째로 출력될 때는 상위 block을 참조하기 때문에 0으로 출력된다.

Cursor

GOLDILOCKS LITE에서 cursor는 snapshot의 개념이다. 실행 시점에 획득한 SCN 값을 기준으로 자신이 접근 가능한 record에 대해 결과 집합을 만든다. 별도의 isolation level은 제공하지 않는다.

DECLARE

Cursor_declaration ::= CURSOR cursor_name [ ( param_list ) ]  IS select_statement ;

cursor_name := Cursor 이름
param_list := Cursor 선언 내의 select 문에 사용될 parameter가 있을 경우 기술한다. 없으면 생략한다.
select_statement := 결과 집합에 대한 질의문

다음과 같이 cursor 관련 구문을 활용하여 실행할 수 있다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1()
IS
  v1 INT;
  v2 CHAR(20);
  v3 DOUBLE;
  v4 t1%ROWTYPE;
  CURSOR c1 (a1 INT, a2 DOUBLE )
     IS select * from t1 where c1 >= a1 and c3 > a2;
BEGIN
  OPEN c1 (2, 3);

  LOOP
    FETCH c1 INTO v1, v2, v3;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line( 'v1 = ' || v1 || ', v2 = ' || v2 || ', v3 = ' || v3 || ', RowCount=' || c1%rowcount );
  END LOOP;

  CLOSE c1;

  OPEN c1 (2, 3);

  LOOP
    FETCH c1 INTO v4;
    EXIT WHEN c1%notfound;
    dbms_output.put_line( 'c1 = ' || v4.c1 || ', c2 = ' || v4.c2 || ', c3 = ' || v4.c3 || ', RowCount=' || c1%rowcount );
  END LOOP;

END;
/
success
dbmMetaManager(DEMO)> exec proc1
V1 = 3, V2 = c, V3 = 3.500000, ROWCOUNT=1
V1 = 4, V2 = d, V3 = 4.500000, ROWCOUNT=2
V1 = 5, V2 = e, V3 = 5.500000, ROWCOUNT=3
C1 = 3, C2 = c, C3 = 3.500000, ROWCOUNT=1
C1 = 4, C2 = d, C3 = 4.500000, ROWCOUNT=2
C1 = 5, C2 = e, C3 = 5.500000, ROWCOUNT=3
success

User Exception

Procedure를 실행하는 과정에서 사용자가 예외 처리할 사항을 정의한다.

User_exception ::= exception_name EXCEPTION ;

exception_name := 사용자가 정의하려는 exception name

다음은 user exception을 사용한 exception handler의 예이다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1
IS
  user1 EXCEPTION;
BEGIN
  BEGIN
    RAISE user1;
    EXCEPTION WHEN case_not_found THEN dbms_output.put_line('fatal execution');
  END;
EXCEPTION WHEN user1 THEN dbms_output.put_line( 'SQLCODE=' || SQLCODE || ',SQLERRM=' || SQLERRM );
END;
/
success
dbmMetaManager(DEMO)> exec proc1
SQLCODE=70121,SQLERRM=a user exception raised
success

GOLDILOCKS LITE에 미리 정의된 내부 exception은 다음 표와 같다.

Assign Statement

Assign statement는 procedure 내의 변수에 사용자가 지정한 값을 설정할 때 사용한다.

Assign_statement ::=   Variable := Expression ;

variable ::= Procedure내에 선언된 변수 이름
expression := 수식

A := B의 수식에서 A가 RowType과 같은 필드로 구성된 변수 타입인 경우, B도 A와 동일한 타입 또는 필드의 구성 타입과 개수가 동일한 타입의 변수이어야 assign을 수행할 수 있다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1 ()
IS
v1 t1%ROWTYPE;
v2 t1%ROWTYPE;
BEGIN
    v1.c1 := 100;
    v1.c2 := 200;

    dbms_output.put_line( 'v1.c1 = ' || v1.c1 );
    dbms_output.put_line( 'v1.c2 = ' || v1.c2 );

    v2 := v1;

    dbms_output.put_line( 'v2.c1 = ' || v2.c1 );
    dbms_output.put_line( 'v2.c2 = ' || v2.c2 );
END;
/
success
dbmMetaManager(DEMO)> exec proc1
V1.C1 = 100
V1.C2 = 200
V2.C1 = 100
V2.C2 = 200
success

Control Statements

Procedure 수행 과정에서 조건에 따라 반복, 점프, 분기 등을 수행하기 위한 구문을 제공한다.

IF

조건절을 만족할 경우, 하위 statement들을 수행한다.

IF_statement ::=  IF condition_expression THEN statements 
                  [ ( ELSIF condition_expression THEN Statements ) ... ]
                  | 
                  [ ELSE statements ]
                  END IF ;

condition_expression ::= 수행 여부를 판단하는 조건 수식
statements ::= Procedure statement의 정의

condition_expression에서 두 개 이상의 조건을 판단해야 할 경우, AND 또는 OR로 나열하여 기술한다.
다음 예제를 참조한다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1( a1 int )
IS
c1 INT;
BEGIN
  c1 := a1;


  IF c1 = 1 THEN
      dbms_output.put_line( 'C1(cond1) = ' || c1 );
  END IF;

  IF c1 = 1 THEN
      dbms_output.put_line( 'C1(cond2.1) = ' || c1 );
  ELSE
      dbms_output.put_line( 'C1(cond2.2) = ' || c1 );
  END IF;

  IF c1 = 1 THEN
      dbms_output.put_line( 'C1(cond3.1) = ' || c1 );
  ELSIF c1 = 2 THEN
      dbms_output.put_line( 'C1(cond3.2) = ' || c1 );
  ELSIF c1 = 3 THEN
      dbms_output.put_line( 'C1(cond3.3) = ' || c1 );
  ELSE
      dbms_output.put_line( 'C1(cond3.4) = ' || c1 );
  END IF;

END;
/
dbmMetaManager(DEMO)> exec proc1( 1 )
C1(COND1) = 1
C1(COND2.1) = 1
C1(COND3.1) = 1
success
dbmMetaManager(DEMO)> exec proc1( 2 )
C1(COND2.2) = 2
C1(COND3.2) = 2
success
dbmMetaManager(DEMO)> exec proc1( 3 )
C1(COND2.2) = 3
C1(COND3.3) = 3
success
dbmMetaManager(DEMO)> exec proc1( 4 )
C1(COND2.2) = 4
C1(COND3.4) = 4
success

Simple Case

Case 절에 기술한 값이 WHEN 절의 수식 결과값과 일치할 경우, 해당 WHEN 절에 기술된 statement들을 수행한다.

simple_case ::= CASE value_expression 
                [ WHEN value_expression THEN statements ; ( ... ) ]
                ( ELSE statements ; )
                END CASE;

value_expression ::= 수식
statements ::= Procedure에서 실행 가능한 구문

다음 예제를 참조한다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1( a1 int )
IS
  c1 INT;
BEGIN

  c1 := a1;

  dbms_output.put_line( 'NoElsePart' );
  CASE a1
      WHEN 1 THEN dbms_output.put_line( '1. c1 = ' || c1 );
      WHEN 2 THEN dbms_output.put_line( '2. c1 = ' || c1 );
      WHEN 3 THEN dbms_output.put_line( '3. c1 = ' || c1 );
  END CASE;


  dbms_output.put_line( 'ElsePart' );
  CASE a1
      WHEN 1 THEN dbms_output.put_line( '1. c1 = ' || c1 );
      WHEN 2 THEN dbms_output.put_line( '2. c1 = ' || c1 );
      ELSE dbms_output.put_line( 'else c1 = ' || c1 );
  END CASE;

END;
/
success
dbmMetaManager(DEMO)> exec proc1(1)
NOELSEPART
1. C1 = 1
ELSEPART
1. C1 = 1
success
dbmMetaManager(DEMO)> exec proc1(2)
NOELSEPART
2. C1 = 2
ELSEPART
2. C1 = 2
success
dbmMetaManager(DEMO)> exec proc1(3)
NOELSEPART
3. C1 = 3
ELSEPART
ELSE C1 = 3
success

Searched Case

CASE WHEN 절에 기술된 수식이 참 (TRUE)인 경우, 해당 WHEN 절의 statement들을 수행한다.

searched_case::= CASE [ WHEN condition_expression THEN statements ; ( ... ) ]
                 ( ELSE statements ; )
                  END CASE;

condition_expression ::= 조건 수식
statements ::= Procedure에서 실행 가능한 구문

다음 예제를 참조한다.

dbmMetaManager(demo)> CREATE OR REPLACE PROCEDURE proc1( a1 int )
IS
  c1 INT;
BEGIN

  c1 := a1;

  dbms_output.put_line( 'NoElsePart' );
  CASE
      WHEN a1 = 1 THEN dbms_output.put_line( '1. c1 = ' || c1 );
      WHEN a1 = 2 THEN dbms_output.put_line( '2. c1 = ' || c1 );
      WHEN a1 = 3 THEN dbms_output.put_line( '3. c1 = ' || c1 );
  END CASE;


  dbms_output.put_line( 'ElsePart' );
  CASE
      WHEN a1 = 1 THEN dbms_output.put_line( '1. c1 = ' || c1 );
      WHEN a1 = 2 THEN dbms_output.put_line( '2. c1 = ' || c1 );
      ELSE dbms_output.put_line( 'else c1 = ' || c1 );
  END CASE;

END;
/
success
dbmMetaManager(DEMO)> exec proc1(1)
NOELSEPART
1. C1 = 1
ELSEPART
1. C1 = 1
success
dbmMetaManager(DEMO)> exec proc1(2)
NOELSEPART
2. C1 = 2
ELSEPART
2. C1 = 2
success
dbmMetaManager(DEMO)> exec proc1(3)
NOELSEPART
3. C1 = 3
ELSEPART
ELSE C1 = 3
success

GOTO

특정 label 위치로 jump 한다. 일반적인 C 언어의 goto 문과 같은 개념으로 사용한다. 단, IF 절의 안쪽으로는 진입할 수 없다.

GOTO_statement ::= GOTO label;

label ::= Procedure 내에 명시된 label name

다음 예제를 참조한다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1()
IS
  c1 INT := 0;
BEGIN

    << AA >>
    BEGIN

       dbms_output.put_line( 'seoul(c1=' || c1 || ')' );

       << BB >>
       BEGIN
           c1 := c1 + 1;

           dbms_output.put_line( 'paris(c1=' || c1 || ')' );
           IF c1 < 3
           THEN
              GOTO aa;
           ELSIF c1 >= 3 AND c1 < 5 THEN
              GOTO bb;
           ELSE
              GOTO cc;
           END IF;

           dbms_output.put_line( 'never print' );

           BEGIN
               dbms_output.put_line( 'brazil(c1=' || c1 || ')' );

               BEGIN
                 << CC >>
                 dbms_output.put_line( 'sanghai(c1=' || c1 || ')' );
                 IF c1 = 5 THEN
                    GOTO aa;
                 ELSIF c1 = 6 THEN
                    GOTO bb;
                 END IF;

               END;
           END;

           dbms_output.put_line( 'taipei(c1=' || c1 || ')' );
       END;
    END;

END;
/
success
dbmMetaManager(DEMO)> exec proc1
SEOUL(C1=0)
PARIS(C1=1)
SEOUL(C1=1)
PARIS(C1=2)
SEOUL(C1=2)
PARIS(C1=3)
PARIS(C1=4)
PARIS(C1=5)
SANGHAI(C1=5)
SEOUL(C1=5)
PARIS(C1=6)
SANGHAI(C1=6)
PARIS(C1=7)
SANGHAI(C1=7)
TAIPEI(C1=7)
success

EXIT (WHEN)

LOOP를 수행하는 도중에 멈추고 해당 scope를 빠져나가기 위해 사용한다.

EXIT_statement ::= EXIT ( WHEN condition_expression ) ;

condition_expression ::= 조건을 지정하고자 할 경우 조건 수식을 기술

조건없이 EXIT를 조건 없이 기술할 경우에는 즉시 scope를 빠져나가고, 조건절이 기술된 경우에는 조건을 만족했을 때 scope를 벗어난다. 
다음 예제를 참조한다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1()
IS
  c1 INT;
BEGIN
    dbms_output.put_line( 'first_loop' );
    c1 := 0;

    LOOP
        c1 := c1 + 1;
        dbms_output.put_line( 'c1 = ' || c1 );
        EXIT;
    END LOOP;

    dbms_output.put_line( 'second_loop' );

    c1 := 0;
    LOOP
        c1 := c1 + 1;
        dbms_output.put_line( 'c1 = ' || c1 );
        EXIT WHEN c1 = 10;
    END LOOP;
END;
/
success
dbmMetaManager(DEMO)> exec proc1
FIRST_LOOP
C1 = 1
SECOND_LOOP
C1 = 1
C1 = 2
C1 = 3
C1 = 4
C1 = 5
C1 = 6
C1 = 7
C1 = 8
C1 = 9
C1 = 10
success

CONTINUE (WHEN)

LOOP를 수행하는 도중에 조건을 만족할 경우, LOOP scope 내의 시작 위치로 이동하여 수행을 시작한다.

CONTINUE_statement ::= CONTINUE ( WHEN condition_expression ) ;

condition_expression ::= 조건을 지정하고자 할 경우 조건 수식을 기술

조건없이 CONTINUE를 기술할 경우에는 LOOP 내의 첫 번째 statement로 이동하고, 조건절이 기술된 경우에는 조건을 만족할 경우에 이동한다. 
다음 예제를 참조한다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1()
IS
  c1 INT;
BEGIN
    c1 := 0;

    LOOP
        c1 := c1 + 1;
        IF c1 < 5 THEN
            CONTINUE;
        END IF;

        CONTINUE WHEN c1 < 10;

        dbms_output.put_line( 'c1 = ' || c1 );
        EXIT;
    END LOOP;
END;
/
success
dbmMetaManager(DEMO)> exec proc1
C1 = 10
success

RAISE_APPLICATION_ERROR

사용자가 특정 에러 코드와 에러 메시지를 설정하여 exception을 발생시키고자 할 경우에 사용된다.
GOLDILOCKS LITE에서 사용자 에러 코드는 음수로만 정의할 수 있다.

raise_application_error_statement ::= RAISE_APPLICATION_ERROR( errorCode, errorMessage);

errorCode ::= 음수 범위 내의 정수형 값
errorMessage ::= 사용자가 지정한 에러 메시지 (512 byte 이내로 설정 가능)

다음과 같이 exception을 발생시키는데 exception handler에 의해 처리되지 않을 경우, 상위로 에러를 전달한다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1
IS
 user1 EXCEPTION;
 PRAGMA EXCEPTION_INIT( user1, -100);
BEGIN
  RAISE_APPLICATION_ERROR( -100, 'user1 raise' );
EXCEPTION WHEN user1 THEN dbms_output.put_line( 'SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM );
END;
/
success
dbmMetaManager(DEMO)> exec proc1
SQLCODE=-100, SQLERRM=USER1 RAISE
success

Loop Statements

Procedure 내에서 반복되는 명령문을 처리하기 위한 구문들이다.

SIMPLE LOOP

LOOP와 END LOOP 구문 사이에 기술된 구문들을 반복적으로 수행한다. Loop 구간을 빠져나가려면 EXIT 구문을 사용한다.

LOOP Statement ::=   LOOP 
                        < statements > 
                     END LOOP

statements ::= Procedure 내에서 사용 가능한 구문들

dbmMetaManager(demo)> CREATE OR REPLACE PROCEDURE proc1()
IS
  c1 INT;
BEGIN
    dbms_output.put_line( 'first_loop' );
    c1 := 0;

    LOOP
        c1 := c1 + 1;
        dbms_output.put_line( 'c1 = ' || c1 );
        EXIT;
    END LOOP;

    dbms_output.put_line( 'second_loop' );

    c1 := 0;
    LOOP
        c1 := c1 + 1;
        dbms_output.put_line( 'c1 = ' || c1 );
        EXIT WHEN c1 = 10;
    END LOOP;
END;
/
success
dbmMetaManager(DEMO)> exec proc1
FIRST_LOOP
C1 = 1
SECOND_LOOP
C1 = 1
C1 = 2
C1 = 3
C1 = 4
C1 = 5
C1 = 6
C1 = 7
C1 = 8
C1 = 9
C1 = 10
success

FOR LOOP

FOR LOOP는 FOR 구문에 기술된 수식을 증가/ 감소시키면서 최종값에 도달할 때까지 LOOP ~ END LOOP 구문 사이에 기술된 procedure 구문들을 수행한다.

FOR LOOP Statement ::= FOR <identifier> IN (REVERSE) <first_expr> .. <last_expr>
                       LOOP
                            <statements>
                       END LOOP


identifier := FOR LOOP 내에서 사용될 LOOP 범위 연산의 변수명
REVERSE := 생략할 경우 First_expr을 시작으로 Last_expr 값까지 진행, 기술되면 역순으로 진행
first_expr := FOR LOOP 내의 identifier가 가질 첫 번째 수식 값, REVERSE가 기술된 경우에는 마지막 값
last_expr := FOR LOOP 내의 identifier가 가질 마지막 수식 값, REVERSE가 기술된 경우에는 첫 번째 값
statements := FOR LOOP 내에서 수행될 procedure 구문들

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1()
IS
  c1 INT;
BEGIN
  FOR i IN 1 .. 5
  LOOP
    dbms_output.put_line( 'i = ' || i );
  END LOOP;
END;
/
success
dbmMetaManager(DEMO)> EXEC proc1()
I = 1
I = 2
I = 3
I = 4
I = 5
success
dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1()
IS
  c1 INT;
BEGIN
  FOR i IN REVERSE 1 .. 5
  LOOP
    dbms_output.put_line( 'i = ' || i );
  END LOOP;
END;
/
success
dbmMetaManager(DEMO)> EXEC proc1()
I = 5
I = 4
I = 3
I = 2
I = 1
success

WHILE LOOP

WHILE LOOP는 WHILE 절에 기술된 조건이 참인 경우에만 LOOP 내의 procedure 구문들을 수행하는 방식이다.

WHILE LOOP Statement ::= WHILE  <cond_expr >
                         LOOP
                            <statements>
                         END LOOP

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1()
IS
  c1 INT := 0;
BEGIN
    dbms_output.put_line( 'start: c1 = ' || c1 );

    WHILE c1 < 5
    LOOP
        c1 := c1 + 1;
    END LOOP;

    dbms_output.put_line( 'last c1 = ' || c1 );
END;
/
success
dbmMetaManager(DEMO)> EXEC proc1
START: C1 = 0
LAST C1 = 5
success

CURSOR Statements

SELECT 문을 사용하여 두 건 이상의 데이터를 가져오고자 할 경우에 사용한다.  GOLDILOCKS LITE의 cursor는 실행 시점의 snapshot을 이용하며 committed-read mode만 보장한다.
Cursor를 사용하기 위해서는 cursor 정의를 먼저 수행해야 하는데 이는 declare section의 Cursor 부분을 참조한다.

OPEN

Declare section에 정의된 cursor 구문을 실행한다.

OPEN statement ::= OPEN <cursor_name>;

cursor_name := Declare section에 정의된 cursor 이름

dbmMetaManager(DEMO)> CREATE TABLE t1 (c1 INT, c2 CHAR(20), c3 DOUBLE)
success
dbmMetaManager(DEMO)> CREATE UNIQUE INDEX idx_t1 ON t1 (c1)
success
dbmMetaManager(DEMO)> INSERT INTO t1 VALUES (1, 'a', 1.5 )
success
dbmMetaManager(DEMO)> INSERT INTO t1 VALUES (2, 'b', 2.5 )
success
dbmMetaManager(DEMO)> INSERT INTO t1 VALUES (3, 'c', 3.5 )
success
dbmMetaManager(DEMO)> INSERT INTO t1 VALUES (4, 'd', 4.5 )
success
dbmMetaManager(DEMO)> INSERT INTO t1 VALUES (5, 'e', 5.5 )
success
dbmMetaManager(DEMO)> COMMIT
success
dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1()
IS
  v1 INT;
  v2 CHAR(20);
  v3 DOUBLE;
  v4 t1%ROWTYPE;
CURSOR c1 (a1 INT, a2 DOUBLE )
 IS select * from t1 where c1 >= a1 and c3 > a2;
BEGIN
  OPEN c1 (2, 3);

  LOOP
    FETCH c1 INTO v1, v2, v3;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line( 'v1 = ' || v1 || ', v2 = ' || v2 || ', v3 = ' || v3 || ', RowCount=' || c1%rowcount );
  END LOOP;

  CLOSE c1;

  OPEN c1 (2, 3);

  LOOP
    FETCH c1 INTO v4;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line( 'c1 = ' || v4.c1 || ', c2 = ' || v4.c2 || ', c3 = ' || v4.c3 || ', RowCount=' || c1%rowcount );
  END LOOP;


END;
/
success
dbmMetaManager(DEMO)> EXEC proc1
V1 = 3, V2 = c, V3 = 3.500000, ROWCOUNT=1
V1 = 4, V2 = d, V3 = 4.500000, ROWCOUNT=2
V1 = 5, V2 = e, V3 = 5.500000, ROWCOUNT=3
C1 = 3, C2 = c, C3 = 3.500000, ROWCOUNT=1
C1 = 4, C2 = d, C3 = 4.500000, ROWCOUNT=2
C1 = 5, C2 = e, C3 = 5.500000, ROWCOUNT=3
success

FETCH

OPEN 구문으로 열린 cursor에 한해 record를 한 건씩 읽어들여 INTO 절에 기술된 변수로 값을 복사한다.

FETCH Statement ::=  FETCH <cursor_name>  INTO < variable_name (, ... ) >

cursor_name := Open 구문으로 정상 처리된 cursor name
variable_name := Fetch 된 결과를 저장할 변수 이름

Scalar와 ROWTYPE 변수 둘 다 INTO 절에 기술할 수 있다. 변수를 기술하는데 제약 사항은 없지만 cursor결과를 가진 target 절의 개수와 INTO 절에 기술된 변수와의 DataType 호환에 제약이 있을 경우 fetch를 수행하는 도중에 오류가 발생할 수 있다.

CLOSE

OPEN 구문에 의해 열린 cursor를 정리한다.

CLOSE statement ::= CLOSE <cursor_name>

cursor_name := Open 구문에 의해 정상적으로 열린 cursor 이름

SQL Statements

Procedure 내에 일반 DML을 사용할 수 있는데 이에 대한 구문을 설명한다.

INSERT

한 건의 record를 지정한 테이블에 삽입한다.

INSERT Statement  ::= INSERT INTO <target_table> ( column_name (, ...) >
                      VALUES ( value_expr ( ,...) );

target_table := 대상 테이블
column_name := 테이블 내의 특정 column을 나열하고자 할 경우
value_expr := Procedure 변수를 포함한 value list

UPDATE

테이블에서 한 건 이상의 레코드의 지정된 column 값을 갱신한다.

UPDATE Statement ::= UPDATE <target_table> 
                     SET  <column_name> = <value_expr> (, ...)
                     ( WHERE cond_expr );

target_table := 대상 테이블
column_name := 값을 변경하고자 하는 대상 column 이름
value_expr := 변수등을 포함하는 value expression
cond_expr := 특정 레코드를 탐색할 경우 해당하는 조건절

DELETE

테이블에서 한 건 이상의 레코드를 삭제한다.

DELETE Statement ::= DELETE FROM <target_table> 
                     (WHERE cond_expr);

target_table := 대상 테이블
cond_expr := 특정 레코드를 탐색할 경우 조건을 기술

SELECT INTO

테이블에서 한 건의 데이터를 탐색하여 procedure의 변수에 값을 저장하고자 할 경우에 사용한다. Cursor와 달리 한 건만 fetch 할 수 있다.

SELECT INTO statement ::= SELECT <target_list> INTO <variable_list>
                                    FROM <source_table>
                                   (WHERE cond_expr);

target_list := 테이블에서 가지고 올 column 이름 ( *를 사용할 경우, 모든 column을 가지고 옴)
variable_list := Procedure 내의 변수
source_table := 대상 테이블
cond_expr := 조건절

COMMIT

현재 세션에서 진행한 트랜잭션을 영구적으로 반영한다.
다음 예제와 같이 procedure 내에서 COMMIT을 사용하여 트랜잭션을 영구적으로 반영한다.  자신의 세션에서 조회할 경우 commit 하기 전이라도 세션 변경사항을 볼 수 있지만 다른 세션에서 조회할 경우에는 변경 전의 committed-image를 조회한다.

dbmMetaManager(DEMO)> CREATE TABLE t1 (c1 INT, c2 INT)
success
dbmMetaManager(DEMO)> INSERT INTO t1 VALUES (2, 2)
success
dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1
IS
  V1 INT := 100;
BEGIN
    INSERT INTO T1 VALUES (1, 1);
    COMMIT;

    UPDATE T1 SET C2 = 100 WHERE C1 = 1;
    DBMS_OUTPUT.PUT_LINE( 'v1 = ' || v1 );
END;
/
success
dbmMetaManager(DEMO)> EXEC proc1
V1 = 100
success
dbmMetaManager(DEMO)> SELECT * FROM T1
---------------------------------------------------------------------------------
C1                   : 2
C2                   : 2
---------------------------------------------------------------------------------
C1                   : 1
C2                   : 100
---------------------------------------------------------------------------------

2 row selected

다른 세션에서 조회할 경우 다음과 같이 변경전 이미지를 조회한다.

[2nd_proc] dbmMetaManager(DEMO)> set instance demo
success
[2nd_proc] dbmMetaManager(DEMO)> select * from t1
---------------------------------------------------------------------------------
C1                   : 2
C2                   : 2
---------------------------------------------------------------------------------
C1                   : 1
C2                   : 1
---------------------------------------------------------------------------------

2 row selected

ROLLBACK

현재의 세션에서 진행한 트랜잭션을 철회한다.
다음 예제와 같이 갱신된 데이터에 rollback을 수행하여 트랜잭션을 이전의 원래 데이터로 복구한다.

dbmMetaManager(DEMO)> CREATE TABLE t1 (c1 INT, c2 INT)
success
dbmMetaManager(DEMO)> INSERT INTO t1 VALUES (2, 2)
success
dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc1
IS
  V1 INT := 200;
BEGIN
    INSERT INTO T1 VALUES (1, 1);
    UPDATE T1 SET C2 = 100 WHERE C1 = 1;
    COMMIT;

    UPDATE T1 SET C2 = 200 WHERE C1 = 1;
    ROLLBACK;

    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
END;
/
success
dbmMetaManager(DEMO)> EXEC proc1
V1 = 200
success
dbmMetaManager(DEMO)> SELECT * FROM T1
---------------------------------------------------------------------------------
C1                   : 2
C2                   : 2
---------------------------------------------------------------------------------
C1                   : 1
C2                   : 100
---------------------------------------------------------------------------------

2 row selected

Exception Handler

Exception handler는 procedure를 수행하는 도중에 발생하는 오류를 감지하여 사용자가 지정한 코드를 수행하고 해당 블럭을 종료하는 procedure의 exception 코드들이다.

이러한 예외 처리들은 미리 정의된 구문이나 사용자 정의에 의해 구분된다. 사용자 정의 exception에 대한 자세한 앞서 설명한 Declare Section을 참조한다.

PreDefined Exception

Exception Handler 정의

Exception Handler :::=  EXCEPTION 
                        ( WHEN <exception_name> THEN <statements>; [, ...] )

exception_name := PreDefined exception 또는 사용자 정의 exception name
statements := Procedure 구문들

다음과 같은 방식으로 사용할 수 있다. 다음은 select 문에 의해 NOT_FOUND 에러가 발생한 경우, OTHERS exception handler를 통해 처리되는 부분과 exception scope에 대한 예이다.

dbmMetaManager(DEMO)> CREATE OR REPLACE PROCEDURE proc2 ()
IS
v1 INT;
BEGIN
    dbms_output.put_line( 'start' );
    BEGIN
        BEGIN
            select c1 into v1 from t1 where c1 = 1;
            dbms_output.put_line( 'first line' );
        EXCEPTION
        WHEN others THEN dbms_output.put_line( 'exception raise' );
        END;

        dbms_output.put_line( 'middle line' );
    EXCEPTION
    WHEN others THEN dbms_output.put_line( 'exception raise' );
    END;
    dbms_output.put_line( 'last line' );
END;
/
success
dbmMetaManager(DEMO)> EXEC proc2
START
EXCEPTION RAISE
MIDDLE LINE
LAST LINE
success

Raise Statement

Procedure를 수행하는 도중에 사용자가 임의로 exception 상태를 발생시키려고 할 경우에 사용한다.

RAISE Statement ::= RAISE ( <exception_name> );

exception_name ::= 사용자 정의 exception 이름

Attribute Variable

SQL 수행 상태 또는 cursor의 수행 상태와 정보를 가진 변수들을 의미하며 사용자 선언 없이 내부적으로 할당되어 존재한다.

SQL Attribute Variable

SQL 문의 수행 상태에 대한 정보를 담고 있다. GOLDILOCKS LITE의 상태/ 초기값은 다른 DBMS들에서 쓰이는 것과 다를 수 있다.

Cursor Attribute Variable

CURSOR 문의 수행 상태에 대한 정보를 담고 있다. GOLDILOCKS LITE의 상태/ 초기값은 다른 DBMS들에서 쓰이는 것과 다를 수 있다. SQL attribute 변수들과 의미는 동일하며 cursor에 대해서만 사용할 수 있다.

SQLCODE

Procedure를 수행 중에 발생하는 오류들 중에 가장 마지막 오류의 에러 코드를 저장한다. Exception handler의 첫 구문이 정상적으로 처리되면 에러 코드는 0으로 설정된다.

SQLERRM

Procedure 수행 중에 발생하는 오류들 중에 가장 마지막 오류의 에러 메시지를 저장한다. Exception handler의 첫 구문이 정상적으로 처리되면 오류는 초기화 된다.