A package is not an executable piece of code but a repository of code. When you use a package, you actually execute or make reference to an element within a package.
Create the package specification
The package specification contains the definition of all the elements in the package that can be referenced from outside of the package. These are called the public elements of the package, and they act as the package interface. The following code sample is a package specification:
This code sample creates the emp_admin package specification. This package specification consists of two functions and two stored procedures. You can also add the OR REPLACE clause to the CREATE PACKAGE statement for convenience.
Create the package body
--
-- 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;