Operating Environment: There are table Game_list, fields: uid,score1,score2,seat_id,last_update;
The incoming parameter is I_player_detail, the passed-in value is the ID of multiple users, the previous score, after the score, Seat numbers, each user's data separated by semicolons (;);
Action Purpose: To insert the corresponding properties of each user into the corresponding field in the target table, last_update the date of the data update,
I_player_detail the parameter, which holds the information of multiple users. A group of data for each user is separated by semicolons, with multiple messages per user, such as
user id,score,desk,seat;
User id,score,desk,seat ... "
--Using Stored procedures delimiter $$ use ' LOG_PDK ' $$ drop procedure if exists ' Game_c ' $$ to create PROCEDURE ' Game_c ' (in I_player_deta
Il varchar ()) SQL security invoker BEGIN DROP TABLE IF EXISTS ' temp_list '; --Creates a temporary table, inserts the intercepted data into the temporary table create temporary table ' temp_list ' (' uid ' int () UNSIGNED not NULL, ' score1 ' int (a) UNSIGNED not N
ull, ' Score2 ' INT (a) UNSIGNED not null, ' seat_id ' TINYINT (3) UNSIGNED not NULL);
--Declare str varchar (500);--Used to splice SQL dynamic statements declare m_detail varchar (500);
declare m_num tinyint; --When the incoming user information string contains a semicolon '; ', to intercept the set m_num = Position (' in str ')--when there is no semicolon, return 0 while M_num >= 1 does begin set @str = ' Inse RT into Temp_list values (' + substring (m_detail,1,m_num-1) + ') '--intercepts the first user's information (the character preceding the first semicolon) and inserts it into the temporary table prepare statement1 from @
Str
Execute statement1;
deallocate prepare statement1; Set m_detail = substring (m_detail,m_num+1);
--Defines the string set set M_num = Position ('; ' in str) that removes the first user and semicolon part; --Extract all fields from temporary tables, add Time fields, insert into table game_list INSERT INTO ' game_list ' (' uid ', ' score1 ', ' SCOre2 ', ' seat_id ', ' last_update ') SELECT ' uid ', ' score1 ', ' score2 ', ' seat_id ', current_date () from ' temp_list '; end$$ delimiter;