PL/SQL 패키지를 작성하려면 다음을 수행하십시오.
패키지 스펙에서는 패키지 외부에서 참조할 수 있는 패키지 오브젝트를 설정합니다.
패키지 본문에는 패키지 스펙 내에 선언된 모든 프로시저 및 함수의 구현이 포함되어 있습니다.
CREATE OR REPLACE PACKAGE emp_admin
IS
FUNCTION get_dept_name (
p_deptno NUMBER DEFAULT 10
)
RETURN VARCHAR2;
FUNCTION update_emp_sal (
p_empno NUMBER,
p_raise NUMBER
)
RETURN NUMBER;
PROCEDURE hire_emp (
p_empno NUMBER,
p_ename VARCHAR2,
p_job VARCHAR2,
p_sal NUMBER,
p_hiredate DATE DEFAULT sysdate,
p_comm NUMBER DEFAULT 0,
p_mgr NUMBER,
p_deptno NUMBER DEFAULT 10
);
PROCEDURE fire_emp (
p_empno NUMBER
--
-- Package body for the 'emp_admin' package.
--
CREATE OR REPLACE PACKAGE BODY emp_admin
IS
--
-- Function that queries the 'dept' table based on the department
-- number and returns the corresponding department name.
--
FUNCTION get_dept_name (
p_deptno IN NUMBER DEFAULT 10
)
RETURN VARCHAR2
IS
v_dname VARCHAR2(14);
BEGIN
SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno;
RETURN v_dname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno);
RETURN '';
END;
--
-- Function that updates an employee's salary based on the
-- employee number and salary increment/decrement passed
-- as IN parameters. Upon successful completion the function
-- returns the new updated salary.
--
FUNCTION update_emp_sal (
p_empno IN NUMBER,
p_raise IN NUMBER
)
RETURN NUMBER
IS
v_sal NUMBER := 0;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
v_sal := v_sal + p_raise;
UPDATE emp SET sal = v_sal WHERE empno = p_empno;
RETURN v_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found');
RETURN -1;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
DBMS_OUTPUT.PUT_LINE(SQLCODE);
RETURN -1;
END;
--
-- Procedure that inserts a new employee record into the 'emp' table.
--
PROCEDURE hire_emp (
p_empno NUMBER,
p_ename VARCHAR2,
p_job VARCHAR2,
p_sal NUMBER,
p_hiredate DATE DEFAULT sysdate,
p_comm NUMBER DEFAULT 0,
p_mgr NUMBER,
p_deptno NUMBER DEFAULT 10
)
AS
BEGIN
INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno)
VALUES(p_empno, p_ename, p_job, p_sal,
p_hiredate, p_comm, p_mgr, p_deptno);
END;
--
-- Procedure that deletes an employee record from the 'emp' table based
-- on the employee number.
--
PROCEDURE fire_emp (
p_empno NUMBER
)
AS
BEGIN
DELETE FROM emp WHERE empno = p_empno;
END;
END;
새 PL/SQL 패키지에는 스펙 및 본문이 포함됩니다.