Oracle专题15之包
1、包的概述
a、什么是包？
- 包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合。
b、包的特点？
- 它具有面向对象程序设计语言的特点，是对PL/SQL程序设计元素（过程、函数、变量等）的封装。
- 它使程序设计模块化。
c、包中的程序元素
- 包中的程序元素分为两种：公用元素（公用组件）、私用元素（私用组件）。
e、包的组成
- 一个包由两个分开的部分组成：
- 包规范（包定义）：用于定义包的公用组件，包括常量、变量、游标、过程和函数等。
- 包体（包主体）：用于实现包规范所定义的公用过程和函数。包体不仅可用于实现公用过程和函数，而且还可以定义包的私有组件（变量、游标、过程、函数等）。
f、简单代码示例
- 创建包规范和包体的示例代码
--创建包规范 CREATE OR REPLACE PACKAGE first_package IS v_no emp.deptno%TYPE := 10; --过期 PROCEDURE query_emp(v_deptno IN NUMBER DEFAULT v_no, v_avgsal OUT NUMBER, v_cnt OUT NUMBER); END first_package; --创建包体 CREATE OR REPLACE PACKAGE BODY first_package IS PROCEDURE query_emp(v_deptno IN NUMBER DEFAULT v_no, v_avgsal) OUT NUMBER, v_cnt OUT NUMBER) IS BEGIN SELECT avg(sal), count(*) INTO v_avgsal, v_cnt FROM emp WHERE deptno = v_deptno; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('没有此部门'); WHEN OTHERS THEN dbms_output.put_line(SQLERRM); END; END first_package; - 调用包中的存储过程：
DECLARE v_avgsal NUMBER; v_cnt NUMBER; BEGIN first_package.query_emp(20, v_avgsal, v_cnt); DBMS_OUTPUT.put_line('平均工资：' || v_avgsal); DBMS_OUTPUT.put_line('总人数：' || v_cnt); END;2、包的创建
a、创建包规范
- 语法格式如下：
CREATE [OR REPLACE] PACKAGE package_name IS | AS -- 定义公用常量、变量、游标、过程、函数等 END [package_name]; 示例代码：
专注于为中小企业提供网站设计制作、成都网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业泸县免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了1000+企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。
CREATE OR REPLACE PACKAGE emp_package IS --添加员工信息的存储过程 PROCEDURE add_emp_proc (v_empno IN emp.empno%TYPE, v_ename IN emp.ename%TYPE, v_sal IN emp.sal%TYPE, v_deptno IN emp.deptno%TYPE); --删除员工信息的存储过程 PROCEDURE del_emp_proc (v_empno IN emp.empno%TYPE); END emp_package;b、创建包体
- 语法格式如下：
CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS --定义私有常量、变量、游标、过程和函数等 --实现公用过程和函数 END [package_name]; 示例代码：
CREATE OR REPLACE PACKAGE BODY emp_package IS --添加员工信息的存储过程 PROCEDURE add_emp_proc (v_empno IN emp.empno%TYPE, v_ename IN emp.ename%TYPE, v_sal IN emp.sal%TYPE, v_deptno IN emp.deptno%TYPE) IS e_2291 EXCEPTION; PRAGMA EXCEPTION_INIT(e_2291, -2291); BEGIN INSERT INTO emp(empno, ename, sal, deptno) VALUES(v_empno, v_ename, v_sal, v_deptno); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN RAISE_APPLICATION_ERROR(-20001, '员工号不能重复'); WHEN e_2291 THEN RAISE_APPLICATION_ERROR(-20002, '部门号不存在'); END; --删除员工信息的存储过程 PROCEDURE del_emp_proc (v_empno IN emp.empno%TYPE) IS BEGIN --根据员工号删除指定的员工信息 DELETE FROM emp WHERE empno = v_empno; --判断是否删除成功 IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20009, '指定删除的员工不存在'); ELSE DBMS_OUTPUT.PUT_line('删除成功'); END IF; END; END emp_package;c、包创建的过程案例代码
根据员工号查询工资，如果工资小于等于3000，工资涨500。
--创建包规范 CREATE OR REPLACE PACKAGE emp_sal_pkg IS FUNCTION get_sal(eno NUMBER) RETURN NUMBER; PROCEDURE upd_sal(eno NUMBER, salary NUMBER); END emp_sal_pkg; --包体 CREATE OR REPLACE PACKAGE BODY emp_sal_pkg IS FUNCTION get_sal(eno NUMBER) RETURN NUMBER IS v_sal emp.sal%TYPE := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = eno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010,'此员工号不存在'); END; PROCEDURE upd_sal(eno NUMBER, salary NUMBER) IS BEGIN IF salary <=3000 THEN UPDATE emp SET sal = sal + 500 WHERE empno = eno; END IF; END; END emp_sal_pkg;3、包的调用和删除
a、包的调用
- 对包内共有元素（公用组件）的调用格式为：包名.元素名称（组件名称）
- 例如：emp_package.del_emp_proc();
b、 包的调用示例1
调用emp_package包下添加员工信息的存储过程。（在本专题的第2节包的创建，已经在Oracle数据库中创建emp_package包规范和包体）
DECLARE v_empno emp.empno%TYPE := &empno; v_ename emp.ename%TYPE := '&name'; v_sal emp.sal%TYPE := &salary; v_deptno emp.deptno%TYPE := &deptno; e_dup_val EXCEPTION; e_no_dept EXCEPTION; PRAGMA EXCEPTION_INIT(e_dup_val, -20001); PRAGMA EXCEPTION_INIT(e_no_dept, -20002); BEGIN emp_package.add_emp_proc(v_empno, v_ename, v_sal, v_deptno); COMMIT; EXCEPTION WHEN e_dup_val THEN DBMS_OUTPUT.put_line(SQLERRM); WHEN e_no_dept THEN DBMS_OUTPUT.put_line(SQLERRM); ROLLBACK; END;b、包的调用示例2
- 调用emp_package包下删除员工信息的存储过程。（在本专题的第2节包的创建，已经在Oracle数据库中创建emp_package包规范和包体）
DECLARE v_empno emp.empno%TYPE := &empno; e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp, -20009); BEGIN emp_package.del_emp_proc(v_empno); COMMIT; EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(SQLERRM); ROLLBACK; END;c、包的调用示例3
- 调用emp_sal_pkg包下的过程和函数：（在本专题的第2节包的创建，已经在Oracle数据库中创建emp_sal_pkg包规范和包体）
1、调用根据员工号返回员工工资的函数
2、调用更新满足条件的员工工资的过程DECLARE v_empno emp.empno%TYPE := &empno; v_salary emp.sal%TYPE; e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp, -20010); BEGIN v_salary := emp_sal_pkg.get_sal(v_empno); emp_sal_pkg.upd_sal(v_empno, v_salary); COMMIT; EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(SQLERRM); END;d、如何在SQLPlus窗口中进行包的调用
示例1：在SQLPlus窗口中进行emp_sal_pkg包的调用，查询员工的工资信息：
SQL> VAR v_empno NUMBER SQL> EXEC :v_empno := &no PL/SQL procedure successfully completed v_empno --------- 1234 SQL> VAR v_salary NUMBER SQL> EXEC :v_salary := emp_sal_pkg.get_sal(:v_empno) begin :v_salary := emp_sal_pkg.get_sal(:v_empno); end; ORA-20010: 此员工号不存在 ORA-06512: 在 "SCOTT.EMP_SAL_PKG", line 11 ORA-06512: 在 line 1 v_salary --------- v_empno --------- 1234示例2：在SQLPlus窗口中进行emp_sal_pkg包的调用，更新员工的工资信息：（变量v_empno已经在示例1中定义）
SQL> EXEC :v_empno := &no PL/SQL procedure successfully completed v_empno --------- 7369 SQL> EXEC :v_salary := emp_sal_pkg.get_sal(:v_empno) PL/SQL procedure successfully completed v_salary --------- 800 v_empno --------- 7369 SQL> EXEC emp_sal_pkg.upd_sal(:v_empno, :v_salary) PL/SQL procedure successfully completed v_empno --------- 7369 v_salary --------- 800 SQL> SELECT ename, sal from emp WHERE empno = 7369; ENAME SAL ---------- --------- G_EASON 1300.00- 在SQLPlus环境中，可以使用 [VAR 变量名 变量类型]命令定义变量。
e、包的删除
- 可以使用DROP PACKAGE命令对不需要的包进行删除，语法如下：
DROP PACKAGE [BODY] [user.] package_name;4、子程序重载
a、什么是子程序重载？
- 所谓重载是指两个或者多个子程序有相同的名称，但是拥有不同的参数变量、参数顺序或者参数数据类型。
b、示例代码
示例：1、根据员工号或者员工姓名获取员工信息；2、根据员工号或者员工姓名删除员工的信息。
--创建包规范 CREATE OR REPLACE PACKAGE overload_pkg IS FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPE; FUNCTION get_info(name VARCHAR) RETURN emp%ROWTYPE; PROCEDURE del_emp(eno NUMBER); PROCEDURE del_emp(name VARCHAR); END; --创建包体 CREATE OR REPLACE PACKAGE BODY overload_pkg IS FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPE IS emp_record emp%ROWTYPE; BEGIN SELECT * INTO emp_record FROM emp WHERE empno = eno; RETURN emp_record; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20020, '不存在此员工'); END; FUNCTION get_info (name VARCHAR) RETURN emp%ROWTYPE IS emp_record emp%ROWTYPE; BEGIN SELECT * INTO emp_record FROM emp WHERE ename= name; RETURN emp_record; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20020, '不存在此员工'); END; PROCEDURE del_emp(eno NUMBER) IS BEGIN DELETE FROM emp WHERE empno = eno; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20020, '不存在此员工'); END IF; END; PROCEDURE del_emp(name VARCHAR) IS BEGIN DELETE FROM emp WHERE ename = name; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20020, '不存在此员工'); END IF; END; END overload_pkg;- overload_pkg包的调用示例：
--根据员工号查询员工信息 DECLARE emp_record emp%rowtype; e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp, -20020); BEGIN emp_record := overload_pkg.get_info(&no); DBMS_OUTPUT.put_line('员工号：' || emp_record.empno || '，姓名：' || emp_record.ename || '，工资：' || emp_record.sal); EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(SQLERRM); END;--根据员工姓名查询员工信息 DECLARE emp_record emp%rowtype; e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp, -20020); BEGIN emp_record := overload_pkg.get_info('&ename'); DBMS_OUTPUT.put_line('员工号：' || emp_record.empno || '，姓名：' || emp_record.ename || '，工资：' || emp_record.sal); EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(SQLERRM); END;--根据员工号删除员工信息 DECLARE e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp, -20020); BEGIN overload_pkg.del_emp(&no); COMMIT; EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(SQLERRM); ROLLBACK; END;--根据员工姓名删除员工信息 DECLARE e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp, -20020); BEGIN overload_pkg.del_emp('&ename'); COMMIT; EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(SQLERRM); ROLLBACK; END;
本文题目：Oracle专题15之包
链接URL：http://scjbc.cn/article/gipphp.html
