MySQL Tutorial
Introduction Installation of MySQL Getting MySQL server information RDBMS Concepts Data Definition Language Data types Managing Databases Managing Tables Managing Views Inserting records in a table Updating records in a table Deleting records from a table Retrieving Records Data Control Language Variables in MySQL Procedures in MySQL Built-In functions in MySQL User Defined functions in MySQL Triggers in MySQL Cursors in MySQL Transaction control Event scheduler Tuning MySQL Server Performance Managing users Importing and Exporting data Database Testing Workbench PHPMyAdminCursors in MySQL
To work with multiple records, you can use cursors. We can handle each row at a time using cursors.Key things to know about cursor
- MySQL cursor is read-only, non-scrollable.
- MySQL cursor is asensitive. It means that cursor works on actual table data. If any other user changes the data, those changes are reflected in the cursor as well.
Creating a new cursor in MySQL
-Declare the cursor using below syntax. The cursor declaration shoud be done after other variables are declared.
DECLARE mycursor CURSOR FOR SELECT_statement;
-Then open the cursor using below syntax.
OPEN mycursor;
-Fetch one row at a time using below syntax.
FETCH mycursor INTO v1, v2;
-At the end, you need to close the cursor using below syntax.
CLOSE mycursor;
When you try to fetch the rows from the empty cursor, you get an error. So to handle this error, we need to define NOT FOUND handler using below syntax.
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET completed = 1;
-- Example on the cursor
DELIMITER $$
CREATE PROCEDURE getCustdata (INOUT data varchar(1000))
BEGIN
DECLARE completed INTEGER DEFAULT 0;
DECLARE tempdata varchar(50) DEFAULT “”;
DEClARE mycursor CURSOR FOR
SELECT FirstName FROM CUSTOMER;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET completed = 1;
OPEN mycursor;
myloop: LOOP
FETCH mycursor INTO tempdata;
IF completed = 1 THEN
LEAVE myloop;
END IF;
SET data = CONCAT(tempdata,”;”,data );
END LOOP myloop;
CLOSE mycursor;
END$$
DELIMITER ;
You can test your cursor using below statements.
SET @custdata = “”;
CALL getCustdata(@custdata);
SELECT @custdata;
Web development and Automation testing
solutions delivered!!