oracle总结文档第七章

开发PL/SQL子程序和包

简介：

子程序和包都是被命名的PL/SQL块，我们可以用子程序和包将PL/SQL代码封装起来，存在oracle 数据库中，以后可以在不同的应用程序中多次调用。同过这种方式可以简化客服端的开发和维护，并提高应用程序的性能。

7.1 子程序

子程序是已命名的PL/SQL块，可带参数并可从调用环境中进行调动。

子程序的优点：

1）模块化：通过子程序可以将程序分解为可管理的、明确的逻辑模块。

2）可重用性：子程序在创建并执行后，就可以在任意数目的应用程序中使用。

3）可维护性: 子程序可以简化维护操作，因为一个子程序受到影响，只需要修改该子程序的定义。

子程序有两种：过程和函数，其中过程用于执行特定的操作，而函数则用于返回特定的数据。

7.1.1 开发存储过程

create or replace procedure

pro_name(v_num number,v_show number) is

--这里可以写is 或者是as

v_sum number(7);

begin

v_sum:= v_num+v_show;

dbms_output.put_line(v_sum);

end;

当指定参数的类型时候是不能够添加长度的v_num number 后面是没有长度的。其取值包括in、out、inout，分别代表的是输入参数、输出参数、输入输出参数。

1）建立不带参数的存储过程，下面建议是用来输出系统日期和时间。

--下面是输出系统时间

create or replace procedure

pro_name is

begin

dbms_output.put_line(systimestamp);

end;

call pro_name();

2）建立有输入参数的存储过程

3）建立带有输出参数的存储过程

在过程中使用的是out和in out参数来完成的。当定义输出参数时候必须提供out关键字。

create or replace procedure

pro_name(v_name out varchar2 ) is

begin

select b_name into v_name from t_bumen where b_id =&v_id;

exception

when no_data_found then

dbms_output.put_line('你输入的部门编号有误');

end;

--这是调用上面的过程

declare

-- 在块中调用存储过程不需要 call

-- 如果是输出参数，需要传递一个变量名称用来接收上面传递下来的值

b_name varchar2(20);

begin

pro_name(b_name);

dbms_output.put_line(b_name);

end;

4）输出参数用的in out类型

--输入输出参数 in out

create or replace procedure

pro_name_in(v_name in out varchar2) is

begin--这里是需要into 赋值的

select b_name into v_name from t_bumen where b_id=(select b_id from t_bumen where

b_name =v_name);

exception

when no_data_found then

dbms_output.put_line('你输入的部门编号有误');

end;

declare

b_name varchar2(20) :='财务部';

begin

pro_name_in(b_name);

dbms_output.put_line(b_name);

end;

5）为参数传递变量和默认值

当调用带有参数的子程序时，需要将数值或者是变量传递给参数。为参数传递变量或者是数据可以采用：位置传递（是指在调用有参数的子程序时按照参数定义的顺序，依次指定相应的变量或者是数值）

create or replace procedure

pro_weizhi(v_id number, v_name varchar2default'未知') is

begin

insert into t_bumen (b_id,b_name) values (v_id,v_name);

exception

when others then

dbms_output.put_line('未知错误');

end;

select * from t_bumen

call pro_weizhi('');

名称传递（在调用子程序时指定参数名，并用关联符号“=>”为其提供相应的数值或者是

变量）

exec pro_weizhi(v_name=>'人事部',v_id=>9);？？？对上面代码无效

组合法传递（在调用子程序时同时使用位置传递和名称传递）

6）查看程序源代码：

当建立过程后，oracle 会将过程名、源代码以及执行带啊存放到数据库词典中，当执行过程时，应用程序会按照其执行的代码直接执行，而不需要重新解析过程代码。通过查询user_source可以显示当前用户所有子程序及其源代码。

select * from user_source;

7）删除过程

drop procedure pro_name;

7.1.2 开发函数

在没有输出的函数的时候直接用call 或者是exec就可以了

