MSSQL网页编程之变异表的成绩
如果WHERE子句的查询条件里使用比较操作符LIKE和REGEXP，MySQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKEabc%‘，MySQL将使用索引；如果查询条件是LIKE%abc’，MySQL将不使用索引。成绩在论坛中常常碰到变异表的成绩,我保藏了一片有关变异表成绩的文章,如今把他放在这里,但愿能和人人分享!
AvoidingMutatingTables
Ok,soyouvejustrecievedtheerror:
ORA-04091:tableXXXXismutating,trigger/functionmaynotseeit
andyouwanttogetaroundthat.Thisshortarticlewilldescribeanddemonstratethevariousmethodsofgettingaroundthemutatingtableerror.
Ifyouareinterestedinwhyyouaregettingitandinwhatcasesyouwillgetit,pleaseseetheOracleServerApplicationDevelopersGuide(clickheretoreaditrightnow--thislinkistotechnet.oracle.com.Youneedapasswordtoaccessthissitebutyoucangetonerightawayforfree).
Avoidingthemutatingtableerrorisfairlyeasy.WemustdeferprocessingagainstthemutatingorconstrainngtableuntilanAFTERtrigger.Wewillconsidertwocases:
HittingtheORA-4091inanINSERTtriggeroranUPDATEtriggerwhereyouonlyneedaccesstothe:newvalues
HittingtheORA-4091inaDELETEtriggeroranUPDATEtriggerwhereyouneedtoaccessthe:oldvalues
Case1-youonlyneedtoaccessthe:newvalues
Thiscaseisthesimplest.WhatwewilldoiscapturetheROWIDSoftheinsertedorudpatedrows.WecanthenusetheseROWIDSinanAFTERtriggertoqueryuptheaffectedrows.
Italwaystakes3triggerstoworkaroundthemutatingtableerror.Theyare:
Abeforetriggertosetthepackagestatetoaknown,consistentstate
Anafter,rowleveltriggertocaptureeachrowschanges
Anaftertriggertoactuallyprocessthechange.
Asanexample--toshowhowtodothis,wewillattempttoanswerthefollowingquestion:
Ihaveatablecontainingakey/status/effectivedatecombination.Whenstatus
changes,thevaluesarepropagatedbytriggertoalogtablerecordingthe
statushistory.WhennoRIconstraintisinplaceeverythingworksfine.
WhenanRItriggerenforcesaparent-childrelationship,thestatuschange
loggingtriggerfailsbecausetheparenttableismutating.Propagatingthe
valuestothelogtableimplicitlygeneratesalookupbacktotheparenttable
toensuretheRIconstraintissatisfied.
IdonotwanttodroptheRIconstraint.Irealizethatthestatusis
denormalized.Iwantitthatway.Whatisagoodwaytomaintainthelog?
Hereistheimplementation:
SQL>createtableparent
2(theKeyintprimarykey,
3statusvarchar2(1),
4effDatedate
5)
6/
Tablecreated.
SQL>createtablelog_table
2(theKeyintreferencesparent(theKey),
3statusvarchar2(1),
4effDatedate
5)
6/
Tablecreated.
SQL>REMthispackageisusedtomaintainourstate.Wewillsavetherowidsofnewly
SQL>REMinserted/updatedrowsinthispackage.Wedeclare2arrays--onewill
SQL>REMholdournewrowsrowids(newRows).Theotherisusedtoresetthisarray,
SQL>REMitisanemptyarray
SQL>createorreplacepackagestate_pkg
2as
3typeridArrayistableofrowidindexbybinary_integer;
4
4newRowsridArray;
5emptyridArray;
6end;
7/
Packagecreated.
SQL>REMWemustsetthestateoftheabovepackagetosomeknown,consistentstate
SQL>REMbeforewebeingprocessingtherowtriggers.Thistriggerismandatory,
SQL>REMwe*cannot*relyontheAFTERtriggertoresetthepackagestate.This
SQL>REMisbecauseduringamulti-rowinsertorupdate,theROWtriggermayfire
SQL>REMbuttheAFTERtirggerdoesnothavetofire--ifthesecondrowinanupdate
SQL>REMfailsduetosomeconstrainterror--therowtriggerwillhavefired2times
SQL>REMbuttheAFTERtrigger(whichwereliedontoresetthepackage)willneverfire.
SQL>REMThatwouldleave2erroneousrowidsinthenewRowsarrayforthenextinsert/update
SQL>REMtosee.Therefore,beforetheinsert/updatetakesplace,wereset
SQL>createorreplacetriggerparent_bi
2beforeinsertorupdateonparent
3begin
4state_pkg.newRows:=state_pkg.empty;
5end;
6/
Triggercreated.
SQL>REMThistriggersimplycapturestherowidoftheaffectedrowand
SQL>REMsavesitinthenewRowsarray.
SQL>createorreplacetriggerparent_aifer
2afterinsertorupdateofstatusonparentforeachrow
3begin
4state_pkg.newRows(state_pkg.newRows.count+1):=:new.rowid;
5end;
6/
Triggercreated.
SQL>REMthistriggerprocessesthenewrows.WesimplyloopoverthenewRows
SQL>REMarrayprocessingeachnewlyinserted/modifiedrowinturn.
SQL>createorreplacetriggerparent_ai
2afterinsertorupdateofstatusonparent
3begin
4foriin1..state_pkg.newRows.countloop
5insertintolog_table
6selecttheKey,status,effDate
7fromparentwhererowid=state_pkg.newRows(i);
8endloop;
9end;
10/
Triggercreated.
SQL>REMthisdemonstratesthatwecanprocesssingleandmulti-rowinserts/updates
SQL>REMwithoutfailure(andcandoitcorrectly)
SQL>insertintoparentvalues(1,A,sysdate-5);
1rowcreated.
SQL>insertintoparentvalues(2,B,sysdate-4);
1rowcreated.
SQL>insertintoparentvalues(3,C,sysdate-3);
1rowcreated.
SQL>insertintoparentselecttheKey+6,status,effDate+1fromparent;
3rowscreated.
SQL>select*fromlog_table;
THEKEYSEFFDATE
--------------------
1A04-AUG-99
2B05-AUG-99
3C06-AUG-99
7A05-AUG-99
8B06-AUG-99
9C07-AUG-99
6rowsselected.
SQL>updateparentsetstatus=chr(ascii(status)+1),effDate=sysdate;
6rowsupdated.
SQL>select*fromlog_table;
THEKEYSEFFDATE
--------------------
1A04-AUG-99
2B05-AUG-99
3C06-AUG-99
7A05-AUG-99
8B06-AUG-99
9C07-AUG-99
1B09-AUG-99
2C09-AUG-99
3D09-AUG-99
7B09-AUG-99
8C09-AUG-99
9D09-AUG-99
12rowsselected.
Case2-youneedtoaccessthe:oldvalues
Thisoneisalittlemoreinvolvedbuttheconceptisthesame.WellsavetheactualOLDvaluesinanarray(asopposedtojusttherowidsofthenewrows).Usingtablesofrecordsthisisfairlystraightforward.Letssaywewantedtoimplementaflagdeleteofdata--thatis,insteadofactuallydeletingtherecord,youwouldliketosetadatefieldtoSYSDATEandkeeptherecordinthetable(buthideitfromqueries).Weneedtoundothedelete.
InOracle8.0andup,wecoulduse"INSTEADOF"triggersonaviewtodothis,butin7.3theimplementationwouldlooklikethis:
SQL>REMthisisthetablewewillbeflagdeletingfrom.
SQL>REMNoonewilleveraccessthistabledirectly,rather,
SQL>REMtheywillperformallinsert/update/delete/selectsagainst
SQL>REMaviewonthistable..
SQL>createtabledelete_demo(aint,
2bdate,
3cvarchar2(10),
4hidden_datedatedefaultto_date(01-01-0001,DD-MM-YYYY),
5primarykey(a,hidden_date))
6/
Tablecreated.
SQL>REMthisisourview.AllDMLwilltakeplaceontheview,thetable
SQL>REMwillnotbetouched.
SQL>createorreplaceviewdelete_demo_viewas
2selecta,b,cfromdelete_demowherehidden_date=to_date(01-01-0001,DD-MM-YYYY)
3/
Viewcreated.
SQL>grantallondelete_demo_viewtopublic
2/
Grantsucceeded.
SQL>REMhereisthestatepackageagain.Thistimethearrayisof
SQL>REMTABLE%ROWTYPE--notjustarowid
SQL>createorreplacepackagedelete_demo_pkg
2as
3typearrayistableofdelete_demo%rowtypeindexbybinary_integer;
4
4oldvalsarray;
5emptyarray;
6end;
7/
Packagecreated.
SQL>REMtheresettrigger...
SQL>createorreplacetriggerdelete_demo_bd
2beforedeleteondelete_demo
3begin
4delete_demo_pkg.oldvals:=delete_demo_pkg.empty;
5end;
6/
Triggercreated.
SQL>REMHere,insteadofcapturingtherowid,wemustcapturethebeforeimage
SQL>REMoftherow.
SQL>REMWecannotreallyundothedeletehere,wearejustcapturingthedeleted
SQL>REMdata
SQL>createorreplacetriggerdelete_demo_bdfer
2beforedeleteondelete_demo
3foreachrow
4declare
5inumberdefaultdelete_demo_pkg.oldvals.count+1;
6begin
7delete_demo_pkg.oldvals(i).a:=:old.a;
8delete_demo_pkg.oldvals(i).b:=:old.b;
9delete_demo_pkg.oldvals(i).c:=:old.c;
10end;
11/
Triggercreated.
SQL>REMNow,wecanputthedeleteddatabackintothetable.WeputSYSDATE
SQL>REMinasthehidden_datefield--thatshowsuswhentherecordwasdeleted.
SQL>createorreplacetriggerdelete_demo_ad
2afterdeleteondelete_demo
3begin
4foriin1..delete_demo_pkg.oldvals.countloop
5insertintodelete_demo(a,b,c,hidden_date)
6values
7(delete_demo_pkg.oldvals(i).a,delete_demo_pkg.oldvals(i).b,
8delete_demo_pkg.oldvals(i).c,sysdate);
9endloop;
10end;
11/
Triggercreated.
SQL>REMNow,toshowitatwork...
SQL>insertintodelete_demo_viewvalues(1,sysdate,Hello);
1rowcreated.
SQL>insertintodelete_demo_viewvalues(2,sysdate,Goodbye);
1rowcreated.
SQL>select*fromdelete_demo_view;
ABC
-----------------------------
109-AUG-99Hello
209-AUG-99Goodbye
SQL>deletefromdelete_demo_view;
2rowsdeleted.
SQL>select*fromdelete_demo_view;
norowsselected
SQL>select*fromdelete_demo;
ABCHIDDEN_DA
--------------------------------------
109-AUG-99Hello09-AUG-99
209-AUG-99Goodbye09-AUG-99
BDB源自BerkeleyDB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性 从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。 两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书 两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书 SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。） 需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。 对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。 我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力 现在是在考虑:如果写到服务器端,我一下搞他个10个存储过程导过去,那久之服务器不就成垃圾箱了吗?即便优化了我的中间层.
页:
[1]