MySQL过程式数据库对象-实验

一、实验目的

1、掌握创建、修改、删除和调用存储过程、存储函数、触发器和事件的方法。

2、掌握游标的使用方法。

二、实验内容

（一）：2学时

使用SQL语句完成下列题目：

1、创建存储过程P_compare，比较两个订单的订单总价，如前者比后者高就输出0，否则输出1.调用该存储过程比较20130411和20130414两个订单的订单总价，输出结果。（2分）

2、创建存储过程，利用循环语句控制fetch语句来检索并显示游标pro_cursor中可用的数据，其中游标pro_cursor指向的是从商品表中查询商品编号和商品名的记录集。（2分）

3、创建存储函数F_totalprice，返回给定用户，给定年份的总购买金额。调用存储函数，返回用户u0003，2013年的总购买金额。（2分）

4、创建一个存储函数F_bj，比较某商品的市场价和成本价，如果相同则返回YES，若不是则返回NO。调用该存储函数分别比较“燕雀”、“狮子犬”和“响尾蛇”的价格，输出结果。（2分）

5、查看名称为p_compare的存储过程。（1分）

6、删除名称为p_compare的存储过程。（1分）

（二）：2学时

使用SQL语句完成下列题目：

1、创建触发器ord_upd，实现当向lineitem表插入一行数据时，根据订单号对orders表的订单总价进行修改，订单总价加上该商品明细的金额。（2分）

2、创建一个触发器，当修改product表中商品的市场价格（listprice）时，则触发器将修改lineitem表中对应商品的成交价格unitprice，令其成交价格与商品的市场价格一致。（2分）

3、创建触发器usr_del，在account表中当删除客户信息时将orders表中与该客户有关的数据同时全部删除。（2分）

4、创建触发器ord_del，在orders表中当删除订单信息时将lineitem表中与该订单有关的数据同时全部删除。（2分）

5、查看petstore数据库中的全部触发器。（1分）

6、删除ord_del触发器。（1分）

（三）：2学时

使用SQL语句完成下列题目：

1、首先创建一个和orders表结构完全相同的订单历史表oldorders，再创建一个和lineitem表结构完全相同的订单明细历史表oldlineitem，然后创建事件e_orders，从现在开始执行，每隔一年执行一次，实现将orders表中订单日期超过一年的订单记录插入到oldorders表中，将lineitem表中订单日期超过一年的订单记录插入到oldlineitem表中。（4分）

2、创建事件dellorder，从现在开始执行，每隔一年执行一次，将订单时间超过1年的订单信息和订单明细信息删除。（3分）

3、查看事件e_orders的创建信息（1分）

4、临时关闭e_orders事件；（1分）

5、删除事件e_orders。（1分）

三、实验代码及注释

（一）
1.
delimiter //
create procedure P_compare(in m int(11),in n int(11),out k integer)
begin declare totalprice1,totalprice2 decimal(10,2);
select totalprice into totalprice1 from orders where orderid=m;
select totalprice into totalprice2 from orders where orderid=n;
if totalprice1>totalprice2
then set k=0;
else
 set k=1;
end if;
end //
delimiter ;
call P_compare('20130411','20130414',@k);
select @k;
2.
delimiter //
create procedure pro_cursor()
begin
declare productids char(10);
declare names varchar(30);
declare cursor_text CURSOR FOR select productid,name from product;
declare continue handler for not found set @cur=0;
set @cur=1;
open cursor_text;
fetch cursor_text into productids,names;
while @cur do
select productids,names;
fetch cursor_text into productids,names;
end while;
close cursor_text;
end //
delimiter ;
call pro_cursor();
3.
delimiter //
create function F_totalprice(cid char(6),cdate char(10))
returns decimal(10,2)
DETERMINISTIC
begin 
return (select  sum(totalprice)   from orders  where userid=cid and orderdate like cdate) ;
end //
delimiter ;
select F_totalprice('u0003','2013%');
4.
delimiter //
create function F_bj(n varchar(30))
returns char(6)
DETERMINISTIC
begin
declare a char(6);
declare clistprice decimal(10,2);
declare cunitcost decimal(10,2);
select listprice into clistprice from product where name=n;
select unitcost into cunitcost from product where name=n;
if clistprice=cunitcost  then
set a='YES';
else
set a='NO';
end if;
return a;
end //
delimiter ;
select F_bj('燕雀'),F_bj('狮子犬'),F_bj('响尾蛇');

5.show create procedure P_compare;
6.drop procedure P_compare;
（二）
1、
select * from orders where orderid=20130411;
insert into lineitem values(20130411,'K9-DB-01',2,120);

create trigger ord_upd before insert on lineitem   for each row
update orders set totalprice=totalprice+new.quantity*new.unitprice;

select * from orders where orderid=20130411;
2、
select * from lineitem where productid='K9-DL-01';

create trigger line_upd after update on product  for each row
update lineitem set unitprice =new.listprice where productid=old.productid;

update product set listprice=250 where productid='K9-DL-01';
select * from lineitem where productid='K9-DL-01';
3、
select * from orders where userid='u0002';

create trigger usr_del after delete on account for each row
delete  from orders where userid=old.userid;

delete from account where userid='u0002';
select * from orders where userid='u0002';
4、
select * from lineitem where orderid=20130413;

create trigger ord_del after delete on orders for each row
delete from lineitem where orderid=old.orderid;	


delete from orders where orderid=20130413;
select * from lineitem where orderid=20130413;
5、select * from information_schema.triggers where trigger_schema='petstore'\G
6、select *  from information_schema.triggers  where trigger_name='ord_del';
drop trigger ord_del;

（三）
1.
create table oldorders like orders;
create table oldlineitem like lineitem;
set @@global.event_scheduler = true;
delimiter //
create  event e_orders 
on schedule every 1 year 
do
begin
insert into oldorders 
select * from orders where (datediff(now(),orderdate)/365)>1 ;
insert into oldlineitem
select lineitem.* from lineitem,orders where (datediff(now(),orderdate)/365)>1 and lineitem.orderid=orders.orderid;
end //
delimiter ;
2.
set @@global.event_scheduler = true;
delimiter //
create  event dellorder 
on schedule every 1 year 
do
begin
delete  orders,lineitem
 from  orders,lineitem
where lineitem.orderid=orders.orderid and (datediff(now(),orders.orderdate)/365)>1;
end //
delimiter ;
3.show create event e_orders\G
4.alter event  e_orders disable;
5.drop event  e_orders;

四、运行结果截图

（一）

6.