[PostgreSQL] Procedural 프로시저; PL/pgSQL

프로시저(PL/pgSQL)란?

프로시저란 데이터베이스 내에서 일련의 작업을 수행하기 위해 미리 작성된 코드 블록입니다. 이 코드 블록은 SQL 명령문과 프로그래밍 로직을 포함할 수 있습니다. 프로시저를 사용하면 데이터베이스에서 반복적으로 수행되는 작업을 단순화하고, 실행 효율을 높이며, 코드의 재사용성을 높일 수 있습니다.

 

프로시저(PL/pgSQL) 구조

DECLARE
  -- 선언부: 변수, 상수 및 데이터 타입 정의
BEGIN
  -- 실행 부분: 실행 코드 작성
EXCEPTION
  -- 예외 처리부: 예외 처리 코드 작성
END;

 

프로시저(PL/pgSQL) 종류

종류	설명
함수 (Functions)	데이터베이스에서 자주 사용되는 작업을 수행하기 위해 사용되며 주로 ‘계산’을 수행하기 위해서 사용이 됩니다
절차 (Procedures)	함수와 유사하며 주로 ‘작업’을 수행하기 위해서 사용이 됩니다.
트리거 (Triggers)	데이터베이스에 대한 이벤트가 발생했을 때, 자동으로 실행되는 코드입니다.
블록 (Blocks)	트랜잭션 내에서 실행되는 코드 블록입니다.
패키지 (Packages)	관련된 함수, 절차, 타입 등을 함께 묶어서 관리하는 개념입니다.

 

저장 프로시저(Stored Procedure)

저장 프로시저(Stored Procedure)는 데이터베이스에 저장된 코드 블록이며, 일련의 작업을 수행하기 위해 미리 작성된 프로그램입니다. 저장 프로시저는 데이터베이스 내에서 실행되며, 일반적으로 SQL 문과 프로그래밍 로직이 조합된 형태를 가지고 있습니다. 이러한 프로시저는 데이터베이스에 저장되어 있으므로 필요할 때마다 호출하여 사용할 수 있습니다.

저장 프로시저(Stored Procedure) 특징

1. 기능: 저장 프로시저는 데이터베이스에서 실행되는 코드 블록으로, 다양한 작업을 수행할 수 있습니다. 예를 들어, 데이터의 조회, 삽입, 수정, 삭제와 같은 데이터 조작 작업을 수행하거나, 복잡한 비즈니스 로직을 구현할 수 있습니다.
2. 재사용성: 저장 프로시저는 필요할 때마다 호출하여 사용할 수 있으며, 여러 번 호출될 수 있습니다. 이러한 특성으로 인해 코드의 중복을 줄이고 재사용성을 높일 수 있습니다.
3. 보안: 저장 프로시저는 데이터베이스 내에 저장되어 있기 때문에, 데이터베이스와 관련된 작업을 수행할 때 보안을 강화할 수 있습니다. 데이터베이스 사용자에게 직접 SQL 문을 실행할 권한을 부여하는 것보다 저장 프로시저를 호출하여 실행하는 것이 더 안전할 수 있습니다.
4. 성능: 저장 프로시저는 데이터베이스 내에서 실행되기 때문에 네트워크 비용이 들지 않습니다. 또한, 데이터베이스 시스템은 저장 프로시저를 미리 컴파일하여 최적화할 수 있으므로 실행 속도가 빠릅니다.
5. 트랜잭션 관리: 저장 프로시저 내에서 여러 개의 SQL 문을 하나의 트랜잭션으로 묶어서 실행할 수 있습니다. 이를 통해 데이터의 일관성을 유지하고 오류 발생 시 롤백을 수행할 수 있습니다.

 

저장 프로시저(Stored Procedure) 구조

CREATE [OR REPLACE] PROCEDURE procedure_name (arguments)
LANGUAGE plpgsql
AS $$
DECLARE
    -- 변수 선언
BEGIN
    -- 저장 프로시저 로직
END;
$$;

키워드	설명
CREATE [OR REPLACE] PROCEDURE	저장 프로시저를 생성합니다. OR REPLACE를 사용하면 새 버전으로 기존 저장 프로시저를 업데이트합니다.
procedure_name	저장 프로시저의 이름을 정의합니다.
arguments	저장 프로시저의 인수를 정의합니다.
DECLARE	저장 프로시저 내에서 사용할 변수를 선언합니다.
BEGIN, END	저장 프로시저의 논리를 정의합니다.
$$;	저장 프로시저를 종료합니다.

 

저장 프로시저와 함수 차이

저장 프로시저(Stored Procedure)와 함수(Function)는 데이터베이스에서 코드를 실행하는 두 가지 주요 방법입니다. 그러나 두 가지는 목적과 사용 방법에서 몇 가지 중요한 차이점이 있습니다.

1. 목적:
   • 저장 프로시저: 주로 데이터베이스에서 비즈니스 로직을 구현하거나 데이터 조작 작업을 수행하기 위해 사용됩니다. 저장 프로시저는 여러 개의 SQL 문과 프로그래밍 로직을 포함할 수 있습니다.
   • 함수: 주로 특정한 계산을 수행하거나 값을 반환하기 위해 사용됩니다. 함수는 일반적으로 입력 값을 받아들이고, 그 값을 기반으로 계산을 수행한 후 결과 값을 반환합니다.
2. 반환 값:
   • 저장 프로시저: 일반적으로 반환 값이 없거나, 명시적인 반환 값을 가지지 않습니다. 저장 프로시저는 주로 데이터 조작 작업을 수행하며, 결과를 반환할 필요가 없을 수 있습니다.
   • 함수: 반드시 반환 값을 가지며, 결과 값을 반환합니다. 함수는 주로 계산을 수행하고 결과 값을 반환하기 위해 사용됩니다.
3. 사용 방법:
   • 저장 프로시저: 일련의 작업을 실행하고 데이터베이스 내에서 호출될 때마다 실행됩니다. 주로 CALL 또는 EXECUTE 문을 사용하여 호출됩니다.
   • 함수: 일반적으로 SQL 문의 일부로 사용되며, 쿼리 내에서 함수 이름과 함께 호출됩니다. 예를 들어, SELECT my_function()과 같이 사용될 수 있습니다.
4. 트랜잭션 관리:
   • 저장 프로시저: 트랜잭션 내에서 여러 개의 SQL 문을 실행하고, 트랜잭션의 일관성을 관리할 수 있습니다.
   • 함수: 보통 하나의 SQL 문 내에서 호출되며, 함수 호출이 포함된 SQL 문이 하나의 트랜잭션으로 처리됩니다.
5. 포맷 및 언어:
   • 저장 프로시저: 데이터베이스 제품에 따라 다양한 언어로 작성될 수 있습니다. 주로 PL/SQL, T-SQL 등의 언어를 사용합니다.
   • 함수: 주로 SQL과 특정 프로그래밍 언어의 조합으로 작성됩니다. 예를 들어, PostgreSQL에서는 PL/pgSQL을 사용하여 함수를 작성할 수 있습니다.

 

함수(Function)	프로시저(Procedure)
특정 계산 수행	특정 작업 수행
리턴값 필수 O	리턴값 필수 X
리턴값이 1개여야만 함	리턴값이 여러개일 수 있음
Client에서 실행 (어플리케이션에서 호출)	Server에서 실행(DB)
단독 문장 구성 불가	단독 문장 구성 가능
수식내에서만 사용 가능	수식 내에서 사용 불가