PL-SQL(存储过程培训).ppt

《PL-SQL(存储过程培训).ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、ProgramminginOraclewithPL/SQLProceduralLanguageExtensiontoSQLPL/SQLAllowsusinggeneralprogrammingtoolswithSQL,forexample:loops,conditions,functions,etc.ThisallowsalotmorefreedomthangeneralSQL,andislighter-weightthanJDBC.WewritePL/SQLcodeinaregularfile,forexamplePL.sql,andloaditw

2、ith@PLinthesqlplusconsole.PL/SQLBlocksPL/SQLcodeisbuiltofBlocks,withauniquestructure.TherearetwotypesofblocksinPL/SQL:AnonymousBlocks:havenoname(likescripts)canbewrittenandexecutedimmediatelyinSQLPLUScanbeusedinatrigger2.NamedBlocks:ProceduresFunctionsAnonymousBlockStructure:DEC

3、LARE(optional)/*Hereyoudeclarethevariablesyouwilluseinthisblock*/BEGIN(mandatory)/*Hereyoudefinetheexecutablestatements(whattheblockDOES!)*/EXCEPTION(optional)/*Hereyoudefinetheactionsthattakeplaceifanexceptionisthrownduringtherunofthisblock*/END;(mandatory)/Alwaysputanewlinewit

4、honlya/attheendofablock!(ThistellsOracletoruntheblock)Acorrectcompletionofablockwillgeneratethefollowingmessage:PL/SQLproceduresuccessfullycompletedDECLARESyntaxExamplesidentifier[CONSTANT]datatype[NOTNULL][:=

5、DEFAULTexpr];DeclarebirthdayDATE;ageNUMBER(2)NOTNULL:=27;nameVARCHAR2

6、(13):='Levi';magicCONSTANTNUMBER:=77;validBOOLEANNOTNULL:=TRUE;NoticethatPL/SQLincludesallSQLtypes,andmore…DeclaringVariableswiththe%TYPEAttributeExamplesDECLAREsnameSailors.sname%TYPE;fav_boatVARCHAR2(30);my_fav_boatfav_boat%TYPE:='Pinta';...AccessingcolumnsnameintableSailorsA

7、ccessinganothervariableDeclaringVariableswiththe%ROWTYPEAttributeDeclareavariablewiththetypeofaROWofatable.Andhowdoweaccessthefieldsinreserves_record?reserves_recordReserves%ROWTYPE;reserves_record.sid:=9;Reserves_record.bid:=877;AccessingtableReservesCreatingaPL/SQLRecordAreco

8、rdisatypeofvariablewhichwecandefine(like‘struct’inCor‘object’inJava)DECLARETYPEsailor_record_typeISRECORD(snameVARCHAR2(10),sidVARCHAR2(9),ageNUMBER(3),ratingNUMBER(3));sailor_recordsailor_record_type;...BEGINSailor_record.sname:=‘peter’;Sailor_record.age:=45;…CreatingaCursorWec

9、reateaCursorwhenwewanttogooveraresultofaquery(l