《Oracle生成代理键的方法》由会员分享，可在线阅读，更多相关《Oracle生成代理键的方法（19页珍藏版）》请在金锄头文库上搜索。
1、Oracle生成代理键的方法（页1）Oracle生成代理键的方法这里代理键指使用非businesscolumn作为主键字段的情况，通常就是我们想给每条记录添加的ID。一MSSqlServer的代理键1. 最简单的方法是使用自增字段，这样就不必在代码中处理自增字段。缺点：用过的值删除后不会很难再被使用2. 采用最大值表的方法保存所有代理键的最大值。可以将处理算法放在服务器端的存储过程中，也可以采用客户端算法生成代理键，但这都就需要在代码中明确给代理键字段赋值。采用这种方法还要注意防止同时读写代理键候选值。二Oracle的代理键1. Oracle中没有自增字段，所以不管采取什么方法，都免不了要明确
2、处理代理键字段。最接近MSSqlServer自增字段的方法步骤如下：a.针对表创建一个序列se_tablename_idcolnameb.为表创建一个触发器CreateOrReplaceTriggertg_tablename_idcolnameBeforeInsertONtablenameReferencingNewASNewOldASOldForEachRowDeclarenTmpIDnumber(10,0);BeginSelectse_tablename_idcolname.NEXTVALintonTmpIDfromdual;:new.idcolname:=nTmpID;End;这种方法的
3、缺点是：需要为每个表创建一个对应的序列和一个对应的触发器。序列：虽然一个序列可以为多个表提供值但这样会使数值增加速度以及达到最大值的速度成倍增长。因此还是需要为每个表创建一个序列。序列值会在生成过程中产生漏洞，同样用过的值删除后也不难再被使用。触发器：上面的触发器如果不被disable，则无法由序列以外的其它途径指定代理键值，而事实上当从其它数据库复制已存在的数据或通过后台管理工具手工输入记录的时候，我们是不希望序列来提供值的。为了解决触发器的这一问题，可以稍加改进，将触发器代码改为Beginif:new.idcolnameisnullthenSelectse_tablename_idcoln
4、ame.NEXTVALintonTmpIDfromdual;:new.idcolname:=nTmpID;endif;End;这样，就可以在记录的代理键已指定的情况下不在由触发器/序列提供值了。2.只使用触发器生成代理键上面的方法中需要为每个表创建一个对应的序列和一个对应的触发器，这显然比较麻烦，我们可以采用下面的方法进行改进。a.创建一个存储过程，用于计算指定表的可用键值(如果有漏洞则返回最小的一个，否则返回最大值的下一个值)。该过程只需要创建一个，就可以被所有表的触发器调用。PROCEDURESP_FINDIDHOLE(sTableINvarchar2,sIDColNameINvarcha
5、r2,nIDHoleOutNumber,nMinINNumber:=1)IS/*function:查找ID字段中的空缺数，如果没有就返回最大值加1.从指定的最小值开始查找，忽略比指定的最小值还小的ID值。algorithm：对半查找法/插值法*/l_minNumber(38);l_maxNumber(38);actual_countNumber(38);expected_countNumber(38);halfNumber(38);sDynSqlvarchar(1000);beginsDynSql:=selectmax(|sIDColName|),min(|sIDColName|),count
6、(|sIDColName|)From|sTable|Where|sIDColName|>=|To_Char(nMin);executeimmediatesDynSqlintol_max,l_min,actual_count;l_min:=nMin;expected_count:=l_max-l_min+1;ifexpected_count=actual_countthennIDHole:=l_max+1;return;endif;whilel_max-l_min>=1loop-trylowerhalfofrangehalf:=trunc(expected_count/2);expe
7、cted_count:=expected_count-half;sDynSql:=selectcount(|sIDColName|)From|sTable|Where|sIDColName|between|To_Char(l_min)|and|To_Char(l_max-half);executeimmediatesDynSqlintoactual_count;exitwhenactual_count=0;ifactual_count=expected_countthen-missingvaluemustbeinupperhalfl_min:=l_min+half;elsel_max:=l_m
8、ax-half;endif;endloop;nIDHole:=l_min;END;-Procedureb.为表创建一个触发器CreateOrReplaceTriggertg_tablename_idcolnameBeforeInsertONtablenameReferencingNewASNewOldASOldForEachRowDeclarenTmpIDnumber(10,0);Beginif:new.idcolnameisnullthensp_FindIDHole(tablename,idcolname,:new.idcolname);endif;End;这种方法优点是：只需要为每个表创建
9、一个触发器各个表的触发器调用同一存储过程，触发器中代码少。存储过程每次先查找表中的键值漏洞以供使用，可以减少表中的键值漏洞，删除后的键值将被重复使用。如果不指定键值，则只能用于使用InsertIntoValues进行单行插入时生成键值，如果使用了InsertIntoSelect插入，则会出现错误：ora-04091表正在修改中，函数/过程/触发器不能读取。这是因为InsertIntoSelect执行过程可能涉及多条记录，而每条记录引起触发器调用的存储过程又要读取该表数据。3.最完善的方法上面的方法都有一定的局限性，下面的方法通用性较强，局限小a. 创建一个存储过程（用于查找漏洞，同上）b. 创
10、建一个存储过程（用于替换临时键值）PROCEDURESP_SETNEWROWID（sTableINvarchar2,sIDColNameINvarchar2）IS/*function:为临时标识的新行替换代理键值*/nNewIDnumber;nMinnumber(10,0);nMaxnumber(10,0);inumber(10,0);BeginExecuteImmediateSelectMin(|sIDColName|),Max(|sIDColName|)From|sTable|Where|sIDColName|<=-10IntonMin,nMax;ifnMinisnullthenre
11、turn;endif;i:=nMax;whilei>=nMinLoopifi<=-10thensp_FindIDHole(sTable,sIDColName,nNewID);ExecuteImmediateUpdate|sTable|Set|sIDColName|=|To_Char(nNewID)|Where|sIDColName|=|To_Char(i);endif;i:=i-1;endloop;End;c.创建一个序列，用于为任意表的新增行提供临时的键值CREATESEQUENCEse_multiinsert_newrow_tmpidINCREMENTBY-1STARTWITH
12、-10MINVALUE-9999999999MAXVALUE-10CYCLENOORDERCACHE 20d.为每个表创建两个触发器i.行触发器（用于给每个新增的行赋一个临时的键值以标识所有新增行)CreateOrReplaceTriggertg_tablename_idcolnameBeforeInsertON tablenameReferencingNewASNewOldASOldForEachRowDeclarenTmpIDnumber(10,0);Beginif:new.idcolnameisnullthenSelectse_MultiInsert_NewRow_TmpID.NEXTV
13、ALintonTmpIDfromdual;:new.idcolname:=nTmpID;endif;End;ii.表触发器(用于在Insert语句执行后，对每个带有临时键值的新增行依次赋予由存储过程计算出的最终的键值)CreateOrReplaceTriggertg_tablename_idcolname_AAfterInsertONtablenameReferencingNewASNewOldASOldBeginsp_SetNewRowID(tablename,idcolname);End;这种方法的优点：可以通用于所有插入记录的情况。这种方法的缺点：需要为每个表创建两个触发器触发器的执行可
14、能使数据库性能下降（但总体消耗原本就是必须的）三Oracle方法说明和选择1. 方法的选择：a） 如果确认在不指定键值的情况下，只使用InsertIntoValues向表中插入记录，则可以对该表采用第2种方法。（如果指定了键值，触发器实际上不起作用，因此可以一次插入多条记录，例如通过导入进行插入记录）b） 如果在不指定键值的情况下，有可能使用InsertIntoSelect向表中插入记录，则应该采用第3种方法。c） 如果表的键值要求不能重复使用（删除的不能再重用），则应该采用第1种方法。如果不介意多表共用一个序列，可以只创建一个序列以尽量减少工作量。2.方法说明a) 以上方法中的示例代码，均默认键值为正数，因此采用10以下的负数来临时标识新增行。如果需要负数作为键值，应对相关代码进行适当修改。主要涉及的是sp_FindIDHole中的查找范围的确定和排除，se_MultiInsert_NewRow_TmpID的生成值范围。b) 以上通用的对象(如存储过程和序列)可用于多个表，如果多个表的情况不同，可将表按情况分组，每组采用一个方法，使用一套通用对象。个别的表也可以个别处理。
