which capture, among others, the items that are ordered by customers. Explain what the following code is and explain how it works by writing comments against each line of the code:

Question

Assume the following tables:
Order(orderNo, statusCode, customerNo,…)
Item(itemNo, orderNo, price, amount,…)

Answer 1

FUNCTION total_sales (customerNo_in IN orders.customerNo%TYPE,
statusCode_in IN order.statusCode%TYPE:=NULL)
RETURN NUMBER
IS
statusCode_int order.statusCode%TYPE:= UPPER(statusCode_in);
CURSOR salesCur (statusCode_in IN statusCode%TYPE) IS
SELECT SUM (price*amount)
FROM item
WHERE EXISTS (select ‘x’ FROM Order
WHERE order.orderNo = item.orderNo AND customerNo = customerNo_in
AND statusCode = statusCode_in);

returnValue NUMBER;
BEGIN
OPEN salesCur (statusCode_int);
FETCH salesCur INTO returnValue;
IF salesCur%notfound
THEN
CLOSE salesCur;
RETURN NULL;
ELSE
CLOSE salesCur;
RETURN returnValue;
END IF;
END total_sales;

The student should be able to explain the main points of the function: the structure, the meaning and type of the parameters, the cursor, variables, the embedded SQL statement and the main executable body.