下载

0下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 chapter7存储过程.sql(Chapter7 stored procedure&#…

chapter7存储过程.sql(Chapter7 stored procedure.Sql).doc

chapter7存储过程.sql(Chapter7 s…

其实我不快活
2018-02-05 0人阅读 举报 0 0 0 暂无简介

简介:本文档为《chapter7存储过程.sql(Chapter7 stored procedure.Sql)doc》,可适用于社会民生领域

chapter存储过程sql(ChapterstoredprocedureSql)chapter存储过程sql(ChapterstoredprocedureSql)*hours**case:executionsystemstoredprocedureusedtoobtaintherelevantinformation*databaseobjectsTheuseofPurpose:commonlyusedsystemstoredproceduresEXECspdatabases:liststhecurrentsystemdatabaseEXECsprenamedb'MyBank','Bank'changedthenameofthedatabase(singleuseraccess)USEMySchoolGOAlistofqueryobjectsinthecurrentdatabaseEXECsptablesEXECspcolumnsStudentseelistofinformationinStudentEXECsphelpStudentalltheinformationseetableStudentEXECsphelpconstraintStudentseetableStudentconstraintsEXECsphelpindexResultseetableResultindexTheEXECsphelptext'vwstudentresultInfo'statementtextViewViewEXECspstoredproceduresreturnsalistofstoredproceduresinthecurrentdatabase*case:theimplementationoftheextendedstoredproceduretocreateafolder,seetheoperatingsystemfile*USEmasterGO*bankDBtocreatethedatabase,storedintheD:bank*requirementsEXECxpcmdshell'mkdirD:bank',NOOUTPUTcreatethefolderD:bankCreatedatabasebankDBIFexists(SELECT*FROMsysdatabasesWHEREname='bankDB')DROPDATABASEbankDBGOCREATEDATABASEbankDBON(NAME='bankDBdata',FILENAME='D:bankbankDBdatamdf',SIZE=mb,FILEGROWTH=)LOGON(NAME='bankDBlog',FILENAME='D:bankbankDBlogldf',SIZE=mb,FILEGROWTH=)GOTheuseofadvancedstorageprocessopenconfigurationSpconfigure'showadvancedoptions',GORECONFIGUREGOEXECxpcmdshell'dirD:bank'viewfileGO*case:information*usesastoredproceduretoviewtheStudenttableUSEMySchoolGOEXECspcolumnsStudentseelistofinformationinStudentEXECsphelpStudentalltheinformationseetableStudentEXECsphelpconstraintStudentseetableStudentconstraints*hours**case:UsingstoredprocedurestoquerytheaveragescoreofthelastJavaLogictestandthelistofstudentsfailingtopasstheexam*IFEXISTS(SELECT*FROMsysobjectsWHEREname='uspGetAverageResult'),detectthepresenceofDROPPROCEDUREuspGetAverageResultGOCREATEPROCEDUREuspGetAverageResulttocreateastoredprocedureASDECLAREsubjectNointthecoursenumberDECLAREdatedatetimethelastexamtimeSELECTsubjectNo=subjectNoFROMSubjectWHERESubjectName='javalogic'SELECTdate=max(ExamDate)FROMResultINNERJOINSubjectONResultSubjectNo=SubjectSubjectNoWHERESubjectName=subjectNoDECLAREavgdecimal(,)averagevariableSELECTavg=AVG(StudentResult)FROMResultWHEREExamDate=dateandSubjectNo=subjectNoPRINT'average:'CONVERT(varchar(),avg)IF(avg>)PRINT'examresults:excellent'ELSEPRINT'testscores:poor'PRINT''InPRINT'didnotpassthisexamstudents':SELECTStudentName,StudentStudentNoStudentResult,FROMStudentINNERJOINResultONStudentStudentNo=ResultStudentNoWHEREStudentResult<ANDExamDate=dateandSubjectNo=subjectNoGO*callsthestoredprocedureisexecuted*EXECuspGetAverageResultstoredprocedurecallsyntax:EXECprocedurenameparameter*case:theuseofstoredproceduretoqueryeachsemestercourses**detect:storedproceduresstoredinthesystemtablesinsysobjects*IFEXISTS(SELECT*FROMsysobjectsWHEREname='uspgradesubject')DROPPROCEDUREuspgradesubjectGOCreateastoredprocedure**CREATEPROCEDUREuspgradesubjectASSELECTGradeName,SubjectNameClassHour,FROMGradeINNERJOINSubjectONGradeGradeId=SubjectGradeIdORDERBYSubjectGradeId,SubjectNoGO*callsthestoredprocedureisexecuted*EXECuspgradesubjectstoredprocedurecallsyntax:EXECprocedurenameparameter*case:theuseofstoredproceduresaccordingtotheinputquerystudentinformationobtained*passinarecentJavatestfailedIFEXISTS(SELECT*FROMsysobjectsWHEREname='uspunpass'),detectthepresenceofDROPPROCEDUREuspunpassGOCREATEPROCEDUREuspunpasstocreateastoredproceduresubNamevarchar(),scoreINTASDECLAREsubjectNointcoursenumberDECLAREdatedatetimethelatestexamtimeSELECTsubjectNo=subjectNoFROMSubjectWHERESubjectName=subNameSELECTdate=max(ExamDate)FROMResultINNERJOINSubjectONResultSubjectNo=SubjectSubjectNoWHEREResultSubjectNo=subjectNoPRINT'passlineis:'CAST(scoreASvarchar())''PRINT''PRINT'inarecent'subName'examinationdidnotmeetthestudents'scores:SELECTStudentName,StudentStudentNoStudentResult,FROMStudentINNERJOINResultONStudentStudentNo=ResultStudentNoWHEREStudentResult<scoreANDExamDate=dateandSubjectNo=subjectNoGOEXECuspunpass'C#OOP',storedprocedurecallsyntax:EXECprocedurenameparameterGOEXECuspunpasssubName='C#OOP',score=*hours**case:queryspecifiesthesemestercoursenameusingstoredprocedureIfyoudonotspecifythetermname,theoutputinformationinthecourse*allsemesterIFEXISTS(SELECT*FROMsysobjectsWHEREname='uspquerysubject')TestforexistenceDROPPROCEDUREuspquerysubjectGOCREATEPROCEDUREuspquerysubjectGradeNameVARCHAR()=ASIFGradeNameISSELECTGradeName,SubjectNameClassHour,FROMGradeLEFTJOINSubjectONGradeGradeId=SubjectGradeIdUNION选择gradename'和(课时)级左连接问题在gradeid=subjectgradeid等级。集团通过gradename其他的选择gradenamesubjectname年级学科课时的加入左在gradeid=subjectgradeid等级。在gradename=gradename联盟选择gradename'和(课时)级左连接问题在gradeid=subjectgradeid等级。在gradename=gradename集团通过gradename去执行uspquerysubject“S”调用存储过程的语法:exec过程名参数去*案例:利用存储过程根据输入的及格分数查询获得本次考试未通过的学员信息返回未通过学员的人数*如果存在(SELECT*FROMsysobjectsWHEREname=“uspquerynum”)uspquerynum下降过程去**创建存储过程创建程序uspquerynum“unpassnumINT输出输出参数未通过人数“totalnumINT输出输出参数参加考试总人数“subjectnamenchar()输入参数课程名称“通过int=输入参数:及格线作为声明的日期日期最近考试时间声明subjectno内景课程编号选择“日期=max(examdate)从结果内加入主体在subjectno=subjectsubjectno结果。在subjectname=subjectname选择“subjectno=subjectno从主体那里subjectname=subjectname打印subjectname课程在转换(varchar()“日)考试的及格线是投(通为varchar())未通过的学员信息**打印未通过学员的信息如下”选择的结果。StudentNostudentname学生成绩的结果内部联接resultstudentno=studentstudentno学生在examdate=“日期和subjectno=subjectno和学生成绩<通获得未通过的学员人数**选择“unpassnum=数(*)的结果在examdate=“日期和subjectno=subjectno和学生成绩<通获得参加考试的学员总人数**选择“totalnum=数(*)的结果在examdate=“日期和subjectno=subjectno去执行存储过程获得输出参数值并计算及格率声明unpassnum内景定义变量用于存放调用存储过程时返回的结果声明totalnum内景定义变量用于存放调用存储过程时返回的结果执行uspquerynumtotalnumunpassnum输出输出'java逻辑调用时也带输出关键字机试及格线默认为**,声明subjectnamevarchar()声明“通过int”执行uspquerynumtotalnumunpassnum输出输出通=默认“subjectname='java逻辑”execUSPqueryaunpassnumoutput,totalnumoutputpass=default,'Javalogic"ExecUSPqueryaunpassnumoutput,totalnumoutput**Declareratiodecimal()Setratio=convert(decimal,(totalnumtotalnumunpassnum))*Print'未通过人数:'cast(unpassnumthevarchar())'人,及格率是'cast(ratiothevarchar())''Ifunpassnum>BeginIfratio>及格分数线不需下调'print'Else及格分数线应下调'print'EndElse恭喜本次考试成绩优良'print'Go*案例:利用存储过程查询指定学期开设的课程信息,返回课程数、课时数*Ifexists(select*fromsysobjectswherename='USPquerysubject')DropprocedureUSPquerysubjectGoCreateprocedureUSPquerysubjectcoursenumintoutput,输出参数hournumintoutput,输出参数gradenamevarchar()TheIflen(gradename)=Begin学期名称不能为空'print'ReturnEndPrint'(学期课程信息如下―'Selectgradename,subjectname,classhourfromgradeleftjoinsubjectOngradegradeid=subjectgradeidWheregradename=gradenameSelectcoursenum=count(),hournum=sum(classhour)fromgridinnerjoinsubjectOngradegradeid=subjectgradeidWheregradename=gradenameGoDeclaresumint定义变量,用于存放调用存储过程时返回的课程数Declarehoursint定义变量,用于存放调用存储过程时返回的课时数Declaresubnamevarchar()Setsubname='S'ExecUSPquerysubjectsumoutput,hoursoutput,subname调用时也带output关键字Selectsubname'学期名称'cast(sumthevarchar())'课程数目'cast(hoursthevarchar())'总课时'Go*学时**案例:存储过程中判断输入参数值如果传入的及格线不在~之间时,弹出错误警告,终止存储过程的执行使用raiserror语句*Ifexists(select*fromsysobjectswherename='USPstu')DropprocedureUSPstuGo*创建存储过程*CreateprocedureUSPstuunpassnumintoutput,输出参数:未通过人数totalnumintoutput,输出参数,参加考试总人数subjectnamenchar()输入参数:课程名称passint=输入参数:及格线TheDeclaredatedatetime最近考试时间Declaresubjectnoint课程编号Selectdate=max(examdate)fromresultinnerjoinsubjectOnresultsubjectno=subjectsubjectnoWheresubjectname=subjectname选择“subjectno=subjectno从主体那里subjectname=subjectname如果(不在到之间)开始RAISERROR(“及格线错误请指定,之间的分数统计中断退出,)回报立即返回退出存储过程结束打印subjectname课程在转换(varchar()“日)考试的及格线是投(通为varchar())打印”该课程最近一次考试没有通过的学员成绩:'打印'选择的结果。StudentNostudentname学生成绩的结果内部联接resultstudentno=studentstudentno学生在examdate=“日期和subjectno=subjectno和学生成绩<通获得未通过的学员人数**选择“unpassnum=数(*)的结果在examdate=“日期和subjectno=subjectno和学生成绩<通获得参加考试的学员总人数**选择“totalnum=数(*)的结果在examdate=“日期和subjectno=subjectno去*调用存储过程假定java逻辑课程最近一次考试的试题偏容易及格线定为分*声明unpassnum内景定义变量用于存放调用存储过程时返回的结果声明totalnum内景定义变量用于存放调用存储过程时返回的结果执行uspstutotalnumunpassnum输出输出'java逻辑”调用时也带输出关键字机试及格线默认为声明设置错误如果错误>开始打印错误号:转换(varchar()“呃)回归退出批处理后续语句不再执行结束声明比十进制(,)集比率=转换(十进制(totalnumUnPassNum))totalnum*打印未通过人数:投(unpassnum为varchar())“人及格率是投(比varchar())“”如果“unpassnum>开始如果比>打印及格分数线不需下调”其他的打印及格分数线应下调”结束其他的打印恭喜本次考试成绩优良”~去*案例:增加新课程记录*如果存在(SELECT*FROMsysobjectsWHEREname=“uspinsertsubject”)uspinsertsubject下降过程去创建程序uspinsertsubject“subjectnoINT输出“gradeidINT输出GradeNamevarchar()“subjectnamevarchar()”一节课时int=作为声明错误int设置“错误=如果(Len(RTrim(subjectname))=len(RTrim(gradename))=)返回事务的开始如果不存在(SELECT*FROM级gradename=gradename)开始插入级(gradename)值(gradename)设置“错误=“错误误差选择“gradeid=身份结束其他的选择“gradeid=gradeid年级在gradename=gradename插入对象(subjectnameClassHourgradeid)值(subjectname课时gradeid)设置“错误=“错误误差选择“subjectno=身份如果(错误>)开始回滚事务返回结束其他的开始提交事务返回结束去调用存储过程声明subjectnoint声明gradeidint声明GradeNamevarchar()声明subjectnamevarchar()声明课时的int声明int设置“gradename=“Y”设置“subjectname=“Linux”设置“课时=执行“RT=uspinsertsubjectgradeidsubjectno输出输出gradenamesubjectname课时如果(=)开始打印增加课程”“”subjectname记录成功”打印学期编号是投(gradeid为varchar())”“”gradename学期名称是打印课程编号是投(subjectno为varchar())”“”subjectname课程名称是结束否则如果(=)打印增加课程记录失败~”其他的打印学期名称或课程名称不能为空请重新执行~”去*案例:利用存储过程和事务实现删除指定学员记录的功能*实用性较低暂不用如果存在(SELECT*FROMsysobjectsWHEREname=“uspdeletestudent”)uspdeletestudent下降过程去创建程序uspdeletestudent“成功=个输出点“studentnoint作为声明errorsumint设置“errorsum=如果存在(选择*从学生那里studentno=studentno)开始事务的开始删除从结果在studentno=studentno设置“errorsum=errorsum误差删除从学生那里studentno=studentno设置“errorsum=errorsum误差如果“errorsum<>如果有错误开始回滚事务选择“成功=结束其他的开始提交事务选择“成功=结束结束其他的开始RAISERROR(“该学员编号不存在~',)结束去调用存储过程声明成功点执行uspdeletestudent成功输出如果(成功=)打印删除学生记录成功”其他的打印删除学生记录失败”

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

评分:

/20

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利