第9章存储过程和触发器 本章导读: 存储过程和触发器都不是标准的SQL语句,不同的 数据库管理系统在具体实现时语法会有所不同 存储过程是一组 Transact-SQL语句和可选控制 语句的预编译集合,它是按名存储并运行于服 务器上,独立于表的数据库对象。而触发器是 种在关系表上定义,伴随用户修改相关数据 而自动执行的特殊存储过程,主要用于强化复 杂的规则和要求。 知识要点: 存储过程 2.触发器
第9章 存储过程和触发器 ⚫ 本章导读： 存储过程和触发器都不是标准的SQL语句，不同的 数据库管理系统在具体实现时语法会有所不同。 存储过程是一组Transact-SQL语句和可选控制 语句的预编译集合，它是按名存储并运行于服 务器上，独立于表的数据库对象。而触发器是 一种在关系表上定义，伴随用户修改相关数据 而自动执行的特殊存储过程，主要用于强化复 杂的规则和要求。 知识要点： 1. 存储过程 2. 触发器
9.1存储过程 存储过程是存储在服务器上的一组预编译好的T sQL代码。存储过程可以通过输入参数接收调 用程序的实参输入,也可以通过输出形参将运 行结果返回给调用程序,还可以通过状态参数 判断存储过程的执行成功与否。用户自定义的 存储过程名存储于系统表 sysobjects中,而存 储过程中定义的文本内容存储于系统表 syscomments中。 ●9.1.1存储过程概述 ●9.12存储过程的创建
9.1 存储过程 存储过程是存储在服务器上的一组预编译好的TSQL代码。存储过程可以通过输入参数接收调 用程序的实参输入，也可以通过输出形参将运 行结果返回给调用程序，还可以通过状态参数 判断存储过程的执行成功与否。用户自定义的 存储过程名存储于系统表sysobjects中，而存 储过程中定义的文本内容存储于系统表 syscomments中。 ⚫ 9.1.1 存储过程概述 ⚫ 9.1.2 存储过程的创建
9.1.1存储过程概述 存储过程是一个独立于表之外的数据库对象,可以作为一个单元被用户 的应用程序调用。执行存储过程时,只需要将参数传递到数据库中 而不需要将整条SQL语句都提交给数据库,从而减少了网络传送流量, 另外,因为省去了执行SQL语句时对SQL进行编译的时间,也提高 程序的运行速度。 SQL Server支持5种类型的存储过程: 1.系统存储过程 SQL Server提供了大量的系统存储过程,很多管理活动都是通过系统存 储过程实现的。系统存储过程名以sp_为前缀,存储在 master数据库 中,用户可以在任何数据库中执行系统存储过程。另外,用户可以在 master数据库中定义sp为前缀的自定义系统存储过程。 2.用户存储过程 是指用户自行创建并存储在用户数据库中的存储过程。为了与系统存储 过程相区别,一般不要将用户存储过程名定义为以sp为前缀的名称。 如果用户自定义的存储过程和系统存储过程同名,那么用户存储过程 永远不执行
9.1.1 存储过程概述 存储过程是一个独立于表之外的数据库对象，可以作为一个单元被用户 的应用程序调用。执行存储过程时，只需要将参数传递到数据库中， 而不需要将整条SQL语句都提交给数据库，从而减少了网络传送流量， 另外，因为省去了执行SQL语句时对SQL进行编译的时间，也提高了 程序的运行速度。SQL Server支持5种类型的存储过程： 1．系统存储过程 SQL Server提供了大量的系统存储过程，很多管理活动都是通过系统存 储过程实现的。系统存储过程名以sp_为前缀，存储在master数据库 中，用户可以在任何数据库中执行系统存储过程。另外，用户可以在 master数据库中定义sp_为前缀的自定义系统存储过程。 2．用户存储过程 是指用户自行创建并存储在用户数据库中的存储过程。为了与系统存储 过程相区别，一般不要将用户存储过程名定义为以sp_为前缀的名称。 如果用户自定义的存储过程和系统存储过程同名，那么用户存储过程 永远不执行
9.1.1存储过程概述 3.临时存储过程 临时存储过程分为局部临时存储过程和全局临时存储过程。 局部临时存储过程名称以#为前缀,存放在 tempdb数据库中,只由 创建并连接的用户使用,当该用户断开连接时将自动删除局部临 时存储过程 全局临时存储过程名称以#为前缀,存放在 tempdb数据库中,允许 所有连接的用户使用,在所有用户断开连接时自动被删除。 4.远程存储过程 位于远程服务器上的存储过程。 5.扩展存储过程 扩展存储过程:利用外部语言(如C语言编写的存储过程,以弥补 SQL Serverl的不足之处,扩展新的功能,扩展存储过程名以xp 为前缀
9.1.1 存储过程概述 3．临时存储过程 临时存储过程分为局部临时存储过程和全局临时存储过程。 局部临时存储过程名称以#为前缀，存放在tempdb数据库中，只由 创建并连接的用户使用，当该用户断开连接时将自动删除局部临 时存储过程。 全局临时存储过程名称以##为前缀，存放在tempdb数据库中，允许 所有连接的用户使用，在所有用户断开连接时自动被删除。 4．远程存储过程 位于远程服务器上的存储过程。 5．扩展存储过程 扩展存储过程：利用外部语言（如C语言编写的存储过程，以弥补 SQL Server的不足之处，扩展新的功能，扩展存储过程名以xp_ 为前缀
912存储过程的创建 SQL Server提供了两种创建存储过程的方法:使用企业 管理器和使用T-SQL语句的 reate Procedure命令。 1.使用 Transact-SQL语句 在 SQL Server中,使用T-SQL语句的 Create Procedure 命令创建存储过程,其格式如下: create procedure];分组编号] [@形式参数数据类型}[=默认值][ output[ /arying [with recompile encryption recompile, encryption) for replication as sq语句[n
9.1.2 存储过程的创建 SQL Server提供了两种创建存储过程的方法：使用企业 管理器和使用T-SQL语句的Create Procedure命令。 1．使用Transact-SQL语句 在SQL Server中，使用T-SQL语句的Create Procedure 命令创建存储过程，其格式如下： create proc[edure] [;分组编号] [{@形式参数 数据类型} [ = 默认值 ] [output] [varying]] [,...n] [with { recompile | encryption | recompile,encryption}] [for replication] as sql语句 [...n]
912存储过程的创建 功能:在当前数据中创建指定名称的存储过程。 说明 (1)存储过程名:存储过程名须符合标识符规则,且对于数据库及其所 有者是唯一的 (2)分组编号:整数,指明同名存储过程的分组编号,以便于一条drop procedure语句删除一组同名存储过程; (3)@形式参数:指明形式参数(形参)名称,形参有输入参数和输 出参数之分; (4)数据类型:指明形参的数据类型,包括text,next和 dimage等数据 类型,当形参是输入参数时,不能使用 cursor(游标)数据类型; (5)默认值:指明输入参数的默认值,可以是常量、NUL或字符匹配 运算符ike关键字,当输入参数定义了默认值后,调用语句可以省略 实参,否则必须提供实参 (6) output:指明形参是输出参数,且允许有返回值,否则为输入参数
9.1.2 存储过程的创建 功能：在当前数据中创建指定名称的存储过程。 说明： （1）存储过程名：存储过程名须符合标识符规则，且对于数据库及其所 有者是唯一的； （2）分组编号：整数，指明同名存储过程的分组编号，以便于一条drop procedure语句删除一组同名存储过程； （3）@形式参数：指明形式参数（形参）名称，形参有输入参数和输 出参数之分； （4）数据类型：指明形参的数据类型，包括text，ntext和image等数据 类型，当形参是输入参数时，不能使用cusor（游标）数据类型； （5）默认值：指明输入参数的默认值，可以是常量、NULL或字符匹配 运算符like关键字，当输入参数定义了默认值后，调用语句可以省略 实参，否则必须提供实参； （6）output：指明形参是输出参数，且允许有返回值，否则为输入参数；
912存储过程的创建 (7) varying:指明返回值是可变的,当形参数据类型为 cursor时需 指定 varying选项; (8) recompilelencryption: recompile表示每次重新编译存储过程, 而 encryption表示加密存储过程文本 (9) for replication:表示创建的存储过程只能在复制过程中执行 而不能在订阅服务器上执行。 for replication和 with encryption不 能联合使用; (10)as:表示指定要执行的操作 (11)sq语句:过程中包含的任意类型和数目的SQL语句,但有 些限制,如不可以使用创建数据库对象的语句
9.1.2 存储过程的创建 （7）varying：指明返回值是可变的，当形参数据类型为cursor时需 指定varying选项； （8）recompile|encryption：recompile表示每次重新编译存储过程， 而encryption表示加密存储过程文本； （9）for replication：表示创建的存储过程只能在复制过程中执行， 而不能在订阅服务器上执行。for replication和with encryption不 能联合使用； （10）as：表示指定要执行的操作； （11）sql语句：过程中包含的任意类型和数目的SQL语句，但有一 些限制，如不可以使用创建数据库对象的语句
912存储过程的创建 【例9-1】创建一个存储过程,用来求任意一个数的阶乘。 use xg if exists (select name from sysobjects where name=fact and type =p) drop proc fac create procedure fact @n int, @f int output as if @n<0 print'你输入了的+cast@ n as varchar(20)+,请输入非负数 else begi declare @i int set @i= set @f=1 while @i<=@n begin set @f=@f@i set @i=@i+1 end print cast@ n as varchar(20)+的阶乘是:'+cast(@ f as varchar(20) end
9.1.2 存储过程的创建 【例9-1】 创建一个存储过程，用来求任意一个数的阶乘。 use jxgl if exists(select name from sysobjects where name='fact' and type ='p') drop proc fact go create procedure fact @n int,@f int output as if @n<0 print '你输入了的'+cast(@n as varchar(20))+'，请输入非负数' else begin declare @i int set @i=1 set @f=1 while @i<=@n begin set @f=@f*@i set @i=@i+1 end print cast(@n as varchar(20))+'的阶乘是：'+cast(@f as varchar(20)) end
912存储过程的创建 2.使用企业管理器创建存储过程 【例9-2】使用企业管理器创建一个打印9乘9 乘法表的存储过程 ●操作步骤如下: (1)在企业管理器中选择数据库jg节点中的 “存储过程”节点,右击弹出快捷菜单,如图 9-1所示,单击“新建存储过程”命令后,弹 出“存储过程属性”对话框,如图9-2所示
9.1.2 存储过程的创建 ⚫ 2．使用企业管理器创建存储过程 ⚫ 【例9-2】 使用企业管理器创建一个打印9乘9 乘法表的存储过程。 ⚫ 操作步骤如下： ⚫ （1）在企业管理器中选择数据库jxgl节点中的 “存储过程”节点，右击弹出快捷菜单，如图 9-1所示，单击“新建存储过程”命令后，弹 出“存储过程属性”对话框，如图9-2所示
912存储过程的创建 SQL Server Enterprise夏magr-控制台,回回区存储过程属性一新建存储过程 险文件)操作③)查看的工具〔)窗口①)帮助0)=常规 中中国回X国品画米N0 〈新建存储过程 权限巴 存储过程31个项目 SHUJU (Windows NT) 所有者 口数据库 名称 所有者 e目izgl 创建日期 关系图 dt_whocheckedout 文本 dt_verstamp007 TE PROCEDURE [OWNER)(PROCEDURE NAMEJASI 6视图 dt_verstamp006 储过程 新建存储过程 npar am 角色 np ar ans dbo 规则 查看① 默认值从这里创建窗口( yid_ u yid 民用户定义 E用户定义 刷新①) urcecontrol dbo 导出列表) 枪查语法①」另存为模板 帮助 确定 取消 图9-1“企业管理器”对话框1 图9-2“存储过程属性”对话框
9.1.2 存储过程的创建 图9-1 “企业管理器”对话框1 图9-2 “存储过程属性”对话框