[Oracle] 저장 서브프로그램 - 저장 프로시저(stored procedure), 함수(function)

 

 

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

 

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

 

 

 

 

  ■ 익명 블록과 저장 프로그램 차이점

•  익명 블록(anonymous block) 
  : 이름이 정해져 있지 않은 PL/SQL 블록. 작성한 내용을 단 한 번 실행하며 오라클에 저장되지 않기 때문에 한 번 실행한 뒤에 다시 실행하려면 PL/SQL블록을 다시 작성해서 실행해야 한다.
  (.sql파일을 만들 수는 있지만 오라클에 저장되는 것은 X)
•  저장 서브프로그램(stored subprogram) 
  : 여러번 사용할 목적으로 이름을 지정하여 오라클에 저장해 두는 PL/SQL 프로그램.
   익명블록과 달리 저장하여 공유할 수 있으므로 메모리 · 성능 · 재사용성 등의 장점이 있다.

 

	익명 블록 (anonymous block)	저장 서브프로그램 (stored subprogram)
이름	이름 없음	이름 지정
오라클 저장	저장할 수 X	저장
컴파일	실행할 때마다 컴파일	저장할 때 한 번 컴파일
공유	공유 불가	공유하여 사용 가능
다른 응용 프로그램에서의 호출 가능 여부	X	O

 

 

  ■ 저장 서브 프로그램의 대표적인 구현 방식과 용도

 

서브프로그램	용  도
프로시저	일반적으로 특정 처리 작업 수행을 위한 서브프로그램. SQL문에서는 사용할 수 X
함수	일반적으로 특정 연산을 거친 결과 값을 반환하는 서브프로그램. SQL문에서 사용 가능
패키지	저장 서브프로그램을 그룹화하는 데 사용
트리거	특정 상황(이벤트)이 발생했을 때 자동으로 연달아 수행할 기능을 구현하는데 사용

 

 

저장 프로시저(stored procedure)

  프로시저(저장 프로시저)는 특정 처리 작업을 수행하는 데 사용하는 저장 서브 프로그램으로 파라미터는 용도에 따라 지정하거나 지정하지 않을 수 있다.

 

 

1. 파라미터가 없는 프로시저

  작업 수행에 입력 데이터가 필요하지 않을 경우 사용한다.

 

 

  ■ 프로시저 생성

CREATE [OR REPLACE] PROCEDURE [프로시저 이름]
IS | AS
    [선언부]
BEGIN
    [실행부]
EXCEPTION
    [예외 처리부]
END [프로시저 이름];

•  OR REPLACE  (선택) : 지정한 이름을 가진 프로시저가 이미 존재하면 현재 작성한 내용으로 대체(덮어 씌우기)
• [프로시저 이름] : 저장할 프로시저의 고유 이름 지정. (같은 스키마 내에서 중복될 수 X
•  IS  |  AS  : 선언부를 시작하기 위해  IS 나  AS 를 사용
              선언부가 존재하지 않더라도 반드시 명시해야 하며  DECLARE 키워드는 사용하지 X
•  EXCEPTION  (선택) : 예외를 처리하기 위한 예외처리부
•  END  [프로시저 이름] : 프로시저 생성의 종료를 의미. 프로시저 이름 생략 가능

 

-- ex) 프로시저 생성 예시
CREATE OR REPLACE PROCEDURE pro_noparam
IS
    V_EMPNO NUMBER(4) := 7788;
    V_ENAME VARCHAR2(10);
BEGIN
    V_ENAME := 'SCOTT';
    DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);
    DBMS_OUTPUT.PUT_LINE('V_ENAME : ' || V_ENAME);
END;
/

프로시저를 생성하는 예시이다.

 

 

 

  ■ 프로시저 실행

-- 1) SQLPlus에서 프로시저 실행
$SQLPLUS scott/tiger

SET SERVROUTPUT ON;
EXECUTE pro_noparam;

-- 2) PL/SQL 블록(익명블록)에서 프로시저 실행
BEGIN
    pro_noparam;
END;
/

  생성한 프로시저는 SQLPlus나 다른 PL/SQL 블록에서 사용할 수 있다. SQLPlus에서 실행할 때는  EXECUTE 명령어를 사용하고, 특정 PL/SQL 블록에서 이미 만들어진 프로시저를 실행한다면 위와 같이 실행부에 실행할 프로시저 이름을 지정하면 된다.

 

