数据库系统英第三版课件衷宜2011版 server-environment(9).ppt

《数据库系统英第三版课件衷宜2011版 server-environment(9).ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、1Chapter9SQLinaServerEnvironment第9章服务器环境中的SQL2SQLinRealProgramsWehaveseenonlyhowSQLisusedatthegenericqueryinterface---anenvironmentwherewesitataterminalandaskqueriesofadatabase.Realityisalmostalwaysdifferent:conventionalprogramsinteractingwithSQL.3OptionsCodeinaspecializedlan

2、guageisstoredinthedatabaseitself(e.g.,PSM,PL/SQL).SQLstatementsareembeddedinahostlanguage(e.g.,C).Connectiontoolsareusedtoallowaconventionallanguagetoaccessadatabase(e.g.,CLI,JDBC,PHP/DB).4StoredProceduresPSM,or“persistentstoredmodules,”allowsustostoreproceduresasdatabaseschem

3、aelements.PSM=amixtureofconventionalstatements(if,while,etc.)andSQL.LetsusdothingswecannotdoinSQLalone.5BasicPSMFormCREATEPROCEDURE();Functionalternative:CREATEFUNCTION()RETURNS6ParametersinPSMUnl

4、iketheusualname-typepairsinlanguageslikeC,PSMusesmode-name-typetriples,wherethemodecanbe:IN=procedureusesvalue,doesnotchangevalue.OUT=procedurechanges,doesnotuse.INOUT=both.7Example:StoredProcedureLet’swriteaprocedurethattakestwoargumentseandn,andaddsatupletoSalesman(empid,nam

5、e,deptid)thathasempid=e,name=n,anddeptid=1.8TheProcedureCREATEPROCEDUREInsertToD(INeCHAR(10),INnCHAR(20))INSERTINTOSalesmanVALUES(e,n,1);Parametersarebothread-only,notchangedThebody---asingleinsertion9InvokingProceduresUseSQL/PSMstatementCALL,withthenameofthedesiredprocedurean

6、darguments.Example:CALLInsertToD(’A0043’,’Zhangsan’);FunctionsusedinSQLexpressionswhereveravalueoftheirreturntypeisappropriate.10KindsofPSMstatements–(1)RETURNsetsthereturnvalueofafunction.UnlikeC,etc.,RETURNdoesnotterminatefunctionexecution.DECLAREused

7、todeclarelocalvariables.BEGIN...ENDforgroupsofstatements.Separatestatementsbysemicolons.11KindsofPSMStatements–(2)Assignmentstatements:SET=;Example:SETn=’Mary’;Statementlabels:giveastatementalabelbyprefixinganameandacolon.12IFStatementsSimplestform:IF

8、dition>THENENDIF;AddELSEifdesire