Oracle生成代理键的方法页1Word文档格式.docx

1、Referencing New AS New Old AS OldFor Each RowDeclarenTmpID number（10,0）;BeginSelect se_tablename_idcolname.NEXTVAL intonTmpID from dual;:new.idcolname:=nTmpID;End; 这种方法的缺点是：需要为每个表创建一个对应的序列和一个对应的触发器。序列：虽然一个序列可以为多个表提供值但这样会使数值增加速 度以及达到最大值的速度成倍增长。因此还是需要为每个表 创建一个序列。序列值会在生成过程中产生漏洞，同样用过的值删除后也不 难再被使用。触发器：上面

2、的触发器如果不被 disable ，则无法由序列以外的其它途 径指定代理键值，而事实上当从其它数据库复制已存在的数 据或通过后台管理工具手工输入记录的时候，我们是不希望 序列来提供值的。为了解决触发器的这一问题，可 以稍加改进，将触发器代码改为Begin if :new.idcolname is null thenSelect se_tablename_idcolname.NEXTVAL into nTmpID from dual;end if;这样，就可以在记录的代理键已指定的情况下不在由触发器 /序列提供值了。 2. 只使用触发器生成代理键上面的方法中需要为每个表创建一个对应的序列和一个对

3、 应的触发器，这显然比较麻烦，我们可以采用下面的方法进 行改进。a. 创建一个存储过程，用于计算指定表的可用键值 （如果有漏洞则返回最小的一个，否则返回最大值的下一个 值）。该过程只需要创建一个，就可以被所有表的触发器调 用。PROCEDURE SP_FINDIDHOLE（ sTable INvarchar2, sIDColName IN varchar2, nIDHole Out Number, nMin IN Number:=1）IS /*function: 查找 ID 字段中的空缺数，如果没有就返回最大值 加 1. 从指定的最小值开始查找， 忽略比指定的最小值还小的 ID 值。 algo

4、rithm ：对半查找法 /插值法 */l_min Number（38）;l_max Number（38）;actual_count Number（38）;sDynSql varchar（1000）;beginsDynSql:=select max（|sIDColName|）,min（）,count（|sIDCo lName|） From |sTable| Where>|To_Char（nMin）;execute immediate sDynSql into l_max,l_min,actual_count;l_min:=nMin;expected_count := l_max - l_

5、min + 1;if expected_count = actual_count thennIDHole:=l_max+1;return;while l_max - l_min &= 1 loop- try lower half of rangehalf := trunc（expected_count/2）;= expected_count - half;select count（ Where between |To_Char（l_min）| and |To_Char（l_max - half）; execute immediate sDynSql into actual_count;exit

6、 when actual_count = 0;if actual_count = expected_count then- missing value must be in upper halfl_min := l_min + half;elsel_max := l_max - half;end loop;=l_min;END; - Procedureb. 为表创建一个触发器Create Or Replace Trigger tg_tablename_idcolnameBefore Insert ON tablenameFor Each RowDeclare nTmpID number（10,

7、0）;if :sp_FindIDHole（ tablename , idcolname,:new.idcolname）; 这种方法优点是：只需要为每个表创建一个触发器各个表的触发器调用同一存储过程，触发器中代码少。存储过程每次先查找表中的键值漏洞以供使用，可以减少表 中的键值漏洞，删除后的键值将被重复使用。这种方法缺点是：如果不指定键值，则只能用于使用 Insert IntoValues 进行单行插入时生成键值，如果使用了 Insert IntoSelect 插入，则会出现错误： ora-04091 表正在修改中，函 数 /过程 /触发器不能读取。这是因为 Insert Into Select

8、 执行 过程可能涉及多条记录，而每条记录引起触发器调用的存储 过程又要读取该表数据。 3. 最完善的方法上面的方法都有一定的局限性，下面的方法通用性较强，局 限小创建一个存储过程（用于查找漏洞，同上）b.创建一个存储过程（用于替换临时键值）PROCEDURE SP_SETNEWROWID（ sTable INvarchar2,sIDColName IN varchar2）IS/*function:为临时标识的新行替换代理键值 */nNewID number;nMin number（10,0）;nMax number（10,0）;i number（10,0）;Execute Immediate

9、SelectMin（）,Max（lt;=-10 Into nMin,nMax;if nMin is null theni:=nMax;while i&=nMin Loopif i&=-10 thensp_FindIDHole（sTable,sIDColName,nNewID）;Update Set |To_Char（nNewID）|To_Char（i）;=i-1;c. 创建一个序列，用于为任意表的新增行提供 临时的键值CREATE SEQUENCE se_multiinsert_newrow_tmpid INCREMENT BY -1 START WITH -10 MINVALUE -9999

10、999999 MAXVALUE -10 CYCLE NOORDER CACHE 20d. 为每个表创建两个触发器值以标识所有新增行）se_MultiInsert_NewRow_TmpID.NEXTVAL into nTmpIDfrom dual;ii. 表触发器（用于在 Insert 语句执行 后，对每个带有临时键值的新增行依次赋予由存储过程计算 出的最终的键值）Create Or Replace Trigger tg_tablename_idcolname_AAfter Insert ON tablenameBegin sp_SetNewRowID（）;这种方法的优点：可以通用于所有插入记录

11、的情况。这种方法的缺点：需要为每个表创建两个触发器 触发器的执行可能使数据库性能下降（但总体消耗原本就是必须的）三 Oracle 方法说明和选择1. 方法的选择：a） 如果确认在不指定键值的情况下，只使用 InsertInto Values 向表中插入记录， 则可以对该表采用第 2 种方法。 （如果指定了键值，触发器实际上不起作用，因此可以一次 插入多条记录，例如通过导入进行插入记录）b） 如果在不指定键值的情况下，有可能使用 InsertInto Select 向表中插入记录，则应该采用第 3 种方法。c） 如果表的键值要求不能重复使用（删除的不能再重用），则应该采用第 1 种方法。如果不介意多表共用一个 序列，可以只创建一个序列以尽量减少工作量。2.方法说明 a） 以上方法中的示例代码，均默认键值为正数，因 此采用 10 以下的负数来临时标识新增行。 如果需要负数作 为键值，应对相关代码进行适当修改。主要涉及的是 sp_FindIDHole 中的查找范围的确定和排除， se_MultiInsert_NewRow_TmpID 的生成值范围。b） 以上通用的对象（如存储过程和序列）可用于多 个表，如果多个表的情况不同，可将表按情况分组，每组采 用一个方法， 使用一套通用对象。 个别的表也可以个别处理。