요약
1.PL/pgSQL 이란?
- Procedural Language/PostgreSQL(이하 PL/pgSQL)
- Oracle DB에서 사용되는 프로그래밍 언어
- SQL 문장을 포함하는 블록으로 구성되며, 객체 생성, 변경, 제거 등의 작업을 수행
- 데이터베이스 관리 및 조작에 사용되며, 프로시저, 트리거, 함수 등을 작성하는 데 사용됨
- 작성할 때,
LANGUAGE plpgsql을 쓰는 이유!!!
2. 기본 구조
구성별 특징
- 선언부(Declaration section, DECLARE 절): 변수 및 상수 및 데이터 타입을 정의하며, 맨 처음에 작성되어야 함
- 실행 부분(Execution section, BEGIN 절): 실행할 코드가 작성되는 부분으로 SQL 쿼리, 제어문, 루프 및 함수 호출 등 포함
- 예외 처리부분(Exception handling section, EXCEPTION 절): 예외 처리를 위한 코드로 DB 문제 또는 코드 내 발생한 문제를 처리하는데 사용
DECLARE
-- 선언부: 변수, 상수 및 데이터 타입 정의
BEGIN
-- 실행 부분: 실행할 코드 작성
EXCEPTION
-- 예외 처리부: 예외 처리할 코드 작성
END;3. 함수(Function)
함수란?
- 하나 이상의 입력 값을 받아 결과값을 반환하는 작업을 수행하는 SQL 코드 블록
- 결과값을 반드시 반한
- 데이터베이스 내부에서 실행되며, 개발자가 호출하여 실행 가능
1) 구조
| 키워드 | 설명 |
|---|---|
CREATE [OR REPLACE] FUNCTION | 함수 생성. [OR REPLACE] 는 기존 함수를 업데이트 |
schema_name.function_name | 함수 이름 (스키마명도 추가로 설정) |
arguments | 함수 입력 인수 지정(데이터 타입도 설정) |
RETURNS return_datatype | 반환 값 데이터 유형 지정 |
DECLARE | 사용할 변수 선언 |
BEGIN, END | 함수의 로직 |
$$ LANGUAGE plpgsql | 함수의 언어를 지정하며, plpgsql을 의미 |
CREATE [OR REPLACE] FUNCTION schema_name.function_name (arguments)
RETURNS return_datatype
AS
$$
DECLARE -- 변수 선언
BEGIN -- 함수 로직
END;
$$
LANGUAGE plpgsql;2) 사용 예시
예시1
- 1행:
name인자를 받는hello_world라는 함수 생성- 2행: 해당 함수의 반환 값은
TEXT타입- 3행~4행: 함수 내부적으로 활용되는
greeting라는 TEXT 타입의 변수 선언- 5행 ~7행: 해당 쿼리를 적용하고
greeting변수를 반환
CREATE FUNCTION hello_world(name TEXT)
RETURNS TEXT AS $$
DECLARE
greeting TEXT;
BEGIN
greeting := 'Hello, ' || name || '!';
RETURN greeting;
END;
$$ LANGUAGE plpgsql예시 2
4. 저장 프로시저(Stored Procedure)
저장 프로시저란?
- 하나 이상의 입력값을 받아 결과를 반환하는 작업을 수행하는 SQL 코드 블록
- 특정 작업을 수행하므로 결과값을 반드시 반환할 필요 없음
- 작업은 데이터베이스 내 데이터 조작 및 업데이트를 의미함
- 데이터베이스 내부에서 실행되며, 개발자가 호출하여 실행 가능
1) 구조
| 키워드 | 설명 |
|---|---|
CREATE [OR REPLACE] PROCEDURE | 저장 프로시정 생성. [OR REPLACE] 는 기존 저장 프로시저를 업데이트 |
schema_name.procedure_name | 저장 프로시저 이름 (스키마명도 추가로 설정) |
arguments | 저장 프로시저 입력 인수 지정(데이터 타입도 설정) |
DECLARE | 사용할 변수 선언 |
BEGIN, END | 저장 프로시저 논리 정의 |
$$; | 저장 프로시저 종료 |
CREATE [OR REPLACE] PROCEDURE schema_name.procedure_name (arguments)
LANGUAGE plpgsql
AS $$
DECLARE -- 변수 선언
BEGIN -- 저장 프로시저 로직
END;
$$;2) 사용 예시
예시1
- 1행:
name인자를 받는hello_world프로시저를 생성- 2행: 해당 프로시저가 작성된 언어를 지정
- 4~5행: 저장 프로시저 내부적으로 활용되는
greeting라는 TEXT 타입의 변수 선언- 6~7행: 해당 쿼리 적용
- 8행: 해당 프로시저가 종료되기 전 콘솔로 전달
- RAISE NOTICE 란? 디버깅 목적으로 사용되는 문장
- 실행 중 콘솔에 ‘텍스트를 출력’ 가능하게 함
- 개발 중 변수의 값을 출력하거나 디버깅 정보를 출력하는 데 자주 사용됩니다.
- RAISE NOTICE로 출력된 텍스트는 데이터베이스 로그나 로깅을 활성화한 클라이언트 애플리케이션에서 확인 가능
CREATE PROCEDURE hello_world(name TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
greeting TEXT;
BEGIN
greeting := 'Hello, ' || name || '!';
RAISE NOTICE '%', greeting;
END;
$$;예시2
참고사이트