基于T-SQL的存储过程创建

Size: px

Start display at page:

Download "基于T-SQL的存储过程创建"

短吊凤
4 years ago
Views:

1 基于 T-SQL 的存储过程创建案例分析

2 教学目的本案例的教学, 主要目的在于引导学员掌握基于 T-SQL 语句的存储过程的创建和使用, 使学员真正理解存储过程的强大功能通过从简单到复杂的教学内容设计, 并结合简单具体的例子, 使学员在一个个问题的解决过程中, 不仅掌握了基于 T-SQL 的存储过程的创建和使用方法, 而且增强了分析解决问题的能力, 从而达到培养学员创新意识和创新能力的目标 1

3 教学用途本案例的适用对象为计算机科学与技术信息工程管理工程信息管理与信息系统等专业本科生数据库系统工程师培训班等可以用于数据库原理 SQL Server 数据库技术等课程的存储过程专题的教学 2

4 内容提要本案例以教材中大学教学管理数据库 JXGL 为例, 主要从最简单的不带参数的存储过程的创建和执行开始, 陆续介绍带输入参数同时带输入和输出参数加密过程, 以及错误信息处理等情况下存储过程的创建和使用本案例结合具体的例子, 从简单到复杂引出存储过程创建和使用的语句格式使用方法等, 为学员提供了一个循序渐进地学习和使用存储过程的素材 3

5 案例正文一问题的提出为了完成控制访问权限管理进行数据库审计追踪实现关系到数据库及其所有相关应用程序的数据定义语句与数据操作语句的分隔, 以及完成其它一些具有特定功能的复杂工作,SQL Server 提供了一种独立于数据表之外的称为存储过程 (Stored-Procedure) 的数据库对象但如何构造和使用存储过程, 一直是学生初学感到比较困难的问题二存储过程的概念及特点从概念上来说,SQL Server 的存储过程类似于编程语言中的过程, 它是人们使用 T-SQL 语言的编程方法, 将某些需要多次调用的实现某个特定任务的代码段编写成一个过程, 并将其视为独立的数据库对象保存在数据库中, 由 SQL Server 服务器通过过程名来调用它们由于这样的过程是预先用 T-SQL 语言编写好并用一个指定的名称存储在数据库中的, 所以称为做存储过程显然, 存储过程是 SQL Server 服务器上一组完成特定功能的预编译的 T-SQL 语句集合, 其功能特点包括 : (1) 存储过程可以接受输入参数, 并以输出参数的形式为调用过程或批处理返回多个值 (2) 存储过程包含有执行数据库操作的 T-SQL 编程语句, 4

