Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: Cursors
Oracle Tips by Burleson
 

The cursor is not the little blinky thing on your screen. Nor is it someone who talks dirty.

A cursor is a pointer to a memory area where your executing query is waiting.  First, you create your pointer by declaring a cursor query, you initialize that area (by OPENing the cursor), you populate the area (by FETCHing data) and when you're finished, you CLOSE the cursor to free up the memory.

The syntax for declaring a cursor is:

CURSOR <cursor name>[(<parameter list>)] IS
  <select statement>;


The syntax for cursor processing is:

OPEN <cursor name>[(<parameters>)];
FETCH <cursor name> INTO <variable list|record>;
CLOSE <cursor name>;

Let's take a look at a very simple cursor that retrieves a single row, displays it on the screen and then exits:

DECLARE

  v_ename emp.ename%TYPE;  -- Step 1 

  CURSOR c_get_name IS     -- Step 2
    SELECT ename
      FROM emp
      LIMIT 5; 

BEGIN

  OPEN c_get_name;                -- Step 3
  FETCH c_get_name INTO v_ename;  -- Step 4
  DBMS_OUTPUT.PUT_LINE( 'Ename: ' || v_ename );  -- Step 5
  CLOSE c_get_name;               -- Step 6 

END;

INFO:  Ename: SMITH

Your results may be a little different than mine depending on the name returned.  Without an order by, there is no guarantee that any particular ename will be returned.

Step 1 declares a regular variable that will be used to hold the data returned by the cursor. 

In step 2, I declared a cursor query.  A cursor query is a normal select statement preceded by the CURSOR keyword.  The cursor is given a name like a TABLE or RECORD type, although it is not really creating a type.  It is just naming your memory pointer.  In my case here, I decided to limit the number of rows to 5.

In step 3, I opened the cursor (initialized it). In step 4, I fetched data into my variable and in step 6, I closed the cursor.  Step 5 displayed the data to the screen.

I can modify this block to return more than a single record.  There are two ways to loop through a cursor.  I will do the more complicated one first and then show you how to do it the easy way:

DECLARE
  v_ename emp.ename%TYPE;  -- Step 1 

  CURSOR c_get_name IS     -- Step 2
    SELECT ename
      FROM emp
      LIMIT 5;
BEGIN

 
OPEN c_get_name;                -- Step 3 

  LOOP
    FETCH c_get_name INTO v_ename;  -- Step 4
    EXIT WHEN v_ename IS NULL;
    DBMS_OUTPUT.PUT_LINE( 'Ename: ' || v_ename );  -- Step 5

  END LOOP;
 

  CLOSE c_get_name;               -- Step 6
END; 

INFO:  Ename: SMITH
INFO:  Ename: ALLEN
INFO:  Ename: WARD
INFO:  Ename: JONES
INFO:  Ename: MARTIN

The code is exactly the same.  I wrapped the fetch and output in a loop.  I exit the loop when v_ename is null.  v_ename will only be null when there are no records to retrieve.

As useful as this cursor is, there is an easier way to achieve the same results:

DECLARE
  CURSOR c_get_name IS     -- Step 2
    SELECT ename
      FROM emp
      LIMIT 5;

BEGIN 

  FOR cursor_variable IN c_get_name LOOP
    DBMS_OUTPUT.PUT_LINE( 'Ename: ' || cursor_variable.ename );  -- Step 5

  END LOOP;


END;

I won't display the output from this code because it exactly matches the last code output.

What did I change?  I was able to get rid of the variable, the open, the fetch and the close.  This is an example of a CURSOR FOR LOOP.  The cursor for loop opens the query and loops through it.  On each loop through, it fetches a row and makes it available in a dynamically created row variable.  In this example, I called it cursor_variable.  It can be named anything you would like to name it.

When the cursor for loop hits the last row, it automatically closes the cursor for you.  It really doesn't get much easier than that.

Cursors have variables associated with them (called cursor attributes) that give you status about that cursor.  Implicit cursors (DML and SELECT INTO) are referred to by the keyword SQL.  Explicit cursors are referred to by name.  SPL has four cursor attributes:  %ROWCOUNT, %FOUND, %NOTFOUND, %ISOPEN.

* %ROWCOUNT – The number of rows affected by a SQL DML statement or the number of rows fetched from an explicit cursor

* %FOUND – True if any row was affected by the statement

* %NOTFOUND – True if no row was affected by the statement

* %ISOPEN – True if the named cursor is already open

Here is a small example:

DECLARE
  v_ename emp.ename%TYPE;  -- Step 1 

  CURSOR c_get_name IS     -- Step 2
    SELECT ename
      FROM emp
      LIMIT 5;

