💡 저장 프로시저(Stored Procedure) - 하나 이상의 ‘입력 인수’를 받아서 ‘결과를 반환’하는 일련의 작업을 수행하는 SQL 코드 블록을 의미합니다.
- 해당 일련의 작업은특정 ‘작업’을 수행하기 위해서 수행되며 ‘입력’을 받아서 처리한 후 결과값은 ‘반환할 수도 있고 반환하지 않을 수도’ 있습니다. 특정 작업은 데이터베이스 내의 데이터를 조작하고 업데이트를 하는 작업을 의미합니다.데이터 베이스 내부에서 실행되며 애플리케이션 개발자가 호출하여 실행할 수 있습니다.
1. 저장 프로시저 기본구조
💡 저장 프로시저 기본구조
1. CREATE [OR REPLACE] PROCEDURE - 저장 프로시저를 생성합니다. - OR REPLACE를 사용하면 새 버전으로 기존 저장 프로시저를 업데이트합니다.
2. procedure_name : 저장 프로시저의 이름을 정의합니다.
3. arguments : 저장 프로시저의 매개변수를 정의합니다.(IN, OUT, INOUT)
4. DECLARE : 저장 프로시저 내에서 사용할 변수를 선언합니다.(선언부)
5. BEGIN, END: 저장 프로시저의 로직을 정의합니다.
6. $$; : 저장 프로시저를 종료합니다.
CREATE [OR REPLACE] PROCEDURE procedure_name (arguments)
LANGUAGE plpgsql
AS $$
DECLARE
-- 변수 선언 영역
BEGIN
-- 저장 프로시저 로직 영역
END;
$$;
2) 저장 프로시저 매개변수 종류
💡 저장 프로시저 매개변수(Stored Procedure Parameter)
- 프로시저를 호출할 때 전달되는 정보를 가리킵니다. 이것들은 입력 값, 출력 값 또는 입력/출력 값일 수 있습니다. 이러한 파라미터를 사용하여 프로시저에 데이터를 전달하거나, 프로시저의 결과를 받아올 수 있습니다.
- 일반적으로 프로시저 호출 시에 전달되는 실제 값(argument)에 의해 설정됩니다. 각 파라미터는 특정 데이터 타입을 가지며, 이 데이터 타입은 프로시저가 호출될 때 해당 파라미터가 받을 수 있는 값의 종류를 결정합니다. - 프로시저의 입력 값으로 작용하며, 프로시저 내부에서 사용되어 원하는 결과를 생성하는 데 도움을 줍니다. 이는 프로시저의 재사용성을 높이고 코드의 가독성을 향상합니다.
- 프로시저로 전달된 이 데이터는 프로시저 내에서 읽기 전용으로 사용이 됩니다. - 매개변수의 기본값으로 사용되기에 파라미터에 (in) 키워드는 생략해도 됩니다. - 프로시저에서 사용되는 지역 변수와 구분을 두기 위해 prefix로 ‘pi_xxx’를 붙이는 형태를 사용합니다
💡 사용예시 - proc_increase_salary 이름의 프로시저는 tb_employees 테이블 내에 salary를 월급 인상률을 포함하여 업데이트를 하는 프로시저입니다.
- 해당 예시에서는 직원의 아이디인 ‘pi_emp_id’ 값과 인상률인 ‘pi_increase’를 받아서 직원 별로 다른 월급 인상률을 나타내는 예시입니다.
-- 프로시저 생성
CREATE OR REPLACE FUNCTION proc_increase_salary(pi_emp_id IN int, pi_increase IN int)
RETURNS void AS $$
BEGIN
UPDATE tb_employees SET salary = salary + pi_increase WHERE id = pi_emp_id;
END;
$$ LANGUAGE plpgsql;
-- 호출부
CALL PROC_INCREASE_SALARY(1, 100);
2. 출력 파라미터(output parameter) : OUT
💡 출력 파라미터(output parameter) : OUT - 프로시저가 수행한 결과를 호출자에게 반환하는 데 사용됩니다.
- 출력 파라미터를 통해 여러 가지 결과 값을 반환할 수 있습니다. - 초기 값은 NULL이며, 프로시저 내에서 값을 할당받습니다. - 프로시저에서 사용되는 지역 변수와 구분을 두기 위해 prefix로 ‘po_xxx’를 붙이는 형태를 사용합니다
💡 사용예시 - proc_total_column 이름의 프로시저는 tb_employees 테이블 내에 컬럼의 개수를 반환하는 프로시저입니다. - 해당 예시에서는 최종적인 연산 값을 po_total의 매개변수로 반환합니다.
CREATE OR REPLACE FUNCTION proc_total_column (OUT po_total int)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(*) INTO pi_total FROM tb_employees;
END;
$$;
-- 호출부
SELECT multiflex_scma.proc_total_column();
3. 입력/출력 파라미터(input/output parameter): INOUT
💡 INOUT - 입력 파라미터와 출력 파라미터의 기능을 모두 가지고 있고 각각 사용이 가능합니다. - 이는 프로시저에 정보를 전달하고 프로시저의 결과를 반환하는 데 사용됩니다. - 프로시저에서 사용되는 지역 변수와 구분을 두기 위해 prefix로 ‘pio_xxx’를 붙이는 형태를 사용합니다
💡 사용예시
- proc_update_salary 이름으로 프로시저는 tb_employees 테이블 내에 salary를 월급 인상률을 포함하여 업데이트를 하며 증가된 급여를 pio_increase에 저장하고 이를 출력하는 예시입니다.
-- INOUT 키워드를 사용한 프로시저 생성
CREATE OR REPLACE FUNCTION proc_update_salary(pio_emp_id INOUT int, pio_increase INOUT int)
LANGUAGE plpgsql AS $$
BEGIN
UPDATE tb_employees SET salary = salary + pio_increase WHERE id = pio_emp_id;
SELECT salary INTO pio_increase FROM tb_employees WHERE id = pio_emp_id;
END;
$$;
-- 호출부
SELECT * FROM proc_update_salary(1, 100);
