Fix the last row of the MySQL cursor

When using the MySQL cursor, I found that the last row was repeatedly read. Later I found that I should put the read data in the next loop to avoid repeated Code as follows:

When using the MySQL cursor, I found that the last row was repeatedly read. Later I found that I should put the read data in the next loop to avoid repeated Code as follows:

Homepage → Database Technology

Background:

Read News

Fix the last row of the MySQL cursor

[Date: 2011-07-20] Source: Linux Author: babaoqi [Font:]

When MySQL cursor is used, the last row is read repeatedly.

Later, we found that we should put the read data in the next loop to avoid duplication.

The Code is as follows:
DELIMITER $

USE 'test' $

Drop procedure if exists 'SP _ getalltablename' $

Create procedure 'SP _ getalltablename' (OUT strAllTableNames VARCHAR (10000 ))
BEGIN
DECLARE bEnd boolean default false;
DECLARE tbName VARCHAR (255 );

# Declare a cursor
DECLARE curTableNames cursor for select TABLE_NAME FROM information_schema.tables AS t WHERE t. table_schema = "test ";

# DECLARE CONTINUE HANDLER
Declare continue handler for sqlstate '000000' SET bEnd = TRUE;

SET strAllTableNames = "";

# Open a cursor
OPEN curTableNames;

# Retrieving data from all rows
# Obtain the first line of content
FETCH curTableNames INTO tbName;

# Loop start
REPEAT
SET strAllTableNames = CONCAT (strAllTableNames, tbName ,";");
# Obtain the content of the next row
FETCH curTableNames INTO tbName;
# Loop end
UNTIL bEnd end repeat;

# Closing a cursor
CLOSE curTableNames;

# Processed
SET strAllTableNames = CONCAT ("All table names:", strAllTableNames );

END $

DELIMITER;

CALL test. sp_getAllTableName (@ allNames );

SELECT @ allNames;

0

Set CentOS to automatically start Oracle at startup

Oracle Shared Pool optimization ideas

MySQL tutorial

Image Information

View All comments in this article (0)

Comments

Latest Information

Popular this week

Introduction to Linux community-advertisement service-website map-help Info-contact us
The articles published on this site (LinuxIDC) do not mean that they agree with the statement or description. They only provide more information and do not constitute any suggestions.


Copyright©2006-2011 Linux community All rights reserved Zhejiang ICP backup No. 06018118

,