CREATE [OR REPLACE] PACKAGE package_name IS | AS public type and item declarations subprogram specifications END package_name;示例如下：
CREATE OR REPLACE PACKAGE emp_package
AS
g_deptno NUMBER(3) := 30;
PROCEDURE add_employee(eno NUMBER,NAME VARCHAR2,
salary NUMBER,dno NUMBER DEFAULT g_deptno);
PROCEDURE fire_employee(eno NUMBER);
FUNCTION get_sal(eno NUMBER) RETURN NUMBER;
END emp_package;
/当执行了以上命令之后，会建立包规范emp_package，并且定义了所有公用组件。但因为只定义了过程和函数的头部，没有编写过程和函数的执行代码，所以公用的过程和函数只有在建立了包体之后才能调用。CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS private type and item declarations subprogram bodies END package_name;示例如下：
CREATE OR REPLACE PACKAGE BODY emp_package
AS
--验证部门编号是否存在
FUNCTION validate_deptno(v_deptno NUMBER)
RETURN BOOLEAN
IS
v_temp INT;
BEGIN
SELECT 1 INTO v_temp FROM dept WHERE deptno = v_deptno;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
--添加员工
PROCEDURE add_employee(eno NUMBER,NAME VARCHAR2,
salary NUMBER,dno NUMBER DEFAULT g_deptno)
IS
BEGIN
IF validate_deptno(dno) THEN
INSERT INTO emp(empno,ename,sal,deptno)
VALUES (eno,NAME,salary,dno);
ELSE
RAISE_APPLICATION_ERROR(-20010,‘不存在该部门‘);
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20011,‘该雇员不存在‘);
END;
--删除员工（根据员工编号）
PROCEDURE fire_employee(eno NUMBER)
IS
BEGIN
DELETE FROM emp WHERE empno = eno;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20012,‘该雇员不存在‘);
END IF;
END;
--根据员工编号获得工资
FUNCTION get_sal(eno NUMBER) RETURN NUMBER
IS
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp
WHERE empno = eno;
RETURN v_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20012,‘该雇员不存在‘);
END;
END emp_package;
/3、调用包组件--添加员工
PROCEDURE add_employee(eno NUMBER,NAME VARCHAR2,
salary NUMBER,dno NUMBER DEFAULT g_deptno)
IS
BEGIN
IF validate_deptno(dno) THEN
INSERT INTO emp(empno,ename,sal,deptno)
VALUES (eno,NAME,salary,dno);
ELSE
RAISE_APPLICATION_ERROR(-20010,‘不存在该部门‘);
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20011,‘该雇员不存在‘);
END;示例二：调用包公用变量SQL> EXEC emp_package.g_deptno := 30;示例三：调用包公用过程
SQL> EXEC emp_package.add_employee(1111,‘MARY‘,2000); SQL> EXEC emp_package.add_employee(1112,‘CLARK‘,2000,10);示例四：调用包公用函数
SQL> VAR salary NUMBER; SQL> EXEC :salary := emp_package.get_sal(7788); SQL> print salary;示例五：以其他用户身份调用包公用组件
SQL> conn SYSTEM/bdqn; SQL> EXEC scott.emp_package.add_employee(1115,‘SCOTT‘,2000); SQL> EXEC scott.emp_package.fire_employee(1115);示例六：调用远程数据库包的公用组件
SQL> EXEC emp_package.add_employee@orcl_link(1116,‘SCOTT‘,2000);查看包源代码
SELECT text FROM user_source WHERE NAME=‘EMP_PACKAGE‘ AND TYPE=‘PACKAGE‘;
原文：http://blog.csdn.net/com185272358/article/details/21986769