数据库实验七:存储过程实验

数据库实验七:存储过程实验

233333,实验八的时候总感觉好像实验七计算的公式不太对,懒得改了就这样吧2333

在这里插入图片描述

定义一个存储过程proc2,更新给定订单的(含税折扣价)的总价,执行这个存储过程。

含税折扣总价=extendedprice*(税率+1)*折扣

SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.orderkey=18 && orders.orderkey=lineitem.orderkey;
DELIMITER $$
CREATE PROCEDURE proc2()
BEGIN
 DECLARE sum_price numeric(10,2);
 UPDATE lineitem SET extendedprice = extendedprice * discount  * (1+tax) WHERE orderkey = 18;
 SELECT SUM(lineitem.extendedprice) INTO sum_price FROM lineitem WHERE orderkey = 18 GROUP BY orderkey;
 UPDATE orders SET totalprice = sum_price WHERE orderkey = 18;
END;
 $$
DELIMITER ;
CALL proc2();
SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.orderkey=18 && orders.orderkey=lineitem.orderkey;

在这里插入图片描述

定义一个存储过程proc3,更新某个顾客的所有订单的(含税折扣价)总价,执行这个存储过程。

SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.custkey=320728 && orders.orderkey=lineitem.orderkey;
DELIMITER $$
CREATE PROCEDURE proc3()
BEGIN
 DECLARE sum_price numeric(10,2);
 UPDATE lineitem SET extendedprice = extendedprice * discount  * (1+tax) WHERE orderkey IN (SELECT orderkey FROM orders WHERE custkey = 320728);
 SELECT SUM(lineitem.extendedprice) INTO sum_price FROM lineitem,orders WHERE orders.custkey = 320728 && lineitem.orderkey=orders.orderkey GROUP BY orders.custkey;
 UPDATE orders SET totalprice = sum_price WHERE custkey = 320728;
END;
 $$
DELIMITER ;
CALL proc3();
SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.custkey=320728 && orders.orderkey=lineitem.orderkey;

在这里插入图片描述

定义一个存储过程proc4,更新某个顾客的所有订单的(含税折扣价)总价并输出该总价,执行这个存储过程。

SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.custkey=518445 && orders.orderkey=lineitem.orderkey;
DELIMITER $$
 CREATE PROCEDURE proc4()
 BEGIN
 DECLARE sum_price numeric(10,2);
 UPDATE lineitem SET extendedprice = extendedprice * discount  * (1+tax) WHERE orderkey IN (SELECT orderkey FROM orders WHERE custkey = 518445);
 SELECT SUM(lineitem.extendedprice) INTO sum_price FROM lineitem,orders WHERE orders.custkey = 518445 && lineitem.orderkey=orders.orderkey GROUP BY orders.custkey;
 UPDATE orders SET totalprice = sum_price WHERE custkey = 518445;
 SELECT sum_price;
END;
 $$
DELIMITER ;
CALL proc4();
SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.custkey=518445 && orders.orderkey=lineitem.orderkey;

在这里插入图片描述

删除存储过程proc4。

DROP PROCEDURE proc4;

定义一个存储过程proc1,更新所有订单(含税折扣价)的总价,执行这个存储过程。

先使用游标逐行更新lineitem表所有extendedprice,再使用更新后的extendedprice更新totalprice
lineitem更新前

在这里插入图片描述

orders更新前

在这里插入图片描述

DELIMITER $$
CREATE PROCEDURE proc1()
BEGIN
 DECLARE totalprice_y numeric(10,2);
 DECLARE totalprice_x numeric(10,2);	
 DECLARE count INT; #统计一共多少行
 DECLARE orderkey_y INT;/*orderkey 和 linenumber联合做主键,这里取这俩*/
 DECLARE linenumber_y INT;
 DECLARE extendedprice_y DECIMAL(15,2);
 DECLARE discount_y DECIMAL(3,2);
 DECLARE tax_y DECIMAL(3,2);
 DECLARE count_y INT DEfAULT 0;
 DECLARE done2 INT DEFAULT 0;
 DECLARE orderkey_u INT;
 DECLARE count2 INT;
 DECLARE count3 INT;
 
 DECLARE done BOOLEAN DEFAULT FALSE; 
 
 DECLARE orderkey_x CURSOR FOR SELECT
	orderkey,linenumber,extendedprice,discount,tax FROM lineitem;
 DECLARE orderkey_z CURSOR FOR SELECT
  orderkey,totalprice FROM orders;
	
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;#找不到的时候done置TRUE 
 
 SELECT COUNT(DISTINCT orderkey) INTO count FROM lineitem; 
 SELECT COUNT(DISTINCT orderkey) INTO count2 FROM orders;
 SET count3 = count2;
 
#更新lineitem的所有extendedprice
 OPEN orderkey_x;
 FETCH orderkey_x INTO orderkey_y,linenumber_y,extendedprice_y,discount_y,tax_y;
