oracle存储过程的简单学习2

技术小胖子 2017-11-08

Oracle open fetch Create 存储过程


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);


-- 说明

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

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


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

这一句并没有写死,查询参数由变量v_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;

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

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

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

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

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



2.游标的循环策略

create or replace procedure proc1

as

cursor c1 is select ename,sal from emp ;

v1 varchar2(20);

v2 number(4);

begin

 open c1;

 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 ...');

 close c1;


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

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

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



 --2.while循环

 open c1;

fetch c1 into v1,v2;

 while c1%found loop

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

     fetch c1 into v1,v2;

 end loop;

 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);

begin

 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...。。。');

end;



--调用

call proc2();



<2.>

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

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

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

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

  如下面的例子:

eg2:

create or replace procedure proc3

as

     v varchar2(20);

begin

begin

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

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...give new value...');

     v := '';

end;    

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

end;


-- 调用:

call proc3();



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

eg3:

create or replace procedure proc4

as

      v varchar2(20);

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

begin

 open c;

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

fetch c into v;

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

 close c;

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

end;


--

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

as

      v varchar2(20);

begin

begin

select ename into v from emp where rownum < 5;

exception

     when no_data_found then

       v := null;

when too_many_rows then  

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

   end;

   dbms_output.put_line(v);

end;  


--

call proc5();    




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

见参考文章吧:→ 【http://www.cnblogs.com/chinafine/archive/2010/07/12/1776102.html】


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

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

as

      total number(4) := 0;

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


begin

 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');      

 end if;


for varObj in c loop

total := c%Rowcount ;

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

  end loop;


  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;

end;



--

call proc6(null,1);

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

call proc6('tom',0);



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

1.texit用来跳出循环  2.return跳出存储过程.参考地址:http://sunrise-king.iteye.com/blog/391374

http://www.jb51.net/article/34230.htm




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




登录 后评论
下一篇
云栖号资讯小编
34952人浏览
2020-07-13
相关推荐
学习Oracle的三重境界
2301人浏览
2017-07-18 11:18:21
【ASM学习】ASM文档
688人浏览
2010-08-11 23:37:05
学习笔记800天总结
1368人浏览
2016-05-15 22:33:18
goldengate学习-安装篇
646人浏览
2014-05-11 20:33:25
强烈推荐Oracle的入门心得
699人浏览
2017-09-05 10:57:00
Oracle学习经验谈
450人浏览
2013-07-07 14:18:24
Oracle学习的经验谈
601人浏览
2011-09-06 21:55:34
0
0
0
850