MySQL - SPLessons

MySQL Cursors

Chapter 17

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

MySQL Cursors

MySQL Cursors

shape Description

MySQL Cursors are supported inside stored programs. Whenever a client get a connection with the server, by default Oracle server provides an implicit work area to each client for its internal processing of MySQL statements.

This work area is dedicated to a specific client. This implicit work area is called as implicit cursor. A client can access the implicit cursor with the name MySQL.

MySQL Cursors are used for rows repetition returned by a query on a row-by-row process. SQL commands will function on all the rows at one time in the program. Cursors can be created inside the Triggers, Functions and Stored Procedures.

MySQL Cursors contain 3-properties:

shape Conceptual
figure

Asensitive

shape Description

MySQL Cursors can be classified into the following types:

  • Asensitive cursors
  • Insensitive cursors.

asensitive cursor

  • Asensitive cursor focus on genuine data.
  • Asensitive cursor work faster than the insensitive cursor.
  • Asensitive cursor doesn’t need any duplicate copy of data.
  • Any alteration done in the data through dead end should effect the data contain in asensitive cursors.

insensitive cursor

  • An insensitive cursor uses duplicate copy data.
  • Insensitive cursors work very slower.
  • Insensitive cursors contain duplicate data.

Read only

shape Description

In read only data, there is only permission to read the data. And it does not contain any permissions to update the data or alter the data.

one directional

shape Description

MySQL cursor moves only in one direction. Data fetching can be done in only one direction, and cannot fetch data in opposite direction.

Creating the cursor

shape Description

Creating MySQL Cursors is done by creating cursor with all the specifications.

shape Syntax

Declare <cursor_name>cursor for<select statement>

open <cursor_name>;

fetch<cursor_name>into<variable1>,<variable2>;

close<cursor_name>;

cursor_name => The name assigned to a cursor.

variables => variables are the column values.

shape Examples

By viewing the below example, the concept of create cursors can be easily understood.

mysql> select * from student;
+--------+----------+-----------+
| stu_id | stu_name | stu_class |
+--------+----------+-----------+
|      1 | david    |        10 |
|      2 | shah     |        20 |
|      3 | mike     |        30 |
|      4 | maze     |        40 |
+--------+----------+-----------+
4 rows in set (0.00 sec)

mysql> delimiter $$
mysql> create procedure curdemo(id int)
    -> begin
    -> declare name varchar(255);
    -> declare cur1 cursor for select stu_name from student where stu_id=id;
    -> open cur1;
    -> fetch cur1 into name;
    -> select name;
    -> close cur1;
    -> end $$
Query OK, 0 rows affected (0.13 sec)

mysql> delimiter ;
mysql> call curdemo(2);
+------+
| name |
+------+
| shah |
+------+
1 row in set (0.39 sec)

In the above example, curdemo is a input parameter which accept input values. cur1 is the attribute used in the cursor. open() is used to open the cursor, fetch is used to fetch the row values into names and close cursor will remove the open cursor for same minutes. Then the call cursor will execute the curdemo and return the records(i.e, call cursor will call the stu_id(2) and it will return the name(shah)).

Closing a cursor

shape Description

Close Cursor is used to close the Cursor upto some extent, when the open cursor is not required. The keyword close in the cursor will discharge the present records to close and then open the open cursor.

shape Conceptual
figure

  • Declare – Is a variable length value.
  • Open – Opens a cursor variable that is associate with a query.
  • Fetch – Retrieve a specific row and columns.
  • Empty – Which contain an empty set.
  • Close – Closing of a cursor.

shape Syntax

Declare <cursor_name>cursor for<select statement>

open <cursor_name>;

fetch<cursor_name>into<variable1>,<variable2>;

close<cursor_name>;

Cursor_name => The name assigned to a cursor.

variables => variables are the column values.

shape Examples

By viewing the below example, the concept of close cursors can be easily understood.

mysql> select * from student;
+--------+----------+-----------+
| stu_id | stu_name | stu_class |
+--------+----------+-----------+
|      1 | david    |        10 |
|      2 | shah     |        20 |
|      3 | mike     |        30 |
|      4 | maze     |        40 |
+--------+----------+-----------+
4 rows in set (0.00 sec)

mysql> delimiter $$
mysql> create procedure close_cursor(id int)
    -> begin
    -> declare name varchar(255);
    -> declare cur1 cursor for select stu_name from student where stu_id=id;
open cur1;
    -> fetch cur1 into name;
    -> select name;
    -> close cur1;
    -> end $$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call close_cursor(4);
+------+
| name |
+------+
| maze |
+------+
1 row in set (0.39 sec)

In the above example, close_cursor is a input parameter which accept input values. cur1 is the attribute used in the cursor. open() is used to open the cursor, fetch is used to fetch the record values from cursor, select name will return the name of the record and close cursor is used to close the cur1. Then the call cursor will execute the cur1 and return the records(i.e, call cursor will call the stu_id(4) and it will return the name(maze)).

Summary

shape Key Points

  • MySQL Cursors are database objects used in the database application used to manipulate the data.
  • Read only will only read the data.
  • Asensitive cursor focuses to genuine information.
  • Insensitive cursor utilizes duplicate information.