函数是建立在PL/SQL的代码程序，它执行某个特定的、明确的任务。通常，函数将处理从程序的调用部分传递给它的信息，然后返回某个值。信息通过参数传递给函数，然后通过return语句返回。

1）建立不带参数的函数

--建立不带参数的函数

create or replace function

fun_name return varchar2is

v_name varchar2(20);

--在起头部分必须要有return 子句

begin

select b_name into v_name from t_bumen where b_id=&id;

return v_name;

end;

-- 调用函数

declare

v_name varchar2(20);

begin

-- 在块中调用存储过程不需要 call

-- 函数返回值，必须用变量接受，

-- 所以函数不能直接通过call 调用

v_name:=fun_name();

dbms_output.put_line(v_name);

end ;

2）建立带有输入参数的函数

--建立带有参数的函数

create or replace function

fun_name(v_id in number) return varchar2is

v_name varchar2(20);

--在起头部分必须要有return 子句

begin

select b_name into v_name from t_bumen where b_id=v_id;

return v_name;

end;

-- 调用函数

declare

v_name varchar2(20);

begin

--联想java中调用方法并且是有返回值类型的。这样就方便理解了。

v_name:=fun_name(3);

dbms_output.put_line(v_name);

end ;

5）函数调用限制

因为函数必须要有返回数据，所以只能作为表达式的一种。另外函数也可以在SQL语句一下部分调

A)Select命令选择列表

B)Where和having子句

C)Connect by ，start with，order by 以及group by子句中

D)Insert 语句中的value子句中

E)Update 命令中的sel子句中

F)在SQL语句只能调用存储函数（服务器端），而不能调用客服端的函数

H)在SQL中只能带有输入参数（in）而不能带有其他的如out 、in out之类的

J）在SQL语句中调用函数只能使用SQL所支持的标准数据类型，而不能使用PL/SQL锁支持的类型而不是PL/SQL索尼用到的类型。

G）在SQL中调用的函数不能包含insert 、update、delete语句

7.1.3 过程和函数的比较

过程函数

作为PL/SQL语句执行作为表达式的一部分调用

在规范中不包含return语句必须在规范中包含return语句

不返回任何值必须返回单个值

可以包含return但是与函数不同，不能用来返回值必须包含一条return语句

7.2 开发包

包是一种数据对象，它对相关PL/SQL类型、子程序、游标、异常、变量、和常量的封装。通过了包不仅简化了应用程序，提高了性能，而且还实现信息隐藏、子程序重载等面向对象语言所具有的的功能。

7.2.1 建立包

包是有包体和包规范组成，在建立包时首先要建立包规范，然后在建立包规范的实现。即是包体。在包规范中可以声明类型、变量、常量、异常、游标和子程序。

1）建立包规范

包规范相当于公用的对象和类型，也可是包括子程序。规范包含有应用程序所需要的资源，它相当于面向对象中编程语言的接口。

如果包规范中只声明类型、常量、变量、和异常，则不需要包体，因为使用类型、常量、变量和异常所需要的信息已经在规范中指定。是有子程序和游标才具有底层实现或定义，因此必须使用包体。在规范中用create package 命令创建

--创建包体

create or replace package

pack_name is

public type and item declarations--用于声明类型、常量、变量、异常

subprogram specifications--声明PL/SQL的子程序

end pack_name;

2）建立包体

包体包含包规范中声明的每个游标和子程序的定义并实现它们。私有生命可以包含在包体中包的初始化部分是可以选择的，它可以包含初始化在包声明的某些变量的语句。包的初始化既不能调用包，也不能将参数传递给包。因此包的初始仅运行一次创建的命令是create package body

3）调用包组件

包类定义的公用组件被其它应用程序调用时，必须加上包名作为前缀（包名.组件名）

--建立包体

create or replace package body

pack_name is

private type and item declarations

subprogram bodies

end pack_name;

其中pack_name是包的名称，必须和包规范的名称是一致的；private type and item declarations 用来声明变量、常量、类型和异常的subprogram bodies用来定义共有和私有PL/SQL子程序

create or replace package package_name is

