Anonymous PL Block

Anonymous PL block은 PSM 구문을 database에 저장하지 않고 일회성으로 실행하기 위한 SQL 구문이다. GOLDILOCKS에서 제공하는 정규 SQL 중 하나이기 때문에, GOLDILOCKS에서 제공하는 ODBC, JDBC 또는 precompiler에서도 다른 SQL과 동일하게 사용할 수 있다. 문법은 일반 basic block 구문과 동일하다.
자세한 내용은 Block (BEGIN .. END)을 참조한다.

<<Label>> 1 (optional)
DECLARE   2 (optional)
3 declare items (variables, cursors, types, ...) (optional)
BEGIN     4 (required)
5 PSM statements to execute (required)
EXCEPTION 6 Exception Handling Part (optional)
END;

Anonymous PL block을 사용할 때는 다음과 같은 점에 유의해야 한다.

Nested Procedure

Nested procedure는 특정 PL block 내부에 선언된 procedure 타입의 subprogram이다. Nested procedure는 선언된 PL block과 그 하위에서만 참조 가능한 scope를 가진다. 
자세한 내용은 Procedure Declaration and Definition을 참조한다.

DECLARE
  PROCEDURE PROC1( A1 INTEGER )  1 Define nested procedure
    IS   
    BEGIN
      DBMS_OUTPUT.PUT_LINE( 'A1 = ' || A1 );
    END; 
BEGIN
  PROC1( 100 );  2 Call nested procedure
END;
/

Nested subprogram 내부에서 사용 가능한 item들은 다음과 같다.

• Nested subprogram의 인자 (argument) 변수

• Nested subprogram이 정의된 PL block과 그 상위 scope에 정의된 변수와 각종 item들 (type, cursor,...)

• Anonymous PL block의 경우에는 bind parameter (예: '?', ':V1' 등)

Nested procedure는 forward declaration을 지원하기 때문에 declare와 define 구문을 따로 기술할 수 있다. 이를 이용하면 두 개의 nested procedure 사이에 상호 호출할 수 있는 로직을 구현할 수 있다.

gSQL> DECLARE

  PROCEDURE PROC1( A1 INTEGER );  1 declare proc1

  PROCEDURE PROC2( A1 INTEGER )   2 define  proc2
  IS
  BEGIN
    IF A1 > 0 THEN
      DBMS_OUTPUT.PUT_LINE( '(proc2)A1 = ' || A1 );
      PROC1( A1 -1 );  3 call proc1
    END IF;
  END; 

  PROCEDURE PROC1( A1 INTEGER )   4 define  proc1
  IS
  BEGIN
    IF A1 > 0 THEN
      DBMS_OUTPUT.PUT_LINE( '(proc1)A1 = ' || A1 );
      PROC2( A1 -1 );  5 call proc2
    END IF;
  END; 

BEGIN
  PROC1(5);  6 call proc1
END;
/
 
(proc1)A1 = 5
(proc2)A1 = 4
(proc1)A1 = 3
(proc2)A1 = 2
(proc1)A1 = 1

Anonymous PL block executed.

GOLDILOCKS PSM은 무한대의 상호참조를 방지하기 위해 최대 child statement depth를 50 개로 제한하고 있다. 이를 초과하면 다음과 같은 오류가 발생한다. (Nested function, schema-level procedure/ function에 동일하게 적용된다.)

gSQL> DECLARE
  PROCEDURE PROC1( A1 INTEGER );  1 declare proc1
  PROCEDURE PROC2( A1 INTEGER )   2 define  proc2
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( 'A1 = ' || A1 );
    PROC1( A1 -1 );
  END;
  PROCEDURE PROC1( A1 INTEGER )   3 define  proc1
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( 'A1 = ' || A1 );
    PROC2( A1 -1 );
  END;
BEGIN
  PROC1(100);
END;
/

ERR-42000(16411): maximum number of recursive SQL levels (50) exceeded.

Nested Function

