MYSQL tutorial 14: basic operations on a cursor _ MySQL

MYSQL tutorial 14: basic cursor operations bitsCN.com

MYSQL tutorial 14: basic operations on cursors

Related links:

MYSQL: Basic operations

Http: // database/201212/173868 .html

MYSQL 2: use regular expressions to search

Http: // database/201212/173869 .html

MYSQL 3: full text search

Http: // database/201212/173873 .html

MYSQL entry 4: MYSQL data types

Http: // database/201212/175536 .html

MYSQL entry 5: MYSQL character set

Http: // database/201212/175541 .html

MYSQL getting started 6: MYSQL operators

Http: // database/201212/175862 .html

MYSQL entry 7: MYSQL common functions

Http: // database/201212/175864 .html

Step 8: basic database and table operations

Http: // database/201212/175867 .html

MYSQL entry 9: simple indexing operations

Http: // database/201212/176772 .html

MYSQL Getting started: Basic View operations

Http: // database/201212/176775 .html

MYSQL getting started 11: basic trigger operations

Http: // database/201212/176781 .html

MYSQL entry 12: basic operations of stored procedures

Http: // database/201212/177380 .html

Step 13: basic operations of user-defined functions

Http: // database/201212/177382 .html

CURSOR is a database query stored on the MySQL server. it is not a SELECT statement but a result set retrieved by this statement. After the cursor is stored, the application can scroll or browse the data as needed.

Cursors are mainly used for interactive applications. users need to scroll the data on the screen and browse or modify the data.

To use a cursor, MySQL5 or a later version is required.

1. procedure for using a cursor

Before using a cursor, you must declare (define) it.

Once declared, the cursor must be opened for use.

For a cursor with data filled in, retrieve (retrieve) each row as needed.

You must close the cursor when using the end cursor.

II. basic cursor operations

1. create a cursor

DECLARE cur_name cursor for select ****;

Example:

[SQL]

Mysql> delimiter //

Mysql> create procedure test_cur ()

-> Begin

-> Declare mycur cursor

->

-> Select userid, username from newname;

-> End;

-> //

Mysql> delimiter;

2. open the cursor

OPEN cur_name;

MySQL executes queries when processing OPEN statements to store the retrieved data for browsing and scrolling.

3. close the cursor

CLOSE cur_name;

The CLOSE statement releases all internal memory and resources used by the cursor.

After a cursor is closed, it cannot be used if it is not re-opened. However, the declared cursor does not need to be declared again.

If you do not explicitly close the cursor, MySQL will automatically close it when it reaches the END statement.

Example:

[SQL]

Mysql> delimiter //

Mysql> create procedure test_cur ()

-> Begin

-> Declare mycur cursor

->

-> Select userid, username from newname;

-> Open mycur;

-> Close mycur;

-> End;

-> //

Mysql> delimiter;

4. use cursor data

After the cursor is opened, you can use the FETCH statement to access each row of the cursor.

Example:

[SQL]

Mysql> delimiter //

Mysql> create procedure test_cur ()

-> Begin

-> Declare done boolean default 0;

-> Declare cur_userid int;

-> Declare cur_birthday varchar (50 );

->

-> Declare mycur cursor

->

-> Select userid, year (birthday) from newname where birthday is not null;

-> Declare continue handler for sqlstate '000000' set done = 1;

->

-> Open mycur;

->

-> Repeat

-> Fetch mycur into cur_userid, cur_birthday;

-> Select cur_userid, cur_birthday;

-> Until done end repeat;

->

-> Close mycur;

-> End;

-> //

Mysql> delimiter;

Mysql> call test_cur;

+ ------------ + -------------- +

| Cur_userid | cur_birthday |

+ ------------ + -------------- +

| 19 | 1988 |

+ ------------ + -------------- +

1 row in set (0.00 sec)

+ ------------ + -------------- +

| Cur_userid | cur_birthday |

+ ------------ + -------------- +

|42 | 2008 |

+ ------------ + -------------- +

1 row in set (0.01 sec)

BitsCN.com