oracle往游标中存数据,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_curisselectnamefromtableA;

nvarchar2(10);

begin

open v_cur;

loop

exitwhen v_cur%notfound;

fetch v_curinto n;

dbms_output.put_line(n);

close v_cur;

endloop;

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_curisselectnamefromtableA where name = 'c';

nvarchar2(10);

begin

open v_cur;

loop

exitwhen v_cur%notfound;

n:='hehe'

fetch v_curinto n;

dbms_output.put_line(n);

close v_cur;

endloop;

end;

执行代码的结果：

hehe

疑问：游标是空游标，也就是说游标在打开的时候就没有指向任何的值。但为什么exitwhen v_cur%notfound;这条语句还通过了呢??

oracle文档的解释：

Before the first fetch,%NOTFOUNDreturnsNULL. IfFETCHnever executes successfully, the loop is never exited, because theEXITWHENstatement executes only if itsWHENcondition is true. To be safe, you might want to use the followingEXITstatement instead:

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

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