资源描述：
《游标简明教程（vernier concise tutorial）》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、游标简明教程（Vernierconcisetutorial）OraclecursortutorialSQLisalanguageforaccessingORACLEdatabases,andPL/SQLextendsandreinforcesthecapabilitiesofSQL,whichintroducesamorepowerfulprogramlogic.PL/SQLsupportstheDMLcommandandthetransactioncontrolstatementofSQL.DDLisnotsupportedinPL/SQL,whichmeansthatyoucan
2、notcreatetablesoranyotherobjectinaPL/SQLblock.PL/SQLprogrammingisbetteruselikeDBMS_SQLbuilt-inpackageorexecutetheEXECUTEIMMEDIATEcommandtobuilddynamicSQLtoexecutetheDDLcommandinthePL/SQLblock,thecompilerensuresthatPL/SQLobjectreferencesanduserpermissions.Next,wediscussvariousDDLandTCLstatements
3、thatareusedtoaccesstheORACLEdatabase.queryTheSELECTstatementisusedtoquerydatafromthedatabase,whenusingtheSELECTstatementinPL/SQL,tobeusedwiththeINTOclause,thereturnvalueisgivenintheINTOclauseofthequeryvariables,variabledeclarationisinDELCARE.TheSELECTINTOsyntaxisasfollows:SELECT[DISTICT
4、ALL]{*
5、
6、column[,column,]}...INTO(variable[,variable,...)
7、recordFROM{table
8、(sub-query)}[alias]WHERE............InPL/SQL,theSELECTstatementreturnsonlyonerowofdata.Ifmorethanonelineofdataisused,explicitcursorsareused(forthediscussionofthecursor,whichwewillfollowlater),andintheINTOclausewehavevariablesthat
9、arethesamenumberofcolumnsasintheSELECTclause.TheINTOclausecanalsobearecordvariable.%TYPEpropertyInPL/SQL,youcandeclarevariablesandconstantsasbuilt-inoruser-defineddatatypestorefertoacolumnname,whileinheritinghisdatatypeandsize.Thisdynamicassignmentmethodisveryuseful,suchaschangingthedatatypeand
10、sizeofcolumnsreferencedbyvariables.Ifyouuse%TYPE,youdon'thavetochangethecode,oryou'llhavetochangethecode.Cases:V_empnoSCOTT.EMP.EMPNO%TYPE;V_salaryEMP.SALARY%TYPE;Youcanuse%TYPEnotonlyforcolumnnames,butalsoforvariables,cursors,records,ordeclaredconstants,using%TYPE.Thisisveryusefulforvariablest
11、hatdefinethesamedatatype.DELCAREV_ANUMBER(5):=10;V_BV_A%TYPE:=15;V_CV_A%TYPE;BEGINDBMS_OUTPUT.PUT_LINE('V_A='
12、
13、V_A
14、
15、'V_B='
16、
17、V_B
18、
19、'V_C='
20、
21、V_C);ENDSQL>/V_A=10V_B=15V_C=PL/SQLproceduresuccessfullycompleted.SQL>OtherDMLstatementsTheDM
