01 Basic SQL Procedure Structure

Basic SQL Procedure Structure

 

1 The CREATE PROCEDURE statement

 

 
 

2 Procedure Name

 

Procedure 생성시 Scheme Name이 생략된 경우 “CURRENT SCHEME” special register에 의해 결정된다. CURRENT SCHEME의 기본값은 현재 User의 인증 받은 ID값이 사용된다.

동일한 Name과 다른 Parameter로 Procedure를 정의할 경우 SPECIFIC절이 요구된다

 

3 Parameters

 

CREATE PROCUDURE문장에서 기술되며, MODE/PARAMETER NAME/DATA TYPE등 세가지 Part로 구분된다.

 

- MODE : IN/OUT/INOUT

- PARAMETER-NAME

- DATA : DATA TYPE/SIZE

 

CREATE PROCEDURE UPDATE_EMPLOYEE_SAL( IN p_EMPID INTEGER,

                                                                        IN p_PER    DECIMAL(4,2),

                                                                        OUT p_UPDATE_SAL   )...

 

V.7 Parameter가 없는 경우 반드시 ()를 코딩하여야 한다

CREATE PROCEDURE UPDATE_EMPLOYEE_SAL( ) LANGUAGE SQL BEGIN...

 

V.8 Parameter가 없는 경우 ()를 생략할수 있다

CREATE PROCEDURE UPDATE_EMPLOYEE_SAL BEGIN...

 

Parameter 수가 다를 경우, 동일한 Name을 가진 다수의 Procedure를 생성할 수 있다.  이를 “PROCEDURE OVERLOADING”이라 한다

 

NOTE : Parameter수가 같고, Data Type이 틀려도 동일한 Name을 가진 Procedure를 생성할 수 없다. User Defined Function(UDF)는 동일한 수의 Parameter를 가진 이름이 같은 Function을 생성할 수 있다.

 

1) Procedure sum with three parameters

 

CREATE PROCEDURE sum( IN p_a INTEGER, IN  p_b INTEGER,OUT p_s INTEGER)

SPECIFIC sum_ab

LANGUAGE SQL

BEGIN

      SET p_s = p_s+p_b;

END

 

2) Procedure sum with four parameters

 

CREATE PROCEDURE sum(IN p_a INTEGER,IN p_b INTEGER,IN p_c INTEGER,OUT p_s INTEGER)

SPECIFIC sum_abc

LANGUAGE SQL

BEGIN

      SET p_s = p_s+p_b+p_c;

END

 

3) procedure 실행

 

CALL sum(100,200,?)

CALL sum(100,200,300,?)

 

 

4 Specific Name

 

SPECIFIC은 PROCEDURE의 UNIQUE NAME을 정의할 수 있는 선택절(OPTIONAL)이다.

SPECIFIC NAME은 PROCEDURE OVERLOADING을 동일한 이름과 Parameter 수를 다르게 하여 정의된 경우 유용하게 된다

즉 OVERLOADING Procedure를 Drop하거나 Comment를 할 경우 Procedure Name을 사용하는 경우 그 모호성 때문에 에러를 발생하게 된다

 

   TIP : Procedure의 관리를 용이하게 하기 위하여 SPECIFIC NAME을 사용하기를 권장합니다

 

    1) DROP PROCEDURE SUM

                  SQLCODE-476(SQLSTATE 42725)

    

    2) DROP PROCEDURE SUM (INTEGER,INTEGER,INTEGER)

 

    3) DROP SPECIFIC PROCEDURE sum_ab

 

COMMENT ON SPECIFIC PROCEDURE sum_abc IS ‘THIS IS THE 3 PARM VERSION OF THE PROCEDURE’

 

Specific Name은 18자 이상을 정의 할 수 없다. Scheme Name에 의해 QUALIFIED또는 UNQUALIFIED 될 수 있다. Specific Name이 Procedure Name과 동일하게 정의 할 수 있으며, Qualified Specific Name은  Unique하여야 한다. Specific Name이 Procedure생성시 명시 되지 않으면 SQL과 Timestamp으로 구성된 Unique한 Specific Name을 생성한다. “SQLyymmddhhmmsshhn"

 

5 DYNAMIC RESULT SETS

 

DYNAMIC RESULT SETS 절은 Return하고자 하는 최대 Result Set의 수를 정의할 때 사용된다. 이전 DB2 Version과의 호환성을 위해 DYNAMIC은 생략 될 수 있다. 그러나 DYNAMIC을 사용하는 것이 표준으로 권장됩니다

 

6 CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA

 

SQL-data access indication절은 Procedure내에서 사용되는 SQL statement Type을 제한 한다. “MODIFIES SQL DATA” 절을 기본값으로 하여 SQL statement를 사용할 수 있는 최소한의 제한을 한다

 

- CONTAINS SQL : Data를 읽거나 수정하는 것을 제외한 문장을 허용한다.

  ex) DECLARE GLOBAL TEMPORARY TABLE

          PREPARE

          SET special register

          SQL control statements 등

 

- READS SQL DATA : Data를 수정하는 것을 제외한 문장을 허용한다

- MODIFIES SQL DATA : CONNECT / DISCONNECT / RELEASE CONNECT 등

   Session 관련 문장을 제외한 SQL

 

7 DETERMINISTIC or NOT DETERMINISTIC

 

Input Parameter에 의해 동일한 결과 값을 반환하는 경우는 “DETERMINISTIC”, Input Parameter 또는 current date ,  time에 의해 값이 바뀌는 경우 “NOT DETERMINISTIC”을 사용한다. 기본값은 “NOT DETERMINISTIC”이 적용된다

 

