oracle存储过程的简单学习2

1.选用何种游标?

显示游标分为：普通游标，参数化游标和游标变量三种。

create or replace procedure proc(p varchar2)

as

v_rownum number(10) := 1;

cursor c1 is select ename from emp where rownum = 1;

cursor c2 is select ename from emp where rownum = v_rownum;

cursor c3(p_rownum number) is select ename from emp where rownum = p_rownum;

type c_c is ref cursor;

c4 c_c;

v1 varchar2(20);

begin

 open c1;

 fetch c1 into v1;

 dbms_output.put_line('1.' || v1);

 close c1;

 open c2;

 fetch c2 into v1;

 dbms_output.put_line('2.' || v1);

 close c2;

 open c3(1);

 fetch c3 into v1;

 dbms_output.put_line('3.' || v1);

 close c3;

 open c4 for select ename from emp where rownum = 1;

 fetch c4 into v1;

 dbms_output.put_line('4.' || v1);

 close c4;

end;  

-- 调用

call   proc(1);

-- 说明

这一句是定义了一个最普通的游标，把整个查询已经写死,调用时不可以作任何改变。

这一句并没有写死，查询参数由变量v_rownum来决定。需要注意的是v_rownum必须在这个游标定义之前声明。

这一条语句与第二条作用相似，都是可以为游标实现动态的查询。但是它进一步的缩小了参数的作用域范围。但是可读性降低了不少。

先定义了一个引用游标类型，然后再声明了一个游标变量。

open c4 for select ename from emp where rownum = 1;

然后再用open for 来打开一个查询。需要注意的是它可以多次使用,用来打开不同的查询。

从动态性来说，游标变量是最好用的，但是阅读性也是最差的。

注意，游标的定义只能用使关键字IS，它与AS不通用。

2.游标的循环策略

create or replace procedure proc1

cursor c1 is select ename,sal from emp ;

v2 number(4);

 if c1%found = true then

   dbms_output.put_line('found true ...');

 elsif c1%found = false then

   dbms_output.put_line('found false ...');

 else

   dbms_output.put_line('found null ...');

 end if;

 --1.loop循环

 loop

   fetch c1 into v1,v2;

   exit when c1%notfound;

   dbms_output.put_line('ename: ' || v1 || ',val:' || v2);

 end loop;

 dbms_output.put_line('--- loop end ...');

/*exit when语句一定要紧跟在fetch之后。必避免多余的数据处理。

处理逻辑需要跟在exit when之后。这一点需要多加小心。

循环结束后要记得关闭游标*/

 --2.while循环

fetch c1 into v1,v2;

 while c1%found loop

     dbms_output.put_line('ename: ' || v1 || ',val:' || v2);

     fetch c1 into v1,v2;

 close c1;  

 dbms_output.put_line('---while end---');  

 /*

我们知道了一个游标打开后，必须执行一次fetch语句，游标的属性才会起作用。

所以使用while 循环时，就需要在循环之前进行一次fetch动作。

而且数据处理动作必须放在循环体内的fetch方法之前。

循环体内的fetch方法要放在最后。否则就会多处理一次。这一点也要非常的小心。

总之，使用while来循环处理游标是最复杂的方法。

 */

  --3.for循环

  for v in c1 loop

    v1 := v.ename;

    v2 := v.sal;

    dbms_output.put_line('ename: ' || v1 || ',val:' || v2);

  end loop;

  dbms_output.put_line('---for end---');  

/*

可见for循环是比较简单实用的方法。

首先，它会自动open和close游标。解决了你忘记打开或关闭游标的烦恼。

其它,自动定义了一个记录类型及声明该类型的变量，并自动fetch数据到这个变量中。

我们需要注意v 这个变量无需要在循环外进行声明，无需要为其指定数据类型。

它应该是一个记录类型，具体的结构是由游标决定的。

这个变量的作用域仅仅是在循环体内。

把v看作一个记录变量就可以了，如果要获得某一个值就像调用记录一样就可以了。

如v.ename

由此可见，for循环是用来循环游标的最好方法。高效，简洁，安全。

但遗憾的是，常常见到的却是第一种方法。所以从今之后得改变这个习惯了

*/

end;

--调用

call proc1();

说明：

在打开一个游标之后，马上检查它的%found或%notfound属性，

它得到的结果即不是true也不是false.而是null.

必须执行一条fetch语句后，这些属性才有值。

3.select into不可忽视的问题

<1.>

