[PL/SQL] 프로시저

1. 프로시저(Procedure)란? 

데이버테이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리시스템에 저장한 것으로 영구 저장 모듈(Persistens Storage Module)이라고도 불린다.

테이블에서 데이터를 추출해 조작하고 그 결과를 다른 테이블에 다시 저장하거나 갱신하는 일련의 처리를 할 때 주로 프로시저를 사용한다. 

보통 저장 프로시저를 프로시저라고 부르며, 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다. 
즉, 특정 작업을 위한 쿼리들의 블록이다.

2. 장점

• 하나의 요청으로 여러 SQL문을 실행시킬 수 있다.(네트워크 부하를 줄일 수 있다.)
  
  • 네트워크 소요시간을 감소시켜 성능이 개선된다.
• 여러 어플리케이션과 공유가 가능하다.(API처럼 제공가능)
• 기능 변경이 편리하다.(특정 기능을 변경해야 하는 경우 프로시저만 변경하면 된다.)

3. 단점

• 문자나 숫자 연산에 사용하면 오히려 C나 Java보다 느린 성능을 보일 수 있다. 
• 유지보수가 어렵다. (프로시저가 애플리케이션의 어느 부분에서 사용되는지 직관적인 확인이 어렵다)

4. CRUD

테스트의 용이성을 위해서 필자는 Oracle의 HR계정에 있는 .EMPLOYEES 테이블에 데이터를 활용하였다. 프로시저를 사용하여 부서이름을 파라미터로 받으면 사원ID와 사원이름을 출력하는 프로시저를 만들어 본다. 

4-1. 생성(CREATE)

--프로시저 생성 []는 필수값이 아님.
CREATE OR REPLACE PROCEDURE 프로시저명
( 매개변수명1 [IN | OUT | IN OUT ] 데이터타입 [:= 디폴트 값]
, 매개변수명2 [IN | OUT | IN OUT ] 데이터타입 [:= 디폴트 값],
...
)

--PL/SQL 시작부분(호출)
IS[AS]
	변수, 상수 선언 부분
BEGIN
	실행부
[EXCEPTION 
	예외 처리 부
]
END 프로시저명;

매개변수는 아래의 세가지 타입이 존재하며 기본값은 IN이다. 
IN         => 입력 (내부 로직에 전달될 데이터)
OUT     => 출력 (내부 로직에 결과로 나갈 데이터)
IN OUT => 입출력 (입출력이 모두 가능한 데이터)

 

삽질의 흔적. 20230313 -> 프로시저 생성이 안 되는데 원인 파악해서 수정하자.
20230314 -> 부끄러운 흔적이지만 그래도 남겨두려 한다. 바보같이 뭘 만들 건지 DBMS에게 알려주지 않아서 문법오류가 발생했다. PROCEDURE 예약어를 넣어주니 생성은 잘 되었다. 

CREATE OR REPLACE example_user_get (
	p_dept IN HR.DEPARTMENTS.DEPARTMENT_NAME%TYPE, 
	out_sawon OUT varchar2
)
AS  
BEGIN 
	SELECT 
		E.EMPLOYEE_ID || ' ' ||E.LAST_NAME AS res 
	FROM HR.EMPLOYEES e 
	, HR.DEPARTMENTS d 
	WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
	AND D.DEPARTMENT_NAME = p_dept;
END example_user_get;


CREATE OR REPLACE select_test
(
	p_dept IN HR.DEPARTMENTS.DEPARTMENT_NAME%TYPE
);
AS 
BEGIN 
	SELECT * FROM HR.EMPLOYEES e; 
END;

 

4-2. 조회(READ)

조회하는 부분이 사실 굉장히 오랜 시간이 걸렸다. 생성할 때에 잘못 만들어서 그런지 컴파일 오류난 내용을 실행할 수 없다는 오류를 발견했기 때문이다. 

먼저, 생성한 프로시저를 실행하는 방법으로는 2가지가 존재한다. 

1. EXEC or EXECUTE 프로시저명; 
필자는 이 명령어를 DBeaver와 같은 tool에서 사용하다보니 계속 유효하지 않는 sql이라고 나와서 당황했는데, 구글에서 서칭해본 결과 CMD의 sqlplus에서 적용되는 명령어라고 한다. 

2. DECLARE ~ BEGIN ~ END문 
통상적으로 tool을 사용해 실행하는 경우는 이 문법을 사용해서 조회해야 한다.

4-3. 수정(UPDATE)

수정의 경우는 내부 실행 생성하는 쿼리에서 내부 실행 내용만 수정하면 된다. 
CREATE OR REPLACE는 특정 이름의 프로시저가 존재하지 않으면 CREATE를 하고, 이미 존재하면 REPLACE 해버린다.

4-4. 삭제(DELETE)

DROP PROCEDURE example_user_get;
DROP PROCEDURE select_test;

 

참조

- https://fomaios.tistory.com/entry/PLSQL-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80Procedure%EB%9E%80-feat-CRUD

- https://dailycoding-diary.tistory.com/28