Databases Reference
In-Depth Information
2.
Retrieve the records in the
SALES
table and loop through them to count the number
of sales that took place before June 28, 1998 with a quantity greater than 1:
SET TIMING ON
DECLARE
TAB_QTY DBMS_SQL.NUMBER_TABLE;
TAB_TIME DBMS_SQL.DATE_TABLE;
CNT NUMBER := 0;
BEGIN
SELECT AMOUNT_SOLD, TIME_ID
BULK COLLECT INTO TAB_QTY, TAB_TIME FROM SALES;
FOR J IN TAB_QTY.FIRST..TAB_QTY.LAST LOOP
IF TAB_QTY(J) > 1 AND TAB_TIME(J) < '27-JUN-98' THEN
CNT := CNT + 1;
END IF;
END LOOP;
END;
/
SET TIMING OFF
3.
In the previous script, we change the order in which the two conditions are
expressed in the
IF
statement:
SET TIMING ON
DECLARE
TAB_QTY DBMS_SQL.NUMBER_TABLE;
TAB_TIME DBMS_SQL.DATE_TABLE;
CNT NUMBER := 0;
BEGIN
SELECT AMOUNT_SOLD, TIME_ID
BULK COLLECT INTO TAB_QTY, TAB_TIME FROM SALES;
FOR J IN TAB_QTY.FIRST..TAB_QTY.LAST LOOP
IF TAB_TIME(J) < '27-JUN-98' AND TAB_QTY(J) > 1 THEN
CNT := CNT + 1;
END IF;
END LOOP;
END;
/
SET TIMING OFF
