第11章存储过程和触发器
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
数据库原理与应用教程
第11章 存储过程和触发器
第11章 存储过程和触发器
在SQL Server 2005应用操作中，存储过程和触 发器都扮演着相当重要的角色。
存储过程可以使用户对数据库的管理工作变得 更容易。当开发一个应用程序时，为了易于修 改和扩充，经常会将负责不同功能的语句集中 起来而且按照用途分别独立放置，以便能够反 复调用，而这些独立放置且拥有不同功能的语 句，即是“过程”(Procedure)。
ห้องสมุดไป่ตู้
11.1.4 执行存储过程
【例11-7】执行存储过程student_avg。 EXECUTE student_avg 【例11-8】执行带参数的存储过程GetStudent，查询 1302001 号学生的基本信息。 EXECUTE GetStudent '1302001' 【例11-9】执行修改成绩的存储过程Update_score，将 1302001号学生选修的C001号课程的成绩改为100。 EXECUTE Update_score '1302001','C001',100 【例11-10】执行修改单价的存储过程Update_price，如果 “ds_018”号商品的库存超过20，则单价打九折。 EXECUTE Update_price 'ds-018',20
11.1.3 创建存储过程
2．利用T-SQL语句创建存储过程 CREATE { PROC | PROCEDURE } procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } [ ,...n ] ] [FOR REPLICATION] AS sql_statement [ ...n ]
11.1.3 创建存储过程
【例11-2】在“teaching”库创建带参数的存储过程， 查询某个学生的基本信息。 USE teaching GO CREATE PROCEDURE GetStudent @number char(7) AS SELECT * FROM student WHERE sno= @number GO
11.1.1 存储过程概述
存储过程是一种独立存储在数据库内的对象，可以接 受输入参数、输出参数，返回单个或多个结果集以及 返回值，由应用程序通过调用执行。存储过程可以由 客户调用，也可以从另一个过程或触发器调用，参数 可以被传递和返回，出错代码也可以被检验。 存储过程最主要的特色是当写完一个存储过程后即被 翻译成可执行码存储在系统表内，当作是数据库的对 象之一，一般用户只要执行存储过程，并且提供存储 过程所需的参数就可以得到所要的结果而不必再去编 辑T-SQL命令。
11.1.3 创建存储过程
【例11-4】使用流程控制语句，在“inventory”数据库创建存 储过程，修改某商品的单价，如果库存总量大于某个值，就 打九折。 USE inventory GO CREATE PROCEDURE Update_price @gno char(3), @s float AS IF (SELECT sum(number) FROM invent WHERE gno= @gno )> @s UPDATE goods SET price=price*0.9 WHERE gno= @gno
11.1.1 存储过程概述
一般来讲，应使用SQL Server中的存储过程而不使用存储在 客户计算机本地的 T-SQL 程序，其优势主要表现在： (1)模块化程序设计。只需创建一次并将其存储在数据库中， 以后即可在程序中调用该过程任意次。 (2)加快T-SQL语句的执行速度。如果某操作需要大量 TSQL 语句或需重复执行，存储过程比批处理代码执行要快。 (3)减少网络流量。一个需要数百行 T-SQL 语句的操作由 一条执行过程代码的单独语句就可实现，而不需要在网络中 发送数百行代码。 (4)可作为安全机制使用。数据库用户可以通过得到权限来 执行存储过程，而不必给予用户直接访问数据库对象的权限。
11.1.3 创建存储过程
【例11-1】在“teaching”库创建无参存储过程，查 询每个学生各门课程的平均成绩。 USE teaching GO CREATE PROCEDURE student_avg AS SELECT sno, avg(score) as 'avgstore' FROM sc GROUP BY sno GO
11.1.3 创建存储过程
【例11-5】在“teaching”库创建带有参数和默认值（通配符） 的存储过程，从“student”表中返回指定的学生（提供姓名） 的信息。该存储过程对传递的参数进行模式匹配，如果没有 提供参数，则返回所有学生的信息。 USE teaching GO CREATE PROCEDURE Student_Name @sname varchar(40) = '%' AS SELECT * FROM student WHERE sname LIKE @sname GO
11.1.2 存储过程的类型
1.系统存储过程 存储过程在运行时生成执行方式，其后在运行时执行速 度很快。SQL Server 2005中的许多管理活动都是通过一 种特殊的存储过程执行的，这种存储过程被称为系统存 储过程。系统过程主要存储在master数据库中并以sp_为 前缀，并且系统存储过程主要是从系统表中获取信息， 从而为数据库系统管理员管理SQL Server提供支持。通 过系统存储过程，SQL Server中的许多管理性或信息性 的活动（如获取数据库和数据库对象的信息）都可以被 顺利有效地完成。
第11章 存储过程和触发器
触发器是一种特殊类型的存储过程。当有操作影响到触 发器保护的数据时，触发器就会自动触发执行。触发器 是与表紧密联系在一起的，它在特定的表上定义，并与 指定的数据修改事件相对应，它是一种功能强大的工具， 它可以扩展SQL Server完整性约束默认值对象和规则 的完整性检查逻辑，实施更为复杂的数据完整性约束。 本章主要介绍存储过程的基本概念，存储过程的创建、 修改、调用和删除操作；触发器的基本概念，触发器的 分类，触发器的创建、修改和删除，以及触发器的应用。
11.1.4 执行存储过程
2. 使用T-SQL 语句执行存储过程 EXECUTE可以简写为EXEC，如果存储过程是批处理中的 第一条语句，那么可以省略EXECUTE关键字。对于以sp_开 头的系统存储过程，系统将在master数据库中查找。如果执 行用户自定义的sp_开头的存储过程，就必须用数据库名和所 有者名限定。 EXECUTE语句的语法格式为： [ [EXEC[UTE] ] [@return_status=] procedure_name[;number] {[[@parameter=]value | [@ parameter=] @variable [OUTPUT]]} [WITH RECOMPILE ]
11.1.3 创建存储过程
在SQL Server 2005中创建存储过程主要有两种 方式：
一种方式是在SQL Server Management Studio中 创建存储过程；另一种方式是通过在查询窗口中 执行T-SQL语句创建存储过程。
11.1.3 创建存储过程
(1)打开SQL Server Management Studio，展开要创 建存储过程的数据库，展开“可编程性”选项，可 以看到存储过程列表中系统自动为数据库创建的系 统存储过程。右键单击“存储过程”选项，选择 “新建存储过程”命令。 (2)出现创建存储过程的T-SQL命令，编辑相关的命 令即可。 (3)命令编辑成功后，进行语法检查，然后单击“！” 按钮，存储过程建立成功。
11.1.3 创建存储过程
【例11-6】在“inventory”数据库创建带OUTPUT参数的存 储过程，用于计算指定的商品的平均价格，存储过程中使用 一个输入参数（商品名）和一个输出参数（平均价格）。 USE inventory GO CREATE PROCEDURE avgprice @gn varchar(20), @avgp int OUTPUT AS SELECT @avgp= avg(price) FROM goods WHERE gname=@gn GO
11.1.3 创建存储过程
【例11-3】在“teaching”库创建带参数的存储过程， 修改某个学生某门课的成绩。 USE teaching GO CREATE PROCEDURE Update_score @number char(7),@cno char(4),@score int AS UPDATE sc SET score=@score WHERE sno= @number and cno=@cno
11.1.2 存储过程的类型
2．本地存储过程 本地存储过程也就是用户自行创建并存储在 用户数据库中的存储过程，一般所说的存储过程 指的就是本地存储过程。 用户创建的存储过程是由用户创建并能完成 某一特定功能(如查询用户所需的数据信息)的存 储过程。
11.1.2 存储过程的类型
3．临时存储过程
11.1.4 执行存储过程
执行存储过程即调用存储过程，可以使用SQL Server Management Studio界面，也可以使用T-SQL 语句中的 EXECUTE命令。 1．使用SQL Server Management Studio执行存储过程 (1)打开SQL Server Management Studio，展开存储过程所 在的数据库，展开“可编程性”选项，右键单击存储过程名， 如“teaching”中的“GetStudent”，在弹出的快捷菜单中选择 “执行存储过程”命令。 (2)进入“执行过程”对话框，输入要查询的学生的sno，如 “1302001” 。 (3)单击“确定”按钮。
(1)本地临时存储过程 不论哪一个数据库是当前数据库，如果在创建存储过程 时，其名称以“#”号开头，则该存储过程将成为一个存 放在tempdb数据库中的本地临时存储过程。 (2)全局临时存储过程 不论哪一个数据库是当前数据库，只要所创建的存储过 程名称是以两个“#”号开头，则该存储过程将成为一个 存储在tempdb数据库中的全局临时存储过程。
11.1 存储过程
SQL Server 2005的存储过程（Stored Procedure）就是一个 具有独立功能的子程序，以特定的名称存储在数据库中。 可以在存储过程中声明变量、有条件地执行以及其他各项 强大的程序设计功能。
11.1.1 存储过程概述
存储过程是T-SQL语句和可选流程控制语句的预编译集合， 它以一个名称存储并作为一个单元处理，能够提高系统的 应用效率和执行速度。SQL Server提供了许多系统存储过 程以管理SQL Server和显示有关数据库和用户的信息。
11.1.2 存储过程的类型
4．远程存储过程 在SQL Server 2005中，远程存储过程是位于远程 服务器上的存储过程，通常可以使用分布式查询和 EXECUTE命令执行一个远程存储过程。 5．扩展存储过程 扩展存储过程是用户可以使用外部程序语言（例如 C语言）编写的存储过程。显而易见，扩展存储过 程可以弥补SQL Server 2005的不足，并按需要自行 扩展其功能。
第11章 存储过程和触发器
第11章 存储过程和触发器
在SQL Server 2005应用操作中，存储过程和触 发器都扮演着相当重要的角色。
存储过程可以使用户对数据库的管理工作变得 更容易。当开发一个应用程序时，为了易于修 改和扩充，经常会将负责不同功能的语句集中 起来而且按照用途分别独立放置，以便能够反 复调用，而这些独立放置且拥有不同功能的语 句，即是“过程”(Procedure)。
ห้องสมุดไป่ตู้
11.1.4 执行存储过程
【例11-7】执行存储过程student_avg。 EXECUTE student_avg 【例11-8】执行带参数的存储过程GetStudent，查询 1302001 号学生的基本信息。 EXECUTE GetStudent '1302001' 【例11-9】执行修改成绩的存储过程Update_score，将 1302001号学生选修的C001号课程的成绩改为100。 EXECUTE Update_score '1302001','C001',100 【例11-10】执行修改单价的存储过程Update_price，如果 “ds_018”号商品的库存超过20，则单价打九折。 EXECUTE Update_price 'ds-018',20
11.1.3 创建存储过程
2．利用T-SQL语句创建存储过程 CREATE { PROC | PROCEDURE } procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } [ ,...n ] ] [FOR REPLICATION] AS sql_statement [ ...n ]
11.1.3 创建存储过程
【例11-2】在“teaching”库创建带参数的存储过程， 查询某个学生的基本信息。 USE teaching GO CREATE PROCEDURE GetStudent @number char(7) AS SELECT * FROM student WHERE sno= @number GO
11.1.1 存储过程概述
存储过程是一种独立存储在数据库内的对象，可以接 受输入参数、输出参数，返回单个或多个结果集以及 返回值，由应用程序通过调用执行。存储过程可以由 客户调用，也可以从另一个过程或触发器调用，参数 可以被传递和返回，出错代码也可以被检验。 存储过程最主要的特色是当写完一个存储过程后即被 翻译成可执行码存储在系统表内，当作是数据库的对 象之一，一般用户只要执行存储过程，并且提供存储 过程所需的参数就可以得到所要的结果而不必再去编 辑T-SQL命令。
11.1.3 创建存储过程
【例11-4】使用流程控制语句，在“inventory”数据库创建存 储过程，修改某商品的单价，如果库存总量大于某个值，就 打九折。 USE inventory GO CREATE PROCEDURE Update_price @gno char(3), @s float AS IF (SELECT sum(number) FROM invent WHERE gno= @gno )> @s UPDATE goods SET price=price*0.9 WHERE gno= @gno
11.1.1 存储过程概述
一般来讲，应使用SQL Server中的存储过程而不使用存储在 客户计算机本地的 T-SQL 程序，其优势主要表现在： (1)模块化程序设计。只需创建一次并将其存储在数据库中， 以后即可在程序中调用该过程任意次。 (2)加快T-SQL语句的执行速度。如果某操作需要大量 TSQL 语句或需重复执行，存储过程比批处理代码执行要快。 (3)减少网络流量。一个需要数百行 T-SQL 语句的操作由 一条执行过程代码的单独语句就可实现，而不需要在网络中 发送数百行代码。 (4)可作为安全机制使用。数据库用户可以通过得到权限来 执行存储过程，而不必给予用户直接访问数据库对象的权限。
11.1.3 创建存储过程
【例11-1】在“teaching”库创建无参存储过程，查 询每个学生各门课程的平均成绩。 USE teaching GO CREATE PROCEDURE student_avg AS SELECT sno, avg(score) as 'avgstore' FROM sc GROUP BY sno GO
11.1.3 创建存储过程
【例11-5】在“teaching”库创建带有参数和默认值（通配符） 的存储过程，从“student”表中返回指定的学生（提供姓名） 的信息。该存储过程对传递的参数进行模式匹配，如果没有 提供参数，则返回所有学生的信息。 USE teaching GO CREATE PROCEDURE Student_Name @sname varchar(40) = '%' AS SELECT * FROM student WHERE sname LIKE @sname GO
11.1.2 存储过程的类型
1.系统存储过程 存储过程在运行时生成执行方式，其后在运行时执行速 度很快。SQL Server 2005中的许多管理活动都是通过一 种特殊的存储过程执行的，这种存储过程被称为系统存 储过程。系统过程主要存储在master数据库中并以sp_为 前缀，并且系统存储过程主要是从系统表中获取信息， 从而为数据库系统管理员管理SQL Server提供支持。通 过系统存储过程，SQL Server中的许多管理性或信息性 的活动（如获取数据库和数据库对象的信息）都可以被 顺利有效地完成。
第11章 存储过程和触发器
触发器是一种特殊类型的存储过程。当有操作影响到触 发器保护的数据时，触发器就会自动触发执行。触发器 是与表紧密联系在一起的，它在特定的表上定义，并与 指定的数据修改事件相对应，它是一种功能强大的工具， 它可以扩展SQL Server完整性约束默认值对象和规则 的完整性检查逻辑，实施更为复杂的数据完整性约束。 本章主要介绍存储过程的基本概念，存储过程的创建、 修改、调用和删除操作；触发器的基本概念，触发器的 分类，触发器的创建、修改和删除，以及触发器的应用。
11.1.4 执行存储过程
2. 使用T-SQL 语句执行存储过程 EXECUTE可以简写为EXEC，如果存储过程是批处理中的 第一条语句，那么可以省略EXECUTE关键字。对于以sp_开 头的系统存储过程，系统将在master数据库中查找。如果执 行用户自定义的sp_开头的存储过程，就必须用数据库名和所 有者名限定。 EXECUTE语句的语法格式为： [ [EXEC[UTE] ] [@return_status=] procedure_name[;number] {[[@parameter=]value | [@ parameter=] @variable [OUTPUT]]} [WITH RECOMPILE ]
11.1.3 创建存储过程
在SQL Server 2005中创建存储过程主要有两种 方式：
一种方式是在SQL Server Management Studio中 创建存储过程；另一种方式是通过在查询窗口中 执行T-SQL语句创建存储过程。
11.1.3 创建存储过程
(1)打开SQL Server Management Studio，展开要创 建存储过程的数据库，展开“可编程性”选项，可 以看到存储过程列表中系统自动为数据库创建的系 统存储过程。右键单击“存储过程”选项，选择 “新建存储过程”命令。 (2)出现创建存储过程的T-SQL命令，编辑相关的命 令即可。 (3)命令编辑成功后，进行语法检查，然后单击“！” 按钮，存储过程建立成功。
11.1.3 创建存储过程
【例11-6】在“inventory”数据库创建带OUTPUT参数的存 储过程，用于计算指定的商品的平均价格，存储过程中使用 一个输入参数（商品名）和一个输出参数（平均价格）。 USE inventory GO CREATE PROCEDURE avgprice @gn varchar(20), @avgp int OUTPUT AS SELECT @avgp= avg(price) FROM goods WHERE gname=@gn GO
11.1.3 创建存储过程
【例11-3】在“teaching”库创建带参数的存储过程， 修改某个学生某门课的成绩。 USE teaching GO CREATE PROCEDURE Update_score @number char(7),@cno char(4),@score int AS UPDATE sc SET score=@score WHERE sno= @number and cno=@cno
11.1.2 存储过程的类型
2．本地存储过程 本地存储过程也就是用户自行创建并存储在 用户数据库中的存储过程，一般所说的存储过程 指的就是本地存储过程。 用户创建的存储过程是由用户创建并能完成 某一特定功能(如查询用户所需的数据信息)的存 储过程。
11.1.2 存储过程的类型
3．临时存储过程
11.1.4 执行存储过程
执行存储过程即调用存储过程，可以使用SQL Server Management Studio界面，也可以使用T-SQL 语句中的 EXECUTE命令。 1．使用SQL Server Management Studio执行存储过程 (1)打开SQL Server Management Studio，展开存储过程所 在的数据库，展开“可编程性”选项，右键单击存储过程名， 如“teaching”中的“GetStudent”，在弹出的快捷菜单中选择 “执行存储过程”命令。 (2)进入“执行过程”对话框，输入要查询的学生的sno，如 “1302001” 。 (3)单击“确定”按钮。
(1)本地临时存储过程 不论哪一个数据库是当前数据库，如果在创建存储过程 时，其名称以“#”号开头，则该存储过程将成为一个存 放在tempdb数据库中的本地临时存储过程。 (2)全局临时存储过程 不论哪一个数据库是当前数据库，只要所创建的存储过 程名称是以两个“#”号开头，则该存储过程将成为一个 存储在tempdb数据库中的全局临时存储过程。
11.1 存储过程
SQL Server 2005的存储过程（Stored Procedure）就是一个 具有独立功能的子程序，以特定的名称存储在数据库中。 可以在存储过程中声明变量、有条件地执行以及其他各项 强大的程序设计功能。
11.1.1 存储过程概述
存储过程是T-SQL语句和可选流程控制语句的预编译集合， 它以一个名称存储并作为一个单元处理，能够提高系统的 应用效率和执行速度。SQL Server提供了许多系统存储过 程以管理SQL Server和显示有关数据库和用户的信息。
11.1.2 存储过程的类型
4．远程存储过程 在SQL Server 2005中，远程存储过程是位于远程 服务器上的存储过程，通常可以使用分布式查询和 EXECUTE命令执行一个远程存储过程。 5．扩展存储过程 扩展存储过程是用户可以使用外部程序语言（例如 C语言）编写的存储过程。显而易见，扩展存储过 程可以弥补SQL Server 2005的不足，并按需要自行 扩展其功能。