• 数据库实践 Hw07

    1.1

    (多选题)以下关于触发器说法正确的有 ABCD

    • A、
    同一表不能创建两个相同触发时间、触发事件的触发程序（5.7.2版本之后可以）

    • B、
    不支持在视图上定义触发器

    • C、
    触发器中可以调用存储过程和存储函数

    • D、
    如果before触发器失败，则MySQL将不执行请求的操作

    1.2

    以下关于MySQL8.0触发器说法正确的有 ABD

    A、
    A. 触发器和存储过程之间的主要区别在于，当对表执行数据修改事件时，会自动调用触发器，而存储过程必须要明确地调用

    B、
    B. MySQL目前只支持FOR EACH ROW行级触发器，没有支持语句级触发器

    C、
    C. OLD列（旧值）不但可以引用，还可以修改

    D、
    D. 触发器执行的顺序是BEFORE触发器、表操作、AFTER触发器

    1. 3

    以下哪些语句会触发表上的INSERT触发器？ ABC

    A、
    A. INSERT

    B、
    B. REPLACE

    C、
    C. LOAD DATA

    D、
    D. UPDATE

    1.4

    下面关于游标的说法正确的是： ABD

    A．利用基于变量的select into语句，仅能处理一条记录的数据。通过游标（或光标），能够对查询的结果集进行循环处理。

    B．MySQL游标为只读，不可滚动和敏感。

    C．游标只允许应用程序对查询语句select返回的行结果集中每一行进行相同的操作，而不能进行不同的操作。

    D．游标必须在声明处理程序之前被声明，并且声明变量和条件之后。

    1.5

    下面关于触发器说法正确的是（） ABCD

    A、
    A. 在INSERT触发程序中，仅能使用NEW.col_name,没有旧值。

    B、
    B. 在DELETE触发程序中，仅能使用OLD.col_name,没有新值。

    C、
    C. 在UPDATE触发程序中，可以使用OLD.col_name来引用更新前的旧值，也能使用NEW.col_name来引用更新后的行中的新值。

    D、
    D. 使用OLD和NEW关键字，能够访问受触发程序影响的行中的字段值。

    2.1

    1. 先在account表中执行更新语句，将最后活跃日期和当前时间相距超过三年的所有账户的Status属性设置为“不活跃”。
    UPDATE account a SET a.`STATUS` = '不活跃' WHERE DATE_ADD(a.LAST_ACTIVITY_DATE, INTERVAL 3 YEAR) < CURDATE();

    • 1
    1. 然后，在acc_transaction上定义一个AFTER INSERT触发器，触发器名为“d_newTransaction”，当对acc_transaction表执行任何插入操作后被启动，将该账户在account表中的Status属性设置为 “正常”。

    正确答案：

    DROP TRIGGER IF EXISTS d_newTransaction; 
CREATE TRIGGER d_newTransaction AFTER INSERT ON acc_transaction FOR EACH ROW 
BEGIN
UPDATE account a
SET a.`STATUS` = '正常'
WHERE a.ACCOUNT_ID = new.ACCOUNT_ID; 
END;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    update account a set a.STATUS = '不活跃'
where date_add(a.last_activity_Date, interval 3 year) < curdate();
select * from account;
select  * from acc_transaction;


drop trigger if exists d_newTransaction;
delimiter $$
create trigger d_newTransaction
after insert
on acc_transaction
for each row
begin
update account set account.STATUS = '正常'
where account.account_id = new.account_id;
end $$ 
delimiter ;

insert into acc_transaction (TXN_ID, AMOUNT, TXN_DATE, TXN_TYPE_CD, ACCOUNT_ID, EXECUTION_BRANCH_ID, TELLER_EMP_ID)
values(28, 1, curdate(), 'CD', 1,1,1);
select * from account;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    2.2

    创建存储过程updateCloseDate，该存储过程更新account表中的关闭日期（CLOSE_DATE），根据产品编号做不同的更新操作，要求使用游标：

    1. 对产品编号对应类型为存款的账户（即产品编号PRODUCT_CD对应PRODUCT_TYPE_CD为ACCOUNT），如开户日期（OPEN_DATE）在2015-01-01之前（含）的，设置其关闭日期（CLOSE_DATE）为开户日期加20年，否则为开户日期加30年；
    2. 对产品编号对应类型为贷款的账户（即产品编号PRODUCT_CD对应PRODUCT_TYPE_CD为LOAN），如可用余额（AVAIL_BALANCE）少于100000的（含），设置其关闭日期（CLOSE_DATE）为开户日期加20年，否则为开户日期加30年；
    3. 对产品编号对应类型为保险的账户（即产品编号PRODUCT_CD对应PRODUCT_TYPE_CD为INSURANCE），设置其关闭日期（CLOSE_DATE）为开户日期加15年。
    DROP PROCEDURE IF EXISTS updateCloseDate;
