实现存储过程实例

1、NIITSQL/Lesson 9/Slide 1 of 61实现存储过程目标目标在这一课里，你将学习:*创建存储过程*执行存储过程*向存储过程传递参数*修改存储过程*从存储过程返回值*从存储过程返回多个值*从另一个存储过程调用一个过程*重编译存储过程*撤消存储过程NIITSQL/Lesson 9/Slide 2 of 61实现存储过程开始开始*存储过程是T-SQL语句和控制流语言的一个集合或批量，它在一个名称下存储，按独立单元方式执行。*存储过程的好处3 提高性能3 减轻网络拥塞3 一致性较好3 改善安全机制NIITSQL/Lesson 9/Slide 3 of 61实现存储过程开始开始(续续

2、)*过程的类型3 用户定义的3 系统3 临时3 远程3 扩展NIITSQL/Lesson 9/Slide 4 of 61实现存储过程稍等一下稍等一下*使用存储过程有什么好处?NIITSQL/Lesson 9/Slide 5 of 61实现存储过程9.D.1提高批量语句的执行速度提高批量语句的执行速度*包含招聘代理的名称、地址、城市、邮政编码、电话号码和传真号码的列表是经常需要的。最近，有通知说由于网络拥塞，产生这份报告会延迟。另外，人力资源部门的职员，在查询信息的过程中犯了错误。建议并实现一个解决方案。NIITSQL/Lesson 9/Slide 6 of 61实现存储过程任务单任务单*确定用

3、于解决上述问题的对象*在纸上编写存储过程*在数据库中创建存储过程*在数据库中检查过程存在*执行过程NIITSQL/Lesson 9/Slide 7 of 61实现存储过程确定用于解决上述问题的对象确定用于解决上述问题的对象 *结果：3 使用一个存储过程以减少网络通信量和由HR人员造成的错误NIITSQL/Lesson 9/Slide 8 of 61实现存储过程在纸上编写存储过程在纸上编写存储过程* CREATE PROCEDURE语句3 语法CREATE PROCEDURE proc_nameASBEGINsql_statement1sql_statement2 ENDNIITSQL/Less

4、on 9/Slide 9 of 61实现存储过程在纸上编写存储过程在纸上编写存储过程(续续)*结果：3 需要创建存储过程的数据库是Recruitment.3 过程的类型是用户定义类型3 存储过程的名称是prcPrintRecruitmentAgencyList NIITSQL/Lesson 9/Slide 10 of 61实现存储过程在数据库中创建存储过程在数据库中创建存储过程*动作：3 在Query Analyzer窗口中，输入：CREATE PROCEDURE prcPrintRecruitmentAgencyListASBEGINPRINT List of Recruitment Age

5、nciesSELECT cName, vAddress, cCity, cZip, cPhone, cFax FROM RecruitmentAgenciesEND 3 按F5 执行语句NIITSQL/Lesson 9/Slide 11 of 61实现存储过程在数据库中检查过程的存在在数据库中检查过程的存在*用 sp_helptext Command 检查数据库中过程的存在*语法 sp_helptext proc_name*动作3在Query Analyzer窗口中，输入:sp_helptext prcPrintRecruitmentAgencyList3检查结果。输出必须是你编写创建过程的代

6、码NIITSQL/Lesson 9/Slide 12 of 61实现存储过程执行过程执行过程* 用 EXECUTE PROCEDURE 语句执行此存储过程* 语法 EXECUTE proc_name 或 EXEC proc_name 或 proc_nameNIITSQL/Lesson 9/Slide 13 of 61实现存储过程执行过程执行过程(续续)*动作：3 在Query Analyzer窗口中，输入：EXECUTE prcPrintRecruitmentAgencyListorEXEC prcPrintRecruitmentAgencyListor prcPrintRecruitment

7、AgencyList 3 按F5 执行命令NIITSQL/Lesson 9/Slide 14 of 61实现存储过程稍等一下稍等一下 *为得到候选人列表和他们的招聘代理的查询是：SELECT Candidate Name = vFirstName , Recruitment Agency = cName FROM ExternalCandidate JOIN RecruitmentAgencies ON ExternalCAndidate.cAgencyCode = RecruitmentAgencies.cAgencyCode同理创建存储过程。NIITSQL/Lesson 9/Slide 1

