数据库实验四 触发器与存储过程实验(SQL入门教学三 从0到1）

实验四 触发器与存储过程实验

前言

• 实验内容和要求

  （1）触发器的使用

        ①在数据表“学生”中创建update触发器，级联更新“选课”表相应的记录
    
        ②利用新表，将“学生”表中被删除的记录存储到“学生备份”表中，以供日后的数据查询和分析
    
    （2）存储过程的使用
    
        ①在建立的数据库中，建立一个存储过程，要求统计计算机系学生的人数，并将人数返回给用户
    
        ②在建立的数据库中，建立一个存储过程，要求
    
            统计成绩大于等于90分学生的人数
    
             统计成绩大于等于80分并且小于90分学生的人数，并将人数返回给用户
  

• 参考教材《数据库系统概论第五版》==P168 触发器 P255 8.3存储过程和函数==

• 参考博客数据库实验存储过程和触发器

• 触发器和存储函数这里知识点十分多，建议另外去找一些视频课和书籍系统学习

• 到了实验四，这个专栏就停止更新啦！四个实验只是带大家入门SQL，系统学习还是配套书籍和课本效果更好！

• 实验前,先熟悉常用的语句,方便理解以下代码(个人整理，如有错误，欢迎指正)

SQL知识点

一、触发器

触发器：是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，触发器将被保存在数据库服务器中。热河用户对表的增、删、改操作均有服务器自动激活相应的触发器，在关系数据库管理系统核心层进行集中的完整性控制。触发器类似于约束，但是比约束更加灵活，可以实施更为复杂的检查和操作，具有更精细和强大的数据控制能力

当对表SC的Grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表SC_U(Sno、Cno、Oldgrade、Newgrade),其中Oldgrade是修改前的分数，Newgrade是修改后的分数

CREATE TRIGGER SC_T /*SC_T是触发器的名字*/
AFTER UPDATE OF Grade ON SC /*UPDATE OF...是触发事件*/
							/*AFTER是触发时机，表示当对SC的Grade属性修改完成后再触发下面的规则*/
REFERENCING
	OLDROW AS OldTuple,
	NEWROW AS NewTuple
FOR EACH ROW /*行级触发器，即每执行依次Grade的更新，下面的规则就执行一次*/
WHEN(New Tuple.Grade>=1.1*OldTuple.Grade) /*触发器条件，只有该条件为真时才执行下面的INSERT操作*/
	INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade) 
	VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

FOR EACH STATEMENT语级触发器，执行完后面的语句才执行一次。

二、存储过程

SQL中存储过程优点：

1、重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量

2、减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量

3、安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程

4、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度

2.1创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) /*存储过程首部*/
AS <过程化SQL块>; /*存储过程体，描述该存储过程的操作*/

这里以银行存储为例子，不用学籍管理数据库

建立新表Account并插入两个账户

CREATE TABLE Account
(
accountnum CHAR(3),	-- 账户编号
total FLOAT		-- 账户余额
);

INSERT INTO Account VALUES(101,50);
INSERT INTO Account VALUES(102,100);

SELECT * FROM Account;

从账户1转指定数额款项到账户2中.假定账户关系为Account（Accountnum，Toral）,利用存储过程实现如下：

CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount INT,amount FLOAT)
/*定义存储过程TRANSFER，其参数为转入账户，转出账户，转账额度*/
AS DECLARE /*定义变量*/
  totalDepositOut Float;
  totalDepositIn Float;
  inAccountnum INT; --定义变量
BEGIN   /*检查转出账户的余额*/
  SELECT total INTO totalDepositOut FROM Account 
  WHERE Accountnum = outAccount; /*将Account表中的转出账户的余额赋给对应变量*/
  IF totalDepositOut IS NULL THEN  /*如果转出账户不存在或账户中没有存款*/
      ROLLBACK; /*回滚事务*/
      RETURN
  END IF; /*转出账户不存在，回滚*/
  IF totalDepositOut < amount THEN /*如果账户存款不足*/
      ROLLBACK; /*回滚事务*/
      RETURN
  END IF; /*账户余额不足，回滚*/
SELECT Accountnum INTO inAccountnum FROM Account
WHERE Accountnum = inAccount;
IF inAccount IS NULL THEN /*如果转入账户不存在*/
   ROLLBACK; /*回滚事务*/
   RETURN
END IF;

UPDATE Account SET total=total-amount WHERE accountnum = outAccount;/*修改转出账户余额，减去转出金额*/
UPDATE Account SET total = total + amount WHERE accountnum = inAccount; /*修改转入账户余额，增加转入额*/
COMMIT; /*提交转账事务*/
END;

2.2执行存储过程

CALL/PERFORM PROCEDURE 过程名([参数1,参数2,...]);

从账户01003815868转10000元到01003813828账户中

CALL PROCEDURE TRANSFER(01003815868,01003813828,10000);

2.3修改存储过程

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

2.4删除存储过程

DROP PROCEDURE 过程名();

实验内容

