包是Oracle数据库中一种重要的PL/SQL程序结构,它将逻辑相关的变量、常量、游标、异常、过程和函数组织在一起,提供了更好的封装性和模块化。在大型项目中,可能有很多模块,而每一个模块又有自己的存过、函数等。而这些存过、函数默认是放在一起的,如果所有的存过函数都是放在一起的那么非常不容易查询和维护,甚至会发生误删除事件。
目录
一、包的组成
1. 包规范(Package Specification)包头、包定义
2. 包体(Package Body)
二、包的优势
三、创建包
1. 包规范语法
2. 包体语法
四、包示例
1. 完整包示例
包规范(emp_pkg.sql):
包体(emp_pkg_body.sql):
2、完整简单示例
五、包的使用
1. 调用包中的子程序
2. 使用包常量
3. 处理包异常
六、包的重载
七、包的初始化
八、包的持久状态
九、系统内置包
十、最佳实践
一、包的组成
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|isbegin 要执行的语句;returnend;
function fun_2(v2 in|out|in out 类型) return 类型as|isbegin 要执行的语句;returnend;
……
procedure pro_1(v3 in|out|in out 类型)as|isbegin 要执行的语句;end;
procedure pro_2(v4 in|out|in out 类型)as|isbegin 要执行的语句;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 ISv_count NUMBER;BEGINSELECT COUNT(*) INTO v_countFROM departmentsWHERE department_id = p_dept_id;RETURN v_count > 0;END validate_dept;-- 实现公共游标CURSOR emp_by_dept_cursor(p_dept_id NUMBER) RETURN employees%ROWTYPE ISSELECT * FROM employeesWHERE department_id = p_dept_id;-- 实现公共函数FUNCTION get_emp_count(p_dept_id NUMBER) RETURN NUMBER ISv_count NUMBER;BEGINSELECT COUNT(*) INTO v_countFROM employeesWHERE department_id = p_dept_id;RETURN v_count;EXCEPTIONWHEN OTHERS THENRETURN 0;END get_emp_count;-- 实现公共过程PROCEDURE update_salary(p_emp_id IN NUMBER,p_percent IN NUMBER DEFAULT 5,p_rows_updated OUT NUMBER) ISBEGINUPDATE employeesSET salary = salary * (1 + p_percent/100)WHERE employee_id = p_emp_id;p_rows_updated := SQL%ROWCOUNT;COMMIT;EXCEPTIONWHEN OTHERS THENp_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) ISBEGIN-- 验证薪资IF p_salary < c_min_salary THENRAISE_APPLICATION_ERROR(-20001, '薪资不能低于最低标准 ' || c_min_salary);END IF;-- 验证部门IF NOT validate_dept(p_dept_id) THENRAISE 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;EXCEPTIONWHEN e_invalid_dept THENDBMS_OUTPUT.PUT_LINE('错误: 无效的部门ID ' || p_dept_id);ROLLBACK;WHEN DUP_VAL_ON_INDEX THENDBMS_OUTPUT.PUT_LINE('错误: 邮箱地址已存在');ROLLBACK;WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);ROLLBACK;END hire_employee;-- 初始化代码(可选)BEGINDBMS_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 asv_dname varchar2(20);beginselect dnameinto v_dnamefrom emp ainner join dept bon a.deptno = b.deptnowhere empno = v1;return v_dname;end;function fun_2(v1 varchar2) return date asv_date date;beginselect hiredate into v_date from emp where ename = v1;return v_date;end;procedure pro_1(v1 number) asv_loc dept.loc%type;beginselect loc into v_loc from dept where deptno = v1;end;
end pak_name;
/
------------------------------------------------------------------------
select pak_name.fun_2('SCOTT')from dual----------------------调用包中函数
五、包的使用
1. 调用包中的子程序
-- 调用函数
DECLAREv_count NUMBER;
BEGINv_count := emp_pkg.get_emp_count(10);DBMS_OUTPUT.PUT_LINE('部门10有 ' || v_count || ' 名员工');
END;
/-- 调用过程
DECLAREv_rows NUMBER;
BEGINemp_pkg.update_salary(100, 10, v_rows);DBMS_OUTPUT.PUT_LINE('更新了 ' || v_rows || ' 条记录');
END;
/-- 使用包游标
DECLAREv_emp employees%ROWTYPE;
BEGINOPEN emp_pkg.emp_by_dept_cursor(20);LOOPFETCH 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. 使用包常量
BEGINDBMS_OUTPUT.PUT_LINE('最低薪资标准: ' || emp_pkg.c_min_salary);
END;
/
3. 处理包异常
BEGINemp_pkg.hire_employee('John', 'Doe', 'JDOE','IT_PROG', 4500, 10);
EXCEPTIONWHEN emp_pkg.e_invalid_dept THENDBMS_OUTPUT.PUT_LINE('错误: 提供的部门ID无效');WHEN OTHERS THENDBMS_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 ISFUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2 ISv_name VARCHAR2(100);BEGINSELECT first_name || ' ' || last_name INTO v_nameFROM employeesWHERE employee_id = p_emp_id;RETURN v_name;END;FUNCTION get_employee_name(p_email VARCHAR2) RETURN VARCHAR2 ISv_name VARCHAR2(100);BEGINSELECT first_name || ' ' || last_name INTO v_nameFROM employeesWHERE email = p_email;RETURN v_name;END;FUNCTION get_employee_name(p_emp_id NUMBER, p_last_name VARCHAR2) RETURN VARCHAR2 ISv_name VARCHAR2(100);BEGINSELECT first_name || ' ' || last_name INTO v_nameFROM employeesWHERE employee_id = p_emp_idAND 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 asv_sal number;beginselect sal into v_sal from emp where empno = v1;return v_sal;end;function f4(v2 varchar2) return varchar2 asv_loc varchar2(20); beginselect loc into v_loc from dept where dname = v2;return v_loc;end;function f4(v3 date) return number asv_count number;beginselect count(*) into v_count from emp where hiredate > v3;return v_count;end;
end pak_3;
/
七、包的初始化
包体可包含初始化代码块,在首次调用时执行:
CREATE OR REPLACE PACKAGE stats_pkg ISPROCEDURE record_usage(p_action VARCHAR2);FUNCTION get_usage_count RETURN NUMBER;
END stats_pkg;
/CREATE OR REPLACE PACKAGE BODY stats_pkg ISv_count NUMBER := 0;v_init_time TIMESTAMP;PROCEDURE record_usage(p_action VARCHAR2) ISBEGINv_count := v_count + 1;INSERT INTO usage_log VALUES (p_action, SYSTIMESTAMP);END;FUNCTION get_usage_count RETURN NUMBER ISBEGINRETURN v_count;END;-- 初始化代码BEGINv_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 ISPROCEDURE increment;FUNCTION get_count RETURN NUMBER;PROCEDURE reset;
END counter_pkg;
/CREATE OR REPLACE PACKAGE BODY counter_pkg ISv_count NUMBER := 0;PROCEDURE increment ISBEGINv_count := v_count + 1;END;FUNCTION get_count RETURN NUMBER ISBEGINRETURN v_count;END;PROCEDURE reset ISBEGINv_count := 0;END;
END counter_pkg;
/-- 测试会话状态
BEGINcounter_pkg.increment;counter_pkg.increment;DBMS_OUTPUT.PUT_LINE('当前计数: ' || counter_pkg.get_count); -- 2counter_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编程中最强大的特性之一,合理使用可以显著提高代码的组织性、重用性和性能。