今天接到同事的一个任务,让我把开发用户(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。所以以后为了安全期间可以加上是否为空的判断