个人笔记：数据库——过程T-SQL （存储过程、触发器ect）

徐昕

2023-12-01

本文仅供参考学习使用，谢谢

Transact-SQL引用数据库引擎

过程Transact-SQL

存储过程

利用T-SQL 以存储过程和触发器实现编程

一、存储过程的定义

存储过程就是利 Transact-SQL语言编写的存储在数据库内的可以执行一些特殊的或常用的数据处理的程序，它是数据库对象之一，可以通过编译在数据库后台执行，是数据库端编程的重要技术。
即：存储过程是存放在数据库服务器上的预先定义与编译好的T-SQL语句集合，是一个独立的数据库对象。
我们可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。
存储过程在创建时就被编译和优化，在第一次执行时进行语法检查和编译。编译好的版本存储在过程高速缓存中供应用程序多次调用

二、存储过程的特点

实现了模块化编程。
存储过程具有对数据库立即访问的功能。
使用存储过程可以加快程序的运行速度。
使用存储过程可以减少网络流量。
使用存储过程可以提高数据库的安全性。
存储过程由应用程序激活，而不是由系统自动执行
存储过程可以接受输入参数和返回值。

三、存储过程的使用

1. 创建存储过程

存储过程的创建
存储过程的定义主要包括两部分：
- 一是过程名及参数的说明
- 二是过程体的说明

CREATE PROCEDURE <存储过程名>
[<参数列表>]
AS	
<过程体>

参数列表：由一个或多个参数说明组成，每个参数说明包括参数名和参数的数据类型，存储过程可以没有任何参数。

//参数格式为：
@ 参数名 数据类型 [=缺省值] [OUTPUT]

//存储过程可以分为无参数和有参数两种
//有参数的又可以分为	带OUTPUT	和	不带OUTPUT	两种

过程体：是实现存储过程功能的一组T-SQL语句，可以包含任意多的SQL语句。
1. 但sql语句中不能使用CREATE（VIEW、TRIGER、DEFAULT、RULE、PROCEDURE等）语句，同时要慎重使用其他的CREATE、DROP等语句。
2. 为了使存储过程的设计更方便，功能更强大。可使用流程控制语句，主要有以下几种：
  - 赋值语句：可将SQL表达式的值赋值给局部变量。
  - 分支语句：用于设计分支程序。如： IF语句、CASE
    语句等。
  - 循环语句：如：WHILE等语句。
  - 调用存储过程语句：CALL，或EXECUTE和从存储过程返回语句RETURN。
3. 在存储体中除了可以使用流程控制语句外，还可以使用游标。

eg：

//创建一个不带参数的存储过程，完成查询每位学生的选课情况及其成绩。
CREATE PROCEDURE Proc1 
AS
SELECT Student.Sno,Sname,Course.Cno,Cname,Grade  
     FROM Student,Course,SC   
       WHERE Student.Sno=SC.Sno 
         AND Course.Cno=SC.Cno

eg：

//创建一个带参数的存储过程，完成查询指定课程的选修情况。
CREATE PROCEDURE Proc2 @x CHAR(2)
AS
SELECT Student.Sno,Sname,Course.Cno,Cname,Grade  
   FROM Student,Course,SC 
    WHERE Student.Sno=SC.Sno 
      AND Course.Cno=SC.Cno and SC.Cno=@x 
        ORDER by SC.Sno

eg：

//创建一个带参数并有返回值的存储过程，完成输出指定学号的学生的‘2’号课程的成绩，并将结果，赋给一输出参数。
CREATE PROCEDURE Proc3 
    @x CHAR(5),@vgrade INT OUTPUT
AS 
    SELECT @vgrade=Grade FROM SC  
         WHERE Sno=@x AND Cno=‘2’
    RETURN

