sql server 2017数据库复习：第十二章-T-SQL语言概述、存储过程和触发器

本文链接：https://blog.csdn.net/Mxeron/article/details/112473034

存储过程是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户通过指定存储过程的名字，并给出参数来执行它。存储过程是数据库中的一个重要对象, 任何一个设计良好的数据库应用程序都应该用到存储过程。

触发器是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动的，而是由事件来触发，如当对一个表进行操作(插入、修改和删除)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

（存储过程类似于C语言中的函数）

T-SQL概述

1、T-SQL语法变量
变量用于临时存放数据，变量中的数据随着程序的运行而变化。 T-SQL中的变量可以分为局部变量和全局变量两种：

局部变量以@开头，全局变量以@@开头。

在这里插入图片描述

2、T-SQL流程控制语句

TSQL中，两条及两条以上的语句要用begin、end封装起来使用！

在这里插入图片描述

while语句中需要加入begin end！！代表循环结束和开始！

在这里插入图片描述

存储过程

存储过程在第一次执行时进行编译！将编译好的代码保存在高速缓存中！

在这里插入图片描述
1、创建存储过程：

2、执行存储过程：（可以简写为：exec）在这里插入图片描述

参数声明（输入参数和输出参数）在 as begin 的前面用@引导！在存储过程中如果要用到参数，也要用@来进行使用！

例：创建一个存储过程proc1，从课程表中查询某门课程的总学时（授课时数+实践时数）。要查询的课程号通过输入参数传递给存储过程，并执行该过程。

create proc pro1
@id char(8)（声明输入参数）
as begin
    select 授课时数+实践时数 as 总学时 from 课程表
        where 课程号=@id
end

例：在上题基础上创建存储过程proc1，定义一个输出参数来存放查询得到的总学时，并执行该过程。

create proc proc1
@id char(10),
@result int output
as begin
    select @result=授课时数 from 课程表 where 课程号=@id
end

declare @sum int（声明一个变量来存放输出的result，一定不要搞忘！)

exec proc1 '74012002',@sum output

print @sum

在用输出参数时，在声明时要在最后加上 output 关键字，在调用的时候也要把它传给一个变量来存放输出值，上面用的是@sum，同样也要在末尾加上output以表示它是用来存输出值的！

3、修改存储过程： 在这里插入图片描述
4、删除存储过程：

常用的系统存储过程

在这里插入图片描述

触发器

在这里插入图片描述

SQL Server包括两大类触发器：DML触发器和DDL触发器。

在这里插入图片描述

DML触发器

DML触发器是在数据库服务器中发生数据操作语言事件时执行的存储过程，也就是说，DML触发器在执行INSERT, DELETE,UPDATE语句时触发。

根据 触发的时机不同，DML触发器又分为AFTER触发器和INSTEAD OF触发器两种。

AFTER触发器是在记录已经修改完成后才会被激活执行，它主要用于记录变更后的处理或检查，一旦发现错误，也可以用ROLLBACK TRANSACTION语句来回滚本次操作。

INSTEAD OF触发器一般用于取代原本的操作，在记录变更之前发生，它不去执行原来的SQL语句，而去执行触发器中所定义的操作。

魔表（INSERTED、DELETED）

SQL Server中，系统为每个DML触发器都定义了两个特殊的表，一个是插入表 (INSERTED表)，一个是删除表(DELETED表)。它们名字就叫这个！属于关键字

其中，DELETED表里存放的是更新前的记录，对于UPDATE操作来说，DELETED表里存放的是更新前的记录(更新完后即被删除)，对于DELETE操作来说，DELETED表里存放的就是被删除的记录。

而，INSERTED 表里存放的是更新后的记录，对于INSERT操作来说，INSERTED表里存放的是要插入的数据记录，对于UPDATE操作来说，INSERTED表里存放的是修改后的数据记录。

1、AFTER触发器（也可以用FOR）是在记录变更完成之后才被激活执行的， 以删除记录为例：当SQL Server接收到一个要执行删除操作的SQL语句时，SQL Server先将要删除的记录存放在DELETED表里，然后把数据表里的数据删除，再激活AFTER触发器，执行AFTER触发器中的SQL语句。执行完毕后，删除内存中的DELETED表，退出整个操作。

2、INSTEAD OF触发器与AFTER触发器不同。 AFTER触发器是在INSERT, UPDATE 和DELETE操作完成后才激活的，而INSTEAD OF触发器是在这些操作进行之前就激活了,并且不再去执行原来的SQL操作，而去执行触发器中本身的SQL语句。
在这里插入图片描述

DDL触发器

DDL触发器是在响应数据定义语言事件时执行的存储过程。DDL触发器一般用于执行数据库中的管理任务，如审核和规范数据库操作、防止数据库表结构被修改等。
DDL触发器并不响应INSERT、UPDATE、DELETE语句，它主要响应CREATE、ALTER、DROP、GRANT、DENY、REVOKE等语句 。

DDL触发器与DML触发器的不同之处在于：
1、只有在完成T-SQL语句后才运行DDL触发器，DDL触发器无法作为 INSTEAD OF触发器使用。
2、DDL触发器不会创建INSERTED表和DELETED表，但是可以使用EVENTDATA函数捕获有关信息。

创建触发器

在这里插入图片描述
例：课程学分不允许超过6分，创建一个Insert触发器，如果Course表中中插入的数据，学分超过6分，则提示不能插入数据，取消本次插入操作。

create trigger InsertCourse on Course  for insert
    as begin
    declare @c int
        select @c=ccreadit from inserted
    if @c>6
        rollback transaction（丢回它的操作）
        print '不能插入数据，取消本次插入操作。'
end

说明：

这里既可以用for（afer）+rollback transaction ，也可以只用instead of，具体看自己。
after是执行之后再执行，instead of是执行之前触发，被触发了就只执行触发器中的语句，原语句不执行！

例：创建一个delete触发器，如果删除student表中某个学生，则同时将该学生的修课记录一并删除。

create trigger Deletestudent on student for delete 
    as begin 
    declare @id char(10)
    select @id=sno from deleted
    delete sc where sno=@id
end