Procedure, Function & Package 개념

19장 Procedure, Function & Package 개념

1. PL/SQL은 Database 내에서 절차적인(Procedureal)처리를 할 수 있도록 지원하는 3GL 언어이다.
2. Procedure와 Functions은 PL/SQL, Java, C등을 이용하여 작성한다.
3. Procedure는 실행결과를 리턴하지 않고 Function은 실행결과를 리턴한다.
4. Package는 관련된 Procedure와 Function들의 묶음이다.
5. Package는 Header와 Body 부분으로 구성된다.
6. Business Logic를 Stored Procedure 내에 두면 Application 수정없이 System을 변경할 수 있고(유연성) 성능(Performance)을 향상한다.

-- 급여조정
CREATE OR REPLACE PROCEDURE oratest.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_cpt/100)
WHERE empno = v_empno;
ELSE
UPDATE emp SET sal=sal-(sal*(v_cpt/100)
WHERE empno = v_empno;
END IF;
END

-- 사원의 연봉을 구하는 Function
CREATE OR REPLACE FUNCTION oratest.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 oratest.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

-- 사원 삭제
CREATE OR REPLACE FUNCTION oratest.remove_emp
(v_empno NUMBER) AS
BEGIN
DELETE FROM emp WHERE empno=v_empno;
END

-- 사원 입사 일자를 구하는 함수
CREATE OR REPLACE FUNCTION oratest.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 실행하는 방법
exec adjust_sal('INCREASE', 7369, 10);

Function 실행하는 방법
SELECT empno "사번", ename "성명",
get_annual_sal(empno) "연봉", get_retire_money(empno) "퇴직급"
FROM emp
WHERE deptno = 30;

----------------------------------
Package 생성
관련된 Procedure나 Functions들의 묶음이다.
Header와 Body를 따로 나누어야 함

패키지 헤더
CREATE OR REPLACE PACKAGE emp_mgmt AS
PROCEDURE adjust_sal(v_flas VARCHAR2, v_empno NUMBER, v_pct NUBMER);
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;

패키지 바디 (패키지 헤더와 같은 이름이어야 함)
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_cpt/100) WHERE empno = v_empno;
ELSE
UPDATE emp SET sal=sal-(sal*(v_cpt/100) WHERE empno = v_empno;
END IF;
END

-- 사원의 연봉을 구하는 Function
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
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

-- 사원 삭제
FUNCTION remove_emp
(v_empno NUMBER) AS
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;

패키지 실행
EXEC emp_mgmt.adjust_sal('INCREATE',7369,10);
SELECT emp_mgmt.get_hiredate(7369, 'yyyy-mm-dd hh24:mi:ss') FROM dual;

관련 Dictionary

SELECT name,type,line, text
FROM user_source
WHERE name='ADJUCT_SAL'
ORDER BY line;

SELECT object_name, object_type, created, timestamp FROM user_objects;