PostgreSQL에서 저장 프로시저를 사용하여 데이터베이스 작업 효율화하기: CREATE PROCEDURE 문 상세 가이드

https://neon.tech/postgresql/postgresql-plpgsql/postgresql-create-procedure

PostgreSQL CREATE PROCEDURE By Examples

 

소개

PostgreSQL은 강력한 오픈 소스 관계형 데이터베이스 관리 시스템으로, 복잡한 데이터베이스 작업을 효율적으로 처리하기 위한 다양한 기능을 제공합니다. 그중에서도 저장 프로시저는 자주 사용되는 SQL 문장들을 하나의 블록으로 묶어놓아 코드의 재사용성을 높이고, 복잡한 로직을 처리하는 데 유용하게 활용되는 기능입니다. 이 글에서는 PostgreSQL에서 저장 프로시저를 생성하고 사용하는 방법에 대해 자세히 알아보겠습니다.

PostgreSQL 저장 프로시저란?

저장 프로시저는 데이터베이스 내에 저장되어 반복적으로 실행되는 코드 블록입니다. 일반적인 SQL 문장과 달리, PL/pgSQL과 같은 절차적 언어를 사용하여 복잡한 논리를 구현하고, 변수를 선언하여 데이터를 저장하고 처리할 수 있습니다. 저장 프로시저를 사용하면 다음과 같은 이점을 얻을 수 있습니다.

• 코드 재사용성 향상: 자주 사용되는 SQL 문장들을 한 번 정의하여 여러 번 사용할 수 있습니다.
• 데이터베이스 부하 감소: 클라이언트와 서버 간의 네트워크 통신 횟수를 줄여 성능을 향상시킬 수 있습니다.
• 보안 강화: 특정 사용자에게만 프로시저 실행 권한을 부여하여 데이터 보안을 강화할 수 있습니다.
• 복잡한 로직 구현: 조건문, 반복문 등을 사용하여 복잡한 비즈니스 로직을 구현할 수 있습니다.

CREATE PROCEDURE 문으로 저장 프로시저 생성하기

새로운 저장 프로시저를 생성하려면 CREATE PROCEDURE 문을 사용합니다.

CREATE OR REPLACE PROCEDURE procedure_name(
    parameter_list
)
LANGUAGE plpgsql
AS $$
DECLARE
    -- 변수 선언
BEGIN
    -- 프로시저 본문
END;
$$;

 

• CREATE OR REPLACE PROCEDURE: 저장 프로시저를 생성하거나 이미 존재하는 프로시저를 덮어씁니다.
• procedure_name: 프로시저의 이름을 지정합니다.
• parameter_list: 프로시저에 전달할 매개변수를 정의합니다.
• LANGUAGE plpgsql: 저장 프로시저의 언어를 PL/pgSQL로 지정합니다.
• $$: PL/pgSQL 블록의 시작과 끝을 나타냅니다.
• DECLARE: 변수를 선언하는 부분입니다.
• BEGIN ~ END: 프로시저의 본체를 작성하는 부분입니다.

저장 프로시저 예제: 계좌 이체

CREATE OR REPLACE PROCEDURE transfer(
    sender int,
    receiver int,
    amount dec
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 송금인 계좌에서 금액 차감
    UPDATE accounts
    SET balance = balance - amount
    WHERE id = sender;

    -- 수신인 계좌에 금액 추가
    UPDATE accounts
    SET balance = balance + amount
    WHERE id = receiver;

    COMMIT;
END;
$$;

위 예제는 accounts 테이블에서 특정 계좌에서 다른 계좌로 금액을 이체하는 저장 프로시저입니다.

저장 프로시저 호출하기

저장 프로시저를 호출하려면 CALL 문을 사용합니다.

CALL transfer(1, 2, 1000);

 

위 예제는 transfer 프로시저를 호출하여 계정 1에서 계정 2로 1000원을 이체하는 명령입니다.

저장 프로시저의 장점과 활용 예시

• 복잡한 비즈니스 로직 구현: 여러 개의 SQL 문장을 하나의 프로시저로 묶어 복잡한 비즈니스 로직을 구현할 수 있습니다.
• 트랜잭션 처리: BEGIN과 COMMIT 또는 ROLLBACK을 사용하여 트랜잭션을 관리할 수 있습니다.
• 코드 재사용성: 자주 사용되는 SQL 문장을 프로시저로 만들어 여러 번 호출할 수 있습니다.
• 보안 강화: 특정 사용자에게만 프로시저 실행 권한을 부여하여 데이터 보안을 강화할 수 있습니다.
• 데이터베이스 성능 향상: 클라이언트와 서버 간의 네트워크 통신 횟수를 줄여 성능을 향상시킬 수 있습니다.

결론

PostgreSQL의 저장 프로시저는 데이터베이스 개발 생산성을 향상시키고, 코드의 재사용성을 높이는 데 매우 유용한 기능입니다. PL/pgSQL을 사용하여 다양한 비즈니스 로직을 구현하고, 트랜잭션을 관리하여 데이터 무결성을 보장할 수 있습니다.