Created 2014-06-27 22:02:51 Modified 2014-06-28 00:03:52
A database cursor allows you to manipulate data for each row, if you need a process which allows you to update each row with different data, you need a database cursor, for instance let's say you have in your table first name and surname on your table and you want to fill the field fullname concatenating the previous two fields, you can't use an update sentence because it will update the fullname in every row with the same data.
In this tutorial we are going to develop a cursor which goes to generate a password for each user based on his own attributes
this tutorial was written using the following gear:
First we need a database to work on the cursor, let's take the following script and run it on mysql
CREATE DATABASE tutorial; USE tutorial; CREATE TABLE users( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(45) NOT NULL, birthdate DATE NOT NULL, password VARCHAR(45) NOT NULL ); INSERT INTO users(name, birthdate, password)VALUES('Rodrigo', '1991-04-02',''); INSERT INTO users(name, birthdate, password)VALUES('Mike' , '1993-06-12',''); INSERT INTO users(name, birthdate, password)VALUES('Joe' , '1989-09-22',''); INSERT INTO users(name, birthdate, password)VALUES('José' , '1996-12-11',''); INSERT INTO users(name, birthdate, password)VALUES('Xing' , '1990-07-23','');
now as you may see we don't have a password defined for each user, or perhaps we could have, but we want to reset all the passwords, whatever the case, we need a cursor then
DELIMITER $$ CREATE PROCEDURE genera_passwords() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE id_user INT; DECLARE nombre VARCHAR(45); DECLARE fec_nac VARCHAR(10); DECLARE cur_map CURSOR FOR SELECT id, name, birthdate FROM users; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur_map; read_loop: LOOP FETCH cur_map INTO id_user, nombre, fec_nac; IF done THEN LEAVE read_loop; END IF; UPDATE users SET PASSWORD=CONCAT(nombre, SUBSTRING(fec_nac, 1, 4)) WHERE id=id_user; END LOOP; CLOSE cur_map; END$$ DELIMITER ;
The first thing that you can notice is we are executing the cursor within stored procedure, you can't execute a cursor by itself, on mysql, cursors can only be executed inside a stored program (stored procedure).
"MySQL supports cursors inside stored programs" Mysql Official website
up to this point, we are done, now let's execute the stored procedure and see the data changes
we have executed the cursor within the stored procedure, now we should have passwords for all depending of their names and birthdates
With cursors we can manipulate data for each row without affecting another rows, you can go row by row using the data for that specific row where you are, for that same specific row on where you are. I recommend to work with mysql workbench, I leave a screenshot