包和子程序

程序包的优点

模块化：在程序包中可以包含过程函数、触发器

更轻松的应用程序设计：

信息隐藏：把实现过程封装在包中，使得访问时无法看到实现过程

新增功能：定义公共的类型和变量，这是存储过程和函数无法实现的

性能更佳：已经经过编译，不需要每次都重复编译

1、PL/SQL为了满足程序模块化的需要，除了块（block）和子程序结构外，还引入了包的构造。

2、程序包是对相关过程、函数、变量、游标和异常等对象的封装

3、程序包由规范和主体两部分组成

 

程序包的定义和使用规范

包规范：

1、将相关对象存储在一起的PL/SQL结构

2、包含二个分离的部件：包和包体

包的定义形式如下

包说明（公共的）：

Package 包名 is

--变量说明;

--游标说明;

--例外说明;

--记录说明;

--Plsql表说明;

--过程说明;

--函数说明;

End[包名];

create or replace package ClassPackage as

procedure addStudent(p_studentid in student.studentid%type,

                     p_department in classes.department%type,

                     p_course in classes.course%type);--定义存储过程

procedure removeStudent(p_studentid in student.studentid%type,

                        p_department in classes.department%type,

                        p_course in classes.course%type);--定义存储过程

e_StudentNotRegistered exception;

type t_StudentIDTable is table of student.studentid%type

index by binary_integer;--定义类型

procedure ClassList(p_department in classes.department%type,

                    p_course in classes.course%type

                    p_IDs out t_StudentIDTable,

                    p_NumStudents in out binary_integer);--定义存储过程

end ClassPackage;

包主体（私有的）:

Package body 包名 is

--变量名说明;

--游标说明;

--游标申明;

--例外说明;

--记录说明;

--plsql说明;

--过程体;

--函数体;

Begin

--语句序列

End [包名];

create or replace package body ClassPackage as

procedure addStudent(p_studentid in student.studentid%type,

                     p_department in classes.department%type,

                     p_course in classes.course%type) is

begin 

  insert into registered_students(student_id,department,course)

  values(p_studentid,p_department,p_course);

  commit;

  end addStudent;

... ...(实现其他在包规范中定义的内容)

... ...(实现其他在包规范中定义的内容)

... ...(实现其他在包规范中定义的内容)

end ClassPackage

程序包用例1：（程序包的用法）

--定义包规范

create or replace package sales_report

as

cursor salescur return emp%rowtype;

end;

--包主体

create or replace package body sales_report

as 

cursor salescur return emp%rowtype

is 

select * from emp;

end;

调用：

SQL> declare ename emp%rowtype;

  2      begin

  3       open sales_report.salescur;

  4      loop

  5      fetch sales_report.salescur into ename;

  6      exit when sales_report.salescur%notfound;

  7      if ename.sal>3000 then

  8      dbms_output.put_line(ename.ename||'你的工资还可以啊！');

  9      elsif ename.sal<=3000 then

 10     dbms_output.put_line(ename.ename||'你的工资有点低哦！');

 11     end if;

 12     end loop;

 13     close sales_report.salescur;

 14     end;

 15  /

程序包用例2：（程序包的用法）

--定义包规范

create or replace package emp_data

as

type emprectype is record

(emp_id number(5),

emp_name varchar2(10),

job_title varchar2(9),

dept_name varchar2(14),

dept_loc varchar2(15));

--定义类型

type empcurtype is ref cursor return emprectype;

--定义存储过程

procedure get_staff(dept_no in number,emp_cv in out empcurtype);

end;

--包主体

create or replace package body emp_data

as

procedure get_staff(dept_no in number,emp_cv in out empcurtype)

  is

  begin 

    open emp_cv for select empno,ename,job,dname,log from emp,dept

    where emp.deptno = dept_no and emp.deptno = dept.deptno

    order by emp.empno;

  end;

end;

运行：

SQL> set autoprint on

SQL> var cv refcursor

SQL> exec emp_data.get_staff(20,:cv);

程序包定义规则

1、包部件可以是任意顺序出现；

2、并非所有部件都必须被使用，所定义的变量不一定要被使用；

3、对函数/事件的声明，必须是前向声明。也就是说在包规范部分被声明，包的主体才能被使用；

4、在编译包主体时，先编译包规范说明；

5、包主体也不是必须有的；

6、包内定义的函数/过程对外是可见的；

7、包内定义的函数/过程可以被重载。

程序包内函数/过程重载说明

1、两个子程序的参数仅在名字或模式上是不同的，那么不能重载

2、不能根据返回类型的不同来重载两个函数

程序包的初始化

1、包第一次调用时被调入内存

2、可以添加初始化脚本来初始化包

Create or replace package body

Begin

 Inital_code

End;

创建程序包

(1)程序包规范

Create [or replace] 

Package

Package_name IS|AS

[Public item declarations]

[Subprogram specification]

End [package_name]

(2)程序包主体

Create [or replace] 

Package body

Package_name is|as

[private item declarations]

[subprogram bodies]

Begin

[ initialization]

End [package_name]

程序包实例3：（创建程序包）

