Stored Procedure (Oracle)

Stored Procedure란?

• oracle의 스토어드 프로시저는 한 마디로 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용합니다.
• 반복적인 작업을 할 경우 PL/SQL* 을 저장해 놓고 필요한 경우 호출하여 사용할 수 있으면 할 때가 있습니다. 이때 스토어드 프로시저(=저장 프로시저)를 사용합니다. stored procudure를 사용하면 복잡한 DML 문들을 필요할 때마다 다시 입력할 필요 없이 저장해 둔 프로시저를 호출해서 실행 결과를 얻을 수 있습니다.

=> 즉, 프로시저(함수) 라는 이름에 걸맞게 일련의 쿼리문을 하나의 함수처럼 만드는 것입니다. 어떤 동작을 해야할 때 일일히 DML문을 다시 입력하는 게 아닌, 해당 프로시저를 호출하면 됩니다!

 

 

* PL/SQL이란?

PL/SQL
- PL/SQL은 상용관계형데이터베이스(RDBMS) 오라클 DBMS에서 SQL 언어를 확장하기 위해 사용하는 컴퓨터 프로그래밍 언어이다.

 

 

 

---

프로시저 구조

CREATE [OR REPLACE] PROCEDURE 프로시저명 (
매개변수1 [mode] data_type,
매개변수2 [mode] data_type ...
)
IS
--- 변수 선언부 ---
local_variable declaration
BEGIN
--- 실행부 --- // 수행할 sql 쿼리를 아래에 넣는다.
statement1;
statement2;
...
EXCEPTION
--- 예외처리부 ---
END [프로시저명];   -- // 프로시저명은 넣어도 안넣어도 된다

• create로 프로시저를 정합니다. 프로시저 실행 시 받을 변수는 여기서 선언합니다.
• 프로시저 안에서 선언할 변수는 IS 뒤에 작성합니다.
• 실행할 DML문은 BEGIN 뒤에 작성합니다.
• END로 끝냅니다.

 

 

 

생성되어 있는 프로시저 조회

--- 저장 프로시저 조회 ---
SELECT *
FROM user_source;

 

 

 

 

 

프로시저에서 IF문 사용

IF 조건1 THEN 처리
IF 조건2 THEN 처리
ELSE
END IF;

 

 

 

프로시저에서 로그 찍는 법

DBMS_OUTPUT.PUT_LINE('내용');

 

 

 

 

 

---

프로시저 생성 예시

1번 예시

--- 생성문 시작 ---
CREATE OR REPLACE PROCEDURE TEST_PROC
(
	--- 프로시저 실행 시 받을 매개 변수를 CREATE 안에 작성 ---
	param1 IN NUMBER,
	param2 IN VARCHAR2
)

--- 프로시저에서 선언할 변수는 IS 뒤에 작성 ---
IS
param3 IN VARCHAR(20) = "Study Hard";

--- 실행 내용은 BEGIN 뒤에 작성 ---
BEGIN

--- 예시로 insert문을 넣음 ---
INSERT INTO TEST_TABLE
(NO, NAME, SITE)
VALUE
(param1, param2, param3);

END TEST_PROC;

 

 

 

2번 예시 : 사원번호를 받아서 이름, 급여, 직업을 OUT 파라미터에 넘겨주기

- 프로시저 생성

CREATE OR REPLACE PROCEDURE sel_empno (
	p_empno IN NUMBER, -- IN 매개변수, 사원번호
	p_ename OUT VARCHAR2, --OUT 매개변수, 이름
	p_sal   OUT NUMBER,  --OUT 매개변수, 급여
	p_job   OUT VARCHAR2  --OUT 매개변수, 직업
)
IS
BEGIN
	SELECT
		first_name|| ' ' ||last_name, salary, job_id
	INTO
		p_ename, p_sal, p_job
	FROM
		emp
	WHERE
		employee_id = p_empno;
END;

 

-프로시저 실행

DECLARE
	var_ename VARCHAR2(15);
	var_sal   NUMBER;
	var_job   VARCHAR2(9);
BEGIN
	--- 익명 블록에서 프로시저 실행, EXEC/EXECUTE를 붙이지 않는다
	sel_empno(101, var_ename, var_sal, var_job);
	--- 프로시저 로그 찍기 ---
	dbms_output.put_line('조회결과: '||var_ename||'/'||var_sal||'/'||var_job);
END;

 

 

 

 

---

프로시저 실행 (=호출)

1. IN 변수만 있는 프로시저 (파라미터만 있고 return 값이 없는 프로시저)

EXEC TEST_PROC();
EXEC TEST_PROC(200,'HELLO');
--- EXECUTE TEST_PROC(200, 'HI'); 이렇게도 쓴다 ---

2. OUT 변수 있는 프로시저 (파리미터와 return 값 모두 있는 프로시저)

DECLARE
param1 VARCHAR2(200);
param2 VARCHAR(200);
BEGIN
TEST_PROC(200, param1, param2);
END;

 

 

 

---

 

번외 : OUT 파라미터가 있는 프로시저를 SQL 환경에서 사용하기

OUT 파라미터가 있는 프로시저를 PL/SQL 환경이 아닌 SQL환경에서도 변수를 선언하여 사용할 수 있습니다.
프로시저의 OUT 파라미터 값을 SQL 환경에서 받기 위해서는 바인드 변수*가 필요합니다.
*바인드변수란? 호스트 환경에서 생성되어 데이터를 저장하므로 호스트 변수라고도 합니다. VARIABLE 키워드를 사용하여 생성할 수 있습니다.

 

--- 바인드 변수 생성 ---
VARIABLE v_ename VARCHAR2(15);
VARIABLE v_sal NUMBER;
VARIABLE v_job VARCHAR2(9);

- 프로시저 실행

--- 프로시저 호출 ---
EXEC sel_empno(101, :v_ename, :v_sal, :v_job);

 

 

 

---

참고사이트
1. https://kookyungmin.github.io/db/2018/07/31/oracle_35/
2. https://studyingazae.tistory.com/59