Of Interest
Be responsible for your future
Enter the USA legally!
Visa, Green Card, Citizenship, Passport
Consultation Services
|
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;
|
|
|
Get the latest Rocket99 news and tech tips via
|
