[PostgreSQL] 프로시저(Procedure)

프로시저

일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

프로시저 사용 시 장점

1. 하나의 요청으로 여러 SQL문을 실행할 수 있다. (네트워크에 대한 부하를 줄일 수 있다.)
2. 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어든다.
3. 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조 무결성 유지가 가능하게 된다. 간단히 말하면 응용 프로그램 측 로직을 가지지 않고도 데이터베이스의 데이터 앞뒤가 맞게 될 수 있다.
4. JAVA 등의 호스트 언어와 SQL 문장이 확실하게 분리된 소스 코드의 전망이 좋아지는 것, 또한 웹사이트 등 운용 중에도 저장 프로시저의 교체에 의한 수정이 가능하기 때문에 보수성이 뛰어나다. 웹 패치를 할 때 프로시저만 변경되었다면 웹 패치를 할 필요 없이 프로시저만 교체해주면 된다.
5. 프로시저마다 권한 할당이 가능하여 보안을 강화할 수 있다.

프로시저 사용 시 단점

1. 데이터베이스 제품에 대해 설명하는 구문 규칙이 SQL / PSM 표준과의 호환성이 낮기 때문에 코드 자산으로의 재사용성이 나쁘다.
2. 비즈니스 로직의 일부로 사용하는 경우 업무의 사양 변경 시 외부 응용 프로그램과 함께 저장 프로시저의 정의를 변경할 필요가 있다. 이때 불필요한 수고와 변경 실수에 의한 장애를 발생시킬 가능성이 있다.

작성방법

postgresql로 프로시저 작성하는 방법이다.
먼저, 프로시저에 대해 간단하게 살펴보자.

CREATE OR REPLACE FUNCTION 프로시저명(매개변수명 타입)
  RETURNS 반환타입
  LANGUAGE 언어종류
AS $function$
  declare
    변수명 변수타입;
  begin 
    프로시저 내용 입력
  end
$function$
;

매개변수 없는 프로시저

프로시저이름이 sp_test라고 하자.

CREATE OR REPLACE FUNCTION sp_test
  RETURNS void
  LANGUAGE plpgsql
AS $function$

매개변수 있는 프로시저

다른 언어에서 사용하는 함수와 같이 매개변수를 선언하면 된다.
name을 String으로, year을 int로 매개변수를 받아보자.

CREATE OR REPLACE FUNCTION sp_test(name text, year int)
  RETURNS void
  LANGUAGE plpgsql
AS $function$

반환값이 없는 프로시저

RETURNS void를 작성하면 된다.

CREATE OR REPLACE FUNCTION sp_test(name text, year int)
  RETURNS void
  LANGUAGE plpgsql
AS $function$

반환값이 있는 프로시저

RETURNS 반환 타입을 작성하면 된다.
만약, 반환 타입이 문자열이라면 RETURNS text로 작성하고 int형이라면 RETURNS integer로 작성하면 된다.
반환하는 변수를 retrun 반환값; 돌려줘야 한다.

CREATE OR REPLACE FUNCTION sp_test(name text, year int)
  RETURNS text
  LANGUAGE plpgsql
AS $function$
  begin 
    --프로시저 내용 입력
    return 반환변수;
  end
$function$;

변수 선언 방법

프로시저 내에서만 변수를 선언하여 사용할 수 있다.

declare
  _RET text;
  _SQL text;
  _QUERY text;
  _NAME text;
  _YEAR integer;
begin

end

프로시저 내에서 쿼리문 실행 - EXECUTE

프로시저 내에서 쿼리문을 실행하는 경우가 있다.
쿼리를 실행만 하는 경우에 실행할 쿼리문을 적은 뒤 EXECUTE로 실행시키면 된다.

declare
  _RET text;
  _SQL text;
begin
  _SQL = '
    UPDATE customer
    SET first_name = ''shin''
    WHERE seq = 5
  ';
  EXECUTE _SQL;
end

프로시저 내에서 쿼리문을 실행하여 해당 출력 값이 필요한 경우가 있다.
SELECT처럼 출력값이 나오는 경우에는 INTO 변수를 통해 해당 출력 값을 받는다.

declare
  _RET text;
  _SQL text;
begin
  _SQL = '
    select first_name, address from customer
  ';
  EXECUTE _SQL INTO _RET;
end

출력하기 - RAISE NOTICE

변수에 어떤 값이 들어갔는지 프로시저만 실행해서는 알아볼 방법이 없다.
그래서 프로시저 실행 시 해당 변수에 어떤 값이 들어갔는 지를 알아보기 위해 변수에 대한 출력을 찍을 수 있다. C의 println이나 자바의 System.out.println과 같은 역할을 한다.

%에 , 뒤에 나오는 변수를 순서대로 넣어준다. (C에서 print를 사용하여 변수를 넣어주는 방식과 유사한 것을 볼 수 있다.)

RAISE NOTICE 'SQL : %', _SQL;
RAISE NOTICE 'SQL : %, RET : %', _SQL, _RET;

프로시저 실행 방법

SELECT 프로시저명(매개변수);로 프로시저를 실행할 수 있다.

SELECT sp_text;
SELECT sp_text(name);
SELECT sp_text(name, year);

만약 반환값이 있으면, 프로시저 실행 후 해당 값이 결과로 나오고 없을 경우 해당 프로시저가 실행만 된다.