Shulou(Shulou.com)06/01 Report--

Demand:

There are three tables: Player, Consumption, Consumption_other. User information (playerid, origin, etc.) is recorded in the Player table, while Consumption and Consumption_other record the user's consumption information. Now you need to insert a consumption record into the Consumption and Consumption_ other tables according to the origin field in the Player table. It is stipulated that if origin=0 is in the Player table, the information will be inserted into the Consumption table; if the origin in the Player table is not 0, the information will be inserted into the Consumption_ table.

Methods:

Using MySQL's stored procedures and cursors:

Mysql > DELIMITER / / mysql > CREATE PROCEDURE `add_ Secretption` ()-> BEGIN->-define variables that need to receive cursor data-> DECLARE id int (11);-> DECLARE origin int (11);->-- define traversal data end flag-> DECLARE done BOOLEAN DEFAULT 0 ->-- define cursors-> DECLARE cur CURSOR FOR SELECT-> player.playerid as id,-> player.origin as origin-> FROM player;->-bind the closing flag to cursors-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;->-- Open cursors-> OPEN cur;->-- turn off transaction autocommit-> SET autocommit=0 ->-- start loop-> read_loop:LOOP->-- extract data from cursors-> FETCH cur INTO id,origin;->-- declare when to end the loop-> IF done THEN-> LEAVE read_loop;-> END IF ->-- events in loops-- > IF origin=0-> THEN-> INSERT INTO consumption VALUES (0Med 1525467600);-> ELSE-> INSERT INTO consumption_other VALUES (0Med 1525467600);-> END IF;-> END LOOP;-> commit;->-- close 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 `roomplayer` (- > IN ori int (11),-> OUT total int (11)-> BEGIN-> select count (*) from player where origin=ori into total;-> END//mysql > DELIMITER; mysql > call originplayer (0, @ total); mysql > select @ total;+-+ | @ total | +-+ | 172 | +-+

Parsing:

Delimiter means a separator. Because MySQL defaults to ";", if no delimiter is declared, the compiler will treat the stored procedure as a SQL statement, and the compilation of the stored procedure will report an error. "delimiter /" declares that the delimiter is "/ /". When the code in the stored procedure is finished, declare "delimiter;" again, using ";" as the delimiter.

The stored procedure created may have input, output, input and output parameters. This example has one input parameter "ori" of type int and one output parameter "total" of type int. If you have multiple parameters, split them with ",".

The beginning and end of the process body are identified by BEGIN and END.

MySQL calls the execution of a stored procedure a call, so the statement that executes the stored procedure is CALL. CALL receives the name of the stored procedure and any parameters that need to be passed to it.

2. Parameters:

There are three parameter types of stored procedures, INT, OUT, and INOUT. Form such as: CREATE PROCEDURE ([[IN | OUT | INOUT] parameter name data type.])

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: specify the MySQL variable to receive the value returned after calling the stored procedure.

INOUT input and output parameters: specified when called, and can be changed and returned.

3. Variables:

Define stored procedure local variables:

DECLARE variable_name datatype [default value]

Datatype has the same data type as MySQL, such as int, float, date, varchar (length)

MySQL variable: MySQL variable usually starts with @

Variable assignment:

SET variable_name = value

4. Query stored procedures:

# list all stored procedures: mysql > show procedure status\ G# list stored procedures owned by a library: mysql > select name from mysql.proc where db='project';# query stored procedure details: mysql > show create procedure project.originplayer

5. Delete stored procedure:

Mysql > drop procedure project.originplayer

Cursor related:

1. Create a cursor:

Mysql > DELIMITER / / mysql > CREATE PROCEDURE `getplayerid` ()-> BEGIN-> DECLARE id int (11);-> 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

Parsing:

MySQL cursors are only used in stored procedures

The DECLARE statement is used to define and name cursors, where the cursor is "cur"

OPEN and CLOSE are used to open and close cursors. The query is executed when the OPEN statement is processed, and the retrieved data is stored for browsing. The CLOSE cursor frees up all memory and internal resources occupied by the cursor. If the cursor is not explicitly closed, MySQL automatically closes the cursor when the END statement is reached

After a cursor is opened, you can use the fetch statement to access each row of the cursor and specify where to store the data.

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

CONTINUE HANDLER, this condition occurs when REPEAT cannot continue because there are no more row supply cycles, set done to 1, and REPEAT terminates.

2. The order of DECLARE statements:

There is a specific order in which DECLARE statements are issued. Local variables defined with the declare statement must be before any cursor or handle is defined; the handle must be defined after the cursor.

3. Repeat or cycle:

In addition to the REPEAT used in creating cursors, MySQL also supports loop statements that are used to execute code repeatedly until you exit manually using the level statement. As follows:

…… -> read_loop:LOOP->-- extract data from cursors-> FETCH cur INTO id,origin;->-- declare when to end the loop-> IF done THEN-> LEAVE read_loop;-> END IF ->-- event in a loop-> IF origin=0-> THEN-> INSERT INTO consumption VALUES (0mai 1525467600);-> ELSE-> INSERT INTO consumption_other VALUES (0pc1525467600);-> END IF;-> END LOOP;...

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.