实验7 数据库编程

一、实验项目：

数据库编程。

二、实验目的

1、会使用SQL语句创建、修改、删除、调用存储过程和存储函数。

2、会使用SQL语句创建、删除、使用触发器和事件。

三、实验内容

（一）：2学时

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

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

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

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

4、创建一个存储函数F_bj，比较某商品的市场价和成本价，如果相同则返回YES，若不是则返回NO。调用该存储函数分别比较“金龙鱼一级大豆油5L”、“东北珍珠香米10斤”和“五得利五星小麦粉10斤”的价格，输出结果。

（二）：2学时

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

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

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

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

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

5、创建事件dellorder，从现在开始执行，每隔一个月执行一次，将订单时间超过1个月的订单信息和订单明细信息删除。

四、实验参考代码及结果截图

（一）：

1、delimiter $$

create procedure p_compare(in a int,in b int,out result tinyint)

begin

declare t1 decimal(10,2);

declare t2 decimal(10,2);

select totalprice into t1 from orders where orderid=a;

select totalprice into t2 from orders where orderid=b;

if t1>t2 then set result=0;

else set result=1;

end if;

end $$

delimiter ;

call p_compare(20230411,20230414,@result);

select @result;

2、delimiter $$

create procedure proc_cursor()

begin

declare  v_pno char(10) default  ' ';

declare  v_pname varchar(30)  default ' ';

declare  pro_cursor cursor  for  select productid, name from product;

declare continue  handler for not found  set @dovar=1;#定义处理程序

set @dovar =0;

open pro_cursor;

fetch_Loop:LOOP

fetch pro_cursor into v_pno,v_pname;

if @dovar=1 then leave fetch_Loop;

else select v_pno,v_pname;

end IF;

end LOOP fetch_Loop;

close pro_cursor;

end $$

delimiter ;

call proc_cursor();

3、delimiter $$

create function f_totalprice(u_id char(6),o_year char(4)) returns decimal(10,2)

begin

declare s_price decimal(10,2);

select sum(totalprice) into s_price from orders 

where userid=u_id and year(orderdate)=o_year;

return s_price;

end $$

delimiter ;

select  f_totalprice('u0003','2023');

4、delimiter $$

create function bj(nm varchar(30)) returns char(8)

begin

declare num1 decimal(10,2);

declare num2 decimal(10,2);

  declare result char(8);

select listprice into num1 from product where name=nm;

select unitcost into num2 from product where name=nm;

if num1!=num2 then set result='NO';

else set result='YES';

end if;

return result;

end $$

delimiter ;

select bj('金龙鱼一级大豆油5L');

select bj('东北珍珠香米10斤');

select bj('五得利五星小麦粉10斤');

（二）：

1、delimiter $$

create trigger ord_upd after insert on lineitem for each row

begin

update orders set totalprice=totalprice+new.quantity * new.unitprice where orderid=new.orderid;

end $$

delimiter ;

select * from orders where orderid=20230411;

使用触发器：insert into lineitem values(20230411,'0010003',30,20);

select * from orders where orderid=20230411;

2、delimiter $$

create trigger item_upd after update on product for each row

begin

update lineitem set unitprice=new.listprice where productid=old.productid;

end $$

delimiter ;

select * from lineitem where productid='0010001';

使用触发器：update product set listprice=52 where productid='0010001';

select * from lineitem where productid='0010001';

3、delimiter $$

create trigger ord_del after delete on orders for each row

begin

delete from lineitem where orderid=old.orderid;

end $$

delimiter ;

select * from lineitem where orderid=20230413;

使用触发器：delete from orders where orderid=20230413;

select * from lineitem where orderid=20230413;

4、create table oldorders like orders;

create table oldlineitem like lineitem;

set @@global.event_scheduler=true;

delimiter $$

create event if not exists e_orders on schedule every 1 month starts now()

do

begin

insert into oldorders

select * from orders where date_add(orders.orderdate,interval 1 month)<now();

insert into oldlineitem

select lineitem.* from orders,lineitem

where orders.orderid=lineitem.orderid and date_add(orders.orderdate,interval 1 month)<now();

end$$

delimiter ;

事件执行前：select * from oldorders;select * from oldlineitem;

事件执行后：select * from oldorders;select * from oldlineitem;

5、drop trigger ord_del;

delimiter $$

create event dellorder on schedule  every 1 month  starts  now()

do

begin

    delete orders,lineitem

    from  orders , lineitem where orders.orderid=lineitem.orderid

    and  date_add(orders.orderdate,interval 1 month)<now();

end$$

delimiter ;

事件执行前：select * from orders;select * from lineitem;

事件执行后：select * from orders;select * from lineitem;