해당 글에서는 PL/pgSQL에 대해 이해하고 종류에 대해 알아보며 주요한 Function과 Stored Procedure에 대해서 이해를 돕기 위해 작성한 글입니다.
1) PL/pgSQL
💡 PL/pgSQL(Procedural Language/PostgreSQL)
- PostgreSQL에서 사용되는 프로그래밍 언어로 ‘SQL문을 확장’하여 ‘저장 프로시저’ 및 ‘함수’ 작성을 가능하게 합니다. - 다른 프로그래밍 언어와 유사한 구문을 사용하여 데이터베이스를 조작할 수 있습니다.
[ 더 알아보기 ] 💡 PL/SQL(Procedural Language/Structured Query Language)
- Oracle Database에서 사용되는 프로그래밍 언어입니다. PL/SQL은 SQL 문장을 포함하는 블록으로 구성되며, 데이터베이스 객체를 생성, 변경, 제거하는 등의 작업을 수행할 수 있습니다.
- PL/SQL은 데이터베이스 관리 및 조작에 사용되며 저장 프로시저, 트리거, 함수 등을 작성하는 데 사용됩니다.
1. PL/pgSQL구조
💡 PL/pgSQL의 구조는 일반적으로 선언부, 실행 부분, 예외 처리부로 구성이 되어 있습니다.
DECLARE
-- 선언부: 변수, 상수 및 데이터 타입 정의
BEGIN
-- 실행 부분: 실행 코드 작성
EXCEPTION
-- 예외 처리부: 예외 처리 코드 작성
END;
1. 선언부(Declaration section)
💡 선언부(Declaration section)란?
- 변수, 상수 및 사용자 정의 데이터 타입을 ‘정의’합니다. 이 부분은 함수 또는 블록의 맨 처음에 작성되어야 합니다.
2. 실행 부분(Execution section)
💡 실행 부분(Execution section)란?
- ‘실제 코드’가 작성되는 부분입니다. 해당 부분에서는 SQL 쿼리, 제어문, 루프 및 함수 호출 등이 포함됩니다.
3. 예외 처리부분(Exception handling section)
💡 예외 처리부분(Exception handling section)란?
- ‘예외 처리’를 위한 코드를 작성합니다. 예외 처리는 데이터베이스 문제 또는 코드 내에서 발생한 문제를 처리하는 데 사용됩니다.
2. PL/pgSQL 종류
종류
설명
함수 (Functions)
데이터베이스에서 자주 사용되는 작업을 수행하기 위해 사용되며 주로 ‘계산’을 수행하기 위해서 사용이 됩니다
절차 (Procedures)
함수와 유사하며 주로 ‘작업’을 수행하기 위해서 사용이 됩니다.
트리거 (Triggers)
데이터베이스에 대한 이벤트가 발생했을 때, 자동으로 실행되는 코드입니다.
블록 (Blocks)
트랜잭션 내에서 실행되는 코드 블록입니다.
패키지 (Packages)
관련된 함수, 절차, 타입 등을 함께 묶어서 관리하는 개념입니다.
3. PL/pgSQL 특징
기능
설명
변수 선언 및 초기화
PL/pgSQL에서는 변수 선언과 초기화가 가능합니다.
조건문 및 반복문 사용
if/else와 for/while 등의 제어문을 사용할 수 있습니다.
예외 처리 가능
예외 처리를 할 수 있습니다.
쿼리 결과 저장 가능
쿼리의 결과를 변수에 저장하여 사용할 수 있습니다.
문법이 다른 언어와 유사
다른 언어와 유사한 문법을 사용하므로 쉽게 배울 수 있습니다.
2) 함수(Function)
💡 함수(Function)
- 하나 이상의 ‘입력 인수’를 받아서 ‘결과를 반환’하는 일련의 작업을 수행하는 SQL 코드 블록을 의미합니다. - 해당 일련의 작업은 특정 ‘계산’을 수행하기 위해서 수행되며 ‘입력’을 받아서 처리한 후 결과값을 ‘반드시 반환’합니다.
- 데이터 베이스 내부에서 실행되며 애플리케이션 개발자가 호출하여 실행 할 수 있습니다.
1. 함수(Function) 구조
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $$
DECLARE
-- 변수 선언
BEGIN
-- 함수 로직
END;
$$ LANGUAGE plpgsql;
키워드
설명
CREATE [OR REPLACE] FUNCTION
함수를 생성합니다. [OR REPLACE] 는 기존 함수를 업데이트합니다.
function_name
함수의 이름을 지정합니다.
arguments
함수의 입력 인수를 지정합니다.
RETURNS return_datatype
함수의 반환 데이터 유형을 지정합니다.
DECLARE
함수에서 사용할 변수를 선언합니다.
BEGIN, END
함수의 로직을 포함합니다.
$$ LANGUAGE plpgsql
함수의 언어를 지정합니다. 이 경우 plpgsql입니다.
2. 함수(Function)사용예시
💡 [ 예시 설명 ]
- 1행: hello_world라는 이름에 인자로 name을 받는 함수를 생성하였습니다. - 2행: 해당 함수의 반환 값은 TEXT 타입입니다. - 3행~4행: 함수 내부적으로 변수로 greeting이라는 TEXT 타입의 변수를 선언하였습니다. - 5행~7행: greeting 변수는 name 인자와 "Hello, " 및 "!" 문자열을 연결한 문자열로 설정됩니다. 마지막으로 함수는 greeting 변수를 반환합니다. 💡 최종적으로 hello_world 함수에 name의 인자를 넣어서 호출을 하면 hello, {name}!이라는 문자열을 반환해 주는 함수입니다.
CREATE FUNCTION hello_world(name TEXT)
RETURNS TEXT AS $$
DECLARE
greeting TEXT;
BEGIN
greeting := 'Hello, ' || name || '!';
RETURN greeting;
END;
$$ LANGUAGE plpgsql;
3. 함수(Function)장점
장점
설명
재사용성
Function은 데이터베이스에서 ‘자주 사용되는 연산’을 캡슐화하여 코드의 재사용성을 높입니다.
모듈화
Function은 데이터베이스 내부에서 실행되므로 Function을 사용하여 ‘데이터베이스 개발을 모듈화’하면 애플리케이션 개발 생산성을 높일 수 있습니다.
성능 향상
Function은 데이터베이스 내부에서 실행되므로 데이터베이스에 ‘직접 액세스’하여 Function을 호출하면 불필요한 데이터 전송을 줄일 수 있습니다.
보안
Function을 사용하면 애플리케이션 개발자가 ‘데이터베이스에 직접 액세스하지 않고’도 데이터베이스에서 연산을 수행할 수 있으므로 보안을 강화할 수 있습니다.
3) 저장 프로시져(Stored Procedure)
💡 저장 프로시저(Stored Procedure)란?
- 하나 이상의 ‘입력 인수’를 받아서 ‘결과를 반환’하는 일련의 작업을 수행하는 SQL 코드 블록을 의미합니다. - 해당 일련의 작업은 특정 ‘작업’을 수행하기 위해서 수행되며 ‘입력’을 받아서 처리한 후 결과값은 ‘반환할 수도 있고 반환하지 않을 수도’ 있습니다. - 특정 작업은 데이터베이스 내의 데이터를 조작하고 업데이트를 하는 작업을 의미합니다.
- 데이터 베이스 내부에서 실행되며 애플리케이션 개발자가 호출하여 실행할 수 있습니다.
[ 더 알아보기 ]
💡Procedure와 Stored Procedures 차이점은?
- Procedure는 특정 task를 수행하는 코드 블록입니다. - Stored Procedures는 데이터베이스에 저장된 Procedure입니다. - Stored Procedures는 데이터베이스에 저장되어 다른 사용자 또는 애플리케이션에서 호출될 수 있습니다.
1. 저장 프로시져(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
저장 프로시저의 논리를 정의합니다.
$$;
저장 프로시저를 종료합니다.
2. 저장 프로시져(Stored Procedure) 예시
[ 예시 설명 ]
- 1행: hello_world라는 이름에 인자로 name을 받는 프로시저를 생성하였습니다. - 2행: 해당 프로시저가 작성된 언어를 지정합니다. - 4행~5행: 함수 내부적으로 변수로 greeting이라는 TEXT 타입의 변수를 선언하였습니다. - 6행~7행: greeting 변수는 name 인자와 "Hello, " 및 "!" 문자열을 연결한 문자열로 설정됩니다. - 8행 : 해당 프로시저가 종료되기 전 콘솔로 전달이 됩니다.
💡 최종적으로 hello_world 프로시저에 name의 인자를 넣어서 호출을 하면 hello, {name}!이라는 문자열을 콘솔로 반환해 주는 프로시저입니다.
CREATE PROCEDURE hello_world(name TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
greeting TEXT;
BEGIN
greeting := 'Hello, ' || name || '!';
RAISE NOTICE '%', greeting;
END;
$$;
[ 더 알아보기 ] 💡 RAISE NOTICE 란?
- PL/pgSQL에서 디버깅 목적으로 사용되는 문장으로 실행 중 콘솔에 ‘텍스트를 출력’할 수 있게 합니다. - 개발 중 변수의 값을 출력하거나 디버깅 정보를 출력하는 데 자주 사용됩니다. - RAISE NOTICE로 출력된 텍스트는 데이터베이스 로그나 로깅을 활성화한 클라이언트 애플리케이션에서 확인할 수 있습니다.
3. 저장 프로시져(Stored Procedure) 장점
장점
설명
재사용성
- 데이터베이스에서 자주 사용되는 작업을 캡슐화하여 코드의 재사용성을 높입니다.
모듈화
- 데이터베이스 내부에서 실행되므로, Stored Procedure을 사용하여 데이터베이스 개발을 모듈화하면 애플리케이션 개발 생산성을 높일 수 있습니다.
성능 향상
- 데이터베이스 내부에서 실행되므로, 데이터베이스에 직접 액세스하여 Stored Procedure을 호출하면 불필요한 데이터 전송을 줄일 수 있습니다. - 소스코드 변경이 발생하는 경우 프로시저 내에서만 수정하여 적용하면 되기에 편리하여 버그에 대한 빠른 대응에 좋습니다.(단, 프로시저가 검증이 안된 상태에 잘못된 처리를 수행하는 경우 문제가 생길수 있습니다)
보안(접근 권한, 소스 보호)
- Stored Procedure을 사용하면 애플리케이션 개발자가 데이터베이스에 직접 액세스하지 않고도 데이터베이스에서 작업을 수행할 수 있으므로, 보안을 강화할 수 있습니다. -테이블에 대한 직접적인 처리에 대한 권한을 줄이고 Procedure 권한만 주어진다면 접근 권한 및 소스 보안에 좋습니다.
유지보수
- 하나의 처리작업이 DELETE 작업을 수행하고, SELECT하며 INSERT를 수행하고 SELECT를 수행하는 과정이라면 여러개의 트랜잭션을 하나로 묶어 프로시저로 구성하여 간소화 처리가 가능합니다.
프로시저 배치 잡
- 첫 번째 프로시저를 통해 대용량 데이터 처리를 수행하고 이에 대한 반환 값을 기반으로 다음 프로시저를 처리하는 경우에 간단하게 처리가 가능합니다.
