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

SQL Server存储过程和触发器操作实训一、由已给数据库文件完成下列SQL存储过程操作1、创建和执行不带参数的存储过程创建一个存储过程p1，返回所有女生信息。

2、创建和执行带参数的存储过程（1）创建一个存储过程p2，返回指定系部的学生信息。

（输入参数）（2）创建从sc表查询指定学生学号的总成绩的存储过程p3。

（输入和输出参数）（3）创建一个名为Query_student的存储过程，该存储过程的功能是根据学号查询学生表中某一学生的姓名、系别、性别及年龄。

（输入参数）执行存储过程Query_student，查询学号为”9512103”的学生的姓名、系别、性别及年龄。

写出完成此功能的SQL命令。

3、创建和执行带输入参数的存储过程（1）创建一个能向学生表中插入一条记录的存储过程Insert_student,该过程需要5个参数，分别用来传递学号、姓名、性别、年龄、系别5个值。

写出执行存储过程Insert_student的SQL语句，向学生表中插入一个新同学，并提供相应的实参值（实参值自定）。

create procedure Insert_student( @no char(10),@name char(8),@sex char(2),@age int,@ dept char(10) )ASbegininsert into s(sno,sname,ssex,sage,sdept)values(@no,@name,@sex,@age,@dept)endEXEC Insert_student @no='9512104', @name='徐小花',@sex='女', @age=20, @dept='计算机系'（2）在课程表中添加“学分”列，类型为小整型，然后创建一个向课程表中插入一门新课程的存储过程Insert_course，该存储过程需要三个参数，分别用来传递课程号、课程名、学分，但允许参数“学分”的默认值为2，即当执行存储过程Insert_course时，未给参数“学分”提供实参值时，存储过程将按默认值2进行运算。

sql server 2012数据库开发操作实验原理SQL Server 2012数据库是一款由微软公司开发的关系型数据库管理系统（RDBMS），广泛应用于企业级应用程序和数据仓库。

数据库开发操作是指使用SQL Server 2012进行数据库设计、创建、管理和优化的过程，这些操作涉及到数据库对象的创建、数据的插入、查询、更新和删除等操作。

本文将重点介绍SQL Server 2012数据库开发操作的原理和实验过程。

SQL Server 2012数据库开发操作的原理主要涉及到数据库设计、数据类型、表的创建和管理、数据操作语言（DML）、数据查询语言（DQL）、数据定义语言（DDL）、存储过程、触发器、索引、视图、事务和并发控制等方面。

下面将对这些内容进行详细介绍。

1.数据库设计SQL Server 2012数据库设计是数据库开发的第一步，它涉及到数据库的逻辑设计和物理设计。

逻辑设计是指根据需求分析和数据建模的结果，设计出数据库对象的结构和关系。

物理设计是指将逻辑设计转化为实际数据库对象的创建和存储结构的设计。

在进行数据库设计时，需要考虑到数据的完整性、一致性、性能和安全等方面。

2.数据类型SQL Server 2012提供了丰富的数据类型，包括数值型、字符型、日期型、二进制型、空间型等。

在进行数据库开发时，需要选择合适的数据类型来存储和操作数据，以保证数据的正确性和高效性。

3.表的创建和管理表是SQL Server 2012数据库中最基本的对象，它用于存储数据并描述数据之间的关系。

在进行数据库开发时，需要通过DDL语句来创建表，并使用DML语句对表中的数据进行管理。

此外，还需要通过索引、主键、外键等约束来保证数据的完整性和一致性。

4.数据操作语言（DML）DML是SQL Server 2012中用于对数据库中的数据进行操作的语言，它包括插入（INSERT）、查询（SELECT）、更新（UPDATE）和删除（DELETE）等操作。

实验7存储过程和触发器的使用1.目的与要求（1）掌握存储过程的使用方法。

（2）掌握触发器的使用方法。

2.实验准备（1）了解存储过程的使用方法。

（2）了解触发器的使用方法。

（3）了解inserted逻辑表和deleted逻辑表的使用。

（4）了解如何编写CRL存储过程与触发器。

3.实验内容（1）存储过程①创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。

USE YGGLGOCREATE PROCEDURE TEST@NU MBER1 int OUTPUTASBEGINDECLARE@ NUMBER2 INT;SET NUMBER2=(SELECT COUNT(*) FROM Employees);SET NUMBER1=NUMBER2;END执行该存储过程，并查看结果DECLEAR@num intEXEC TEST @num OUTPUTSELECT@num②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1。

CREATE PROCEDURE COMPA@ID1 CHAR(6),@ID2 CHAR(6),@BJ INT OUTPUTASBEGINDECLARE@ SR1FLOAT,@SR2 FLOATSELECT @SR1=InCome-OutComeFROM FROM Salary WHERE EmployeeID=@ID1 SELECT @SR2=InCome-OutCome FROM Salary WHERE EmployeeID=@ID2 IF @ID1>ID2SET @BJ=0ELSESET @BJ=1END执行该存储过程,并查看结果：DECLARE@BJ intEXEC COMPA ‘000001’,’108991’,@BJ OUTPUTSELECT@BJ③创建添加职员记录的存储过程EmployeeAdd。

USE YGGLGOCREATE PROCEDURE EmployeeAdd(@employeeid char(6),@name char(10),@education char(4),@birthday datetime,@woekyear tinyint, @sex bit,@address char(40),@phonenumber char(12), @departmentID char(3))ASBEGININSERT INTO EmployeesVALUES(@employeeid,@name,@education,@birthday,@woekyear,@sex,@address,@phonenumber,@departmentID)ENDRETURNGO执行该存储过程：EXEC EmployeeAdd’990230’,,’刘朝’，‘本科’，‘840909’，2，1，‘武汉小洪山5号’，‘85465213’，‘3’①创建一个带有OUTPUT游标参数的存储过程，在Employees表中声明并打开一个游标。

Sql/server 存储过程和触发器set select同时对多个变量同时赋值不支持支持表达式返回多个值时出错将返回的最后一个值赋给变量表达式未返回值变量被赋null值变量保持原值declare @addr varchar(128)set @addr = (select addr from chinadba1)/////////////////////declare @addr varchar(128)select @addr = addr from chinadba1print @addr///////////////////////declare @addr varchar(128)set @addr = '初始值'set @addr = (select addr from chinadba1 where userid = 4 )print @addr/////////////////////////declare @addr varchar(128)set @addr = '初始值'select @addr = addr from chinadba1 where userid = 4 print @addr////////////////declare @addr varchar(128)set @addr = '初始值'select @addr = (select addr from chinadba1 where userid = 4)print @addr1.存储过程概念存储过程是为了实现某个特定任务，由一组预先编译好的SQL语句组成，将其放在服务器上，由用户通过指定存储过程的名字来执行的一种数据库对象。

无参存储过程的创建1）语法格式如下：CREATE PROC [ EDURE ] procedure_nameAS sql_statement [ ...n ]2）语法注释：procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。