1.触发器的使用

1.1在数据表“学生”中创建update触发器，级联更新“选课”表相应的记录

首先为表SC添加级联(在实验一建表的时候没有添加级联，如果已经有了就跳过这步)

按照下面四张图依次点击

依次点开学籍管理-表-Student-触发器-新建触发器

这里新建触发器，在数据库已经给出了模板，只需要修改即可

CREATE TRIGGER Stu_UPDATE
	ON Student
	AFTER UPDATE
AS
BEGIN
DECLARE @bsno char(9),@asno char(9)
--'使用UPDATE触发器级联更新SC表中相关的行'
SELECT @bsno=Sno FROM DELETED
PRINT '更新前的学号:'+@bsno
SELECT @asno =Sno FROM INSERTED
PRINT '更新后的学号:'+@asno;
UPDATE SC SET Sno=@asno WHERE SC.Sno=@bsno
END
GO

测试结果,将192054401更改为192054481

UPDATE Student
SET Sno='192054481'
WHERE Sno='192054401';

运行结果如图，此时在SC和Student表该学生信息都被修改

1.2利用新表，将“学生”表中被删除的记录存储到“学生备份”表中，以供日后的数据查询和分析

同理，在Student表中新建触发器，不能在同一个触发器中写两个

CREATE TRIGGER Stu_DELETE
	ON Student
	AFTER DELETE
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
CREATE TABLE Student_BACK --在触发器中新建一个表
(Sno char(9) PRIMARY KEY,
 Sname char(20),
 Ssex char(2),
 Sage SMALLINT,
 Dno char(20)
);
PRINT '使用DELETE触发器备份删除Student表中相关的行-开始'
INSERT INTO Student_BACK
SELECT * FROM DELETED
PRINT '使用DLETED触发器备份删除Student表中相关一行-结束'
END
GO

测试结果,删除192054401的学生信息

DELETE
FROM Student
WHERE Sno='192054401'

查看Student_BACK表信息

SELECT *
FROM Student_BACK;

2.存储过程的使用

这里先介绍一下如何建立存储过程，依次点击下图，同样SQL Serve已经给出说明，只需要修改下面的代码即可

2.1在建立的数据库中，建立一个存储过程，要求统计计算机系学生的人数，并将人数返回给用户

CREATE proc Num (@a varchar(15),@b int output)
AS
BEGIN
SELECT @b = COUNT(sno) from Student where sdept = @a;
end

执行语句

DECLARE @num char;
EXECUTE Num '计算机工程系',@num output;
PRINT @num+'人';

2.2统计成绩大于等于90分学生的人数

CREATE PROCEDURE Proc_count
@Sdept varchar(20),
@num SMALLINT OUTPUT

AS
BEGIN
SELECT @num=count(*)
FROM Student
WHERE Sdept=@Sdept

DECLARE @sd varchar(20),@n SMALLINT
SELECT count(*)
FROM Student
WHERE @Sdept='计算机工程系'

END
GO

EXEC Proc_count @sd,@n OUTPUT
PRINT @n--convert(varchar(6),@n)

使用SQL创建储存

CREATE PROCEDURE Grade_num(@n INT OUTPUT)
AS
BEGIN
SELECT @n=COUNT(*) from SC where Grade>=90;
END
GO

执行存储过程

执行结果

这里扩展一个问题

实现根据用户输入的院系编号参数，查找该学院的学生人数，并以变量形式输出的功能

CREATE proc sp_sdept_student2 (@a varchar(15),@b int output)
AS
BEGIN
SELECT @b = COUNT(sno) from Student where sdept = @a;
end

这里写了两种执行结果的调用,注意==num定义==

DECLARE @num int;
EXECUTE sp_sdept_student2 '数学系',@num output;
PRINT @num;

DECLARE @num char;
EXECUTE sp_sdept_student2 '数学系',@num output;
PRINT @num+'人';

2.3统计成绩大于等于80分并且小于90分学生的人数，并将人数返回给用户

CREATE PROCEDURE PROC_ST @n1 SMALLINT OUTPUT
AS
BEGIN
SELECT @n1 = count(*) FROM SC WHERE Grade>=80 AND Grade<90

DECLARE @n1 SMALLINT
EXEC  PROC_ST @n1 OUTPUT,PRINT convert(varchar(6),@n1) 
END
GO

使用SQL创建储存

CREATE PROCEDURE Grade_num2(@n INT OUTPUT)
AS
BEGIN
SELECT @n=COUNT(*) from SC where Grade<90 AND Grade>=80;
END
GO

执行结果

思考题

触发器和存储过程何时执行？

触发器相当于是一个时间的触发装置，当满足了触发事件的条件后进行相应的处理操作，例如在数据库表中增加或者删除、修改了某条记录后，输出消息来告知该操作。这样就可以在这个表上设置一个触发器，触发条件为增加，删除或者修改了记录，触发的时间就是进行消息通知的输出.存储过程只有调用时才会执行

触发器能否带参数？

不能