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