SQLPlus 실행 결과

SQL Developer 실행결과

 

 

 

  ■ 프로시저 내용 확인

-- 1) SQL Developer에서 프로시저 확인
SELECT *
  FROM USER_SOURCE
 WHERE NAME = 'PRO_NOPARAM';

-- 2) SQLPlus에서 프로시저 확인
$ SQLPlus SCOTT/tiger

SELECT TEXT
  FROM USER_SOURCE
 WHERE NAME = 'PRO_NOPARAM';

  이미 저장되어 있는 프로시저를 포함하여 서브 프로그램의 소스 코드 내용을 확인하려면  USER_SOURCE 데이터 사전에서 조회한다. 앞서 만든 프로시저인  pro_noparam 을 조회하면 아래와 같은 결과가 나온다. SQLPlus에서는  TEXT 열을 조회해주면 같은 결과가 나온다.

 

 

실행 결과(SQL Developer)

실행결과(SQLPlus)

 

USER_SOURCE의 열	설  명
NAME	서브프로그램(생성 객체)이름
TYPE	서브프로그램 종류( PROCEDURE ,  FUNCTION 등)
LINE	서브프로그램에 작성한 줄 번호
TEXT	서브프로그램에 작성한 소스 코드

 

 

 

  ■ 프로시저 수정

  이미 저장되어 있는 프로시저의 소스를 변경할 때는  CREATE OR REPLACE PROCEDURE 명령어로 프로시저를 재생성한다.  ALTER PROCEDURE 은 프로시저의 소스 코드 내용을 재컴파일하는 명령어이므로 작성한 코드 내용을 변경하지 않는다. 

 

 

  ■ 프로시저 삭제

DROP PROCEDURE PRO_NOPARAM;

 DROP PROCEDURE 명령어로 프로시저를 삭제할 수 있다. 

 

 

 

 

2. 파라미터를 사용하는 프로시저

  입력 데이터가 필요한 경우 파라미터를 정의할 수 있다. 파라미터는 여러 개 작성할 수 있다.

 

 

  ■ 프로시저 생성

CREATE [OR REPLACE] PROCEDURE [프로시저 이름]

[(파라미터 이름1 [modes] 자료형 [ := || DEFAULT 기본값 ],
  파라미터 이름2 [modes] 자료형 [ := || DEFAULT 기본값 ],
  ...
  파라미터 이름N [modes] 자료형 [ := || DEFAULT 기본값 ]
)]
IS | AS
    [선언부]
BEGIN
    [실행부]
EXCEPTION
    [예외 처리부]
END [프로시저 이름];

•  OR REPLACE  (선택) : 지정한 이름을 가진 프로시저가 이미 존재하면 현재 작성한 내용으로 대체(덮어 씌우기)
• [프로시저 이름] : 저장할 프로시저의 고유 이름 지정. (같은 스키마 내에서 중복될 수 X)
• [파라미터 이름 ~ ] : 실행에 필요한 파라미터 정의. 여러 개일 경우 쉼표(,)로 구분하여 지정.
                          기본값과 모드(modes)는 생략 가능, 자료형은 자리수 지정과  NOT NULL  제약조건 사용 불가
•  IS  |  AS  : 선언부를 시작하기 위해  IS 나  AS 를 사용 
              선언부가 존재하지 않더라도 반드시 명시해야 하며  DECLARE 키워드는 사용하지 X
•  EXCEPTION  (선택) : 예외처리를 위한 예외처리부
•  END  [프로시저 이름] : 프로시저 생성의 종료를 의미. 프로시저 이름 생략 가능

 

파라미터의 모드      1.  IN : 프로시저를 호출할 때 값을 입력 받음 (지정하지 않으면 기본값)     2.  OUT  : 호출할 때 값을 반환     3.  IN OUT  : 호출할 때 값을 입력받은 후 실행 결과 값을 반환

 

 

  1)  IN 모드 파라미터 

-- ex) IN모드 파라미터 지정 예시
CREATE OR REPLACE PROCEDURE pro_param_in
(
    param1 IN NUMBER, 	-- 파라미터에 IN 모드 지정
    param2 NUMBER, 	-- 모드를 지정하지 않으면 기본값으로 IN 모드가 지정됨
    param3 NUMBER := 3,
    param4 NUMBER DEFAULT 4
)
IS