8、5 of 61实现存储过程9.D.2创建类属存储过程创建类属存储过程*某城市中合同招聘人员的信息是经常需要的。需要的城市材料随着时间的变化而变化。创建一个存储过程，它将为指定城市产生信息NIITSQL/Lesson 9/Slide 16 of 61实现存储过程任务单任务单*识别一种编写类属过程的方法，该过程产生可变输出的结果*编写创建过程的语句*在数据库中创建存储过程*检查数据库中过程的存在*准备带有存在和不存在值的测试用例*执行有存在值的过程*执行没有存在值的过程NIITSQL/Lesson 9/Slide 17 of 61实现存储过程识别一种编写类属过程的方法，该过程产生可变输入的结识别一

9、种编写类属过程的方法，该过程产生可变输入的结果果*参数3 参数是查询中的一个占位符或是一个存储过程，只要查询或存储过程被执行该过程就接受用户定义的值*参数的类型3 输入参数3 输出参数*结果：3 因为城市名将由用户提供，那么使用输入参数NIITSQL/Lesson 9/Slide 18 of 61实现存储过程编写创建过程的语句编写创建过程的语句*结果：3 变量名是cCity3 变量的数据类型是大小为15的char。所以，需要声明的变量如下： cCity char(15)NIITSQL/Lesson 9/Slide 19 of 61实现存储过程在数据库中创建存储过程在数据库中创建存储过程*动作：

10、3 在Query Analyzer窗口中，输入：CREATE PROC prcListContractRecruiter cCity char(15)ASBEGIN PRINT List of Contract Recruiters SELECT cName,cCity,cZip,cPhone FROM ContractRecruiter WHERE cCity = cCityEND3 按F5执行查询NIITSQL/Lesson 9/Slide 20 of 61实现存储过程检查数据库中过程的存在检查数据库中过程的存在*动作：3 在Query Analyzer窗口中，输入：sp_helptext

11、 prcListContractRecruiter3 检查结果准备带有存在和不存在值的测试用例准备带有存在和不存在值的测试用例*动作：3 对于存在于ContractRecuiter 表中： Alexandria.的城市进行测试3 对于不存在于ContractRecuiter 表中： Boston. 的城市进行测试NIITSQL/Lesson 9/Slide 21 of 61实现存储过程执行有存在值的过程执行有存在值的过程*动作：3 在Query Analyzer窗口中，输入： prcListContractRecruiter Alexandria3 按F5 执行过程执行有不存在值的过程执行有不

12、存在值的过程*动作：3 在Query Analyzer窗口中，输入：prcListContractRecruiter Boston 3 按F5执行过程NIITSQL/Lesson 9/Slide 22 of 61实现存储过程9.D.3修改存储过程修改存储过程*需要某城市招聘代理的材料。需要数据的城市随着时间的变化而变化。修改现存的prcPrintRecruitmentAgencyList过程以满足需求，而不是创建一个新过程。在此新过程中，如果不传值给过程，它将显示Usage: prcPrintRecruitmentAgencyList 并结束执行。NIITSQL/Lesson 9/Slide

13、23 of 61实现存储过程任务单任务单*确定一种修改现有过程方法*编写修改过程的语句*验证过程已被修改*执行过程NIITSQL/Lesson 9/Slide 24 of 61实现存储过程确定一种修改现有过程方法确定一种修改现有过程方法*用ALTER PROCEDURE语句来修改现有过程。*The Default参数3 假如没有值传递给过程，你可以使用缺省参数向存储过程传值。3 作为缺省值传递的值必须为常量或NULL。*结果：3 使用ALTER PROCEDURE语句修改此过程。NIITSQL/Lesson 9/Slide 25 of 61实现存储过程确定一种修改现有过程方法确定一种修改现有过

14、程方法(续续)3 使用NULL作为过程的缺省参数。如果没有值传递给过程参数，那么参数将包含NULL值。如果参数值为NULL，过程将显示一条消息，解释过程的用法。编写修改过程的语句编写修改过程的语句*动作：3 在Query Analyzer窗口中，输入：ALTER PROCEDURE prcPrintRecruitmentAgencyList city char(15)=NULLNIITSQL/Lesson 9/Slide 26 of 61实现存储过程编写修改过程的语句编写修改过程的语句(续续)AS BEGINIF city is nullBEGINPRINT Usage: prcPrintRe

15、cruitmentAgencyList RETURNENDPRINT List of Recruitment AgenciesSELECT cName,vAddress,cZip,cPhone FROM RecruitmentAgencies WHERE cCity = cityENDNIITSQL/Lesson 9/Slide 27 of 61实现存储过程编写修改过程的语句编写修改过程的语句(续续)3 按F5执行查询3 在上述代码中，RETURN语句把控制返回到过程被调用的地方NIITSQL/Lesson 9/Slide 28 of 61实现存储过程验证过程已被修改验证过程已被修改*动作：3

16、 在Query Analyzer窗口中，输入： sp_helptext prcPrintrecruitmentAgencyList3 按F5执行查询执行过程执行过程*动作：3 在Query Analyzer窗口中，输入：EXEC prcPrintRecruitmentAgencyList Alexandria3 按F5执行查询NIITSQL/Lesson 9/Slide 29 of 61实现存储过程执行过程执行过程(续续)3 假如你不向过程传递任何参数，而输入： prcPrintRecruitmentAgencyList 你将得到以下输出： Usage: prcPrintRecruitment

17、AgencyList NIITSQL/Lesson 9/Slide 30 of 61实现存储过程9.D.4通知执行成功通知执行成功*修改prcPrintRecruitmentAgencyList过程以通知用户它已被成功执行NIITSQL/Lesson 9/Slide 31 of 61实现存储过程任务单任务单*识别编写将返回值的过程的方法*编写过程*在数据库中修改过程*在数据库中检查过程已被修改*执行过程NIITSQL/Lesson 9/Slide 32 of 61实现存储过程识别编写将返回值的过程的方法识别编写将返回值的过程的方法* RETURN关键字3 你使用从存储过程中返回值的RETURN

18、语句*语法RETURN value*结果：RETURN 关键字可用于从存储过程中返回值NIITSQL/Lesson 9/Slide 33 of 61实现存储过程撤消过程撤消过程*结果：3 创建存储过程的batch语句如下： ALTER PROCEDURE prcPrintRecruitmentAgencyList city char(15)AS BEGIN IF EXISTS(SELECT * FROM RecruitmentAgencies WHERE cCity=city)NIITSQL/Lesson 9/Slide 34 of 61实现存储过程撤消过程撤消过程(续续)BEGINPRINT

19、 List of Recruitment AgenciesSELECT cName,vAddress,cZip,cPhone FROM RecruitmentAgencies WHERE cCity = cityReturn 0 END ELSE BEGINPRINT No Records Found for given city RETURN 1 ENDENDNIITSQL/Lesson 9/Slide 35 of 61实现存储过程在数据库中修改过程在数据库中修改过程*动作：3 在Query Analyzer窗口中，输入prcPrintRecruitmentAgencyList3 按F5执行

20、语句NIITSQL/Lesson 9/Slide 36 of 61实现存储过程 在数据库中检查过程已被修改在数据库中检查过程已被修改*动作：3 在Query Analyzer窗口中，输入：sp_helptext prcPrintRecruitmentAgencyList3 按F5执行语句NIITSQL/Lesson 9/Slide 37 of 61实现存储过程执行过程执行过程*动作：3 在Query Analyzer窗口中，输入：DECLARE ReturnValue intEXEC ReturnValue = prcPrintRecruitmentAgencyList AlexandriaS

21、ELECT ReturnValue 3 按F5 执行此语句。如果上述语句被执行，prcPrintRecruitmentAgencyList过程的返回值将存储于变量ReturnValue中。这个值将由SELECT语句来显示NIITSQL/Lesson 9/Slide 38 of 61实现存储过程9.D.5由另一个过程来调用一个过程由另一个过程来调用一个过程*在一份单独的报告中需要有关招聘代理和城市里的合同招聘人员的材料。为此报告创建一个单独过程，它使用现有的过程NIITSQL/Lesson 9/Slide 39 of 61实现存储过程任务单任务单*识别修改现有过程的方法*在数据库中创建过程*在数

22、据库中检查过程的存在*执行带参数的过程NIITSQL/Lesson 9/Slide 40 of 61实现存储过程*识别一种方法，来编写将使用现有过程的过程识别一种方法，来编写将使用现有过程的过程*嵌套过程3 你可以执行或调用来自另一个过程的一个过程*结果：3 创建将调用现有过程的一个新存储过程NIITSQL/Lesson 9/Slide 41 of 61实现存储过程在数据库中创建过程在数据库中创建过程*动作：3 在Query Analyzer窗口中，输入：CREATE PROCEDURE prcDisplayBothcity char(15)ASBEGIN EXEC prcListContra

