Database Reference
In-Depth Information
SET SERVEROUTPUT ON
DECLARE
TODAY DATE DEFAULT '01-JUL-2001';
CURSOR CSTUDIO IS
SELECT A.COUNTRY,A.NAME,S.DUE_DATE
,NVL(S.AMOUNT_CHARGED - NVL(S.AMOUNT_PAID,0),0)
AS AMOUNT
FROM ARTIST A JOIN STUDIOTIME S USING (ARTIST_ID)
ORDER BY 1,2,3;
AT90DAYS INTEGER DEFAULT 0;
AT60DAYS INTEGER DEFAULT 0;
AT30DAYS INTEGER DEFAULT 0;
NOTDUE INTEGER DEFAULT 0;
BEGIN
/* Loop through all the rows, adding amounts to the
4 variables. */
FOR RSTUDIO IN CSTUDIO LOOP
CASE
WHEN TODAY - RSTUDIO.DUE_DATE > 90 THEN
AT90DAYS := AT90DAYS + RSTUDIO.AMOUNT;
WHEN TODAY - RSTUDIO.DUE_DATE > 60 THEN
AT60DAYS := AT60DAYS + RSTUDIO.AMOUNT;
WHEN TODAY - RSTUDIO.DUE_DATE > 30 THEN
AT30DAYS := AT30DAYS + RSTUDIO.AMOUNT;
ELSE
NOTDUE := NOTDUE + RSTUDIO.AMOUNT;
END CASE;
END LOOP;
/* Print results now */
DBMS_OUTPUT.PUT_LINE('90 + days: '||TO_CHAR(AT90DAYS,'$999,999.99'));
DBMS_OUTPUT.PUT_LINE('60 - 89 days: '||TO_CHAR(AT60DAYS,'$999,999.99'));
DBMS_OUTPUT.PUT_LINE('30 - 59 days: '||TO_CHAR(AT30DAYS,'$999,999.99'));
DBMS_OUTPUT.PUT_LINE('< 30 days: '||TO_CHAR(NOTDUE,'$999,999.99'));
END;
/
24.7.1.2.2
CASE Statement Selector and Expression
The next example shows the selector version of the CASE statement.
Change the CASE statement code of the previous example. In this example,
we have divided the output based on the COUNTRY column. Addition-
ally, the ELSE clause is not used because we know we do not have any
