sqlplus之防御式编程代码分享

采用防御式编程技术来构建真正可移植的数据库应用。

场景：为每一行生成一个唯一的主键，

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

表已创建。

scott@ORCL>insert into id_table values('MY_KEY',0);
已创建 1 行。

scott@ORCL>commit;
提交完成。

为了得到一个新的键，必须执行以下代码：

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.一次只能有一个用户处理事务行，需要更新这一行来递增计数器，这会导致程序必须串行完成这个操作。

2.在oracle中，倘若隔离级别为serializable，除第一个用户外，视图并发完成此操作的其他用户都会接到这样一个错误:ORA-08117：can't serialize access for this transaction (无法串行访问这个事务)

scott@ORCL>set transaction isolation level serializable;
set transaction isolation level serializable
*
第 1 行出现错误:
ORA-01453: SET TRANSACTION 必须是事务处理的第一个语句


scott@ORCL>commit;
提交完成。

scott@ORCL>set transaction isolation level serializable;
事务处理集。

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

下面，再到另一个sql*plus会话中完成同样的操作，并发的请求唯一的ID：

scott@ORCL>set transaction isolation level serializable;
事务处理集。

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

此时它会阻塞，因为一次只有一个事务可以更新这一行。这展示了第一个问题，即这个会话会阻塞，并等待该行提交。

由于我们使用的是oracle，而且隔离级别是serializable,提交第一个会话的事务时会观察到以下行为：

scott@ORCL>commit;
提交完成。

第二个会话会立即显示以下错误：

scott@ORCL>update id_table
  2  set id_value=id_value+1
  3  where id_name='MY_KEY';
update id_table
*
第 1 行出现错误:
ORA-08177: 无法连续访问此事务处理

对于这个问题，正确解法：

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

表已创建。

scott@ORCL>create sequence t_seq;
序列已创建。

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  /

触发器已创建

scott@ORCL>insert into t(name) values('yin');
已创建 1 行。

scott@ORCL>insert into t(name) values('xian');
已创建 1 行。

scott@ORCL>select * from t;

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

其效果是为所插入的每一行自动地(而且透明地)指定一个唯一键。

还有一个更优的方法，完全没有触发器的开销(首选方法)：

scott@ORCL>insert into t values(t_seq.nextval,'wangwang');
已创建 1 行。

scott@ORCL>insert into t values(t_seq.nextval,'qqq');
已创建 1 行。

scott@ORCL>select * from t;

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