Topics: (1) to define a stored procedure proc1, update all orders (tax included) at the total price, execute the stored procedure,
My code:
CREATE PROCEDURE proc1
As the declare @ order_key int, @ sum_price int
The SET @ sum_price=0
Declare find_orderky CURSOR FOR the SELECT orderkey FROM the orders
The OPEN find_orderey
The fetch NEXT from find_orderey INTO @ order_key
WHILE @ @ FETCH_STATUS=0
The BEGIN
The SET @ sum_Price=0
SELECT @ sum_Price=sum (lineitem extendedprice * (1 - lineitem. Discount) * (1 - lineitem. Tax))
The FROM lineitem
WHERE orderkey=@ order_key
UPDATE the orders
The SET totalprice=@ sum_price
WHERE orderkey=@ order_key
The fetch NEXT from find_orderey INTO @ order_key
END
The CLOSE find_orderkey
DEALLOCATE find_orderkey
Then I pop up after execution:
CodePudding user response:
Then the table is as follows: