21st Century PL/SQL

September 06, 2005

New cursor attribute COUNTHITS

Every now and then I need to report te amount of records which need to be processed by a cursor loop before i accually start the loop. Now I have to write the query twice, once for the count and once for the loop. It would be nice if I could use the cursor to determine the number of records it will return.

For example:

  open cursor c_cur:
  l_number_of_records := c_cur%counthits;
  close c_cur;

  

  for r_cur in c_cur
  loop
    
  end loop;

  
   

Posted by Mike van Zon at 08:19 AM | Add your thoughts (2)

February 09, 2006

Creating a cursor on one step

To avoid defining variables and/or cursors DECLARE BEGIN SELECT COUNT(*) COUNT INTO Cursor1 FROM TABLE; IF Cursor1.COUNT=1 THEN ... END IF; END; DECLARE BEGIN SELECT emp.COUNT(*) COUNT INTO Cursor1 FROM TABLE; IF Cursor1.COUNT(1)=1 AND Cursor1.EMP=\'ORACLE\' THEN ... END IF; END;

Posted by juan carlos reyes pacheco at 03:34 PM | Add your thoughts (0)

June 29, 2006

WITH clause for PL/SQL block

I\'d like to be able to define a subquery that can be referenced by multiple statements within a BEGIN/END block, much like you can define a subquery in the WITH clause of a SELECT statement and reference it anywhere in the statement. This could be done either by adding a new section to the DECLARE/BEGIN/EXCEPTION/END block or by defining a new cursor-like datatype. Here\'s one possibility: DECLARE lv_x NUMBER; USING lrs_customers AS (SELECT a, b, c FROM ...) BEGIN SELECT count(*) INTO lv_x FROM lrs_customers; UPDATE table1 WHERE a IN (SELECT a FROM lrs_customers) DELETE FROM table2 WHERE b NOT IN (SELECT b FROM lrs_customers) END;

Posted by Alan Beaulieu at 10:34 PM | Add your thoughts (0)