Code instance created by a stored procedure with a cursor in a MySQL database

This article mainly introducesMySQL databaseOfStored Procedure with CursorFor example, let's take a look at how to create a stored procedure with a cursor.

The code example is as follows:

 
 

  
  
DELIMITER $$  
  
  
 
  
  
DROP PROCEDURE IF EXISTS `jf`.`insert2`$$  
  
  
 
  
  
CREATE PROCEDURE `insert2`(in yhbh varchar(50),out bjbh varchar(50))  
  
  
 
  
  
BEGIN  
  
  
 
  
  
declare tempbjbh varchar(50) default 0;  
  
  
 
  
  
declare done int default 0;  
  
  
 
  
  
declare cur1 CURSOR FOR select ffgl.c_bjbh from ffgl,dj_info where ffgl.c_yhbh='U01010101' and ffgl.n_fflx=dj_info.n_fflx order by ffgl.c_bjbh asc;  
  
  
 
  
  
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;   
  
  
 
  
  
open cur1;  
  
  
 
  
  
Repeat  
  
  
 
  
  
fetch cur1 into tempbjbh;  
  
  
 
  
  
if not done then   
  
  
 
  
  
set tempbjbh=concat(tempbjbh,"/");  
  
  
 
  
  
set bjbh=concat(bjbh,tempbjbh);  
  
  
 
  
  
end if;  
  
  
 
  
  
until done end repeat;  
  
  
 
  
  
close cur1;  
  
  
 
  
  
select bjbh;   
  
  
 
  
  
END$$  
  
  
 
  
  
DELIMITER ; 
 
 

Note:The cursor definition statement must be placed behind the definition of other variables. Otherwise, an error occurs. For example, if the positions of rows 8th and 9th are changed, an error is returned.

The code example of the stored procedure with a cursor in the MySQL database has been introduced. If you want to learn more about the MySQL database, please read the article: http://database.51cto.com/mysql/. your harvest is definitely for our work!