Procedure, Function, Package

 
 
 

* 실습개요
- 사원의 급여를 조정하는 프로시저를 생성한다.
- 사원의 연봉을 구하는 Function을 생성한다.
- 사원의 퇴직급여를 구하는 Function을 생성한다.
- 사원정보를 삭제하는 Procedure를 생성한다.
- 사원의 입사일자를 구하는 Function을 생성한다.
- 개발자를 위해 각 Procedure, Function의 사용법을 알려주는 Function을 생성한다.
- 관련된 Procedure 및 Function 들을 Package로 묶는다.

 

 

* 사원급여 조정 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;

 

 

* 사원의 연봉을 구하는 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 adjust_sal('INCREASE', 7369, 10);

* Function 실행
SQL>  SELECT empno "사번", ename "성명",  get_annual_sal(empno) "연봉", get_retire_money(empno) "퇴직금"
 FROM emp
 WHERE deptno = 30;

 

 

* 개발자를 위한 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 := 'TYPE : PROC, Usage : ADJUST_SAL(INCREASE|DECREASE, 사번, 증감율)';
 ELSIF UPPER(v_module) = 'GET_ADJUST_SAL' THEN
  v_usage := 'TYPE : FUNC, Usage  : GET_ADJUST_SAL(사번)';
 ELSIF UPPER(v_module) = 'GET_RETIRE_SAL' THEN
  v_usage := 'TYPE : FUNC, Usage : GET_RETIRE_MONEY(사번)';
 ELSIF UPPER(v_module) = 'REMOVE_EMP' THEN
  v_usage := 'TYPE : PROC, Usage : REMOVE_EMP(사번)';
 ELSIF UPPER(v_module) = 'GET_HIREDATE' THEN
  v_usage := 'TYPE : PROC, Usage : GET_HIREDATE(사번, 날짜포맷)';
 END IF;
 RETURN v_usage;
END;

 

 

 

SELECT HELP('GET_HIREDATE') FROM DUAL; 

 

 

 

* Package 생성
CREATE OR REPLACE PACKAGE emp_mgmt AS
 PROCEDURE adjust_sal (v_flag VARCHAR2, v_empno NUMBER, v_pct NUMBER) ;
 FUNCTION get_annual_sal (v_empno NUMBER) RETURN NUMBER;
 FUNCTION get_retire_money (v_empno NUMBER) RETURN NUMBER;
 PROCEDURE remove_emp (v_empno NUMBER);
 FUNCTION get_hiredate (v_empno NUMBER, v_fmt VARCHAR2) RETURN VARCHAR2;
END emp_mgmt;

* Package BODY 생성
CREATE OR REPLACE PACKAGE 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;

 -- 퇴직급여 계산
 FUNCTION get_retire_money (v_empno NUMBER)
  RETURN NUMBER IS v_money NUMBER;
 BEGIN
  SELECT ROUND((sal*NVL(comm,0)) * ROUND(MONTHS_BETWEEN(sysdate, hiredate),0)/12,0)
  INTO v_money FROM emp WHERE empno=v_empno;
  RETURN v_money;
 END; 
 -- 사원 정보 삭제
 PROCEDURE remove_emp (v_empno NUMBER) IS
 BEGIN 
  DELETE FROM emp WHERE empno=v_empno;
 END;

 -- 입사일 구하기
 FUNCTION 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;
END emp_mgmt;

 

 

 

* Package 실행
SQL> EXEC emp_mgmt.adjust_sal('INCREASE', 7369, 10); 

 

SQL> SELECT emp_mgmt.get_hiredate(7369, 'yyyy-mm-dd hh24:mi:ss') FROM dual; 

* Package 관련 Dictionary
SQL>
SELECT name, type, line, text
FROM user_source
WHERE name = 'ADJUST_SAL'
ORDER BY line;

SQL>
SELECT object_name, object_type, created, timestamp FROM user_objects;