数据库实验五、六（存储过程、游标与触发器）

一、实验五

(1) 建立存储过程，查询在指定银行贷款、且贷款金额高于此银行的平均贷款金额的法人名称、贷款日期和贷款金额。 要求银行名称为输入参数，如果指定的银行不存在，则显示“指定的银行不存

在 ” 。

delimiter $$
create procedure highaver(in bank_name varchar(32))
begin
if bank_name in(select bname from bankt) then
select ename 法人名称,Lamount 贷款日期,LSdate 贷款金额
from loadt left join let on loadt.eno=let.eno
left join bankt on loadt.bno=bankt.bno

where bname=bank_name and lamount>
(select avg(lamount) from loadt left join bankt on loadt.bno=bankt.bno where bname=bank_name);

else
	select '指定的银行不存在';
end if;
end $$

call highaver('中国工商银行股份有限公司北京新发地支行')$$

(2) 建立存储过程，查询指定法人的总贷款金额。要求法人名称为输入参数，总贷款金额为输出参

数。

delimiter $$
create procedure Eqamount(in letname varchar(100),out loadamount int)
begin
	if letname in (select ename from let) then
	select sum(lamount) into loadamount
	from loadt left join let on loadt.eno=let.eno
	where ename=letname;
	else
	set letname='北京金开培训学校';  #输入法人不存在则使用默认值
	select sum(lamount) into loadamount
	from loadt left join let on loadt.eno=let.eno
	where ename=letname;
	
	end if;	
end $$
CALL Eqamount('杭州天兔创业投资有限公司',@loadamount);
SELECT @loadamount 贷款金额;

(3) 建立函数，根据银行编号，查询并返回该银行的总贷款金额。并使用 UPDATE 命令，调用该函

数，更新 BankT 表中的贷款总额（ Bamount ）字段。

delimiter $$
create function BLamount(bankno char(12))
	returns int deterministic
begin
	declare bloadamount int;
	select sum(lamount) into bloadamount
	from loadt
	where loadt.bno=bankno;
	return bloadamount;
end $$
delimiter;

update bankt set bamount=BLamount('103100009138')
where bno='103100009138';

二、实验六

（一）游标

(1) 建立存储过程，查询指定法人的总贷款金额。要求法人代码为输入参数，总贷款金额为输出参

数。

delimiter $$
create procedure Enoamount(in letno char(7),out loadamount int)
begin
	if letno in (select eno from let) then
	select sum(lamount) into loadamount
	from loadt
	where loadt.eno=letno;
	end if;	
end $$
CALL Enoamount('M052014',@loadamount);
SELECT @loadamount 贷款金额;

(2) 编写存储过程实现以下操作，使用游标，访问 LET 表，获取有贷款信息的法人代码，再调用

（1）所建立的存储过程获得该法人的贷款总额，并更新 LET 表中的 Eamount 字段。

delimiter $$
create procedure upletamount()
begin
	declare e char(7);
	declare stop int default 0;
	declare loadeno cursor
	for
	(select let.eno 
	from let,loadt
	where let.eno=loadt.eno);
	declare continue handler for sqlstate '02000' set stop = 1;
	open loadeno;
	fetch loadeno into e;
	while stop<>1 do
	call Enoamount(e,@loadamount);
	update let set Eamount=@loadamount where eno=e;
	fetch loadeno into e;
	end while;
	close loadeno;
end $$

call upletamount();

（二）触发器

(3) 为 LoanT 表创建一个 INSERT 操作上的 AFTER 触发器，当有新的贷款记录插入时，调用（1）所 建立的存储过程，获得该法人的贷款总额，并更新 LET 表中的 Eamount 字段。

(5) 创建一个 INSERT 、 UPDATE 操作上的触发器（根据题目要求自行判断应建立 BEFORE 触发器还 是 AFTER 触发器），为贷款表 (LoanT) 定义规则“法人的总贷款金额不得超过法人注册资金的 10 倍，如果贷款总额达到阈值，则拒绝添加/ 修改数据的请求”。

delimiter $$
create trigger newamount after insert on loadt
FOR each row 
begin
call Enoamount(new.eno,@loadamount);
update let set Eamount=@loadamount where new.eno=eno;
end $$
delimiter;

insert into loadt(eno,bno,lsdate,lamount,lterm,ledate) values('M052024','102100022525','2017-06-03',5,15,null);

(4) 为 LoanT 创建一个 DELETE 操作上的触发器（根据题目要求自行判断应建立 BEFORE 触发器还 是 AFTER 触发器），当删除记录时，调用（1）所建立的存储过程，获得该法人的贷款总额，更 新 LET 表中的 Eamount 字段。

delimiter $$
create trigger delload after delete on loadt
FOR each row 
begin
call Enoamount(old.eno,@loadamount);
update let set Eamount=@loadamount where eno=old.eno;
end $$
delimiter;

Delete from loadt where eno='M052024' and bno='102100022525'; 

(5) 创建一个 INSERT 、 UPDATE 操作上的触发器（根据题目要求自行判断应建立 BEFORE 触发器还 是 AFTER 触发器），为贷款表 (LoanT) 定义规则“法人的总贷款金额不得超过法人注册资金的 10 倍，如果贷款总额达到阈值，则拒绝添加/ 修改数据的请求”。

drop trigger IF EXISTS intrigger; 
drop trigger IF EXISTS uptrigger; 
delimiter $$
create trigger intrigger before insert on loadt
for each row 
begin
if new.eno in (select eno from let where eamount>=ecapital*10) then
signal sqlstate '45000'
set MESSAGE_TEXT = "法人总贷款金额不得超过法人注册资金的10倍。";
end if;
end $$
delimiter;


delimiter $$
create trigger uptrigger before update on loadt
for each row
begin
if old.eno in (select eno from let where eamount>=ecapital*10) then
signal sqlstate '45000'
set MESSAGE_TEXT = "法人总贷款金额不得超过法人注册资金的10倍。";
end if;
end $$
delimiter;

insert into loadt(eno,bno,lsdate,lamount,lterm,ledate) values('M052024','102100022525','2017-02-03',5,15,null);
update loadt set lterm=15 where eno='M052024' and bno='102100020778';