Nested function은 nested procedure와 동일하지만 함수 형태를 가지는 subprogram이다.
자세한 내용은 Function Declaration and Definition을 참조한다.

gSQL> DECLARE
  V1 INTEGER := 0;
  FUNCTION FUNC1( A1 INTEGER )
    RETURN INTEGER
    IS   
    BEGIN
      RETURN A1 * 10;
    END; 
BEGIN
  V1 := FUNC1( 10 );
  DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
END;
/

V1 = 100

Anonymous PL block executed.

Nested function은 visible scope 내에 있는 모든 PSM expression에서 사용할 수 있지만, SQL 구문 안에서는 사용할 수 없다.

gSQL> DECLARE
  V1 INTEGER := 0;
  FUNCTION FUNC1( A1 INTEGER )
    RETURN INTEGER
    IS
    BEGIN
      RETURN A1 * 10;
    END;
BEGIN
  SELECT FUNC1(10) INTO V1 FROM DUAL;
  DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
END;
/

ERR-HY000(17032): PSM compilation error : 
(1) at (10:3): ERR-HY000(17079): a nested function not allowed in executing SQL

Schema-level Procedure

Schema-level subprogram은 database에 저장되는 이름을 가진 SQL 객체이다. Schema-level procedure는 procedure 형태의 schema-level subprogram이다.

생성

Schema-level procedure는 다음과 같이 생성된다. 필요할 경우, 인자들의 타입에 precision과 scale 값을 명시해야 한다. 
자세한 내용은 CREATE PROCEDURE를 참조한다.

CREATE OR REPLACE PROCEDURE PROC1( A1 INTEGER, A2 INTEGER )
IS  
  V1 INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO V1
    FROM T1
    WHERE T1.I1 >= A1 AND T1.I1 <= A2; 
  DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
END;
/

생성된 schema-level procedure에 대한 정보는 INFORMATION_SCHEMA.ROUTINES 테이블로 확인할 수 있다.
자세한 내용은 ROUTINES를 참조한다.

gSQL> SELECT SPECIFIC_NAME, ROUTINE_DEFINITION
  FROM  INFORMATION_SCHEMA.ROUTINES
  WHERE SPECIFIC_NAME = 'PROC1';

SPECIFIC_NAME ROUTINE_DEFINITION                                   
------------- -----------------------------------------------------
PROC1         PROCEDURE "PUBLIC"."PROC1" ( A1 INTEGER, A2 INTEGER )
              IS                                                   
                V1 INTEGER;                                        
              BEGIN                                                
                SELECT COUNT(*)                                    
                  INTO V1                                          
                  FROM T1                                          
                  WHERE T1.I1 >= A1 AND T1.I1 <= A2;               
                DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );             
              END;                                                 
                                                                   

1 row selected.

인자 (argument)에 대한 정보는 INFORMATION_SCHEMA.PARAMETERS 테이블로 확인할 수 있다.
자세한 내용은 PARAMETERS를 참조한다.

gSQL> SELECT P.PARAMETER_NAME, P.ORDINAL_POSITION
  FROM  INFORMATION_SCHEMA.ROUTINES     R,
        INFORMATION_SCHEMA.PARAMETERS   P
  WHERE R.SPECIFIC_NAME = 'PROC1'
    AND R.SPECIFIC_SCHEMA = P.SPECIFIC_SCHEMA
    AND R.SPECIFIC_NAME = P.SPECIFIC_NAME
  ORDER BY P.ORDINAL_POSITION;

PARAMETER_NAME ORDINAL_POSITION
-------------- ----------------
A1                            1
A2                            2

2 rows selected.

사용

Schema-level procedure는 다른 PSM의 내부 구문이나 CALL 구문에 의해 사용된다.
다른 PSM 구문은 nested procedure와 같은 형식으로 schema-level procedure를 호출한다.

gSQL> BEGIN
  PROC1( 2, 4 );  1 call schema-level procedure
END;
/

