프로시저(Procedure)란

프로시저(Procedure)란

데이터 베이스에 대한 일련의 작업을 정리한 절차를 RDBMS에 저장한 것으로 영구저장 모듈(Persistent Storage Module)이라고 합니다.

보통 저장 프로시저(Stored Procedure)를 프로시저라고 부르며, 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다.

즉, 특정 작업을 위한 쿼리들의 블록입니다.

장점

1. 하나의 요청으로 여러 SQL문을 실행시킬 수 있습니다.

2. 네트워크 소요 시간을 줄여 성능을 개선할 수 있습니다.

3. 여러 애플리케이션과 공유가 가능합니다.

4. 기능 변경이 편합니다.

단점

1. 문자나 숫자열 연산에 사용하면 오히려 C, Java보다 느린 성능을 보일 수 있습니다.

2. 유지보수가 어렵습니다.

 

 

1. 프로시저 생성 구조

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

IS 
[변수]

BEGIN
[쿼리문]

END [프로시저이름] ;

Ex) p1 프로시저 생성

CREATE OR REPLACE PROCEDURE p1

IS

BEGIN

DBMS_OUTPUT.PUT_LINE (‘A’);

END;

2. 프로시저 조회

DECLARE

[출력될 변수 선언]

[실행할 프로시저]

[출력물]

END

Ex) p1 프로시저 2번 호출

DECLARE 
PROCEDURE p1

IS
BEGIN
DBMS_OUTPUT.PUT_LINE (‘B’);

END;

BEGIN
p1; 
scott.p1;
END;

——— 결과 ———
B
A

 

설명

첫 번째 p1는 중첩 프로시저가 수행

두 번째 scott.p1는 저장 프로시저가 수행

프로시저를 호출하면 중첩 프로시저의 존재여부를 먼저 확인하고 프로시저를 스키마 명으로 한정하면 저장 프로시저를 수행할 수 있다.

 

* SQL*Plus의 EXEC 명령어로도 저장 프로시저를 수행할 수 있다.

EX) EXEC p1

// A

 

* ALTER PROCEDURE 문은 저장 프로시저를 컴파일합니다.

ALTER PROCEDURE [프로시저이름] COMPILE;

Ex) ALTER PROCEDURE p1 COMPILE;

 

3. 프로시저 수정

수정은 CREATE OR REPLACE 구문을 사용하면 해당 프로시저명이 있다면 수정, 없다면 생성되게 됩니다.

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

IS 
[변수]

BEGIN
[쿼리문]

END [프로시저이름] ;

4. 프로시저 삭제

DROP PROCEDURE [프로시저명] ;

Ex) DROP PROCEDURE p1;

 

5. 매개변수 선언

형식 파라미터(매개변수)

: 헤더에 선언하는 매개변수의 이름, 방식, 데이터 타입, 기본값

 

실질 파라미터(인수)

: 호출 시 매개변수에 할당하는 값이나 표현식

 

Ex)i_v1, iv2 형식 파라미터와 1, 2 인수

DECLARE
	PROCEDURE p1 (i_v1 IN NUMBER, i_v2 IN NUMBER) — 형식 파라미터
IS

BEGIN
	DBMS_OUTPUT.PUT_LINE(i_v1 || ‘, ’ || i_v2);
END;

BEGIN
	p1(1, 2); —실질 파라미터
END;

// 1, 2

 

6. 매개변수 방식

• 입력 IN
• 출력 OUT
• 입출력 IN OUT

1. 입력 IN

Ex) 입력 매개변수는 프로시저 내부에서 상수로 동작하며 값을 변경할 수 없습니다.

DECLARE
	PROCEDURE p1 (i_v1 IN NUMBER)
..(생략)
BEGIN 
	i_v1 := 2;
END;

Ex) %TYPE 속성으로 기본값 선언한 값은 호출 시 인수 생략가능합니다.

DECLARE
	PROCEDURE p1 (i_deptno IN dept.deptno%TYPE DEFAULT 10)
IS
	v_dname dept.dname%TYPE;
BEGIN
	SELECT dname INTO v_dname FROM dept WHERE deptno = i_deptno;
	DBMS_OUTPUT.PUT_LINE (v_dname);
END;

BEGIN
	p1;
END;

Ex) 레코드 타입으로 매개변수 전달

DECLARE
	v1 dept%ROWTYPE;
	PROCEDURE p1 (i_dept IN dept%ROWTYPE)
IS
BEGIN
	DBMS_OUTPUT.PUT_LINE (i_dept.deptno || ‘: ‘ || i_dept.dname);
END;

BEGIN
	v1.deptno := ’10;
	v1.dname :’ ‘ACCOUNTING’;
	p1 (v1);
END;

// 10: ACCOUNTING

 

2. 출력

Ex) SYS_REFCURSOR 타입으로 o_rc 출력 매개변수로 선언하고, deptno에 해당하는 커서 변수를 전달하고 출력

DECLARE
    v_rc SYS_REFCURSOR; —  v_rc 커서 변수
    v_emp emp%ROWTYPE; — v_emp 로우 타입 변수
	PROCEDURE p1 (i_deptno IN dept.deptno%TYPE, o_rc OUT SYS_REFCURSOR) -- o_rc 출력
IS
BEGIN
	OPEN o_rc FOR SELECT * FROM emp WHERE deptno = i_deptno ORDER BY empno; — o_rc에 결과 레코드
END;

BEGIN
	p1 (10, v_rc); — 호출
    
LOOP
	FETCH v_rc INTO v_emp;
	EXIT WHEN v_rc%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE (v_emp.ename);
END LOOP;

END;

 

3. 입출력 매개변수 IN OUT

Ex). 값 스왑(swap)

DECLARE
    v1 NUMBER := 1;
    v2 NUMBER := 2;
	PROCEDURE p1 (io_v1 IN OUT NUMBER, io_v2 IN OUT NUMBER)
IS
	v1 VARCHAR2(1);
BEGIN
	v1 := io_v1;
	io_v1 := io_v2;
	io_v1 := v1;
END;

 

* 프로시저 정보 검색

USER_OBJECTS 테이블 : DB에 존재하는 모든 오브젝트의 목록 정보(TABLE, VIEW, PROCEDURE, FUNCTION…)

SELECT * FROM USER_OBJECTS;

 

USER_PROCEDURES 테이블 : DB에 존재하는 프로시저 목록 정보

SELECT * FORM USER_PROCEDURES;

 

USER_SOURCE 테이블 : 프로시저 내용과 TYPE정보

SELECT 
	NAME, — 프로시저 명
	TEXT — 내용
FROM USER_SOURCE