Remember the use of MySQL stored procedures and cursors

Demand:

There are three tables: Player, Consumption, consumption_other. The player table records user information (fields such as playerID, origin, and so on), and consumption and Consumption_other record the user's consumption information. You now need to insert a consumer record into the consumption and Consumption_other tables, based on the Origin field in the Player table. Rules: Origin=0 in the player table, inserting information into the consumption table, and inserting information into the Consumption_other table in the Player table, where Origin is not 0.

Method:

Using the MySQL stored procedure and cursor implementation:

mysql> delimiter //mysql> create procedure  ' Add_consumption ' ()      -> BEGIN    ->   --  define variables that need to receive cursor data      ->   declare id int (one);    ->    declare origin int (one);    ->   --  define traverse data End flag      ->   DECLARE done BOOLEAN DEFAULT 0;     ->   --  Defining Cursors     ->   DECLARE cur  Cursor for select    ->     player.playerid as  id,    ->     player.origin as origin     ->   from player;    ->   --   Bind the end flag to the cursor   &Nbsp; ->   declare continue handler for not found set  done=1;    ->   --  Open Cursors     ->    OPEN cur;    ->     --  Close Transaction Auto-commit      ->     SET autocommit=0;    ->      --  Start Cycle     ->     read_loop:loop     ->       --  extracting data from a cursor      ->       FETCH cur INTO id,origin;     ->       --  declaring when to end a loop     ->        IF done THEN    ->          leave read_loop;    ->       end if;     ->       --  events at the time of the cycle     ->        IF origin=0    ->        THEN    ->          INSERT INTO consumption VALUES  (0,1525467600);    ->        ELSE    ->          insert into consumption_other values (0,1525467600);     - >       END IF;    ->      END LOOP;    ->     commit;     ->     --  Closing Cursors     ->   CLOSE cur;    ->  end    -> //mysql> delimiter ;mysql> call add_ Consumption ();

Stored Procedure Related:

1. Create a stored procedure:

Format:

CREATE PROCEDURE Procedure name ([parameter]) process body

Example:

mysql> DELIMITER//mysql> CREATE PROCEDURE ' Originplayer ' (--in Ori int (one), out of total int (1    1), BEGIN, select COUNT (*) from player where Origin=ori to total; end//mysql> DELIMITER;mysql> Call Originplayer (0, @total);mysql> select @total; +--------+|    @total |+--------+| 172 |+--------+

Analytical:

• Delimiter is the meaning of the separator. Because MySQL defaults to ";" As a delimiter, if you do not declare the delimiter, then the compiler will treat the stored procedure as an SQL statement, the process of compiling the stored procedure will be error-free. The "delimiter//" declaration separator is "//". After the code in the stored procedure finishes, declare "delimiter ;" again, and then ";" As a separator.

• The stored procedure you create may have input, output, and input and output parameters. This example has an input parameter "ori", the type is int, an output parameter "total", and the type is int. If there are multiple parameters, use "," split open.

• The beginning and end of the process body is identified using begin and end.

• MySQL says that the execution of the stored procedure is called, so the statement executing the stored procedure is call. Call receives the name of the stored procedure and any arguments that need to be passed to it.

2. Parameters:

There are three types of parameters for stored procedures, INT, out, INOUT. Form: CREATE PROCEDURE ([[In | Out | INOUT] Parameter name Data class ...])

• In input parameter: The value of this parameter must be specified when the stored procedure is called. If the value of the parameter is modified in the stored procedure, the value of the parameter is still the value before the modification.

• Out OUTPUT parameter: Specifies a MySQL variable that receives the value returned after the stored procedure is called.

• InOut input and OUTPUT parameters: specified on invocation, and can be changed and returned.

3. Variables:

• To define a stored procedure local variable:

DECLARE variable_name datatype [default value];

DataType is the same as MySQL data type, such as: int, float, date, varchar (length);

• MySQL variable: MySQL variable usually begins with @;

• Variable assignment:

SET variable_name = value

4. Query the stored procedure:

# list all stored procedures:mysql> Show procedure status\g# lists stored procedures owned by a library:mysql> select name from Mysql.proc where db= ' project '; # query Save Detailed information about the stored procedure:mysql> Show CREATE PROCEDURE Project.originplayer;

5. Delete stored procedures:

Mysql> drop procedure Project.originplayer;

Cursor-Related:

1. Create a cursor:

mysql> delimiter //mysql> create procedure  ' Getplayerid ' ()      -> begin    ->   declare id int (one);     ->   DECLARE done BOOLEAN DEFAULT 0;     ->   declare cur cursor for select    ->      playerid    ->   FROM player;     ->   declare continue handler for not found set  done=1;    ->   OPEN cur;    ->      repeat    ->       fetch  cur into id;    ->     util done end  repeat;    ->   close cur;    -> end//mysql>  delimiter ;

Analytical:

• MySQL cursors are used only in stored procedures;

• The Declare statement is used to define and name the cursor, where the cursor is "cur";

• Open and close are used to turn cursors on and off. Executes a query when the open statement is processed, storing the retrieved data for browsing. The close cursor frees all memory and internal resources that the cursor occupies. If the cursor is not explicitly closed, MySQL will automatically close the cursor when the end statement is reached;

• After a cursor is opened, each row of the cursor can be accessed using a FETCH statement, and you can specify where to store the data.

• In the example above, the FETCH statement is within REPEAT, so it executes repeatedly until done is true (by UTIL done END REPEAT; specified );

• CONTINUE HANDLER, this condition occurs when repeat is unable to continue because there is no more rows for the loop, and done is set to 1, at which time repeat terminates.

2. The order of DECLARE statements:

The publication of the Declare statement has a specific order. A local variable defined with the Declare statement must precede the definition of any cursor or handle, and the handle must be defined after the cursor.

3. Repeat or cycle:

In addition to the repeat used in the creation of cursors in 1, MySQL also supports loop statements, which are used to repeatedly execute code until manually exiting with the leave statement. As follows:

    ......    ->     read_loop:LOOP     ->       --  extracting data from a cursor     ->        fetch cur into id,origin;    - >       --  declaring when to end a loop     ->        IF done THEN    ->          LEAVE read_loop;    ->        END IF;    ->       --  events at the time of the cycle      ->       IF origin=0    ->        THEN    ->          insert INTO consumption VALUES  (0,1525467600);    ->        ELSE    ->          insert into consumption_other values (0,1525467600);    ->        end if;    ->     end  loop;    

Remember the use of MySQL stored procedures and cursors