Login

Create a cursor

Rodrigo

By Rodrigo

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:

  • Hardware:           laptop Thinkpad E430 Intel I5, 4gb Ram, 500gb HDD
  • OS:                     x86_64 GNU/Linux Ubuntu 14.10
  • Kernel:                3.13.0-29-generic
  • IDE/Text editor:   MySql workbench 6.0
  • DB:                      Mysql 5.5.31
  • Mysql client:        MySql workbench 6.0

 

1.- Creating the db:

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','');

 

2.- Creating cursor:

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

3.- Executing the cursor:

up to this point, we are done, now let's execute the stored procedure and see the data changes

call genera_passwords;

we have executed the cursor within the stored procedure, now we should have passwords for all depending of their names and birthdates

select*from users;

 

Download it in https://docs.google.com/file/d/0BwOqSob-RrBOVVh0V1pveDd1bk0/edit?pli=1

4.- Conclusion:

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

5.- Reference links:




comments powered by Disqus