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

This article mainly shows you how to use mysql cursors, the content is easy to understand, I hope you can learn, after learning there will be gains, the following let the editor to take a look at it.

What is a cursor:

If you have seen the mysql function before, you will find that statements that return multiple rows of results cannot be used. But if you do want to use it, you need to use cursors, which can help you select a result (so you can return a single result). In addition, cursors can also be used to easily retrieve the results of one or more rows forward or backward in the retrieved rows. Cursors can traverse the returned multiple rows of results. Add: cursors in Mysql are only applicable to stored procedures and functions. Create cursors: syntax: 1. Define cursor: declare cursor name cursor for select statement; 2. Open cursor: open cursor name; get result: fetch cursor name into variable name [, variable name]; close cursor: close cursor name; create procedure p1 () begin declare id int; declare name varchar (15);-- declare cursor declare mc cursor for select * from class;-- Open cursor open mc;-- get result fetch mc into id,name;-- here to display the result select id,name -- close the cursor close mc; end;create procedure p2 () begin declare id int; declare name varchar (15);-- declare the cursor declare mc cursor for select * from class;-- Open the cursor open mc;-- get the result loop-- loop, transfer all the contents of the table to the fetch mc into id,name; in the class2-- here to display the acquisition result insert into class2 values (id,name) -- close cursors end loop; close mc; end; use cursors: cursors get a row of results each time a cursor fetch. You can use variables to get the value create procedure p2 () begin declare id int; declare name varchar (15) of each column to which fetch goes;-- declare cursors declare mc cursor for select * from class;-- Open cursors open mc. -- get the result loop-- loop, transfer all the contents of the table to the class2 fetch mc into id,name;-- here to display the acquisition result insert into class2 values (id,name);-- close the cursor end loop; close mc; end

The above code will have an error. If it keeps looping, it will always reach the end of the table. At the end, it will not be able to continue fetch. Generally speaking, it is necessary to avoid reporting an error. There will be a mysql definition before the end.

Create procedure p3 () begin declare id int; declare name varchar (15); declare flag int default 0;-- declare the cursor declare mc cursor for select * from class; declare continue handler for not found set flag=1;-- Open the cursor open mc;-- get the result l2:loop fetch mc into id,name; if flag=1 then-- trigger handler continue leave L2; end if when the fetch is not available -- here is to display the obtained result insert into class2 values (id,name);-- close the cursor end loop; close mc; end;call p3 ();-- No error select * from class2; above is about how to use the mysql cursor. If you have learned knowledge or skills, you can share it for more people to see.

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.