包是Oracle数据库中一种重要的PL/SQL程序结构，它将逻辑相关的变量、常量、游标、异常、过程和函数组织在一起，提供了更好的封装性和模块化。在大型项目中，可能有很多模块，而每一个模块又有自己的存过、函数等。而这些存过、函数默认是放在一起的，如果所有的存过函数都是放在一起的那么非常不容易查询和维护，甚至会发生误删除事件。
目录
1. 包规范(Package Specification)包头、包定义
一、包的组成
1. 包规范(Package Specification)包头、包定义
定义包的公共接口
声明可供外部访问的对象
不包含具体实现代码
包定义部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理元素，这些元素是包的公有元素。
2. 包体(Package Body)
实现包规范中声明的子程序
可以包含私有对象(仅在包内可见)
实现包初始化代码
包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。
包定义和包主体要分开编译，并作为两部分分开的对象存放在数据字典中(user_source/all_source/dba_source),声明的时候包头和包体的变量类型一致，先执行包头，在执行包体。
二、包的优势
模块化：将相关功能组织在一起
封装性：隐藏实现细节，暴露清晰接口
性能提升：首次调用时整个包被加载到内存
减少依赖：修改包体不会使依赖对象失效
全局共享：包变量在会话期间保持状态
三、创建包
1. 包规范语法
CREATE [OR REPLACE] PACKAGE package_name
[IS|AS]
-- 公共类型和常量声明
-- 公共变量声明
-- 公共异常声明
-- 公共游标声明
-- 过程和函数声明
END [package_name];
-- 简单举例
create or replace package pak_name as|is
function fun_1(v1 in|out|in out 类型) return 类型;
function fun_2(v2 in|out|in out 类型) return 类型;
……
procedure pro_1(v3 in|out|in out 类型);
procedure pro_2(v4 in|out|in out 类型);
……
end pack_name;2. 包体语法
CREATE [OR REPLACE] PACKAGE BODY package_name
[IS|AS]
-- 私有类型和常量声明
-- 私有变量声明
-- 私有异常声明
-- 私有游标声明
-- 过程和函数实现
[BEGIN -- 初始化代码]
END [package_name];
-- 简单举例
create or replace package body pak_name as|is
function fun_1(v1 in|out|in out 类型) return 类型
as|is
begin 要执行的语句;
return
end;
function fun_2(v2 in|out|in out 类型) return 类型
as|is
begin 要执行的语句;
return
end;
……
procedure pro_1(v3 in|out|in out 类型)
as|is
begin 要执行的语句;
end;
procedure pro_2(v4 in|out|in out 类型)
as|is
begin 要执行的语句;
end;
……
end pak_name;四、包示例
1. 完整包示例
包规范(emp_pkg.sql):
CREATE OR REPLACE PACKAGE emp_pkg IS
-- 公共常量
c_min_salary CONSTANT NUMBER := 5000;
-- 公共异常
e_invalid_dept EXCEPTION;
-- 公共游标
CURSOR emp_by_dept_cursor(p_dept_id NUMBER) RETURN employees%ROWTYPE;
-- 函数声明
FUNCTION get_emp_count(p_dept_id NUMBER) RETURN NUMBER;
-- 过程声明
PROCEDURE update_salary(
p_emp_id IN NUMBER,
p_percent IN NUMBER DEFAULT 5,
p_rows_updated OUT NUMBER
);
PROCEDURE hire_employee(
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2,
p_job_id IN VARCHAR2 DEFAULT 'SA_REP',
p_salary IN NUMBER DEFAULT c_min_salary,
p_dept_id IN NUMBER DEFAULT 30
);
END emp_pkg;
/包体(emp_pkg_body.sql):
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
-- 私有变量
v_hire_date DATE := SYSDATE;
-- 私有函数
FUNCTION validate_dept(p_dept_id NUMBER) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM departments
WHERE department_id = p_dept_id;
RETURN v_count > 0;
END validate_dept;
-- 实现公共游标
CURSOR emp_by_dept_cursor(p_dept_id NUMBER) RETURN employees%ROWTYPE IS
SELECT * FROM employees
WHERE department_id = p_dept_id;
-- 实现公共函数
FUNCTION get_emp_count(p_dept_id NUMBER) RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM employees
WHERE department_id = p_dept_id;
RETURN v_count;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END get_emp_count;
-- 实现公共过程
PROCEDURE update_salary(
p_emp_id IN NUMBER,
p_percent IN NUMBER DEFAULT 5,
p_rows_updated OUT NUMBER
) IS
BEGIN
UPDATE employees
SET salary = salary * (1 + p_percent/100)
WHERE employee_id = p_emp_id;
p_rows_updated := SQL%ROWCOUNT;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_rows_updated := 0;
ROLLBACK;
RAISE;
END update_salary;
PROCEDURE hire_employee(
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2,
p_job_id IN VARCHAR2 DEFAULT 'SA_REP',
p_salary IN NUMBER DEFAULT c_min_salary,
p_dept_id IN NUMBER DEFAULT 30
) IS
BEGIN
-- 验证薪资
IF p_salary < c_min_salary THEN
RAISE_APPLICATION_ERROR(-20001, '薪资不能低于最低标准 ' || c_min_salary);
END IF;
-- 验证部门
IF NOT validate_dept(p_dept_id) THEN
RAISE e_invalid_dept;
END IF;
-- 插入新员工
INSERT INTO employees (
employee_id, first_name, last_name, email,
job_id, salary, department_id, hire_date
) VALUES (
employees_seq.NEXTVAL, p_first_name, p_last_name, p_email,
p_job_id, p_salary, p_dept_id, v_hire_date
);
COMMIT;
EXCEPTION
WHEN e_invalid_dept THEN
DBMS_OUTPUT.PUT_LINE('错误: 无效的部门ID ' || p_dept_id);
ROLLBACK;
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('错误: 邮箱地址已存在');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
ROLLBACK;
END hire_employee;
-- 初始化代码(可选)
BEGIN
DBMS_OUTPUT.PUT_LINE('员工包已初始化 ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD'));
END emp_pkg;
/2、完整简单示例
--创建一个包，里面包含fun_1,fun_2,pro_1.
--fun_1函数，输入一个员工编号返回其部门名称；
--fun_2函数，输入一个姓名返回其入职日期；
--pro_1 存储过程 根据输入的部门编号，打印部门编号和所在地；
------------------------------------------------------------------------
create or replace package pak_name as--------------------------创建包头
function fun_1(v1 in number) return varchar2;
function fun_2(v1 in varchar2) return date;
procedure pro_1(v1 in number);
end pak_name;
/
------------------------------------------------------------------------
create or replace package body pak_name as---------------------创建包体
function fun_1(v1 number) return varchar2 as
v_dname varchar2(20);
begin
select dname
into v_dname
from emp a
inner join dept b
on a.deptno = b.deptno
where empno = v1;
return v_dname;
end;
function fun_2(v1 varchar2) return date as
v_date date;
begin
select hiredate into v_date from emp where ename = v1;
return v_date;
end;
procedure pro_1(v1 number) as
v_loc dept.loc%type;
begin
select loc into v_loc from dept where deptno = v1;
end;
end pak_name;
/
------------------------------------------------------------------------
select pak_name.fun_2('SCOTT')from dual----------------------调用包中函数五、包的使用
1. 调用包中的子程序
-- 调用函数
DECLARE
v_count NUMBER;
BEGIN
v_count := emp_pkg.get_emp_count(10);
DBMS_OUTPUT.PUT_LINE('部门10有 ' || v_count || ' 名员工');
END;
/
-- 调用过程
DECLARE
v_rows NUMBER;
BEGIN
emp_pkg.update_salary(100, 10, v_rows);
DBMS_OUTPUT.PUT_LINE('更新了 ' || v_rows || ' 条记录');
END;
/
-- 使用包游标
DECLARE
v_emp employees%ROWTYPE;
BEGIN
OPEN emp_pkg.emp_by_dept_cursor(20);
LOOP
FETCH emp_pkg.emp_by_dept_cursor INTO v_emp;
EXIT WHEN emp_pkg.emp_by_dept_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.employee_id || ': ' || v_emp.last_name);
END LOOP;
CLOSE emp_pkg.emp_by_dept_cursor;
END;
/2. 使用包常量
BEGIN
DBMS_OUTPUT.PUT_LINE('最低薪资标准: ' || emp_pkg.c_min_salary);
END;
/3. 处理包异常
BEGIN
emp_pkg.hire_employee(
'John', 'Doe', 'JDOE','IT_PROG', 4500, 10
);
EXCEPTION
WHEN emp_pkg.e_invalid_dept THEN
DBMS_OUTPUT.PUT_LINE('错误: 提供的部门ID无效');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('错误代码: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);
END;
/六、包的重载
Oracle包支持子程序重载(同名不同参数):
包里的函数或者存储过程名字相同但是参数类型不同，通过传入值得不同得到不同的结果。
CREATE OR REPLACE PACKAGE overload_pkg IS
-- 根据ID获取员工名
FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2;
-- 根据邮箱获取员工名
FUNCTION get_employee_name(p_email VARCHAR2) RETURN VARCHAR2;
-- 根据ID和姓氏获取全名
FUNCTION get_employee_name(p_emp_id NUMBER, p_last_name VARCHAR2) RETURN VARCHAR2;
END overload_pkg;
/
CREATE OR REPLACE PACKAGE BODY overload_pkg IS
FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2 IS
v_name VARCHAR2(100);
BEGIN
SELECT first_name || ' ' || last_name INTO v_name
FROM employees
WHERE employee_id = p_emp_id;
RETURN v_name;
END;
FUNCTION get_employee_name(p_email VARCHAR2) RETURN VARCHAR2 IS
v_name VARCHAR2(100);
BEGIN
SELECT first_name || ' ' || last_name INTO v_name
FROM employees
WHERE email = p_email;
RETURN v_name;
END;
FUNCTION get_employee_name(p_emp_id NUMBER, p_last_name VARCHAR2) RETURN VARCHAR2 IS
v_name VARCHAR2(100);
BEGIN
SELECT first_name || ' ' || last_name INTO v_name
FROM employees
WHERE employee_id = p_emp_id
AND last_name = p_last_name;
RETURN v_name;
END;
END overload_pkg;
/
/*#####################################################################################*/
--创建一个包，包含3个函数
--f4 输入员工编号 number 返回员工工资
--f4 输入部门名称 varchar2 返回部门地址
--f4 输入日期 date 返回比这个日期入职日期晚的人数
create or replace package pak_3 as--创建包头
function f4(v1 number) return number;
function f4(v2 varchar2) return varchar2;
function f4(v3 date) return number;
end pak_3;
/
create or replace package body pak_3 as--创建包体
function f4(v1 number) return number as
v_sal number;
begin
select sal into v_sal from emp where empno = v1;
return v_sal;
end;
function f4(v2 varchar2) return varchar2 as
v_loc varchar2(20);
begin
select loc into v_loc from dept where dname = v2;
return v_loc;
end;
function f4(v3 date) return number as
v_count number;
begin
select count(*) into v_count from emp where hiredate > v3;
return v_count;
end;
end pak_3;
/七、包的初始化
包体可包含初始化代码块，在首次调用时执行:
CREATE OR REPLACE PACKAGE stats_pkg IS
PROCEDURE record_usage(p_action VARCHAR2);
FUNCTION get_usage_count RETURN NUMBER;
END stats_pkg;
/
CREATE OR REPLACE PACKAGE BODY stats_pkg IS
v_count NUMBER := 0;
v_init_time TIMESTAMP;
PROCEDURE record_usage(p_action VARCHAR2) IS
BEGIN
v_count := v_count + 1;
INSERT INTO usage_log VALUES (p_action, SYSTIMESTAMP);
END;
FUNCTION get_usage_count RETURN NUMBER IS
BEGIN
RETURN v_count;
END;
-- 初始化代码
BEGIN
v_init_time := SYSTIMESTAMP;
DELETE FROM usage_log WHERE log_date < SYSDATE - 30;
COMMIT;
record_usage('PACKAGE_INIT');
END stats_pkg;
/八、包的持久状态
包变量在会话期间保持状态:
CREATE OR REPLACE PACKAGE counter_pkg IS
PROCEDURE increment;
FUNCTION get_count RETURN NUMBER;
PROCEDURE reset;
END counter_pkg;
/
CREATE OR REPLACE PACKAGE BODY counter_pkg IS
v_count NUMBER := 0;
PROCEDURE increment IS
BEGIN
v_count := v_count + 1;
END;
FUNCTION get_count RETURN NUMBER IS
BEGIN
RETURN v_count;
END;
PROCEDURE reset IS
BEGIN
v_count := 0;
END;
END counter_pkg;
/
-- 测试会话状态
BEGIN
counter_pkg.increment;
counter_pkg.increment;
DBMS_OUTPUT.PUT_LINE('当前计数: ' || counter_pkg.get_count); -- 2
counter_pkg.reset;
DBMS_OUTPUT.PUT_LINE('重置后计数: ' || counter_pkg.get_count); -- 0
END;
/九、系统内置包
Oracle提供了许多有用的内置包：
| 包名 | 主要功能 |
|---|---|
| DBMS_OUTPUT | 输出调试信息 |
| DBMS_SQL | 动态SQL处理 |
| DBMS_JOB | 作业调度(旧版) |
| DBMS_SCHEDULER | 高级作业调度 |
| DBMS_LOB | 大对象处理 |
| DBMS_RANDOM | 随机数生成 |
| DBMS_CRYPTO | 加密解密 |
| UTL_FILE | 文件I/O操作 |
| UTL_HTTP | HTTP请求 |
| UTL_SMTP | 邮件发送 |
十、最佳实践
合理设计包结构：按功能模块组织包
最小化公共接口：只暴露必要的组件
充分文档化：为包添加注释说明
错误处理：统一处理异常
避免过度依赖：减少包间的循环依赖
性能优化：将频繁使用的代码放在包中
版本控制：使用
OR REPLACE谨慎更新生产环境包
包是Oracle PL/SQL编程中最强大的特性之一，合理使用可以显著提高代码的组织性、重用性和性能。