在用户方案SCOTT中创建函数FUN_TOTALVALUE,它根据客户编号查询该客户的订单总金额
(1)定义函数
CREATE OR REPLACE FUNCTION FUN_TOTALVALUE
(cid ORDERS.CUSTID%TYPE)
RETURN NUMBER;
AS
TOTVALUE NUMBER;
BEGIN
SELECT SUM(PRICE * OD.QUANTITY) INTO TOTVALUE
FROM ORDERS O
JOIN ORDERDETAILS OD
ON O.ORDERID=OD.ORDERID
JOIN SUPPLY S
ON OD.SUPPID=S.SUPPID
AND OD.PRODID=S.PRODID
WHERE CUSTID=cid;
RETURN TOTVALUE;
END FUN_TOTALVALUE;
(2)调用函数
DECLARE
cid ORDERS.CUSTID%TYPE;
BEGIN
cid :='C070902003';
DBMS_OUTPUT.PUT_LINE(cid||':'||FUN_TOTALVALUE(cid));
END;