SQL/PSM이란?

SQL/PSM( SQL/Persistent Stored Modules )은 절차적 SQL로 Syntax와 Semantics을 International Standard ISO/IEC 9075에서 정의하고 있다.

SQL/PSM은 아래와 같은 조건을 포함한다.

• control statement를 지원
• paramter 또는 variable에 표현식의 결과를 할당 가능
• 예외 처리를 지원
• Standing Cursor 선언 가능
• local variable 선언 가능

각 벤더사 별 SQL/PSM

SQL 표준 문서에서는 DBMS 개발 시 선택 사항이다. 따라서, 표준에서 언급하는 기능은 있으나 각 DBMS 벤더마다 syntax의 차이는 있다. 각 벤더마다 아래와 같이 SQL/PSM 기능을 지원하고 있다.

• Oracle : PL/SQL
• PostgresQL : PL/pgSQL
• DB2 : PL/SQL
• MySQL & MariaDB : Stored Objects

PL/SQL

> 본 문서에서는 대중적으로 사용되고 있는 Oracle의 PL/SQL을 기준으로 서술하도록 한다.

객체

PL/SQL의 객체는 아래와 같다.

• Procedure
• Fucntion
• Package
• Library( External Procedure의 Library )
• User Defined TYPE
• etc.

PL/SQL BLOCK

PL/SQL의 기본 구조는 BLOCK이다. BLOCK은 bdeclare section과 body로 구성된다.

• Declare Section : local item을 선언한다.
• Body : 실행 가능한 statement들과 예외 처리를 하기 위한 exception block으로 구성한다.
  

Declare Section

Declare section에서 local item을 선언한다. Oracle의 PL/SQL에서는 아래와 같은 item들을 선언 및 정의할 수 있다.

• item declaration
  • variable declaration
  • constant declaration
  • collection variable declaration
  • record variable declaration
  • reference cursor variable declaration
  • exception declaration
• type definition
  • collection type definition
    • associative array type definition
    • vaaray type definition
    • nested table type definition
  • record type definition
  • reference cursor type definition
  • subtype definition
• explicit cursor declaration 및 definition
• subprogram( procedure, function ) declaration 및 definition

Body

Body는 실행 가능한 statement와 Error Handling을 위한 Exception Block으로 구성된다. Excpetion Block에서 역시 실행 가능한 statement를 명시 할 수 있다.

실행 가능한 statement는 아래와 같다.

• assignment_statement
• basic_loop_statement
• while_loop_statement
• for_loop_statement
• forall_statement
• cursor_for_loop_statement
• if_statement
• case_statement
• continue_statement
• goto_statement
• exit_statement
• open_statement
• open_for_statement
• fetch_statement
• close_statement
• sql_statement
  • insert_statement
  • update_statement
  • delete_statement
  • merge_statement
  • lock_table_statement
  • commit_statement
  • rollback_statement
  • savepoint_statement
  • set_transaction_statement
  • collection_method_call
• select_into_statement
• execute_immediate_statement
• null_statement
• pipe_row_statement
• return_statement
• raise_statement
• plsql_block

PL/SQL DataType

PL/SQL에서 지원하는 Datatype은 DB에서 지원하는 datatype도, row형태를 지원하는 %ROWTYPE, %TYPE으로 다른 Variable이나 table의 column의 속성을 참조하여 datatype으로 표현할 수 있는 Attribute Type이 있다.

DECLARE
  var1 INTEGER;     -- DB Type
  var2 t1%ROWTYPE;  -- %ROWTYPE : row 형식으로 지원하며, 
                    --datatype은 참조한 table의 column의 datatype과 동일
  var3 t1.c1%TYPE;  -- %TYPE : datatype은 참조한 table의 column의 datatype과 동일
BEGIN
  NULL;
END;
/

Oracle PL/SQL에서 DB에서 지원하는 datatype의 maximum 값은 SQL에서의 maximum 값과 다르다. 아래의 그림은 Oracle 21c 메뉴얼의 일부이다.

Example

마지막으로 간단한 PL/SQL의 예제이다.

DECLARE
  v_numerator   NUMBER := 22;
  v_denominator NUMBER := 7;
  v_division    NUMBER := 0;
BEGIN
  v_division = numerator/denominator
  
  DBMS_OUTPUT.PUT_LINE('Ratio with denominator = ' || v_division); 
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Divide-by-zero error: can''t divide '
        || numerator || ' by ' || denominator);
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error.');
END;
/