Oracle数据库数据编程SQL＜3.6 PL/SQL 包(Package)＞

包是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. 模块化：将相关功能组织在一起

2. 封装性：隐藏实现细节，暴露清晰接口

3. 性能提升：首次调用时整个包被加载到内存

4. 减少依赖：修改包体不会使依赖对象失效

5. 全局共享：包变量在会话期间保持状态

三、创建包

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	邮件发送

十、最佳实践

1. 合理设计包结构：按功能模块组织包

2. 最小化公共接口：只暴露必要的组件

3. 充分文档化：为包添加注释说明

4. 错误处理：统一处理异常

5. 避免过度依赖：减少包间的循环依赖

6. 性能优化：将频繁使用的代码放在包中

7. 版本控制：使用OR REPLACE谨慎更新生产环境包

 包是Oracle PL/SQL编程中最强大的特性之一，合理使用可以显著提高代码的组织性、重用性和性能。