[GOLDILOCKS] SQL/PSM, PL/SQL, PROCEDURE, FUNCTION 선언 및 사용법 알아보기

GOLDILOCKS 는 SQL/PSM 을 지원합니다.

SQL/PSM 이란

1. 프로그래밍 언어 기능이 포함된 SQL 입니다.

2. 트랜잭션을 단위별로 수행할 수 있는 SQL 입니다.

3. 제어흐름, 예외처리, 지역변수, 커서의 절차적 이용을 위한 문법등을 표준화한 SQL 입니다.

4. SQL/PSM 은 PL/SQL 혹은 PL/pgSQL 과 비슷한 언어입니다.

SQL/PSM 기본구조

DECLARE
    변수 선언;
BEGIN
    코드;
[EXCEPTION
    예외처리;]
END;
/

변수 선언 : GOLDILOCKS 에서 지원하는 데이터 타입을 작성할 수 있습니다.

코드 : SQL/PSM 을 작성합니다.

예외처리 : 코드 수행시 발생하는 예외처리를 작성합니다.

예시]

gSQL> set serveroutput on;
gSQL> DECLARE
    2   MOZI VARCHAR(10);
    3 BEGIN
    4   MOZI := 'GOLDILOCKS';
    5   DBMS_OUTPUT.PUT_LINE(MOZI);
    6 END;
    7 /
GOLDILOCKS

Anonymous PL block executed.

위 SQL/PSM 은 수행할 때마다 버퍼에서 동작되기 때문에 매번 작성하여야 합니다.

GOLDILOCKS 에서는 위 구문을 캡슐화하여 데이터베이스에 저장가능한 Stored Function, Stored Procedure 을 지원합니다.

Stored Function 구조, 사용법

CREATE [OR REPLACE] FUNCTION 함수명 [인수]
RETURN 타입 {AS | IS}
  변수선언;
BEGIN
  코드;
  RETURN
    변수;
[EXCEPTION
  예외처리;]
END;
/

기본문법에서 객체로 저장할수 있는 CREATE FUNCTION 구문이 추가되었습니다.

인자를 받을 수 있습니다.

값을 반환할 수 있습니다.

예시]

gSQL> set serveroutput on;
gSQL> CREATE OR REPLACE FUNCTION TISTORY
    2 RETURN VARCHAR(10) AS
    3   MOZI VARCHAR(10);
    4 BEGIN
    5   MOZI := 'GOLDILOCKS';
    6   RETURN MOZI;
    7 END;
    8 /

Function created.

gSQL> SELECT TISTORY FROM DUAL;

TISTORY
----------
GOLDILOCKS

1 row selected.

Stored Procedure 구조, 사용법

CREATE [OR REPLACE] PROCEDURE 프로시져명 [인수]
{AS | IS}
BEGIN
  코드;
[EXCEPTION
  예외처리;]
END;
/

기본문법에서 객체로 저장할 수 있는 CREATE PROCEDURE 구문이 추가되었습니다.

인자를 받을 수 있습니다.

값을 반환할 수 없습니다.

예시]

gSQL> SELECT * FROM MOZI;

  C1   C2
---- ----
   1    1

1 rows selected.

gSQL> CREATE OR REPLACE PROCEDURE TISTORY (NUM IN INTEGER)
    2 AS
    3 BEGIN
    4   UPDATE MOZI SET C2=2 WHERE C1=NUM;
    5 END;
    6 /

Procedure created.

gSQL> CALL TISTORY (1);

Procedure Call complete.

gSQL> SELECT * FROM MOZI;

  C1   C2
---- ----
   1    2

1 rows selected.

Nested Function, Nested Procedure 구조, 사용법

DECLARE
    변수 선언;
    함수;
    프로시져;
BEGIN
    코드;
[EXCEPTION
    예외처리;]
END;
/

DECLARE 와 BEGIN 사이에 Function, Procedure 를 사용할 수 있습니다.

이 때 구문은 CREATE 와 / 를 뺀 나머지를 작성해 주면 됩니다.

예시]

gSQL> DECLARE
    2   FUNCTION F_TISTORY
    3   RETURN VARCHAR(10) AS
    4     MOZI VARCHAR(10);
    5   BEGIN
    6     MOZI := 'GOLDILOCKS';
    7     RETURN MOZI;
    8   END;
    9
   10   PROCEDURE P_TISTORY (NUM IN INTEGER)
   11   AS
   12   BEGIN
   13     UPDATE MOZI SET C2=2 WHERE C1=NUM;
   14   END;
   15 BEGIN
   16   DBMS_OUTPUT.PUT_LINE(F_TISTORY);
   17   P_TISTORY(1);
   18 END;
   19 /
GOLDILOCKS

Anonymous PL block executed.