mysql游标嵌套.docVIP

本文将举例演示游标的嵌套用法，首先建造表结构，如下：Sql代码 drop table if exists `tb_user`; create table tb_user id bigint 22 not null auto_increment primary key, name varchar 20 not NULL, age tinyint 3 , gmt_create date, gmt_modified date ; drop table if exists `tb_user_param`; create table tb_user_param id bigint 22 not null auto_increment primary key, user_id bigint 22 not null, code varchar 100 not null, value varchar 1000 , gmt_create DATE, gmt_modified date ; drop procedure if exists `sp_init_data`; create procedure `sp_init_data` begin declare v_i bigint 22 default 0; declare v_uid bigint 22 ; declare v_nick varchar 20 default lanbo_; declare v_code_1 varchar 20 default address; declare v_code_2 varchar 20 default phone; declare v_code_3 varchar 20 default wangwang_id; declare v_value_1 varchar 20 default HZ.XiHu; declare v_value_2 varchar 20 default 1座机电话号码8; declare v_value_3 varchar 20 default shansun_; while v_i 10 do set v_i v_i + 1; insert into tb_user values null, concat v_nick, v_i , 23, now , now ; select LAST_INSERT_ID into v_uid; insert into tb_user_param values null, v_uid, v_code_1, v_value_1, now , now ; insert into tb_user_param values null, v_uid, v_code_2, concat v_value_2, v_i , now , now ; insert into tb_user_param values null, v_uid, v_code_3, concat v_value_3, v_i , now , now ; end while; commit; end; call sp_init_data ; drop table if exists `tb_key_value`; create table `tb_key_value` uid bigint 22 not null, k varchar 100 , v varchar 100 ; drop table if exists `tb_user`; create table tb_user id bigint 22 not null auto_increment primary key, name varchar 20 not NULL, age tinyint 3 , gmt_create date, gmt_modified date ; drop table if exists `tb_user_param`; create table tb_user_param id bigint 22 not null auto_increment primary key, user_id bigint 22 not null, code varchar 100 not null, value varchar 1000 , gmt_create DATE, gmt_modified date ; drop procedure if exists `sp_init_data`