PL/SQL-包

PL/SQL-包 原文：http://owftc.iteye.com/blog/95153

• 博客分类：
• db

SQL

包可将一些有联系的对象放在其内部。任何能在块定义部分出现的对象都可以在包中出现。这些对象包括存储过程、函数、游标、自定义的类型(例如PL/SQL表和记录)和变量。
我们可以在其它的PL/SQL块中引用包中的这些对象。也就是说，包为PL/SQL提供了全局变量。
1、包的定义
一个包由两个独立的部分组成---包头和包体。各部分被单独地存放在数据字典中。定义一个包，要分别定义包头和包体。
(1)定义包头
存储过程或函数必须在包头中预定义。也就是说，在包头中仅定义存储过程名或函数名以及它们的参数。存储过程或函数的执行代码将在包体中定义。这不同于无名块中定义存储过程和函数。

Java代码  

1. create or replace package authspack as   
2.   --获得auths表中作家的工资。   
3.   procedure querysalary(p_code auths.author_code%type,p_salary out auths.salary%type);   
4.   --向auths表插入记录。   
5.   procedure insertauthor(p_code auths.authr_code%type,p_name auths.name%type,p_sex auths.sex%type,p_birthdate auths.birthdate%type,p_entry_date_time auths.entry_date_time%type);   
6. end authspack;  

create or replace package authspack as
  --获得auths表中作家的工资。
  procedure querysalary(p_code auths.author_code%type,p_salary out auths.salary%type);
  --向auths表插入记录。
  procedure insertauthor(p_code auths.authr_code%type,p_name auths.name%type,p_sex auths.sex%type,p_birthdate auths.birthdate%type,p_entry_date_time auths.entry_date_time%type);
end authspack;

(2)定义包体
包体是一个数据字典对象。只有在包头成功编译后，包体才能被编译。包体只包含包头中已预定义的子程序的代码。在包头中定义(不是预定义)的对象可以直接在包体中使用，不必再在包体中定义。
下面是authspack包的包体：

Java代码  

1. create or replace package body authspack is   
2.   --获得auths表中作家的工资。   
3.   procedure querysalary(p_code auths.author_code%type,p_salary out auths.salary%type)  as   
4.   begin   
5.     select salary into p_salaary from auths where author_code=p_code;   
6.   end querysalary;   
7.   --向auths表插入记录。   
8.   procedure insertauthor(p_code auths.authr_code%type,p_name auths.name%type,p_sex auths.sex%type,p_birthdate auths.birthdate%type,p_entry_date_time auths.entry_date_time%type) as   
9.   begin   
10.     insert into auths(author_code,name,sex,birthdate,entry_date_time)   
11.       values(p_code,p_name,p_sex,p_birthdate,p_entry_date_time);   
12.   end insertauthor;   
13. end authspack;  

create or replace package body authspack is
  --获得auths表中作家的工资。
  procedure querysalary(p_code auths.author_code%type,p_salary out auths.salary%type)  as
  begin
    select salary into p_salaary from auths where author_code=p_code;
  end querysalary;
  --向auths表插入记录。
  procedure insertauthor(p_code auths.authr_code%type,p_name auths.name%type,p_sex auths.sex%type,p_birthdate auths.birthdate%type,p_entry_date_time auths.entry_date_time%type) as
  begin
    insert into auths(author_code,name,sex,birthdate,entry_date_time)
      values(p_code,p_name,p_sex,p_birthdate,p_entry_date_time);
  end insertauthor;
end authspack;

如果包头不包含存储过程和函数，则不必定义包体。如果包头中有预定义的子程序，则在包体中必须编写其子程序代码，而且包头和包体两部分指定的子程序必须一致，这包括相同的子程序、参数名和参数类型。
(3)包的初始化
包可被初始化，只是初始化部分在包体的最后部分被定义。

Java代码  

1. create or replace package authorinfopack as   
2.   v_auths_sex varchar2(2);   
3.   procedure author_sex(p_author_code auths.author_code%type,p_sex out varchar2);   
4. end authorinfopack;   
5. --定义包体   
6. create or replace package body authorinfopack as   
7.   procedure author_sex(p_author_code auths.author_code%type,p_sex out varchar2) as   
8.     v_sex number;   
9.   begin   
10.     select sex into v_sex from auths where author_code=p_author_code;   
11.     if v_sex=0 then   
12.       p_sex:='女';   
13.     else  
14.       p_sex:='男';   
15.     end if;   
16.   end author_sex;   
17. --初始化部分。   
18. begin   
19.   author_sex('A00001',v_auths_sex);   
20. end authorinfopack;  

