Oracle里定义包和使用包的方法比较简单，如下
例如定义包header:
CREATE OR REPLACE PACKAGE EMP_PACKAGE IS
g_deptNo NVARCHAR2(90) := 'KD036023';
PROCEDURE ADD_EMP(empNo NVARCHAR2,deptNo NVARCHAR2);
PROCEDURE FIRE_EMP(empNo NVARCHAR2);
FUNCTION GET_NO(empNo NVARCHAR2) RETURN NUMBER;
END EMP_PACKAGE;
定义包体：
CREATE OR REPLACE PACKAGE BODY EMP_PACKAGE AS
FUNCTION validate_dept(deptID VARCHAR2 )
RETURN BOOLEAN
IS
v_temp INT;
BEGIN
SELECT 1 INTO v_temp FROM AM_DEPT WHERE DEPT_ID=deptID;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
PROCEDURE ADD_EMP(EMPNO NVARCHAR2,DEPTNO NVARCHAR2)
IS
BEGIN
IF validate_dept(DEPTNO) THEN
INSERT INTO AM_EMPLOYEE (EMP_ID, EMP_NO, LOGIN_NAME, EMP_NAME,DEPT_CODE)
VALUES (sys_guid(), EMPNO, 'testName', 'aaa', DEPTNO );
ELSE
RAISE_APPLICATION_ERROR(-20010,'this dept is not exists.');
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error(-20011,'this employee is already exists.');
END;
PROCEDURE FIRE_EMP(EMPNO NVARCHAR2)
IS
BEGIN
DELETE FROM AM_EMPLOYEE WHERE EMP_NO=EMPNO;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20012,'this employee is not exists.');
END IF;
END;
FUNCTION GET_NO(empNo NVARCHAR2 )
RETURN NUMBER
IS
L_ID AM_EMPLOYEE.LOCATION_ID%TYPE;
BEGIN
SELECT LOCATION_ID INTO L_ID FROM AM_EMPLOYEE WHERE EMP_NO=empNo;
RETURN L_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20012,'this employee is not exists.');
END;
END EMP_PACKAGE;
调用包的存储过程：
BEGIN
EMP_PACKAGE.ADD_EMP ('EMPID125', 'KD0290201');
END;
调用Function可以直接用
SELECT EMP_PACKAGE.GET_NO('EMPID125') FROM DUAL