ITPub博客

首页 > 数据库 > Oracle > oracle 存储过程游标的使用

oracle 存储过程游标的使用

原创 Oracle 作者:贺子_DBA时代 时间:2015-09-03 15:48:17 0 删除 编辑
今天接到同事的一个任务,让我把开发用户(pd_zh_cn)下的某个表的数据筛选一部分,倒进测试用户(pdtest_zh_cn)下的相应表中, 筛选条件有点恶心,
第一步先从产品分类表里面选出所有2级分类对应的id号。共有172个
SQL> select id  from  pd_zh_cn.TD_CLASSIFY    where   CLASSIFY_LEVEL=2;

        ID
----------
       101
       102
       103
       104
       105
       106
       107
       108
       109
       201
       202

        ID
----------
       203
       204
       205
       206
       301
       302
       303
       304
       305
       306
       307

第二步:每个二级分类对应这好多产品,(例如 水果是一级分类,然后这个一级分类下,有个二级分类是瓜果类,)然后再在产品表中找到各自的二级分类下的产品,我们只要选出200个倒进测试库 ,即可。
这个目的完全可以用sql语句来完成,借助union  all  来完成,也就是先选出各自的id下的200个产品,然后172个union  all  ,傻子才这样做呢,
于是我想用存储过程来完成,借助游标来完成,
存储过程语句:
create or replace procedure insert_shaxiang_150902   is
begin
declare  CURSOR   c_number  is
select  id  from  pd_zh_cn.TD_CLASSIFY    where   CLASSIFY_LEVEL=2;
my_re  c_number%rowtype;
begin    
open   c_number;
loop
exit  when c_number%notfound;
fetch c_number  into  my_re;
insert  into   liuwenhe.TB_CHANNEL_GOODS     select * from   pd_zh_cn.TB_CHANNEL_GOODS b    where  b.id= my_re.id and rownum<=200;
end loop;
close c_number;
commit;
end;
end  insert_shaxiang_150902 ;
注释create or replace procedure insert_shaxiang_150902   is
begin
declare  CURSOR   c_number  is           
select  id  from  pd_zh_cn.TD_CLASSIFY    where   CLASSIFY_LEVEL=2;            ###定义游标
my_re     c_number%rowtype;             ###这个就是定义一个记录,这个记录和前边的游标c_number类型一样,是一行数据,我这里定义的游标只有一个列id,如果有两个的话,那这个记录my_re也就是两个列,所以后面调用该记录的时候,要这样:my_re.id
begin    
open   c_number;
loop     ##开始循环
exit  when c_number%notfound;
fetch c_number  into  my_re;    ##将游标的值赋值给定义的记录my_re
insert  into   liuwenhe.TB_CHANNEL_GOODS     select * from   pd_zh_cn.TB_CHANNEL_GOODS b    where  b.id= my_re.id and rownum<=200;
end loop;  
close c_number;
commit;
end;
end  insert_shaxiang_150902 ;

小结:1,关于 c_number%rowtype和 c_number%type的区别:
 c_number%rowtype:定义的是一行记录,表示该类型为行数据类型,存储的是一行数据,一行数据里可以有多列,类似于表里的一行数据,也可以是游标里的一行数据,如:  vs_row1  表%rowtype; 
                                          vs_row2  游标%rowtype;
c_number%type:定义个是一个字段的记录,表示该类型为字段类型。存储的是一个字段,也可以取表或者游标的字段类型。
2,c_number%notfound   
下面引自网络:

错误的例子:
tableA
id  name
1   a
2   b

declare
cursor v_cur is select name from tableA;

n varchar2(10);
begin
open v_cur;
loop
exit when v_cur%notfound;
fetch v_cur into n;

dbms_output.put_line(n);

close v_cur;
end loop;

end;


执行上面的语句,结果为:
a
b
b
发现最后一条记录被打印了两次。原因是%notfound是判断最后一次fetch的结果,把bfetch到变量n中之后再执行exit when %notfound判断得到的是false的记过,也就是说是有返回行的,所以判断通过,再此执行了打印语句。
发现了另一个疑问:
把a,b都fetch之后按理说游标已经空了,那么第三次应该是fetch的空值,为什么打印出来的还是b呢??
因为fetch..into语句末尾不会修改into变量后面的值。就像select..into如果没有数据会报异常,但是不会把into后面的变量置为空
再写一段代码

declare
cursor v_cur is select name from tableA where name = 'c';

n varchar2(10);
begin
open v_cur;
loop
exit when v_cur%notfound;

n:='hehe'
fetch v_cur into n;

dbms_output.put_line(n);

close v_cur;
end loop;

end;
执行代码的结果:
hehe
疑问:游标是空游标,也就是说游标在打开的时候就没有指向任何的值。但为什么
exit when v_cur%notfound;这条语句还通过了呢??
oracle文档的解释:

Before the first fetch, %NOTFOUND returns NULL. If FETCH never executes successfully, the loop is never exited, because the EXIT WHEN statement executes only if its WHEN condition is true. To be safe, you might want to use the following EXIT statement instead:

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

也就是说v_cur%notfound有三种状态,true,false,null。所以以后为了安全期间可以加上是否为空的判断







来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-1790157/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
记录工作中遇到的问题,积少成多,坚持就是胜利,工作经历:曾就职于国美、中国采购于招标网、目前就职于一家正规消费金融公司负责Oracle和MySQL以及hadoop相关运维和优化的工作

注册时间:2014-05-12

  • 博文量
    231
  • 访问量
    1633427