存储过程案例

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

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）

as

变量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                             赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED                           PL/SQL 应用程序连接到 oracle 数据库时，提供了不

正确的用户名或密码

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 存储过程名字

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存储过程的若干问题备忘
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;  //过程调用结束