BEGIN

  IF NOT c_get_name%ISOPEN   -- Step 2a
  THEN
    OPEN c_get_name;                -- Step 3
    FETCH c_get_name INTO v_ename;  -- Step 4 

    DBMS_OUTPUT.PUT_LINE( 'Ename: ' || v_ename ||
          ', Row Count: ' || to_char( c_get_name%ROWCOUNT ));  -- Step 5
 
    CLOSE c_get_name;               -- Step 6
  END IF;

  INSERT INTO emp (empno)     -- Step 7
    VALUES (999);


 
DBMS_OUTPUT.PUT_LINE( 'Insert rows: ' || to_char(SQL%ROWCOUNT) );  -- Step 8 

  UPDATE emp                          -- Step 9
    SET ename = 'Jimmy'
    WHERE empno = 999; 

  IF SQL%FOUND THEN   -- Step 10
    DBMS_OUTPUT.PUT_LINE( 'Updated rows: ' || to_char(SQL%ROWCOUNT) ); 

  ELSE

    DBMS_OUTPUT.PUT_LINE( 'No Rows Updated.' );
  END IF;  

END; 

INFO:  Ename: SMITH, Row Count: 1
INFO:  Inserting employee 999
CONTEXT:  SQL statement "INSERT  INTO emp ( empno ) VALUES ( 999 )"
INFO:
CONTEXT:  SQL statement "INSERT  INTO emp ( empno ) VALUES ( 999 )"
INFO:  User enterprisedb added employee(s) on 2006-11-21
CONTEXT:  SQL statement "INSERT  INTO emp ( empno ) VALUES ( 999 )"
INFO:  Insert rows: 1
INFO:  Updating employee 999
CONTEXT:  SQL statement "UPDATE  emp SET ename = 'Jimmy' WHERE empno = 999"
INFO:
CONTEXT:  SQL statement "UPDATE  emp SET ename = 'Jimmy' WHERE empno = 999"
INFO:
CONTEXT:  SQL statement "UPDATE  emp SET ename = 'Jimmy' WHERE empno = 999"
INFO:
CONTEXT:  SQL statement "UPDATE  emp SET ename = 'Jimmy' WHERE empno = 999"
INFO:  User enterprisedb updated employee(s) on 2006-11-21
CONTEXT:  SQL statement "UPDATE  emp SET ename = 'Jimmy' WHERE empno = 999"
INFO:  Updated rows: 1

* If you are wondering where all of the extra information in my output is coming from, the sample schema has triggers that produce additional output.  You can disable or drop those triggers if you prefer not to see that output.  For the examples in the book, I think the additional information is useful.

The new and changed steps here are 2a, 5 and 7-10.

Step 2a uses the %ISOPEN variable to see if the cursor is already open.  If it is not open, it executes the code.

Step 5 was changed to include a %rowcount.

Step 7 inserted a record (via implicit cursor) and step 8 checked the rowcount using the implicit cursor syntax.

Step 9 updated a record and step 10 checked to see if a row was found using the %FOUND attribute and the implicit cursor syntax.  If a row was found (which there was a row), it displayed the number of rows updated. If no row was found, it displayed the text, "No Rows Updated.".

Cursors also allow parameter replacement.  A cursor parameter is a variable name and a data type specified in the cursor declaration.  A cursor may have 0, 1 or more parameters.

Parameters are bound to the query at runtime and allow cursors to be re-used across calls for varying result sets.  Parameters can be used anywhere a variable is allowed in a query: where clause, select list, group by, order by, having.  You cannot replace a column or table name with a parameter.

Below is an example, like the one above, that sets the limit of the select.

DECLARE
  CURSOR c_get_name( p_limit NUMBER ) IS     -- Step 1
    SELECT ename
      FROM emp
      LIMIT p_limit; 

BEGIN 

  FOR cursor_variable IN c_get_name( 5 ) LOOP    -- Step2
    DBMS_OUTPUT.PUT_LINE( 'Ename: ' || cursor_variable.ename );  -- Step 3

  END LOOP;
 

END; 

In the code above, Step 1 creates a cursor with a parameter.  The parameter will be used in the LIMIT clause. Notice that the parameter p_limit is defined as a number and that the same parameter is used in the LIMIT clause.

In Step 2, the cursor is called in a cursor for loop and the parameter is set to 5.  This could be set to any number and it would limit the number of rows returned by the query.  A parameter could have been used to restrict by ename, deptno or any other valid where criteria.

The next step in making your life easier (at least where querying data is concerned) is the REF CURSOR.

             

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter