MySQL Cursor

Create Cursor in MySQL

To create CURSOR, use the DECLARE

     DECLARE myName VARCHAR(200);
     DECLARE student_age INT DEFAULT 18;
     DECLARE all_student CURSOR
         FOR SELECT * FROM student_table;
     DECLARE check_con INT;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET check_con = 1;

MySQL cursor for declaration

All cursor variables have to declare first before use.

MySQL cursor for loop

mysql> DELIMITER $$
mysql> CREATE PROCEDURE myLoop()
    -> DETERMINISTIC
    -> BEGIN
    ->       DECLARE myCount INT DEFAULT 10;
    ->       my_loop1: LOOP
    ->           SET myCount = myCount-1;
    ->           SELECT myCount;
    ->           IF myCount=0 THEN
    ->              LEAVE my_loop1;
    ->           END IF;
    ->       END LOOP my_loop1;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL myLoop();
+---------+
| myCount |
+---------+
|       9 | 
+---------+
1 row in set (0.00 sec)

+---------+
| myCount |
+---------+
|       8 | 
+---------+
1 row in set (0.00 sec)

+---------+
| myCount |
+---------+
|       7 | 
+---------+
1 row in set (0.00 sec)

+---------+
| myCount |
+---------+
|       6 | 
+---------+
1 row in set (0.00 sec)

+---------+
| myCount |
+---------+
|       5 | 
+---------+
1 row in set (0.00 sec)

+---------+
| myCount |
+---------+
|       4 | 
+---------+
1 row in set (0.00 sec)

+---------+
| myCount |
+---------+
|       3 | 
+---------+
1 row in set (0.00 sec)

+---------+
| myCount |
+---------+
|       2 | 
+---------+
1 row in set (0.00 sec)

+---------+
| myCount |
+---------+
|       1 | 
+---------+
1 row in set (0.00 sec)

+---------+
| myCount |
+---------+
|       0 | 
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL cursor for select

mysql> DELIMITER $$
mysql> CREATE PROCEDURE mySelect(IN myWord VARCHAR(20))
    -> BEGIN
    ->      DECLARE myValue VARCHAR(40) DEFAULT 'Good Old Day';
    ->      SET myValue = CONCAT(myValue , ' ',myWord);
    ->      SELECT myValue;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL mySelect('young man!');
+-------------------------+
| myValue                 |
+-------------------------+
| Good Old Day young man! | 
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Table

mysql> SELECT id,content,status,t FROM todo;
+----+-------------------------+--------+---------------------+
| id | content                 | status | t                   |
+----+-------------------------+--------+---------------------+
|  1 | what is your name       | open   | 2010-02-20 14:11:32 | 
|  2 | go to work man          | closed | 2010-02-20 14:10:30 | 
|  6 | lovely                  | open   | 2010-08-15 23:12:35 | 
|  7 | lovely                  | open   | 2010-08-15 23:12:59 | 
| 10 | fishing and drinking    | open   | 2012-03-02 10:30:30 | 
| 20 | reading and watching tv | open   | 2012-03-04 10:30:30 | 
| 30 | eating and watching tv  | closed | 2012-03-18 10:20:30 | 
| 40 | working and assignment  | closed | 2012-03-11 10:30:00 | 
+----+-------------------------+--------+---------------------+
8 rows in set (0.00 sec)

MySql cursor for stored procedure

mysql> delimiter $$
mysql> CREATE PROCEDURE content_info
    -> (IN INtodoId INT, OUT OUTtodoContent VARCHAR(200))
    -> BEGIN 
    ->     DECLARE todo_content_name VARCHAR(200);
    ->     DECLARE content_infcur CURSOR 
    ->           FOR SELECT content FROM todo 
    ->               WHERE id= INtodoId;
    ->      OPEN content_infcur;
    ->      FETCH content_infcur INTO todo_content_name;
    ->      CLOSE content_infcur;
    ->      SET OUTtodoContent = todo_content_name;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

Test cursor for stored procedure

mysql> CALL content_info(20,@con);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @con;
+-------------------------+
| @con                    |
+-------------------------+
| reading and watching tv | 
+-------------------------+
1 row in set (0.00 sec)

Create table open_proce

This table use to verify code of the todo list.

mysql> CREATE TABLE open_proce
    -> (open_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  verify_code VARCHAR(255) NOT NULL,
    ->  date TIMESTAMP );
Query OK, 0 rows affected (0.02 sec)

check todo id and generate verify code to insert into the open_proce table

This stored procedure require the input for the todo Id, and check if the id had the status 'open' IF the status is opened, it will generate the unique md5 key, and insert into the open_proce table. Finally, return the verify code.

mysql> DELIMITER $$
mysql> CREATE PROCEDURE gen_verify_code2(IN IN_todo_ID INT, OUT OUT_verify_code VARCHAR(255))
    ->      BEGIN
    ->          DECLARE verify_code VARCHAR(255);
    ->           DECLARE d_content, d_status VARCHAR(100);
    ->          DECLARE content_info CURSOR
    ->               FOR SELECT content,status FROM todo
    ->                   WHERE id = IN_todo_ID;
    ->          OPEN content_info;
    ->           FETCH content_info INTO d_content, d_status;
    ->          IF d_status='open' THEN
    ->             SET verify_code = md5(d_content);
    ->             INSERT INTO open_proce(verify_code,date)
    ->        VALUES(verify_code,CURRENT_TIMESTAMP);
    ->          ELSE
    ->             SET verify_code = "can not define";
    ->          END IF;
    ->          CLOSE content_info;
    ->          SET OUT_verify_code = verify_code;
    ->      END;
    ->     $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL gen_verify_code2(1,@code);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT @code;
+----------------------------------+
| @code                            |
+----------------------------------+
| 84ed2067cf603fff6b8fd958b79972f2 | 
+----------------------------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM open_proce;
+---------+----------------------------------+---------------------+
| open_id | verify_code                      | date                |
+---------+----------------------------------+---------------------+
|       1 | 84ed2067cf603fff6b8fd958b79972f2 | 2012-02-03 00:09:54 | 
+---------+----------------------------------+---------------------+

check for the todo status

The todo ID=2 is closed, so the verify code is not set.

mysql> CALL gen_verify_code2(2,@code);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @code;
+----------------+
| @code          |
+----------------+
| can not define | 
+----------------+
1 row in set (0.00 sec)