SQL Server 2012 数据库教程第7章 存储过程和触发器
SQL Server 2012 数据库教程第7章 存储过程和触发器
① 存储过程：存储过程保存T-SQL语句集合，可以接收和返回用户提供的参 数。
② CLR存储过程：CLR存储过程是对Microsoft .NET Framework公共语言运行时 （CLR）方法的引用，可以接收和返回用户提供的参数。
则创建完成，如图7.4所示。
2．执行存储过程 在pxscj数据库的“存储过程”目录下选择要执行的存储过程，如 student_info1，右键单击鼠标，选择“执行存储过程”菜单项。在弹出的“执行 过程”窗口中会列出存储过程的参数形式，如果“输出参数”栏为“否”，则表
以下命令的执行结果与上面的相同：
EXECUTE student_info1 @name='王林', @cname='计算机基础'
或者： DECLARE @proc char(20) SET @proc= 'student_info1' EXECUTE @proc @name='王林', @cname='计算机基础'
接下来执行存储过程do_action来查看结果： DECLARE @str char(8) EXEC dbo.do_action 0, @str OUTPUT SELECT @str; 执行结果显示“修改成功”。
Hale Waihona Puke 4）使用带有通配符参数的存储过程【例7.4】 从3个表的连接中返回指定学生的学号、姓名、所选课程名称及该
在如下的批处理中，声明一个局部游标变量，执行上述存储过程，并将游标 赋值给局部游标变量，然后通过该游标变量读取记录。
网络数据库SQL Server 2012教程第7章 存储过程与触发器
目录
目录
7.1 存储过程
7.1.1 存储过程概述 存储过程的概念
存储过程(Stored Proce完成特定功能的T-SQL语句集合。存储过程 存储在数据库内，可以有用户的应用程序，通过指定存储 过程名称及相关参数来执行。
7.1 存储过程
7.1.2 创建存储过程
USE StuInfo GO CREATE PROCEDURE Search_2 @name char(10) --@name char(10)：定义参数 AS BEGIN SELECT S.学号 , S.姓名 ,Sc.成绩 , C .课程名 FROM Student AS S INNER JOIN Score AS Sc ON S.学号 = Sc.学号 INNER JOIN Course AS C ON Sc.课程号 = C.课程号 WHERE S.姓名 = @name END
7.1.2 创建存储过程
USE StuInfo GO CREATE PROCEDURE Search_1 AS BEGIN SELECT Student.学号 , Student.姓名 ,Score.成绩 , Course .课程名 FROM Student INNER JOIN Score ON Student.学号 = Score.学号 INNER JOIN Course ON Score.课程号 = Course.课程号 WHERE Student.姓名 = '祁鹏' END
7.1 存储过程
7.1.1 存储过程概述 存储过程的优点：
1.模块化的程序设计，实现代码多次调用。存储过程只需 创建一次，并存储在数据库中，
在以后的使用中，便可重复调用，不需要每次重新编写。
SQL Sever存储过程及触发器
1、创建一个存储过程SC_PROC，要求指定学生选课的平均成绩（以学号为参数）。
Create proc sc_proc@sunm varchar(10),@sname varchar(8) output，@avg_grade numeric(3,1) outputAsBeginSelect @avg_grade=avg(grade),@sname=snameFrom sc join s on sc.sno=s.snoWhere s.sno=@snumGroup by snameEnd调用Declare @avg numeric(3,1),@sname1 varchar(8)Exec sc_proc ‘200215121’,@sname=@sname1 output,@avg_grade=@avg outputSelect ‘200215121’,@sname1,@avg2,设有如下定义：declar student cursor for select sno,sname from s;写一段程序，将student中的数据逐一显示出来。
Create procedure s_cursor_procAsBegin --声明变量Declare @xh varchar(10),@xm varchar(10)Declare student cursorFor select sno,sname from sOpen studentFetch next from student into @xh,@xmPrint ‘学号姓名’Print ‘----------------’While @@fetch_status = 0BeginPrint @xh + ‘’ +@xmFetch next from student INTO @xh,@xmEndClose studentDeallocate student --释放游标EndExecute s_cursor_proc3,以s(sno,sname),sc(sno,cno,grade)查询相关数据。
数据库SQL ServerSQLite教程课件：存储过程、触发器和游标
存储过程、触发器和游标
⒉ 使用 SSMS 创建存储过程 操作步骤如下： (1) 在“对象资源管理器”中展开“数据库”节点，展 开要创建存储过程的数据库。 (2) 展开“可编程性”节点，选择“存储过程”选项， 右击鼠标弹出快捷菜单，选择“新建存储过程”命令，打开 创建存储过程对话框，如图 8-3 所示。
存储过程、触发器和游标
8.1.2 创建存储过程 1. 使用 create procedure 语句创建存储过程 1) 语法格式
存储过程、触发器和游标
参数说明： (1) procedure_name 是要创建的存储过程的名称，它后 面跟一个可选项 number，是一个整数，用来区别一组同名 的存储过程，如 proc1、proc2 等。 (2) @parameter 用来声明存储过程的形式参数。在 create procedure 语句中，可以声明一个或多个参数。 (3) data_type 是参数的数据类型。 (4) varying 指定由 output 参数支持的结果集，仅应用于 游标型参数。
exec xp_logininfo;
存储过程、触发器和游标 例 8-3 查看有关 D:\sq 文件夹的文件信息。显示结果如
图 8-2 所示。代码如下： exec xp_cmdshell 'dir D:\sq\';
图 8-2 扩展存储过程
存储过程、触发器和游标
3) 用户自定义存储过程 用户自定义存储过程 (User-Defined Stored Procedure) 是 由用户设计的存储过程。其名称可以是任意组合 SQL Server 命令规则的字符组合，通常以“usp_”开头，避免以“sp_” 或“xp_”开头，以免造成混淆。自定义的存储过程会被添 加到所属数据库的存储过程中，并以对象的形式保存。
整理的关于SQL Server中事务,触发器和存储过程的介绍以及实际例子
事务事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。
这些操作要么都做，要么都不做，是一个不可分割的工作单位。
通过事务，SQL Server能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。
(2):事务通常是以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束。
COMMIT表示提交，即提交事务的所有操作。
具体地说就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，事务正常结束。
ROLLBACK表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有以完成的操作全部撤消，滚回到事务开始的状态。
(3):事务运行的三种模式:A:自动提交事务每条单独的语句都是一个事务。
每个语句后都隐含一个COMMIT。
B:显式事务以BEGIN TRANSACTION显式开始，以COMMIT或ROLLBACK显式结束。
C:隐性事务在前一个事务完成时，新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK显式结束。
(4):事务的特性(ACID特性)A:原子性(Atomicity)事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。
B:一致性(Consistency)事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
一致性与原子性是密切相关的。
C:隔离性(Isolation)一个事务的执行不能被其他事务干扰。
D:持续性/永久性(Durability)一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。
注:事务是恢复和并发控制的基本单位。
数据库事务的ACID属性原子性（atomic）（atomicity)事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。
如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。
SQL server中的存储过程和触发器
实验六存储过程和触发器实验报告姓名：张娜成绩：__________【实验目的】1、掌握存储过程的创建。
2、掌握存储过程的执行。
3、掌握存储过程的修改和删除。
4、掌握触发器的创建和使用。
【实验内容】2、创建存储过程GetOrder_details的SQL语句。
create procedure getorder_details(@orderno char(4))with recompileasselect orderdate,deliverydate,addressofdelivery,name,price,qtyordered,price*qtyordered as allmoney from orders join orderdetailson orders.orderno=orderdetails.orderno join products on products.productno=orderdetails.productnowhere orders.orderno=@orderno3、（1）输入参数为‘1002’，调用存储过程的SQL语句。
（2）执行结果。
（抓图）declare @orderno char(4)set @orderno='1002'execute getorder_details @orderno4、（1）修改存储过程GetOrder_details后的SQL语句。
（2）输入参数为‘1003’，调用存储过程的SQL语句。
（3）执行结果。
（抓图）if exists(select name from sysobjects where name='getorder_details'and xtype='p')drop procedure getorder_detailscreate view getdetailsasselect orderno,price*qtyordered as allmoney from orderdetails join products on products.productno=orderdetails.productnocreate procedure getorder_details(@orderno char(4))with recompileasbeginselectorderdate,deliverydate,addressofdelivery,name,price,qtyordered,price*qtyordered as allmoney from orders join orderdetailson orders.orderno=orderdetails.orderno join products on products.productno=orderdetails.productnowhere orders.orderno=@ordernoselect orderno,sum(allmoney) as '总金额' from getdetails group by orderno having orderno=@ordernoenddeclare @orderno char(4)set @orderno='1003'execute getorder_details @orderno5、（1）说明触发器attention完成的功能。
SQL Server 2012存储过程及调用
else
begin
insertintodbo.STATIC_Do_Not_Wanna_Display_Listvalues(@t_name)
print'插入成功！套餐类型“'+@t_name+'”已插入需屏蔽套餐定义列表！'
end
end
declare@t_namevarchar(50)
select@t_name=''
SQL-Server2012存储过程及调用
黑名单维护
USE[OutCallMarketing]
GO
/****** Object:StoredProcedure[dbo].[add_value_to_black_user_list] Script Date: 2014/2/25 16:03:31 ******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
ALTERprocedure[dbo].[add_value_to_can_not_buy_traffic_package_list]
@t_name_invarchar(50)
as
declare@t_namevarchar(50)
需屏蔽套餐列表维护
USE[OutCallMarketing]
GO
/****** Object:StoredProcedure[dbo].[add_value_to_do_not_wanna_display_List] Script Date: 2014/2/25 16:07:46 ******/
SETANSI_NULLSON
end
if@destination_all_called_table_name=''
实验内容_SQL Server 2012 数据库教程（第3版）_[共2页]
![实验内容_SQL Server 2012 数据库教程（第3版）_[共2页]](https://img.taocdn.com/s3/m/ed9908cdff00bed5b8f31d1c.png)
249 满足参照完整性规则，即：（1）向Employees 表添加记录时，该记录的“DepartmentID ”字段值在Departments 表中应存在。
（2）修改Departments 表的“DepartmentID ”字段值时，该字段在Employees 表中的对应值也应修改。
（3）删除Departments 表中的记录时，该记录的“DepartmentID ”字段值在Employees 表中对应的记录也应删除。
对于上述参照完整性规则，在此通过触发器实现。
实验内容在“查询分析器”窗口中输入各触发器的代码并执行。
（1）向Employees 表插入或修改一个记录时，通过触发器检查记录的DepartmentID 值在Departments 表中是否存在，若不存在，则取消插入或修改操作。
USE YGGL GO CREATE TRIGGER EmployeesIns ON dbo.Employees FOR INSERT , UPDATE AS BEGIN IF ((SELECT DepartmentID from inserted ） NOT IN (SELECT DepartmentID FROM Departments)) ROLLBACK /*对当前事务回滚，即恢复到插入前的状态*/ END向Employees 表插入或修改一行记录，查看效果。
（2）修改Departments 表“DepartmentID ”字段值时，该字段在Employees 表中的对应值也做相应修改。
USE YGGL GO CREATE TRIGGER DepartmentsUpdate ON dbo.Departments FOR UPDATE AS BEGIN UPDATE Employees SET DepartmentID=(SELECT DepartmentID FROM inserted) WHERE DepartmentID=(SELECT DepartmentID FROM deleted) END GO （3）删除Departments 表中记录的同时删除该记录“DepartmentID ”字段值在Employees 表中对应的记录。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
3．举例 （1）设计简单的存储过程 【例7.1】 返回191301号学生的成绩情况。该存储过程不使用任何参数。 USE pxscj GO CREATE PROCEDURE student_info
AS SELECT * FROM cjb WHERE学号= '191301'
GO 存储过程定义后，执行存储过程student_info： EXECUTE student_info
1．使用命令方式创建存储过程 创建存储过程的语句是CREATE PROCEDURE或CREATE PROC，两者同义。 语法格式：
CREATE { PROC | PROCEDURE } [架构名.]过程名[ ; 组号] /*定义过程名*/ [ { @参数[类型架构名. ]数据类型 } /*定义参数的类型*/ [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY] /*定义参数的属性*/
]
[ FOR REPLICATION ]
AS { <SQL语句>
/*ECUTE或EXEC命令可以执行一个已定义的存储过程，EXEC是EXECUTE 的简写。语法格式：
[ { EXEC | EXECUTE } ] { [ @返回状态 = ]
{ 模块名 | @模块名变量 } [ [ @参数名 = ] { 值 | @变量[ OUTPUT ] | [ DEFAULT ] } ]
如果该存储过程是批处理中的第一条语句，则可使用：
student_info 执行结果如图7.1所示。
（2）使用带参数的存储过程 【例7.2】 从pxscj数据库的3个表中查询某人指定课程的成绩和学分。该存储 过程接收与传递参数精确匹配的值。
USE pxscj
GO
CREATE PROCEDURE student_info1 @name char (8), @cname char(16)
} 存储过程的执行要注意以下几点。
（1）如果存储过程名的前缀为“sp_”，SQL Server会首先在master数据库中 寻找符合该名称的系统存储过程。如果没能找到合法的过程名，SQL Server才会 寻找架构名称为dbo的存储过程。
（2）在执行存储过程时，若语句是批处理中的第一个语句，则不一定要指 定EXECUTE关键字。
（1）系统存储过程。系统存储过程是由SQL Server提供的存储过程，可以作 为命令执行。系统存储过程定义在系统数据库master中，其前缀是“sp_”，例如， 常用的显示系统对象信息的sp_help系统存储过程，为检索系统表的信息提供了方 便快捷的方法。
（2）扩展存储过程。扩展存储过程是指在SQL Server 2012环境之外，使用编 程语言（如C++语言）创建的外部例程形成的动态链接库（DLL）。使用时，先将 DLL加载到SQL Server 2012系统中，并且按照使用系统存储过程的方法执行。扩展 存储过程在SQL Server实例地址空间中运行。
以下命令的执行结果与上面的相同：
EXECUTE student_info1 @name='王林', @cname='计算机基础'
或者： DECLARE @proc char(20) SET @proc= 'student_info1' EXECUTE @proc @name='王林', @cname='计算机基础'
（3）用户存储过程。在SQL Server中，用户存储过程可以使用T-SQL语言编写， 也可以使用CLR方式编写。在本书中，T-SQL存储过程就称为存储过程。
① 存储过程：存储过程保存T-SQL语句集合，可以接收和返回用户提供的参 数。
② CLR存储过程：CLR存储过程是对Microsoft .NET Framework公共语言运行时 （CLR）方法的引用，可以接收和返回用户提供的参数。
7.1 存 储 过 程 7.2 触 发 器
（1）存储过程在服务器端运行，执行速度快。 （2）存储过程执行一次后，就驻留在高速缓冲存储器，在以后的操作中， 只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。 （3）使用存储过程可以完成所有数据库操作，并可通过编程方式控制对数 据库信息访问的权限，确保数据库的安全。 （4）自动完成需要预先执行的任务。存储过程可以在SQL Server启动时自动 执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动 完成一些需要预先执行的任务。
AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分 FROM xsb a INNER JOIN cjb b ON a.学号 = b.学号INNER JOIN kcb t ON b.课程号= t.课程号 WHERE a.姓名=@name and t.课程名=@cname
GO
执行存储过程student_info1： EXECUTE student_info1 '王林', '计算机基础' 执行结果如图7.2所示。
第二个存储过程：
CREATE PROCEDURE do_action @X bit, @STR CHAR(8) OUTPUT AS BEGIN EXEC do_insert IF @X=0 BEGIN UPDATE xsb SET姓名='刘英', 性别=0 WHERE学号='091201' SET @STR='修改成功' END ELSE IF @X=1 BEGIN DELETE FROM xsb WHERE学号='091201' SET @STR='删除成功' END END
（3）使用带OUPUT参数的存储过程
【例7.3】 创建一个存储过程do_insert，作用是向xsb表中插入一行数据。创
建另外一个存储过程do_action，在其中调用第一个存储过程，并根据条件处理该
行数据，处理后输出相应的信息。
第一个存储过程：
CREATE PROCEDURE dbo.do_insert AS INSERT INTO xsb VALUES('091201', '陶伟', 1, '1990-03-05', '软件工程',50, NULL);