第十章 创建于管理存储过程
目标 理解存储过程的作用和运行机制、在实际应用开发时根据需要创建、修改存储过程、根据实际需要在存储过程中定义并使用输入参数、输出参数
1 存储过程概述
在数据库应用系统开发过程中经常需要将需要多次调用的T-SQL语句编写的程序段，存储在服务器上，应用程序通过子程序调用方式执行该程序段，从而提高系统的运行效率和数据的完整性，这种方式酒窖存储过程
存储过程是sql语句和流程控制语句的预编译集合，将T-SQL语句多个封装到一起作为一个单元处理，只需编译一次，以后可以多次执行。
存储过程由参数，编程语句和返回值组成。通过输入参数向存储过程传递参数、通过输出参数向存储过程的调用者传递参数。存储过程只能有一个返回值，通常用于表示调用存储过程的结果是否成功
优点：
提高系统运行速度 只在创建时编译，以后每次执行不必重新编译
2 提高系统的开发速度 存储过程通过封装复杂的数据库操作以简化开发过程
3 提高系统的可维护性 存储过程可以实现模块化的程序设计，提供统一的数据库访问接口，改进应用程序的可维护性。
4 提高系统的安全性 用户不能直接操作存储过程中引用的对象、SQL Server可以设定用户对制定存储过程的执行权限来增强程序代码的安全性
5 降低网络流量 存储过程直接存储在数据库中，通信过程中，不会产生大量的T-SQL代码流量
但存储过程依赖数据库管理系统，不便移植
CREATE PROCEURE
2 存储过程分类
系统存储过程 SQL Server本身具有的功能 主要用来获取信息，为系统管理员提供帮助，为用户查看数据库对象提供帮助 、
sp_rename更改当前数据库用户创建对象的名称 sp_helptext 可以显示规则、默认值或视图的文本信息
用户自定义存储过程
用户为实现某一特定任务或需求创建的。##表示全局存储过程，#表示局部临时存储过程
扩展存储过程
以前缀xp_为标识，提供了SQL Server到外部程序的接口，以便进行各种维护活动。 EXEC xp_logininfo 返回账户，账户类型，账户的特权级别，账户的映射登陆名，账户访问SQL Server的权限路径
EXEC xp_loginconfig,返回SQL Server在Windows上运行的登陆安全配置
1 USE XK 2 GO 3 -- 根据班级名称查询数据信息 4 ALTER PROCEDURE p_class @classname nvarchar(20) 5 AS 6 SELECT * FROM student WHERE class_id=(SELECT class_id FROM class where class_name=@classname) 7 GO 8 EXEC p_class '软件2班' 9 GO 10 -- 根据班级编号查询数据信息 11 CREATE PROCEDURE p_class1 @classid nvarchar(20) 12 AS 13 SELECT * FROM student WHERE class_id=@classid 14 GO 15 EXEC p_class '软件2班' 16 --带输出参数的存储过程 根据编辑编号 获取学生个数 17 ALTER PROCEDURE p_class2 18 @classid nvarchar(20) ,@studentnum smallint OUTPUT 19 WITH ENCRYPTION 20 AS 21 SET @studentnum=( SELECT COUNT(student_id) FROM student WHERE class_id=@classid) 22 GO 23 DECLARE @classid nvarchar(20),@studentnum smallint 24 SET @classid='11111116' 25 EXEC p_class2 @classid,@studentnum OUTPUT 26 SELECT @studentnum 27 GO 28 --创建一个输出字符串的存储过程 29 ALTER PROCEDURE p_class3. 30 AS 31 DECLARE @output2008 NVARCHAR(50) 32 SET @output2008='Hello SQL Server 2008' 33 SELECT @output2008 AS '问候语' 34 GO 35 EXEC p_class3
第十一章 创建与管理触发器
目标 理解触发器的概念及运行机制 根据实际需要创建修改和删除触发器 禁用和启用触发器 了解查看触发器的方法
1 触发器概述
数据库主要根据约束和触发器来强制业务规则和数据完整性。触发器是一个在修改指定数据表的数据时执行的存储过程，当向某一个数据表插入删除或修改记录时，SQL Server会自动执行触发器所定义的SQL语句，从而保证对数据的处理符合由这些语句定义的规则。触发器和引起触发器执行的SQL语句被当做一次事务处理，操作失败回滚。
主要功能：
1 强化约束 能实现比CHECK更为复杂的约束 强制执行数据库中相关表的引用完整性
2 根据数据的变化 撤销和回滚违反了引用完整性的操作，防止非法修改数据
3 级联运行 级联修改数据库中相关的表，自动触发其他与之相关的操作
4 返回自定义的错误信息 触发器可以返回信息，而约束只能通过标准的系统错误显示错误信息
注意
1 开头使用CREATE TRIGGER并且只能应用于一个表中
2 需要向应用程序返回结果，否则不应使用SELECT 变量
3 触发器只在当前数据库中创建但可以引用数据库的外部对象
4 同一条CREATE TRIGGER语句中可以为（INSERT、DELETE、UPDATE）语句定义相同的触发器操作
5 如果一个表的外键定义了级联，则不能定义INSEAD OF触发器
6 执行修改语句的过程中，触发器的执行只是修改语句事务的一部分，如果触发器执行不成功，则整个修改事务会回滚
7 约束可以实现预定的数据完整性时，优先使用约束‘ ’
8 TRUNCATE TABLE语句不触发触发器
9 触发器不允许使用 ALTER DATABASE CREATE DATABASE和DROP DATABASE 等T-SQL语句
2 触发器分类
数据操纵语言DML触发器
使用触发器情况 1 通过相关表实现级联修改 2 防止恶意操作 3 评估数据修改前后的状态，根据差异采取措施
INSERTED DELETED 用来临时存储要增加和删除的数据，UPDATE时先删除放在deleted后增加放在inserted表中，方便触发器判断条件，实现回滚，提示灯业务
数据定义语言DDL触发器 当发生数据定义语言时被激活调用、使用DDL可以防止对数据库架构进行的某些修改或记录数据库架构中的更改事件。、
1 ----第十一章 触发器 2 3 --更改选修表数据时 可以及时更改人数 4 --将表中数据读取出来然后依次删除更改 需要用到游标操作 本次只能执行单个操作 5 ALTER TRIGGER changenum ON elective 6 FOR INSERT,UPDATE,DELETE 7 AS 8 --UPDATE course SET student_num=(SELECT COUNT(course_id) FROM inserted WHERE course.course_id =inserted.course_id) 9 -- UPDATE course SET student_num=(SELECT COUNT(course_id) FROM deleted WHERE course.course_id =deleted.course_id) 10 DECLARE @id nvarchar(10) 11 DECLARE cur_course CURSOR 12 FOR 13 SELECT course_id FROM inserted 14 OPEN cur_course 15 FETCH NEXT FROM cur_course INTO @id 16 WHILE @@FETCH_STATUS=0 17 BEGIN 18 UPDATE course SET student_num=student_num+1 19 WHERE course_id =@id 20 FETCH NEXT FROM cur_course INTO @id 21 END 22 CLOSE cur_course 23 DEALLOCATE cur_course 24 --提取第一行信息 数据库换行符和其他符号利用char 25 DECLARE @id1 nvarchar(10) 26 DECLARE cur_course CURSOR 27 FOR 28 SELECT course_id FROM deleted 29 OPEN cur_course 30 FETCH NEXT FROM cur_course INTO @id 31 while @@FETCH_STATUS=0 32 BEGIN 33 UPDATE course SET student_num=student_num-1 34 WHERE course_id =@id1 35 FETCH NEXT FROM cur_course INTO @id 36 END 37 CLOSE cur_course 38 DEALLOCATE cur_course 39 PRINT '已经自动更新数据' 40 GO 41 DELETE FROM elective WHERE course_score=1 42 GO 43 44 DELETE FROM elective WHERE student_id='10000002' 45 SELECT * FROM course 46 GO 47 48 49 ----创建触发器保证课程分数只能为 1 2 3 50 ALTER TRIGGER scorefilter ON elective 51 AFTER UPDATE 52 AS 53 BEGIN 54 DECLARE @n smallint,@s char(10) 55 56 DECLARE cur_elective CURSOR 57 FOR 58 SELECT course_score FROM inserted 59 OPEN cur_elective 60 FETCH NEXT FROM cur_elective INTO @n 61 WHILE @@FETCH_STATUS=0 62 BEGIN 63 64 IF @N=1 PRINT '执行更改成功' 65 ELSE 66 IF @N=2 PRINT '执行更改成功' 67 ELSE 68 IF @N=3 PRINT '执行更改成功' 69 ELSE 70 PRINT '执行更改成功' 71 72 FETCH NEXT FROM cur_elective INTO @n 73 END 74 --外部rollback允许 内部不行 75 --ROLLBACK 76 CLOSE cur_elective 77 DEALLOCATE cur_elective 78 END 79 GO 80 UPDATE elective SET course_score=1 81 ----更新数据课程选课人数 82 --UPDATE course SET student_num=(SELECT COUNT(student_id) FROM elective WHERE course.course_id=elective.course_id) 83 --DELETE FROM elective WHERE course_id='11111113'
第十二章 创建与使用游标
目标 理解SQL Server中游标的概念和运行机制 根据需要创建和使用游标 在存储过程中使用游标
1 认识游标 由于SELECT 语句返回的是结果集，没有指向数据表中单一记录的形式，游标就是做这个的。作为SQL Server的一种数据访问机制，允许用户访问单独的数据行，对每一行数据进行处理，降低了系统开销和潜在的网络堵塞。游标主要用于存储过程，触发器和T-SQL脚本中，使结果集的内容可用于其他SQL语句。
2012支持T-SQL服务器游标，api（应用程序编程接口）游标，客户端游标三中类型。服务器游标在服务器上实现，由客户端发送的T-SQL语句进行管理，
2 游标的生命周期 游标首先根据SELECT 语句创建结果集，从中一次获取一行数据进行操作。游标的生命周期包含以下五个阶段
声明游标 打开游标 获取游标中的数据 关闭游标 释放游标
1 --第十二章 游标 2 --建立游标，并且输出数值 课程信息 3 DECLARE @id nvarchar(10),@name nvarchar(10) ,@tname nvarchar(10),@time varchar(10) 4 DECLARE cur_course CURSOR 5 FOR 6 SELECT course_id,course_name,teacher_name,course_time FROM course 7 OPEN cur_course 8 --提取第一行信息 数据库换行符和其他符号利用char 9 FETCH NEXT FROM cur_course INTO @id,@name,@tname,@time 10 ----输出时有些问题 最后一项似乎给定的类型需要是字符型 11 WHILE @@FETCH_STATUS=0 12 BEGIN 13 PRINT '课程编号 课程名称 教师姓名 上课时间' 14 PRINT @id+ char(9) +@name+char(9)+char(9)+char(9)+@tname+ char(9)+@time 15 16 FETCH NEXT FROM cur_course INTO @id,@name,@tname,@time 17 END 18 CLOSE cur_course 19 DEALLOCATE cur_course 20 GO
第十三章 处理事务和锁
目标 理解事务的概念和事务的属性 了解锁的类型和锁的作用 在程序中使用事务 了解查看锁的方法
1 事务 数据库提供了约束，触发器，事务和锁等多种保证数据库完整性的保证机制
事务作为单个逻辑工作单元执行的一系列操作，这些操作要么都被执行，要么都不执行
2 事务的属性 原子性（执行全部执行） 一致性（执行完成，所有数据保持一致） 隔离性（当前事务正在执行的修改操作其他事务不得干预） 持续性（出现故障保证最能恢复到最后一个提交）
3 事务的分类
自动提交事务，隐式事务（将显式提交关闭后的操作，不自动执行，需要commit和rollback），显式事务（begin tran rollback tran commit tran） 分布式事务(跨越多个服务器的事务
4 事务的隔离级别
未提交读级别 提交读级别（避免脏读） 可重复读级别 快照级别 序列化级别
5 锁 通常SQL Server会自动处理锁 如果对数据完整性，安全性和一致性有特殊要求 需要亲自控制数据库的锁和解锁
6 锁的概述 由于数据库是由多用户，多进程，多线程组成的并发系统，对数据库的数据进行并发操作时，
出现脏读（一个事务读到另外一个事务没有提交的数据），幻想读（进行一些操作，导致数量不符合逻辑），不可重复读（读取同一条数据，两次数据不一样）和丢失更新（两条更新语句只执行了一条）等问题
7 锁的分类
共享锁（用于不更新数据的操作）
更新锁（用于执行更新删除等操作前放置在更新锁中可以读取）
独占锁（只允许当前用户更新删除查询操作，操作完成之前不会对其他造成影响）
8 死锁（两个或多个任务，互相占据资源，等待对方释放，形成死锁） SQL Server出现死锁在事务中寻找一个牺牲者，终止事务，返回错误，释放资源给其他事务
1 --第十三章 实现报名 课程门数不能大于三门 2 BEGIN TRANSACTION 3 DECLARE @name char(10) 4 SET @name='10000088' 5 INSERT INTO elective VALUES(@name,'11111115','1') 6 INSERT INTO elective VALUES(@name,'11111114','1') 7 8 DECLARE @count int 9 SET @count=( SELECT COUNT(*) FROM elective WHERE student_id=@name) 10 IF @count>3 11 BEGIN 12 ROLLBACK TRANSACTION 13 PRINT '插入失败' 14 END 15 ELSE 16 BEGIN 17 COMMIT TRANSACTION 18 PRINT '事务执行成功' 19 END 20 GO 21 SELECT COUNT(*) FROM elective WHERE student_id='10000088'