Databases Reference
In-Depth Information
SET PAGESIZE 80
CREATE OR REPLACE PROCEDURE SH.SALES_BY_PRODUCT
(P OUT SYS_REFCURSOR) IS
BEGIN
OPEN P FOR
SELECT PROD_ID, SUM(AMOUNT_SOLD) AS AMOUNT FROM SH.SALES
GROUP BY PROD_ID;
END;
/
ALTER PROCEDURE SH.SALES_BY_PRODUCT COMPILE;
/
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET TIMING ON
SELECT PROD_ID, SUM(AMOUNT_SOLD) FROM SH.SALES GROUP BY PROD_ID;
SET TIMING OFF
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET TIMING ON
VAR TEST REFCURSOR
EXEC SH.SALES_BY_PRODUCT(:TEST);
SET TIMING OFF
SET FEEDBACK ON
SET ECHO ON
2.
Launch SQL*Plus and connect as user SYS:
sqlplus /@TESTDB AS SYSDBA
3.
Launch the previous script (we assume the script is in the same directory from
which SQL*Plus has been executed):
@StoredProcedure
The output will be something similar to the following:
...
126 370204.56
127 1033311.97
16 2082330.3
122 84498.67
139 244595.65
Elapsed: 00:00:01.26
Elapsed: 00:00:00.45