23、ctRecruiter cityEXEC prcPrintRecruitmentAgencyList cityEND 3 按F5 执行语句NIITSQL/Lesson 9/Slide 42 of 61实现存储过程在数据库中检查过程的存在在数据库中检查过程的存在*动作：3 在Query Analyzer窗口中，输入：sp_helptext prcDisplayBoth3 按F5 执行语句执行带参数的过程执行带参数的过程*动作：3 在Query Analyzer窗口中，输入：prcDisplayBoth Alexandria3 按F5执行语句NIITSQL/Lesson 9/Slide 43 of

24、 61实现存储过程稍等一下稍等一下*你需要修改一个存储过程。你将使用哪个命令来修改此过程?NIITSQL/Lesson 9/Slide 44 of 61实现存储过程9.D.6返回多个输出值返回多个输出值*一份格式化的有关公司提供职位状态的报告将被显示。创建一个过程，prcGetPositionDetail ，它将返回职位描述、预算人力资源和给定职位的当前员工的人数。创建另外一个过程称为prcDisplayPositionStatus，它将显示Requisition表的cRequisitionCode、 vRegion和siNoOfVacancy。过程prcDisplayPositionStat

25、us还应调用prcGetPositionDetail过程，按以下格式显示它关于职位0002的返回值NIITSQL/Lesson 9/Slide 45 of 61实现存储过程9.D.6返回多个输出值返回多个输出值(续续)The Status for the Position: Maketing Manager Budgeted Strength : 100 Current Strength : 83 cRequisitionCode vRegion siNoOfVacancy - - - 000002 Texas 11NIITSQL/Lesson 9/Slide 46 of 61实现存储过程任务

26、单任务单*确定一种方法来编写将返回多值的类属过程*在数据库中创建过程*在数据库中检查过程的存在。*执行带参数的过程NIITSQL/Lesson 9/Slide 47 of 61实现存储过程确定一种方法来编写将返回多值的类属过程确定一种方法来编写将返回多值的类属过程* OUTPUT参数- 用来从过程返回一个以上的值*语法CREATE PROCEDURE procedure_name parameter data_type OUTPUT ASsql_statement .n3 结果：使用 CREATE PROCEDURE 带 OUTPUT 选项NIITSQL/Lesson 9/Slide 48 o

27、f 61实现存储过程在数据库中创建过程在数据库中创建过程*动作:3 在Query Analyzer窗口中，输入以下语句.CREATE PROCEDURE prcGetPositionDetail Pcode char(4), Description char(30) OUTPUT, budget int OUTPUT, CurrentStrength int OUTPUTASBEGINIF EXISTS(SELECT * FROM Position WHERE cPositionCode = PCode)NIITSQL/Lesson 9/Slide 49 of 61实现存储过程在数据库中创建过

28、程在数据库中创建过程(续续)BEGINSELECT Description = vDescription,budget = iBudgetedStrength, CurrentStrength = iCurrentStrength FROM Position WHERE cPositionCode=Pcode RETURN 0ENDELSERETURN 1END3 按F5 执行语句NIITSQL/Lesson 9/Slide 50 of 61实现存储过程在数据库中创建过程在数据库中创建过程(续续)3 在Query Analyzer窗口中，输入以下语句：CREATE PROCEDURE prcD

29、isplayPositionStatus PCode char(4)ASBEGINDECLARE Description char(30)DECLARE budget intDECLARE CurrentStrength intDECLARE ReturnValue intEXEC ReturnValue = prcGetPositionDetail Pcode,Description output,Budget output,CurrentStrength outputNIITSQL/Lesson 9/Slide 51 of 61实现存储过程在数据库中创建过程在数据库中创建过程(续续)IF

30、(ReturnValue = 0)BEGINPRINT The Status for the Position: + DescriptionPRINT Budgeted Strength : + CONVERT( char(30), budget)PRINT Current Strength : + CONVERT (char(30), CurrentStrength)SELECT cRequisitionCode,vRegion, siNoOfVacancy FROM Requisition WHERE cPositionCode=PcodeNIITSQL/Lesson 9/Slide 52

31、 of 61实现存储过程在数据库中创建过程在数据库中创建过程(续续) ENDELSEPRINT No records for the given position codeEND3 按F5执行语句NIITSQL/Lesson 9/Slide 53 of 61实现存储过程在数据库中检查过程的存在在数据库中检查过程的存在*在Query Analyzer 窗口中，输入并执行下面语句： sp_helptext prcGetPositionDetailgosp_helptext prcDisplayPositionStatusgoNIITSQL/Lesson 9/Slide 54 of 61实现存储过程执行带参数的过程执行带参数的过程*动作：3 在Query Analyzer窗口中，输入：prcDisplayPositionstatus