--创建名为 GetStuCou_Ext 的返回多个结果集的存储过程
create procedure GetStuCou_Ext
@StuNo    nvarchar(64),
@Height nvarchar(32)
as
begin
    declare @Var nvarchar(10)    --定义变量

    set @Var='123'        --赋值变量

    --定义表变量
    declare @StuTab table 
    (
        ID     int not null primary key,
        StuNo    nvarchar(50) unique,
        Name varchar(50),
        Sex varchar(10),
        Height varchar(10)
    )
    --表变量只能在定义的时候添加约束

    --定义临时表
    create table #Tab
    (
        ID     int not null primary key,
        StuNo    nvarchar(50),
        Name varchar(50),
        Sex varchar(10),
        Height varchar(10)
    )

    alter table #Tab add constraint S_UNIQUE unique(StuNo)

    --临时表可以在之后添加约束
    
    if(@StuNo is not null and @StuNo <> '')
    begin
        insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把数据插入表变量
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student 
        where S_StuNo=@StuNo

        insert into #Tab(ID,StuNo,Name,Sex,Height)    --把数据插入临时表
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student 
        where S_StuNo=@StuNo
    end

    if(@Height is not null and @Height <> '')
    begin
        insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把数据插入表变量
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student
        where S_Height=@Height

        insert into #Tab(ID,StuNo,Name,Sex,Height)    --把数据插入临时表
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student
        where S_Height=@Height
    end

    SELECT * FROM @StuTab
    select * from #Tab
end

--执行名为 GetStuCou_DSS 的返回多个结果集的存储过程
execute GetStuCou_Ext '005','185'

2. 执行存储过程

在SQL Server中，使用EXECUTE语句执行存储过程。
EXECUTE语句一般格式如下：