V1 = 3

Anonymous PL block executed.

주어진 PSM을 실행하는 SQL인 CALL 구문은 다음과 같이 실행된다.
자세한 내용은 CALL Statement를 참조한다.

gSQL> CALL PROC1( 2, 4 );
V1 = 3

Procedure Call complete.

ODBC나 JDBC에서 사용하는 procedure call escape sequence를 지원하기 위해 procedure에 대한 다음 구문을 지원한다.

{ CALL procedure_name( param1, param2, ... ) }

Procedure call escape sequence 구문은 일반 SQL처럼 사용할 수 있다.

gSQL> { CALL PROC1(2, 4) };
V1 = 3

Procedure Call complete.

GOLDILOCKS의 interactive command tool인 gsql은 tool 자체 명령어인 \EXEC를 통한 procedure 수행을 지원하지 않는다.

Schema-level procedure는 실행 시 권한에 대한 다음 옵션을 명시할 수 있다. Definer가 아닌 사용자가 이 옵션들에 따라 PSM내의 SQL을 수행하면 해당 사용자의 schema-path 정의에 따라 서로 다른 schema의 이름이 같은 테이블을 참조할 수도 있다. (Item을 선언할 때 사용된 객체명 (예: T1%ROWTYPE)은 항상 definer로 해석된다.)

• AUTHID DEFINER (default): 해당 procedure를 작성한 사용자로 변경된 후 수행된다.

• AUTHID CURRENT_USER: 수행하는 사용자를 변경하지 않고 현재 사용자에 의해 수행된다.

CREATE OR REPLACE PROCEDURE "PROC1"( A1 INTEGER, A2 INTEGER )
AUTHID CURRENT_USER
IS
  V1 INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO V1
    FROM T1
    WHERE T1.I1 >= A1 AND T1.I1 <= A2;
    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
END;
/

제거

Schema-level procedure는 다음 DROP PROCEDURE 구문으로 제거한다.
자세한 내용은 DROP PROCEDURE를 참조한다.

DROP PROCEDURE PROC1;

GOLDILOCKS의 다른 DROP 구문들처럼 IF EXISTS 구문도 지원한다.

DROP PROCEDURE IF EXISTS PROC1;

Recompile

Schema-level subprogram 내부에서 참조된 객체가 변경되면 해당 subprogram도 영향을 받아 실행 플랜을 다시 생성해야 할 수도 있다.

선언부나 인자에서 참조된 객체

선언부의 각종 item을 정의하거나 인자 타입을 정의할 때 사용된 객체가 변경되면 procedure의 플랜이 자동으로 recompile 된다.

• %TYPE, %ROWTYPE에 사용된 객체

• Explicit cursor 정의 구문에 사용된 객체

객체가 변경된 후 처음으로 해당 procedure가 수행되면 다음 순서에 따라 자동으로 플랜을 다시 생성한다.

1. Plan cache로부터 해당 procedure의 플랜을 가져온다

2. 해당 플랜의 객체 리스트를 validation하는 중에 변경된 객체를 찾는다.

3. 현재 플랜을 discard 하고, dictionary에 저장된 procedure 정의 구문으로부터 새로운 플랜을 생성한다.

4. 새로 생성된 플랜을 plan cache에 등록한다.

5. 플랜을 실행한다.

Body의 SQL에서 참조된 객체

Procedure의 플랜에는 body에 사용된 SQL의 플랜이 저장되지 않고 SQL text만 저장된 상태이다. Procedure를 실행할 때 해당 SQL 구문을 실시간으로 compile 하여 플랜을 생성한 후 수행한다. 따라서 body 내에서 사용된 SQL 객체는 procedure의 플랜 자체에 영향을 주지 않는다.

단, 객체가 변경되면 procedure의 기존 interface인 바인드 개수나 타입이 변경되거나 column이 삭제될 수 있으므로 procedure까지 적절하게 변경하지 않으면 실행할 때 오류가 발생할 수 있다.

