相关推荐recommended
数据库实验七 存储过程实验
作者:mmseoamin日期:2023-12-14

1.实验目的

掌握数据库存储过程的设计和使用方法。

2.实验内容和要求

存储过程的定义,存储过程运行,存储过程更名,存储过程删除。

3.实验步骤和实验结果

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

创建存储过程:

```sql
CREATE PROCEDURE proc1 () BEGIN
	UPDATE orders 
	SET totalprice =(
		SELECT
			SUM(
				extendedprice *(
					1+tax 
					)*(
					1-discount 
				)) 
		FROM
			lineitem 
		WHERE
			orders.orderkey = lineitem.orderkey 
		);
END;

调用存储过程:

CALL proc1;

数据库实验七 存储过程实验,在这里插入图片描述,第1张

查看:

实验前数据:

数据库实验七 存储过程实验,在这里插入图片描述,第2张

实验后数据:

数据库实验七 存储过程实验,在这里插入图片描述,第3张

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

创建存储过程:

CREATE PROCEDURE proc2 ( IN okey INT ) BEGIN
	UPDATE orders 
	SET totalprice =(
		SELECT
			SUM( extendedprice *( 1+tax )*( 1-discount ) ) 
		FROM
			lineitem 
		WHERE
			orders.orderkey = lineitem.orderkey 
		);
END;

调用存储过程:

CALL proc2(20);

数据库实验七 存储过程实验,在这里插入图片描述,第4张

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

创建存储过程:

CREATE PROCEDURE proc3 ( IN cname INT ) BEGIN
	DECLARE
		ckey INT;
	SELECT
		custkey INTO ckey 
	FROM
		customer 
	WHERE
		customer.NAME = cname;
	UPDATE orders 
	SET totalprice =(
		SELECT
			SUM(
				extendedprice *(
					1+tax 
					)*(
					1-discount 
				)) 
		FROM
			lineitem,
			orders 
		WHERE
			lineitem.orderkey = orders.orderkey 
			AND orders.custkey = ckey 
		);
END;

调用:

首先根据orderkey找到一个顾客名用做调用存储过程的参数值:

CALL proc3(‘孔珍’);

数据库实验七 存储过程实验,在这里插入图片描述,第5张

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

创建存储过程:

CREATE PROCEDURE proc4 (
	IN cname CHAR ( 25 ),
	OUT tprice DECIMAL ( 10, 2 )) BEGIN
	DECLARE
		ckey INT;
	SELECT
		custkey INTO ckey 
	FROM
		customer 
	WHERE
		NAME = cname;
	UPDATE orders 
	SET totalprice =(
		SELECT
			SUM(
				extendedprice *(
					1+tax 
					)*(
					1-discount 
				)) 
		FROM
			lineitem 
		WHERE
			lineitem.orderkey = orders.orderkey 
		);
	SELECT
		SUM( totalprice ) INTO tprice 
	FROM
		orders 
	WHERE
		orders.custkey = ckey 
	GROUP BY
	orderkey;
END;

调用:

数据库实验七 存储过程实验,在这里插入图片描述,第6张

查看:

数据库实验七 存储过程实验,在这里插入图片描述,第7张

(5)删除存储过程 proc4:

DROP PROCEDURE proc4;

数据库实验七 存储过程实验,在这里插入图片描述,第8张

查看:

数据库实验七 存储过程实验,在这里插入图片描述,第9张

(6)定义一个存储过程 proc5,更新某年订单(含税折扣价)的总价,执行这个存储过程。(要求使用游标完成):

创建存储过程:

CREATE PROCEDURE proc5 () BEGIN
	DECLARE
		cursor_price DECIMAL ( 10, 2 );#记录某一个totalprice
	DECLARE
		cursor_extendedprice DECIMAL ( 8, 2 );
	DECLARE
		cursor_discount DECIMAL ( 3, 2 );
	DECLARE
		cursor_tax DECIMAL ( 3, 2 );
	DECLARE
		cursor1 CURSOR FOR SELECT
		extendedprice 
	FROM
		lineitem,
		orders 
	WHERE
		orders.orderkey = lineitem.orderkey;
	DECLARE
		cursor2 CURSOR FOR SELECT
		discount 
	FROM
		lineitem,
		orders 
	WHERE
		orders.orderkey = lineitem.orderkey;
	DECLARE
		cursor3 CURSOR FOR SELECT
		tax 
	FROM
		lineitem,
		orders 
	WHERE
		orders.orderkey = lineitem.orderkey;
	OPEN cursor1;
	OPEN cursor2;
	OPEN cursor3;
	FETCH cursor1 INTO cursor_extendedprice;
	FETCH cursor2 INTO cursor_discount;
	FETCH cursor3 INTO cursor_tax;
	
	SET cursor_price = cursor_extendedprice *(
		1+cursor_tax 
		)*(
		1-cursor_discount 
	);
	CLOSE cursor1;
	CLOSE cursor2;
	CLOSE cursor3;
	SELECT
		SUM( totalprice ) 
	FROM
		orders 
	WHERE
	YEAR ( orderdate )= 2022;
END;

查看:

数据库实验七 存储过程实验,在这里插入图片描述,第10张

(7)定义一个存储过程 proc6,能够根据某年(年份作为输入参数,整数)各个客户的下订单购买情况,把该年客户订单总金额超过某个阈值(该阈值为输入参数,为整数)的客户表(customer)中的备注(comment)字段更新为重要客户(“SVIP”)(要求使用游标完成):

创建存储过程:

CREATE PROCEDURE proc6 ( IN YEAR INT, IN threshold INT ) BEGIN
	DECLARE
		price DECIMAL ( 10, 2 );
	DECLARE
		cursor_comment VARCHAR ( 100 );
	DECLARE
		cursor1 CURSOR FOR SELECT COMMENT 
	FROM
		customer 
	WHERE
		custkey IN ( SELECT custkey FROM orders );
	DECLARE
		cursor2 CURSOR FOR SELECT
		totalprice 
	FROM
		orders 
	WHERE
		YEAR ( orderdate )= YEAR 
		AND custkey IN ( SELECT custkey FROM customer );
	OPEN cursor1;
	OPEN cursor2;
	FETCH cursor1 INTO cursor_comment;
	FETCH cursor2 INTO price;
	IF
		price >= threshold THEN
			
			SET cursor_comment = 'SVIP';
		
	END IF;
	CLOSE cursor1;
END;

查看:

数据库实验七 存储过程实验,在这里插入图片描述,第11张