6 可以调用其他存储过程或嵌套调用 (3) 存储过程可以为调用过程或批处理返回一个状态值, 以表示成功或失败, 以及失败原因创建存储过程语句 CREATE PROCEDURE 的格式为 : CREATE PROC[EDURE] [OWNER.]procedure_name [; number ] [{@parameter data_type } [VARYING] [=default] [OUTPUT]] [,... n ] [WITH { RECOMPILE ENCRYPTION RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [;] [,... n ] [;] 其中 : 1 procedure_name 为指定的要创建的存储过程的名称为过程的参数在 CREATE PROCEDURE 语句中可以声明一个或多个参数 3 data_type 用于指定参数的数据类型 4 VARYING 用于指定作为输出 OUTPUT 参数支持的结果集 5 Default 用于指定参数的默认值 6 OUTPUT 表示该指定的参数是一个返回参数 7 RECOMPILE 可选项用于指明 SQL Server 不会保存该存储过程的执行计划 8 ENCRYPTION 可选项用于表示 SQL Server 加密了 syscomments 表, 该表的 text 字段是包含 CREATE PROCEDURE 语 5

7 句的存储过程文本 9 AS 用于指定该存储过程要执行的操作 10 sql_statement 是存储过程中要包含的任意数目和类型的 Transact-SQL 语句在存储过程创建后, 存储过程的名称存放在 sysobject 表中, 文本存放在 syscomments 表中使用 T-SQL 语句执行 ( 调用 ) 存储过程的语句格式为 : [ EXEC[UTE]] { [@return_status=] procedure_name } [[@parameter=] [OUTPUT] [DEFAULT]}] [,...n ] [WITH RECOMPILE ] 其中 : 1 return_status 代表返回值变量, 是可选的整型变量, 用来保存存储过程向调用者返回的值 2 procedure_name_var 是一变量名, 用于代表存储过程的名字 3 parameter 为参数, 参数是输出参数, 则其后要加选项 OUTPUT 4 WITH RECOMPILE 选项表示下次执行时对其重新编译, 一般用该选项创建一个新的执行规划如果所传递的参数与经常传递给存储过程的参数很不相同时, 则使用该选项 ; 如果数据从存 6

8 储过程被最后一次重编译后进行了较大的改变, 该选项就很有用处三创建存储过程需要做的工作在创建存储过程时, 需要根据拟创建的存储过程要完成的功能, 提前确定以下要素 : (1) 存储过程的输入参数, 要传回给调用者的输出参数 (2) 根据实现的功能要求确定对数据库的操作语句 ; 确定是否要调用其它存储过程 ( 语句 ) (3) 该存储过程返回给调用者的状态值, 以及指明调用是成功还是失败的约定四存储过程的创建与执行为方便起见, 以下所有例子均使用教材中的 JXGL 数据库其它的数据库和应用程序可以依此类推 1 不带输入参数的存储过程例 1-- 创建一个存储过程, 其实现的功能是返回学生的姓名所学课程的课程名和成绩分析 : 这是一个只返回结果集的存储过程, 不使用任何参数创建的存储过程语句为 : -- 简单的存储过程 USE JXGL CREATE PROCEDURE SCG_name 7

9 AS SELECT SNAME, CNAME, GRADE FROM S, SC, C WHERE S. S# = SC.S# AND SC.C# = C.C# 创建完上面的语句后, 使用下面的命令可以执行该存储过程 EXEC SCG_name1 利用查询编辑器窗口创建该存储过程及执行结果如下 : 2 带输入参数的存储过程例 2-- 创建一个存储过程, 其实现的功能是根据某学生的学号返回学生的姓名所学课程的课程名和成绩分析 : 该存储过程要求根据某学生的学号查询相关记录, 有一个输入参数, 该输入参数用来传递学生的学号创建的存储过程语句为 : 8

10 -- 带输入参数的存储过程 USE JXGL CREATE PROCEDURE varchar(9) AS SELECT SNAME, CNAME, GRADE FROM S, SC, C WHERE S.S# AND S. S# = SC.S# AND SC.C# = C.C# 该存储过程有输入参数, 对它的执行可以有两种不同方法 : EXECUTE SCG_name 或 EXECUTE 第一种方法仅仅传入了我们想要的参数值而第二种方法包含了参数名和参数值任意一种方法都可以运行该例中的存储过程, 查询结果都相同, 如下所示 : 9

11 这个例子有没有其它解决方法呢? 实际上, 该例在例 1 的基础上仅做了一点修改 : 传入了一个参数, 根据传入的参数来查询相应的记录于是, 我们转变思路, 不再重新创建一个存储过程, 而是使用 ALTER PROCEDURE 来修改例 1 中已经创建好的存储过程代码如下 : ALTER PROCEDURE varchar(9) AS SELECT SNAME, CNAME, GRADE FROM S, SC, C WHERE S.S# AND S. S# = SC.S# AND SC.C# = C.C# 3 使用带通配符参数的存储过程例 3-- 创建一个存储过程, 其实现的功能是返回名字中有一 10

12 个丽字的学生信息要求使用输入参数分析 : 该例要求实现对姓名的模糊查询, 并要求使用输入参数, 因此可在存储过程中使用通配符参数, 进行模糊查询创建的存储过程语句为 : -- 使用带通配符参数的存储过程 USE JXGL- CREATE PROCEDURE varchar(10) = % 丽 % AS SELECT * FROM S WHERE SNAME 该例创建的存储过程 S_info 有多种执行形式 (1)EXECUTE S_info % 丽 % 其执行结果如下 : 11

13 (2)EXECUTE S_info -- 参数使用默认值查询结果如下 : (3)EXECUTE S_info 杨 % 查询结果如下 : - 的实参为杨 % 12

14 4 带输入和输出参数的存储过程例 4-- 创建一个存储过程, 其实现的功能是根据某学生的学号返回其所学的课程门数要求使用输入和输出参数分析 : 该例要求使用输入和输出参数, 因此可用输入参数传递查询条件学号的值, 用输出参数传递查询结果学生所学课程门数的值创建的存储过程语句为 : -- 带输入和输出参数的存储过程 USE JXGL- CREATE PROCEDURE int OUTPUT AS FROM SC 13

WHERE S# = @s_number 编写一个批处理, 调用 C_s 存储过程 : DECLARE @xuehao varchar(9),@total int SET @xuehao= 200401001 EXECUTE C_s @xuehao,@total OUTPUT SELECT @xuehao AS 学号,@total AS 所学课程数

15 WHERE S# 编写一个批处理, 调用 C_s 存储过程 : varchar(9),@total int EXECUTE OUTPUT AS 学号,@total AS 所学课程数执行结果如下 : 注意 : 在创建存储过程和使用 OUTPUT 变量时, 都必须对 OUTPUT 变量进行定义 ; 存储过程定义时的形参名 (@s_number) 和调用时的变量名 (@xuehao) 不一定要相同, 但数据类型和参数位置必须匹配 ( 这里都是第一个参数, 且数据类型的定义为 varchar(9)) 5 使用 WITH ENCRIPTION 选项 14

16 例 5 创建加密过程, 使用 sp_helptext 系统存储过程获取关于加密的存储过程的信息分析 : 使用 WITH ENCRIPTION 选项可对用户隐藏存储过程的文本, 通过系统存储过程 sp_helptext 可显示规则默认值未加密的存储过程用户自定义函数触发器或视图的文本 USE JXGL CREATE PROCEDURE varchar(9) WITH ENCRIPTION AS SELECT SNAME, CNAME, GRADE FROM S, SC, C WHERE S.S# AND S. S# = SC.S# AND SC.C# = C.C# 执行语句 : EXEC sp_helptext SCG_name 结果如下 : 15

17 若在创建存储过程时, 未使用 WITH ENCRIPTION 选项, 则结果如下 : 6 错误信息处理 16

18 为了增强存储过程的效率, 应使用错误信息向用户传达事务状态 ( 成功或失败 ), 可以在错误处理逻辑中检查下列错误 : 返回码 SQLServer 错误用户定义的错误信息 (1)RETURN 语句从查询或存储过程无条件返回, 同时可以返回一个整数状态值 ( 返回码 ), 返回码为 0 表示成功若用户不提供返回码, 则返回 SQL Server 的返回码用户定义的返回码优先级高于系统提供的返回码例如 : 创建一个存储过程, 其实现的功能是根据某学生的学号返回其所学的课程门数要求使用输入和输出参数 USE JXGL CREATE PROCEDURE int OUTPUT AS FROM SC WHERE S# 17

编写一个批处理, 调用 C_s 存储过程 : DECLARE @xuehao varchar(9), @total datetime SET @xuehao= 200401001 EXECUTE C_s @xuehao,@total OUTPUT

19 编写一个批处理, 调用 C_s 存储过程 : datetime EXECUTE OUTPUT AS 学号,@total AS 所学课程数 RETURN(@@ROWCOUNT) 的数据类型声明为 datetime, 与存储过程 C_s 中原来的 int 类型不符, 执行结果如下 : 18

20 函数系统函数包含了最近执行的 T-SQl 语句的错误号, 随着每一条语句的执行而更新例如 : 查询和王丽丽在同一个班的女同学的信息 USE JXGL -- 打开 JXGL 数据库 CHAR(2),@classl CHAR(6) -- 声明局部变量 = ' 女 =CLASS FROM S WHERE SNAME= 王丽丽 19

$(@@error) 结果如下 : 返回的错误号为 0, 表示没有错误, 成功执行 (3)RAISERROR 语句返回用户定义的错误信息并设系统标志, 记录发生的错误语句格式为 : RAISERROR({msg_id msg_str @local_variable} {,severity,state} [,argument[, n]]) [WITH option[, n]]$

21 -- 根据局部变量值进行查询 SELECT S# AS 学号, SNAME AS 姓名, SBIRTHIN AS 出生年月, PLACEOFB AS 籍贯, SCODE# AS 专业编号, CLASS AS 班级 FROM S WHERE SSEX AND CLASS=@classl 若想查看最近执行的 T-SQl 语句的错误号, 则执行 : PRINT (@@error) 结果如下 : 返回的错误号为 0, 表示没有错误, 成功执行 (3)RAISERROR 语句返回用户定义的错误信息并设系统标志, 记录发生的错误语句格式为 : RAISERROR({msg_id {,severity,state} [,argument[, n]]) [WITH option[, n]] 其中 : 20

22 (1){ msg_id } msg_id: 表示可以是一个 sys.messages 表中定义的消息代号 ; 使用 sp_addmessage 存储在 sys.messages 目录视图中的用户定义错误消息号 ; 用户定义错误消息的错误号应当大于 msg_str: 表示也可以是一个用户定义消息, 该错误消息最长可以有 2047 个字符 ;( 如果是常量, 请使用 N'xxxx', 因为是 nvarchar 的 ) 当指定 msg_str 时,RAISERROR 将引发一个错误号为 5000 表示也可以是按照 msg_str 方式的格式化字符串变量 (2)severity 用户定义的与该消息关联的严重级别 ( 这个很重要 ) 任何用户都可以指定 0 到 18 之间的严重级别 [0,10] 的闭区间内, 不会跳到 catch; 如果是 [11,19], 则跳到 catch; 如果是 [20, 无穷 ), 则直接终止数据库连接. (3) state 如果在多个位置引发相同的用户定义错误, 则针对每个位置使用唯一的状态号有助于找到引发错误的代码介于 1 至 127 之间的任意整数 (state 默认值为 1), 当 state 值为 0 或大于 127 时会生成错误! (4)argument 用于代替 msg_str 或对应于 msg_id 的消息中的定义的变量的参数 21

23 (5)option 错误的自定义选项, 可以是下表中的任一值 : LOG : 在错误日志和应用程序日志中记录错误 ; NOWAIT: 将消息立即发送给客户端 ; SETERROR: 值和 ERROR_NUMBER 值设置为 msg_id 或 50000; 例如 : 创建一个存储过程, 其实现的功能是根据某学生的学号返回其所学的课程门数要求使用输入和输出参数 USE JXGL CREATE PROCEDURE int OUTPUT AS FROM SC WHERE S# 执行结果如下 : 22

24 编写一个批处理, 调用 C_s 存储过程 : varchar(9),@total datetime EXECUTE OUTPUT AS 学号,@total AS 所学课程数 RAISERROR( 输出参数类型错误,10,1) RETURN -- 立即返回, 退出存储过程执行结果过如下 : 23

25 (4)sp_addmessage 系统存储过程允许开发者创建用户定义错误信息, 指定消息号严重级别和消息文本, 可设定为错误信息自动记录到 Windows 应用程序日志中, 可在 SYS.MESSAGES 中查看五结束语通过上述例子的学习, 相信大家已能初步掌握基于 T-SQL 的存储过程创建与使用, 如果能够在查询窗口或者应用程序中得心应手的使用查询语句 (SELECT 语句 ), 那么创建一个 SQL Server 的存储过程就不会感到困难了参考文献 24

26 1. 李俊山, 罗蓉, 赵方舟. 数据库原理及应用 (SQL Server). 北京 : 清华大学出版社, 郑阿奇主编 SQL Server 教程北京 : 清华大学出版社, / shtml d.html html 25

第4单元创建数据类型和表

第4单元创建数据类型和表 138 MCSE 2000 SQL 2000 8.1 Stored Procedures Microsoft SQL Server 2000 ( ) 8.1.1 Transact-SQL SQL Server (System Stored Procedures,sp_), master ( sp_prefix ) (Local Stored Procedures), (Temporary Stored