Oracle生成代理键的方法页1Word文档格式.docx
《Oracle生成代理键的方法页1Word文档格式.docx》由会员分享，可在线阅读，更多相关《Oracle生成代理键的方法页1Word文档格式.docx（6页珍藏版）》请在冰豆网上搜索。
ReferencingNewASNewOldASOld
ForEachRow
Declare
nTmpIDnumber（10,0）;
Begin
Selectse_tablename_idcolname.NEXTVALinto
nTmpIDfromdual;
:
new.idcolname:
=nTmpID;
End;
这种方法的缺点是：
需要为每个表创建一个对应的序列和一个对应的触发器。
序列：
虽然一个序列可以为多个表提供值但这样会使数值增加速度以及达到最大值的速度成倍增长。
因此还是需要为每个表创建一个序列。
序列值会在生成过程中产生漏洞，同样用过的值删除后也不难再被使用。
触发器：
上面的触发器如果不被disable，则无法由序列以外的其它途径指定代理键值，而事实上当从其它数据库复制已存在的数据或通过后台管理工具手工输入记录的时候，我们是不希望序列来提供值的。
为了解决触发器的这一问题，可以稍加改进，将触发器代码改为
Beginif:
new.idcolnameisnullthen
Selectse_tablename_idcolname.NEXTVALintonTmpIDfromdual;
endif;
这样，就可以在记录的代理键已指定的情
况下不在由触发器/序列提供值了。
2.只使用触发器
生成代理键
上面的方法中需要为每个表创建一个对应的序列和一个对应的触发器，这显然比较麻烦，我们可以采用下面的方法进行改进。
a.创建一个存储过程，用于计算指定表的可用键值（如果有漏洞则返回最小的一个，否则返回最大值的下一个值）。
该过程只需要创建一个，就可以被所有表的触发器调用。
PROCEDURESP_FINDIDHOLE（sTableIN
varchar2,sIDColNameINvarchar2,nIDHoleOutNumber,nMinINNumber:
=1）
IS/*function:
查找ID字段中的空缺数，如果没有就返回最大值加1.从指定的最小值开始查找，忽略比指定的最小值还小的ID值。
algorithm：
对半查找法/插值法*/
l_minNumber（38）;
l_maxNumber（38）;
actual_countNumber（38）;
sDynSqlvarchar（1000）;
begin
sDynSql:
='
selectmax（'
||sIDColName||'
）,min（'
）,count（'
||sIDColName||'
）From'
||sTable||'
Where
'
&
gt;
||To_Char（nMin）;
executeimmediatesDynSqlintol_max,l_min,actual_count;
l_min:
=nMin;
expected_count:
=l_max-l_min+1;
ifexpected_count=actual_countthen
nIDHole:
=l_max+1;
return;
whilel_max-l_min&
=1loop
--trylowerhalfofrange
half:
=trunc（expected_count/2）;
=expected_count-half;
selectcount（'
Where'
between'
||To_Char（l_min）||'
and'
||To_Char（l_max-half）;
executeimmediatesDynSqlintoactual_count;
exitwhenactual_count=0;
ifactual_count=expected_countthen
--missingvaluemustbeinupperhalf
l_min:
=l_min+half;
else
l_max:
=l_max-half;
endloop;
=l_min;
END;
--Procedureb.为表创建一个触发器
CreateOrReplaceTriggertg_tablename_idcolname
BeforeInsertONtablename
ForEachRowDeclarenTmpIDnumber（10,0）;
if:
sp_FindIDHole（'
tablename'
'
idcolname
:
new.idcolname）;
这种方法优点是：
只需要为每个表创建一个触发器
各个表的触发器调用同一存储过程，触发器中代码少。
存储过程每次先查找表中的键值漏洞以供使用，可以减少表中的键值漏洞，删除后的键值将被重复使用。
这种方法缺点是：
如果不指定键值，则只能用于使用InsertInto
Values进行单行插入时生成键值，如果使用了InsertInto
Select插入，则会出现错误：
ora-04091表正在修改中，函数/过程/触发器不能读取。
这是因为InsertIntoSelect执行过程可能涉及多条记录，而每条记录引起触发器调用的存储过程又要读取该表数据。
3.最完善的方法
上面的方法都有一定的局限性，下面的方法通用性较强，局限小
创建一个存储过程（用于查找漏洞，同上）
b.
创建一个存储过程（用于替换临时键值）
PROCEDURESP_SETNEWROWID（sTableIN
varchar2,
sIDColNameINvarchar2）IS
/*function:
为临时标识的新行替换代理键值*/
nNewIDnumber;
nMinnumber（10,0）;
nMaxnumber（10,0）;
inumber（10,0）;
ExecuteImmediate'
Select
Min（'
）,Max（'
lt;
=-10'
IntonMin,nMax;
ifnMinisnullthen
i:
=nMax;
whilei&
=nMinLoop
ifi&
=-10then
sp_FindIDHole（sTable,sIDColName,nNewID）;
Update'
Set'
||To_Char（nNewID）||'
||To_Char（i）;
=i-1;
c.创建一个序列，用于为任意表的新增行提供临时的键值
CREATESEQUENCEse_multiinsert_newrow_tmpidINCREMENTBY-1STARTWITH-10MINVALUE-9999999999MAXVALUE-10CYCLENOORDERCACHE20d.为每个表创建两个触发器
值以标识所有新增行）
se_MultiInsert_NewRow_TmpID.NEXTVALintonTmpID
fromdual;
ii.表触发器（用于在Insert语句执行后，对每个带有临时键值的新增行依次赋予由存储过程计算出的最终的键值）
CreateOrReplaceTriggertg_tablename_idcolname_A
AfterInsertONtablename
Beginsp_SetNewRowID（'
）;
这种方法的优点：
可以通用于所有插入记录的情况。
这种方法的缺点：
需要为每个表创建两个触发器触发器的执行可能使数据库性能下降（但总体消耗
原本就是必须的）三．Oracle方法说明和选择
1.方法的选择：
a）如果确认在不指定键值的情况下，只使用Insert
IntoValues向表中插入记录，则可以对该表采用第2种方法。
（如果指定了键值，触发器实际上不起作用，因此可以一次插入多条记录，例如通过导入进行插入记录）
b）如果在不指定键值的情况下，有可能使用Insert
IntoSelect向表中插入记录，则应该采用第3种方法。
c）如果表的键值要求不能重复使用（删除的不能再
重用），则应该采用第1种方法。
如果不介意多表共用一个序列，可以只创建一个序列以尽量减少工作量。
2.
方法说明a）以上方法中的示例代码，均默认键值为正数，因此采用－10以下的负数来临时标识新增行。
如果需要负数作为键值，应对相关代码进行适当修改。
主要涉及的是sp_FindIDHole中的查找范围的确定和排除，se_MultiInsert_NewRow_TmpID的生成值范围。
b）以上通用的对象（如存储过程和序列）可用于多个表，如果多个表的情况不同，可将表按情况分组，每组采用一个方法，使用一套通用对象。
个别的表也可以个别处理。
升级会员 