EXEC  [UTE] <存储过程名> [ [过程参数变量=] { 值| 变量 [OUTPUT] }

eg：

//执行例2定义的存储过程。
EXEC Proc2 @x=’3’   
//或   
EXEC Proc2  ’3’

eg：

//执行例8定义的存储过程。
DECLARE @v1 CHAR(5),@v2 INT 
SELECT @v1=’00101’
EXEC Proc3 @v1,@v2 OUTPUT
SELECT @v2

3. 修改和删除存储过程

修改存储过程：

ALTER PROC[EDURE] <存储过程名> {同定义}

删除存储过程：DROP PROCEDURE

DROP PROC	[EDURE] <存储过程名>

四、存储过程的种类

存储过程分系统存储过程、扩展存储过程和用户自定义的存储过程。
系统存储过程是SQL Server 自身配备的具有特殊功能和用途的存储过程，SQL Server 也配备了部分扩展存储过程。
用户自定义的存储过程是用户使用Transact-SQL编写的用户常用的一些程序，这些程序代存储在SQL Server数据库中，并可以通过编译行。
调用存储过程类似调用系统函数，可以输入参数，也可以通过参数输出执行的结果，大大提了代码的重用性、安全性和执行速度。

1.系统存储过程(sp_)：

SQL Server系统存储过程是为管理员而提供的，SQL Server安装时在master数据库中创建并由系统管理员拥有。使用户可以很容易地从系统表中取出信息，管理数据库，并执行涉及更新系统表的其他任务。系统存储过程命令均以sq_打头，其作用进行数据库管理。
SQL Server提供了许多系统存储过程以方便检索和操纵存放在系统表中的信息，系统存储过程可以在任意一个数据库中执行。
例如，常用的系统存储过程有：
- sp_ helpdb(database_name)：返回指定数据库信息
- sp_help(object)：返回指定数据库对象的信息
- sp_addlogin：建立SQL Server用户帐号
- sp_datatype_info：返回由当前环境支持的数据类型的信息
- sp_monitor：按一定格式显示的系统全局变量的当前值

sp_addlogin [ @loginame = ] 'login' 
    [ , [ @passwd = ] 'password' ] 
    [ , [ @defdb = ] 'database' ] 
    [ , [ @deflanguage = ] 'language' ] 
    [ , [ @sid = ] sid ] 
    [ , [ @encryptopt= ] 'encryption_option' ]

2.扩展存储过程(xp_)

扩展存储过程提供一种类似于存储过程的方式，它们是动态装入和执行的动态连接库（DLL）内的函数，无缝地扩展SQL Server功能。SQL Serve之外的动作可以很容易地触发，外部信息返回到SQL Server。另外，扩展存储过程支持返回状态码和输出。
注意：必须从master数据库执行扩展存储过程。
用户可以创建自己的扩展存储过程。
例如，下面是一些的扩展存储过程：

xp_cmdshell：作为一个操作系统外壳执行指定命令串，并以文本形式返回任何输出。
xp_logevent：在SQL Server日志文件或WindowsNT事件查看器中记录用户定义的信息。
xp_msver：返回SQL Server版本信息及各种环境信息。

3.远程存储过程

是从连接到不同服务器的远程服务器或客户机调用的存储过程。

4.局部存储过程

局部存储过程在各个用户数据库中创建。只能由创建它的用户调用。

5.临时存储过程

临时存储过程可是局部的，名字前的前缀是"#"；也可是全局的，名字前的前缀是"##"。临时存储过程存放在tempdb数据库中。
局部临时存储过程在单个用户会话中使用，该用户退出时，自动被删除。
全局临时存储过程所有用户都可以使用，当最后一个用户退出时，自动被删除。

函数

函数和存储过程的异同：

同：都是持久性存储模块
异：函数必须指定返回的类型

函数的定义语句格式：

CREATE FUNCTION 函数名 ([参数1,参数2,...]) 
RETURNS <类型> AS <过程化SQL块>;

函数的执行语句格式

CALL/SELECT 函数名 ([参数1,参数2,...]);

修改函数

重命名：

ALTER FUNCTION 过程名1 RENAME TO 过程名2;

重新编译：

ALTER FUNCTION  过程名 COMPILE;

ﰁ

触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程，当一个触发器建立后，它作为一个数据库对象被存储。当某个触发事件发生时，触发器被触发，执行一系列操作
触发器一旦由某个用户建立，任何用户对该触发器指定的数据进行增、删或改操作时，DBMS系统将自动激活相应的触发器，定义在触发器中的功能将被DBMS执行，在核心层进行集中的完整性控制
触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力，不仅能实现完整性规则，而且能保证一些较复杂业务规则的实施

补充：
使用触发器时，SQL Server提供了两张特殊的临时表：
inserted表和deleted表。

这两张表存在于高速缓存中，它们与创建触发器的表有相同的结构。
用户可以使用该表检查某些修改操作的效果。
但用户不能直接修改该表中的数据。
用户可以使用该表的内容作为查询操作的判断条件，但要在FROM中写出使用的表名（inserted 或 deleted）

eg：
假设学生表S新增一属性Cnum(类型为INT,初值均为0), 记录该学生的选课数，分别创建如下触发器：
（1）INSERT 触发器

//创建INSERT 触发器Tri1，其功能是：在学习表SC插入一条记录后，立即更新学生表S的Cnum属性，即将原值加一。
CREATE  TRIGGER Tri1 ON SC 
FOR INSERT
	AS
    UPDATE Student SET Cnum=Cnum+1
	WHERE Student.Sno=
          (SELECT Sno FROM inserted 
          WHERE	Student.Sno=inserted.Sno)

⑵ DELETE触发器

//创建DELETE 触发器Tri2，其功能是：在删除学习表SC一条记录后，立即更新学生表S的Cnum属性，即将原值减一。
CREATE  TRIGGER Tri2  ON SC 
FOR DELETE
	AS
	UPDATE Student SET Cnum=Cnum-1
    WHERE Student.Sno=
		(SELECT Sno FROM deleted   
		WHERE Student.Sno=deleted.Sno)

⑶ UPDATE触发器

//创建UPDATE 触发器Tri3，功能是：在SC表更新一条记录的学号后，立即更新学生表S的Cnum属性，改前学号学生的Cnum值减1，改后学号学生的Cnum值加1。
CREATE  TRIGGER Tri3 ON SC 
FOR UPDATE  
	AS
	IF UPDATE(Sno)
	BEGIN
    UPDATE Student SET Cnum=Cnum-1 
		WHERE Student.Sno=(
			SELECT Sno 
			FROM deleted 
			WHERE Student.Sno=deleted.Sno)  
	UPDATE Student SET Cnum=Cnum+1 
		WHERE Student.Sno=(
			SELECT Sno 
			FROM inserted 
			WHERE Student.Sno=inserted.Sno)
   END

1.定义触发器

MSSQL：

//Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name


//Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)

CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

//Trigger on a LOGON event (Logon Trigger)

CREATE TRIGGER trigger_name 
ON ALL SERVER 
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

//表的拥有者才可以在 表上创建触发器
CREATE TRIGGER <触发器名>	//触发器名可以包含模式名，也 可以不包含模式名
							//同一模式下，触发器名必须是 唯一的
							
							//• 触发器只能定义在基本表上，不能定义 在视图上
							//• 当基本表的数据发生变化时，将激活定 义在该表上相应触发事件的触发器
{BEFORE | AFTER} <触发事件> ON <表名>			//触发事件：
//AFTER/BEFORE是触发的时机					//• INSERT、DELETE或UPDATE 
//• AFTER表示在触发事件的操作执行之后激活触发器	//• 几个事件的组合
//• BEFORE表示在触发事件的操作执行之前激活触发器	//• UPDATE OF<触发列，...>
 													
REFERENCING NEW|OLD ROW AS <变量> FOR EACH {ROW | STATEMENT}
									//触发器类型
									//• 行级触发器(FOR EACH ROW)
									//• 语句级触发器(FOR EACH STATEMENT)
[WHEN <触发条件>]<触发动作体>
//• 触发器被激活时，只有当触发条件为真时触发动作体才执行;否则触发动作体不执行。
//• 如果省略WHEN触发条件，则触发动作体在触发 器激活后立即执行
				//• 触发动作体可以是一个匿名PL/SQL 过程块，也可以是对已创建存储过程的调用
				//• 如果是行级触发器，用户都可以在过程体中使用NEW和OLD引用事件，之后的新值和事件之前的旧值
				//• 如果是语句级触发器，则不能在触 发动作体中使用NEW或OLD进行引 用
				//• 如果触发动作体执行失败，激活触 发器的事件就会终止执行，触发器 的目标表或触发器可能影响的其他 对象不发生任何变化

CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS <变量> 
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>

eg：

在TEACHER表上创建一个AFTER UPDATE触发器，
触发事件是UPDATE语句:UPDATE TEACHER SET Deptno=5;
假设表TEACHER有1000行
如果是语句级触发器，那么执行完该语句后，触发动作只发生一次
如果是行级触发器，触发动作将执行1000次

eg：

//当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中: 
//SC_U(Sno,Cno,Oldgrade,Newgrade)

CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC REFERENCING
	OLD row AS OldTuple,
	NEW row AS NewTuple
FOR EACH ROW
WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade)
INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade) VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