Schema-level Function

Schema-level function은 expression 내에서 사용되는 함수 형태의 schema-level subprogram이다.

생성

Schema-level function은 다음과 같이 생성된다. 필요할 경우, 인자들의 타입에 precision과 scale 값을 명시해야 한다. 
자세한 내용은 CREATE FUNCTION을 참조한다.

gSQL> CREATE OR REPLACE FUNCTION FUNC1( A1 INTEGER, A2 INTEGER )
RETURN INTEGER
IS
  V1 INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO V1
    FROM T1
    WHERE T1.I1 >= A1 AND T1.I1 <= A2;
  RETURN V1;
END; 
/

Function created.

생성된 schema-level function은 procedure와 마찬가지로 INFORMATION_SCHEMA.ROUTINES와 INFORMATION_SCHEMA.PARAMETERS 테이블에서 확인할 수 있다.

사용

Schema-level function은 일반 SQL이나 PSM 내의 SQL 객체가 사용될 수 있는 모든 expression에서 사용할 수 있다.

gSQL> SELECT FUNC1( 2, 4 ) FROM DUAL;

FUNC1( 2, 4 )
-------------
            3

1 row selected.

PSM 내에서도 다음과 같이 사용된다.

gSQL> DECLARE
  V1 INTEGER;
BEGIN
  V1 := FUNC1( 2, 4 );
  DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
END;
/

V1 = 3

Anonymous PL block executed.

다음과 같이 CALL 구문을 이용하여 수행할 수 있다.

자세한 내용은 CALL Statement를 참조한다.

gSQL> \var V1 INTEGER
gSQL> CALL FUNC1( 2, 4 ) INTO :V1;

Procedure Call complete.

gSQL> \print V1

V1
--
 3

Schema-level procedure와 마찬가지로 procedure call escape sequence 구문도 지원한다.

{ ? = CALL function_name( param1, param2, ... ) }

gSQL> { :V1 = CALL FUNC1( 2, 4 ) };

Procedure Call complete.

gSQL> \print V1

V1
--
 3

제거

Schema-level function은 다음 DROP FUNCTION 구문으로 제거한다.
자세한 내용은 DROP FUNCTION을 참조한다.

gSQL> DROP FUNCTION FUNC1;

Function dropped.

Built-in Procedures

GOLDILOCKS PSM은 procedure와 function을 구현할 때 디버깅하거나 exception을 처리하기 위해 다음과 같은 built-in procedure들을 제공한다.

gSQL> DECLARE
V1 VARCHAR(1024);
V2 INTEGER;
BEGIN
  DBMS_OUTPUT.ENABLE(2000);
  DBMS_OUTPUT.PUT_LINE('TEST MSG');
  DBMS_OUTPUT.GET_LINE( V1, V2);
  DBMS_OUTPUT.PUT_LINE('V1 = ' || v1);
  DBMS_OUTPUT.PUT_LINE('V2 = ' || v2);
END;
/

V1 = TEST MSG

V2 = 0

Anonymous PL block executed.

메시지 로깅 기능은 세션별로 관리되며, GOLDILOCKS의 interactive command tool인 gsql에서는 serveroutput 옵션으로 메시지 로깅 기능을 켜고 끌 수 있다. PSM을 수행한 후에 메시지 버퍼에 내용이 있으면 모두 자동으로 출력된다.

gSQL> \set serveroutput on
gSQL> \var msg VARCHAR(4000)
gSQL> \var status NATIVE_INTEGER
gSQL> CALL DBMS_OUTPUT.PUT_LINE( 'aaa' );
aaa

Procedure Call complete.

gSQL> CALL DBMS_OUTPUT.PUT_LINE( 'bbb' );
bbb

Procedure Call complete.

gSQL> CALL DBMS_OUTPUT.GET_LINE( :msg, :status );

Procedure Call complete.

gSQL> \print msg

MSG 
----
null

gSQL> \print status

STATUS
------
     1