资源描述：
《oracle绑定变量在c#net中的应用及意义》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、1.Whatareboundvariables?Bindvariables(bind,variable):Select*from,EMP,where,empno=:empno;Theuserisinplaceholdersinthequery,itwilltellOracleIwillthenprovideavalueforthevariable,nowneedtocreateaprogram,butmyactualexecutiontime,willprovidetheactualvalueshouldbeused〃・Essentially,itisanalternativevaria
2、blethatreplacesconstantsintheSQLstatement.BindingvariablescanmakeeachcommitSQLstatementexactlythesame・Two.Whydoyouusebindvariables?Usingboundvariablescanreducehardparsingandoptimizetheuseofsharedpools・InOracle,therearetwooptionalparsingprocessesforacommittedSQLstatement,onecalledhardparsing,andon
3、eiscalledsoftparsing・WhenaSQLstatementissubmitted,Oraclewillfirstcheckwhetherthesharedbufferpoolhasexactlythesamestatement,andifso,simplyperformasoftanalysis,oryou'11havetoperformahardanalysis.Ahardparserequiresmanysteps,suchasanalysis,analysis,securitycheck,establishmentofexecutionpath,optimizat
4、ionofaccessplan,andsoon.LargeamountsofCPUandresourcesareconsumed.Forexample,tofindachildnumbered001,thefollowingtwoformsofwritingcanbeachieved:Select*fromt_childwherech订did二'001';//donotusebindvariables・Select*fromtchildwherechildid二childid;//usebindvariables・However,theactualapplicationisoftenin
5、quiriesforchildrennumbered001,onceaftermaynotbeused;thenyoumayinquireaboutchildren'002',andthenquery'003'andsoon.Thus,eachqueryisanewquery,requiringhardparsing;Whilethesecondqueryprovidesabindingvariable:childid,itsvalueisprovidedinthequeryexecution,thequeryafteracompiledqueryschemeisstoredinthes
6、haredpool,canbeusedtoretrieveandreuse;inperformanceandscalability,thedifferenceishuge,evenisamazing;Withoutbindingvariables,theconditionsforeachqueryvary,resultingintoomanySQLstatementsinthesharedpool,lowreusability,speedinguptheagingofSQLstatements,andresultinginsharedpoolfragmentation.Thenumber
7、ofsharedSQLstatementsdifferentpoolishuge,accordingtotheLRUprinciple,somestatementofagingwaseventuallyclearedsothatthesharedpool;theshared_pool_sizehitrate,thesharedpoolmorefragmented,lackofavailablememoryspace・Inordert