BEGIN
    DBMS_OUTPUT.PUT_LINE('param1 : ' || param1);
    DBMS_OUTPUT.PUT_LINE('param2 : ' || param2);
    DBMS_OUTPUT.PUT_LINE('param3 : ' || param3);
    DBMS_OUTPUT.PUT_LINE('param4 : ' || param4);
END;
/

프로시저 실행에 필요한 값을 직접 입력받는 형식의 파라미터를 지정할 때  IN 을 사용한다.

기본값이기 때문에 생략 가능하다.

 

 

 

   파라미터에 값을 지정하는 방식 

• 위치 지정 : 지정한 파라미터 순서대로 값을 지정하는 방식
• 이름 지정 :  => 연산자로 파라미터 이름을 명시하여 값을 지정하는 방식
• 혼합 지정 : 일부 파라미터는 순서대로 값만 지정하고 일부 파라미터는  => 연산자로 값을 지정 (11g부터 사용 가능)

 

-- 파라미터의 값 지정 방식
-- 1-1) 위치 지정
EXECUTE pro_param_in(1, 2, 9, 8);

-- 1-2) 위치지정 - IN 모드로 지정된 파라미터만 입력 (기본값이 지정된 파라미터 입력 제외)
EXECUTE pro_param_in(1, 2);

-- 2) 기본값이 지정되지 않은 파라미터 수보다 적은 수의 값을 지정(오류)
EXECUTE pro_param_in(1);

-- 3) 이름 지정
EXECUTE pro_param_in(param1 => 10, param2 => 20);

실행할 때는 파라미터의 개수와 값을 맞춰서 입력해야 한다.

위의 예시 외에도 기본값이 지정된 파라미터와 그렇지 않은 파라미터의 순서가 섞여있는 경우 위치 지정 방식으로 할 경우 기본값이 지정되지 않은 파라미터까지 순서대로 값을 입력해야 한다.

 

파라미터  param3 ,  param4 는 기본값이 지정되어 있어 호출할 때 값을 지정하지 않아도 실행이 가능하다.

 

 

2 실행 결과

기본값이 지정되지 않은 수보다 적은 수의 값을 지정하면 프로시저 실행은 실패한다.

 

 

 

 

  2)  OUT 모드 파라미터 

   OUT 모드를 사용한 파라미터는 프로시저 실행 후 호출한 프로그램으로 값을 반환한다. 반환되는 값을 또 다른 PL/SQL블록에서 받아서 처리하는 것도 가능하다.

 

-- OUT 모드 파라미터 지정 예시
CREATE OR REPLACE PROCEDURE pro_param_out
(
    in_empno IN EMP.EMPNO%TYPE,
    out_ename OUT EMP.ENAME%TYPE, 	-- OUT 모드로 파라미터를 지정하면 프로시저 실행후 값을 반환
    out_sal OUT EMP.SAL%TYPE
)
IS


BEGIN
    SELECT ENAME, SAL INTO out_ename, out_sal 	-- 결과로 나온 ENAME과 SAL을 각각 out_ename과 out_sal에 저장
      FROM EMP
     WHERE EMPNO = in_empno; 	-- IN 모드로 입력받은 in_empno에 따라서 ENAME과 SAL을 반환
END pro_param_out;
/

 OUT 모드를 사용하는 프로시저이다.

 EMPNO 를 입력받아  ENAME 과  SAL 을 각각  out_ename 과  out_sal 에 저장해서 값을 반환한다.

 

-- OUT 모드 파라미터 사용 예시
DECLARE
    v_ename EMP.ENAME%TYPE;
    v_sal EMP.SAL%TYPE;
BEGIN
    pro_param_out(7788, v_ename, v_sal); 	-- 반환되는 값을 다른 PL/SQL블록에서 받아서 처리할 수 있음
    DBMS_OUTPUT.PUT_LINE('ENAME : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('SAL : ' || v_sal);
END;
/

프로시저에서  OUT 모드로 지정한 두 파라미터  out_ename ,  out_sal 은 프로시저를 실행한 후 값이 반환된다. 반환되는 값을 위와 같이 또 다른 PL/SQL블록에서 받아서 처리할 수도 있다. 위는 변수 2개를 선언하여  pro_param_out 프로시저의 반환 값을 대입한다.

 

실행 결과

 

 

  3)  IN OUT 모드 파라미터 

   IN OUT 모드로 선언한 파라미터는  IN ,  OUT 으로 선언한 파라미터 기능을 동시에 수행한다. 따라서 값을 입력받을 때와 프로시저 수행 후 결과 값을 반환할 때 사용한다.

 