//将每次对表Student的插入操作所增加的学生个数 记录到表StudentInsertLog中。
CREATE TRIGGER Student_Count 
AFTER INSERT ON Student 
REFERENCING
	NEW TABLE AS DELTA 
FOR EACH STATEMENT
	INSERT INTO StudentInsertLog (Numbers) 
	SELECT COUNT(*) FROM DELTA

2.激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器
自动执行
一个数据表上可能定义了多个触发器，遵循如下的执行顺
序:
- 执行该表上的BEFORE触发器;
- 激活触发器的SQL语句;
- 执行该表上的AFTER触发器。

3.删除触发器

触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除

DROP TRIGGER <触发器名> ON <表名>;

SQL server触发器中 update insert delete 示例

INSERT：

表1 (ID,NAME)
表2 (ID,NAME)
当用户插入表1数据后，表2也被插入相同的数据
CREATE TRIGGER TRI1
ON 表1
FOR INSERT
AS
BEGIN 
INSERT INTO 表2 SELECT * FROM INSERTED
END
GO

DELETE：

表1 (ID,NAME)
表2 (ID,AGE,GENDER)
当用户删除表1某条ID的数据后，表2相应ID的数据也被删除 
CREATE TRIGGER TRI2 
ON 表1
FOR DELETE
AS
BEGIN 
DECLARE @id INT
SELECT @id FROM DELETED   
DELETE 表2 
WHERE ID = @id
END
GO

UPDATE：

