oracle程序包介绍,oracle之程序包

是对PL/SQL类型，过程，函数，游标，异常，变量，常量的封装。

包括两部分：规范和主体

规范：是程序包的公共接口，

主体：规范的实现，以及私有例程、数据和变量。

语法：

CREATE OR REPLACE PACKAGE package_name

IS | AS

公用类型或变量常量的声明;

公用过程或函数的声明;

END package_name;

/

CREATE OR REPLACE PACKAGE BODY package_name

IS | AS

私有类型或变量常量的声明;

公用过程或函数的实现;

END package_name

规范：

规范是程序包的接口，规范中定义的所有内容都可以由调用者使用(当然需要具有EXECUTE特权)，比如规范中定义的过程函数可以被执行，类型可以被访问，变量可以被引用。

例子：使用两个过程PRINT_ENAME() 和PRINT_SAL()，定义称为EMPLOYEE_PKG的程序包。

CREATE OR REPLACE

PACKAGE employee_pkg as

Procedure print_ename(p_empno number);

Procedure print_sal(p_empno number);

End;

/

并没有为过程提供代码，只是定义了名称和参数。

这个时候如果试图使用这个包，会报错

exec employee_pkg.print_ename(1234);

主体：

程序包是过程，函数的具体实现部分，实现规范中定义的接口。

CREATE OR REPLACE

PACKAGE BODY employee_pkg as

Procedure print_ename(p_empno number) is

L_ename emp.ename%type;

Begin

Select ename into l_ename from emp where empno=p_empno;

Dbms_output.put_line(l_ename);

Exception

When no_data_found then

Dbms_output.put_line(‘Invalid employee number’);

End print_ename;

Procedure print_sal(p_empno number) is

L_sal emp.sal%type;

Begin

Select sal into l_sal from emp where empno=p_empno;

Dbms_output.put_line(l_sal);

Exception

When NO_DATA_FOUND then

Dbms_output.put_line(‘Invalid employee number’);

End print_sal;

End employee_pkg;

/

执行：

set serveroutput on

exec employee_pkg.print_ename(1234);

exec employee_pkg.print_ename(7782);

exec employee_pkg.print_sal(7782);

过程和函数的重载：

在单独的程序包中定义的共享相同名称的两个或者多个过程和函数。单独的过程和函数必须具有唯一的名称，但是程序包中可以有条件的共享相同的名称。

条件：参数列表在数量、次序或者参数类型上有所区别，比如

procedure foo(p1 varchar2);

procedure foo(p1 number);

procedure foo(p1 varchar2, p2 number);

procedure foo(p1 varchar2, p2 varchar2);

但是：只在参数名称、输入输出模式、或者返回类型上不同，是不够的。比如

procedure foo(p1 varchar2);

procedure foo(p2 varchar2);

procedure foo(p1 in varchar2);

procedure foo(p1 in out varchar2);

例如

desc dbms_output

试验：重载SWAP()

之前的例子：

create or replace

procedure swap(

p1 in out number,

p2 in out number ) as

---

l_temp number;

begin

l_temp := p1;

p1 := p2;

p2 := l_temp;

end swap;

/

该例子只能实现交换数值，我们将其包装，并进行重载，使其也可以交换varchar2 和 date

(1) 建立规范

create or replace package utilities as

procedure swap(p1 in out number, p2 in out number);

procedure swap(p1 in out varchar2,p2 in out varchar2);

procedure swap(p1 in out date,p2 in out date);

end utilities;

/

(2) 建立主体

create or replace package body utilities as

procedure swap(p1 in out number, p2 in out number ) as

l_temp number;

begin

dbms_output.put_line(‘Swapping number’);

l_temp := p1;

p1 := p2;

p2 := l_temp;

end swap;

procedure swap(p1 in out varchar2,p2 in out varchar2) as

l_temp varchar2(32767);

begin

dbms_output.put_line(‘Swapping varchar2’);

l_temp := p1;

p1 := p2;

p2 := l_temp;

end swap;

procedure swap(p1 in out date,p2 in out date) as

l_temp date;

begin

dbms_output.put_line(‘Swapping date’);

l_temp := p1;

p1 := p2;

p2 := l_temp;

end swap;

end utilities;

/

(3) 测试结果：

set serveroutput on

declare

l_num1 number :=1;

l_num2 number :=2;

l_date1 date:= sysdate;

l_date2 date:= sysdate +1;

begin

utilities.swap(l_num1,l_num2);

dbms_output.put_line(‘l_num1=’|| l_num1);

dbms_output.put_line(‘l_num2=’|| l_num2);

utilities.swap(l_date1,l_date2);

dbms_output.put_line(‘l_date1=’|| l_date1);

dbms_output.put_line(‘l_date2=’|| l_date2);

end;

/

程序包中的游标：

在PL/SQL块中，return 子句是游标的可选部分。

在规范中声明游标的时候，必须存在与游标关联的return 子句，表示从游标获取的数据元素类型。

并且游标的select 子句只出现在主体中，不出现在规范中。

Create or replace package cur_pack is

Cursor ord_cur return order_master%rowtype;

Procedure ord_pro (orno varchar2);

End;

/

Create or replace package body cur_pack is

Cursor ord_cur return order_master%rowtype is

Select * from order_master;

Procedure ord_pro (orno varchar2) is

Or_rec order_master%rowtype;

Begin

Open ord_cur;

Loop

Fetch ord_cur into or_rec;

Exit when ord_cur%notfound;

Dbms_output.put_line(‘Return ’|| or_rec.orderno);

End loop;

End ord_pro;

End cur_pack;

/

PRAGMA RESTRICT REFERENCES

P206

限定函数的纯度级别

P197

WNDS: write no data status

RNDS: read no data status

WNPS: write no package status

RNPS: read no package status

Create or replace package pack_me is

Procedure order_proc(orno varchar2);

Function order_fun (ornos varchar2) return varchar2;

Pragma restrict_references(order_fun, WNDS,RNDS);

END;

/

数据字典：

select object_name, object_type

from user_objects

where object_type in (‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’);

查看用户代码：

desc user_source

select text from user_source where name=’INSERT_INTO_T’ order by line;

desc employee_pkg

包装工具：

将utilities包的主体部分保存在utilities.sql文件中，通过wrap工具加密，只生成oracle认识的格式，保护智力成果

wrap iname=utilities.sql

生成utilities.plb

规范部分：

create or replace package utilities as

procedure swap(p1 in out number, p2 in out number);

procedure swap(p1 in out varchar2,p2 in out varchar2);

procedure swap(p1 in out date,p2 in out date);

end utilities;

/

select text from user_source where name='UTILITIES';