14. 오라클 - PROCEDURE, FUNCTION & PACKAGE

PROCEDURE : 실행결과를 반환하지 않는다.
FUNCTION : 실행결과를 반환한다.

. PL/SQL은 DATABASE 내에서 절차적인 처리를 할 수 있도록 지원하는 3GL 언어이다.
. PROCEDURE 와 FUNCTION은 PL/SQL, JAVA, C 등을 이용하여 작성된다.
. PACKAGE는 관련된 PROCEDURE와 FUNCTION들의 묶음이다.
. PACKAGE는 HEADER 부분과 BODY 부분으로 구성되어진다.
. BUSINESS LOGIC 을 STORED PROCEDURE 내에 두면 APPLICATION 수정 없이 SYSTEM을 변경 할 수 있고(유연성) PERFORMANCE를 향상 시킬 수 있다.


예)  사원 급여 조정 Procedure
CREATE OR REPLACE PROCEDURE scott.adjust_sal(v_flag VARCHAR2 , v_empno NUMBER, v_pct NUMBER ) AS
BEGIN
         IF v_flag = 'INCREASE' THEN
             UPDATE emp SET sal=sal + (sal * (v_pct / 100)) WHERE empno=v_empno;
         ELSE
             UPDATE emp SET sal = sal - (sal * (v_pct / 100)) WHERE empno = v_empno;
        END IF;
END;
 - > scott.adjust_sal // scott 사용자의 스키마 내에 생성.
 - > adjust_sal // 사용자 명이 빠진다면 현재 로그인 계정 스키마 내에 생성


예)  사원의 연봉을 구하는 Function
CREATE OR REPLACE FUNCTION scott.get_annual_sal(v_empno NUMBER)
        RETURN NUMBER IS v_sal NUMBER;
BEGIN
         SELECT (sal + NVL(comm,0)) * 12 INTO v_sal FROM emp WHERE empno = v_empno;
         RETURN v_sal;
END;


예) 사원의 퇴직급여를 구하는 Function
CREATE OR REPLACE FUNCTION scott.get_retire_money (v_empno NUMBER)
          RETURN NUMBER IS v_sal NUMBER;
BEGIN
        SELECT ROUND((sal + NVL(comm, 0)) * ROUND(MONTHS_BETWEEN(sysdate,hiredate), 0 ) /12, 0) INTO v_sal FROM emp WHERE empno = v_empno;
        RETURN v_sal;
END;


예) 사원 정보를 삭제 하는 Procedure
CREATE OR REPLACE PROCEDURE scott.remove_emp (v_empno NUMBER) AS
BEGIN
        DELETE FROM emp WHERE empno = v_empno;
END;


예) 사원의 입사일자를 구하는 Function
CREATE OR REPLACE FUNCTION scott.get_hiredate(v_empno NUMBER, v_fmt VARCHAR2)
         RETURN VARCHAR2 IS v_hiredate VARCHAR2(20);
BEGIN
         SELECT TO_CHAR(hiredate, v_fmt) INTO v_hiredate FROM emp WHERE empno = v_empno;
         RETURN v_hiredate;
END;


PROCEDURE 실행
SQL > EXEC procedure_name(매개변수,.....);

FUNCTION 실행
예) SELECT empno "사번", ename "성명", get_annual_sal(empno) "연봉", get_retire_money(empno) "퇴직금" FROM emp WHERE deptno = 30;
 - > get_annual_sal, get_retire_money 가 function 이다. 


개발자를 위한 Help Function 생성
CREATE OR REPLACE FUNCTION scott.help(v_module VARCHAR2) 
            RETURN VARCHAR2 IS v_usage VARCHAR2(100);
BEGIN
         v_usage := v_module || '는(은) 등록되지 않은 모듈 입니다.' ;
         IF UPPER(v_module) = 'ADJUST_SAL' THEN
            v_usage := '종류 : PROC, 사용법 : ADJUST_SAL(INCREASE | DECREASE, 사번, 증감율)';
         ELSIF UPPER(v_module) = 'GET_ANNUAL_SAL' THEN
            v_usage := '종류 : FUNC, 사용법 : GET_ANNUAL_SAL(사번)';
         .....
         ....
         END IF;
         RETURN v_usage;
END;
 
사용 : SELECT HELP('ADJUST_SAL') FROM dual;




PACKAGE 생성
1) package header 생성
CREATE OR REPLACE PACEAGE emp_mgmt AS
    PROCEDURE adjust_sal(v_flag VARCHAR2, v_empno NUMBER, v_pct NUMBER);
    FUNCTION get_annual_sal(v_empno NUMBER) RETURN NUMBER;
END emp_mgmt;

2) package body 생성
CREATE OR REPLACE PACEAGE BODY emp_mgmt AS
-- 급여 조정    
PROCEDURE adjust_sal(v_flag VARCHAR2, v_empno NUMBER, v_pct NUMBER) IS
     BEGIN
              IF v_flag = 'INCREASE' THEN
                  UPDATE emp SET sal = sal + (sal * (v_pct / 100)) WHERE empno = v_empno;
              ELSE
                  UPDATE emp SET sal = sal - (sal * (v_pct / 100)) WHERE empno = v_empno;
              END IF;
   END;

-- 연봉 계산
FUNCTION get_annual_sal (v_empno NUMBER) RETURN NUMBER IS v_sal NUMBER;
     BEGIN 
             SELECT (sal + NVL(comm,0)) * 12 INTO v_sal FROM emp WHERE empno = v_empno;
             RETURN v_sal;
    END;
.....
....
END emp_mgmt;


package 실행
EXEC emp_mgmt.adjust_sal('INCREASE',7369, 10);
 - > procedure 은 EXEC 이용하여 패키지명.함수

function은 SELECT 문장으로 이용하여 실행


PROCEDURE, FUNCTION , PACKAGE  - 관련 DICTIONARY
 - > user_source , user_objects
 - > user_souce 에는 소스가  user_objects에는 오브젝트들의 이름을 확인할수 있다. 즉 user_objects 로 이름 확인하고 user_source 로 소스 확인한다.