存储过程案例

╰半夏微凉° 2022-09-27 03:58 150阅读 0赞

\---存储过程基本语法结构

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

Oracle存储过程可以有无参数存储过程和带参数存储过程

一、无参程序过程语法

1 create or replace procedure NoParPro  
2 as  ;  
3 begin  
4 ;  
5 exception     //存储过程异常  
6     ;  
7 end;  
8  
        二、带参存储过程实例  
 1 create or replace procedure queryempname(sfindno emp.empno%type) as  
 2        sName emp.ename%type;  
 3        sjob emp.job%type;  
 4 begin  
 5        ....  
 7 exception  
          ....  
14 end;  
15  
    三、 带参数存储过程含赋值方式 1 create or replace procedure runbyparmeters  (isal in emp.sal%type,  
                            sname out varchar,sjob in out varchar)  
 2  as icount number;  
 3  begin  
 4       select count(\*) into icount from emp where sal>isal and job=sjob;  
 5       if icount=1 then  
 6         ....  
 9       else  
10         ....  
12       end if;  
13  exception  
14       when too\_many\_rows then  
15       DBMS\_OUTPUT.PUT\_LINE('返回值多于1行');  
16       when others then  
17       DBMS\_OUTPUT.PUT\_LINE('在RUNBYPARMETERS过程中出错！');  
18  end;  
19  
  四、在Oracle中对存储过程的调用  
  过程调用方式一  
 1 declare  
 2        realsal emp.sal%type;  
 3        realname varchar(40);  
 4        realjob varchar(40);  
 5  begin   //存储过程调用开始  
 6        realsal:=1100;  
 7        realname:='';  
 8        realjob:='CLERK';  
 9        runbyparmeters(realsal,realname,realjob);     －－必须按顺序  
10        DBMS\_OUTPUT.PUT\_LINE(REALNAME||'   '||REALJOB);  
11  END;  //过程调用结束  
12  
  过程调用方式二  
 1 declare  
 2       realsal emp.sal%type;  
 3       realname varchar(40);  
 4       realjob varchar(40);  
 5 begin    //过程调用开始  
 6       realsal:=1100;  
 7       realname:='';  
 8       realjob:='CLERK';  
 9       runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);  －－指定值对应变量顺序可变  
10       DBMS\_OUTPUT.PUT\_LINE(REALNAME||'   '||REALJOB);  
11 END;  //过程调用结束  
12

、、、、、、、、、、、、、、  
　1.基本结构  
　　create OR REPLACE PROCEDURE存储过程名字  
　　(  
　　参数1 IN NUMBER,  
　　参数2 IN NUMBER  
　　) IS

变量1 INTEGER :=0;  
　　变量2 DATE;  
　　BEGIN  
　　END 存储过程名字  
　　2.select INTO STATEMENT  
　　将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO\_DATA\_FOUND)  
　　例子：  
　 BEGIN  
　　select col1,col2 into 变量1,变量2 FROM typestruct where xxx;  
　　EXCEPTION  
　　WHEN NO\_DATA\_FOUND THEN  
　 　　 xxxx;  
　　END;  
　　...  
　　3.IF 判断  
　　IF V\_TEST=1 THEN  
　 　 BEGIN  
　　　　 do something  
　 　 END;  
　　END IF;  
　　4.while 循环  
　　WHILE V\_TEST=1 LOOP  
　　BEGIN  
　XXXX  
　　END;  
　 END LOOP;  
　　5.变量赋值  
　　V\_TEST := 123;  
　　6.用for in 使用cursor  
　 ...  
　 IS  
　　CURSOR cur IS select \* FROM xxx;  
　 BEGIN  
　FOR cur\_result in cur LOOP  
　 BEGIN  
　　 V\_SUM :=cur\_result.列名1+cur\_result.列名2  
　 END;  
　END LOOP;  
　　END;  
　　7.带参数的cursor  
　 CURSOR C\_USER(C\_ID NUMBER) IS select NAME FROM USER where TYPEID=C\_ID;  
　 OPEN C\_USER(变量值);  
　　LOOP  
　　FETCH C\_USER INTO V\_NAME;  
　　EXIT FETCH C\_USER%NOTFOUND;  
　　　do something  
　 END LOOP;  
　　CLOSE C\_USER;  
　　8.用pl/sql developer debug  
　　连接数据库后建立一个Test WINDOW

在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

\---于oracle存储过程的若干问题备忘

中国网管联盟[www.bitscn.com][]

1.在oracle中，数据表别名不能加as，如：

