오라클 프로시저(procedure)와 함수(function)

류정원·2021년 8월 5일

오라클

목록 보기

2/3

프로시저(procedure)

필요 할때마다 호출,사용하여 작업 효율을 늘릴 수 있다.
프로시저는 특정한 로직을 처리하기만 하고 결과 값은 반환하지 않는 서브 프로그램이다.

프로시저 생성

CREATE OR REPLACE PROCEDURE SP_FRUIT
(
 P_FRUIT_NAME IN VARCHAR,
 P_FRUIT_QTY  IN NUMBER 
)
IS P_FRUIT_STORE VARCHAR(100) := '과일가게';

 BEGIN

  INSERT INTO FRUIT_SHOP
  (
    FRUIT_STORE
   ,FRUIT_NAME
   ,FRUIT_QTY
  )VALUES(
    P_FRUIT_STORE
   ,P_FRUIT_NAME
   ,P_FRUIT_QTY
  )
  COMMIT;
END SP_FRUIT;

테이블에 데이터를 입력하는 프로시저이다. 파라미터로 받을 값을 프로시저명을 뒤에 명시해주면 되고 변수를 선언할 일이 있으면 IS뒤에 써주면 된다. 동작은 BEGIN뒤에 명시하면 된다. INSERT말고도 SELECT, UPDATE를 원하는 동작을 하는 프로시저를 만들면 된다.

프로시저 실행

EXEC SP_FRUIT('복숭아', 200);

프로시저를 실행시킬대에는 EXEC[프로시저명]이라고 명시하면 된다.

예제1

CREATE OR REPLACE PROCEDURE SP_UPDATE_FRUIT_QTY
(
    P_FRUIT     IN  VARCHAR2,
    P_FRUIT_NO  IN  VARCHAR2,
    P_FRUIT_QTY IN  NUMBER,
    O_CURSOR OUT VARCHAR2
)
IS

BEGIN

    -- 수정 과일 대상 조회
    BEGIN
        OPEN o_cursor FOR
            SELECT FRUIT_NO
                 , FRUIT_NAME
                 , FRUIT_QTY
              FROM FRUIT
             WHERE FRUIT_NO = P_FRUIT_NO;
              
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);              
    END

END SP_UPDATE_FRUIT_QTY;

함수(function)

함수는 특정 연산을 수행한 뒤 결과 값을 반환한다.
IN 파라미터만 사용할 수 있다.
반드시 반환될 값의 데이터 타입을 RETURN문에 선언해야 한다.

기본함수 생성

CREATE OR REPLACE FUNCTION FN_GET_FRUIT_NAME (P_FRUIT_NO NUMBER) 
    RETURN VARCHAR2 
IS 
    V_FRUIT_NAME VARCHAR2(14); 
BEGIN 
    SELECT FRUIT_NAME
      INTO V_FRUIT_NAME
      FROM FRUIT
     WHERE FRUIT_NO = P_FRUIT_NO; 
    RETURN V_FRUIT_NAME;     
END;