I have created a function that has the Customer_id as input and returns a number that represents the total cost of orders for this customer. I have to multiply the Order_Qty from table order_details and the Unit_Price from table items to get the total cost of each order.
This is my code:
CREATE OR REPLACE FUNCTION FIND_TOTAL
(CUSTOMER_ID_IN IN VARCHAR2)
RETURN NUMBER IS
ORDER_QTY_IN NUMBER;
UNIT_PRICE_IN NUMBER;
TOTAL_COST_OUT NUMBER;
CURSOR C1 IS
SELECT od.ORDER_QTY
FROM ORDER_DETAILS od JOIN ORDERS o
ON od.ORDER_ID = o.ORDER_ID
WHERE CUSTOMER_ID = CUSTOMER_ID_IN;
CURSOR C2 IS
SELECT i.UNIT_PRICE
FROM ITEMS i JOIN ORDER_DETAILS od
ON i.ITEM_ID = od.ITEM_ID
JOIN ORDERS o
ON o.ORDER_ID = od.ORDER_ID
WHERE CUSTOMER_ID = CUSTOMER_ID_IN;
BEGIN
OPEN C1;
FETCH C1 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;
/
Now I have to use the function to write a select statement that selects all the customers and the total cost of the orders they have ever made.
SELECT CUSTOMER_ID, FIND_TOTAL(CUSTOMER_ID) AS TOTAL_COST
FROM CUSTOMERS;
But when I do this I get an error:
ORA-01001: invalid cursor
ORA-06512: at "TUG81959.FIND_TOTAL", line 22
01001. 00000 - "invalid cursor"
*Cause:
*Action:
Here's a list of my tables in case it is helpful:
