a)      课堂练习：  创建参数化存储过程

                        i.  目标

b)        本次练习的目标是创建一个接受输入参数并将输出参数与成功或失败标志一起返回的存储过程。

                        i.  准备

c)         确保虚拟机器2779A-2780A-09正在运行，并且您作为 Student 登录。

d)        如果未启动虚拟机，请执行下列步骤：

2.         关闭其他正在运行的虚拟机。

3.         启动该虚拟机。

4.         在“登录到 Windows”对话框中，通过使用用户名 Student 及密码 Pa$$w0rd 完成该登录过程。

                        i.  创建简单存储过程

b)        执行以下步骤以创建简单存储过程：

5.         单击“开始”，指向“所有程序”，指向“Microsoft SQL Server 2005”，然后单击“SQL Server Management Studio”。

6.         在“连接到服务器”对话框中，指定下表中的值，然后单击“连接”。

13.     在“文件”菜单上，指向“打开”，然后单击“文件”。

14.     打开 E:\Practices 文件夹中的 StoredProcedures.sql 查询文件。出现提示时使用 Windows 身份验证连接到 MIAMI。

15.     检查注释“Get reviews for all products”下的代码，然后选中这些代码并单击“执行”。

16.     查看查询输出，并确认命令已成功完成。

17.     选中注释“Test stored procedure”下的查询，然后在工具栏上单击“执行”。

18.     查看查询结果。

                        i.  创建接受输入参数的存储过程

b)    执行以下步骤以创建接受输入参数的存储过程：

19.     选中注释“Alter procedure to get specific product review”下的 ALTER PROCEDURE 语句，然后在工具栏上单击“执行”。

20.     查看查询输出，并确认命令已成功完成。

21.     选择注释“Test procedure with parameter”下的查询，然后在工具栏上单击“执行”。

22.     查看查询结果。注意第一条 EXECUTE 语句产生了正确的结果，但是第二条 EXECUTE 语句失败，因为参数未传给过程，并且该参数在过程中没有默认值。

23.     选中注释“Alter procedure to get specific product review or all reviews”下的 ALTER PROCEDURE 语句，然后在工具栏上单击“执行”。

24.     查看查询输出，并确认命令已成功完成。

25.     选中注释“Test procedure with parameter and default”下的查询，然后在工具栏上单击“执行”。

26.     查看查询结果。请注意，第一条 EXECUTE 语句生成了指定产品的结果，而第二条 EXECUTE 语句生成了所有产品的结果。

                        i.  创建接受输出参数和返回值的存储过程

b)        执行以下步骤以创建接受输出参数并返回成功或失败值的存储过程：

27.     选中注释“Alter procedure to output number of reviews and check product exists”下的 ALTER PROCEDURE 语句，然后在工具栏上单击“执行”。

28.     查看查询输出，并确认命令已成功完成。

29.     选中注释“Test output and return values”和 GO 语句之间的查询，然后在工具栏上，单击“执行”。

30.     查看查询结果。请注意，结果显示正确，后跟得自于输出参数的评审数量。

31.     修改 EXECUTE 语句，将 OUTPUT 关键字删除。

32.     再次选中注释“Test output and return values”和 GO 语句之间的查询，然后在工具栏上，单击“执行”。

33.     查看查询结果。请注意结果显示正确，但是评审数量返回 NULL，因为 OUTPUT 关键字已删除。

34.     修改 EXECUTE 语句，将 OUTPUT 关键字重新放回，并将输入参数从 937 更改为 DEFAULT。

35.     再次选中注释“Test output and return values”和 GO 语句之间的查询，然后在工具栏上，单击“执行”。

36.     查看查询结果。注意所有评审现在都已返回，并且评审数量也已增加。

37.     修改 EXECUTE 语句，将输入参数从 DEFAULT 更改为 100。

38.     再次选中注释“Test output and return values”和 GO 语句之间的查询，然后在工具栏上，单击“执行”。

39.     查看查询结果。注意，没有返回任何评审，并且出现了消息“ProductID does not exist”，因为使用了无效的产品 ID。

                        i.  删除存储过程

b)        执行以下步骤以删除存储过程：

40.     选中注释“Drop the procedure”下的 DROP PROCEDURE 语句，然后在工具栏上单击“执行”。

41.     查看查询输出，并确认命令已成功完成。

42.     关闭 SQL Server Management Studio 而不将任何更改保存到文件。

a)      课堂练习：  创建函数

                        i.  目标

b)        本次课堂练习的目标是创建标量函数、内联表值函数以及多语句表值函数。

                        i.  准备

c)         确保虚拟机器2779A-2780A-09运行，且以 Student 身份登录。

d)        如果未启动虚拟机，请执行下列步骤：

43.     关闭其他正在运行的虚拟机。

44.     启动该虚拟机。

45.     在“登录到 Windows”对话框中，通过使用用户名 Student 及密码 Pa$$w0rd 完成登录过程。

                        i.  创建标量函数

b)        执行以下步骤以创建标量函数：

46.     单击“开始”，指向“所有程序”，指向“Microsoft SQL Server 2005”，然后单击“SQL Server Management Studio”。

47.     在“连接到服务器”对话框中，指定下表中的值，然后单击“连接”。

54.     在“文件”菜单上，指向“打开”，然后单击“文件”。

55.     打开 E:\Practices 文件夹中的 UserDefinedFunctions.sql 查询文件。出现提示时使用 Windows 身份验证连接到 MIAMI。