select a.appname from appinfo a;-- 正确 网管联盟[www.bitsCN.com][]  
select a.appname from appinfo as a;-- 错误  
也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正确编译 网管网bitsCN.com  
  select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：Compilation 中国网管联盟www\_bitscn\_com  
   Error: PLS-00428: an INTO clause is expected in this SELECT statement

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。  
网管联盟[www.bitsCN.com][]

可以在该语法之前，先利用select count(\*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行

select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错，提示

ORA-01422:exact fetch returns more than requested number of rows  
5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：  
create table A(  
id varchar2(50) primary key not null,  
vcount number(8) not null,  
bid varchar2(50) not null -- 外键

);  
如果在存储过程中，使用如下语句：  
select sum(vcount) into fcount from A where bid='xxxxxx';  
如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：  
if fcount is null then  
     fcount:=0;  
end if;这样就一切ok了。

///  
\--创建存储过程  
CREATE OR REPLACE PROCEDURE xxxxxxxxxxx\_p  
(  
\--参数IN表示输入参数，OUT表示输入参数，类型可以使用任意Oracle中的合法类型。  
 is\_ym  IN CHAR  
)  
AS  
\--定义变量  
 vs\_msg   VARCHAR2(4000);   --错误信息变量  
 vs\_ym\_beg  CHAR(6);      --起始月份  
 vs\_ym\_end  CHAR(6);      --终止月份  
 vs\_ym\_sn\_beg CHAR(6);     --同期起始月份  
 vs\_ym\_sn\_end CHAR(6);     --同期终止月份  
\--定义游标(简单的说就是一个可以遍历的结果集)  
 CURSOR cur\_1 IS  
 SELECT area\_code,CMCODE,SUM(rmb\_amt)/10000 rmb\_amt\_sn,SUM(usd\_amt)/10000 usd\_amt\_sn  
 FROM BGD\_AREA\_CM\_M\_BASE\_T  
  WHERE ym >= vs\_ym\_sn\_beg  
  AND ym <= vs\_ym\_sn\_end  
 GROUP BY area\_code,CMCODE;

BEGIN  
 --用输入参数给变量赋初值，用到了Oralce的SUBSTR TO\_CHAR ADD\_MONTHS TO\_DATE 等很常用的函数。  
 vs\_ym\_beg := SUBSTR(is\_ym,1,6);  
 vs\_ym\_end := SUBSTR(is\_ym,7,6);  
 vs\_ym\_sn\_beg := TO\_CHAR(ADD\_MONTHS(TO\_DATE(vs\_ym\_beg,'yyyymm'), -12),'yyyymm');  
 vs\_ym\_sn\_end := TO\_CHAR(ADD\_MONTHS(TO\_DATE(vs\_ym\_end,'yyyymm'), -12),'yyyymm');  
 --先删除表中特定条件的数据。  
 DELETE FROM xxxxxxxxxxx\_T WHERE ym = is\_ym;  
  --然后用内置的DBMS\_OUTPUT对象的put\_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount  
 DBMS\_OUTPUT.put\_line('del上月记录='||SQL%rowcount||'条');  
   
 INSERT INTO xxxxxxxxxxx\_T(area\_code,ym,CMCODE,rmb\_amt,usd\_amt)  
 SELECT area\_code,is\_ym,CMCODE,SUM(rmb\_amt)/10000,SUM(usd\_amt)/10000  
 FROM BGD\_AREA\_CM\_M\_BASE\_T  
  WHERE ym >= vs\_ym\_beg  
  AND ym <= vs\_ym\_end  
 GROUP BY area\_code,CMCODE;  
   
 DBMS\_OUTPUT.put\_line('ins当月记录='||SQL%rowcount||'条');  
 --遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的一种。  
 FOR rec IN cur\_1 LOOP  
  UPDATE xxxxxxxxxxx\_T  
  SET rmb\_amt\_sn = rec.rmb\_amt\_sn,usd\_amt\_sn = rec.usd\_amt\_sn  
   WHERE area\_code = rec.area\_code  
   AND CMCODE = rec.CMCODE  
   AND ym = is\_ym;  
 END LOOP;  
   
 COMMIT;  
 --错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。  
EXCEPTION  
   WHEN OTHERS THEN  
      vs\_msg := 'ERROR IN xxxxxxxxxxx\_p('||is\_ym||'):'||SUBSTR(SQLERRM,1,500);  
   ROLLBACK;  
   --把当前错误记录进日志表。  
   INSERT INTO LOG\_INFO(proc\_name,error\_info,op\_date)  
   VALUES('xxxxxxxxxxx\_p',vs\_msg,SYSDATE);  
   COMMIT;  
   RETURN;  
END;  
  
存储过程创建语法：

create or replace procedure 存储过程名（param1 in type，param2 out type）

变量1 类型（值范围）;

变量2 类型（值范围）;

Begin

Select count(\*) into 变量1 from 表A where列名=param1；

If (判断条件) then

Select 列名 into 变量2 from 表A where列名=param1；

Dbms\_output。Put\_line(‘打印信息’);

Elsif (判断条件) then

Dbms\_output。Put\_line(‘打印信息’);

Else

Raise 异常名（NO\_DATA\_FOUND）;

End if;

Exception

When others then

Rollback;

End;

注意事项：

1，  存储过程参数不带取值范围，in表示传入，out表示输出

2，  变量带取值范围，后面接分号

3，  在判断语句前最好先用count（\*）函数判断是否存在该条操作记录

4，  用select 。。。into。。。给变量赋值

5，  在代码中抛异常用 raise+异常名

以命名的异常

命名的系统异常                          产生原因

ACCESS\_INTO\_NULL                   未定义对象

CASE\_NOT\_FOUND                     CASE 中若未包含相应的 WHEN ，并且没有设置

ELSE 时

COLLECTION\_IS\_NULL                集合元素未初始化

CURSER\_ALREADY\_OPEN          游标已经打开

DUP\_VAL\_ON\_INDEX                   唯一索引对应的列上有重复的值

INVALID\_CURSOR                 在不合法的游标上进行操作

INVALID\_NUMBER                       内嵌的 SQL 语句不能将字符转换为数字

NO\_DATA\_FOUND                        使用 select into 未返回行，或应用索引表未初始化的

TOO\_MANY\_ROWS                      执行 select into 时，结果集超过一行

ZERO\_DIVIDE                              除数为 0

SUBSCRIPT\_BEYOND\_COUNT     元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT\_OUTSIDE\_LIMIT       使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE\_ERROR                             赋值时，变量长度不足以容纳实际数据

正确的用户名或密码

NOT\_LOGGED\_ON                       PL/SQL 应用程序在没有连接 oralce 数据库的情况下

访问数据

PROGRAM\_ERROR                       PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL

系统包

ROWTYPE\_MISMATCH                宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF\_IS\_NULL                             使用对象类型时，在 null 对象上调用对象方法

STORAGE\_ERROR                        运行 PL/SQL 时，超出内存空间

SYS\_INVALID\_ID                         无效的 ROWID 字符串

TIMEOUT\_ON\_RESOURCE         Oracle 在等待资源时超时  
\-----------------------------

1、什么是存储过程。存储过程是数据库服务器端的一段程序，它有两种类型。一种类似于SELECT查询，用于检索数据，检索到的数据能够以数据集的形式返回给客户。另一种类似于INSERT或DELETE查询，它不返回数据，只是执行一个动作。有的服务器允许同一个存储过程既可以返回数据又可以执行动作。  
2、什么时候需要用存储过程  
　　如果服务器定义了存储过程，应当根据需要决定是否要用存储过程。存储过程通常是一些经常要执行的任务，这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程，可以改善应用程序的性能。这是因为：  
.服务器往往具有强大的计算能力和速度。  
.避免把大量的数据下载到客户端，减少网络上的传输量。  
　　例如，假设一个应用程序需要计算一个数据，这个数据需要涉及到许多记录。如果不使用存储过程的话，把这些数据下载到客户端，导致网络上的流量剧增。  
　　不仅如此，客户端可能是一台老掉牙的计算机，它的运算速度很慢。而改用存储过程后，服务器会很快地把数据计算出来，并且只需传递一个数据给客户端，其效率之高是非常明显的。  
3、存储过程的参数  
　　要执行服务器上的存储过程，往往要传递一些参数。这些参数分为四种类型：  
　　第一种称为输入参数，由客户程序向存储过程传递值。  
　　第二种称为输出参数，由存储过程向客户程序返回结果。  
　　第三种称为输入/输出参数，既可以由客户程序向存储过程传递值，也可以由存储过程向客户程序返回结果。  
　　第四种称为状态参数，由存储过程向客户程序返回错误信息。  
　　要说明的是，并不是所有的服务器都支持上述四种类型的参数，例如，InterBase就不支持状态参数。  
4、oracle 存储过程的基本语法

1.基本结构  
CREATE OR REPLACE PROCEDURE 存储过程名字  
(  
    参数1 IN NUMBER,  
    参数2 IN NUMBER  
) IS  
变量1 INTEGER :=0;  
变量2 DATE;  
BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT  
  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条  
  记录，否则抛出异常(如果没有记录抛出NO\_DATA\_FOUND)  
  例子：  
  BEGIN  
  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;  
  EXCEPTION  
  WHEN NO\_DATA\_FOUND THEN  
      xxxx;  
  END;  
  ...

3.IF 判断  
  IF V\_TEST=1 THEN  
    BEGIN  
       do something  
    END;  
  END IF;

4.while 循环  
  WHILE V\_TEST=1 LOOP  
  BEGIN  
 XXXX  
  END;  
  END LOOP;

5.变量赋值  
  V\_TEST := 123;

6.用for in 使用cursor  
  ...  
  IS  
  CURSOR cur IS SELECT \* FROM xxx;  
  BEGIN  
 FOR cur\_result in cur LOOP  
  BEGIN  
   V\_SUM :=cur\_result.列名1+cur\_result.列名2  
  END;  
 END LOOP;  
  END;

7.带参数的cursor  
  CURSOR C\_USER(C\_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C\_ID;  
  OPEN C\_USER(变量值);  
  LOOP  
 FETCH C\_USER INTO V\_NAME;  
 EXIT FETCH C\_USER%NOTFOUND;  
    do something  
  END LOOP;  
  CLOSE C\_USER;

8.用pl/sql developer debug  
  连接数据库后建立一个Test WINDOW  
  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

资料引用:http://www.knowsky.com/344816.html

这段时间开始学习写存储过程，主要原因还是因为工作需要吧，本来以为很简单的，但几经挫折，豪气消磨殆尽，但总算搞通了，为了避免后来者少走弯路，特记述与此，同时亦对自己进行鼓励。

一：无返回值的存储过程

存储过程为：

CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2)  AS

BEGIN

INSERT INTO HYQ.B\_ID (I\_ID,I\_NAME) VALUES (PARA1, PARA2);

END TESTA;

一：无返回值的存储过程  
存储过程为：

CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS  
    BEGIN  
    INSERT INTO HYQ.B\_ID (I\_ID,I\_NAME) S (PARA1, PARA2);  
    END TESTA;

二：有返回值的存储过程（非列表）  
存储过程为：

CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS  
    BEGIN  
    SELECT INTO PARA2 FROM TESTTB WHERE I\_ID= PARA1;  
    END TESTB;

三：返回列表  
由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，  
1， 建一个程序包。如下：

CREATE OR REPLACE PACKAGE TESTPACKAGE AS  
    TYPE Test\_CURSOR IS REF CURSOR;  
    end TESTPACKAGE;

2，建立存储过程，存储过程为：

CREATE OR REPLACE PROCEDURE TESTC(p\_CURSOR out TESTPACKAGE.Test\_CURSOR) IS  
    BEGIN  
    OPEN p\_CURSOR FOR SELECT \* FROM HYQ.TESTTB;  
    END TESTC;

可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。  
过程返回记录集：  
CREATE OR REPLACE PACKAGE pkg\_test  
AS  
    TYPE myrctype IS REF CURSOR;  
   
    PROCEDURE get (p\_id NUMBER, p\_rc OUT myrctype);  
END pkg\_test;  
/  
   
CREATE OR REPLACE PACKAGE BODY pkg\_test  
AS  
    PROCEDURE get (p\_id NUMBER, p\_rc OUT myrctype)  
    IS  
       sqlstr   VARCHAR2 (500);  
    BEGIN  
       IF p\_id = 0 THEN  
          OPEN p\_rc FOR  
             SELECT ID, NAME, sex, address, postcode, birthday  
               FROM student;  
       ELSE  
          sqlstr :=  
             'select id,name,sex,address,postcode,birthday  
            from student where id=:w\_id';  
          OPEN p\_rc FOR sqlstr USING p\_id;  
       END IF;  
    END get;  
END pkg\_test;  
/  
   
\---函数返回记录集:  
建立带ref cursor定义的包和包体及函数：  
CREATE OR REPLACE  
package pkg\_test as  
/\* 定义ref cursor类型  
    不加return类型，为弱类型，允许动态sql查询，  
    否则为强类型，无法使用动态sql查询;  
\*/  
   type myrctype is ref cursor;   
    
\--函数申明  
   function get(intID number) return myrctype;  
end pkg\_test;  
/  
    
CREATE OR REPLACE  
package body pkg\_test as  
\--函数体  
    function get(intID number) return myrctype is  
      rc myrctype;  --定义ref cursor变量  
      sqlstr varchar2(500);  
    begin  
      if intID=0 then  
         --静态测试，直接用select语句直接返回结果  
         open rc for select id,name,sex,address,postcode,birthday from student;  
      else  
         --动态sql赋值，用:w\_id来申明该变量从外部获得  
         sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w\_id';  
         --动态测试，用sqlstr字符串返回结果，用using关键词传递参数  
         open rc for sqlstr using intid;  
      end if;  
    
      return rc;  
    end get;  
    
end pkg\_test;  
/

\--------------------------------

1、创建存储过程

create or replace procedure test(var\_name\_1 in type,var\_name\_2 out type) as

\--声明变量(变量名 变量类型)

begin

\--存储过程的执行体

end test;

打印出输入的时间信息

E.g:

create or replace procedure test(workDate in Date) is

begin

dbms\_output.putline(&apos;The input date is:&apos;||to\_date(workDate,&apos;yyyy-mm-dd&apos;));

end test;

2、变量赋值

变量名 := 值;

E.g：

create or replace procedure test(workDate in Date) is

x number(4,2);

begin

x := 1;

end test;

3、判断语句:

if 比较式 then begin end; end if;

E.g

create or replace procedure test(x in number) is

begin

if x >0 then

begin

x := 0 - x;

end;

end if;

if x = 0 then

begin

x: = 1;

end;

end if;

end test;

4、For 循环

For ... in ... LOOP

\--执行语句

end LOOP;

(1)循环遍历游标

create or replace procedure test() as

Cursor cursor is select name from student; name varchar(20);

begin

for name in cursor LOOP

begin

dbms\_output.putline(name);

end;

end LOOP;

end test;

(2)循环遍历数组

create or replace procedure test(varArray in myPackage.TestArray) as

\--(输入参数varArray 是自定义的数组类型，定义方式见标题6)

i number;

begin

i := 1;  --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张

\--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历

for i in 1..varArray.count LOOP

dbms\_output.putline(&apos;The No.&apos;|| i || &apos;record in varArray is:&apos;||varArray(i));

end LOOP;

end test;

5、While 循环

while 条件语句 LOOP

begin

end;

end LOOP;

E.g

create or replace procedure test(i in number) as

begin

while i < 10 LOOP

begin

i:= i + 1;

end;

end LOOP;

end test;

6、数组

首先明确一个概念：Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。

使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。

(1)使用Oracle自带的数组类型

x array; --使用时需要需要进行初始化

e.g:

create or replace procedure test(y out array) is

x array;

begin

x := new array();

y := x;

end test;

(2)自定义的数组类型 (自定义数据类型时，建议通过创建Package的方式实现，以便于管理)

E.g (自定义使用参见标题4.2) create or replace package myPackage is

-- Public type declarations type info is record(name varchar(20),     y number);

type TestArray is table of info index by binary\_integer;   --此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TestArray 就是一张表，有两个字段，一个是

name，一个是y。需要注意的是此处使用了Index by binary\_integer 编制该Table的索引项，也可以不写，直接写成：type TestArray is

table of info，如果不写的话使用数组时就需要进行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();

end TestArray;

7.游标的使用 Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：

(1)Cursor型游标(不能用于参数传递)

create or replace procedure test() is

cusor\_1 Cursor is select std\_name from student where  ...;  --Cursor的使用方式1   cursor\_2 Cursor;

begin

select class\_name into cursor\_2 from class where ...;  --Cursor的使用方式2

可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor的遍历

end test;

(2)SYS\_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递

create or replace procedure test(rsCursor out SYS\_REFCURSOR) is

cursor SYS\_REFCURSOR; name varhcar(20);

begin

OPEN cursor FOR select name from student where ... --SYS\_REFCURSOR只能通过OPEN方法来打开和赋值

LOOP

fetch cursor into name   --SYS\_REFCURSOR只能通过fetch into来打开和遍历 exit when cursor%NOTFOUND;              --SYS\_REFCURSOR中可使用三个状态属性：                                         ---%NOTFOUND(未找到记录信息) %FOUND(找到记录信息)                                         ---%ROWCOUNT(然后当前游标所指向的行位置)

dbms\_output.putline(name);

end LOOP;

rsCursor := cursor;

end test;

下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：

现假设存在两张表，一张是学生成绩表(studnet)，字段为：stdId,math,article,language,music,sport,total,average,step                   一张是学生课外成绩表(out\_school),字段为:stdId,parctice,comment

通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A，就在总成绩上加20分。

create or replace procedure autocomputer(step in number) is

rsCursor SYS\_REFCURSOR;

commentArray myPackage.myArray;

math number;

article number;

language number;

music number;

sport number;

total number;

average number;

stdId varchar(30);

record myPackage.stdInfo;

i number;

begin

i := 1;

get\_comment(commentArray); --调用名为get\_comment()的存储过程获取学生课外评分信息

OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;

LOOP

fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;

total := math + article + language + music + sport;

for i in 1..commentArray.count LOOP

record := commentArray(i);

if stdId = record.stdId then

begin

if record.comment = &apos;A&apos; then

begin

total := total + 20;

go to next; --使用go to跳出for循环

end;

end if;

end;

end if;

end LOOP;

<<continue>>  average := total / 5;

update student t set t.total=total and t.average = average where t.stdId = stdId;

end LOOP;

end;

end autocomputer;

\--取得学生评论信息的存储过程

create or replace procedure get\_comment(commentArray out myPackage.myArray) is

rs SYS\_REFCURSOR；

record myPackage.stdInfo;

stdId varchar(30);

comment varchar(1);

i number;

begin

open rs for select stdId,comment from out\_school

i := 1;

LOOP

fetch rs into stdId,comment; exit when rs%NOTFOUND;

record.stdId := stdId;

record.comment := comment;

recommentArray(i) := record;

i:=i + 1;

end LOOP;

end get\_comment;

\--定义数组类型myArray

create or replace package myPackage is begin

type stdInfo is record(stdId varchar(30),comment varchar(1));

type myArray is table of stdInfo index by binary\_integer;

end myPackage;

学习完以后，就想把自己前面写的一个select语句装在一个存储过程中，把查询的记录集用游标返回，试了多次都不成功，花了九牛二虎之力才弄明白，输出的游标需要引用类型，需要自己定义一个引用类型的游标。

下面是我创建了一个包，在包中完成了游标和存储过程的定义。

包头：  
create or replace package aaatestp is  
  type mycur is ref cursor;  
  procedure aaatest(rq in PC\_WELL\_PRO\_MONTHLY.Year\_Mon%type, org in varchar2, outcur out mycur);  
end aaatestp;  
包体：  
create or replace package body aaatestp is  
  procedure aaatest(rq in PC\_WELL\_PRO\_MONTHLY.Year\_Mon%type, org in varchar2, outcur out mycur) as  
  begin  
    open outcur for  
      SELECT L.Project\_Name       k1,  
             D.WELL\_COMMON\_NAME   k2,  
             H.Md\_Top             k3,  
             H.Md\_Base            k4,  
             H.THICKNESS          k5,  
             e.Prod\_Days          K6,  
             B.OIL\_PROD\_METHOD    K7,  
             A.STROKE\_LENGTH      K8,  
             A.STROKE\_FREQUENCY   K9,  
             A.PUMP\_DIAMETER      K10,  
             A.PUMP\_DEPTH         K11,  
             B.CASING\_PRES        K14,  
             F.bh\_flow\_pressure   K15,  
             B.GAS\_PROD\_MON       K16,  
             B.GAS\_PROD\_YEAR      K17,  
             B.GAS\_PROD\_CUM       K18,  
             B.WATER\_PROD\_VOL\_MON K19,  
             B.WATER\_PROD\_YEAR    K20,  
             B.WATER\_PROD\_CUM     K21,  
             B.GAS\_RELEASE\_MON    K22,  
             B.GAS\_RELEASE\_YEAR   K23,  
             B.GAS\_RELEASE\_CUM    K24,  
             B.REMARKS            K25  
        FROM PC\_WELL\_PRO\_MECH\_MONTHLY A,  
             PC\_WELL\_PRO\_MONTHLY B,  
             PC\_WELL\_PRO\_MONTHLY E,  
             CD\_WELL\_SOURCE D,  
             PC\_LOG\_DESC\_PROCEDURE H,  
             (select distinct b.project\_id, b.project\_name, d.site\_id  
                from cd\_project\_source B, CD\_SITE\_SOURCE c, cd\_well\_source D  
               where d.org\_id in  
                     (SELECT ORG\_ID  
                        FROM PC\_ORGANIZATION  
                       START WITH ORG\_ID IN org  
                      CONNECT BY PRIOR ORG\_ID = PARENT\_ID)  
                 and b.project\_id = c.project\_id  
                 and c.site\_id = d.site\_id) L,  
             (select cd.bh\_flow\_pressure, cs.well\_id  
                from cd\_pressure\_aof\_flow cd,  
                     cd\_pressure\_aof      af,  
                     CD\_PRESSURE\_SURVEY   cs  
               where cs.pressure\_survey\_id = af.pressure\_survey\_id  
                 and cd.pressure\_survey\_id = af.pressure\_survey\_id  
                 and cs.well\_id in  
                     (select well\_id  
                        from cd\_well\_source  
                       where org\_id in  
                             (select org\_id  
                                from pc\_organization  
                               start with org\_id in org  
                              connect by prior org\_id = parent\_id))) F  
       where B.Year\_Mon = rq  
         and a.year\_mon = B.Year\_Mon  
         and e.year\_mon = B.year\_mon  
         and a.well\_id = D.Well\_Id  
         and B.Well\_Id = D.Well\_Id  
         and E.Well\_Id = D.well\_id  
         and H.Well\_Id = D.well\_id  
         and D.Site\_Id = l.site\_id  
         and a.well\_id = f.well\_id;  
  end aaatest;  
end aaatestp;

编译通过，测试也看到了结果，游标参数里面是一张表。

这个过程能不能不放在包里面呢，应该可以吧，我就做了下面一个实验

\--输出记录集的游标必须是引用类型  
create or replace procedure aaatest1(rq     in PC\_WELL\_PRO\_MONTHLY.Year\_Mon%type,  
                                     org    in varchar2,  
                                     outcur out aaatestp.mycur) as  
  cursor tem\_sursor is select t.org\_name, t.org\_id  
      from pc\_organization\_t t  
     where t.org\_level = 10;                                    
  out\_row tem\_sursor%rowtype;  
begin  
\--为输出的游标赋值  
  open outcur for  
    SELECT L.Project\_Name       k1,  
           D.WELL\_COMMON\_NAME   k2,  
           H.Md\_Top             k3,  
           H.Md\_Base            k4,  
           H.THICKNESS          k5,  
           e.Prod\_Days          K6,  
           B.OIL\_PROD\_METHOD    K7,  
           A.STROKE\_LENGTH      K8,  
           A.STROKE\_FREQUENCY   K9,  
           A.PUMP\_DIAMETER      K10,  
           A.PUMP\_DEPTH         K11,  
           B.CASING\_PRES        K14,  
           B.GAS\_PROD\_MON       K16,  
           B.GAS\_PROD\_YEAR      K17,  
           B.GAS\_PROD\_CUM       K18,  
           B.WATER\_PROD\_VOL\_MON K19,  
           B.WATER\_PROD\_YEAR    K20,  
           B.WATER\_PROD\_CUM     K21,  
           B.GAS\_RELEASE\_MON    K22,  
           B.GAS\_RELEASE\_YEAR   K23,  
           B.GAS\_RELEASE\_CUM    K24,  
           B.REMARKS            K25  
      FROM PC\_WELL\_PRO\_MECH\_MONTHLY A,  
           PC\_WELL\_PRO\_MONTHLY B,  
           PC\_WELL\_PRO\_MONTHLY E,  
           CD\_WELL\_SOURCE D,  
           PC\_LOG\_DESC\_PROCEDURE H,  
           (select distinct b.project\_id, b.project\_name, d.site\_id  
              from cd\_project\_source B, CD\_SITE\_SOURCE c, cd\_well\_source D  
             where d.org\_id in  
                   (SELECT ORG\_ID  
                      FROM PC\_ORGANIZATION  
                     START WITH ORG\_ID IN org --'HBwrtyQASD'--to\_char(org)  
                    CONNECT BY PRIOR ORG\_ID = PARENT\_ID)  
               and b.project\_id = c.project\_id  
               and c.site\_id = d.site\_id) L  
     where B.Year\_Mon = rq  
       and a.year\_mon = B.Year\_Mon  
       and e.year\_mon = B.year\_mon  
       and a.well\_id = D.Well\_Id  
       and B.Well\_Id = D.Well\_Id  
       and E.Well\_Id = D.well\_id  
       and H.Well\_Id = D.well\_id  
       and D.Site\_Id = l.site\_id;  
       --跳转标志，<<>>  
       <<ReadSur>>  
  --练习游标的isopen和found属性，isopen判断游标是否打开，found判断是否为空  
  if tem\_sursor%isopen then  
       fetch tem\_sursor into out\_row;  
        if tem\_sursor%found then  
       dbms\_output.put\_line(out\_row.org\_name);  
    else  
      dbms\_output.put\_line('游标为空');  
    end if;  
  else  
    open tem\_sursor;  
    goto ReadSur;  
  end if;  
close outcur;  
end aaatest1;  
过程从包中移了出来，但依然用了包中定义好的游标，其他基本没变，测试了一下，一样可以将整个记录集用游标返回

\----------------------------

\----------Oracle存储过程  
先来一个简单的例子,有个感性认识.  
create or replace procedure test is  
aa number;  
begin     
select count(\*) into aa from t\_server\_server;  
DBMS\_OUTPUT.PUT\_LINE(aa);  
insert into test\_test values(1,'aaa');  
commit;  
end test;  
主要是查询,打印,插入.  
以下是转载  
1.基本结构  
CREATE OR REPLACE PROCEDURE 存储过程名字  
(  
    参数1 IN NUMBER,  
    参数2 IN NUMBER  
) IS  
变量1 INTEGER :=0;  
变量2 DATE;  
BEGIN  
END 存储过程名字

3.IF 判断  
  IF V\_TEST=1 THEN  
    BEGIN  
       do something  
    END;  
  END IF;

4.while 循环  
  WHILE V\_TEST=1 LOOP  
  BEGIN  
 XXXX  
  END;  
  END LOOP;

5.变量赋值  
  V\_TEST := 123;

8.用pl/sql developer debug  
  连接数据库后建立一个Test WINDOW  
  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

关于oracle存储过程的若干问题备忘  
1.在oracle中，数据表别名不能加as，如：

select a.appname from appinfo a;-- 正确  
select a.appname from appinfo as a;-- 错误

也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正确编译  
  select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：Compilation  
  Error: PLS-00428: an INTO clause is expected in this SELECT statement

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。

可以在该语法之前，先利用select count(\*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行  
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错，提示  
ORA-01422:exact fetch returns more than requested number of rows  
5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：

create table A(  
id varchar2(50) primary key not null,  
vcount number(8) not null,  
bid varchar2(50) not null -- 外键  
);  
如果在存储过程中，使用如下语句：

select sum(vcount) into fcount from A where bid='xxxxxx';  
如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：

if fcount is null then  
    fcount:=0;  
end if;  
这样就一切ok了。

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。  
Oracle存储过程可以有无参数存储过程和带参数存储过程。  
一、无参程序过程语法  
1 create or replace procedure NoParPro  
2 as  ;  
3 begin  
4 ;  
5 exception     //存储过程异常  
6     ;  
7 end;  
8   
        二、带参存储过程实例  
create or replace procedure queryempname(sfindno emp.empno%type) as  
 2        sName emp.ename%type;  
 3        sjob emp.job%type;  
 4 begin  
 5        ....  
 7 exception  
          ....  
14 end;  
15   
    三、 带参数存储过程含赋值方式  
create or replace procedure runbyparmeters  (isal in emp.sal%type,  
                            sname out varchar,sjob in out varchar)  
 2  as icount number;  
 3  begin  
 4       select count(\*) into icount from emp where sal>isal and job=sjob;  
 5       if icount=1 then  
 6         ....  
 9       else  
10         ....  
12       end if;  
13  exception  
14       when too\_many\_rows then  
15       DBMS\_OUTPUT.PUT\_LINE('返回值多于1行');  
16       when others then  
17       DBMS\_OUTPUT.PUT\_LINE('在RUNBYPARMETERS过程中出错！');  
18  end;  
19   
  四、在Oracle中对存储过程的调用  
  过程调用方式一  
declare  
 2        realsal emp.sal%type;  
 3        realname varchar(40);  
 4        realjob varchar(40);  
 5  begin   //存储过程调用开始  
 6        realsal:=1100;  
 7        realname:='';  
 8        realjob:='CLERK';  
 9        runbyparmeters(realsal,realname,realjob);     －－必须按顺序  
10        DBMS\_OUTPUT.PUT\_LINE(REALNAME||'   '||REALJOB);  
11  END;  //过程调用结束  
12   
  过程调用方式二  
declare  
 2       realsal emp.sal%type;  
 3       realname varchar(40);  
 4       realjob varchar(40);  
 5 begin    //过程调用开始  
 6       realsal:=1100;  
 7       realname:='';  
 8       realjob:='CLERK';  
 9       runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);  －－指定值对应变量顺序可变  
10       DBMS\_OUTPUT.PUT\_LINE(REALNAME||'   '||REALJOB);  
11 END;  //过程调用结束

[www.bitscn.com]: http://www.bitscn.com
[www.bitsCN.com]: http://www.bitsCN.com