欢迎来到天天文库
浏览记录
ID:48088393
大小:244.00 KB
页数:42页
时间:2020-01-14
《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
此文档下载收益归作者所有