2019年SQL server 2005案例教程 第6章 存储过程与触发器新.ppt
《2019年SQL server 2005案例教程 第6章 存储过程与触发器新.ppt》由会员分享,可在线阅读,更多相关《2019年SQL server 2005案例教程 第6章 存储过程与触发器新.ppt(41页珍藏版)》请在三一文库上搜索。
1、l l l 第 6 章存储过程与触发器 6.1 【案例 19】存储过程 相关知识 1存储过程的类型 (1)存储过程特点 存储过程是存储在 SQL Server 2005 服务器上、一种有效的封装重复性工作的方法,并具有支持用户声明的 变量、条件执行和其他强大的编程功能。与其他编程语言中的存储过程类似,SQL Server 2005 中的存储过程具 有如下特点。 接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。 包含用于在数据库中执行操作的编程语句。 向调用过程或批处理返回状态值,以指明成功或失败及失败的原因。 (2)用户自定义存储过程 用户自定义存储过程是由用户创建并能完成某一种特
2、定功能的存储过程。SQL Server 2005 可以使用的两种 自定义存储过程的类型为 Transact-SQL 和 CLR,具体说明如表 6-1-1 所示。 2019/5/231 (3)扩展存储过程 扩展存储过程是以在 SQL Server 2005 环境外执行的动态链接库(Dynamic-Link Libraries,DLL)来实现。 一般以 xp_为前缀标识。 (4)系统存储过程 在安装 SQL Server 2005 时,系统创建了很多系统存储过程,存储在 master 和 msdb 数据库中,并以 sp_为 前缀,系统存储过程主要是从系统表中获取信息,为系统管理员管理 SQL Se
3、rver 2005 提供支持。通过系统存储 过程,SQL Server 2005 中的许多管理性或信息性的活动(如了解数据库对象、数据库信息)都可以顺利有效地 完成。 在 SQL Server 2005 中,许多管理活动和信息活动都可以使用系统存储过程来执行,系统存储过程的分类如 表 6-1-2 所示。 (5)临时存储过程 存储在 tempdb 数据库中,以#和#为前缀的过程,#表示本地临时存储过程,#表示全局临时存储过程。 (6)远程存储过程 是在远程服务器的数据库中创建和存储过程,可被各种服务器访问,向具有相应许可权限的用户提供服务。 2创建存储过程的规则 2019/5/232 在设计和创
4、建存储过程时,应该满足一定的约束和规则,只有满足了这些约束和规则才能创建有效的存储过 程。设计存储过程应遵守以下规则。 所有数据库对象(除存储过程)均可在存储过程中创建,只要该对象被创建就可被引用。 可以在存储过程内引用临时表。 如果在存储过程内创建了本地临时表,则该临时表仅为该存储过程存在,退出该存储过程后,临时表将 消失。 如果执行的存储过程中调用另一个存储过程,则被调用的存储过程可以访问由一个存储过程创建的所有 对象,包括临时表在内。 远程存储过程不参与事务处理,如果执行对远程 SQL Server 2005 实例进行更改的远程存储过程,不能 回滚这些更改。 存储过程中的参数的最大数目为
5、 2100。 存储过程中的局部变量的最大数目仅受可用内存的权限。 根据可用内存的不同,存储过程最大为 128MB。 使用 CREATE PROCEDURE 定义存储过程,可以包括任意数量和类型的 SQL 语句,但不能在存储过程中使用 以下语句,如表 6-1-3 所示。 2019/5/233 3使用 Transact-SQL 语句创建存储过程 在 SQL Server 2005 系统中,可以使用 Transact-SQL 语句中的 CREATE PROCEDURE 创建存储过程。具体操作 步骤如下:首先编写并测试一个要在存储过程内执行的 SQL 查询语句。然后使用 CREATE PROCEDUR
6、E 语句创建, 可以使用 ALTER PROCEDURE 语句修改。 存储过程的定义包括两个主要内容:过程名和参数的说明以及过程体,即包含执行存储过程操作的 Transact-SQL 语句在创建存储过程时,应该指定所有的输入参数、执行数据库操作的编程语句、返回至调用过 程或批处理时以示成功或失败的状态值、捕获和处理潜在错误时的错误处理语句等。 (1)语法 使用 CREATE PROCEDURE 语句创建存储过程的语法如下所示。 CREATE PROCEDURE 存储过程名 ; number 参数 1 数据类型VARYING= 默认值OUTPUT, 参数 n 数据类型= 默认值OUTPUT AS
7、 SQL 语句 (n) 2019/5/234 l l l l l l (2)主要参数说明 过程名称在架构中必须唯一,可在存储过程名前面使用一个数字符合(#)来创建局部临时过程,使用两个 数字符号(#)来创建全局临时过程。对于 CLR 存储过程,不能指定临时名称。 ;number为可选整数,用来对同名的过程分组,使用一个 DRIP PROCEDURE 语句可将这些分组过程一起删 除。如果名称中包含分隔标识符,则数字不应包含在标识符中,只应在存储过程名前后使用适当的分隔符。 参数:是指过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。除非定义了参数的默 认值或者将参
8、数设置为等于另一个参数,否则用户必须在调用过程时为每个声明的参数提供值,如果指定 了 FOR REPLICATION,则无法声明参数。 数据类型:是指参数的数据类型,存储过程中可以使用所有数据类型。如果指定的数据类型为 crusor,只 能用于 OUTPUT 参数,而且同时指定 VARYING 和 OUTPUT 关键字。对于 CLR 存储过程,不能指定 char、varchar、 text、ntext、image、cursor 和 table 数据类型作为参数。 VARYING:指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化)。仅适用于游标参数。 默认值:是指参数的默认值。如果
9、定义了该值,则不需要指定此参数的值即可执行过程。默认值必须是常 量或 NULL。如果过程使用带 like 关键字的参数,则可包含%、_、和通配符。 2019/5/235 l l OUTPUT:是指该参数是输出参数。此选项的值可以返回给调用 EXECUTE 的语句。使用 OUTPUT 参数将值返回 该过程的调用方。 SQL 语句:是指包含在过程中的一个或多个 Transact-SQL 语句。 4使用 Transact-SQL 语句执行存储过程 可以使用 Transact-SQL 语句中的 EXECUTE 语句执行存储过程,如果存储过程是批处理中的第一条语句,那 么不使用 EXECUTE 的关键字
10、也可以执行该存储过程,EXECUTE 的语法格式如下。 EXECUTE 存储过程名 参数值列表 5使用 Transact-SQL 语句修改、查看、删除存储过程 (1)修改存储过程 使用 ALTER PROCEDURE 语句可以更改先前通过 CREATEPROCEDURE 语句创建的过程,语句格式如下所示。 ALTER PROCEDURE 存储过程名 ; number 参数 1 数据类型VARYING= 默认值OUTPUT, 参数 n 数据类型= 默认值OUTPUT 2019/5/236 AS SQL 语句 (n) (2)查看存储过程 (3)删除存储过程 使用 DROP PROCEDURE 可以
11、删除存储过程,其基本语句格式如下所示。 DROP PROCEDURE 存储过程名 6存储过程中的输入/输出参数 (1)参数的含义 参数是指在存储过程以及应用程序之间交换数据的值,SQL Server 2005 中存储过程可以使用两种类型的参 数:输入参数和输出参数。输入参数允许用户将数据值传递到存储过程或函数;输出参数允许存储过程将数据值 或游标变量传递给用户。每个存储过程可以向用户返回一个整数代码,如果存储过程没有显示设置返回代码的值, 则返回代码为 0。 参数在创建存储过程时,在 CREATE PROCEDURE 和 AS 关键字之间定义,并且要为其指定参数名和数据类型, 参数名必须以符号
12、为前缀,可以为参数指定默认值;如果是输出参数,则应用 OUTPUT 关键字描述。各个参数定 义之间用逗号隔开,具体语法如下所示。 参数名 数据类型=默认值OUTPUT 2019/5/237 l l (2)输入参数 输入参数,相当于存储过程中的一个条件,在执行存储过程时,可以为这个条件指定值,通过存储过程返回 相应的信息。使用输入参数可以向同一存储过程多次查找数据库。 执行带有输入参数的存储过程时,SQL Server 2005 提供了两种传递参数的方式。 直接按值传递:在执行存储过程的语句中,直接给出参数的值。当有多个参数时,给出参数的顺序与创建 存储过程的语句中的参数顺序一致,即参数传递的顺
13、序就是参数定义的顺序。使用这种方式执行存储过程 的代码如下所述。 EXEC 存储过程名 参数值 通过参数名传递:在执行存储过程的语句中,使用“参数名=参数值”的形式给出参数值。使用这种方式,参 数可按任意顺序给出。使用这种方式执行存储过程的代码如下所述。 EXEC 存储过程参数名=参数值 (3)使用默认参数值 执行存储过程时,如果没有指定参数,系统运行就会出错,如果希望不给出参数时也能正确运行,可以通过 设置参数的默认值来实现。 (4)输出参数 2019/5/238 通过定义输出参数,可从存储过程中返回一个或多个值。如果使用输出参数,必须在 CREATE PROCEDURE 语 句和 EXEC
14、UTE 语句中指定关键字 OUTPUT;如果忽略了 OUTPUT 关键字,存储过程仍然会执行但没有返回值。 (5)存储过程的返回值 存储过程在执行后都会返回一个整型值。如果执行成功,返回 0;否则返回-1-99 之间的随机数,也可以 使用 RETURN 语句来指定一个存储过程的返回值。 6.2 【案例 20】触发器 触发器(Trigger)是一种特殊类型的存储过程,与表紧密结合,只要对它所保护的数据进行修改,它就会 自动触发,包括对表进行 INSERT、UPDATE 和 DELETE 操作,通过实现复杂的业务规则,更有效地实施数据完整性。 相关知识 1触发器概述 (1)触发器的概念 触发器是一
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 2019年SQL server 2005案例教程 第6章 存储过程与触发器新 2019 SQL 2005 案例 教程 存储 过程 触发器
链接地址:https://www.31doc.com/p-2823623.html