Shulou(Shulou.com)06/01 Report--

This article illustrates MySQL cursor concepts and usage. Share it for your reference, as follows:

1. Cursor concept

A sql corresponds to N resources. The interface for fetching resources is the cursor. Along the cursor, one line can be fetched at a time. If you have developed Android, you should know that there is an Api that is Cursor, which is also used to read SQLite database, which is a bit similar to this one.

2. Steps to use cursors

(1) Declaration

Declare with Declare

declare cursor name cursor for select_statement

(2) Open cursor

Use open to open

open cursor name

(3) Value from cursor

Use fetch to take values

fetch cursor name into var1,var2[,...] --Assign a line to multiple variables

(4) Close the cursor

Close cursors with close

close Cursor name

Create a simple cursor

Demand: Read the first row of data from the commodity table

Commodity table (goods) data:

Note: I have changed the MySQL end identifier to $here, if you want to know how to set it to $, please refer to the previous article: MySQL triggers.

Definition:

create procedure p12()begin /* Defines three variables used to store commodity id, commodity name, commodity inventory */ declare row_gid int ; declare row_name varchar(20); declare row_num int; declare getgoods cursor for select gid,name,num from goods; --define cursors open getgoods; --open cursors fetch getgoods into row_gid,row_name,row_num;--select row_name, row_num from cursors; --display operations close getgoods; --close cursors end$

Output:

4. Multiple value operations

create procedure p13()begin declare row_gid int ; declare row_name varchar(20); declare row_num int; declare getgoods cursor for select gid,name,num from goods; open getgoods; fetch getgoods into row_gid,row_name,row_num; select row_name,row_num; fetch getgoods into row_gid,row_name,row_num; select row_name,row_num; fetch getgoods into row_gid,row_name,row_num; select row_name,row_num; fetch getgoods into row_gid,row_name,row_num; select row_name,row_num; close getgoods;end$

Output:

Note: When the cursor reads to the end, an error will occur if the value operation is continued.

5. All data in the cursor circular table

(1) Use counters to cycle

create procedure p14()begin declare cnt int default 0; declare i int default 0; declare row_gid int ; declare row_name varchar(20); declare row_num int; declare getgoods cursor for select gid,name,num from goods; select count(*) into cnt from goods; open getgoods; repeat fetch getgoods into row_gid,row_name,row_num; select row_name,row_num; set i:= i+1; until i >= cnt end repeat; close getgoods;end$

Output:

(2) Use of out-of-bounds flags to control loops

In mysql cursor, you can declare continue handler to manipulate 1 out-of-bounds flag

Grammar:

declare continue handler for NOT FOUND statement;

Use:

create procedure p15()begin declare row_gid int ; declare row_name varchar(20); declare row_num int; declare have int default 1; declare getgoods cursor for select gid,name,num from goods; declare continue handler for NOT FOUND set have:= 0; open getgoods; repeat fetch getgoods into row_gid,row_name,row_num; select row_name,row_num; until have = 0 end repeat; close getgoods;end$

Output:

Note: There is an error here, 4 rows of data are output here, and there are only 3 rows of data in the table, and there is also a warning, which will say how to solve this problem later.

Program execution logic:

Loop cursor->fetch 3rd data-> Display->fetch 4th data-> No data-> Set have=0 operation-> Execute continue Handler-> Program does not exit, execute display operation-> Display 3rd data or not

Difference between continue and exit

continue: If no data is returned, the program continues and the variable IS_FOUND is set to 0, which occurs when select XX into XXX from tablename.

exit: If no data is returned, exit the program and set the variable IS_FOUND to 0, which occurs when select XX into XXX from tablename.

Use exit instead of continue:

Use exit will not appear in the above situation, the program execution logic:

Loop cursor->fetch to third data-> display-> fourth fetch operation-> no data-> set have=0 operation-> program exits directly

So there's no indication of the fourth number.

create procedure p16()begin declare row_gid int ; declare row_name varchar(20); declare row_num int; declare have int default 1; declare getgoods cursor for select gid,name,num from goods; declare exit handler for NOT FOUND set have:= 0; open getgoods; repeat fetch getgoods into row_gid,row_name,row_num; select row_name,row_num; until have = 0 end repeat; close getgoods;end$

Output:

7, the correct cursor cycle

In some special cases, we can read empty data, or sql statement has an error, we can not avoid this situation, so we need to use the cursor loop operation correctly.

First, you should create a cursor, and then open the cursor, you should first manually fetch a row of data, and then through the loop, in the loop first do processing content, then fetch operation. In this way, if the data is not obtained during the manual data acquisition, it will execute have = 0, if it is a repeat loop, then enter the repeat loop, output null data first, and finally obtain it, so that when it runs until it will exit the loop; if it is a while loop, it will not enter the while loop at all, and there will be no 1 line output.

1) Repeat cycle:

create procedure p17()begin declare row_gid int; declare row_name varchar(20); declare row_num int; declare have int default 1; declare getgoods cursor for select gid,name,num from goods where 0; declare continue handler for NOT FOUND set have:= 0; open getgoods; fetch getgoods into row_gid,row_name,row_num; repeat select row_name,row_num; fetch getgoods into row_gid,row_name,row_num; until have = 0 end repeat; close getgoods;end$

Output:

(2) While the cycle:

create procedure p18()begin declare row_gid int; declare row_name varchar(20); declare row_num int; declare have int default 1; declare getgoods cursor for select gid,name,num from goods where 0; declare continue handler for NOT FOUND set have:= 0; open getgoods; fetch getgoods into row_gid,row_name,row_num; while have = 1 do select row_name,row_num; fetch getgoods into row_gid,row_name,row_num; end while; close getgoods;end$

Output:

More about MySQL related content interested readers can view this site topic: MySQL query skills, MySQL transaction operation skills, MySQL storage process skills, MySQL database lock related skills, MySQL common function summary

I hope this article is helpful for MySQL database.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.