sql2 1510

FUNCTION tot sales

(company_id_in IN company.company_id%TYPE, status_in IN order .status_code%TYPE:=NUr,l.)

RETURN NUMBER IS

/♦Internal upper-cased version of status codę */ status_int order.status_codc%TYPE:=UPPER(status_in);

Declaration

/♦Paramclcrizcd cursor returns total discounicd sales. ♦/ CURSOR sales cur {status in IN status code%TYPE) 1S SELECT SUM (amount*discount)

•—

FROM item

WHERE EXISTS {SELECT 'X' FROM order WHFRE order.order_id=i tem.order_id AND company id=company id in AND status_codc LIKE status_in) ;

/♦Return valuc for function*/ return value NUMBER;

BEGIN

OPFN sales_cur {status_int);

FETCH sales_cur INTO return_value;

Execution

IF sales_cur%NOTFOUND

THEN

CLOSE salcs_cur;

RETURN NULL;

ELSE

CLOSE sales cur;

RETURN return_value;

END IF;

END tot_sales;