-- 只是定义过程或者函数的名称和参数

procedure pro_name(v_id number);

end package_name;

-- 包体

create or replace package body package_name is

-- 实现包规范的过程和函数

procedure pro_name(v_id number)is

v_ename varchar2(50);--包体中自己定义的私有变量

begin

select b_name into v_ename from t_bumen

where b_id = v_id;

dbms_output.put_line(v_ename);

end;

end package_name;

call package_name.pro_name(3);

-- 删除函数和删除过程

drop procedure pro_par_inout;

drop function fun_name;

-- 删除包

drop package package_name; --包规范

drop package body package_name;--包体

7.2.2 子程序重载

在包中，允许两个或者是多个打包的子程序具有同一名称。在需要子程序接受属于不同数据类型的参数时，可以使用此选项。这就是子程序的重载。

create or replace package package_name is

procedure pro_name (ename varchar2,course char);--过程1

procedure pro_name ( ename varchar2, course number);--过程2

--过程名是相同的，只是参数不相同

end package_name;

--创建包体

create or replace package body

package_name is

procedure pro_name(ename varchar2,course char) is

begin

dbms_output.put_line('具有care参数的过程');

end pro_name;

procedure pro_name (ename varchar2,course number) is

begin

dbms_output.put_line('具有number参数的过程');

end pro_name;

end package_name;

call package_name.pro_name('参数',1);

这时候会根据你输入的值来调用里面的过程。就是下面传递的值和上面包规范中定义变量的值的类型是一致的时候就会自动调用该类的过程。

7.3.3 包的优点

1）模块化：使用包，可以封装相关类型、对象和子程序，因此每个包将帮助我们以更好的方式理解应用程序中涉及到的概念。

2）更轻松应用程序设计：

3）信息隐藏：可以建立私有和共有对象，这些对象不仅可以限制访问权限还可以保护包的完整性；

包会隐藏私有的程序。

4）新增功能：在包规范中声明的公用对象和游标可以由在环境中执行的所有过程共享，所以它们会在会话期间一直存在。

5）性能更强：首次调用打包的子程序时，整个包均加载到内存中，因此后续的调用不需要磁盘I/O，此外，已更改打包函数的定义，则oracle不需要重新编译调用子程序，因此它们不依赖于包体。

7.2.4 私有项和共有项

可以从其他PL/SQL块引用共有元素。这些元素在包规范中定义。私有元素是在包规范中定义的，并不出现在包规范中。是有元素不能在包之外中引用。包中的其他任何元素均可引用和使用私有元素。

对于包元素的引用，请使用表示法。先后顺序为：包名称点元素名称。在同一包中不需要使用表示法。

7.2.5 包中的游标

当在包规范中声明游标时，必须存在与该游标相关联的return子句。在PL/SQL块的声明部分定义游标时，return子句是游标定义的可选部分。但是在包规范中return子句是其结构的必须部分。

Return 子句指示从游标获取并返回的数据元素。实际上，这些数据由该游标的select语句确定，但是这些select语句只出现在主体中，并不会出现在包规范中。游标规范必须包含程序使用游标所需的所有信息，因此需要返回类型。

--在包体中建立游标

create or replace package package_name is

--声明一个游标

cursor cur_bumen return t_bumen%rowtype;

--生命一个过程

procedure pro_name (v_name varchar2);

end package_name;

create or replace package body package_name is

cursor cur_name return t_bumen%rowtype is

select * from t_bumen ;

procedure pro_name(v_name varchar2) is

b_name t_bumen%rowtype;

begin

open cur_name;

loop

fetch cur_name into b_name;

exit when cur_name%notfound;

dbms_output.put_line('返回值是'||b_name.b_name);

end loop;

end cur_name;

end package_name;

-- 这里为什么会报类型错误？？？

call package_name.pro_name('');

7.3 有关程序和包的信息

通过查询user_objects 数据字典视图，可以获得有关会话中创建的子程序和包的信息

select object_name ,object_type from user_objects where object_type in ('procedure','function','package');