MySQL Cursor

Summary: in this tutorial, you will learn how to use MySQL cursor in stored procedures to iterate through a result set returned by a SELECT statement.

Introduction to MySQL cursor

To handle a result set inside a stored procedure, you use a cursor. A cursor allows you to iterate a set of rows returned by a query and process each row accordingly.

MySQL cursor is read-only, non-scrollable and asensitive.

• Read-only: you cannot update data in the underlying table through the cursor.
• Non-scrollable: you can only fetch rows in the order determined by the SELECT statement. You cannot fetch rows in the reversed order. In addition, you cannot skip rows or jump to a specific row in the result set.
• Asensitive: there are two kinds of cursors: asensitive cursor and insensitive cursor. An asensitive cursor points to the actual data, whereas an insensitive cursor uses a temporary copy of the data. An asensitive cursor performs faster than an insensitive cursor because it does not have to make a temporary copy of data. However, any change that made to the data from other connections will affect the data that is being used by an asensitive cursor, therefore, it is safer if you do not update the data that is being used by an asensitive cursor. MySQL cursor is asensitive.

You can use MySQL cursors in stored procedures, stored functions, and triggers.

Working with MySQL cursor

First, you have to declare a cursor by using the DECLARE statement:

1	DECLARE cursor_name CURSOR FOR SELECT_statement;

The cursor declaration must be after any variable declaration. If you declare a cursor before variables declaration, MySQL will issue an error. A cursor must always be associated with a SELECT statement.

Next, you open the cursor by using the OPEN statement. The OPEN statement initializes the result set for the cursor, therefore, you must call the OPEN statement before fetching rows from the result set.

1	OPEN cursor_name;

Then, you use the FETCH statement to retrieve the next row pointed by the cursor and move the cursor to the next row in the result set.

1	FETCH cursor_name INTO variables list;

After that, you can check to see if there is any row available before fetching it.

Finally, you call the CLOSE statement to deactivate the cursor and release the memory associated with it as follows:

1	CLOSE cursor_name;

When the cursor is no longer used, you should close it.

When working with MySQL cursor, you must also declare a NOT FOUND handler to handle the situation when the cursor could not find any row. Because each time you call the FETCH statement, the cursor attempts to read the next row in the result set. When the cursor reaches the end of the result set, it will not be able to get the data, and a condition is raised. The handler is used to handle this condition.

To declare a NOT FOUND handler, you use the following syntax:

1	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

The finished is a variable to indicate that the cursor has reached the end of the result set. Notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures.

The following diagram illustrates how MySQL cursor works.

MySQL Cursor Example

We are going to develop a stored procedure that builds an email list of all employees in the employeestable in the sample database.

First, declare some variables, a cursor for looping over the emails of employees, and a NOT FOUNDhandler:

1 2 3 4 5 6 7 8 9 10

DECLARE finished INTEGER DEFAULT 0; DECLARE email varchar(255) DEFAULT ""; -- declare cursor for employee email DEClARE email_cursor CURSOR FOR SELECT email FROM employees; -- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Next, open the email_cursor by using the OPEN statement:

1	OPEN email_cursor;

Then, iterate the email list, and concatenate all emails where each email is separated by a semicolon(;):

1 2 3 4 5 6 7 8

get_email: LOOP FETCH email_cursor INTO v_email; IF v_finished = 1 THEN LEAVE get_email; END IF; -- build email list SET email_list = CONCAT(v_email,";",email_list); END LOOP get_email;

After that, inside the loop we used the v_finished variable to check if there is an email in the list to terminate the loop.

Finally, close the cursor using the CLOSE statement:

1	CLOSE email_cursor;

The build_email_list stored procedure is as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

