SQL Server (9)
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第10章 10章
存储过程
10.1 存储过程的概念 10.2 创建与执行 10.3 存储过程的操作
10.1 存储过程的概念
一、存储过程 SQL Server提供了一种方法，它可以将一些 Server提供了一种方法 提供了一种方法， 固定的操作集中起来由SQL Server数据库服务器 固定的操作集中起来由SQL Server数据库服务器 来完成，以实现某个任务， 来完成，以实现某个任务，这种方法就是存储过 程。 在SQL Server中存储过程分为两类：即系统 Server中存储过程分为两类 中存储过程分为两类： 提供的存储过程和用户自定义的存储过程。 提供的存储过程和用户自定义的存储过程。
二、存储过程与视图的比较 1、视图中只能有SELECT语句 视图中只能有SELECT SELECT语句 存储过程中可以有各种SQL SQL语句 存储过程中可以有各种SQL语句 2、视图不能接受参数，只能返回结果集 视图不能接受参数， 存储过程能接受参数， 存储过程能接受参数，也可返回结果集
10.2 创建与执行
一、创建 可以使用三种方法创建存储过程 ： 1、使用创建存储过程向导创建存储过程。 使用创建存储过程向导创建存储过程。 2、利用SQL Server 企业管理器创建存储过程。 利用SQL 企业管理器创建存储过程。 3、使用Transact-SQL语句中的CREATE 使用Transact-SQL语句中的 语句中的CREATE PROCEDURE命令创建存储过程。 PROCEDURE命令创建存储过程。 命令创建存储过程
1、 使用创建存储过程向导创建存储过程
在企业管理器中， 在企业管理器中，选 择工具菜单中的向导选项， 择工具菜单中的向导选项， 选择“创建存储过程向 导 ” ， 则出现欢迎使用 创建存储过程向导对话框。 创建存储过程向导对话框。 根据以下各图提示可完成 创建存储过程。 创建存储过程。
新建SQL Server组 图10-1 新建 组
图10-2 欢迎使用创建存储过程向导对话框
图10-3 选择数据库对话框
图10-4 选择数据库对象对话框
图10-5 完成创建存储过程向导对话框
图10-6 编辑存储过程属性对话框
编辑存储过程SQL对话框 图10-7 编辑存储过程 对话框
2、. 使用企业管理器创建存储过程
⑴、在SQL Server企业管理器中，选择指定 Server企业管理器中 企业管理器中，
的服务器和数据库， 的服务器和数据库 ， 用右键单击要创建存储过程 的数据库， 在弹出的快捷菜单中选择“ 新建” 的数据库 ， 在弹出的快捷菜单中选择 “ 新建 ” 选 项，再选择下一级菜单中的“存储过程…”选项， 再选择下一级菜单中的“存储过程… 选项， 或者用右键单击存储过程图标， 或者用右键单击存储过程图标 ， 从弹出的快捷菜 单中选择“新建存储过程… 选项， 单中选择“新建存储过程…”选项，均会出现创建 存储过程对话框。 存储过程对话框。
⑵、在文本框中可以输入创建存储过程的SQL 在文本框中可以输入创建存储过程的SQL
语句，单击“检查语法”，则可以检查语法是 语句，单击“检查语法” 否正确；单击“确定”按钮， 否正确；单击“确定”按钮，即可保存该存储 过程。如果要设置权限，单击“权限…”按钮 按钮。 过程。如果要设置权限，单击“权限…”按钮。
选择新建存储过程对话框（ ） 图10-8 选择新建存储过程对话框（1）
选择新建存储过程对话框（ ） 图10-9 选择新建存储过程对话框（2）
图10-10 新建存储过程对话框
图10-11 设置权限对话框
3、 使用SQL语句创建存储过程 使用SQL语句创建存储过程
3、 使用SQL语句创建存储过程 使用SQL语句创建存储过程
命令格式： 命令格式： CREATE PROCEDURE 存储过程名 长度] [参数 数据类型 长度] [参数 数据类型 长度 OUTPUT] AS SQL语句 SQL语句
二、执行 命令格式： 命令格式： EXEC[ UTE ] 存储过程名 [ 参数名= 参数值 ] 参数名= 参数值1 参数值2 [ 参数值1，参数值2，……]
例1、在XK数据库中创建一个名为P_Student的存 XK数据库中创建一个名为P_Student的存 数据库中创建一个名为P_Student 储过程， 储过程，该存储过程返回学生表中所有班级代 码为20000001 20000001的记录 码为20000001的记录 USE Xk GO CREATE PROCEDURE p_Student AS SELECT * FROM Student WHERE ClassNo='20000001'
例2、在XK数据库中创建一个名为P_StudentPara XK数据库中创建一个名为 数据库中创建一个名为P_StudentPara 的存储过程， 的存储过程，该存储过程根据指定的班级返回 该班级代码对应的Student Student表中的记录 该班级代码对应的Student表中的记录 CREATE PROCEDURE p_StudentPara @ClassNo VARCHAR(8) AS SELECT * FROM Student WHERE ClassNo=@ClassNo
例1的执行 USE Xk GO EXEC p_Student 例2的执行 EXEC p_StudentPara GO EXEC p_StudentPara GO
@ClassNo='20000001’ '20000002'
例3、创建存储过程P_ClassNum,要求能根据用户 创建存储过程P_ClassNum, P_ClassNum,要求能根据用户 给定的班级代码，统计该班的人数， 给定的班级代码，统计该班的人数，并将人数 以输出变量返回给用户。 以输出变量返回给用户。 CREATE PROCEDURE p_ClassNum @ClassNo VARCHAR(8),@ClassNum SMALLINT OUTPUT AS SET @ClassNum= ( SELECT COUNT(*) FROM Student WHERE ClassNo=@ClassNo ) PRINT @ClassNum
例3的执行 DECLARE @ClassNo VARCHAR(8),@ClassNum SMALLINT SET @ClassNo='20000001' EXEC p_ClassNum @ClassNo,@ClassNum
10.3
存储过程的操作
一、查看存储过程 存储过程被创建之后， 存储过程被创建之后 ， 它的名字就存储在系 统 表 sysobjects 中 ， 它 的 源 代 码 存 放 在 系 统 表 syscomments中 syscomments中。可以使用使用企业管理器或系统 存储过程来查看用户创建的存储过程。 存储过程来查看用户创建的存储过程。
1、使用企业管理器查看用户创建的存储过程 在企业管理器中，打开指定的服务器和数据库 在企业管理器中， 选择要创建存储过程的数据库， 项，选择要创建存储过程的数据库，单击存储过程 文件夹， 文件夹，此时在右边的页框中显示该数据库的所有 存储过程。用右键单击要查看的存储过程， 存储过程。用右键单击要查看的存储过程，从弹出 的快捷菜单中选择属性选项， 的快捷菜单中选择属性选项，此时便可以看到存储 过程的源代码。 过程的源代码。
2、使用系统存储过程来查看用户创建的存储过程 sp_help [[@objname=]存储过程名] [[@objname=]存储过程名 存储过程名] 显示存储过程的参数及其数据类型
sp_helptext [[@objname=]存储过程名] [[@objname=]存储过程名 存储过程名] 显示存储过程的源代码
二、 修改存储过程 命令格式： 命令格式： ALTER PROCEDURE 存储过程名 长度] [参数 数据类型 长度] [参数 数据类型 长度 OUTPUT] AS SQL语句 SQL语句
例：修改前边提到的例2 修改前边提到的例2 ALTER PROCEDURE p_StudentPara @ClassName VARCHAR(20) AS SELECT ClassName,StuNo,StuName,Pwd FROM Student,Class WHERE Student.ClassNo=Class.ClassNo AND ClassName LIKE '%'+@ClassName+'%'
三、重命名存储过程 1、企业管理器 2、命令方式： sp_rename 存储过程原名，存储过程新名 存储过程原名，
四、删除存储过程 1、企业管理器 2、命令方式： 命令方式： DROP procedure
存储过程名
存储过程
10.1 存储过程的概念 10.2 创建与执行 10.3 存储过程的操作
10.1 存储过程的概念
一、存储过程 SQL Server提供了一种方法，它可以将一些 Server提供了一种方法 提供了一种方法， 固定的操作集中起来由SQL Server数据库服务器 固定的操作集中起来由SQL Server数据库服务器 来完成，以实现某个任务， 来完成，以实现某个任务，这种方法就是存储过 程。 在SQL Server中存储过程分为两类：即系统 Server中存储过程分为两类 中存储过程分为两类： 提供的存储过程和用户自定义的存储过程。 提供的存储过程和用户自定义的存储过程。
二、存储过程与视图的比较 1、视图中只能有SELECT语句 视图中只能有SELECT SELECT语句 存储过程中可以有各种SQL SQL语句 存储过程中可以有各种SQL语句 2、视图不能接受参数，只能返回结果集 视图不能接受参数， 存储过程能接受参数， 存储过程能接受参数，也可返回结果集
10.2 创建与执行
一、创建 可以使用三种方法创建存储过程 ： 1、使用创建存储过程向导创建存储过程。 使用创建存储过程向导创建存储过程。 2、利用SQL Server 企业管理器创建存储过程。 利用SQL 企业管理器创建存储过程。 3、使用Transact-SQL语句中的CREATE 使用Transact-SQL语句中的 语句中的CREATE PROCEDURE命令创建存储过程。 PROCEDURE命令创建存储过程。 命令创建存储过程
1、 使用创建存储过程向导创建存储过程
在企业管理器中， 在企业管理器中，选 择工具菜单中的向导选项， 择工具菜单中的向导选项， 选择“创建存储过程向 导 ” ， 则出现欢迎使用 创建存储过程向导对话框。 创建存储过程向导对话框。 根据以下各图提示可完成 创建存储过程。 创建存储过程。
新建SQL Server组 图10-1 新建 组
图10-2 欢迎使用创建存储过程向导对话框
图10-3 选择数据库对话框
图10-4 选择数据库对象对话框
图10-5 完成创建存储过程向导对话框
图10-6 编辑存储过程属性对话框
编辑存储过程SQL对话框 图10-7 编辑存储过程 对话框
2、. 使用企业管理器创建存储过程
⑴、在SQL Server企业管理器中，选择指定 Server企业管理器中 企业管理器中，
的服务器和数据库， 的服务器和数据库 ， 用右键单击要创建存储过程 的数据库， 在弹出的快捷菜单中选择“ 新建” 的数据库 ， 在弹出的快捷菜单中选择 “ 新建 ” 选 项，再选择下一级菜单中的“存储过程…”选项， 再选择下一级菜单中的“存储过程… 选项， 或者用右键单击存储过程图标， 或者用右键单击存储过程图标 ， 从弹出的快捷菜 单中选择“新建存储过程… 选项， 单中选择“新建存储过程…”选项，均会出现创建 存储过程对话框。 存储过程对话框。
⑵、在文本框中可以输入创建存储过程的SQL 在文本框中可以输入创建存储过程的SQL
语句，单击“检查语法”，则可以检查语法是 语句，单击“检查语法” 否正确；单击“确定”按钮， 否正确；单击“确定”按钮，即可保存该存储 过程。如果要设置权限，单击“权限…”按钮 按钮。 过程。如果要设置权限，单击“权限…”按钮。
选择新建存储过程对话框（ ） 图10-8 选择新建存储过程对话框（1）
选择新建存储过程对话框（ ） 图10-9 选择新建存储过程对话框（2）
图10-10 新建存储过程对话框
图10-11 设置权限对话框
3、 使用SQL语句创建存储过程 使用SQL语句创建存储过程
3、 使用SQL语句创建存储过程 使用SQL语句创建存储过程
命令格式： 命令格式： CREATE PROCEDURE 存储过程名 长度] [参数 数据类型 长度] [参数 数据类型 长度 OUTPUT] AS SQL语句 SQL语句
二、执行 命令格式： 命令格式： EXEC[ UTE ] 存储过程名 [ 参数名= 参数值 ] 参数名= 参数值1 参数值2 [ 参数值1，参数值2，……]
例1、在XK数据库中创建一个名为P_Student的存 XK数据库中创建一个名为P_Student的存 数据库中创建一个名为P_Student 储过程， 储过程，该存储过程返回学生表中所有班级代 码为20000001 20000001的记录 码为20000001的记录 USE Xk GO CREATE PROCEDURE p_Student AS SELECT * FROM Student WHERE ClassNo='20000001'
例2、在XK数据库中创建一个名为P_StudentPara XK数据库中创建一个名为 数据库中创建一个名为P_StudentPara 的存储过程， 的存储过程，该存储过程根据指定的班级返回 该班级代码对应的Student Student表中的记录 该班级代码对应的Student表中的记录 CREATE PROCEDURE p_StudentPara @ClassNo VARCHAR(8) AS SELECT * FROM Student WHERE ClassNo=@ClassNo
例1的执行 USE Xk GO EXEC p_Student 例2的执行 EXEC p_StudentPara GO EXEC p_StudentPara GO
@ClassNo='20000001’ '20000002'
例3、创建存储过程P_ClassNum,要求能根据用户 创建存储过程P_ClassNum, P_ClassNum,要求能根据用户 给定的班级代码，统计该班的人数， 给定的班级代码，统计该班的人数，并将人数 以输出变量返回给用户。 以输出变量返回给用户。 CREATE PROCEDURE p_ClassNum @ClassNo VARCHAR(8),@ClassNum SMALLINT OUTPUT AS SET @ClassNum= ( SELECT COUNT(*) FROM Student WHERE ClassNo=@ClassNo ) PRINT @ClassNum
例3的执行 DECLARE @ClassNo VARCHAR(8),@ClassNum SMALLINT SET @ClassNo='20000001' EXEC p_ClassNum @ClassNo,@ClassNum
10.3
存储过程的操作
一、查看存储过程 存储过程被创建之后， 存储过程被创建之后 ， 它的名字就存储在系 统 表 sysobjects 中 ， 它 的 源 代 码 存 放 在 系 统 表 syscomments中 syscomments中。可以使用使用企业管理器或系统 存储过程来查看用户创建的存储过程。 存储过程来查看用户创建的存储过程。
1、使用企业管理器查看用户创建的存储过程 在企业管理器中，打开指定的服务器和数据库 在企业管理器中， 选择要创建存储过程的数据库， 项，选择要创建存储过程的数据库，单击存储过程 文件夹， 文件夹，此时在右边的页框中显示该数据库的所有 存储过程。用右键单击要查看的存储过程， 存储过程。用右键单击要查看的存储过程，从弹出 的快捷菜单中选择属性选项， 的快捷菜单中选择属性选项，此时便可以看到存储 过程的源代码。 过程的源代码。
2、使用系统存储过程来查看用户创建的存储过程 sp_help [[@objname=]存储过程名] [[@objname=]存储过程名 存储过程名] 显示存储过程的参数及其数据类型
sp_helptext [[@objname=]存储过程名] [[@objname=]存储过程名 存储过程名] 显示存储过程的源代码
二、 修改存储过程 命令格式： 命令格式： ALTER PROCEDURE 存储过程名 长度] [参数 数据类型 长度] [参数 数据类型 长度 OUTPUT] AS SQL语句 SQL语句
例：修改前边提到的例2 修改前边提到的例2 ALTER PROCEDURE p_StudentPara @ClassName VARCHAR(20) AS SELECT ClassName,StuNo,StuName,Pwd FROM Student,Class WHERE Student.ClassNo=Class.ClassNo AND ClassName LIKE '%'+@ClassName+'%'
三、重命名存储过程 1、企业管理器 2、命令方式： sp_rename 存储过程原名，存储过程新名 存储过程原名，
四、删除存储过程 1、企业管理器 2、命令方式： 命令方式： DROP procedure
存储过程名