Proc中使用char陣列、VARCHAR陣列和char變數進行DELETE操作具體行數的細微區別

EXEC SQL BEGIN DECLARE SECTION;
char a[10000][3];
VARCHAR b[10000][31];
char c[3];
EXEC SQL END DECLARE SECTION;

...

int delete_rows=10000;

...

1. CHAR型別陣列變數

EXEC SQL for :delete_rows

delete FROM table_name

WHERE a= :a;

由於char對應於Oracle的char型別，因此若有空格，則此時char即使用memset初始化，但也會帶有後面的空格，有可能造成delete時where a=:a由於空格不匹配無法刪除，例如：a賦值為'a’，但陣列長度是3，因此實際where條件是a='a '，因為空格導致不能刪除。

2. VARCHAR型別陣列變數

EXEC SQL for :delete_rows

delete FROM table_name

WHERE b= :b;

對於VARCHAR型別對應於Oracle的VARCHAR型別，因此不存在1中的空格問題，會自動濾掉空格，這是最好的一種匹配方法。

對於刪除的資料量，會選擇delete_rows與b陣列的容量中最小的一個值。

3. CHAR型別變數

這裡指的是char字串變數，不是陣列，此時使用：

EXEC SQL for :delete_rows

delete FROM table_name

WHERE c= :c;

由於c只是一個變數字串，此時delete_rows會失效，只會執行一次該語句，有多少條刪除多少條記錄。

總結：

1. 對於VARCHAR型別陣列，Oracle會根據指定刪除行數的整數，與array host陣列變數的容量之間，選擇一個最小值，保證最小刪除的行。

2. 對於CHAR型別字串，就相當於一個常量，此時Oracle由於不能判斷delete ... where c=:c;實際需要刪除多少行，所以乾脆也不判斷了，就執行一次。開始我認為for :delete_rows類似於使用where rownum <= delete_rows，但這個場景是如此判斷，想必不會是rownum這種方式做。

同理，UPDATE與DELETE相同。

另外，值得提一句，EXEC SQL BEGIN DECLARE SECTION;中char和VARCHAR型別可以不是二維陣列，但其它型別的變數必須不能是這種二維陣列。

參考：

The host variables in the WHERE clause must be either all scalars or all arrays. If they are scalars, Oracle executes the DELETE statement only once. If they are arrays, Oracle executes the statement once for each set of array components. Each execution may delete zero, one, or multiple rows.
Array host variables in the WHERE clause can have different sizes. In this case, the number of times Oracle executes the statement is determined by the smaller of the following values:
■The size of the smallest array
■The value of the :host_integer in the optional FOR clause

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7192724/viewspace-774362/，如需轉載，請註明出處，否則將追究法律責任。