MySQL Cursor
From w3cyberlearnings
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)
