pl sql 存储过程

初次接触存储过程，希望共同成长,加油...............



现在的java的数据库-关系映射技术似乎不提倡用存储过程，其实存储过程更能发挥数据库的效率。
1  引言
存储过程因其执行效率高、与事务处理的结合、运行更安全等优点，在数据库应用程序中被广泛采用。PL/SQL是用于从各种环境中访问Oracle数据库的一种编程语言，它与数据库服务器集成在一起，PL/SQL编写的存储过程编译效率高，网络系统开销小，同时PL/SQL直观性好，是大多数人的选择。
以Number、Varchar等基本标量类型为输出参数的PL/SQL存 储过程，每个输出参数只能返回一个对应值。而在实际数据库应用程序中，尤其是在进行系统综合查询统计时，往往需要返回二维数组或结果集，这时应考虑在存储 过程中使用集合这种数据结构。对于集合，我们可以一次把许多元素作为一个整体进行操作，也可以对集合中的单个元素进行操作，使用方便、灵活。

2  PL/SQL存储过程及Java程序的编写
2.1  索引表作为输出参数


索引表是无约束的，其大小的唯一限制（除可用内存外）就是它的关键字BINARY_INTEGER类型所能表示数值的约束（-2147483647...+2147483647），其元素不需要按任何特定顺序排列。在声明时，我们不需要指定其大小，而且对索引表的元素可以直接赋值，不用初始化，可见使用索引表极其方便。2.1.1存储过程的编写
我们可以在PL/SQL语句块中定义索引表，但作为输出参数的索引表，必须要在包（package）里定义，方法如下：
create or replace package out_param is                                        
---- 定义了元素是varchar2类型的一个索引表类型
type out_index_table_typ is table of varchar2(50) index by binary_integer;
end out_param;
接下来就可以在pl/sql存储过程里引用在包里定义的索引表类型：
                   create or replace procedure testPro1(in_param in varchar2,o_table out  out_param. out_index_table_typ ) is
                      begin
                          -------这里略去程序体
                      end  testPro1;
          其中，返回的索引表类型前必须用包名加上句点来引用out_param. out_index_table_typ
2.1.2  Java程序的编写
索引表与数据库表很形似，有key和value两列，但它不是真正的数据库表，不可以存储到数据库中。因此索引表不能使用SQL进行操作，这样它的内容不能通过一个标准的SELECT语句返回游标得到。这一点与嵌套表有很大不同。由存储过程返回的索引表可以映射成java数组类型、JDBC Datatypes的BigDecimal[]数组类型和oracle的Datum[]数组。有一点要注意，尽管索引表中的元素不一定要按任何特定顺序排列，其元素可以借助于任意有效关键字而插入，但对映射数组元素的引用应该从1开始，而且要连续，否则映射成数组时会出现null元素。
下面示例为将索引表映射成java数组类型。
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;
public class ReturnIndexTable
{  Connection ociconn=null;
OracleCallableStatement stmt =null;
public String[] getTable(String in_param)
{  String[] reAry=null;
try
{  OracleDriver S_Driver=null;
if(S_Driver==null)
S_Driver=new oracle.jdbc.OracleDriver();
DriverManager.registerDriver(S_Driver);
String url="jdbc:oracle:oci8:@test";
String user="user";
String password=" password";
ociconn= DriverManager.getConnection(url,user,password);
stmt =(OracleCallableStatement)ociconn.prepareCall("begin testPro1(?,?); end;");
// 返回的索引表最大长度（可以大于索引表实际长度）
int  maxLen =31;
// 索引表元素类型
int  elemSqlType = OracleTypes.VARCHAR;
//索引表元素长度（CHAR, VARCHAR or RAW），其它元素类型可忽略该项值，但该参数仍须定义
int  elemMaxLen=50;
stmt.setString(1,in_param);
// 注册返回参数
stmt.registerIndexTableOutParameter(2,maxLen,elemSqlType,elemMaxLen);
stmt.execute();
// 返回数组类型
reAry=(String[])stmt.getPlsqlIndexTable(2);
}
catch (Exception e)
{e.printStackTrace();
}
finally
{return reAry;
}
}
//关闭连接.............
}
2.2  可变数组作为输出参数


          可变数组和另外两种集合类型不同，其元素在内存中是连续存储的，且在大小方面有一个固定的上界。声明时需要指定该数组中元素的最大数目（可变数组的大小可以用EXTEND方法来增加，但不能被扩展超过所声明的极限大小）。

