Database Reference
In-Depth Information
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
IF TODAY - RSTUDIO.DUE_DATE > 90 THEN
AT90DAYS := AT90DAYS + RSTUDIO.AMOUNT;
ELSIF TODAY - RSTUDIO.DUE_DATE > 60 THEN
AT60DAYS := AT60DAYS + RSTUDIO.AMOUNT;
ELSIF TODAY - RSTUDIO.DUE_DATE > 30 THEN
AT30DAYS := AT30DAYS + RSTUDIO.AMOUNT;
ELSE
NOTDUE := NOTDUE + RSTUDIO.AMOUNT;
END IF;
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
The CASE Statement
The CASE statement can be used in place of lengthy IF statements where a
condition is tested against a single Boolean expression. Internally, a CASE
statement is more efficient than an IF statement. There are two forms of the
CASE statement in PL/SQL. Both forms of the CASE statement will yield
a Boolean result (TRUE, FALSE, or NULL) for each option. The syntax of
both forms of the CASE statement is shown in Figure 24.15.
