- 하나 이상의 ‘입력 인수’를 받아서 ‘결과를 반환’하는 일련의 작업을 수행하는 SQL 코드 블록을 의미합니다.
- 해당 일련의 작업은 특정 ‘작업’을 수행하기 위해서 수행되며 ‘입력’을 받아서 처리한 후 결과값은 ‘반환할 수도 있고 반환하지 않을 수도’ 있습니다. - 특정 작업은 데이터베이스 내의 데이터를 조작하고 업데이트를 하는 작업을 의미합니다. 데이터베이스 내부에서 실행되며 애플리케이션 개발자가 호출하여 실행할 수 있습니다.
💡 저장 프로시저 기본구조 1. CREATE [OR REPLACE] PROCEDURE
- 저장 프로시저를 생성합니다. - OR REPLACE를 사용하면 새 버전으로 기존 저장 프로시저를 업데이트합니다.
2. procedure_name: 저장 프로시저의 이름을 정의합니다.
3. arguments: 저장 프로시저의 매개변수를 정의합니다(IN, OUT, INOUT) 4. DECLARE: 저장 프로시저 내에서 사용할 변수를 선언합니다(선언부) 5. EXCEPTION: 저장 프로시저 내에서 발생할 수 있는 예외 상황을 처리하는 데 사용됩니다.
6. BEGIN, END: 저장 프로시저의 로직을 정의합니다.
7. $$; : 저장 프로시저를 종료합니다.
CREATE [OR REPLACE] PROCEDURE procedure_name (arguments)
LANGUAGE plpgsql
AS $$
DECLARE
-- 변수 선언 영역
BEGIN
-- 저장 프로시저 로직 영역
EXCEPTION
-- 예외 처리 코드 영역
WHEN exception_condition THEN
END;
$$;
2) 저장 프로시저 Exception
💡 저장 프로시저 Exception - 프로그램의 실행 중에 발생하는 예기치 않은 상황을 나타내며 이를 처리하고 프로그램의 흐름을 제어하는 데 사용됩니다.
- 예기치 않은 상황은 잘못된 데이터, 외부시스템과의 연결 문제, 시스템 자원 부족 등 다양한 원인으로 발생할 수 있습니다. - 이러한 예외가 발생하면 시스템은 해당 예외에 대한 처리 코드를 호출하고 처리 코드는 문제를 해결하거나 적절한 오류 메시지를 출력하고 프로그램의 실행을 중단합니다. - 저장 프로시저에서는 EXCEPTION을 사용하여 예외를 명시적으로 선언하고, RAISE 문을 사용하여 예외를 발생시킬 수 있습니다. 또한, BEGIN... EXCEPTION... END; 블록을 사용하여 예외 처리 코드를 작성할 수 있습니다.
💡 Exception 처리 목적 1. 데이터 무결성 보장 - 예외처리를 통해 데이터베이스의 무결성을 보장할 수 있습니다. - 예를 들어 삽입, 수정, 삭제 등의 작업 중 오류가 발생하면 해당 작업을 취소하고 롤백을 수행하여 데이터의 일관성을 유지할 수 있습니다
2. 오류 추적 용이 - 예외가 발생했을 때 오류 메시지를 생성하면 문제의 원인을 빠르게 파악하고 수정하는데 도움이 됩니다.
3. 사용자 친화적 인터페이스 제공 - 예외처리를 통해 예상치 못한 오류 발생 시 사용자에게 친화적인 오류 메시지를 제공하여 사용자 경험을 향상합니다.
2. Exception 처리과정
💡 Exception 처리과정 - 아래의 코드에서 예외 상황이 발생했을 때 적절한 처리가 이루질 수 있습니다.
1. 저장 프로시저 로직 실행 중 오류가 발생하면, PL/pgSQL는 실행을 중지하고 제어권을 예외 목록(Exception)에 전달합니다. 이는 BEGIN과 EXCEPTION 사이에서 발생합니다.
2. 그다음, 발생한 오류의 조건(condition)이 예외 목록에 있는지 확인합니다.
3. 해당 오류 조건이 예외 목록에 있다면, 그에 해당하는 예외 처리 코드 (handle_exception)가 실행됩니다.
4. 만약 오류 조건이 예외 목록에 없다면, when others then 부분의 예외 처리 코드 (handle_other_exceptions)가 실행됩니다.
DECLARE
-- 변수 선언 영역
BEGIN
-- 저장 프로시저 로직 영역
EXCEPTION
-- 예외 처리 코드 영역
when condition [or condition...] then
handle_exception;
[when condition [or condition...] then
handle_exception;]
[when others then
handle_other_exceptions;
]
END;
3) 저장 프로시저 Exception 주요 명령어
1. EXCEPTION
💡 EXCEPTION - 예외 처리를 정의하고 처리하는 데 사용되는 명령어입니다.
- 해당 구문은 BEGIN... EXCEPTION... END; 구조의 블록 안에서 사용되며 이 블록은 일련의 SQL 명령문을 포함합니다. - EXCEPTION 키워드 다음에는 발생할 수 있는 예외의 유형이 나열됩니다. 이 예외 유형에 따라 다른 예외 처리 루틴을 실행할 수 있습니다.
💡 EXCEPTION 명령어 흐름
1. BEGIN … EXCEPTION … END; 구조 안에서 예외처리가 수행이 됩니다.
2. EXCEPTION 내에서는 WHEN 예외 조건, THEN 예외 조건 만족 시 수행되는 예외처리 구문으로 수행됩니다
3. END 구문을 마주하면 EXCEPTION 처리가 종료됩니다.
DECLARE
-- 변수 선언 영역
BEGIN
-- 저장 프로시저 로직 영역
EXCEPTION
-- 예외 처리 코드 영역
WHEN condition [or condition...] THEN
handle_exception;
[WHEN condition [or condition...] THEN
handle_exception;]
[WHEN others THEN
handle_other_exceptions;
]
END;
2. RAISE EXCEPTION
💡 RAISE EXCEPTION
- 사용자가 어떤 조건이 충족될때 '직접 에러를 발생'시킬 수 있게 하는 명령어를 의미합니다. 이를 통해 특정 조건에서 프로그램의 흐름을 제어하거나, 예상치 못한 이슈에 대응할 수 있습니다.
- RAISE EXCEPTION은 주로 PL/pgSQL 함수 내부에서 사용되며 에러 메시지와 함께 선택적으로 값들을 반환할 수 있습니다.
💡 사용예시
- 해당 예시에서는 프로시저 내에 tb_employee 테이블 내에 emp_id 값이 21인 값이 존재하는지 여부를 체크하며, 존재하지 않으면 '강제로 예외를 발생'키는 예제입니다.
DO
$$
DECLARE
v_emp_id int = 21;
BEGIN
-- 만약 emp_id 값이 21인 직원이 존재하지 않으면 예외를 발생시킵니다.
IF NOT EXISTS (SELECT 1 FROM tb_employee WHERE emp_id = v_emp_id) THEN
RAISE EXCEPTION '직원 ID %가 존재하지 않습니다.', v_emp_id;
END IF;
END;
$$
LANGUAGE plpgsql;
3. SQLSTATE
💡 SQLSTATE
- SQL 표준에서 정의한 5자리의 알파벳, 숫자 조합의 에러코드를 의미합니다.
- SQLSTATE를 사용하여 다양한 타입의 에러를 정밀하게 분류합니다. 각각의 SQLSTATE는 특정한 에러나 예외 상황을 나타냅니다. - 개발자들은 에러의 원인을 더 쉽게 파악하고, 적절한 대처를 할 수 있습니다. - SQLSTATE의 첫 두 문자는 에러의 ‘분류’를 나타내며, 마지막 세 문자는 에러의 ‘하위분류’를 나타냅니다.
https://adjh54.tistory.com/412
💡 [참고] SQLSTATE 목록들에 대해 궁금하시면 아래의 글을 참고하시면 도움이 됩니다.
- 해당 예시에서는 tb_employee 테이블에서 SELECT를 진행했을 때 처리과정 중에 예외처리를 하였습니다.
- 이러한 예외처리의 조건은 SQLSTATE의 오류코드를 기반으로 조건문을 구성하고 raise exception을 통해 강제 예외처리가 발생하도록 구성하였습니다. - INTO strict 부분으로 강제 결과 값을 받도록 하여 ‘P0002’는 데이터를 찾을 수 없습니다 와 ‘P0003’은 결과 행이 너무 많습니다에 대한 예외처리를 구성하였습니다.
DO
$$
DECLARE
rec record;
v_emp_id int = 21;
BEGIN
-- 직원 선택
SELECT emp_id, emp_name
INTO strict rec
FROM tb_employee
WHERE emp_id = v_emp_id;
-- 예외 처리
EXCEPTION
WHEN sqlstate 'P0002' THEN
raise exception 'ID가 %인 직원을 찾을 수 없습니다', v_emp_id;
WHEN sqlstate 'P0003' THEN
raise exception 'ID가 %인 직원이 중복입니다', v_emp_id;
END;
$$
language plpgsql;
- 해당 예외는 PL/pgSQL에서 ‘no_data_found’로 데이터를 찾을 수 없는 오류가 발생하는 예외에 대해 확인을 해봅니다.
https://adjh54.tistory.com/412
💡 사용예시
- 해당 예시에서는 Exception 처리를 수행하지 않고 tb_employee 테이블의 emp_id 값이 21이 존재하는지 여부를 확인하는 프로시저를 구성하였습니다.
- 해당 프로시저에서 주목할 사항은 ‘INTO STRICT’로 SELECT 문의 결과를 변수에 할당하는 데 사용되는데, STRICT 키워드를 통해 정확히 하나의 행을 반환해야 합니다. - 해당 결과가 없거나 둘 이상의 행을 반환하면 PostgreSQL에서 예외를 발생합니다.
DO
$$
DECLARE
rec record;
v_emp_id int = 21;
BEGIN
SELECT t1.emp_name, t1.emp_position, t1.emp_salary
INTO STRICT rec
FROM tb_employee t1
WHERE t1.emp_id = v_emp_id;
END;
$$
language plpgsql;
💡 문제 발생
- INTO STRICT를 이용하여서 정확히 하나의 행을 반환받도록 합니다. 그러나 실제 emp_id 값이 21인 값이 존재하지 않기에 SQL에서 오류를 발생시킵니다. - [P0002] ERROR: query returned no rows Where: PL/pgSQL function inline_code_block line 6 at SQL statement
3. no_data_found exception 처리를 한 경우
💡 no_data_found exception 처리를 한 경우
- 동일하게 아래의 오류를 강제로 발생시켜서 예외처리를 하여 반환을 받도록 구성합니다.
https://adjh54.tistory.com/412
💡 사용예시
- 해당 예시에서는 Exception 처리를 수행하지 않고 tb_employee 테이블의 emp_id 값이 21이 존재하는지 여부를 확인하는 구문을 구성하였습니다.
- 해당 프로시저에서 주목할 사항은 ‘INTO STRICT’로 SELECT 문의 결과를 변수에 할당하는 데 사용되는데, STRICT 키워드를 통해 정확히 하나의 행을 반환해야 합니다. - 해당 결과가 없거나 둘 이상의 행을 반환하면 PostgreSQL에서 예외를 발생합니다. - 아래의 코드를 수행하였을 경우 RAISE EXCEPTION을 통해서 예외처리를 수행합니다.
DO
$$
DECLARE
rec record;
v_emp_id int = 21;
BEGIN
SELECT t1.emp_name, t1.emp_position, t1.emp_salary
INTO STRICT rec
FROM tb_employee t1
WHERE t1.emp_id = v_emp_id;
-- catch exception
EXCEPTION
WHEN no_data_found THEN
RAISE EXCEPTION 'Employee ID % is not found', v_emp_id;
END;
$$
language plpgsql;
4) 저장 프로시저 Exception 예시 : SQLSTATE 이용
💡 상단에서는 ‘조건 이름’을 기반으로 Exception의 조건을 구성하였던 부분은 SQLSTATE를 통하여서 예외 조건을 구성합니다.
1. 'P0002: 데이터를 찾을 수 없습니다'에 대한 예시
💡 'P0002: 데이터를 찾을 수 없습니다'에 대한 예시
- 해당 예시에서는 Exception 처리를 수행하지 않고 tb_employee 테이블의 emp_id 값이 21이 존재하는지 여부를 확인하는 프로시저를 구성하였습니다.
- 현재 컬럼 자체가 20번까지 밖에 구성이 되어 있지 않기에 ‘P0002’ 프로시저 에러인 ‘데이터를 찾을 수 없습니다.’라는 에러가 발생될 예제입니다.
DO
$$
DECLARE
rec record;
v_emp_id int = 21;
BEGIN
-- 직원 선택
SELECT emp_id, emp_name
INTO strict rec
FROM tb_employee
WHERE emp_id = v_emp_id;
-- 예외 처리
EXCEPTION
WHEN sqlstate 'P0002' THEN
raise exception 'ID가 %인 직원을 찾을 수 없습니다', v_emp_id;
WHEN sqlstate 'P0003' THEN
raise exception 'ID가 %인 직원이 중복입니다', v_emp_id;
END;
$$
language plpgsql;
💡 역순으로 tb_employee 테이블을 조회하였을 때 마지막 emp_id는 20까지 밖에 생성되어 있지 않습니다.
💡 실제로 수행을 해보았습니다.
- [P0001] ERROR: ID가 21인 직원을 찾을 수 없습니다 Where: PL/pgSQL function inline_code_block line 15 at RAISE라는 에러를 발생하고 있습니다. - ’P0002’ 부분에서 예외처리가 조건에 만족되어 아래와 같은 메시지를 보여주고 있습니다.
2. 'P0003: 결과 행이 너무 많습니다'에 대한 예시
💡 'P0003: 결과 행이 너무 많습니다'에 대한 예시 - 해당 예시에서는 Exception 처리를 수행하지 않고 tb_employee 테이블의 emp_salaray 값이 3100000 이상인 사람에 대해서 조회를 하려는 예시입니다.
- ‘P0003’ 부분에서 예외처리가 걸려서 현재 데이터가 2개 값이 존재하기에 ‘P0003’ 프로시저 에러인 ‘결과 행이 너무 많습니다.’라는 에러가 발생될 예제입니다.
DO
$$
DECLARE
rec record;
v_emp_id int = 21;
v_emp_salary int = 3100000;
BEGIN
-- 직원 선택
SELECT t1.emp_id, t1.emp_name
INTO strict rec
FROM tb_employee t1
WHERE t1.emp_salary > v_emp_salary;
-- 예외 처리
EXCEPTION
WHEN sqlstate 'P0002' THEN
raise exception 'ID가 %인 직원을 찾을 수 없습니다', v_emp_id;
WHEN sqlstate 'P0003' THEN
raise exception '봉급이 % 이상 직원들이 2명이상이 존재합니다.', v_emp_salary;
END;
$$
language plpgsql;
💡 현재 봉급이 3100000 초과인 사람이 8명이 있습니다.
💡 실제로 수행을 해보았을 때 - [P0001] ERROR: 봉급이 3100000 이상 직원들이 2명 이상이 존재합니다. Where: PL/pgSQL function inline_code_block line 19 at RAISE
- ’P0003’ 부분에서 예외처리가 걸려서 아래와 같은 메시지를 보여주고 있습니다.
5) 저장 프로시저 Exception 예시 : 트랜잭션 처리결과받기
1. 정상적인 트랜잭션 처리 이후 결과값 받기
💡 정상적인 트랜잭션 처리 이후 결과값 받기
- 정상적인 데이터로 INSERT를 수행하고 v_result라는 변수에 결과값을 받아서 콘솔에 출력하는 형태로 구성이 되어 있습니다.
DO
$$
DECLARE
v_emp_id int = 21;
v_emp_name varchar(100) = 'Yoo';
v_emp_position varchar(100) = 'Engineer';
v_emp_salary int = 4000000;
v_result text;
BEGIN
-- 새로운 직원 추가
BEGIN
INSERT INTO tb_employee (emp_id, emp_name, emp_position, emp_salary)
VALUES (v_emp_id, v_emp_name, v_emp_position, v_emp_salary);
v_result := '직원 추가 성공';
EXCEPTION WHEN unique_violation THEN
-- emp_id가 이미 존재하는 경우
v_result := 'ERROR: 직원 ID가 이미 존재합니다.';
WHEN others THEN
-- 그 외의 오류
v_result := 'ERROR: 직원 추가 중 알 수 없는 오류가 발생했습니다.';
END;
-- 결과 반환
RAISE NOTICE '%', v_result;
END;
$$
LANGUAGE plpgsql;
💡 INSERT 구문이 성공하여 아래와 같이 ‘직원 추가 성공’이라는 메시지를 반환받았습니다.
💡 아래와 같이 수행하였을 경우 실제 수행한 데이터가 들어가 있음을 확인하였습니다.
SELECT * FROM tb_employee ORDER BY emp_id DESC;
2. INSERT 트랜잭션 실패 시 예외 처리 및 결과 반환 예시
💡 INSERT 트랜잭션 실패 시 예외 처리 및 결과 반환 예시
- 해당 부분에서는 INSERT를 수행할 때 ‘23505’라는 SQLSTATE 코드의 ‘고유 제약 조건 위반’이라는 오류로 키 중복에러가 발생될 예정입니다. - 키 중복 에러가 발생하는 경우 EXCEPTION 내에 unique_violation(23505) 예외처리에 발생하며 v_result 변수 내에 “ERROR: 직원 ID가 이미 존재합니다.'”라는 값을 대입하여 최종 콘솔에 출력하는 형태로 구성이 되어 있습니다.
https://adjh54.tistory.com/412
💡 INSERT 구문이 실패하여 메시지를 출력하고 있음을 보여주고 있습니다.
DO
$$
DECLARE
v_emp_id int = 21;
v_emp_name varchar(100) = 'Yoo';
v_emp_position varchar(100) = 'Engineer';
v_emp_salary int = 4000000;
v_result text;
BEGIN
-- 새로운 직원 추가
BEGIN
INSERT INTO tb_employee (emp_id, emp_name, emp_position, emp_salary)
VALUES (v_emp_id, v_emp_name, v_emp_position, v_emp_salary);
v_result := '직원 추가 성공';
EXCEPTION WHEN unique_violation THEN
-- emp_id가 이미 존재하는 경우
v_result := 'ERROR: 직원 ID가 이미 존재합니다.';
WHEN others THEN
-- 그 외의 오류
v_result := 'ERROR: 직원 추가 중 알 수 없는 오류가 발생했습니다.';
END;
-- 결과 반환
RAISE NOTICE '%', v_result;
END;
$$
LANGUAGE plpgsql;
💡 아래와 같이 수행하였을 경우 실패하였고 “ERROR: 직원 ID가 이미 존재합니다.”라는 오류를 발생하고 있음을 확인하였습니다.