可变数组的元素被赋值之前，必须使用构造器进行初始化。元素插入数组时应从索引1开始，连续插入。
2.2.1 存储过程的编写
可变数组的定义方法如下：
create or replace type testArray is varray(5) of number(3)
         PL/SQL存储过程里调用可变数组作为输出参数：
create or replace function getTestArray return testArray
as
   o_data testArray:= testArray (); 
begin
    for v_count in 1..5 loop
      o_data.extend;
      o_data(v_count):= v_count;
    end loop;
   return o_data;
end;
2.2.2  Java程序的编写
        由存储过程返回的可变数组同样可以映射成java数组类型。但Java程序调用存储过程返回的可变数组方式和索引表方式却不相同，这一点应注意，具体方法如下：
public static void main( )
{
......
//调用存储过程
.           OracleCallableStatement stmt =(OracleCallableStatement)conn.prepareCall ( "begin ? := getTestArray; end;" );   
            stmt.registerOutParameter( 1, OracleTypes.ARRAY," testArray" );
            stmt.executeUpdate();
            // 得到 ARRAY 对象
            ARRAY simpleArray = stmt.getARRAY(1);
//转换为java数组
            String[ ] values = (String[])simpleArray.getArray();
//输出数组内容
            for( int i = 0; i < values.length; i++ )
             System.out.println( "row " + i + " = '" + values[i] +"'" );
}
2.3  嵌套表作为输出参数
存 储过程中使用嵌套表，并不是直接将嵌套表作为输出参数，而是对嵌套表"造型"后以游标形式输出。嵌套表的基本功能与索引表相同，但嵌套表可以使用SQL进 行操作，其内容可通过SELECT 语句查询并"造型"后以游标形式返回。在大多数的查询统计中，常常需要返回结果集，这时使用嵌套表就尤其方便。笔者在开发过程中深刻体会到使用对象嵌套表 可以解决绝大多数的查询统计问题。下面着重介绍如何在存储过程里利用对象类型的嵌套表。
对于任意的统计分析表格，我们可以将其简化成下面的输出形式：
  
       统计项目1
统计项目2
统计项目3
统计项目4

名称1
 
 
 
 

名称2
 
 
 
 

这样我们把每一行看作是一个对象实例，该行的每一列则可以看作是该对象的一个属性，下面通过构造对象，然后对包含对象的嵌套表进行造型，返回游标得到结果集。
2.3.1 存储过程的编写
  首先构造统计对象如下：
      create or replace type TestObj as object
(
vname  varchar2(20),    --名称
item1   number,        --统计项目1
item2   number,        --统计项目2
item3   number,        --统计项目3
item4   number        --统计项目4
);
构造包含对象类型的嵌套表：
create or replace type TestNestTable as table of TestObj;
定义对索引表"造型"后的输出的游标类型：
create or replace package out_param is
type out_cur is ref cursor;        
下面是嵌套表作为输出参数的存储过程：
create or replace procedure testPro2(o_cur out out_param.out_cur ) is
---- 包含对象的嵌套表变量的声明
v_objTable  TestNestTable：= TestNestTable ();
begin
                     --嵌套表变量的使用
