什么是存储过程? q 存储过程(procedure)类似于C语言中的函数 q 用来执行管理任务或应用复杂的业务规则 q 存储过程可以带参数,也可以返回结果 q 存储过程可以包含数据操纵语句、变量、逻辑 控制语句等 存储过程的优点 (1)执行速度快。 存储过程创建是就已经通过语法检查和性能优化,在执行时无需每次编译。 存储在数据库服务器,性能高。 (2)允许模块化设计。 只需创建存储过程一次并将其存储在数据库中,以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建,并可独立于程序源代码而单独修改 。 (3)提高系统安全性。 可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限,建立特定的存储过程供用户使用,完成对数据的访问。 存储过程的定义文本可以被加密,使用户不能查看其内容。 (4)减少网络流量: 一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现,而不需要在网络中发送数百行代码。 存储过程的分类 q 系统存储过程 q 由系统定义,存放在master数据库中 q 类似C语言中的系统函数 q 系统存储过程的名称都以“sp_”开头或”xp_”开头 q 用户自定义存储过程 q 由用户在自己的数据库中创建的存储过程 q 类似C语言中的用户自定义函数 常用的系统存储过程
EXEC sp_databases /*列出当前系统中的数据库*/ EXEC sp_renamedb 'Northwind','Northwind1' /*修改数据库的名称(单用户访问, 最简单的办法就是执行SQL语句时关掉企业管理器)*/ USE stuDB GO EXEC sp_tables /*当前数据库中查询的对象的列表*/ EXEC sp_columns stuInfo /*返回某个表列的信息*/ EXEC sp_help stuInfo /*查看表stuInfo的信息*/ EXEC sp_helpconstraint stuInfo /*查看表stuInfo的约束*/ EXEC sp_helpindex stuMarks /*查看表stuMarks的索引*/ EXEC sp_helptext 'view_stuInfo_stuMarks' /*查看视图的语句文本*/ EXEC sp_stored_procedures /*查看当前数据库中的存储过程*/ 常用的扩展存储过程 q 常用的扩展存储过程:xp_cmdshell q 可以执行DOS命令下的一些的操作 q 以文本行方式返回任何输出 q 调用语法: q EXEC xp_cmdshell DOS命令 [NO_OUTPUT] USE master GO EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT /*创建文件夹D:\bank*/ IF EXISTS(SELECT * FROM sysdatabases WHERE name='bankDB') DROP DATABASE bankDB GO CREATE DATABASE bankDB ( … ) GO EXEC xp_cmdshell 'dir D:\bank\' --查看文件 /*查看文件夹D:\bank*/ 如何创建存储过程? q 定义存储过程的语法 CREATE PROC[EDURE] 存储过程名 @参数1 数据类型 = 默认值, …… , @参数n 数据类型 OUTPUT AS SQL语句 GO q 和C语言的函数一样,参数可选 q 参数分为输入参数、输出参数 q 输入参数允许有默认值 创建不带参数的存储过程 CREATE PROCEDURE proc_stu /* proc_stu为存储过程的名称*/ AS DECLARE @writtenAvg float,@labAvg float /* 笔试平均分和机试平均分变量 */ SELECT @writtenAvg=AVG(writtenExam), @labAvg=AVG(labExam) FROM stuMarks print '笔试平均分:'+convert(varchar(5),@writtenAvg) print '机试平均分:'+convert(varchar(5),@labAvg) IF (@writtenAvg>70 AND @labAvg>70) print '本班考试成绩:优秀' /* 显示考试成绩的等级 */ ELSE print '本班考试成绩:较差' print '--------------------------------------------------' print ' 参加本次考试没有通过的学员:' SELECT stuName,stuInfo.stuNo,writtenExam,labExam /* 显示未通过的学员 */ FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam<60 OR labExam<60 GO 调用存储过程 q EXECUTE(执行)语句用来调用存储过程 q 调用的语法 EXEC 过程名 [参数] 创建带参数的存储过程 q 存储过程的参数分两种: q 输入参数 q 输出参数 q 输入参数: 用于向存储过程传入值,类似C语言的按值传递; q 输出参数: 用于在调用存储过程后, 返回结果,类似C语言的 按引用传递; 带输入参数的存储过程 问题: 修改上例:由于每次考试的难易程度不一样,每次 笔试和机试的及格线可能随时变化(不再是60分),这导致考试的评判结果也相应变化。 分析: 在述存储过程添加2个输入参数: @writtenPass 笔试及格线 @labPass 机试及格线 CREATE PROCEDURE proc_stu @writtenPass int = 60, /*输入参数:笔试及格线*/ @labPass int = 60 /*输入参数:机试及格线*/ AS print '--------------------------------------------------' print ' 参加本次考试没有通过的学员:' SELECT stuName,stuInfo.stuNo,writtenExam, /*查询没有通过考试的学员*/ labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam<@writtenPass OR labExam<@labPass GO q 调用带参数的存储过程 假定本次考试机试偏难,机试的及格线定为55分,笔试及格线定为60分 EXEC proc_stu 60,55 --或这样调用: EXEC proc_stu @labPass=55,@writtenPass=60 带输出参数的存储过程 q 如果希望调用存储过程后,返回一个或多个值,这时就需要使用输出(OUTPUT)参数了 问题: 修改上例,返回未通过考试的学员人数。 CREATE PROCEDURE proc_stu @notpassSum int OUTPUT, /*输出(返回)参数:表示没有通过的人数*/ @writtenPass int=60, /*推荐将默认参数放在最后*/ @labPass int=60 AS …… SELECT stuName,stuInfo.stuNo,writtenExam, /*统计并返回没有通过考试的学员人数*/ labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam<@writtenPass OR labExam<@labPass SELECT @notpassSum=COUNT(stuNo) FROM stuMarks WHERE writtenExam<@writtenPass OR labExam<@labPass GO q 调用带输出参数的存储过程 /*---调用存储过程----*/ DECLARE @sum int /*调用时必须带OUTPUT关键字 ,返回结果将存放在变量@sum中*/ EXEC proc_stu @sum OUTPUT ,64 print '--------------------------------------------------' IF @sum>=3 /*后续语句引用返回结果*/ print '未通过人数:'+convert(varchar(5),@sum)+ '人, 超过60%,及格分数线还应下调' ELSE print '未通过人数:'+convert(varchar(5),@sum)+ '人, 已控制在60%以下,及格分数线适中' GO 注意:调用时也必须跟随关键字OUTPUT,否则SQL Server将视为输入参数。 处理存储过程中的错误 q 可以使用PRINT语句显示错误信息,但这 些信息是临时的,只能显示给用户 q RAISERROR 显示用户定义的错误信息时 q 可指定严重级别, q 设置系统变量@@ERROR q 记录所发生的错误等 q RAISERROR语句的用法如下: RAISERROR (msg_id | msg_str,severity, state WITH option[,...n]]) • msg_id:在sysmessages系统表中指定用户定义错误信息 • msg_str:用户定义的特定信息,最长255个字符 • severity:定义严重性级别。用户可使用的级别为0–18级 • state:表示错误的状态,1至127之间的值 • option:指示是否将错误记录到服务器错误日志中 RAISERROR 语句每个参数的详细讲解,可以查阅SQL帮助! 问题: 完善上例,当用户调用存储过程时,传入的及格线参数不 在0~100之间时,将弹出错误警告,终止存储过程的执行。 CREATE PROCEDURE proc_stu @notpassSum int OUTPUT, --输出参数 @writtenPass int=60, --默认参数放后 @labPass int=60 --默认参数放后 AS IF (NOT @writtenPass BETWEEN 0 AND 100) OR (NOT @labPass BETWEEN 0 AND 100) /*引发系统错误,指定错误的严重级别16,调用状态为1(默认),并影响@@ERROR系统变量的值 */ BEGIN RAISERROR (‘及格线错误,请指定0-100之间的分 数,统计中断退出',16,1) RETURN ---立即返回,退出存储过程 END …..其他语句同上例,略 GO /*---调用存储过程,测试RAISERROR语句----*/ DECLARE @sum int, @t int EXEC proc_stu @sum OUTPUT ,604 /*笔试及格线误输入604分*/ SET @t=@@ERROR print '错误号:'+convert(varchar(5),@t ) IF @t<>0 /*如果执行了RAISERROR,系统全局@@ERROR将不等于0,表示出现错误*/ RETURN --退出批处理,后续语句不再执行 print '--------------------------------------------------' IF @sum>=3 print '未通过人数:'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调' ELSE print '未通过人数:'+convert(varchar(5),@sum)+ '人,已控制在60%以下,及格分数线适中' GO 好,我们来做个总结: • 存储过程是一组预编译的SQL语句,它可以包含数据操纵语句、变量、逻辑控制语句等 • 存储过程允许带参数,参数分为: – 输入参数 – 输出参数 其中,输入参数可以有默认值。 • 输入参数:可以在调用时向存储过程传递参数,此类参数可用来向存储过程中传入值 • 输出参数从存储过程中返回(输出)值,后面跟随OUTPUT关键字 • RAISERROR语句用来向用户报告错误
SQL Server 支持两种临时过程:局部临时过程和全局临时过程。局部临时过程只能由创建该过程的连接使用。全局临时过程则可由所有连接使用。局部临时过程在当前会话结束 时自动除去。全局临时过程在使用该过程的最后一个会话结束时除去。通常是在创建该过程的会话结束时。 临时过程用 # 和 ## 命名,可以由任何用户创建。创建过程后,局部过程的所有者是唯一可以使用该过程的用户。执行局部临时过程的权限不能授予其他用户。如果创建了全局临时过 程,则所有用户均可以访问该过程,权限不能显式废除。只有在 tempdb 数据库中具有显式 CREATE PROCEDURE 权限的用户,才可以在该数据库中显式创建临时过程(不使用编号符命名)。可以授予或废除这些过程中的权限。 说明 频繁使用临时存储过程会在 tempdb 中的系统表上产生争用,从而对性能产生负面影响。建议使用 sp_executesql 代替。sp_executesql 不在系统表中存储数据,因此可以避免这一问题。 自动执行存储过程 SQL Server 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员创建,并在 sysadmin 固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数。 对启动过程的数目没有限制,但是要注意,每个启动过程在执行时都会占用一个连接。如果必须在启动时执行多个过程,但不需要并行执行,则可以指定一个过程作为启动过程,让该过程调用其它过程。这样就只占用一个连接。 在启动时恢复了最后一个数据库后,即开始执行存储过程。若要跳过这些存储过程的执行,请将启动参数指定为跟踪标记 4022。如果以最低配置启动 SQL Server(使用 -f 标记),则启动存储过程也不会执行。 若要创建启动存储过程,必须作为 sysadmin 固定服务器角色的成员登录,并在 master 数据库中创建存储过程。 使用 sp_procoption 可以: 将现有存储过程指定为启动过程。 停止在 SQL Server 启动时执行过程。 查看 SQL Server 启动时执行的所有过程的列表。 存储过程嵌套 存储过程可以嵌套,即一个存储过程可以调用另一个存储过程。在被调用过程开始执行时,嵌套级将增加,在被调用过程执行结束后,嵌套级将减少。如果超出最大的嵌套级,会使整个调用过程链失败。可用 @@NESTLEVEL 函数返回当前的嵌套级。 若要估计编译后的存储过程大小,请使用下列性能监视计数器。 * 各种分类的高速缓存对象均可以使用这些计数器,包括特殊 sql、准备 sql、过程、触发器等。 sql_statement 限制 除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外(这两个语句必须是批处理中仅有的语句),任何 SET 语句均可以在存储过程内部指定。所选择的 SET 选项在存储过程执行过程中有效,之后恢复为原来的设置。 如果其他用户要使用某个存储过程,那么在该存储过程内部,一些语句使用的对象名必须使用对象所有者的名称限定。这些语句包括: ALTER TABLE CREATE INDEX CREATE TABLE 所有 DBCC 语句 DROP TABLE DROP INDEX TRUNCATE TABLE UPDATE STATISTICS 权限 CREATE PROCEDURE 的权限默认授予 sysadmin 固定服务器角色成员和 db_owner 和 db_ddladmin 固定数据库角色成员。sysadmin 固定服务器角色成员和 db_owner 固定数据库角色成员可以将 CREATE PROCEDURE 权限转让给其他用户。执行存储过程的权限授予过程的所有者,该所有者可以为其它数据库用户设置执行权限。 示例 A. 使用带有复杂 SELECT 语句的简单过程 下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = \'au_info_all\' AND type = \'P\') DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GO au_info_all 存储过程可以通过以下方法执行: EXECUTE au_info_all -- Or EXEC au_info_all 如果该过程是批处理中的第一条语句,则可使用: au_info_all |
|