Simple single storage process-loop a select result set _ MySQL

Simple single storage process-loop a select result set bitsCN.com

Abstract: This article describes how to create, call, and use a stored procedure. I believe that mastering the cursor will be helpful to you. if you have any shortcomings, please advise.

Navigation: 1. create and call a stored procedure
II. Use of cursors
III. example
IV. Supplement

Note:
1. two data tables used:

From_data

To_data

2. example requirement: Insert the select result set of the table from_data into the table to_data cyclically;

Pseudocode: while loop select id, name from_data

Insert into to_data (id, name) value (from_data.id, from_data.name)

End

3. environment: mysql



1. create and call a stored procedure

We create a stored procedure named add_test.

1. check whether add_test exists.
SQL code
Drop procedure if exists add_test;
2. Create


SQL code
Create procedure add_test ()
(
# [In | out | inout] datatype


A int;
B int;
)
Begin
# SQL statement;



End;

3. Call

SQL code
Call add_test (1, 2

);


The above is the basic creation method. Note the following points:
1. "()" after add_test is required during creation and calling.
2. if "in", "out", and "inout" are not explicitly specified for the MySQL Stored Procedure parameters, the default value is "in", and the default value cannot be specified for the parameters.
3. when multiple SQL statements are included, the begin end keyword is required. a semicolon (;) must be added at the end of each statement in begin end.
4. DECLARE the variable in begin end and use the keyword DECLARE, for example:

SQL code
Begin

# Declare a name variable with the varchar type (remember the semicolon)
Name varchar (32 );
End;


II. Use of cursors

1. define a cursor
SQL code
/*
Defines the CURSOR keyword: CURSOR.
Cursor_name,
Cursor cursor_name current pointer record
Is a multi-row result set of a table from_data.
*/
DECLARE cursor_name cursor for select id, name

From from_data;



2. open the cursor

SQL code
# Keywords: OPEN
OPEN cursor_name;

3. obtain the cursor

SQL code
# Declare two variables
DECLARE a int

;
DECLARE B varchar (32)

;

/*
FETCH obtains the record of the current pointer of the cursor and transmits the record to the specified variables a and B.
*/
FETCH cursor_name INTO a, B;

Note: (1. this is very important. we will explain in detail how to use it in the following loop examples,
(2) note that the number of variables must be the same as the number of fields returned by the MySQL cursor and the type. for details, see the red dot in step 2 and 3,
Id of type a and name of type B

4. close the cursor

SQL code
CLOSE cursor_name;


The above is the common usage of cursors. I have already explained the key part in each step, so I will not talk about it. now let's look at the example:

III. example

SQL code
Drop procedure if exists add_test;
# Add_test


Create procedure add_test ()

BEGIN
# Defining variables


DECLARE a int;
DECLARE B VARCHAR (30 );

# This change is dispensable. to give a prompt after the storage function is successfully executed, you will know it when you run it.


DECLARE str VARCHAR (300 );
DECLARE x int;

# This is used to process the cursor reaching the last row


DECLARE bitsCN.com