create or replace package authorinfopack as
  v_auths_sex varchar2(2);
  procedure author_sex(p_author_code auths.author_code%type,p_sex out varchar2);
end authorinfopack;
--定义包体
create or replace package body authorinfopack as
  procedure author_sex(p_author_code auths.author_code%type,p_sex out varchar2) as
    v_sex number;
  begin
    select sex into v_sex from auths where author_code=p_author_code;
    if v_sex=0 then
      p_sex:='女';
    else
      p_sex:='男';
    end if;
  end author_sex;
--初始化部分。
begin
  author_sex('A00001',v_auths_sex);
end authorinfopack;

2、包的使用
(1)包中对象的引用
在包中定义的任意对象都可在包外使用，只是在引用该对象前用包名做前缀。
例如，我们可通过如下PL/SQL语句调用authspack包中的存储过程querysalary：
--设置存储缓冲区的大小。

Java代码  

1. set serveroutput on size 100000  
2. declare   
3.   v_salary auths.salary%type;   
4. begin   
5.   authspack.querysalary('A00010',v_salary);   
6.   --显示过程querysalary的查询结果。   
7.   dbms_output.put_line('A00010作家的工资为：');   
8.   dbms_output.put_line(v_salary);   
9. end;  

set serveroutput on size 100000
declare
  v_salary auths.salary%type;
begin
  authspack.querysalary('A00010',v_salary);
  --显示过程querysalary的查询结果。
  dbms_output.put_line('A00010作家的工资为：');
  dbms_output.put_line(v_salary);
end;

(2)重载包中的子程序
在包的内部，存储过程和函数都可被重载，这意味着有多个存储过程或函数可以使用同一个名称，但是参数不能相同。这样就允许用不同的参数调用同一个名字的过程或函数。
重载是非常有用的技术，但是，它也有一些约束，这些约束如下所示：
A.当仅仅参数名不同或者是模式(in、out、in out)不同时，不能重载子程序。例如：
  procedure overload(p_par in char);      procedure overload(p_par out char);　　　不能这样重载。
B.不能对仅有返回类型的函数进行重载。例如，下面这样是不能重载的：
  function overload fun return char;      function overload fun return binary_integer;
C.重载函数的参数必须是数据类型不同或其类型间不可自动转换。例如，由于char和varchar2的变量可以自动转换，因此不能重载下面的存储过程：
  procedure overloadchar(p_theparameter in char);   procedure overloadchar(p_theparameter in varchar2);
尽管在定义包含违反上述限制的子程序的包时不会报编译错误。但是，运行时PL/SQL引擎不能调用该子程序，会出现错误"PLS-307:too many declarations of 'subprogram' match this call."。

3、在SQL语句中使用的函数
通常在SQL语句中不能调用PL/SQL函数(无论是单独存储在数据库中的函数还是包中的函数)，因为PL/SQL函数是过程性的语句。
在PL/SQL2.1以上版本中放宽了这个限制，但函数必须满足特定的约束才能在SQL语句中使用。
PL/SQL为函数指定了四种基本约束，这四种基本约束如下所示：
WNDS [Writes no database state] 在函数内不能用DML语句修改数据库中的表。
RNDS [Reads no database state ] 在函数内不能通过SELECT语句来读取数据库中的表。
WNPS [Writes no package state ] 在函数内不能修改包变量(包变量不能在赋值语句的左边或一人FETCH语句的INTO语句中)。
RNPS [Reads no package state  ] 在函数内不能查询包变量(包变量不能在赋值语句的右边或不能是SQL表达式的一部分)。
当函数满足WNDS时，能够被SQL语句调用。
用户自定义函数必须符合如下约束才能被SQL语句调用，这些约束对于内嵌函数(系统提供的函数)也同样适用：
A.函数必须单独的或作为包的一部分存储在数据库中，不能作为块的一部分。
B.函数只能定义IN参数，不能定义其它两种参数。
C.形参类型必须是数据库中的数据类型，不能是PL/SQL中的数据类型(如boolean或record类型)。数据库类型包括number、char、varchar2、rowid、long、long raw和date。
D:函数返回的数据类型也必须是数据库中的数据类型。
例如，函数nameandsex将作家代码作为输入，返回作家的名字和性别：

Java代码  