56.     选中注释“Create scalar function”下的代码，然后在工具栏上单击“执行”。

57.     查看查询输出，并确认命令已成功完成。

58.     选中注释“Test scalar function in SELECT”下的查询，然后在工具栏上单击“执行”。

59.     查看查询结果。注意大部分产品都没有评审，因此显示平均评级为 0。

60.     选中注释“Test scalar function in WHERE”下的查询，然后在工具栏上单击“执行”。

61.     查看查询结果。注意只返回了有评审的产品。

                        i.  创建内联表值函数

b)        执行以下步骤以创建内联表值函数：

62.     选择注释“Create inline function”下的 CREATE FUNCTION 语句，然后在工具栏上单击“执行”。

63.     查看查询输出，并确认命令已成功完成。

64.     选择注释“Test inline function”下的查询，然后在工具栏上单击“执行”。

65.     查看查询结果。注意只显示了一件产品的评审。

                        i.  创建多语句表值函数

b)        执行以下步骤以创建多语句表值函数：

66.     选择注释“Create multi- statement function”下的 CREATE FUNCTION 语句，然后在工具栏上单击“执行”。

67.     查看查询输出，并确认命令已成功完成。

68.     选中注释“Test multi-statement function”和 GO 语句之间的查询，然后在工具栏上，单击“执行”。

69.     查看查询结果。注意第一个结果集包含 3 或更高的评级，而第二个结果集包含低于 3 的评级。

                        i.  删除用户定义函数

b)        执行以下步骤以删除用户定义函数：

70.     选中注释“Drop user-defined functions”下的 DROP FUNCTION 语句，然后在工具栏上单击“执行”。

71.     查看查询输出，并确认命令已成功完成。

72.     关闭 SQL Server Management Studio 而不将任何更改保存到文件。

a)      课堂练习：  处理错误

                        i.  目标    

73.   本次课堂练习的目标是向存储过程添加错误处理。

                i.  准备  

b)        确保虚拟机器 2779A-2780A-09 正在运行，且以 Student 身份登录。

c)         如果虚拟机器尚未启动，则执行以下步骤：

1.         关闭任何其他正在运行的虚拟机器。

2.         启动虚拟机器。

3.         在“登录到 Windows”对话框中，使用用户名 Student 和密码 Pa$$w0rd 完成登录过程。

                     ii.  向存储过程添加错误处理

d)      执行以下步骤以将错误处理添加到存储过程：

74.     单击“开始”，指向“所有程序”，指向“Microsoft SQL Server 2005”，然后单击“SQL Server Management Studio”。

75.     在“连接到服务器”对话框中，指定下表中的值，然后单击“连接”。

82.     在“文件”菜单上，指向“打开”，然后单击“文件”。

83.     打开 E:\Practices 文件夹中的 ErrorHandling.sql 查询文件。出现提示时使用 Windows 身份验证连接到 MIAMI。

84.     选择注释“Create procedure without error handling”下的代码，然后在工具栏上单击“执行”。

85.     查看查询输出，并确认命令已成功完成。

86.     选择注释“Test stored procedure without error handling”下的 EXECUTE 和 SELECT 查询，然后在工具栏上单击“执行”。

87.     查看查询结果。注意评审已成功添加到表中。

88.     将 @Rating 参数值从 4 更改为 10。

89.     重新选择注释“Test stored procedure without error handling”下的 EXECUTE 和 SELECT 查询，然后在工具栏上单击“执行”。

90.     查看查询结果。注意，由于 ratings 列上的 CHECK 约束，插入失败。

91.     选中注释“Implement error handling”下的 ALTER PROCEDURE 语句，然后在工具栏上单击“执行”。

92.     查看查询输出，并确认命令已成功完成。

93.     选中注释“Test stored procedure with error handling”下的 EXECUTE 查询，然后在工具栏上单击“执行”。

94.     查看查询结果。注意，错误号和错误消息显示，因为 CATCH 块处理错误成功。

95.     选中位于文件结束处的 DROP PROCEDURE 语句，然后单击“执行”。

96.     关闭 SQL Server Management Studio 而不将任何更改保存到文件。

a)      演示：     控制执行上下文

                        i.  简介

                      ii.              可以创建指定执行上下文的存储过程或用户定义函数，以避免当模块调用其他数据库对象时所带来的权限相关问题。可使用 CREATE PROCEDURE 语句的 EXECUTE AS 子句使存储过程在执行时模拟另一个用户。

                   iii.  在存储过程中使用执行上下文

b)        要创建指定执行上下文的存储过程：

97.     单击“开始”，指向“所有程序”，指向“Microsoft SQL Server 2005”，然后单击“SQL Server Management Studio”。

98.     在“连接到服务器”对话框中，指定下表中的值，然后单击“连接”。

105.  在“文件”菜单上，指向“打开”，然后单击“文件”。

106.  打开 E:\Democode 文件夹中的 ExecutionContext.sql 查询文件。出现提示时使用 Windows 身份验证连接到 MIAMI。

107.  选中注释“Preparation tasks”下的代码，然后在工具栏上单击“执行”按钮。这将创建两个 Windows 用户以及 AdventureWorks 数据库中对应用户的登录名。

108.  选中注释“Execute as CALLER”下的代码，然后在工具栏上单击“执行”按钮。这将创建 DisplayContext 存储过程。

109.  选中注释“Grant user permissions”下的代码，然后在工具栏上单击“执行”按钮。这将允许两个用户都能访问 DisplayContext 存储过程。