Using cursor loop to read temporary table in Mysql stored procedure

cursor

A cursor is a method used to view or process data in a result set. Cursors provide the ability to navigate forward or backward through data in a result set, one or more rows at a time.

How to use cursor

Define a cursor: Declare cursor name CURSOR for table; (table can also be the result set of select)
Open cursor: Open cursor name;
Get data from the result set into variables: fetch cursor name into field1, field2;
Execute statement: execute the statement that needs to process data Close cursor: Close cursor name;

BEGIN
  #Declare custom variables declare c_stgId int;
  declare c_stgName varchar(50);
  #Declare the cursor end variable declare done INT DEFAULT 0;

  #Declare the cursor cr and the final processing method after the cursor reads the result set declare cr cursor for select Name,StgId from StgSummary limit 3;
  declare continue handler for not found set done = 1;

  # Open the cursor open cr;

  # loop readLoop: LOOP
    # Get the value in the cursor and assign it to the variable fetch cr into c_stgName,c_stgId;
    # Determine whether the cursor has reached the bottom. If so, exit the cursor. # Note this judgment IF done = 1 THEN
      LEAVE readLoop; 
    END IF; 
    
      SELECT c_stgName,c_stgId;
    
  END LOOP readLoop;
  -- Close the cursor close cr;
END

Note on the variable declaration statement:

• Declare statement is usually used to declare local variables, cursors, conditions or handlers
• Declare statement is only allowed to appear in BEGIN...END statement and must appear in the first line
• The order of Declare is also required. Usually, local variables are declared first, followed by cursors, and then conditions and handlers.
  

Notes on custom variable naming:

The name of the custom variable should not be the same as the name of the cursor result set field. If they are the same, the cursor assignment to the variable will be invalid.

Temporary Tables

The temporary table is only visible in the current connection. When the connection is closed, MySQL will automatically delete the table and release all space. Therefore, temporary tables with the same name can be created in different connections, and operations can be performed on the temporary tables belonging to this connection.
The difference from the normal creation statement is the use of the TEMPORARY keyword

CREATE TEMPORARY TABLE StgSummary(
 Name VARCHAR(50) NOT NULL,
 StgId INT NOT NULL DEFAULT 0
);

Restrictions on using temporary tables

1. In the same query statement, a temporary table can be searched only once. Also, a temporary table cannot be queried multiple times in a stored procedure. But different temporary tables can be used in one query.
2. You cannot use RENAME to rename a temporary table, but you can use ALTER TABLE instead.

ALTER TABLE orig_name RENAME new_name;

• Temporary tables need to be dropped after use
  

DROP TEMPORARY TABLE IF EXISTS StgTempTable;

Using cursor loop to read temporary table data in stored procedure

BEGIN
## Create a temporary table CREATE TEMPORARY TABLE if not exists StgSummary(
 Name VARCHAR(50) NOT NULL,
 StgId INT NOT NULL DEFAULT 0
);
TRUNCATE TABLE StgSummary;

## Add temporary table data INSERT INTO StgSummary(Name,StgId)
select 'temporary data',1

BEGIN

#Custom variable declare c_stgId int;
declare c_stgName varchar(50);
declare done INT DEFAULT 0;

declare cr cursor for select Name,StgId from StgSummary ORDER BY StgId desc LIMIT 3;
declare continue handler for not found set done = 1;

-- Open the cursor open cr;
testLoop:LOOP
	-- Get the result fetch cr into c_stgName,c_stgId;
	IF done = 1 THEN
		LEAVE testLoop; 
	END IF; 
	
  
  SELECT c_stgName,c_stgId;
	
END LOOP testLoop;
-- Close the cursor close cr;

End;
DROP TEMPORARY TABLE IF EXISTS StgSummary;
End;

Initially, create a temporary table and then define the cursor. But the stored procedure cannot be saved anyway. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE ... (Declare statement is only allowed to appear in BEGIN...END statement and must appear on the first line). So in the end you can only add a pair of BEGIN...END to separate them.

Summarize

When I wrote SQL Server stored procedures before, I didn’t pay close attention to this issue. I usually defined variables in the middle of the program, and I took MySQL for granted and wrote them casually, which eventually led to a pitfall. There is little grammatical difference between the two, but it is still quite sudden when you really encounter the difference. But I haven't written SQL statements for a long time, so I'm a little rusty. It’s better to quickly write down the pit and deepen the impression.

The above is the details of using cursor loop to read temporary tables in MySQL stored procedures. For more information about MySQL cursor loop to read temporary tables, please pay attention to other related articles on 123WORDPRESS.COM!