表1 (ID,NAME)
表2 (ID,NAME)
当用户更改表1 NAME列数据后，表2相应ID的数据也同时更新
CREATE TRIGGER TRI3
ON 表1
FOR UPDATE
AS
IF UPDATE(NAME)
BEGIN
    UPDATE 表2
    SET A.NAME =  B.NAME
    FROM 表2 A, INSERTED B
    WHERE A.ID = B.ID
END
GO

事务

定义
- 一个数据库操作序列
- 一个不可分割的工作单位
- 恢复和并发控制的基本单位
事务和程序比较
- 在关系数据库中，一个事务可以是一条或多条SQL语句,也可以包含一个或多个程序。
- 一个程序通常包含多个事务

1.事务的属性

事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为原子性、一致性、隔离性和持久性(ACID)属性，只有这样才能成为一个事务。

原子性 atom

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

一致性 consist

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。

隔离 isolate

由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。

持久性 durable

事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

2.事务模式

事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。

自动提交事务

每条单独的语句都是一个事务。

显式事务

每个事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT 或 ROLLBACK 语句显式结束。

隐式事务

在前一个事务完成时新事务隐式启动，但每个事务仍以 COMMIT 或 ROLLBACK 语句显式完成。

批处理级事务

只能应用于多个活动结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显式或隐式事务变为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动由 SQL Server 进行回滚

3.指定事务边界

可以使用 Transact-SQL 语句或 API 函数和方法来确定数据库引擎事务启动和结束的时间

Transact-SQL 语句

可以使用 BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION、ROLLBACK WORK 和 SET IMPLICIT_TRANSACTIONS 语句来描述事务。这些语句主要用于 DB 库应用程序和 Transact-SQL 脚本（如使用 osql 命令提示实用工具运行的脚本）中。

API 函数和方法

数据库 API（如 ODBC、OLE DB、ADO 和 .NET Framework SQLClient 命名空间）包含用于描述事务的函数或方法。这些是数据库引擎应用程序中用于控制事务的主要机制

每个事务都必须只由其中一种方法管理。在对同一事务使用两种方法会导致出现不确定的结果。例如，不应先使用 ODBC API 函数启动一个事务，再使用 Transact-SQL COMMIT 语句完成该事务。这样将无法向 SQL Server ODBC 驱动程序通知已提交该事务。在这种情况下，应使用 ODBC SQLEndTran 函数结束该事务

事务的定义

BEGIN { TRAN | TRANSACTION } 
    [ { transaction_name | @tran_name_variable }
      [ WITH MARK [ 'description' ] ]
    ]
[ ; ]

参数

transaction_name

分配给事务的名称。transaction_name 必须符合标识符规则，但标识符所包含的字符数不能大于 32。仅在最外面的 BEGIN…COMMIT 或 BEGIN…ROLLBACK 嵌套语句对中使用事务名。

@tran_name_variable

用户定义的、含有有效事务名称的变量的名称。必须用 char、varchar、nchar 或 nvarchar 数据类型声明变量。如果传递给该变量的字符多于 32 个，则仅使用前面的 32 个字符；其余的字符将被截断。

WITH MARK [ ‘description’ ]

指定在日志中标记事务。description 是描述该标记的字符串。如果 description 是 Unicode 字符串，那么在将长于 255 个字符的值存储到 msdb.dbo.logmarkhistory 表之前，先将其截断为 255 个字符。如果 description 为非 Unicode 字符串，则长于 510 个字符的值将被截断为 510 字符。
如果使用了 WITH MARK，则必须指定事务名。WITH MARK 允许将事务日志还原到命名标记

eg：

BEGIN TRANSACTION t11 WITH mark 'aaa';
	DECLARE	@errorflag INT;
	SET @errorflag = 1;
	DELETE FROM	Student WHERE	Sno = '7777777';
	IF	@errorflag =- 1 
	BEGIN
		ROLLBACK TRANSACTION t11;
		RETURN;
	END;
	ELSE 
	BEGIN
		DELETE FROM	CJ WHERE	Sno = '7777777';
		COMMIT TRANSACTION t11;
	END;
END;