MySQL SP practice cursors and precompilation

Create procedurejack_count_cur_dual ()BEGINDECLARETb_nameVARCHAR( -);DECLAREDoneint DEFAULT 0;DECLAREMycurCURSOR  for SELECTtable_name fromtt_countrows;DECLARE CONTINUEHANDLER for  notFOUNDSetDone= 1;DROP TABLE IF EXISTStt_countrows; #删除, create a temporary tableCREATE Temporary TABLEtt_countrows (table_namevarchar( -), Table_countint);INSERT  intott_countrows (table_name) #向临时表中插入表名数据Select 'Autidor'UNIONSELECT 'Spprogram.books'UNIONSELECT 'Information_schema.files';;OPENMycur;myloop:loopFETCHMycur intotb_name; #使用游标读取数据赋值给tb_name, get the table nameifDone= 1  Thenleave Myloop;End if;Set @v_sql =CONCAT ('Select COUNT (*) into @v_count from', tb_name); #预编译查询表中数据数量的语句PREPAREstmt from @v_sql;EXECUTEstmt;UPDATETt_countrowsSETTable_count= @v_count#修改临时表中table_count字段, the value is the count value in the precompiled statementWHEREtable_name=Tb_name;deallocate PREPAREstmt;ENDLOOP Myloop;CLOSEmycur;SELECT *  fromtt_countrows; #输出临时表结果并删除临时表DROP TABLEtt_countrows;END

Problem one: First, the variables in MySQL are divided into four categories, namely:

User variables: Starting with "@", in the form of "@ Variable name"

The user variable is bound to the MySQL client, and the set variable is only valid for the client used by the current user

Global variables: When defined, occurs in the following two forms, set global variable name or SET @ @global. The variable name takes effect for all clients. You can set global variables only if you have super permissions

Session variables: Valid only for connected clients.

Local variables: The scope of action is between the begin and end statement blocks. The variable that is set in the statement block
The Declare statement is specifically used to define local variables. The SET statement is to set variables of different types, including session variables and global variables.

The prepare statement is defined in the stored procedure and then execute, which cannot receive local variables defined in the stored procedure, but only the user variable with @.

This is the design of the MySQL decision. PREPARE, Exceute can be said to be executed on a different stack, not part of the current stored procedure.

Problem two: The stored procedure needs to obtain the database table name and the data quantity in the table, the database table name needs to look for from the tables under the INFORMATION_SCHEMA database, and the data quantity in the table needs to use COUNT () to the corresponding table statistic. Referring to relevant data, using the cursor to realize TableName, using prepare to realize quantity statistics. The results are then displayed by creating a temporary table.

MySQL SP practice cursors and precompilation