Database Reference
In-Depth Information
countries other than Canada, England, or the USA, and we have no null-
valued countries as well. See the result in Figure 24.17.
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;
CANADA INTEGER DEFAULT 0;
USA INTEGER DEFAULT 0;
ENGLAND INTEGER DEFAULT 0;
OTHER INTEGER DEFAULT 0;
BEGIN
/* Loop through all the rows, adding amounts to the
4 variables. */
FOR RSTUDIO IN CSTUDIO LOOP
CASE RSTUDIO.COUNTRY
WHEN 'CAN' THEN CANADA:=CANADA+RSTUDIO.AMOUNT;
WHEN 'England' THEN ENGLAND:=ENGLAND+RSTUDIO.AMOUNT;
WHEN 'USA' THEN USA:=USA+RSTUDIO.AMOUNT;
ELSE OTHER:=OTHER+RSTUDIO.AMOUNT;
END CASE;
END LOOP;
/* Print results now */
DBMS_OUTPUT.PUT_LINE(
'Canada: '||TO_CHAR(CANADA,'$999,999.99'));
DBMS_OUTPUT.PUT_LINE(
'USA: '||TO_CHAR(USA,'$999,999.99'));
DBMS_OUTPUT.PUT_LINE(
'England: '||TO_CHAR(ENGLAND,'$999,999.99'));
DBMS_OUTPUT.PUT_LINE(
'Other: '||TO_CHAR(OTHER,'$999,999.99'));
END;
/
