第12章存储过程和用户存储过程设计
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
码或分别被多次执行，那么存储过程要比批处理的 执行速度快很多。 （3）存储过程能够减少网络流量
对于同一个针对数据数据库对象的操作（如查 询、修改），如果这一操作所涉及到的 Transaction-SQL语句被组织成一存储过程，那么 当在客户计算机上调用该存储过程时，网络中传送 的只是该调用语句，否则将是多条SQL语句，从而 大大增加了网络流量，降低网络负载。
பைடு நூலகம்
sp_approlepassword；
sp_helpremotelogin ； sp_changedbowner ； sp_helprole ； sp_changegroup
；sp_helprolemember；(部分)
»9
sp_add_data_file_recover_suspect_db
；
sp_helpconstraint ； sp_addextendedproc ； sp_helpdb ；
过
sp_helpserver；sp_attach_db；sp_helpsort；
程
sp_attach_single_file_db ； sp_helpstats ； sp_bindefault ； sp_helptext ； sp_bindrule ；
sp_helptrigger；
sp_bindsession
»14
语法格式： CREATE PROC [ EDURE ] 存储过程名 [；版本号] [ ({ @参数名 数据类型 } [VARYING] [=default]
[OUTPUT] [，…])] [WITH
RECOMPILE|ENCRYPTION|RECOMPILE,ENC RYPTION] [FOR REPLICATION] AS SQL语句
AND E.学号=S.学号 AND E.开课号=O.开课号 SELECT @平均成绩 =AVG(成绩) FROM 学生表 S, 开课表 O, 选课表 E WHERE S.学号=@学号 AND E.学号=S.学号 AND E.开课号=O.开课号 RETURN GO
»22
然后在查询分析器中调用 Scorequery 存储过程
些固定的操作集中起来由SQL Server数据库服 务器来完成，以实现某个任务，这种方法就是 存储过程。
存储过程（Stored Procedure）是一组为了 完成特定功能的SQL 语句集，经编译后存储在 数据库中。
»3
（2）存储过程能够实现较快的执行速度 如果某一操作包含大量的Transaction-SQL 代
或者： 或者：
DECLARE @专业 CHAR(20), @课名 CHAR(20) SET @专业='计算机' SET @课名='数据结构' EXEC P_学生选课信息 @专业,@课名
DECLARE @专业 CHAR(20) SET @专业='计算机' EXEC P_学生选课信息 @专业,'数据结构'
»13
2．使用CREATE PROCEDURE命令创建存储过程
创建存储过程前，应该考虑下列几个事项： ① 在一个批处理中，Create Procedure 语句不能与其它SQL语句
合并在一起； ② 创建存储过程的权限默认属于数据库所有者，该所有者可将此
权限授予其他用户。 ③ 存储过程是数据库对象，其名称必须遵守标识符规则。 ④ 只能在当前数据库中创建当前数据库的存储过程。 ⑤ 一个存储过程的最大尺寸为128M。
DECLARE @学号 CHAR(7),@平均成绩 FLOAT
SET @学号='S060102'
EXEC P_成绩检索和平均 @学号, @平均成绩 OUTPUT
IF @平均成绩 >=90
SELECT
'该学生的成绩'='优秀','平均成绩
'=rtrim(cast(@平均成绩 as VARCHAR(20)))
»15
【例12-3】创建一个简单的存储过程，返回所有学 生的基本信息，包括学生的学号、姓名、所学专 业、所在二级学院、来自的城市。 USE 教学管理 GO CREATE PROCEDURE P_学生部分信息 AS SELECT 学号,姓名,专业,所在院系,籍贯 FROM 学生表
»16
【例12-4】创建带参数的存储过程，实现对指定的某一专业 的学生某门课程的选课信息和成绩。
»11
12.3 创建和执行用户存储过程
12.3.1 创建用户存储过程
用户自定义存储过程是由用户创建并能完成某一特定功能 （如查询用户所需数据信息）的存储过程。在SQL Server中， 可以使用以下方法创建存储过程： ① 利用SQL Server管理器创建存储过程。 ② 使用Transact-SQL语句中的CREATE PROCEDURE命令 创建存储过程。
»17
12.3.2 执行用户存储过程
执行已创建的存储过程使用EXECUTE 命令。 语法格式： [ [ EXEC [ UTE ] ] { [ @return_status = ]{ <存储过程名> [ ;版本号] |
@procedure_name_var} [ [ @参数= ] { value | @variable [ OUTPUT ] |
»8
分类 目录过程 游标过程 分布式查 询过程
安全过程
12.2.2 一些常用的系统存储过程
常用的系统存储过程
sp_column_privileges ； sp_special_columns ； sp_columns ； sp_sproc_columns；sp_databases；sp_statistics； sp_fkeys；sp_stored_procedures；(部分)
sp_addremotelogin；sp_grantlogin；
sp_addrole ； sp_helpdbfixedrole ； sp_addrolemember ； sp_helpgroup ；
sp_addserver；sp_helplinkedsrvlogin；
sp_addsrvrolemember ； sp_helplogins ； sp_adduser ； sp_helpntgroup ；
»6
（4）存储过程可被作为一种安全机制来充分利用系 统管理员通过对执行某一存储过程的权限进行限制， 从而能够实现对相应的数据访问权限的限制，避免 非授权用户对数据的访问，保证数据的安全。
（5）自动完成需要预先执行的任务.存储过程可以在 系统启动时自动执行,而不必在系统启动后再进行 手工操作,大大方便了用户的使用,可以自动完成一 些需要预先执行的任务
[ DEFAULT ] ][ ,...n ] [ WITH RECOMPILE ]
»18
【例12-5】对存储过程P_学生部分信息的执行。 EXEC P_学生部分信息
【例12-6】带输入参数的存储过程P_学生选课信息的执行。 （1）按参数位置传递值
EXEC P_学生选课信息 '计算机','数据结构'
»21
【例12-7】使用OUTPUT参数的存储过程及其执行
首先创建存储过程 USE 教学管理 GO CREATE PROCEDURE P_成绩检索和平均 (@学号 CHAR(7), @平均成绩 FLOAT OUTPUT) AS SELECT S.学号,姓名,课号,成绩 FROM 学生表 S, 开课表 O, 选课表 E WHERE S.学号=@学号
USE 教学管理 IF EXISTS(SELECT*
FROM sysobjects WHERE name='SC_infor' AND type='p') BEGIN DROP PROCEDURE SC_infor END GO CREATE PROCEDURE SC_infor(@smajor CHAR(20), @cname CHAR(20)) AS SELECTS.sno,sname,smajor,sdepa,O.cno,cname,grade FROM student S, enrollment E, offering O, course C where smajor=@smajor AND cname=@cname AND S.sno=E.sno AND E.ono=O.ono AND O.cno=C.cno GO
»7
12.2 系统存储过程
12.2.1 系统存储过程分类 系统存储过程就是系统创建的存储过程，目的在于 能够方便地从系统表中查询信息或完成与更新数 据库表相关的管理任务或其它的系统管理任务。 系统过程以“sp_"开头,在Master数据库中创建并 保存在该数据库中，为数据库管理者所有。一些 系统过程只能由系统管理员使用，而有些系统过 程通过授权可以被其它用户所使用。
»19
（2）按参数名传递值 EXEC P_学生选课信息 @专业 ='计算机',
@课名='数据结构' 按参数名传递值可以改变参数的顺序： EXEC P_学生选课信息 @课名='数据结构',
@专业='计算机'
»20
（3）也可以两种方法混合使用，一旦使用了 '@name = value' 形式之后，所有后续的参 数就必须以 '@name = value' 的形式传递。 比如： EXEC P_学生选课信息 '计算机', @课名='数 据结构'
创建存储过程时，需要确定存储过程的三个组成部分： ① 所有的输入参数以及传给调用者的输出参数。 ② 被执行的针对数据库的操作语句，包括调用其它存储过程 的语句。 ③ 返回给调用者的状态值，以指明调用是成功还是失败。
»12
1．使用SQL Server管理器创
建存储过程
① 在SQL Server管理器中，选择指定的 服务器和数据库，用右键单击要创建存储 过程的数据库，在弹出的快捷菜单中选择 “新建”选项，再选择下一级菜单中的 “存储过程…”选项，从弹出的快捷菜单 中选择“新建存储过程…”选项，出现创 建存储过程对话框。
；
sp_indexoption
；
sp_certify_removable ； sp_invalidate_textptr ；
sp_configure sp_lock；（部分）
»10
如果没有指定数据库名，则sp_helpdb报告 master.dbo.sysdatabases中的所有数据库。 【例12-1】返回pubs数据库的信息 exec sp_helpdb pubs 【例12-2】返回有关所有数据库的信息 exec sp_helpdb
sp_addextendedproperty ； sp_helpdevice ；
sp_add_log_file_recover_suspect_db
；
sp_helpextendedproc；
系
sp_addmessage ； sp_helpfile ； sp_addtype ；
统
sp_helpfilegroup；sp_addumpdevice；sp_helpindex； sp_altermessage ； sp_helplanguage ； sp_autostats ；
sp_cursor_list ； sp_describe_cursor_columns ； sp_describe_cursor ； sp_describe_cursor_tables
sp_addlinkedserver ； sp_indexes ； sp_addlinkedsrvlogin ；
sp_linkedservers；sp_catalogs
sp_primarykeys；
sp_droplinkedsrvlogin；sp_foreignkeys
sp_addalias ； sp_droprolemember ； sp_addapprole ； sp_dropserver ；
sp_addgroup；sp_dropsrvrolemember；
sp_addlinkedsrvlogin；sp_dropuser；sp_addlogin；sp_grantdbaccess；
第12章存储过程和用户存储过程设计
➢ 12.1 存储过程概述 ➢ 12.2 系统存储过程 ➢ 12.3 创建和执行用户存储过程 ➢ 12.4 带状态参数的存储过程及实例分析 ➢ 12.5 修改和删除存储过程 ➢ 12.6 存储过程设计实例分析
»2
12.1 存储过程概述
12.1.1 存储过程的概念和分类 SQL Server提供了一种方法，它可以将一
对于同一个针对数据数据库对象的操作（如查 询、修改），如果这一操作所涉及到的 Transaction-SQL语句被组织成一存储过程，那么 当在客户计算机上调用该存储过程时，网络中传送 的只是该调用语句，否则将是多条SQL语句，从而 大大增加了网络流量，降低网络负载。
பைடு நூலகம்
sp_approlepassword；
sp_helpremotelogin ； sp_changedbowner ； sp_helprole ； sp_changegroup
；sp_helprolemember；(部分)
»9
sp_add_data_file_recover_suspect_db
；
sp_helpconstraint ； sp_addextendedproc ； sp_helpdb ；
过
sp_helpserver；sp_attach_db；sp_helpsort；
程
sp_attach_single_file_db ； sp_helpstats ； sp_bindefault ； sp_helptext ； sp_bindrule ；
sp_helptrigger；
sp_bindsession
»14
语法格式： CREATE PROC [ EDURE ] 存储过程名 [；版本号] [ ({ @参数名 数据类型 } [VARYING] [=default]
[OUTPUT] [，…])] [WITH
RECOMPILE|ENCRYPTION|RECOMPILE,ENC RYPTION] [FOR REPLICATION] AS SQL语句
AND E.学号=S.学号 AND E.开课号=O.开课号 SELECT @平均成绩 =AVG(成绩) FROM 学生表 S, 开课表 O, 选课表 E WHERE S.学号=@学号 AND E.学号=S.学号 AND E.开课号=O.开课号 RETURN GO
»22
然后在查询分析器中调用 Scorequery 存储过程
些固定的操作集中起来由SQL Server数据库服 务器来完成，以实现某个任务，这种方法就是 存储过程。
存储过程（Stored Procedure）是一组为了 完成特定功能的SQL 语句集，经编译后存储在 数据库中。
»3
（2）存储过程能够实现较快的执行速度 如果某一操作包含大量的Transaction-SQL 代
或者： 或者：
DECLARE @专业 CHAR(20), @课名 CHAR(20) SET @专业='计算机' SET @课名='数据结构' EXEC P_学生选课信息 @专业,@课名
DECLARE @专业 CHAR(20) SET @专业='计算机' EXEC P_学生选课信息 @专业,'数据结构'
»13
2．使用CREATE PROCEDURE命令创建存储过程
创建存储过程前，应该考虑下列几个事项： ① 在一个批处理中，Create Procedure 语句不能与其它SQL语句
合并在一起； ② 创建存储过程的权限默认属于数据库所有者，该所有者可将此
权限授予其他用户。 ③ 存储过程是数据库对象，其名称必须遵守标识符规则。 ④ 只能在当前数据库中创建当前数据库的存储过程。 ⑤ 一个存储过程的最大尺寸为128M。
DECLARE @学号 CHAR(7),@平均成绩 FLOAT
SET @学号='S060102'
EXEC P_成绩检索和平均 @学号, @平均成绩 OUTPUT
IF @平均成绩 >=90
SELECT
'该学生的成绩'='优秀','平均成绩
'=rtrim(cast(@平均成绩 as VARCHAR(20)))
»15
【例12-3】创建一个简单的存储过程，返回所有学 生的基本信息，包括学生的学号、姓名、所学专 业、所在二级学院、来自的城市。 USE 教学管理 GO CREATE PROCEDURE P_学生部分信息 AS SELECT 学号,姓名,专业,所在院系,籍贯 FROM 学生表
»16
【例12-4】创建带参数的存储过程，实现对指定的某一专业 的学生某门课程的选课信息和成绩。
»11
12.3 创建和执行用户存储过程
12.3.1 创建用户存储过程
用户自定义存储过程是由用户创建并能完成某一特定功能 （如查询用户所需数据信息）的存储过程。在SQL Server中， 可以使用以下方法创建存储过程： ① 利用SQL Server管理器创建存储过程。 ② 使用Transact-SQL语句中的CREATE PROCEDURE命令 创建存储过程。
»17
12.3.2 执行用户存储过程
执行已创建的存储过程使用EXECUTE 命令。 语法格式： [ [ EXEC [ UTE ] ] { [ @return_status = ]{ <存储过程名> [ ;版本号] |
@procedure_name_var} [ [ @参数= ] { value | @variable [ OUTPUT ] |
»8
分类 目录过程 游标过程 分布式查 询过程
安全过程
12.2.2 一些常用的系统存储过程
常用的系统存储过程
sp_column_privileges ； sp_special_columns ； sp_columns ； sp_sproc_columns；sp_databases；sp_statistics； sp_fkeys；sp_stored_procedures；(部分)
sp_addremotelogin；sp_grantlogin；
sp_addrole ； sp_helpdbfixedrole ； sp_addrolemember ； sp_helpgroup ；
sp_addserver；sp_helplinkedsrvlogin；
sp_addsrvrolemember ； sp_helplogins ； sp_adduser ； sp_helpntgroup ；
»6
（4）存储过程可被作为一种安全机制来充分利用系 统管理员通过对执行某一存储过程的权限进行限制， 从而能够实现对相应的数据访问权限的限制，避免 非授权用户对数据的访问，保证数据的安全。
（5）自动完成需要预先执行的任务.存储过程可以在 系统启动时自动执行,而不必在系统启动后再进行 手工操作,大大方便了用户的使用,可以自动完成一 些需要预先执行的任务
[ DEFAULT ] ][ ,...n ] [ WITH RECOMPILE ]
»18
【例12-5】对存储过程P_学生部分信息的执行。 EXEC P_学生部分信息
【例12-6】带输入参数的存储过程P_学生选课信息的执行。 （1）按参数位置传递值
EXEC P_学生选课信息 '计算机','数据结构'
»21
【例12-7】使用OUTPUT参数的存储过程及其执行
首先创建存储过程 USE 教学管理 GO CREATE PROCEDURE P_成绩检索和平均 (@学号 CHAR(7), @平均成绩 FLOAT OUTPUT) AS SELECT S.学号,姓名,课号,成绩 FROM 学生表 S, 开课表 O, 选课表 E WHERE S.学号=@学号
USE 教学管理 IF EXISTS(SELECT*
FROM sysobjects WHERE name='SC_infor' AND type='p') BEGIN DROP PROCEDURE SC_infor END GO CREATE PROCEDURE SC_infor(@smajor CHAR(20), @cname CHAR(20)) AS SELECTS.sno,sname,smajor,sdepa,O.cno,cname,grade FROM student S, enrollment E, offering O, course C where smajor=@smajor AND cname=@cname AND S.sno=E.sno AND E.ono=O.ono AND O.cno=C.cno GO
»7
12.2 系统存储过程
12.2.1 系统存储过程分类 系统存储过程就是系统创建的存储过程，目的在于 能够方便地从系统表中查询信息或完成与更新数 据库表相关的管理任务或其它的系统管理任务。 系统过程以“sp_"开头,在Master数据库中创建并 保存在该数据库中，为数据库管理者所有。一些 系统过程只能由系统管理员使用，而有些系统过 程通过授权可以被其它用户所使用。
»19
（2）按参数名传递值 EXEC P_学生选课信息 @专业 ='计算机',
@课名='数据结构' 按参数名传递值可以改变参数的顺序： EXEC P_学生选课信息 @课名='数据结构',
@专业='计算机'
»20
（3）也可以两种方法混合使用，一旦使用了 '@name = value' 形式之后，所有后续的参 数就必须以 '@name = value' 的形式传递。 比如： EXEC P_学生选课信息 '计算机', @课名='数 据结构'
创建存储过程时，需要确定存储过程的三个组成部分： ① 所有的输入参数以及传给调用者的输出参数。 ② 被执行的针对数据库的操作语句，包括调用其它存储过程 的语句。 ③ 返回给调用者的状态值，以指明调用是成功还是失败。
»12
1．使用SQL Server管理器创
建存储过程
① 在SQL Server管理器中，选择指定的 服务器和数据库，用右键单击要创建存储 过程的数据库，在弹出的快捷菜单中选择 “新建”选项，再选择下一级菜单中的 “存储过程…”选项，从弹出的快捷菜单 中选择“新建存储过程…”选项，出现创 建存储过程对话框。
；
sp_indexoption
；
sp_certify_removable ； sp_invalidate_textptr ；
sp_configure sp_lock；（部分）
»10
如果没有指定数据库名，则sp_helpdb报告 master.dbo.sysdatabases中的所有数据库。 【例12-1】返回pubs数据库的信息 exec sp_helpdb pubs 【例12-2】返回有关所有数据库的信息 exec sp_helpdb
sp_addextendedproperty ； sp_helpdevice ；
sp_add_log_file_recover_suspect_db
；
sp_helpextendedproc；
系
sp_addmessage ； sp_helpfile ； sp_addtype ；
统
sp_helpfilegroup；sp_addumpdevice；sp_helpindex； sp_altermessage ； sp_helplanguage ； sp_autostats ；
sp_cursor_list ； sp_describe_cursor_columns ； sp_describe_cursor ； sp_describe_cursor_tables
sp_addlinkedserver ； sp_indexes ； sp_addlinkedsrvlogin ；
sp_linkedservers；sp_catalogs
sp_primarykeys；
sp_droplinkedsrvlogin；sp_foreignkeys
sp_addalias ； sp_droprolemember ； sp_addapprole ； sp_dropserver ；
sp_addgroup；sp_dropsrvrolemember；
sp_addlinkedsrvlogin；sp_dropuser；sp_addlogin；sp_grantdbaccess；
第12章存储过程和用户存储过程设计
➢ 12.1 存储过程概述 ➢ 12.2 系统存储过程 ➢ 12.3 创建和执行用户存储过程 ➢ 12.4 带状态参数的存储过程及实例分析 ➢ 12.5 修改和删除存储过程 ➢ 12.6 存储过程设计实例分析
»2
12.1 存储过程概述
12.1.1 存储过程的概念和分类 SQL Server提供了一种方法，它可以将一