DELIMITER $$ CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000)) BEGIN DECLARE v_finished INTEGER DEFAULT 0;         DECLARE v_email varchar(100) DEFAULT ""; -- declare cursor for employee email DEClARE email_cursor CURSOR FOR SELECT email FROM employees; -- declare NOT FOUND handler DECLARE CONTINUE HANDLER         FOR NOT FOUND SET v_finished = 1; OPEN email_cursor; get_email: LOOP FETCH email_cursor INTO v_email; IF v_finished = 1 THEN LEAVE get_email; END IF; -- build email list SET email_list = CONCAT(v_email,";",email_list); END LOOP get_email; CLOSE email_cursor; END$$ DELIMITER ;

You can test the build_email_list stored procedure using the following script:

1 2 3

SET @email_list = ""; CALL build_email_list(@email_list); SELECT @email_list;

In this tutorial, we have shown you how to use MySQL cursor to iterate a result set and process each row accordingly.

Fetch cursor to two variables : Cursor Fetch « Cursor « Oracle PL / SQL

1. Oracle PL / SQL
2. Cursor
3. Cursor Fetch
4. 
   

Fetch cursor to two variables

  
SQL>
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );

Table created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, 'Mar', 'Wells','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, 'Man', 'Kyte','Economics', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, 'Pat', 'Poll','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, 'Tim', 'Viper','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, 'Barbara', 'Blues','Economics', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, 'David', 'Large','Music', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, 'Rose', 'Bond','Music', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);

1 row created.

SQL>
SQL>
SQL> DECLARE
  2    firstName VARCHAR2(20);
  3    v_LastName  VARCHAR2(20);
  4    CURSOR c_lecturer IS
  5      SELECT first_name, last_name FROM lecturer;
  6  BEGIN
  7    OPEN c_lecturer;
  8    LOOP
  9      FETCH c_lecturer INTO firstName, v_LastName;
 10      EXIT WHEN c_lecturer%NOTFOUND;
 11    END LOOP;
 12    CLOSE c_lecturer;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table lecturer;

Table dropped.

SQL>
SQL>

Finding duplicate values in MySQL

Do a SELECT with a GROUP BY clause. Let's say name is the column you want to find duplicates in:

SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

This will return a result with the name value in the first column, and a count of how many times that value appears in the second

How do I escape ampersands in batch files?

How do I escape ampersands in a batch file (or from the Windows command line) in order to use the start command to open web pages with ampersands in the URL?

Double quotes will not work with start; this starts a new command-line window instead.

Update 1: Wael Dalloul's solution works. In addition, if there are URL encoded characters (e.g. space is encoded as %20) in the URL and it is in a batch file then '%' must be encoded as '%%'. This is not the case in the example.

Example, from the command line (CMD.EXE):

start http://www.google.com/search?client=opera&rls=en&q=escape+ampersand&sourceid=opera&ie=utf-8&oe=utf-8

will result in

http://www.google.com/search?client=opera 

being opened in the default browser and these errors in the command line window:

'rls' is not recognized as an internal or external command,
operable program or batch file.
'q' is not recognized as an internal or external command,
operable program or batch file.
'sourceid' is not recognized as an internal or external command,
operable program or batch file.
'ie' is not recognized as an internal or external command,
operable program or batch file.
'oe' is not recognized as an internal or external command,
operable program or batch file.

Platform: Windows XP 64 bit SP2.

From a cmd:

• & is escaped like this: ^& (based on @Wael Dalloul's answer)
• % does not need to be escaped

An example:

start http://www.google.com/search?client=opera^&rls=en^&q=escape+ampersand%20and%20percentage+in+cmd^&sourceid=opera^&ie=utf-8^&oe=utf-8

From a batch file

• & is escaped like this: ^& (based on @Wael Dalloul's answer)
• % is escaped like this: %% (based on the OPs update)

An example:

start http://www.google.com/search?client=opera^&rls=en^&q=escape+ampersand%%20and%%20percentage+in+batch+file^&sourceid=opera^&ie=utf-8^&oe=utf-8

Powershell export CSV list from files and folders in directory

dir -Recurse | Export-Csv files.csv