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包规范和包体）
Oracle专题15之包

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包规范和包体）
Oracle专题15之包

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包规范和包体）
Oracle专题15之包

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之包
网址分享：http://cqcxhl.com/article/gipphp.html

重庆分公司，新征程启航

Oracle专题15之包

1、包的概述

a、什么是包？

b、包的特点？

c、包中的程序元素

e、包的组成

f、简单代码示例

2、包的创建

a、创建包规范

b、创建包体

c、包创建的过程案例代码

3、包的调用和删除

a、包的调用

b、包的调用示例1

b、包的调用示例2

c、包的调用示例3

d、如何在SQLPlus窗口中进行包的调用

e、包的删除

4、子程序重载

a、什么是子程序重载？

b、示例代码

其他资讯

重庆分公司，新征程启航

Oracle专题15之包

1、包的概述

a、什么是包？

b、包的特点？

c、包中的程序元素

e、包的组成

f、简单代码示例

2、包的创建

a、创建包规范

b、创建包体

c、包创建的过程案例代码

3、包的调用和删除

a、包的调用

b、 包的调用示例1

b、包的调用示例2

c、包的调用示例3

d、如何在SQLPlus窗口中进行包的调用

e、包的删除

4、子程序重载

a、什么是子程序重载？

b、示例代码

其他资讯

b、包的调用示例1