今天接到同事的一個任務，讓我把開發使用者（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。所以以後為了安全期間可以加上是否為空的判斷

oracle 儲存過程遊標的使用

相關文章