阅读 54

数据库原理及应用(八)——变量、存储过程和触发器

这是我参与更文挑战的第8天,活动详情查看: 更文挑战

一、变量

变量分为全局变量和局部变量。

p.s.

  1. 全局变量不能由用户定义,也不能被显式地赋值或声明,其名称以@@开头。

  2. 局部变量有用户自定义,需要用DECLARE声明,局部变量如同它的名字一样,只能在声明该变量的批处理语句或过程体内有效。

二、存储过程

使用存储过程的好处:

  1. 运行效率高
  2. 降低了客户机和服务器之间的通信量
  3. 方便实施企业规则

语句格式:

CREATE PROCEDURE 存储过程名[;版本号]
[{@参数 数据类型} [VARYING] [=默认值] [OUTPUT],...]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
[FOR REPLICATION]
AS 
    SQL语句
复制代码

例1:创建一个简单的存储过程,用于查看学生表的所有记录

ALTER PROCEDURE EXP1
AS 
	SELECT * FROM s
复制代码

image.png

例2:嵌套调用存储过程,求总成绩最高的学生信息:

CREATE PROCEDURE EXP6 @sno1 char(10) OUTPUT
AS 
	SELECT TOP 1 @sno1 = sc.sno
	FROM sc
	GROUP BY sc.sno
	ORDER BY SUM(sc.grade)	DESC
GO
CREATE PROCEDURE EXP7
AS
	DECLARE @sno2 char(10)
	EXECUTE EXP6 @sno2 OUTPUT
	SELECT *
	FROM s
	WHERE sno = @sno2;
GO

复制代码

image.png image.png

三、触发器

触发器的功能

  • 强化约束
  • 跟踪变化
  • 级联运行
  • 存储过程的调用

例3:利用INSTEAD OF触发器实现级联删除,即若在s表中删除一学生数据,则在sc表中应该同时删除有关学生的成绩信息。

CREATE TRIGGER tr1_S
ON s
INSTEAD OF DELETE
AS 
BEGIN TRANSACTION
	DELETE FROM sc
	WHERE sno IN (SELECT sno FROM DELETED)
	DELETE FROM s
	WHERE sno IN (SELECT sno FROM DELETED)
COMMIT TRANSACTION;
复制代码

运行结果:

CREATE TRIGGER tr1_S
ON s
INSTEAD OF DELETE
AS 
BEGIN TRANSACTION
	DELETE FROM sc
	WHERE sno IN (SELECT sno FROM DELETED)
	DELETE FROM s
	WHERE sno IN (SELECT sno FROM DELETED)
COMMIT TRANSACTION;
> Affected rows: 0
> 时间: 0.017s
复制代码
文章分类
后端
文章标签