After Work
The best club in Chicago
for Chicago Singles is
Highlife Adventures!
Kyoto Sushi
in Chicago, on Lincoln Ave.
Moody's Pub
in Chicago, has great burgers
Skylark
in Chicago, on Halsted .. excellent beer selection!
|
Oracle
»
PL-SQL
»
Coding
»
Cursors
The first example depicts dbase-style row processing ; the second a more
traditional "fetch" approach.
PROCEDURE PROC_SCAN_INVOICES (EXPIRE_DT IN DATE)
IS
CURSOR INVOICE_CUR IS
SELECT INV_ID, INV_DT FROM INVOICE ;
TYPE INVOICE_REC_TYPE IS RECORD
(INV_ID INVOICE.INV_ID%TYPE,
INV_DT INVOICE.INV_DT%TYPE ) ;
INVOICE_REC INVOICE_REC_TYPE ;
BEGIN
FOR INVOICE_REC1 IN INVOICE_CUR
LOOP
IF INVOICE_REC.INV_DT < EXPIRE_DT THEN
DELETE FROM INVOICE
WHERE INV_ID = INV_REC.INV_ID ;
DBMS_OUTPUT.PUT_LINE('INVOICE DELETETED:');
DBMS_OUTPUT.PUT_LINE(INV_REC.INV_ID);
END
END LOOP;
END;
/* ======================================= */
CREATE OR REPLACE PROCEDURE PROC_DOCEXPIRE_RPT
( RPT_BODY OUT LONG RAW )
IS
RPT_LINE VARCHAR2(1900);
RPT_PART VARCHAR2(1900);
RPT_LEAD VARCHAR2(200);
GLIB_ID1 NUMBER ;
GLIB_ID2 VARCHAR(12);
ORIG_LOC_CD1 VARCHAR2(12);
AUTHOR_ID1 VARCHAR2(30);
CONTRIBUTORS1 VARCHAR2(80);
TOPIC1 VARCHAR2(80);
NBR_ACCESS1 NUMBER ;
NBR_ACCESS2 VARCHAR2(12);
TOT_EXPIRED1 NUMBER ;
TOT_EXPIRED2 VARCHAR2(12);
COUNT1 NUMBER ;
RPT_BODY_PART LONG ;
CURSOR CUR1 IS
SELECT GLIB_ID, ORIG_LOC_CD, AUTHOR_ID, CONTRIBUTORS, TOPIC, NBR_ACCESS
FROM GEN_DOC
WHERE EXPIRE_DT < (SYSDATE + 30)
ORDER BY ORIG_LOC_CD, GLIB_ID ;
BEGIN
SELECT COUNT(*)
INTO TOT_EXPIRED1
FROM GEN_DOC
WHERE STAT_CD='90';
TOT_EXPIRED2 := TO_CHAR(TOT_EXPIRED1);
RPT_LEAD := '<H5>TOTAL EXPIRED DOCUMENT COUNT TO DATE: ... ' ||
TOT_EXPIRED2 || '</H5><HR>' ;
RPT_LINE := '<HTML><BODY BGCOLOR=#FFFFFF>' ||
'<H6>ABC Corporation</H6>' ||
'<H2>Gen Doc System - Documents Expiring Within 30 Days</H2><HR>' ||
RPT_LEAD ;
COUNT1 := 0;
OPEN CUR1;
RPT_LINE := RPT_LINE || '<TABLE>' ||
'<TD><U>No. Accesses</U></TD>' ||
'<TD><U>Document #</U></TD>' ||
'<TD><U>Topic</U></TD>' ||
'<TD><U>Author</U></TD>' ;
RPT_BODY := UTL_RAW.CAST_TO_RAW(RPT_LINE);
RPT_LINE := '';
LOOP
COUNT1 := COUNT1 + 1;
EXIT WHEN (COUNT1 > 500);
EXIT WHEN (UTL_RAW.LENGTH(RPT_BODY) > 32000);
FETCH CUR1 INTO
GLIB_ID1, ORIG_LOC_CD1, AUTHOR_ID1, CONTRIBUTORS1, TOPIC1, NBR_ACCESS1 ;
EXIT WHEN CUR1%NOTFOUND ;
RPT_PART := '<TR><TD>';
NBR_ACCESS2 := TO_CHAR(NBR_ACCESS1);
RPT_PART := CONCAT(RPT_PART,NBR_ACCESS2);
RPT_PART := CONCAT(RPT_PART,'</TD><TD>');
GLIB_ID2 := TO_CHAR(GLIB_ID1);
RPT_PART := RPT_PART || ORIG_LOC_CD1 || '-' || GLIB_ID2 ||
'</TD><TD>' || TOPIC1 || '</TD><TD>' ||
AUTHOR_ID1 || '</TD><TR>' ;
RPT_LINE := CONCAT(RPT_LINE, RPT_PART);
RPT_BODY_PART := UTL_RAW.CAST_TO_RAW(RPT_LINE);
RPT_BODY := UTL_RAW.CONCAT(RPT_BODY,RPT_BODY_PART);
-- RPT_BODY := RPT_BODY || RPT_LINE;
RPT_LINE := '';
END LOOP;
CLOSE CUR1 ;
RPT_LINE := '</TABLE></BODY></HTML>';
RPT_BODY_PART := UTL_RAW.CAST_TO_RAW(RPT_LINE);
RPT_BODY := UTL_RAW.CONCAT(RPT_BODY, RPT_BODY_PART);
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('ERROR: PROC_DOCSTAT_RPT');
GLIB_ID1 := UTL_RAW.LENGTH(RPT_BODY);
DBMS_OUTPUT.PUT_LINE(GLIB_ID1);
END;
END;
|
|
|
|
