一、实验项目：
MySQL过程式数据库对象。
二、实验目的
1、掌握创建、修改、删除和调用存储过程、存储函数、触发器和事件的方法。
2、掌握游标的使用方法。
三、实验内容
（一）：
1、创建存储过程P_compare，比较两个订单的订单总价，如前者比后者高就输出0，否则输出1.调用该存储过程比较20130411和20130414两个订单的订单总价，输出结果。（2分）
1. delimiter $$ 2. create procedure p_compare(in a int,in b int,out result tinyint) 3. begin 4. declare t1 decimal(10,2); 5. declare t2 decimal(10,2); 6. select totalprice into t1 from orders where orderid=a; 7. select totalprice into t2 from orders where orderid=b; 8. if t1>t2 then set result=0; 9. else set result=1; 10. end if; 11. end $$ 12. delimiter ; 13. call p_compare(20130411,20130414,@result); 14. select @result;
2、创建存储过程，利用循环语句控制fetch语句来检索并显示游标pro_cursor中可用的数据，其中游标pro_cursor指向的是从商品表中查询商品编号和商品名的记录集。（2分）
1. delimiter $$ 2. create procedure proc_cursor() 3. begin 4. declare v_pno char(10) default ' '; 5. declare v_pname varchar(30) default ' '; 6. declare pro_cursor cursor for select productid, name from product; 7. declare continue handler for not found set @dovar=1;#定义处理程序 8. set @dovar =0; 9. open pro_cursor; 10. fetch_Loop:LOOP 11. fetch pro_cursor into v_pno,v_pname; 12. if @dovar=1 then leave fetch_Loop; 13. else select v_pno,v_pname; 14. end IF; 15. end LOOP fetch_Loop; 16. close pro_cursor; 17. end $$ 18. delimiter ; 19. call proc_cursor();
3、创建存储函数F_totalprice，返回给定用户，给定年份的总购买金额。调用存储函数，返回用户u0003，2013年的总购买金额。（2分）
1. delimiter $$ 2. create function f_totalprice(u_id char(6),o_year char(4)) returns decimal(10,2) 3. begin 4. declare s_price decimal(10,2); 5. select sum(totalprice) into s_price from orders 6. where userid=u_id and year(orderdate)=o_year; 7. return s_price; 8. end $$ 9. delimiter ; 10. select f_totalprice('u0003','2013');
4、创建一个存储函数F_bj，比较某商品的市场价和成本价，如果相同则返回YES，若不是则返回NO。调用该存储函数分别比较“燕雀”、“狮子犬”和“响尾蛇”的价格，输出结果。（2分）
1. delimiter $$ 2. create function bj(nm varchar(30)) returns char(8) 3. begin 4. declare num1 decimal(10,2); 5. declare num2 decimal(10,2); 6. declare result char(8); 7. select listprice into num1 from product where name=nm; 8. select unitcost into num2 from product where name=nm; 9. if num1!=num2 then set result='NO'; 10. else set result='YES'; 11. end if; 12. return result; 13. end $$ 14. delimiter ; 15. select bj('燕雀'); 16. select bj('狮子犬'); 17. select bj('响尾蛇');
5、查看名称为p_compare的存储过程。（1分）
SHOW CREATE PROCEDURE p_compare\G
6、删除名称为p_compare的存储过程。（1分）
drop procedure p_compare;
（二）：
1、创建触发器ord_upd，实现当向lineitem表插入一行数据时，根据订单号对orders表的订单总价进行修改，订单总价加上该商品明细的金额。（2分）
1. delimiter $$ 2. create trigger ord_upd after insert on lineitem for each row 3. begin 4. update orders set totalprice=totalprice+new.quantity * new.unitprice where orderid=new.orderid; 5. end $$ 6. delimiter ; 7. select * from orders where orderid=20130411; 8. insert into lineitem values(20130411,'K9-DB-01',2,120); 9. select * from orders where orderid=20130411;
2、创建一个触发器，当修改product表中商品的市场价格（listprice）时，则触发器将修改lineitem表中对应商品的成交价格unitprice，令其成交价格与商品的市场价格一致。（2分）
1. delimiter $$ 2. create trigger item_upd after update on product for each row 3. begin 4. update lineitem set unitprice=new.listprice where productid=old.productid; 5. end $$ 6. delimiter ; 7. select * from lineitem where productid='K9-DL-01'; 8. update product set listprice=250 where productid='K9-DL-01'; 9. select * from lineitem where productid='K9-DL-01';
3、创建触发器usr_del，在account表中当删除客户信息时将orders表中与该客户有关的数据同时全部删除。（2分）
1. delimiter $$ 2. create trigger usr_del after delete on account for each row 3. begin 4. delete from orders where userid=old.userid; 5. end $$ 6. delimiter ; 7. select * from orders where userid='u0002'; 8. delete from account where userid='u0002'; 9. select * from orders where userid='u0002';
4、创建触发器ord_del，在orders表中当删除订单信息时将lineitem表中与该订单有关的数据同时全部删除。（2分）
1. delimiter $$ 2. create trigger ord_del after delete on orders for each row 3. begin 4. delete from lineitem where orderid=old.orderid; 5. end $$ 6. delimiter ; 7. select * from lineitem where orderid=20130413; 8. delete from orders where orderid=20130413; 9. select * from lineitem where orderid=20130413;
5、查看petstore数据库中的全部触发器。（1分）
show triggers\G
6、删除ord_del触发器。（1分）
drop trigger ord_del;
（三）：
1、首先创建一个和orders表结构完全相同的订单历史表oldorders，再创建一个和lineitem表结构完全相同的订单明细历史表oldlineitem，然后创建事件e_orders，从现在开始执行，每隔一年执行一次，实现将orders表中订单日期超过一年的订单记录插入到oldorders表中，将lineitem表中订单日期超过一年的订单记录插入到oldlineitem表中。（4分）
1. create table oldorders like orders; 2. create table oldlineitem like lineitem; 3. set @@global.event_scheduler=true; 4. delimiter $$ 5. create event if not exists e_orders on schedule every 1 year starts now() 6. do 7. begin 8. insert into oldorders 9. select * from orders where date_add(orders.orderdate,interval 1 year)<now(); 10. insert into oldlineitem 11. select lineitem.* from orders,lineitem 12. where orders.orderid=lineitem.orderid and date_add(orders.orderdate,interval 1 year)<now(); 13. end$$ 14. delimiter ;
2、创建事件dellorder，从现在开始执行，每隔一年执行一次，将订单时间超过1年的订单信息和订单明细信息删除。（3分）
1. delimiter $$ 2. create event dellorder on schedule every 1 year starts now() 3. do 4. begin 5. delete orders,lineitem 6. from orders , lineitem where orders.orderid=lineitem.orderid 7. and date_add(orders.orderdate,interval 1 year)<now(); 8. end$$ 9. delimiter ;
3、查看事件e_orders的创建信息（1分）
show create event e_orders\G
4、临时关闭e_orders事件；（1分）
alter event e_orders disable;
5、删除事件e_orders。（1分）
drop event e_orders;