1. create or replace function nameandsex(   
2.   p_authorcode auths.author_code%type)   
3.   return varchar2 AS    
4.   v_nameandsex varchar2(100);   
5. begin   
6.   select name||' '||replace(replace(sex,0,'女'),1,'男')   
7.     into v_nameandsex from auths where author_code=p_authorcode;   
8.   return v_nameandsex;   
9. end nameandsex;  

create or replace function nameandsex(
  p_authorcode auths.author_code%type)
  return varchar2 AS 
  v_nameandsex varchar2(100);
begin
  select name||' '||replace(replace(sex,0,'女'),1,'男')
    into v_nameandsex from auths where author_code=p_authorcode;
  return v_nameandsex;
end nameandsex;

下面用SQL语句调用它：
select seqno,nameandsex(author_code) "姓名和性别" from auths where author_code<'A00004'
当将单独存储在数据库中的函数用在SQL语句中时，PL/SQL引擎自动确定该函数有哪些约束，这些约束能否保证函数在SQL语句中的调用。
对于包函数，则先在包内部使用restrict_references编译指令来指定包函数的约束。在SQL语句中调用这个包函数时，PL/SQL根据编译指令指定的约束来判断包函数能否在SQL语句中调用。
restrict_references编译指令通过下面的语法指定函数的约束：
pragma restrict_references(function_name,WNDS[,WNPS][,RNDS][,RNPS]);
由于能在SQL语句中调用的函数都有WNDS基本约束，因此编译指令同样也要求这样。编译指令须在函数所在的包头中指定。

Java代码  

1. create or replace package authorpack as   
2.   function fun(p_authorcode auths.author_code%type) return varchar2;   
3.   --使用　约束   
4.   pragma restrict_references(fun,WNDS,WNPS,RNPS);   
5.      
6.   function authorcount return number;   
7.   pragma restrict_references(authorcount,WNDS,WNPS,RNPS);   
8. end authorpack;   
9. create or replace package body authorpack as   
10.   v_num number;--包变量   
11.   --函数fun的函数体满足WNPS、WNDS和RNPS约束。   
12.   function fun(p_authorcode auths.author_code%type)   
13.     return varchar2 as   
14.     v_return varchar2(16);   
15.   begin   
16.     select author_code||name into v_return from auths where author_code=p_authorcode;   
17.     return v_return;   
18.   end fun;   
19.   --函数authorcount的函数体不满足WNPS和RNPS约束。   
20.   function authorcount return number as   
21.     v_return number;   
22.   begin   
23.     if v_num is null then --包变量v_num被读，这将不满足RNPS约束。   
24.       select count(*) into v_return from auths where author_code like 'A%';   
25.       v_num:=v_return; --包变量被，这将不满足WNPS约束。   
26.     else  
27.       v_return:=v_num;   
28.     end if;   
29.     return v_return;   
30.   end authorcount;   
31. end authorpack;  

create or replace package authorpack as
  function fun(p_authorcode auths.author_code%type) return varchar2;
  --使用　约束
  pragma restrict_references(fun,WNDS,WNPS,RNPS);
  
  function authorcount return number;
  pragma restrict_references(authorcount,WNDS,WNPS,RNPS);
end authorpack;
create or replace package body authorpack as
  v_num number;--包变量
  --函数fun的函数体满足WNPS、WNDS和RNPS约束。
  function fun(p_authorcode auths.author_code%type)
    return varchar2 as
    v_return varchar2(16);
  begin
    select author_code||name into v_return from auths where author_code=p_authorcode;
    return v_return;
  end fun;
  --函数authorcount的函数体不满足WNPS和RNPS约束。
  function authorcount return number as
    v_return number;
  begin
    if v_num is null then --包变量v_num被读，这将不满足RNPS约束。
      select count(*) into v_return from auths where author_code like 'A%';
      v_num:=v_return; --包变量被，这将不满足WNPS约束。
    else
      v_return:=v_num;
    end if;
    return v_return;
  end authorcount;
end authorpack;

fun函数正常，但在包体中的authorcount函数代码并不符合指定的约束，包体中定义的变量v_num不仅被读也被修改，所以PL/SQL引擎编译到函数authorcount时，报"PLS-00452:Subprogram 'authorcount' violates its associated pragma"错误。
当遇到函数重载时，pragma只约束最过定义的函数。
注意，如果函数中用到了dbms_output、dbms_pipe、dbms_alter、dbms_sql、utl_file等系统包，则该函数不能用在SQL语句中。
当在过程性语句中调用一个函数时，可以使用参数缺省值。而通过SQL语句调用一个函数时，所有的参数都必须指定。另外，必须使用位置表示法，不能使用命名表示法。

4、系统提供的包dbms_output