Oracle生成代理键的方法(页1).docxVIP

Oracle 生成代理键的方法 （页 1） Oracle 生成代理键的方法这里代理键指使用非 business column 作为主键字段的情况，通常就是我们想给 每条记录添加的ID。一. MSSqIServer的代理键 最简单的方法是使用自增字段，这样就不必在代 码中处理自增字段。 缺点：用过的值删除后不会很难再被使用 采用最大值表的方法保存所有代理键的最大值。 可以将处理算法放在服务器端的存储过程中，也可以采用客 户端算法生成代理键，但这都就需要在代码中明确给代理键 字段赋值。采用这种方法还要注意防止同时读写代理键候选 值。二． OracIe 的代理键 1. OracIe 中没有自增字段， 所以不管采取什么方法， 都免不了要明确处理代理键字段。 最接近 MSSqIServer 自增 字段的方法步骤如下： a.针对表创建一个序列 se_tabIename_idcoIname a. b. b. 为表创建一个触发器 Create Or Replace Trigger tg_tablename_idcolname Before Insert ON tablename Referencing New AS New Old AS Old For Each Row Declare nTmpID number(10,0); Begin Select se_tablename_idcolname.NEXTVAL into nTmpID from dual; :new.idcolname:=nTmpID; End; 这种方法的缺点是： 需要为每个表创建一个对应的序列和一个对应的触发器。 序列： 虽然一个序列可以为多个表提供值但这样会使数值增加速 度以及达到最大值的速度成倍增长。因此还是需要为每个表 创建一个序列。 序列值会在生成过程中产生漏洞，同样用过的值删除后也不 难再被使用。 触发器： 上面的触发器如果不被 disable ，则无法由序列以外的其它途 径指定代理键值，而事实上当从其它数据库复制已存在的数 据或通过后台管理工具手工输入记录的时候，我们是不希望 序列来提供值的。 为了解决触发器的这一问题，可 以稍加改进，将触发器代码改为 Begin if :new.idcolname is null then Select se_tablename_idcolname.NEXTVAL into nTmpID from dual; :new.idcolname:=nTmpID; end if; End; 这样，就可以在记录的代理键已指定的情 况下不在由触发器 /序列提供值了。 2. 只使用触发器 生成代理键 上面的方法中需要为每个表创建一个对应的序列和一个对 应的触发器，这显然比较麻烦，我们可以采用下面的方法进 行改进。 expected_count Number(38); expected_count Number(38); a. 创建一个存储过程，用于计算指定表的可用键值 (如果有漏洞则返回最小的一个，否则返回最大值的下一个 值)。该过程只需要创建一个，就可以被所有表的触发器调 用。 PROCEDURE SP_FINDIDHOLE( sTable IN varchar2, sIDColName IN varchar2, nIDHole Out Number, nMin IN Number:=1) IS /*function: 查找 ID 字段中的空缺数，如果没有就返回最大值 加 1. 从指定的最小值开始查找， 忽略比指定的最小值还小的 ID 值。 algorithm ：对半查找法 /插值法 */ l_min Number(38); l_max Number(38); actual_count Number(38); half Number(38); half Number(38); sDynSql varchar(1000); begin sDynSql:=select max(||sIDColName||),min(||sIDColName||),count(||sIDCo lName||) From ||sTable|| Where ||sIDColName||gt;=||To_Char(nMin); execute immediate sDynSql into l_max,l_min,actual_count; l_min:=nMin; expected_count := l_max - l_min + 1; if expected_count = actual_count then nIDHole:=l_max+1; return; end if; while l_max - l_min gt;= 1 loop -- try lo