In this part we will learn how to use functions: 1.Create a functions that has t
ID: 3860692 • Letter: I
Question
In this part we will learn how to use functions:
1.Create a functions that has the Customer_id as input and returns a number that represents the total cost of orders for this customer. Notice that you will need to multiply the Order_Qty from table order_details and the Unit_Price from table items to get the total cost of each order.
2. Now use the function: Write a select statement that selects all the customers and the total cost of the orders they have ever made.
3.
In this part we create a small procedure:
a.Write a procedure that has as input the customer_id. Then the procedure should delete the corresponding customer from table customers.
B.Notice that customer_id is a foreign key on table orders. Therefore the customer cannot be deleted if he has placed orders. To delete a record from customers you should first delete the corresponding orders and to do that you need to first delete the corresponding order_details.
C.Now execute the procedure to delete customer with id 1. If you have solved part 01 a record will be inserted into Customer_Archives.
D.ROLLBACK; and all these changes will be undone
On Oracle selDeveloper
Explanation / Answer
CREATE OR REPLACE Function Total_Cost
( Customer_id_in IN varchar2 )
RETURN number
IS
Order_Qty_in number;
Unit_Price_in number;
total_cost_out number;
cursor c1 is
SELECT Order_Qty
FROM order_details
WHERE Customer_id = Customer_id_in;
cursor c2 is
SELECT Unit_Price
FROM items
WHERE Customer_id = Customer_id_in;
BEGIN
open c1;
fetch c1 into Order_Qty_in;
open c2;
fetch c2 into Unit_Price_in;
if c1%found and c2%found then
total_cost_out := Order_Qty_in*Unit_Price_in;
end if;
close c1;
close c2;
RETURN total_cost_out;
END;
Select Customer_id, Total_Cost(Customer_id) AS Total_Cost FROM Customer;
CREATE OR REPLACE Procedure DeleteCustomer
( Customer_id_in IN varchar2 )
BEGIN
DELETE FROM order_details where Customer_id = Customer_id_in;
DELETE FROM Customer where Customer_id = Customer_id_in;
commit;
END;
Have provided the queries to best understaanding of table structure.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.