【数据库学习笔记】Day09 - 存储过程与触发器

Clown Piece

已于 2022-03-24 23:08:31 修改

阅读量1.4k

点赞数

分类专栏：数据库学习笔记文章标签： C C++ 数据库

于 2022-03-24 23:07:42 首次发布

本文链接：https://blog.csdn.net/oo00Z00oo/article/details/123673631

版权

数据库学习笔记专栏收录该内容

9 篇文章 10 订阅

订阅专栏

文章目录

一、存储过程
二、触发器

一、存储过程

1.1 存储过程概述

存储过程是SQL Server服务器上一组预编译的Transact-SQL语句，用于完成某项任务，它可以接收参数、返回状态值和参数值，并且可以嵌套调用。SQL提供了一种方法，它可以将一些固定的操作集中起来由数据库服务器来完成，以实现某个任务，这种方法就是存储过程。

存储过程在第一次执行时进行语法检查和编译，之后驻留在系统的内存中，再次调用时不必进行编译。
存储过程可以由应用程序多次激活，提高重复任务的执行性能。
存储过程具有输入参数和输出参数，通过这些参数返回结果集。
存储过程可以实现多种功能，如数据表数据的查询、添加记录、修改记录、和删除记录以及复杂的数据处理。

1.2 存储过程的优点

模块化程序设计
存储过程只需要创建一次并存储在数据库中，即可被应用程序反复调用，用户可以独立于应用程序对存储过程进行修改。
提高执行速度
存储过程在首次运行时编译，之后就常驻内存，再次调用时不必编译，也不必从磁盘调入内存。
降低网络通信量
包括数百行T-SQL语句的存储过程，可以通过一次执行过程代码的语句来执行，不需要在网络中发送数百行代码，减少了T-SQL语句代码在网络上的传输量。
保证系统的安全性
系统管理员可以设置用户对存储过程的操作权限，避免非授权用户对数据的访问。

1.3 存储过程的类型

系统存储过程：
    系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库 master 中，其前缀是 sp_ 。
    系统存储过程允许系统管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。
    当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。
用户自定义存储过程：
    完成用户指定的某一特定的数据库操作，只能在当前数据库中创建。
    其名称不能以 sp_ 为前缀。
    用户存储过程的名称在数据库中必须唯一，可以附带参数，完全由用户创建和维护。
临时存储过程
    属于本地存储过程。如果本地存储过程的名称前面有一个“ #” ，该存储过程就称为局部临时存储过程，只能在一个用户会话中使用。
    如果本地存储过程的名称前有两个“ ##” ，该过程就是全局临时存储过程，可以在所有用户会话中使用。
    使用临时存储过程必须创建本地连接，当 SQL Server 关闭后，这些临时存储过程将自动被删除。
远程存储过程
远程存储过程指从远程服务器上调用的存储过程。
扩展存储过程
    在 SQL Server 环境之外执行的动态链接库称为扩展存储过程，其前缀是 sp_ 。
    使用时需要先加载到 SQL Server 系统中，并且按照使用存储过程的方法执行。
    扩展存储过程只能添加到 master 数据库中。

1.4 创建存储过程

在SQL Server中，有两种方法创建存储过程：
①利用SSMS图形化界面工具创建存储过程。
②利用Transact-SQL语句创建存储过程。
创建语句例：
Create procedure pro_stu
As select * from student

1.5 执行存储过程

执行存储过程的语法格式：
[[EXEC[UTE]]
{[@return_status=]
procedure_name [;number]|@procedure_name_var}
[[@parameter=]{value|@variable
[OUTPUT]|[DEFAULT]
[,…n]
[WITHN RECOMPILE]
存储过程执行举例：
EXEC pro_stu1 ‘200501’,‘张力’,‘女’

二、触发器

2.1 什么是触发器

触发器是一种特殊的存储过程，其特殊性在于它并不需要由用户直接调用，当对表进行插入、删除、修改等操作时，它会自动执行。
触发器可以用来实施复杂的完整性约束，以防止对数据的不正确修改。
触发器不允许带参数、也不允许被调用。
触发器不能返回任何结果。

2.2 触发器的类型

DELETE 触发器
INSERT 触发器
UPDATE 触发器

2.3 触发器的工作原理

触发器触发时：
系统自动在内存中创建deleted表或inserted表。
这些表只读，不允许修改；触发器执行完成后，自动删除。
inserted表：
临时保存了插入或更新后的记录行。
可从inserted表种检查插入的数据是否满足要求。
如不满足，向用户报告错误消息，回滚插入操作。
deleted表
临时保存了删除或更新前的记录行。
可从deleted表检查被删除的数据是否满足业务需求。
如不满足，向用户报告错误消息，并回滚插入操作。
触发方式：
分为前/后触发和替代触发方式。
前/后触发:
执行修改语句→各种约束检查→执行后触发器。
执行前触发器→执行修改语句→各种约束检查。
前/后触发只能创建在表上，不能创建在视图上。
替代触发：
引起触发器执行的修改语句停止执行，仅执行触发器，这种触发方式称为替代触发。替代触发器可以创建在表或视图上。
引起触发器执行的修改语句若违反了某种约束:：
后触发方式不会激活触发器。前触发器会被激活。
替代触发器方式会激活触发器。
原因：
后触发必须在修改语句成功执行后才会激活触发器，当修改语句违反约束，而停止执行，所以不会激活触发器。
替代触发是用触发器的执行代替修改语句的执行。
修改语句没执行，也不存在约束的检查，所以使用
替代触发方式会激活触发器。

2.4 触发器的工作原理

DML触发器：
数据的更改操作引发的触发器。如：UPDATE、DELETE、INSERT。
DDL触发器：
对数据库对象的操作引发执行的触发器。如：CREATE、GRANT、REVOKE、DENY、DROP、ALTER。

2.5 利用SQL命令创建触发器

语法格式：
CREATE TRIGGER 触发器名
On 表名|视图名
With encryption
{ for after|before|instead of }{ delete|update|insert }
[ not for replication ]
[ for each row ]
As … SQL 语句