Cybercrux

Everything is achievable through technology

Cursor

Cursors in SQL procedures

In SQL procedures, a cursor make it possible to define a result set (a
set of data rows) and perform complex logic on a row by row basis. By using
the same mechanics, an SQL procedure can also define a result set and return
it directly to the caller of the SQL procedure or to a client application.

A cursor can be viewed as a pointer to one row in a set of rows. The cursor
can only reference one row at a time, but can move to other rows of the result
set as needed.

To use cursors in SQL procedures, you need to do the following:

  1. Declare a cursor that defines a result set.
  2. Open the cursor to establish the result set.
  3. Fetch the data into local variables as needed from the cursor, one row
    at a time.
  4. Close the cursor when done

To work with cursors you must use the following SQL statements:

  • DECLARE CURSOR
  • OPEN
  • FETCH
  • CLOSE
The following example demonstrates the basic use of a read-only cursor
within an SQL procedure:

  CREATE PROCEDURE sum_salaries(OUT sum INTEGER) 
  LANGUAGE SQL
  BEGIN
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE p_sum INTEGER;
    DECLARE p_sal INTEGER;
    DECLARE c CURSOR FOR SELECT SALARY FROM EMPLOYEE;

     SET p_sum = 0;

     OPEN c;

     FETCH FROM c INTO p_sal;

     WHILE(SQLSTATE = '00000') DO
        SET p_sum = p_sum + p_sal;
        FETCH FROM c INTO p_sal; 
     END WHILE;

     CLOSE c;

     SET sum = p_sum;

  END%

Here is a more complex example of use of a cursor within an SQL procedure.
This example demonstrates the combined use of a cursor and SQL PL statements.

Advertisements

2 thoughts on “Cursor

  1. Hello , you are good writer, i love your site

  2. I must have missed that when it happened. Very interesting post, thanks for sharing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s