Mysql Stored Procedure example (insert data to different sub-tables), mysql Stored Procedure

Mysql Stored Procedure example (insert data to different sub-tables), mysql Stored Procedure

USE test;
Drop procedure if exists test. generate_records;
Delimiter $
Create procedure test. generate_records (in v_count bigint, in v_phone bigint)
BEGIN
Set @ vCount = v_count;
Set @ vPhone = v_phone;
Set @ vLoopCount = 3;
Set @ I = 1;

While (@ I <= @ vLoopCount)
Do
Set @ vPhone = @ vPhone + 1;

Select max (id) + 1 into @ vPersonId from person;

# Person_0 ~ 9 user personal information table
Set @ vcounta = 1;
While (@ vcounta <= @ vLoopCount * 5)
Do
# Obtain the primary key of the user's personal information table
Set @ vSqlStr1 = concat ('select IF (length (max (id) <> 0, max (id) + 1, 2) ');
Set @ vSqlStr2 = concat ('@ vpersonid ');
Set @ vSqlStr3 = concat ('from person _ ', @ vPersonId % 10 );
Set @ vSqlStr = concat (@ vSqlStr1, @ vSqlStr2, @ vSqlStr3 );

Prepare stmt from @ vSqlStr;
Execute stmt;
Deallocate prepare stmt;

Set @ vtype = FLOOR (1 + RAND () * 3 );

If @ vtype = 1 then
Set @ vother = concat ('old users', @ I );
Elseif @ vtype = 2 then
Set @ vother = concat ('new user', @ I );
Else
Set @ vother = concat ('illegal users', @ I );
End if;

# User personal information table pk = personId user ID
Set @ vSqlStr1 = concat ('insert into 'person _ ', @ vPersonId % 10 ,''');
Set @ vSqlStr2 = concat ('('personid', 'phone', 'personname', 'age', 'sex', 'intertest', 'income', 'other ', 'created ', 'updated', 'status ')');
Set @ vSqlStr3 = concat ("VALUES (", @ vPersonId, ",", @ vPhone, ",", @ vPersonName, ",", FLOOR (18 + RAND () * 68), ",", FLOOR (1 + RAND () * 1), ",", FLOOR (0 + RAND () * 10 ),",
", FLOOR (8000 + RAND () * 10000),", ", @ vother,", ", localtime (),", ", localtime (),",", FLOOR (1 + RAND () * 2 ),")");
Set @ vSqlStr = concat (@ vSqlStr1, @ vSqlStr2, @ vSqlStr3 );

Prepare stmt from @ vSqlStr;
Execute stmt;
Deallocate prepare stmt;

Set @ vcounta = @ vcounta + 1;

End while;

COMMIT;


Set @ I = @ I + 1;

End while;

Select @ vPersonId, @ vPhone;


End $
Delimiter;

CALL test. generate_records (10 ,10200000001 );