-- IN OUT 모드 파라미터 지정 예시
CREATE OR REPLACE PROCEDURE pro_param_inout
(
    inout_no IN OUT NUMBER 	-- IN OUT 모드
)
IS

BEGIN
    inout_no := inout_no * 2; 	-- 2배한 값을 반환
END pro_param_inout;
/

 IN OUT 모드로 지정한  inout_no 는 값을 입력받아서 2를 곱한 뒤 반환한다.

 

DECLARE
    no NUMBER;
BEGIN
    no := 5;
    pro_param_inout(no);
    DBMS_OUTPUT.PUT_LINE('no : ' || no);
END;
/

 OUT 모드로 선언된 파라미터와 마찬가지로  IN OUT 모드로 선언된 파라미터도 다른 PL/SQL 블록에서 데이터를 받아 사용할 수 있다.

 

실행 결과

 

 

  ■ 프로시저 오류 정보 확인

  프로 시저를 생성할 때 발생하는 오류를 확인할 수 있는 방법이 2가지 있다. 다른 서브 프로그램의 오류에도 똑같이 적용된다.

 

1.  SHOW ERRORS ( ERR ) : 가장 최근에 생성되거나 변경된 서브 프로그램의 오류 정보를 출력
2.  USER_ERRORS  : 오류 정보를 확인할 수 있는 데이터 사전

 

-- 오류 발생시키기
CREATE OR REPLACE PROCEDURE pro_err
IS
    err_no NUMBER;
BEGIN
    err_no = 100;
    DBMS_OUTPUT.PUT_LINE('err_no : ' || err_no);
END pro_err;
/

먼저 오류를 확인하기 위해 위와 같이 오류가 발생하는 프로시저 생성 문장을 실행시킨다.

 

실행 결과(오류)

  1)  SHOW ERRORS(SHOW ERR)를 이용한 오류 확인 

SHOW ERRORS;

-- SHOW ERR로 줄여서 사용가능
SHOW ERR [프로그램 종류] [프로그램 이름];
SHOW ERR PROCEDURE pro_err;

 SHOW ERRORS 명령어는 가장 최근에 생성되거나 변경된 서브 프로그램의 오류 정보를 출력한다.

특정 프로그램의 오류 정보를 확인하려면 프로그램 종류와 이름을 추가로 지정하면 된다.

 

실행 결과

 

 

 

  2)  USER_ERRORS를 이용한 오류 확인 

SELECT *
FROM USER_ERRORS
WHERE NAME = 'PRO_ERR';

응용 프로그램을 사용하고 있다면  USER_ERRORS 데이터 사전을 조회하여 오류 정보를 확인할 수 있다.

 

실행 결과

 

 

 

 

 

함수(function)

  오라클 함수는 크게 내장 함수(built-in function)와 사용자 정의 함수(user defined function)로 분류할 수 있다. 내장 함수는 이전에 설명했던 게시글을 참조하면 되고 앞으로 정리할 내용은 사용자 정의 함수에 대한 것이다.

---

[Oracle] 함수의 종류와 문자 함수

[Oracle] 숫자 함수, 날짜 함수, 형변환 함수, NULL처리 함수, DECODE와 CASE

[Oracle] 다중행 함수(Multiple Row Function)

---

 

  ■ 함수와 프로시저의 차이점

  함수와 프로시저는 선언방식이 비슷해서 헷갈릴 수 있다.

특  징	프로시저	함수
실행	EXECUTE 명령어 또는 다른 PL/SQL 서브 프로그램 내에서 호출하여 실행	변수를 사용한  EXECUTE 명령어 또는 다른 PL/SQL 서브 프로그램에서 호출하여 실행하거나 SQL문에서 직접 실행 가능
파라미터 지정	필요에 따라 지정하지 않을 수도 있고 여러 지정할 수도 있으며  IN ,  OUT ,  IN OUT 세 가지 모드 사용가능	프로시저와 같게 지정하지 않을 수도 있고 여러개 지정할 수 있지만  IN 모드(또는 생략)만 사용
값의 반환	실행 후 값의 반환이 없을 수도 있고,  OUT ,  IN OUT  모드의 파라미터 수에 따라 여러 개 값을 반환가능	반드시 하나의 값을 반환해야 하며 값의 반환은 프로시저와 달리  OUT ,  IN OUT 모드의 파라미터를 사용하는 것이 아니라  RETURN 절과  RETURN 문을 통해 반환

  엄밀히 말하면 함수에도  OUT ,  IN OUT 모드의 파라미터를 지정할 수는 있지만 지정하게 되면 SQL문에서는 사용할 수 없는 함수가 되어버리기 때문에 오라클에서는 함수 파라미터에  OUT ,  IN OUT 모드를 사용하지 말라고(avoid) 권장한다. 비슷한 맥락으로 SQL문에 사용할 함수는 반환 값의 자료형을 SQL문에서 사용할 수 없는 자료형으로 지정할 수 없으며 트랜잭션을 제어하는 명령어( TCL ,  DDL ) 또는  DML 명령어도 사용할 수 없다.

 

 

 

  ■ 함수 생성

