[DB] 프로시저(Procedure)

프로시저(Procedure)란?

일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이며, 일련의 작업을 정리한 절차입니다.

데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템에 저장한 것으로 영구저장모듈(Persistent Storage Module)이라고도 불립니다.보통 저장 프로시저를 프로시저라고 부르며, 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다.즉, 특정 작업을 위한 쿼리들의 블록입니다.

장점

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

단점

문자나 숫자열 연산에 사용하면 오히려 C,Java보다 느린 성능을 보일 수 있습니다.
유지보수가 어렵습니다.(프로시져가 앱의 어디에 사용되는지 확인이 어려움)

---

생성

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

IS
변수

BEGIN

쿼리문

END 프로시져 이름;

 

소환사의 티어를 알아내는 프로시저

CREATE OR REPLACE PROCEDURE GET_TIER(in_name IN VARCHAR2,out_tier OUT VARCHAR2)

IS

BEGIN
	
    SELECT TIER INTO out_tier FROM SUMMONER_TB WHERE NAME = in_name;

EXCEPTION
  --소환사를 찾을 수 없을 때
  WEHN NO_DATA_FOUND THEN
  
    out_tier:='NO_SUMMONER_FOUND';

END GET_TIER;

 

파라미터 값은 in,out,inout으로 총 세가지 종류로 작성할 수 있습니다.

먼저 in은 전달될 데이터이고, out은 결과로 나갈 데이터, in out in과 out 모두 가능한 데이터를 뜻합니다.

아래와 같이 프로시저가 생성된 걸 볼 수 있습니다.

 

---

조회

DECLARE
출력될 변수 선언
실행할 프로시저
출력문(Optional)
END

 

faker의 티어를 출력하는 프로시저

DECLARE
out_tier VARCHAR2(10);
BEGIN get_tier('faker',out_tier);
DBMS_OUTPUT.PUT_LINE(out_tier);
END;

-- C1

---

수정

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

CREATE OR REPLACE PROCEDURE GET_TIER(in_name IN VARCHAR2,out_tier OUT VARCHAR2)

IS

BEGIN
	
    SELECT NAME INTO out_tier FROM SUMMONER_TB WHERE NAME = in_name;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    out_tier:='NO_DATA_FOUND';

END get_tier;

---

삭제

DROP PROCEDURE 프로시저명;

 

GET_TIER 프로시저를 삭제할 때 아래와 같이 작성하면 됩니다.

DROP PROCEDURE GET_TIER;

---

함수(Function)이란?

하나의 특별한 목적의 작업을 수행하기 위해 독립적으로 설계된 코드의 집합입니다

데이터베이스에서 함수는 일반적인 프로그래밍에서의 함수와 동일합니다.

매개변수를 받아 특정 계산(작업)을 수행하고 결과를 반환하는 구조로 되어 있습니다.

즉, 특정 동작을 수행하는 코드 부분을 의미합니다.

데이터베이스 내에서는 특정 값(매개변수)을 입력 받아 여러 쿼리문을 작업한 뒤 반환하는 기능을 의미합니다.

보통 로직을 도와주는 역할이며, 간단한 계산, 수치 등을 나타낼 때 사용합니다.

생성

CREATE OR REPLACE FUNCTION 함수이름(파라미터1이름 타입, 파라미터2이름 타입...)
RETURN 반환할타입
IS
	변수 선언
BEGIN
	실행문 작성
	RETURN 반환할값
END;

 

두 개의 숫자를 받아 합의 두배를 반환하는 함수

CREATE OR REPLACE FUNCTION SUM_TWICE(n1 NUMBER, n2 NUMBER)
RETURN NUMBER
IS
	twice_sum  NUMBER := 0;
BEGIN
	twice_sum := (n1 + n2)*2;
	RETURN twice_sum;
END;

---

실행

실제로 함수를 사용해서 값을 보면

SELECT SUM_TWICE(12,13) 합의두배
FROM DUAL;

(12+13)*2인 50이 출력된다.

 

---

수정

CREATE OR REPLACE 구문을 사용하면 생성과 똑같이 작성하면 됩니다.

만약 해당되는 함수가 있다면 수정하게 되고, 없다면 생성하게 됩니다.

 

CREATE OR REPLACE FUNCTION...

---

조회

모든 함수들을 조회하고 싶을 때 아래와 같이 작성하면 됩니다.

 

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';

 

---

삭제

DROP FUNCTION 함수명;

 

SUM_TWICE를 삭제할 때 아래와 같이 작성해주면 됩니다.

DROP FUNCTION SUM_TWICE;

---

함수와 프로시저의 차이

 

프로시저 또한 매개변수를 받아 여러 작업을 수행하여 값을 반환할 수 있고, 함수 또한 매개변수를 받아 여러 작업을 수행하여 값을 반환할 수 있습니다.

즉, 함수가 여러 작업을 위한 기능이라면 프로시저는 작업을 정리한 절차입니다.

	프로시저	함수
매개변수	매개변수를 입력,출력,입출력 형식으로 받을 수 있습니다.	매개변수를 입력 형식으로만 받을 수 있습니다.
반환값	반환값을 가질 수도 있고 가지지 않을 수도 있습니다.	반환값을 반드시 가져야 합니다.
쿼리문 내에서 실행가능	SELECT,WHERE 문 등에서 사용 불가합니다.	SELECT,WHERE 문 등에서 사용이 가능합니다.
처리장소	클라이언트(화면)에서 값을 건네받아 서버에서 작업을 한 뒤 클라이언트에게 전달합니다. 즉, 서버에서 실행이 되어 속도면에서 빠른 성능을 보여줍니다.	클라이언트(화면)에서 값을 건네 받고 서버에서 필요한 값을 가져와서 클라이언트에서 작업을 하고 반환합니다. 즉, 클라이언트(화면)에서 실행이 되어 프로시저보단 속도가 느립니다.

 

---

 

연관된 글 :

 

참고:

[Oracle] 함수(Function)란? (feat. C.R.U.D)

[Oracle] 함수(Function)와 프로시저(Procedure) 차이

[Oracle] 프로시저(Procedure)란? (feat. C.R.U.D)