CREATE PROCEDURE updateCloseDate () 
BEGIN
	DECLARE id INT;
	DECLARE type VARCHAR ( 255 );
	DECLARE opend DATE;
	DECLARE closed DATE;
	DECLARE bal DECIMAL ( 12, 4 );
	DECLARE cur CURSOR FOR 
	SELECT
		a.ACCOUNT_ID,
		p.PRODUCT_TYPE_CD,
		a.OPEN_DATE,
		a.CLOSE_DATE,
		a.AVAIL_BALANCE 
	FROM
		account a,
		product p 
	WHERE
		a.PRODUCT_CD = p.PRODUCT_CD;
	DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur;
	OPEN cur;
	REPEAT
			FETCH cur INTO id, type, opend, closed, bal;
		CASE type 
			WHEN 'ACCOUNT' THEN
				UPDATE account 
				SET CLOSE_DATE = DATE_ADD(opend, INTERVAL IF (opend <= '2015-01-01', 20, 30)YEAR)
				WHERE ACCOUNT_ID = id;
			
			WHEN 'LOAN' THEN
				UPDATE account 
				SET CLOSE_DATE = DATE_ADD( opend, INTERVAL IF ( bal <= 100000, 20, 30 ) YEAR ) 
				WHERE ACCOUNT_ID = id;
			
			WHEN 'INSURANCE' THEN
				UPDATE account 
				SET CLOSE_DATE = DATE_ADD( opend, INTERVAL 15 YEAR ) 
				WHERE ACCOUNT_ID = id;
		END CASE;
		UNTIL 0 
	END REPEAT;
	CLOSE cur;
	
END;

CALL updateCloseDate ();

SELECT a.ACCOUNT_ID, p.PRODUCT_TYPE_CD, a.OPEN_DATE, a.CLOSE_DATE, a.AVAIL_BALANCE
FROM account a, product p
WHERE a.PRODUCT_CD = p.PRODUCT_CD
ORDER BY a.ACCOUNT_ID;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    drop procedure  if exists updateCloseDate;
delimiter $$
create procedure updateCloseDate()
BEGIN

	declare ab decimal(12,4);
    declare pc varchar(10);
    declare cd date;
    declare od date;
    declare ptc varchar(255);
	declare cur cursor for 
		select avail_balance, product_cd, close_date, open_date, product_type_cd
        from account join product using(product_cd);
	DECLARE exit HANDLER FOR NOT FOUND CLOSE cur;
	open cur;
	repeat
		fetch cur into ab, pc, cd, od, ptc;
		update account set account.close_date = 
        case 
			when ptc = 'ACCOUNT'
				THEN case when od <= '2015-01-01'
						then open_date + interval 20 year
						else  open_date + interval 30 year
					end 
			when ptc = 'LOAN'
				THEN case when ab <= 100000
						then open_date + interval 20 year
						else  open_date + interval 30 year
					end 
            else close_date = open_date + interval 15 year
        end ;
    until null end repeat;
    close cur;
END $$
delimiter ;
call updateCloseDate();

select * from account;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    2.3

    在acc_transaction上定义触发器t_newTransaction，当往acc_transaction中插入一条数据时，依 据账户编号（ACCOUNT_ID）更新account表中对应账户的可用余额（AVAIL_BALANCE）和最后 活跃日期（LAST_ACTIVITY_DATE）：

    1. 如果插入数据的交易类型编码（TXN_TYPE_CD）为CD、TT、IC、LI则设置可用余额为当前可用余额加上交易金额、最后活跃日期为当前日期
    2. 如果插入数据的交易类型编码（TXN_TYPE_CD）为CW、TF则设置可用余额为当前可用余额 减去交易金额、最后活跃日期为当前日期；如当前可用余额减去交易金额小于0，则撤销对 acc_transaction此条数据的插入
    DROP TRIGGER IF EXISTS t_newTransaction;
CREATE TRIGGER t_newTransaction BEFORE INSERT ON acc_transaction FOR EACH ROW
BEGIN
	DECLARE bal INT;
	IF
		new.TXN_TYPE_CD = 'CW' OR new.TXN_TYPE_CD = 'TF' 
		THEN
			SELECT a.AVAIL_BALANCE INTO bal 
			FROM account a 
			WHERE a.ACCOUNT_ID = new.ACCOUNT_ID;
		IF
			bal < new.AMOUNT 
		THEN 
			DELETE FROM acc_transaction
			WHERE TXN_ID = new.TXN_ID; 
		ELSE UPDATE account a SET a.AVAIL_BALANCE = a.AVAIL_BALANCE - new.AMOUNT, 
			a.LAST_ACTIVITY_DATE = DATE(new.TXN_DATE) WHERE a.ACCOUNT_ID = new.ACCOUNT_ID; 
		END IF; 
	ELSE UPDATE account a SET a.AVAIL_BALANCE = a.AVAIL_BALANCE + new.AMOUNT, a.LAST_ACTIVITY_DATE = DATE(new.TXN_DATE) WHERE a.ACCOUNT_ID = new.ACCOUNT_ID; 
	END IF; 