我们知道在pl/sql中要想从数据表中向变量赋值，需要使用select into 子句。

但是它会带动来一些问题，如果查询没有记录时，会抛出no_data_found异常。

如果有多条记录时，会抛出too_many_rows异常。

这个是比较糟糕的。一旦抛出了异常，就会让过程中断。

特别是no_data_found这种异常，没有严重到要让程序中断的地步，可以完全交给由程序进行处理。  

eg1:

create or replace procedure proc2

AS

     v varchar2(20);

 dbms_output.put_line('---开始：================');

select ename into v from emp where 1 = 0;

 dbms_output.put_line('---' || v);

exception

 when no_data_found then

     dbms_output.put_line('no data found...。。。');

call proc2();

<2.>

说明：加exception → 这样做换汤不换药，程序仍然被中断。

     可能这样不是我们所想要的。

select into做为一个独立的块，在这个块中进行异常处理 。

  这是一种比较好的处理方式了。不会因为这个异常而引起程序中断。

  如下面的例子：

eg2:

create or replace procedure proc3

   dbms_output.put_line('---begin...========');

   dbms_output.put_line('---' || v);

   when no_data_found then

     dbms_output.put_line('no data found...give new value...');

     v := '';

end;    

  dbms_output.put_line('v :' || v);

-- 调用：

call proc3();

<3.>使用游标：这样就完全的避免了no_data_found异常。完全交由程序员来进行控制了。

eg3:

create or replace procedure proc4

      v varchar2(20);

cursor c is select ename from emp where 1=0;

 open c;

fetch c into v;

   dbms_output.put_line('v :' || v);

 close c;

   dbms_output.put_line('end...v :' || v);

--

call proc4();    

4.  too_many_rows 异常的问题。  

Too_many_rows 这个问题比起no_data_found要复杂一些。

给一个变量赋值时，但是查询结果有多个记录。

处理这种问题也有两种情况：

<1>． 多条数据是可以接受的，也就是说从结果集中随便取一个值就行。

这种情况应该很极端了吧，如果出现这种情况，也说明了程序的严谨性存在问题。

<2>． 多条数据是不可以被接受的，在这种情况肯定是程序的逻辑出了问题，也说是说原来根本就不会想到它会产生多条记录。

对于第一种情况，就必须采用游标来处理，而对于第二种情况就必须使用内部块来处理，重新抛出异常。

多条数据可以接受,随便取一条，这个跟no_data_found的处理方式一样，使用游标。

我这里仅说第二种情况，不可接受多条数据，但是不要忘了处理no_data_found哦。

这就不能使用游标了，必须使用内部块。

需要注意的是一定要加上对no_data_found的处理，对出现多条记录的情况则继续抛出异常，让上一层来处理。

总之对于select into的语句需要注意这两种情况了。需要妥当处理啊。

eg4:

create or replace procedure proc5

select ename into v from emp where rownum < 5;

     when no_data_found then

       v := null;

when too_many_rows then  

      raise_application_error('-20000','对v赋值时，找到多行记录！');

   end;

   dbms_output.put_line(v);

call proc5();    

5.在存储过程中返回结果集

------------------demo---------------------

create or replace procedure proc6(v1 varchar2,v2 number)

      total number(4) := 0;

      cursor c is select * from empa ;-- where 1=0;

 if v1is not null  and v2 != 0 then

   dbms_output.put_line('ok' || v1 || v2);    

 elsif v1 is null then

     dbms_output.put_line('v1 is null');  

-- elsif v1 = '' then

 --    dbms_output.put_line('v1 is kong...');  

 elsif v2 = 0 then

     dbms_output.put_line('v2 is 0');      

for varObj in c loop

total := c%Rowcount ;

    dbms_output.put_line(c%Rowcount || 'empno :' || varObj.empno || 'ename: ' || varObj.ename || 'sal:' || varObj.sal);

  dbms_output.put_line('total:' || total);

  if total <= 0 then

     dbms_output.put_line('total:' || total);

 raise_application_error('ORA-00973','rownumber is zero!');

  end if;

call proc6(null,1);

call proc6('',1);-- ''会被认为是null

call proc6('tom',0);

==========================2013-12-17-说明===================

<a href="http://www.jb51.net/article/34230.htm">http://www.jb51.net/article/34230.htm</a>

     本文转自韩立伟 51CTO博客，原文链接：http://blog.51cto.com/hanchaohan/1340791，如需转载请自行联系原作者