Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





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;











Oracle : Related Topics

Oracle : PL-SQL : Creating a stored procedure
Oracle : PL-SQL : Displaying output
Oracle : PL-SQL : Output variables
Oracle : PL-SQL : Arrays and structures
Oracle : PL-SQL : Conditionals
Oracle : PL-SQL : Looping in PL-SQL
Oracle : PL-SQL : Packages
Oracle : PL-SQL : Exception Handling
Oracle : PL-SQL : Sleep and Wait
Oracle : PL-SQL : Date Manipulation
Oracle : PL-SQL : Parallel Query Processing
Oracle : PL-SQL : Exit if error, in a SQL script
Oracle : PL-SQL : Complex IF/THEN Processing
Oracle : PL-SQL : Creating triggers
Oracle : PL-SQL : Ranking Data
Oracle : PL-SQL : How to do an outer join in Oracle
Oracle : PL-SQL : Loops in PL-SQL : Hierarchy

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.