DynamicSQL动态SQL

《DynamicSQL动态SQL》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、DynamicSQLWhatWillILearn?RecallthestagesthroughwhichallSQLstatementspassDescribethereasonsforusingdynamicSQLtocreateaSQLstatementListfourPL/SQLstatementssupportingNativeDynamicSQLDescribethebenefitsofEXECUTEIMMEDIATEoverDBMS_SQLforDynamicSQLWhyLearnIt?In

2、thislesson,youlearntoconstructandexecuteSQLstatementsdynamically—inotherwords,atruntimeusingtheNativeDynamicSQLstatementsinPL/SQL.DynamicallyexecutingSQLandPL/SQLcodeextendsthecapabilitiesofPL/SQLbeyondqueryandtransactionaloperations.Thelessonalsocompare

3、sNativeDynamicSQLtotheDBMS_SQLpackage,whichprovidessimilarcapabilities.ExecutionFlowofSQLAllSQLstatementsinthedatabasegothroughvariousstages:Parse:pre-execution“isthispossible?”checksincludingsyntax,objectexistence,privilegesandsoonBind:gettingtheactualv

4、aluesofanyvariablesreferencedinthestatementExecute:thestatementisexecutedFetch:resultsarereturnedtotheuser.Somestagesmaynotberelevantforallstatements—forexample,thefetchphaseisapplicabletoqueriesbutnotDML.ExecutionFlowofSQLinPL/SQLSubprogramsWhenaSQLsta

5、tementisincludedinaPL/SQLsubprogram,theparseandbindphasesarenormallydoneatcompiletime,iewhentheprocedure,functionorpackagebodyisCREATEd.WhatifthetextoftheSQLstatementisnotknownwhentheprocedureiscreated?HowcouldtheOracleserverparseit?Itcouldn’t.Forexample

6、,supposewewanttoDROPatable,buttheuserwillenterthetablenameatexecutiontime:DynamicSQLWeusedynamicSQLtocreateaSQLstatementwhosetextisnotcompletelyknowninadvance.DynamicSQL:•Isconstructedandstoredasacharacterstringwithinasubprogram.•IsaSQLstatementwithvaryi

7、ngcolumndata,ordifferentconditionswithorwithoutplaceholders(bindvariables)•Enablesdata-definition,data-control,orsession-controlstatementstobewrittenandexecutedfromPL/SQL•IsexecutedwithNativeDynamicSQLstatements(EXECUTEIMMEDIATE)ortheDBMS_SQLpackageDynam

8、icSQLstatementsareparsedatexecutiontime,notatcompiletime.NativeDynamicSQLProvidesnativesupportforDynamicSQLdirectlyinthePL/SQLlanguageProvidestheabilitytoexecuteSQLstatementswhosestructureisunknownuntilexecutiontimeUsesthe