Database/SQL

[SQL] 프로시저 (Procedure)

Untitled_Blue 2024. 5. 15. 17:14
반응형

안녕하세요. 이번 글은 프로시저에 대한 설명입니다.

- 프로시저란?

  • 특정 처리 작업을 수행하기 위해 사용하는 저장 서브프로그램
  • 일정한 논리와 로직에 의거하여 데이터 처리 작업을 수행하는 역할 담당
CREATE [OR REPLACE] PROCEDURE [프로시저명]

IS [AS]
	[변수명] [자료형] := [값 또는 식];
BEGIN
	[내부 로직];
EXCEPTION
	[예외 시 실행 로직];
END;
/

프로시저의 구조는 다음과 같이 진행되며 각 구문에 대한 설명은 하단과 같다.

  • CREATE PROCEDURE : 프로시저를 생성하기 위한 구문이며 CREATE 뒤에 OR REPLACE를 추가하면 현재 작성한 내용으로 새로 대체되며 나중에 프로시저를 수정하는 작업할 때 주로 활용되고 있음
  • IS, AS, BEGIN : 선언부를 의미하며 선언부 내 변수를 지정하지 않아도 반드시 선언해야 함
  • EXCEPTION : 예외부를 의미하며 실행 중 예외 상황에 대처하기 위한 구간 (선택)
  • END : 프로시저의 끝을 의미
CREATE OR REPLACE PROCEDURE TEST_PROC
IS
    V_NAME VARCHAR2(100) := '김땡땡';
    V_DEPT VARCHAR2(150) := '컴퓨터공학과';
    V_AGE NUMBER(10) := 26;
BEGIN
    DBMS_OUTPUT.PUT_LINE('V_NAME : ' || V_NAME);
    DBMS_OUTPUT.PUT_LINE('V_DEPT : ' || V_DEPT);
    DBMS_OUTPUT.PUT_LINE('V_AGE : ' || V_AGE);
END;
/

상단의 쿼리문은 프로시저를 생성하는 역할을 담당하고 있다. 해당 쿼리문을 실행해보면 Console창에 completed in 218 ms 같이 생성 및 대체가 완료되었다는 알림과 함께 목록에서 프로시저가 정상적으로 반영된 점을 확인할 수 있다.

SET SERVEROUTPUT ON;
EXECUTE TEST_PROC;

오라클 SQL*PLUS 기준으로 상단처럼 실행하면 된다.

BEGIN
    SCOTT.TEST_PROC;
END;

PL/SQL 블록이나 별도의 DB툴에서는 상단과 같이 입력해서 실행하면 정상적으로 출력되는 점을 확인할 수 있다.

(필자는 Oracle 19.3 + DataGrip 2023.03 툴을 사용하고 있다.)

SELECT * FROM USER_SOURCE;

프로시저에 대한 전반적인 내용을 확인하기 위해서는 USER_SOURCE라는 데이터 사전을 확인하면 된다.

DROP PROCEDURE TEST_PROC;

프로시저를 삭제할 때는 DROP PROCEDURE [프로시저명]을 사용하면 된다.

CREATE OR REPLACE PROCEDURE [프로시저명] (
    [파라미터명] [IN | OUT | IN OUT] [자료형] [:= || DEFAULT 기본값],
    [파라미터명] [IN | OUT | IN OUT] [자료형] [:= || DEFAULT 기본값],
    ...
)

IS [또는 AS]
    [선언부]
BEGIN
    [수행 로직]
EXCEPTION
    [예외처리 로직]
END;
/

상단 쿼리문 구조는 파라미터(Parameter)를 사용하는 프로시저의 구조를 보여주고 있다. 이전 프로시저 구조와 다른 점이 있다면 프로시저를 실행하기 위한 값을 입력받는 구간인 파라미터의 존재 여부에서 차이점이 존재한다. 여러 개의 파라미터를 설정할 때는 세미콜론이 아닌 쉼표(,)로 구분하며 값을 입력받는 파라미터의 구조는 [파라미터명] [파라미터 모드] [자료형] [:= 또는 DEFAULT 기본값]이며 파라미터명과 자료형을 제외한 나머지 두 개는 선택사항이다.

파라미터 모드 설명
IN 값을 지정하지 않으면 기본값으로 입력받음 (생략 가능)
OUT 호출 시 값 반환
IN OUT 호출 시 값을 입력받고 실행 결과에서 값 출력

상단의 표는 파라미터 모드에 대한 설명을 담고 있다.

create OR REPLACE PROCEDURE TEST_PROC_PARAM (
    V_NAME IN VARCHAR2,
    V_GRADE IN NUMBER DEFAULT 2,
    V_AGE IN OUT NUMBER,

    V_RESULT OUT VARCHAR
)

AS
    V_CHECK NUMBER(10) := 0;

BEGIN
    SELECT COUNT(*) INTO V_CHECK
      FROM STUDENT_INFO
     WHERE STU_NAME = V_NAME
       AND STU_GRADE = V_GRADE;

    IF V_CHECK > 0 THEN
        DBMS_OUTPUT.PUT_LINE('해당 학생은 존재합니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('해당 학생은 없습니다.');
    END IF;

    V_RESULT := '결과 출력을 마칩니다.';

   DBMS_OUTPUT.PUT_LINE(V_RESULT || V_AGE);
END;
/

상단의 쿼리문은 프로시저에서 DECALRE 선언부에 IN, OUT, IN OUT 파라미터를 사용한 PL/SQL문이다. IN을 통해 실행에 필요한 값을 입력받도록 구현하였으며 OUT을 통해 실행 후 내부 로직을 거쳐서 출력되는 값을 확인할 수 있다. 그리고 IN OUT을 통해 입력과 출력에 대한 기능을 동시에 사용가능한 점을 확인할 수 있다.

DECLARE
    TEST_OUT VARCHAR2(100);
    AGE_IO NUMBER;
BEGIN
    AGE_IO := 20;
    SCOTT.TEST_PROC_PARAM('홍길동', 2, AGE_IO, TEST_OUT);

    DBMS_OUTPUT.PUT_LINE(TEST_OUT || ' ' || AGE_IO);
END;

상단의 쿼리문은 이전에 확인했던 TEST_PROC_PARAM 프로시저를 실행하기 위한 것이며 프로시저명([파라미터값1], [파라미터값2] ..)을 통해서 작동해야 되며 괄호 안에 선언해둔 파라미터 갯수대로 반드시 맞춰야 한다. 하지만 IN과 다르게 OUT과 IN OUT은 선언부에 [변수명] [자료형]; 방식으로 선언한 뒤 BEGIN에다 괄호 안에 입력해야 한다. 그 중에서 IN OUT은 IN의 기능도 가지고 있기 때문에 BEGIN 문에다 값을 대입해야 한다. 또한 OUT과 IN OUT에 대한 결과를 출력받기 위해서는 프로시저문에다가도 설정할 수 있으나 실행문에서도 DBMS_OUTPUT.PUT_LINE()을 통해서 출력할 수 있다.

반응형