프로시저(Procedure)란?

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

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

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

특징

리턴값이 있을 수도, 없을 수도 있다 (IN, OUT 즉, 유연하다)
리턴값이 여러개 가능하다(OUT 여러개)

장점

하나의 요청으로 여러 SQL문을 실행시킬 수 있습니다. (네트워크 부하를 줄일 수 있음)

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

여러 어플리케이션과 공유가 가능합니다. (API처럼 제공가능)

기능 변경이 편합니다. (특정 기능을 변경할 때 프로시저만 변경하면 됨)

단점

유지보수가 어렵습니다.(프로시져가 앱의 어디에 사용되는지 확인이 어려움)

재사용성 좋지 않습니다.

생성

생성

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 모두 가능한 데이터를 뜻합니다.

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

함수(Function)이란?

위의 프로시저의 각 프로세스를 수행하기 위해 필요한 기능들을 함수

함수 특징

리턴값이 필수
클라이언트에서 실행되기 때문에 프로시저보다는 느리다
들어온 입력값에 기반하여 새로운 값을 반환한다.

주의

간혹 "함수는 리턴값을 갖는 프로시저이며 프로시저는 리턴값이 없다"라고 생각하시는 분들이 있는데 이는 명백히 틀린 정의이다.
이유는 함수나 프로시저나 구조적으로는 동일하며 프로시저도 리턴값을 가질수 있기 때문이다.

문법

차이점

사용하는 용도에서 가장 큰 차이가 있다.

일단, 프로시저는 로직을 기술하여 해당 업무 처리를 직접 하는 용도로 많이 사용된다.

//로그인 하는 프로시저 (ID, PW입력받고 해당 회원의 ID 조회하는 프로시저)

CREATE OR REPLACE PROCEDURE getMemberOne (
		IN IN_ID VARCHAR(100)
   , 	IN IN_PW VARCHAR(200)
   
   , 	OUT OUT_MEMBER_ID VARCHAR(100)
) IS BEGIN 
	SELECT MEMBER_ID
    FROM MEMBER
    WHERE ID = IN_ID
    AND PW = IN_PW;
END;

함수는 로직을 도와주는 목적으로 사용된다.
예를 들어, 여러 프로시저에서 중복되는 로직이 발생될 경우 또는 자주 쓰는 로직을 모듈화시키고 싶을 때 function으로 만들어 현재 하는 업무를 서포트 해주는 목적으로 사용된다.

아래 예시와 같이 반복적으로 사용되는 특정 기능을 함수로 만들어 줄 수 있다.

// DATE_FORMAT으로 바꿔주는 함수
CREATE OR REPLACE FUNCTION testDate ( 
	   IN_DATE DATETIME
	, IN_FORMAT VARCHAR(200) 
)
RETURN VARCHAR(100)
IS IN_FORMAT VARCHAR(100) DEFAULT NULL;
      
BEGIN 
       IN_FORMAT = (CASE WHEN IN_FORMAT = 'YYYY-MM-DD hh:mm:ss' THEN DATE_FORMAT(IN_DATE, '%Y-%m-%d %H:%i:%s') END)
       RETURN IN_FORMAT ;
END