v_objTabl.extend;
v_objTable(1):= TestObj(‘张三',12，123，123，34);
v_objTabl.extend;
v_objTable(2):= TestObj(‘李四,22，223，223，234);
--对嵌套表进行"造型"返回游标
open  o_cur  for  select * from Table(cast (v_objTable as TestNestTable) );
end  testPro2;
2.3.2  Java程序的编写
    //从游标返回结果集
public ResultSet getCursor()
{
try
{
......
stmt =(CallableStatement )conn.prepareCall("call testPro2(?)");
//注册游标对象类型
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.execute();
//返回结果集
ResultSet  Rs=(ResultSet)stmt.getObject(1);
}
catch(Exception e)
{
}
return Rs;
}
3  结束语
使用索引表和可变数组，可将返回的集合映射成Java数组。由于索引表会自动分配空间，在声明时不需要指定其大小，而且不需要初始化，使用起来比较方便。但是索引表作为输出参数只能使用oci驱动（返回游标时，可以用瘦客户驱动也可以用oci驱动），所需要的动态连接库文件（ocijdbc9.dll）要在环境变量里进行设置（例如：path=D:\oracle\ora90\BIN），在不同的环境下OCI驱动还可能出现类装载异常，所以返回索引表尽管方便，但偶尔会出现意想不到的错误。可变数组映射成Java数组简单，对于返回小数据量的结果，也是不错的选择，但使用可变数组作为输出参数，声明时必须限定该数组的大小上限，并且需使用构造器初始化。
使用嵌套表，可以对嵌套表进行SQL操作，其内容能通过对标准的SELECT 语句造型后可转化为游标输出。而且嵌套表的内容相当于session变量，当断开连接后即释放内存，但同样存在需要初始化和扩展的问题。
综 上所述，究竟采用索引表、嵌套表和可变数组中哪一种作为存储过程的输出要看具体的要求和开发环境。有一点我们需要注意，如果返回的数据量较大，以数组形式 返回，则需一次性取回所有结果，在PL/SQL里为所有结果分配空间并复制，然后将这些数据通过网络发送到客户端，客户端也同样需要分配空间接受这些数 据；而采用游标形式，只要返回一个指针，然后分批返回结果（可自定义每次返回记录的条数），而不是一次性返回所有结果，因此在客户端不需分配大块的空间存 放所有结果。可见，对于大数据量的应用程序，返回游标程序运行效率会更高。


-----------------------------------------------------------------------PL/SQL存储过程与函数
存储过程与函数

1、存储过程
存储过程的参数
形式参数和实际参数，例如，有如下一个存储过程，该过程接收一个作家代码和一个工资值，将该作家的工资改为接收到的工资值。
Java代码
create or replace procedure updateauths(  
  p_authscode auths.author_code%type,  
  p_authssalary auths.salary%type)  
as  
begin  
  update auths set salary=p_authssalary where author_code=p_authscode;  
  commit;  
end updateauths; 

create or replace procedure updateauths(
  p_authscode auths.author_code%type,
  p_authssalary auths.salary%type)
as
begin
  update auths set salary=p_authssalary where author_code=p_authscode;
  commit;
end updateauths;下面的PL/SQl块调用updateauths存储过程，将代码为A00011的作家的工资改为350元。
Java代码
declare  
  v_authorcode auths.author_code%type:='A00011';  
  v_salary auths.salary%type:=350;  
begin  
  updateauths(v_authorcode,v_salary);  
end; 

declare
  v_authorcode auths.author_code%type:='A00011';
  v_salary auths.salary%type:=350;
begin
  updateauths(v_authorcode,v_salary);
end;
v_authorcode、v_salary作为参数传递到存储过程updateauths中，这些参数是实际参数，简称实参。
p_authscode、p_authssalary就是形式参数，简称形参。
参数定义中，IN、OUT和IN OUT代表参数的三种不同模式：
IN：当调用存储过程时，该模式的形参接收对应实参的值，并且该是只读的，即不能被修改。默认为IN。
OUT：该形参被认为只能写，既只能为其赋值。在存储过程中不能读它的值。返回时，将该形参值传给相应的实参。
IN OUT：都允许。
Java代码
create or replace procedure updateauthssalary(  
  p_author_code in out auths.author_code%type,  
  p_salary in number,  
  p_name out auths.name%type) is  
  v_salary_temp number; --定义存储过程中的局部变量  
begin  
  select salary into v_salary_temp from auths where author_code=p_author_code;  
  if v_salary_temp<300 then  
    update auths set salary=p_salary where author_code=p_author_code;  
  end if;  
  select name into p_name from auths where author code=p_author_code;  
end updateauthssalary; 

create or replace procedure updateauthssalary(
  p_author_code in out auths.author_code%type,
  p_salary in number,
  p_name out auths.name%type) is
  v_salary_temp number; --定义存储过程中的局部变量
begin
  select salary into v_salary_temp from auths where author_code=p_author_code;
  if v_salary_temp<300 then
    update auths set salary=p_salary where author_code=p_author_code;
  end if;
  select name into p_name from auths where author code=p_author_code;
end updateauthssalary;(1)参数的数据类型
在定义一个存储过程参数时，不能指定CHAR类型和VARCHAR2类型形参的长度，也不能指定NUMBER形参的精度和标度。这些约束由实参来传递。
例如，下面的存储过程定义不合法，将产生一个编译错误：
Java代码
create or replace procedure proc_auths(  
  --参数定义了类型长度，将产生编译错误。  
  p_code in out varchar2(6),  
  p_salary out number(8,2)) as  
begin  
  select salary into p_salary from auths where author_code=p_code;  
end proc_auths; 

create or replace procedure proc_auths(
  --参数定义了类型长度，将产生编译错误。
  p_code in out varchar2(6),
  p_salary out number(8,2)) as
begin
  select salary into p_salary from auths where author_code=p_code;
end proc_auths;修改上面存储过程的定义为：
Java代码
create or replace procedure proc_auths(  
  --参数定义了类型长度，将产生编译错误。  
  p_code in out varchar2,  
  p_salary out number) as  
begin  
  select salary into p_salary from auths where author_code=p_code;  
end proc_auths; 

create or replace procedure proc_auths(
  --参数定义了类型长度，将产生编译错误。
  p_code in out varchar2,
  p_salary out number) as
begin
  select salary into p_salary from auths where author_code=p_code;
end proc_auths;
p_code的长度约束和p_salary的精度，标度约束由实参传递。
Java代码
delcare  
  v_code varchar2(6);  
  v_salary number(8,2);  
begin  
  v_code:='A00001';  
  proc_auths(v_code,v_salary);  
end; 

delcare
  v_code varchar2(6);
  v_salary number(8,2);
begin
  v_code:='A00001';
  proc_auths(v_code,v_salary);
end;注意，如果使用%type为参数定义类型，那么该参数具有定义在形参上而不是通过实参传递的数据长度。
Java代码
create or replace procedure query_salary(  
  p_code in out auths.author_code%type,  
  p_salary out auths.salary%type) as 

create or replace procedure query_salary(
  p_code in out auths.author_code%type,
  p_salary out auths.salary%type) as--那么由于author_code的长度为6，因此p_code的长度也为6。
(2)参数的传值方式
位置表示法、名称表示法
如有这样的存储过程
Java代码
create or replace procedure insert_auths(  
  p_code auths.author_code%type,  
  p_name auths.name%type,  
  p_sex auths.sex%type,  
  p_birthdate auths.birthdate%type) as 

create or replace procedure insert_auths(
  p_code auths.author_code%type,
  p_name auths.name%type,
  p_sex auths.sex%type,
  p_birthdate auths.birthdate%type) as下面进行两种方法的调用：
Java代码
declare  
  v_code varchar2(6);  
  v_name varchar2(12);  
  v_sex number(1);  
  v_birthdate date;  
begin  
  v_code:='A00021';  
  v_name:='张';  
  v_sex:=1;  
  v_birthdate:='5-seq-70';  
  --实参的位置顺序与形参的位置顺序相对应。---位置表示法  
  insert_auths(v_code,v_name,v_sex,v_birthdate);  
  --实参名与形参名对应，这样就可以重新排列参数的先后顺序。---命名表示法  
end; 

declare
  v_code varchar2(6);
  v_name varchar2(12);
  v_sex number(1);
  v_birthdate date;
begin
  v_code:='A00021';
  v_name:='张';
  v_sex:=1;
  v_birthdate:='5-seq-70';
  --实参的位置顺序与形参的位置顺序相对应。---位置表示法
  insert_auths(v_code,v_name,v_sex,v_birthdate);
  --实参名与形参名对应，这样就可以重新排列参数的先后顺序。---命名表示法
end;注意，位置表示法和命名表示法在一些调用中也可以混合使用。但是，如果出现第一个用命名表示法的参数时，后面的参数也必须使用命名表示法传值。
(3)参数的缺省值
如可以这样：
p_entry_date_time auths.entry_date_time%type:sysdate,
p_sex auths.sex%type default 1



2、创建函数
函数与存储过程非常类似，都有三种模式的参数。它们都可以存储在数据库中(当然过程与函数也可以不在于数据库中)，并且在块中调用。
与存储过程不同，存储过程只能作为一个PL/SQL语句调用，而函数作为表达式的一部分调用。并且它们的定义、可执行、异常处理部分是不同的。
例如，如作家表中男作家或女作家的工资在200元以上的人数大于百分之七十，则下面的函数返回TRUE，否则返回FALSE：
Java代码
create or replace function salarystat(  
  p_sex auths.sex%type)  
  return boolean is  
  v_currentsexauthors number;  
  v_maxauthors number;  
  v_returnvalue boolean;  
  v_percent constant number:=70;  
begin  
  --获得满足条件的作家的最大数。  
  select count(author_code) into v_maxauthors from auths where sex=p_sex and salary>=200;  
  select count(author_code) into v_currentsexauthors from auths where sex=p_sex;  
  if(v_maxauthors/v_currentsexauthors*100)>v_percent then  
    v_returnvalue:=true;  
  else 
    v_returnvalue:=false;  
  end if;  
  return v_returnvalue;  
end salarystat; 

create or replace function salarystat(
  p_sex auths.sex%type)
  return boolean is
  v_currentsexauthors number;
  v_maxauthors number;
  v_returnvalue boolean;
  v_percent constant number:=70;
begin
  --获得满足条件的作家的最大数。
  select count(author_code) into v_maxauthors from auths where sex=p_sex and salary>=200;
  select count(author_code) into v_currentsexauthors from auths where sex=p_sex;
  if(v_maxauthors/v_currentsexauthors*100)>v_percent then
    v_returnvalue:=true;
  else
    v_returnvalue:=false;
  end if;
  return v_returnvalue;
end salarystat;下面进行调用：
Java代码
declare  
  cursor c_auths is  
    select distinct sex from auths;  
begin  
  for v_authsrecord in c_auths loop  
    if salarystat(v_authsrecord.sex) then  
      update auths set salary=salary-50 where sex=v_authsrecord.sex;  
    end if;  
  end loop;  
end; 

declare
  cursor c_auths is
    select distinct sex from auths;
begin
  for v_authsrecord in c_auths loop
    if salarystat(v_authsrecord.sex) then
      update auths set salary=salary-50 where sex=v_authsrecord.sex;
    end if;
  end loop;
end;
return也可以用在存储过程中。在这种情况下，它没有参数。当执行了不带参数的return语句后，立刻将控制返回到调用环境，并将OUT和IN OUT模式的形参的当前值传给实参，然后继续执行调用存储过程后的语句。
在使用函数与存储过程时，一般情况下，如果只有一个返回值，则使用函数；如果有多个返回值则使用存储过程。尽管函数的参数可以是OUT模式，但是一般不这样使用。

3、删除过程与函数
drop procedure procedurename;
drop function functionname;

4、库存子程序和局部子程序
前面的子程序都是存储在数据库中的子程序，即库存子程序。这些子程序是由ORACLE命令创建的，并可在其它的PL/SQL块中调用。它们在创建时要进行编译，并将编译后的代码存储在数据库中。当子程序被调用时，编译后的代码从数据库中读出并执行。
一个子程序也可以在块的定义部分创建，这样的子程序被叫作局部子程序。
下面定义了一个局部函数formatname：
Java代码
declare  
  cursor c_allauthors is  
    select name,sex from auths;  
  v_formattedname varchar2(60);  
  function formatname(p_name in varchar2,p_sex in number)  
    return varchar2 is  
    v_sex varchar2(16);  
  begin  
    if p_sex=1 then  
      v_sex:='男';  
    else 
      v_sex:='女';  
    end if;  
    return p_name||'('||v_sex||')';  
  end formatname;  
begin  
  for v_authsrecord in c_allauthors loop  
    v_formattedname:=  
      formatname(v_authsrecord.name,v_authsrecord.sex);  
    dbms_output.put_line(v_formattedname);  
  end loop;  
end; 

declare
  cursor c_allauthors is
    select name,sex from auths;
  v_formattedname varchar2(60);
  function formatname(p_name in varchar2,p_sex in number)
    return varchar2 is
    v_sex varchar2(16);
  begin
    if p_sex=1 then
      v_sex:='男';
    else
      v_sex:='女';
    end if;
    return p_name||'('||v_sex||')';
  end formatname;
begin
  for v_authsrecord in c_allauthors loop
    v_formattedname:=
      formatname(v_authsrecord.name,v_authsrecord.sex);
    dbms_output.put_line(v_formattedname);
  end loop;
end;如上例，在无名块的定义部分创建了formatname函数。这个函数只在创建它的块中可用，它的作用域从创建它开始到结束。
局部子程序只能在定义部分的最后被创建，如果将formatname函数移到上面，将会出现编译错误。子程序必须先定义再引用。