END; 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    drop trigger t_newTransaction;
delimiter $$
create trigger t_newTransaction
after insert
on acc_transaction
for each row
begin
	update account
	set 
		account.avail_balance = 
			case
				when (new.txn_type_cd = 'CD' or new.txn_type_cd = 'TT'
					or new.txn_type_cd = 'IC' or new.txn_type_cd = 'LI')
				then account.avail_balance + new.amount
				when (new.txn_type_cd = 'CW' or new.txn_type_cd = 'TF')
				then account.avail_balance - new.amount
			end 
	where account.account_id = new.account_id;
		
	
	update account set account.last_activity_date =
		case when(account.avail_balance >= 0) then now()
		else account.last_activity_date
        end
    where account.account_id = new.account_id;
    
    update account set avail_balance  = 
    case  when(account.avail_balance < 0)  then avail_balance + new.amount
    else avail_balance
    end
    where account.account_id = new.account_id;
		
    
	if (select avail_balance from account 
		where account.account_id = new.account_id  < 0)
		then 
		delete from acc_Transaction
		where acc_Transaction.txn_id = new.txn_id;
	end if;
end$$
delimiter ;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42

    2.4

    新建teams表和players表

    – Table structure for teams

    DROP TABLE IF EXISTS teams;
CREATE TABLE teams(
id int NOT NULL,
name varchar(20) NOT NULL,
addres varchar(100) NOT NULL, 
nums int NOT NULL, 
PRIMARY KEY (id)
);

INSERT INTO `teams` ( `id`, `name`, `addres`, `nums` )
VALUES( 1, '火箭', '休斯顿', 3 );
INSERT INTO `teams` ( `id`, `name`, `addres`, `nums` )
VALUES( 2, '湖人', '洛杉矶', 2 );
INSERT INTO `teams` ( `id`, `name`, `addres`, `nums` )
VALUES( 3, '勇士', '金洲', 1 );

DROP TABLE IF EXISTS `players`;
CREATE TABLE `players` ( 
`id` int NOT NULL, `name` varchar(100) NOT NULL, 
`team_id` int NOT NULL, 
`salary` decimal(10,2) NOT NULL, 
PRIMARY KEY (`id`) 
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    请在players表中创建如下两个触发器：

    1. before触发器：插入player表之前，检测该条插入数据的salary是不是小于10,000，如果是则增大10倍；
    2. after触发器：每添加一条数据，就更新team表中nums值(该队下的总人数)。
      输入以下SQL语句，查看效果。
    insert into players VALUES(2,"科比",2,500000);
insert into players VALUES(3,"艾灵顿",2,100.0);
insert into players VALUES(4,"麦迪",1,30000.0);
insert into players VALUES(5,"姚明",1,30000.0);
insert into players VALUES(6,"沃尔",1,5000.0);
insert into players VALUES(7,"戈登",1,6000.0);
最后请运行查看结果: SELECT * FROM teams;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    DROP TRIGGER IF EXISTS `before_inseret`;
CREATE TRIGGER `before_inseret` BEFORE INSERT ON `players` FOR EACH ROW
BEGIN
	IF
		new.salary < 10000 THEN
		SET new.salary = 10 * new.salary;
	END IF;
END;

2. 
DROP TRIGGER IF EXISTS `insert_player`;
CREATE TRIGGER `insert_player` AFTER INSERT ON `players` FOR EACH ROW
BEGIN
	UPDATE teams 
	SET nums = nums + 1 
	WHERE teams.id = new.team_id;
END;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    drop trigger BEFORE_INSERT_TRIGGER;
delimiter $$
create trigger BEFORE_INSERT_TRIGGER
BEFORE insert
on players
for each row
begin
if ( new.salary < 10000)
	then set new.salary = new.salary * 10;
end if;
end$$
delimiter ;

drop trigger AFTER_INSERT_TRIGGER;
delimiter $$
create trigger AFTER_INSERT_TRIGGER
AFTER insert
on players
for each row
begin
	update teams
	set teams.nums = teams.nums + 1;
	
end$$
delimiter ;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
  • 相关阅读:
    python使用django的详细过程
    大模型解决方案：具体业务场景下的智能表单填充（附代码）
    【LeetCode热题100】【图论】课程表
    word实用小技能（肝论文必备）
    TiniXml C++ 开源代码中的几个概念
    LabVIEW中编程更改进程的优先级
    完美世界服务端设置小技巧
    UTF-8字符集成为Java 18默认字符集？发布周期将至，Java 18现身
    卫星多址方式
    语音相似度评价
  • 原文地址：https://blog.csdn.net/JamSlade/article/details/128063725