开发者社区> 技术小胖子> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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


-- 说明

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,如需转载请自行联系原作者




版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
oracle学习1-oracle安装
oracle学习1-oracle安装
16 0
Oracle学习之clent安装(续)
client安装后,还有很多工具是不能用的。   例如:tnsping,sqlldr,exp/imp   如何能让client端,和server端有强大的工具。
526 0
Oracle学习系列之安装Oracle(续)
 oracle安装详细步骤(第二阶段).zip   这个是执行了./runInstaller之后。要做的操作。 具体操作如下:  第一步:安装Oracle软件 第二步:安装Oracle实例 第三步:安装Oracle监听器 第四步:创建测试表空间...
716 0
Oracle 数据库性能优化3日实战(企业培训)
课程名称一: Oracle性能优化及调整 课程时长 1天 课程深度: 高级 上机实验: 10%-30% 授课对象: Oracle开发人员、Oracle数据库管理人员,应用程序开发人员 课程描述: 本课程讲述Oracle数据库物理层规划,系统性能的监控,数据库性能参数调整,统计信息的收集,使用自动化调试工具优化数据库,I/O子系统的配置与设计以及性能优化方法论等。
1824 0
Oracle 10g Express Edition(XE) 转至数据库主页中文乱码问题的解决
Oracle 10g Express Edition是Oracle专门为小型用户提供的免费版本。
886 0
21114
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载