REPEAT

 SET extendedprice_y = extendedprice_y * discount_y * (1.00 + tax_y);
 UPDATE lineitem SET extendedprice = extendedprice_y  WHERE orderkey = orderkey_y AND linenumber = linenumber_y;
 SELECT orderkey_y,linenumber_y,extendedprice_y,discount_y,tax_y,count_y;
 SET count_y = count_y + 1;
 FETCH orderkey_x INTO orderkey_y,linenumber_y,extendedprice_y,discount_y,tax_y;

UNTIL done END REPEAT;
CLOSE orderkey_x;





#更新orders的所有totalprice
 OPEN orderkey_z;
 FETCH orderkey_z INTO orderkey_y,totalprice_y;

WHILE count2 > 0 DO

#orderkey_y取自orders,如果在lineitem中也存在,将orderkey_u=orderkey_y;
SELECT orderkey INTO orderkey_u FROM lineitem
 WHERE orderkey = orderkey_y GROUP BY orderkey;
#如果orderkey_u = orderkey_y; 计算该orderkey下的订单总和,否则done2置1
IF orderkey_u = orderkey_y THEN 
SELECT SUM(extendedprice) INTO totalprice_x FROM lineitem
 WHERE orderkey = orderkey_u GROUP BY orderkey ;
SET done2 = 0;
ELSE
SET done2 = 1;
END IF;

IF done2 = 0 THEN
UPDATE orders SET totalprice = totalprice_x WHERE orderkey = orderkey_y;
SELECT orderkey_y,totalprice_y as old_price, totalprice_x as new_price;
ELSE
SELECT orderkey_y,"该orderkey不在lineitem中";
END IF;

SET done2 = 0;
FETCH orderkey_z INTO orderkey_y,totalprice_y;
SET count2 = count2 - 1;
END WHILE;

CLOSE orderkey_z;

select count_y as orderkey_linenumber_count_in_lineitem , count as distinct_orderkey_count_in_lineitem,count3 as orderkey_count_in_orders;
END
 $$
DELIMITER ;
CALL proc1();
更新exetendedprice的循环一的执行

在这里插入图片描述

使用更新后的extendedprice更新totalprice的循环二的执行

在这里插入图片描述

在这里插入图片描述

更新后的lineitem和orders

在这里插入图片描述

在这里插入图片描述

主要问题所在:DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 这个问题很大,如果游标只是遍历一个表,而且操作也只在这个表,那么用这个语句标志循环结束,可能一般情况都没有问题,比如循环一就可以实现,但是的但是:在循环二中要更新orders中的totalprice,游标设置在orders表上,但是有些orderkey并不存在于lineitem表的orderkey中,那么如果依然用这个done做结束标志的话:在循环里orderkey一旦出现在lineitem中select找不到的情况,done直接置TRUE,然后执行完后面语句到until那里一判断就跳出循环了,而实际上我们想的是在lineitem里面的没有orders中的某个orderkey的时候,直接去获取orders中的下一个orderkey看看在lineitem中有没有,如果有那么就使用lineitem的内容计算所有这种orderkey行的extendprice的总和,把这个值赋值给orders的totalprice就行,所以不能再使用这种循环结束方式,然后我计算了一下orders中的行数,用这个行数循环orders就行了
  • 5
    点赞
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
一、实验目的 1.掌握SQL Server 2005的安装。 2.掌握SQL Server Management Studio的启动和使用。 3.掌握SQL Server 2005服务器的配置和注册。 4.掌握SQL Server 2005查询的基本使用。 5.掌握应用SQL Server Management Studio创建数据库的方法。 6.掌握应用SQL Server Management Studio修改和查看数据库的方法。 7.掌握应用SQL Server Management Studio删除数据库的方法。 8.掌握应用Transact-SQL语句创建数据库的方法。 9.掌握应用Transact-SQL修改和查看数据库的方法。 10.掌握通过Transact-SQL删除数据库的方法。 11.掌握SQL Server 2005数据库和操作系统物理文件的关系。 12.掌握数据库的分离和附加方法。 二、实验内容 1.完成SQL Server 2005开发版的安装。 提示:若计算机系统中已经安装有SQL Server 2005系统,则在安装时需要选择安装命名实例。安装过程中身份验证模式选择“混合模式”并设置sa账户的密码。 2.利用SQL Server配置管理器启动、停止SQL Server服务(包括默认实例和命名实例),配置SQL Server服务为自动启动。 3.利用SQL Server配置管理器配置进行SQL Server 2005网络配置,启用默认实例和命名实例的TCP/IP协议。 4.利用SQL Server外围配置器配置数据库引擎的服务及远程连接,设置为“本地连接和远程连接”,选择“同时使用TCP/IP和named pipes”。 5.利用SQL Server Management Studio注册安装的命名实例。 6.利用SQL Server Management Studio注册远程服务器。 提示:注册远程服务器时需要使用混合验证模式,利用sa账户和密码登录远程服务器。 7.启动SQL Server Management Studio,连接到服务器。新建一个查询,在其中输入如下代码: DECLARE @position int, @string char(5) SET @position = 1 SET @string = 'China' WHILE @position <= DATALENGTH(@string) BEGIN SELECT SUBSTRING(@string, @position, 1) 字符, ASCII(SUBSTRING(@string, @position, 1)) ASCII码 SET @position = @position + 1 END

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值