[DB/Postgres] 저장 프로시저(Stored Procedure) 매개변수 사용방법 : IN, OUT, INOUT

해당 글에서는 PostgreSQL 내에서 저장 프로시저의 매개변수에 대해 알아봅니다.

 

 

💡 [참고] 저장 프로시저에 대해 관심이 있으시면 아래의 글도 도움이 됩니다.

분류	링크
[DB/Postgres] PL/pgSQL 이해-1 : Function, Stored Procedure	https://adjh54.tistory.com/159
[DB/Postgres] PL/pgSQL 구성하기 -2 : DBeaver 기반 구축 및 활용	https://adjh54.tistory.com/168
[DB/Postgres] 저장 프로시저로 Dummy 데이터 만드는 방법	https://adjh54.tistory.com/401
[DB/Postgres] 저장 프로시저 매개변수 사용방법: IN, OUT, INOUT	https://adjh54.tistory.com/408
[DB/Postgres] PL/pgSQL 함수, 프로시저 예외처리 사용방법 : Exception Handling	https://adjh54.tistory.com/413
[DB/Postgres] SQLSTATE Code 목록 : SQL 오류코드	https://adjh54.tistory.com/412
[Java] MyBatis에서 저장 프로시저 호출 방법	https://adjh54.tistory.com/409

 

 

 

1) 저장 프로시저(Stored Procedure)

---

💡 저장 프로시저(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, OUT, INOUT 비교

분류	IN	OUT	INOUT
지정 방법	기본값	명시적으로 지정	명시적으로 지정
기능	함수에 값을 전달	함수에서 값을 반환	함수에 값을 전달하고 업데이트 된 값을 반환.
변수	in 매개변수는 상수처럼 작동	out 매개변수는 초기화되지 않은 변수처럼 작동	inout 매개변수는 초기화 된 변수처럼 작동
값 할당 여부	값이 할당되지 않음	값을 할당해야 함	값이 할당되어야 함

 

PL/pgSQL Function Parameter Modes: IN, OUT, and INOUT

 

 

 

1. 입력 매개변수(input parameter) : IN

---

 💡 입력 매개변수(input parameter) : IN

- 프로시저가 수행하는 작업에 필요한 데이터를 전달하는 데 사용이 됩니다.

- 프로시저로 전달된 이 데이터는 프로시저 내에서 읽기 전용으로 사용이 됩니다.
- 매개변수의 기본값으로 사용되기에 파라미터에 (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);

 

 

 

 

 

오늘도 감사합니다. 😀