[DB] 프로시저 (procedure)

1. 프로시저의 개념 

프로시저란 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

PL/SQL을 통해서 만들어지는 서브 프로그램이라고 생각하면 되는데, PL/SQL은 상용 관계형 데이터베이스 시스템인 오라클 DBMS에서 SQL 언어를 확장하기 위해 사용하는 컴퓨터 프로그래밍 언어 중 하나이다.

 

호출을 통해서 실행되어 SELECT, INSERT, UPDATE, DELETE 명령을 실행한다.

프로시저는 백단과 구별되어 있기 때문에 프로시저의 쿼리문을 수정한다고해도 백엔드에는 영향을 주지 않는다.

 

(실제로 사용해보니 함수와 유사하다.)

 

2. 프로시저의 구성

구성 요소
DECLARE	[선언부] 프로시저의 명칭, 변수와 인수, 데이터 타입 정의
BEGIN / END	[시작/종료부] 프로시저의 시작과 종료, BEGIN과 END는 쌍. 다수 실행을 제어하는 기본적 단위가 됨.
CONTROL	[제어부] 조건문과 반복문을 이용하여 문장 처리
SQL	[SQL] DML, 상황에 따라 DDL도 사용
EXCEPTION	[예외부] BEGIN ~ END에서 예외 발생 시 예외 처리 방법을 정의
TRANSACTION	[TRANSACTION] DML 수행 후 DBMS 적용 또는 취소 여부를 결정하는 처리부

 

3. 사용 예시

CREATE OR REPLACE PROCEDURE 프로시저 이름 (파라미터1, 파라미터2);

IS
변수

BEGIN
쿼리문

END 프로시저 이름;

// 사원 번호를 입력받아서 급여 인상해주는 프로시저

CREATE OR REPLACE PROCEDURE update_salary
( pEmployeeId IN NUMBER )

IS
BEGIN
	UPDATE employee
    SET salary = salary * 1.1
    WHRE employeeId = pEmployeeId;
    
END update_salary;

 

4. IN, OUT 매개변수

프로시저에서 값을 반환할 수 있는데 OUT 매개변수를 통해서 실현할 수 있다.

OUT 매개변수란 프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당한다.

반대로 IN 매개변수란 입력용 변수이다. IN OUT은 모두 가능하다.

 

OUT을 사용할 경우에는 DECLARE를 사용하고 선언해주면 된다. 

 

CREATE OR REPLACE PROCEDURE out_test
(
 	name OUT VARCHAR2,
    age OUT NUMBER
)

IS
BEGIN
	name :='test;
    age := 24;
    
    DBMS_OUTPUT.PUT_LINE ('out test');
END;   
    
DECLARE 
	VAR_NAME VARCHAR2(100);
    VAR_AGE NUMBER;
    
BEGIN 
	out_test(VAR_NAME, VAR_AGE);
END;

-> 프로시저에서 IS-BEGIN 사이에서는 쿼리문이 아니여도 사용이 가능하다.

 

참고)https://benggri.tistory.com/76