实现存储过程实例.ppt

目标在这一课里 你将学习 创建存储过程执行存储过程向存储过程传递参数修改存储过程从存储过程返回值从存储过程返回多个值从另一个存储过程调用一个过程重编译存储过程撤消存储过程 开始存储过程是T SQL语句和控制流语言的一个集合或批量 它在一个名称下存储 按独立单元方式执行 存储过程的好处提高性能减轻网络拥塞一致性较好改善安全机制 开始 续 过程的类型用户定义的系统临时远程扩展 稍等一下 使用存储过程有什么好处 9 D 1提高批量语句的执行速度包含招聘代理的名称 地址 城市 邮政编码 电话号码和传真号码的列表是经常需要的 最近 有通知说由于网络拥塞 产生这份报告会延迟 另外 人力资源部门的职员 在查询信息的过程中犯了错误 建议并实现一个解决方案 任务单确定用于解决上述问题的对象在纸上编写存储过程在数据库中创建存储过程在数据库中检查过程存在执行过程 确定用于解决上述问题的对象结果 使用一个存储过程以减少网络通信量和由HR人员造成的错误 在纸上编写存储过程CREATEPROCEDURE语句语法CREATEPROCEDUREproc nameASBEGINsql statement1sql statement2END 在纸上编写存储过程 续 结果 需要创建存储过程的数据库是Recruitment 过程的类型是用户定义类型存储过程的名称是prcPrintRecruitmentAgencyList 在数据库中创建存储过程动作 在QueryAnalyzer窗口中 输入 CREATEPROCEDUREprcPrintRecruitmentAgencyListASBEGINPRINT ListofRecruitmentAgencies SELECTcName vAddress cCity cZip cPhone cFaxFROMRecruitmentAgenciesEND按F5执行语句 在数据库中检查过程的存在用sp helptextCommand检查数据库中过程的存在语法sp helptextproc name动作在QueryAnalyzer窗口中 输入 sp helptextprcPrintRecruitmentAgencyList检查结果 输出必须是你编写创建过程的代码 执行过程用EXECUTEPROCEDURE语句执行此存储过程语法EXECUTEproc name或EXECproc name或proc name 执行过程 续 动作 在QueryAnalyzer窗口中 输入 EXECUTEprcPrintRecruitmentAgencyListorEXECprcPrintRecruitmentAgencyListorprcPrintRecruitmentAgencyList按F5执行命令 稍等一下 为得到候选人列表和他们的招聘代理的查询是 SELECT CandidateName vFirstName RecruitmentAgency cNameFROMExternalCandidateJOINRecruitmentAgenciesONExternalCAndidate cAgencyCode RecruitmentAgencies cAgencyCode同理创建存储过程 9 D 2创建类属存储过程某城市中合同招聘人员的信息是经常需要的 需要的城市材料随着时间的变化而变化 创建一个存储过程 它将为指定城市产生信息 任务单识别一种编写类属过程的方法 该过程产生可变输出的结果编写创建过程的语句在数据库中创建存储过程检查数据库中过程的存在准备带有存在和不存在值的测试用例执行有存在值的过程执行没有存在值的过程 识别一种编写类属过程的方法 该过程产生可变输入的结果参数参数是查询中的一个占位符或是一个存储过程 只要查询或存储过程被执行该过程就接受用户定义的值参数的类型输入参数输出参数结果 因为城市名将由用户提供 那么使用输入参数 编写创建过程的语句结果 变量名是 cCity变量的数据类型是大小为15的char 所以 需要声明的变量如下 cCitychar 15 在数据库中创建存储过程动作 在QueryAnalyzer窗口中 输入 CREATEPROCprcListContractRecruiter cCitychar 15 ASBEGINPRINT ListofContractRecruiters SELECTcName cCity cZip cPhoneFROMContractRecruiterWHEREcCity cCityEND按F5执行查询 检查数据库中过程的存在动作 在QueryAnalyzer窗口中 输入 sp helptextprcListContractRecruiter检查结果准备带有存在和不存在值的测试用例动作 对于存在于ContractRecuiter表中 Alexandria 的城市进行测试对于不存在于ContractRecuiter表中 Boston 的城市进行测试 执行有存在值的过程动作 在QueryAnalyzer窗口中 输入 prcListContractRecruiterAlexandria按F5执行过程执行有不存在值的过程动作 在QueryAnalyzer窗口中 输入 prcListContractRecruiterBoston按F5执行过程 9 D 3修改存储过程需要某城市招聘代理的材料 需要数据的城市随着时间的变化而变化 修改现存的prcPrintRecruitmentAgencyList过程以满足需求 而不是创建一个新过程 在此新过程中 如果不传值给过程 它将显示 Usage prcPrintRecruitmentAgencyList 并结束执行 任务单确定一种修改现有过程方法编写修改过程的语句验证过程已被修改执行过程 确定一种修改现有过程方法用ALTERPROCEDURE语句来修改现有过程 TheDefault参数假如没有值传递给过程 你可以使用缺省参数向存储过程传值 作为缺省值传递的值必须为常量或NULL 结果 使用ALTERPROCEDURE语句修改此过程 确定一种修改现有过程方法 续 使用NULL作为过程的缺省参数 如果没有值传递给过程参数 那么参数将包含NULL值 如果参数值为NULL 过程将显示一条消息 解释过程的用法 编写修改过程的语句动作 在QueryAnalyzer窗口中 输入 ALTERPROCEDUREprcPrintRecruitmentAgencyList citychar 15 NULL 编写修改过程的语句 续 ASBEGINIF cityisnullBEGINPRINT Usage prcPrintRecruitmentAgencyList RETURNENDPRINT ListofRecruitmentAgencies SELECTcName vAddress cZip cPhoneFROMRecruitmentAgenciesWHEREcCity cityEND 编写修改过程的语句 续 按F5执行查询在上述代码中 RETURN语句把控制返回到过程被调用的地方 验证过程已被修改动作 在QueryAnalyzer窗口中 输入 sp helptextprcPrintrecruitmentAgencyList按F5执行查询执行过程动作 在QueryAnalyzer窗口中 输入 EXECprcPrintRecruitmentAgencyListAlexandria按F5执行查询 执行过程 续 假如你不向过程传递任何参数 而输入 prcPrintRecruitmentAgencyList你将得到以下输出 Usage prcPrintRecruitmentAgencyList 9 D 4通知执行成功修改prcPrintRecruitmentAgencyList过程以通知用户它已被成功执行 任务单识别编写将返回值的过程的方法编写过程在数据库中修改过程在数据库中检查过程已被修改执行过程 识别编写将返回值的过程的方法RETURN关键字你使用从存储过程中返回值的RETURN语句语法RETURNvalue结果 RETURN关键字可用于从存储过程中返回值 撤消过程结果 创建存储过程的batch语句如下 ALTERPROCEDUREprcPrintRecruitmentAgencyList citychar 15 ASBEGINIFEXISTS SELECT FROMRecruitmentAgenciesWHEREcCity city 撤消过程 续 BEGINPRINT ListofRecruitmentAgencies SELECTcName vAddress cZip cPhoneFROMRecruitmentAgenciesWHEREcCity cityReturn0ENDELSEBEGINPRINT NoRecordsFoundforgivencity RETURN1ENDEND 在数据库中修改过程动作 在QueryAnalyzer窗口中 输入prcPrintRecruitmentAgencyList按F5执行语句 在数据库中检查过程已被修改动作 在QueryAnalyzer窗口中 输入 sp helptextprcPrintRecruitmentAgencyList按F5执行语句 执行过程动作 在QueryAnalyzer窗口中 输入 DECLARE ReturnValueintEXEC ReturnValue prcPrintRecruitmentAgencyList Alexandria SELECT ReturnValue按F5执行此语句 如果上述语句被执行 prcPrintRecruitmentAgencyList过程的返回值将存储于变量 ReturnValue中 这个值将由SELECT语句来显示 9 D 5由另一个过程来调用一个过程在一份单独的报告中需要有关招聘代理和城市里的合同招聘人员的材料 为此报告创建一个单独过程 它使用现有的过程 任务单识别修改现有过程的方法在数据库中创建过程在数据库中检查过程的存在执行带参数的过程 识别一种方法 来编写将使用现有过程的过程嵌套过程你可以执行或调用来自另一个过程的一个过程结果 创建将调用现有过程的一个新存储过程 在数据库中创建过程动作 在QueryAnalyzer窗口中 输入 CREATEPROCEDUREprcDisplayBoth citychar 15 ASBEGINEXECprcListContractRecruiter cityEXECprcPrintRecruitmentAgencyList cityEND按F5执行语句 在数据库中检查过程的存在动作 在QueryAnalyzer窗口中 输入 sp helptextprcDisplayBoth按F5执行语句执行带参数的过程动作 在QueryAnalyzer窗口中 输入 prcDisplayBoth Alexandria 按F5执行语句 稍等一下 你需要修改一个存储过程 你将使用哪个命令来修改此过程 9 D 6返回多个输出值一份格式化的有关公司提供职位状态的报告将被显示 创建一个过程 prcGetPositionDetail 它将返回职位描述 预算人力资源和给定职位的当前员工的人数 创建另外一个过程称为prcDisplayPositionStatus 它将显示Requisition表的cRequisitionCode vRegion和siNoOfVacancy 过程prcDisplayPositionStatus还应调用prcGetPositionDetail过程 按以下格式显示它关于职位 0002 的返回值 9 D 6返回多个输出值 续 TheStatusforthePosition MaketingManagerBudgetedStrength 100CurrentStrength 83cRequisitionCodevRegionsiNoOfVacancy 000002Texas11 任务单确定一种方法来编写将返回多值的类属过程在数据库中创建过程在数据库中检查过程的存在 执行带参数的过程 确定一种方法来编写将返回多值的类属过程OUTPUT参数 用来从过程返回一个以上的值语法CREATEPROC EDURE procedure name parameterdata type OUTPUT ASsql statement n 结果 使用CREATEPROCEDURE带OUTPUT选项 在数据库中创建过程动作 在QueryAnalyzer窗口中 输入以下语句 CREATEPROCEDUREprcGetPositionDetail Pcodechar 4 Descriptionchar 30 OUTPUT budgetintOUTPUT CurrentStrengthintOUTPUTASBEGINIFEXISTS SELECT FROMPositionWHEREcPositionCode PCode 在数据库中创建过程 续 BEGINSELECT Description vDescription budget iBudgetedStrength CurrentStrength iCurrentStrengthFROMPositionWHEREcPositionCode PcodeRETURN0ENDELSERETURN1END按F5执行语句 在数据库中创建过程 续 在QueryAnalyzer窗口中 输入以下语句 CREATEPROCEDUREprcDisplayPositionStatus PCodechar 4 ASBEGINDECLARE Descriptionchar 30 DECLARE budgetintDECLARE CurrentStrengthintDECLARE ReturnValueintEXEC ReturnValue prcGetPositionDetail Pcode Descriptionoutput Budgetoutput CurrentStrengthoutput 在数据库中创建过程 续 IF ReturnValue 0 BEGINPRINT TheStatusforthePosition DescriptionPRINT BudgetedStrength CONVERT char 30 budget PRINT CurrentStrength CONVERT char 30 CurrentStrength SELECTcRequisitionCode vRegion siNoOfVacancyFROMRequisitionWHEREcPositionCode Pcode 在数据库中创建过程 续 ENDELSEPRINT Norecordsforthegivenpositioncode END按F5执行语句 在数据库中检查过程的存在在QueryAnalyzer窗口中 输入并执行下面语句 sp helptextprcGetPositionDetailgosp helptextprcDisplayPositionStatusgo 执行带参数的过程动作 在QueryAnalyzer窗口中 输入 prcDisplayPositionstatus 0