Home Mysql Mysql5 CURSORS
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

CURSORS

Advertisement
Cursors are used when the SQL Select statement is expected to return more than one row. Cursors are supported inside procedures and functions. Cursors must be declared and its definition contains the query. The cursor must be defined in the DECLARE sectio

CURSORS

     

Cursors are used when the SQL Select statement is expected to return more than one row. Cursors are supported inside procedures and functions. Cursors must be declared and its definition contains the query. The cursor must be defined in the DECLARE section of the program. A cursor must be opened before processing and close after processing.

Syntax to declare the cursor :
  DECLARE <cursor_name> CURSOR FOR <select_statement>

Multiple cursors can be declared in the procedures and functions but each cursor must have a unique name. And in defining the cursor the select_statement cannot have INTO clause.

Syntax to open the cursor :
  OPEN <cursor_name>

By this statement we can open the previously declared cursor.

Syntax to store data in the cursor :
  FETCH <cursor_name> INTO <var1>,<var2>??.

The above statement is used to fetch the next row if a row exists by using the defined open cursor.

Syntax to close the cursor :
  CLOSE <cursor_name>

By this statement we can close the previously opened cursor. If it is not closed explicitly then a cursor is closed at the end of compound statement in which that was declared.

In the following example firstly we are declaring the Cursor and selecting the all record from Emp table. And after opened the cursor we fetch the record one by one from cursor. And then insert these record in Emp2 table. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE DemoCurs1()
    -> BEGIN
    -> DECLARE d INT DEFAULT 0;
    -> DECLARE id,sal,perk INT;
    -> DECLARE name,city,desig VARCHAR(20);
    -> DECLARE cur CURSOR FOR SELECT * FROM Emp;
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET d=1;
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET d=1;
    -> OPEN cur;
    -> lbl: LOOP
    -> IF d=1 THEN
    -> LEAVE lbl;
    -> END IF;
    -> IF NOT d=1 THEN
    -> FETCH cur INTO id,name,city,desig,sal,perk;
    -> INSERT INTO Emp2 VALUES(id,name,city,desig,sal,perk);
    -> END IF;
    -> END LOOP;
    -> CLOSE cur;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL DemoCurs1();
Query OK, 1 row affected (0.12 sec)
mysql> SELECT * FROM Emp2;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10300  | 853   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10300  | 853   |
| 3   | Chandan | Banglore | Team Leader       | 15450  | 999   |
| 5   | Tapan   | Pune     | Developer         | 20600  | 1111  |
| 6   | Amar    | Chennai  | Developer         | 16000  | 1124  |
| 7   | Santosh | Delhi    | Designer          | 10000  | 865   |
| 8   | Suman   | Pune     | Web Designer      | 20000  | 658   |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.00 sec)
Advertisement

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: March 13, 2008

Ask Questions?    Discuss: CURSORS   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
R.Divya
February 1, 2012
commands

very gud
nitu
May 22, 2012
cursors

nice..
kishore
October 18, 2012
Detail

I need a detail explanation only for the cursors.
agatha
May 30, 2014
fill cursor after doing some code

can i just declaring cursor then fill it after doing something?
DMCA.com