[SQLD/P] 절차형SQL 기초 개념

1. 개요

SQL을 하다보면 많이 들어봤을 프로시저(Procedure), 트리거(trigger), 사용자 정의 함수(User Defined Function)는 모두 절차형SQL에 속하는 방법론적인 개념들이다.

절차형SQL은 말 그대로 SQL을 절차적으로, SQL의 연속적인 실행과 분기처리를 가능한 SQL 구문을 일컫는다. 이 구문, 다시 말해 절차 실행을 가능하게 해주는 방법에는 사용자 정의 함수, 프로시저, 트리거가 있는 것이다.

해당 구문들은 DB Server에 저장하여 호출해서 실행하거나 독립적으로 실행할 수 있어서 저장모듈이라고도 하고, Oracle에서는 PL/SQL이라고도 한다.

2. 종류

위에서 기술하였듯이 절차형SQL에는 종류가 3가지가 있지만, 공통적으로 하나의 Block 구조로 되어있어 Block 단위로 SQL을 실행한다.

즉, 한 Block안에 모듈이 몇개가 있든 상관없이 일괄적으로 실행하고 실행 결과를 반영한다.

2-1. 프로시저

프로시저는 기본적으로 선언부 - 실행부로 나눌 수 있다.

이 선언부는 프로시저의 이름과 실행할 프로시저 내부에서 사용할 변수를 선언하는 영역으로 나눌 수 있고, BEGIN ~ END 영역을 통해 어떻게 실행할 것인지 나타내준다.

CREATE OR REPLACE PROCEDURE my_procedure
IS
	COLUMN = "DEPT_SALARY"
BEGIN 
	SELECT DEPT_NAME, DEPT_ID, COLUMN
    FROM DEPARTMENT
END;

EXEC p_test; --프로시저 호출

이후 프로시저를 호출하고 실행할때는 EXEC 키워드를 사용한다.

※ 프로시저의 인수가 있느냐 없느냐에 따라 IS(AS) 다음의 구문이 달라질 수 있다.

2-1-1. EXCE / EXEC IMMEDIATE

프로시저는 호출(실행)할 수 있는 구문은 환경에 따라 EXEC, EXEC IMMEDIATE로 나눌 수 있다.

EXEC는 WATCOM SQL, EXEC IMMEDIATE는 TSQL이다.

일단 DDL이나 동적SQL에 대해서는 EXEC IIMEDIATE를 사용해야 하는 것과 두 구문의 option 차이가 존재한다는 것까지는 알았으나, 세부사항은 더 공부를 해봐야할 것 같다.

2-1-2. EXEC IMMEDIATE - 동적쿼리/DDL

일단 기본적으로 PL/SQL에서 DDL(TRUNCATE, CREATE 등)구문은 사용할 수 없으나 동적 쿼리에서는 사용가능하기 때문에, 이 동적쿼리를 실행하기 위한 목적으로 EXEC IMMEDATE를 사용한다.

동적쿼리란, 프로시저에 의해 정의한 변수를 사용하는 문자열 즉 쉽게 말해 변할 수 있는 쿼리를 의미한다.

CREATE OR REPLACE FUNCTION FUNC1(
    W_PRODUCT_ID IN NUMBER
)
RETURN VARCHAR2
IS
    PRODUCT_NAME VARCHAR2(100);
BEGIN
    EXECUTE IMMEDIATE
    'SELECT PRODUCT_NAME
    FROM
    PRODUCTS
    WHERE
    PRODUCT_ID = '||  W_PRODUCT_ID
    INTO PRODUCT_NAME;
    RETURN PRODUCT_NAME;
END; 

위 사용자 정의 함수 모듈을 보면, 사용자 정의 함수에서 return하는 PRODUCT_NAME 문자열을 쿼리에서 그대로 이용하는 것을 볼 수 있다.

이러한 형태의 쿼리가 바로 동적쿼리이다(반대말 : 정적쿼리).

2-1-3. AS/IS

환경에 따라 procedure를 작성하고 AS/IS 키워드를 다르게 사용하는 것을 볼 수 있다.

두 구문의 기능적 차이는 없고, 환경에 따른 차이이므로 일단 두 키워드는 서로 같은 것으로 간주하면 될 것 같다.

2-1-4. PRAGMA AUTONOMOUS_TRANSACTION

프로시저 내부에서 또 다른 프로시저를 호출할때, 그리고 이 내부적으로 호출한 프로시저가 부모 프로시저의 영향을 받지 않고 독립적으로 실행하기를 원할때 사용하는 구문이다.

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE1
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO ADDRESS(
                                        ADDRESS_ID
                                      , ZIP_CD
                        ) VALUES(
                                        CSV_SEQUENCE.NEXTVAL
                                      , 'A1' 
                        );
    COMMIT;
    --ROLLBACK;
END;

※ 링크

위 구문에서 PRAGMA AUTONOMOUS_TRANSACTION으로 설정하였으므로 해당 프로시저는 다른 프로시저의 호출시 독립적으로 실행하게 되는 구문, 즉 부모 프로시저에서 ROLLBACK하더라도 실행이 보장되는 SQL이다.

2-1-5. authid current_user

해당 프로시저를 실행할때 컴파일할때의 유저가 아닌, 현재 접속하고 있는 로그인 유저를 사용하는 구문이다.

CREATE OR REPLACE PROCEDURE INSERT_DEPT authid current_user
as
BEGIN
~~
END

2-2. 트리거

특정 테이블에서 INSERT, UPDATE, DELETE를 수행할 때 DBMS에서 자동으로 수행하도록 작성된 구문이다. 물론 로그인 조회 등의 상황에서도 작동할 수 있다.

구문은

CREATE TRIGGER

으로 시작하며, 프로시저와 달리 내부에서 트랜잭션을 실행할 수 없다.

트리거는 보통 변경작업에서 DBMS가 자동으로 호출하도록 지정하는 구문이기 때문에, 데이터 무결성과 일관성을 위해 사용하는 함수이다.

2-3. 사용자 정의 함수

말 그대로 사용자가 정의한 함수로, return(반환)의 개념이 있다.

반드시 반환값이 한건은 존재해야 한다.

CREATE OR REPLACE FUNCTION FUNCTION(
~~~~
)

3. 참고자료

절차형 SQL 기본 개념(절차형SQL과 저장형 모듈 개념의 차이) - https://eehoeskrap.tistory.com/81

프로시저 구문 작성(인수유무에 따른 분류) - https://goddaehee.tistory.com/163

EXEC / EXEC IMMEDIATE - https://sqlanywhere-forum.sap.com/questions/20031/execute-immediate-vs-execute

EXEC IMMEDIATE 세부항목들 - https://itteamb.blogspot.com/2020/04/oracle-procedure-execute-immediate.html

동적쿼리에 대한 개념 - https://coding-factory.tistory.com/457

프로시저 AS/IS의 차이 - http://daplus.net/oracle-oracle-%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C-%EC%8B%9C%EC%A0%80%EC%97%90%EC%84%9C-as%EC%99%80-is%EC%9D%98-%EC%B0%A8%EC%9D%B4%EC%A0%90%EC%9D%80-%EB%AC%B4%EC%97%87%EC%9E%85%EB%8B%88/

PRAGMA AUTONOMOUS_TRANSACTION(자율 트랜잭션) - https://go-coding.tistory.com/95

프로시저에서 사용하는 authid definer / authid current_user 구문의 차이 - https://hermeslog.tistory.com/288