1. The experiment purpose

Master the design and use of database .

2. The contents and requirements of the experiment

Definition of stored procedure , Stored procedure running , The stored procedure is renamed , Stored procedure deletion .

   3. The experimental steps

（1） Define a stored procedure proc1, Update all orders ( Discount price including tax ) Total price of , Execute the stored procedure .

Source code ：

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


);
END;
CALL proc1 ()

（2） Define a stored procedure  proc2, Update the for a given order ( Discount price including tax ) Total price of , Execute the stored procedure .

Source code ：

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

END;

CALL proc2 (11)

（3） Define a stored procedure  proc3, Update all orders for a customer （ Discount price including tax ） The total price , Execute the stored procedure .

Source code ：

CREATE PROCEDURE proc3 (IN cname CHAR(25))
BEGIN

DECLARE L_custkey INT;

SELECT
	custkey INTO L_custkey
FROM
	customer
WHERE
	NAME = cname;

UPDATE orders
SET totalprice = (
	SELECT
		SUM(
			extendedprice * (1 - discount) * (1 + tax)
		)
	FROM
		lineitem
	WHERE
		orders.orderkey = lineitem.orderkey
	AND orders.custkey = L_custkey


);


END;

CALL proc3 (' Kong Zhen ')

（4） Define a stored procedure  proc4, Update all orders for a customer （ Discount price including tax ） Total price and output the total price , Execute the stored procedure .

Source code ：

CREATE PROCEDURE proc4 (
	IN cname CHAR (25),
	OUT price_total NUMERIC (10, 2)
)
BEGIN

DECLARE L_custkey INT;

SELECT
	custkey INTO L_custkey
FROM
	customer
WHERE
	NAME = cname;

UPDATE orders
SET totalprice = (
	SELECT
		SUM(
			extendedprice * (1 - discount) * (1 + tax)
		)
	FROM
		lineitem
	WHERE
		orders.orderkey = lineitem.orderkey
	AND orders.custkey = L_custkey
);

SELECT
		SUM(totalprice) INTO price_total
	FROM
		orders
	WHERE
		custkey = L_custkey
GROUP BY custkey;


END;

CALL proc4 (' Kong Zhen ' ,@p_total);

SELECT
	@p_total;

Results screenshots ：

（5） Delete stored procedure  proc4.

Source code ：

DROP PROCEDURE proc4;