[Oracle] 프로시저

프로시저는 자주 사용하는 SQL을 만들어 두고 필요할 때마다 호출해서 사용할 수 있습니다. 

 

함수랑 비슷한데 함수는 결과 값을 반환하고, 프로시저는 로직을 처리하기만 하고 결과 값은 반환하지 않습니다.

CREATE OR REPLACE PROCEDURE p_name
AS
BEGIN
--- 로직
END;

 

이게 기본 구성입니다. 매개변수 받을 수 있습니다. 

CREATE OR REPLACE PROCEDURE p_name(
   parameter1 IN data_type,
   parameter2 IN data_type,
   parameter3 IN OUT data_type
)
AS
BEGIN
END;

 

IN - 호출할 때 입력으로 제공하는 매개변수

OUT - 호출 후 결과를 반환하는 매개변수

IN OUT - 호출 시 입력으로 제공되고 수정 후 결과를 반환하는 매개변수

 

? 프로시저는 결과 값을 반환하지 않는다면서요..?

CREATE OR REPLACE FUNCTION add_numbers (
  num1 IN NUMBER,
  num2 IN NUMBER
) RETURN NUMBER
AS
BEGIN
  RETURN num1 + num2;
END;

 

약간 달라요. RETURN을 통해 하나의 값을 반환하는 게 함수라면 프로시저는 매개변수를 통해 호출한 쪽으로 결과를 전달하기 위한 거예요. 

 

예시를 볼게요. 두 숫자를 합하는 프로시저를 만들어 볼게요.

CREATE OR REPLACE PROCEDURE add_numbers (
  num1 IN NUMBER,
  num2 IN NUMBER,
  result OUT NUMBER
)
AS
BEGIN
  result := num1 + num2;
END;

 

매개변수 2개를 받아서 result에 담아서 전달하네요. 그럼 호출은 어떻게 할까여? 

// SET SERVEROUTPUT ON
// DECLARE
 // res NUMBER;
BEGIN
  add_numbers(5, 10, res);
 // DBMS_OUTPUT.PUT_LINE('Result: ' || res);
END;

 

DECLARE - res라는 변수를 선언합니다.

 

결과는 Result: 15가 나오게 되는데.. 뭔가 첨 보는게 많네요.. SQL에 뭔 출력인가 싶기도 하고..

 

일단 DBMS_OUTPUT.PUT_LINE는 SET SERVEROUTPUTON 명령어로 활성화시켜서 SQL*Plus 또는 SQL Developer 환경에서 출력을 볼 수 있다고 합니다.

 

음 다른 예제를 들어 볼게요.

CREATE OR REPLACE PROCEDURE p(
   p_number IN NUMBER
)
AS
BEGIN
-- UPDATE 
-- SELECT
-- INSERT 실행

 COMMIT;
 
EXCEPTION
 WHEN OTHERS THEN
  ROLLBACK;
  RAISE;
  
END;

 

프로시저 호출해서 로직 수행하다가 성공하면 COMMIT 하는거고 실패하면 예외가 발생해 ROLLBACK 시켜서 원자성을 보장할 수 있습니다.

 

RAISE는 현재 발생한 예외를 다시 발생시켜서 상위 호출 블록으로 예외를 전파합니다. 왜..? 상위 블록에서 추가적인 예외 처리를 할 수 있게 말이에요.

BEGIN
  p('1234567890');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred during the transaction.');
END;

 

호출한 블록에서 이런 예외처리를 추가적으로 두기 위해서랄까..? 

 

프로시저는 뭐 사용해본적이 없어서.. 어렵네요