CREATE [OR REPLACE] FUNCTION [함수 이름]
[(파라미터 이름1 [IN] 자료형 1,
  파라미터 이름2 [IN] 자료형 2,
  ...
  파라미터 이름N [IN] 자료형 N,
)]
RETURN [자료형]
IS | AS
    [선언부]
BEGIN
    [실행부]
    RETURN (반환 값);
EXCEPTION
    [예외 처리부]
END [함수 이름];

• [파라미터 이름 ~] (선택)
  : 함수 실행에 사용할 입력 값이 필요하면 파라미터를 지정. 
    필요에 따라 여러 개 정의 가능. 프로시저와 달리  IN 모드만 지정가능
     := ,  DEFAULT 옵션으로 기본값 지정도 가능
•  RETURN  [자료형] : 함수의 실행 후 반환 값의 자료형을 정의
•  RETURN  (반환 값) : 함수의 반환 값을 지정

  함수 생성도 프로시저와 마찬가지로  CREATE [OR REPLACE] 명령어와  FUNCTION 키워드를 명시하여 생성한다. 작성 방식이나 문법에서도 프로시저와 큰 차이가 없다. 다만 함수는 반환 값의 자료형과 실행부에서 반환할 값을  RETURN 절 및  RETURN 문으로 명시해야 한다. 또한 실행부의  RETURN 문이 실행되면 함수 실행은 즉시 종료된다.

 

 

-- 함수 생성 예시
CREATE OR REPLACE FUNCTION func_aftertax(
    sal IN NUMBER
)
RETURN NUMBER
IS
    tax NUMBER := 0.05;
BEGIN
    RETURN (ROUND(sal - (sal * tax)));
END func_aftertax;
/

위의 함수는  sal (급여 값)을 입력받아  tax (세금)을 제한 실수령액을 계산하는 함수이다.

 

 

 

  ■ 함수 실행

  생성된 함수는 익명 블록이나 프로시저 같은 저장 서브 프로그램, SQL문에서 사용할 수 있다. PL/SQL로 실행할 때는 함수 반환 값을 대입받을 변수가 필요하다.

 

 

  1)  PL/SQL로 함수 실행 

  함수는 실행 후 하나의 값을 반환하므로 PL/SQL로 구현한 프로그램 안에 반환 값을 받기 위한 변수를 선언해서 사용해야 한다.

DECLARE
    aftertax NUMBER;
BEGIN
    aftertax := func_aftertax(3000);
    DBMS_OUTPUT.PUT_LINE('after-tax income : ' || aftertax);
END;
/

바로 이전에 생성했던 함수를 사용한 것이다.

 

실행 결과

 

 

  2)  SQL문에서 함수 실행 

SELECT func_aftertax(3000)
  FROM DUAL;

SQL문에서 제작한 함수를 사용하는 방식은 기존 오라클의 내장 함수와 같다.

 DUAL 테이블에 위와 같이 값을 직접 입력하여 사용하면 된다.

 

실행 결과

 

SELECT EMPNO, ENAME, SAL, func_aftertax(SAL) AS AFTERTAX
  FROM EMP;

함수에 정의한 파라미터와 자료형이 일치한다면 내장 함수와 마찬가지로 특정 열 또는 열 데이터 간에 연산 가공된 데이터를 입력하는 것도 가능하다.

 

실행 결과

 

  ■ 함수 삭제

DROP FUNCTION func_aftertax;

다른 객체와 마찬가지로  DROP FUNCTION 명령어를 사용하여 함수를 삭제할 수 있다.