SQL Server 2012 数据库教程第7章 存储过程和触发器

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)查询相关数据。

事务事务（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)事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。

如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。

实验六存储过程和触发器实验报告姓名：张娜成绩：__________【实验目的】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完成的功能。

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 表中对应的记录。