8 CALLED ON NULL INPUT

 

Input Parameter가 Null인 경우에도 호출될 수 있음을 묵시적으로 알린다. 이 값은 기본적으로 적용되며, 적용될 수 있는 유일한 값이다. 선택적으로 기술 할 수 있다

 

9 LANGUAGE SQL

 

LANGUAGE SQL은 SQL Procedure를 의미하며, CREATE PROCEDURE Statement body의 내용이 SQL Procedure임을 알린다. LANGUAGE SQL은 V.7에는 반드시 기술하여야 하며, V.8은 선택적으로 사용할 수 있다. Java 또는 C 등 다른 언어로 작성할 경우 LANGUAGE keyword를 반드시 사용하여야 한다

 

10 SQL Procedure Body

 

SQL Statement는 단문 또는 복문 형태(Compound SQL)의 SQL 로 기술 된다

단일 SQL문장 또는 BEGIN~END Block내에 복합 SQL문장을 사용하여 정의할 수 있다. 모든 선언문을 SQL procedure statement 이전에 기술되어야 한다

다음은 선언에 필요한 Syntax Diagram을 표현한 예 이다

   

10.1 Comments

 

두가지 형태의 주석문을 허용한다

1) --

2) /* */

 

  CREATE PROCEDURE proc(IN p_a INTEGER,    --input var1

                                      OUT p_s INTEGER)   --output var1

  SPECIFIC sum_ab

  LANGUAGE SQL                                          --creating SQL procedure

  BEGIN                                                        --this is the beginning of the stored procedure

  /*

    varibables

  */     

  --procedural logic

 

  END

 

10.2 Variables

 

DECLARE 문을 사용하여 변수를 정의할 수 있다. 각 변수의 정의는 변수 name, DB2 data type 및 선택적인  Default값으로 구성된다. 변수는 반드시 BEGIN~END사이의 처음에 선언되어야 한다. 또한 변수 name은 Procedure에 사용된 Table의 Column명과 구별 되어야 한다.

 

  1) v_ :  변수정의 접두어

  2) p_ : parameter접두어

 

변수가 선언되면 NULL로 초기화 되며, DEFAULT절을 이용하여 특정 값으로 초기화 할 수 있다

 

 10.2.1  Setting Variables

 

   1)  DEFAULT값을 이용한 Setting

      DECLARE v_Total INTEGER DEFAULT 0;

 

  2) SET을 이용한 Setting

      SET v_total = v_total+1;

 

  3) Select를 이용한 Setting

      SELECT MAX(EMPNO) INTO v_Max FROM EMP

 

  4) VALUES를 이용한 Setting

     VALUES CURRENT DATE INTO v_adate;

     VALUES CURRENT DATE,CURRENT DATE INTO v_adate,v_other;

 

  5) GET DIAGNOSTICS을 이용한 SETTING - ROW COUNT RETURN_STATUS

      DELETE FROM EMPOLYERR WHERE...

      GET DIAGNOSTICS rcount = ROW_COUNT;

 

10.3 SQL Statement

 

다음을 제외한 문장을 사용할 수 있다

   - connect

   - CREATE any object other than indexes,tables or views

   - describe

   - disconnect

   - drop any object other than indexes,tables or views

   - flush event monitor

   - refresh table

   - release (connection only)

   - rename table

   - rename tablespace

   - revoke

   - set connection

   - set integrity 등

 

10.4 Special Register

 

환경변수를 제공하여 주는 DB2  Memory register 변수

  - current date

  - current time

  - current timestamp

  - current timezone

  - current path

  - current schema

  - current degree(data type : char(5))

  - current query optimzation(data type : integer)

  - current refresh age

  - current node

  - current server

  - current default transform group

  - current explain mode

  - current explain snapshot

  - user

 

10.5 Returning Values to Caller

 

RETURN을 사용하여 하나의 Integer값을 반환할 수 있다. 하나 이상 또는 다른 Data type을 사용하려면 output parameter를 사용한다

 

10.6 ROLLBACK and COMMIT

 

 1) ROLLBACK :  UOW(UNIT OF WORK)에 대한 변경을 취소한다

 2) COMMIT    :  UOW(UNIT OF WORK)에 대한 변경을 저장한다

 

SAVEPOINT & ROLLBACK TO SAVEPOINT

  --> ATOMIC compound statement내에서 ROLLBAKC/COMMIT을 사용할 수 없다

 

데이타베이스 요청이 실패할 때 dbms에 의해 작업을 실행 취소하는 메카니즘,

SAVEPOINT는 비원자적 데이터베이스요청을 ATOMIC 동작하게 만든다

실행중에 오류가 발생하면 SAVEPOINT를 사용하여 SAVEPOINT가 시작된 시간과 SAVEPOINT구간 복원이 요청된 시간 사이에 TRANSACTION에 의해 변경된 사항을 실행 취소 할수 있다

 

1. SAVEPOINT

   - SAVEPOINT를 설정하려면 SAVEPOINT의 이름을 사용하여 SAVEPOINT SQL문을 실행하여

     지정한다

   EX) SAVEPOINT savepoint1 ON ROLLBACK RETAIN CURSORS;

 

2.RELEASE SAVEPOINT

   - SAVEPOINT를 해제하려면 RELEASE SAVEPOINT SQL문을 실행한다

   EX) RELEASE SAVEPOINT savepoint1;

 

3. ROLLBACK TO SAVEPOINT

   - SAVEPOINT 구간을 복원하려면 ROLLBACK TO SAVEPOINT SQL 문을 실행한다

  EX) ROLLBACK TO SAVEPOINT savepoint1;