oracle 存储过程游标的使用

今天接到同事的一个任务，让我把开发用户（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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29654823/viewspace-1790157/