包的规范部分

Create or replace package pack_me

Is

Procedure order_proc(orno varchar2);

Function order_fun(ornos varchar2) return varchar2;

End pack_me;

包的主体部分

Create or replace package body pack_me as 

Procedure order_proc(orno varchar2) is

Stat char(1);

Begin

Select ostatus into stat from order_master

Where orderno=orno;

......

End order_proc;

Function order_fun(ornos varchar2)

Return varchar2

Is

Icode varchar2(5);

Ocode varchar2(5);

Begin

......

End order_fun;

End pack_me;

程序包中的游标

1、游标的定义分为游标规范和游标主体两部分

2、在包规范中声明游标规范时必须使用RETURN字句指定游标的返回类型

3、RETURN字句指定的数据类型可以是：用%ROWTYPE属性引用表定义的记录类型；程序员定义的记录类型

程序包实例4：（程序包中的游标）

包的规范部分

Create or replace package cur_pack is

Cursor ord_cur(vcode varchar2) return order_master%rowtype;

Pricedure ord_pro(vcode varchar2);

End cur_pack;

包的主体部分

Create or replace package body cur_pack as

Cursor ord_cur(vcode varchar2)

Return order_master%rowtype is

Select * from order_master where vencode=vcode;

Procedure ord_pro(vcode varchar2) is

Or_rec order_master%rowtype;

Begin

Open ord_cur(vcode);

Loop

Fetch ord_cur into or_rec;

Exit ehtn ord_cur%notfound

Dbms_output.put_line(‘返回值为’||or_rec.orderno);

End loop;

End ord_pro;

End cur_pack;

程序包实例5：（程序包的使用）

包规范

create or replace package toyspack

is

  procedure updatetoyprice;

  function avgtoyprice return number;

end; 

包主体

create or replace package body toyspack

is

  procedure updatetoyprice

    as

    avgprice number;

  begin 

    avgprice :=avgtoyprice;

    while(avgprice<=400)

    loop

      update my_toys set price = case when price*1.1<500 then price*1.1

      else price

      end;

      avgprice:=avgtoyprice;

     end loop;

     commit;

  end;

  function avgtoyprice 

    return number

  as

    avgprice number;

  begin

    select avg(price) into avgprice from my_toys;

    return avgprice;

  end;

end;

调用

SQL> set serveroutput on

SQL> select * from my_toys;

 

ID         NAME                 PRICE

---------- --------------- ----------

P001       Doll                   100

P002       Batman                 125

P003       Spiderman              110

P004       He-man                 200

 

SQL> execute toyspack.updatetoyprice;

 

PL/SQL procedure successfully completed

 

SQL> select * from my_toys;

 

ID         NAME                 PRICE

---------- --------------- ----------

P001       Doll            345.227121

P002       Batman          431.533901

P003       Spiderman       379.749833

P004       He-man          471.589538

 

SQL> var avgnum number

SQL> execute :avgnum:=toyspack.avgtoyprice;

 

PL/SQL procedure successfully completed

avgnum

---------

407.025098755422

 

SQL> print avgnum;

avgnum

---------

407.025098755422

程序包实例6：（程序包的使用）

创建一个包：生成一个管理雇员工资的包sal_package，其中包括一个为雇员的加薪过程和降薪过程

包规范

create or replace package sal_package

is

 procedure raise_sal(v_empno emp.empno%type,v_sal_increment emp.sal%type);

 procedure reduce_sal(v_empno emp.empno%type,v_sal_reduce emp.sal%type);

 v_raise_sal emp.sal%type:=0;

 v_reduce_sal emp.sal%type:=0;

end;

包主体

create or replace package body sal_package

is

 procedure raise_sal(v_empno emp.empno%type,v_sal_increment emp.sal%type)

   is

   begin

     update emp set sal=sal+v_sal_increment where empno = v_empno;

     commit;

     v_raise_sal:=v_raise_sal+v_sal_increment;

   end;

 procedure reduce_sal(v_empno emp.empno%type,v_sal_reduce emp.sal%type)

   is 

   begin

     update emp set sal=v_sal_reduce where empno = v_empno;

     commit;

     v_raise_sal:=v_raise_sal+v_sal_reduce;

   end;

end;

调用

SQL> execute sal_package.raise_sal(7369,1000);

SQL> exec sal_package.reduce_sal(7369,1000);

子程序

命名的PL/SQL块，编译并存储在数据库中

子程序的各个部分：声明部分、可执行部分、异常处理部分（可选）

子程序的分类：过程-执行某些操作；函数-执行操作并返回值

子程序的优点

1、模块化：将程序分解为逻辑模块

2、可重用性：可以被任意数目的程序调用

3、可维护性：简化维护操作

4、安全性：通过设置权限，是数据更安全

有关子程序和程序包的信息

User_objects视图包含用户创建的子程序和程序包信息

SQL> select object_name,object_type from user_objects where object_type in('PROCEDUAL','FUNCTION','PACKAGE');

User_source 视图存储子程序和程序包的源代码

SQL> select line,text from user_source where name = 'SAL_PACKAGE';