sqlplus之防御式編程

采用防御式編程技術(shù)來構(gòu)建真正可移植的數(shù)據(jù)庫(kù)應(yīng)用。

場(chǎng)景：為每一行生成一個(gè)唯一的主鍵，

scott@ORCL>create?table?id_table
??2??(id_name?varchar2(30)?primary?key,
??3??id_value?number);

表已創(chuàng)建。

scott@ORCL>insert?into?id_table?values('MY_KEY',0);
已創(chuàng)建?1?行。

scott@ORCL>commit;
提交完成。

?為了得到一個(gè)新的鍵，必須執(zhí)行以下代碼：

scott@ORCL>update?id_table
??2??set?id_value=id_value+1
??3??where?id_name='MY_KEY';
已更新?1?行。

scott@ORCL>select?id_value
??2??from?id_table
??3??where?id_name='MY_KEY';

??ID_VALUE
----------
?????????1

問題有：

1.一次只能有一個(gè)用戶處理事務(wù)行，需要更新這一行來遞增計(jì)數(shù)器，這會(huì)導(dǎo)致程序必須串行完成這個(gè)操作。

2.在oracle中，倘若隔離級(jí)別為serializable，除第一個(gè)用戶外，視圖并發(fā)完成此操作的其他用戶都會(huì)接到這樣一個(gè)錯(cuò)誤:ORA-08117：can't serialize access for this transaction (無(wú)法串行訪問這個(gè)事務(wù))

scott@ORCL>set?transaction?isolation?level?serializable;
set?transaction?isolation?level?serializable
*
第?1?行出現(xiàn)錯(cuò)誤:
ORA-01453:?SET?TRANSACTION?必須是事務(wù)處理的第一個(gè)語(yǔ)句


scott@ORCL>commit;
提交完成。

scott@ORCL>set?transaction?isolation?level?serializable;
事務(wù)處理集。

scott@ORCL>update?id_table
??2??set?id_value=id_value+1
??3??where?id_name='MY_KEY';

已更新?1?行。

scott@ORCL>select?id_value
??2??from?id_table
??3??where?id_name='MY_KEY';

??ID_VALUE
----------
?????????2

下面，再到另一個(gè)sql*plus會(huì)話中完成同樣的操作，并發(fā)的請(qǐng)求唯一的ID：

scott@ORCL>set?transaction?isolation?level?serializable;
事務(wù)處理集。

scott@ORCL>update?id_table
??2??set?id_value=id_value+1
??3??where?id_name='MY_KEY';

此時(shí)它會(huì)阻塞，因?yàn)橐淮沃挥幸粋€(gè)事務(wù)可以更新這一行。這展示了第一個(gè)問題，即這個(gè)會(huì)話會(huì)阻塞，并等待該行提交。

由于我們使用的是oracle，而且隔離級(jí)別是serializable,提交第一個(gè)會(huì)話的事務(wù)時(shí)會(huì)觀察到以下行為：

scott@ORCL>commit;
提交完成。

第二個(gè)會(huì)話會(huì)立即顯示以下錯(cuò)誤：

scott@ORCL>update?id_table
??2??set?id_value=id_value+1
??3??where?id_name='MY_KEY';
update?id_table
*
第?1?行出現(xiàn)錯(cuò)誤:
ORA-08177:?無(wú)法連續(xù)訪問此事務(wù)處理

對(duì)于這個(gè)問題，正確解法：

scott@ORCL>create?table?t
??2??(pk?number?primary?key,
??3??name?varchar2(50));

表已創(chuàng)建。

scott@ORCL>create?sequence?t_seq;
序列已創(chuàng)建。

scott@ORCL>create?trigger?t_trigger?before?insert?on?t?for?each?row
??2??begin
??3??select?t_seq.nextval?into?:new.pk?from?dual;
??4??end;
??5??/

觸發(fā)器已創(chuàng)建

scott@ORCL>insert?into?t(name)?values('yin');
已創(chuàng)建?1?行。

scott@ORCL>insert?into?t(name)?values('xian');
已創(chuàng)建?1?行。

scott@ORCL>select?*?from?t;

????????PK?NAME
----------?--------------------------------------------------
?????????1?yin
?????????2?xian

其效果是為所插入的每一行自動(dòng)地(而且透明地)指定一個(gè)唯一鍵。

還有一個(gè)更優(yōu)的方法，完全沒有觸發(fā)器的開銷(首選方法)：

scott@ORCL>insert?into?t?values(t_seq.nextval,'wangwang');
已創(chuàng)建?1?行。

scott@ORCL>insert?into?t?values(t_seq.nextval,'qqq');
已創(chuàng)建?1?行。

scott@ORCL>select?*?from?t;

????????PK?NAME
----------?--------------------------------------------------
?????????1?yin
?????????2?xian
?????????4?wangwang
?????????6?qqq