Cursors in MySQL

Aimed at the 3rd year undergraduates of the University of Vocational Technology, Sri Lanka, 2018/19

1. Cursors in MySQL
Tharindu Weerasinghe
www.tharinduweerasinghe.com

2. l

Cursors
A cursor is a structure that allows you to go over records
sequentially, and perform operations based on the results of
the cursor SELECT statement.
Cursors are created with a DECLARE CURSOR statement and
opened with an OPEN statement. Rows are read with a FETCH
statement before the cursor is finally closed with a CLOSE
statement.
A cursor should be created inside a Stored Programs.

3. l

Cursors

4. Cursors (Let's work out - Step1)
CREATE TABLE sampleTable
(sampleCol1 INT, sampleCol2 VARCHAR(10));
INSERT INTO sampleTable VALUES (1, "First");
INSERT INTO sampleTable VALUES (1, "Second");

5. Full .mysql script
is shown in the next slide

6. DROP TABLE IF EXISTS sampleTable;
CREATE TABLE sampleTable
(sampleCol1 INT, sampleCol2 VARCHAR(100));
INSERT INTO sampleTable VALUES (1, "First");
INSERT INTO sampleTable VALUES (2, "Second");
DROP PROCEDURE IF EXISTS sampleProc;
DELIMITER #
/*Create the Procedure */
CREATE PROCEDURE sampleProc()
BEGIN
  DECLARE x INT;
  /*Create the Cursor */
  DECLARE sampleCur CURSOR FOR SELECT sampleCol1 FROM sampleTable;
  /*Open the Cursor */
  OPEN sampleCur;
  /*Open the Loop to loop through the table according to the cursor SELECT */
  read_loop: LOOP
  /*FETCH the no.results to x*/
  FETCH sampleCur INTO x;
  IF (x > 0) THEN
     INSERT INTO sampleTable VALUES (1010, "InsideCur­Inside IF");  
  ELSE 
     INSERT INTO sampleTable VALUES (101010, "InsideCur­Outside IF");  
  END IF;
  LEAVE read_loop;
  END LOOP;
  CLOSE sampleCur;
END; 
#

8. A bit of a complex example

9. DROP TABLE IF EXISTS sampleTable1;
DROP TABLE IF EXISTS sampleTable2;
DROP TABLE IF EXISTS sampleTable3;
CREATE TABLE sampleTable1
(sampleCol1 INT, sampleCol2 VARCHAR(100));
CREATE TABLE sampleTable2
(sampleCol1 INT, sampleCol2 VARCHAR(100));
CREATE TABLE sampleTable3
(sampleCol1 INT, sampleCol2 VARCHAR(100));
INSERT INTO sampleTable1 VALUES (1, "First");
INSERT INTO sampleTable1 VALUES (2, "Second");
INSERT INTO sampleTable1 VALUES (3, "Third");
INSERT INTO sampleTable2 VALUES (10, "First");
INSERT INTO sampleTable2 VALUES (20, "Second");
INSERT INTO sampleTable2 VALUES (30, "Third");

10. DROP PROCEDURE IF EXISTS sampleProc;
DELIMITER #
/*Create the Procedure */
CREATE PROCEDURE sampleProc()
BEGIN
  DECLARE x,y INT;  
  DECLARE done INT DEFAULT FALSE;
  DECLARE sampleCur1 CURSOR FOR SELECT sampleCol1 FROM 
sampleTable1;
  DECLARE sampleCur2 CURSOR FOR SELECT sampleCol1 FROM 
sampleTable2;  
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN sampleCur1;
  OPEN sampleCur2;  
  read_loop: LOOP  
  FETCH sampleCur1 INTO x;
  FETCH sampleCur2 INTO y;
  IF done THEN
      LEAVE read_loop;
    END IF;
  IF (x < y) THEN
     INSERT INTO sampleTable3 VALUES (1010, "InsideCur­Inside IF"); 
 
  ELSE 
     INSERT INTO sampleTable3 VALUES (101010, "InsideCur­Outside 
IF");  
  END IF;
  END LOOP;
  CLOSE sampleCur1;
  CLOSE sampleCur2;
END; 
#

11. Excercise:
1. Create three tables namely and have columns as follows:
1.1 Employee (EmpID, Name, Age)
1.2 Inventory (ItemID, ItemName, UsedEmpID)
1.3 EmpInvMatchTable (EmpID, ItemID)
2. Insert some relevant values to Employee and Inventory. Make sure there are values
in the Inventory table where it has some existing Employees using the items. And also
note that there are items which have no owner (means no EmplD)
3. Write a Procedure that has two cursors like in the sample, check the existing EmpIDs
in the Inventory table and if found then insert both the IDs (Emp and Item) to the third
table.
Note: You should execute the procedure and show me the results :)

12. Gratitude
References:
https://mariadb.com/kb/en/library/cursor-overview/
https://dev.mysql.com/doc/refman/8.0/en/cursors.html
http://www.mysqltutorial.org/mysql-cursor/