操作环境:有表game_list,字段:uid,score1,score2,seat_id,last_update;
传入参数为i_player_detail ,传入的值为多个用户的id、之前分数、之后分数、座位号,每个用户的数据用分号(;)隔开;
操作目的:将各个用户对应的属性插入到目标表对应的字段中,last_update为数据更新日期;
delimiter
create procedure `game_d`(in i_player_detail varchar(500))
sql security invoker
begin
DROP TABLE IF EXISTS `temp_list`;
CREATE TEMPORARY TABLE `temp_list`(
`uid` INT(10) UNSIGNED NOT NULL,
`score1` INT(10) UNSIGNED NOT NULL,
`score2` INT(10) UNSIGNED NOT NULL,
`seat_id` TINYINT(3) UNSIGNED NOT NULL
);
-- 直接将传入的字符串拼接sql语句,插入到临时表;
SET m_player_detail = REPLACE(i_player_detail,';','),(');
SET @sql_str = CONCAT ('INSERT INTO `temp_list` VALUES (',m_player_detail,')');
PREPARE stm1 FROM @sql_str;
EXECUTE stm1;
DEALLOCATE PREPARE stm1;
-- 再从临时表中抽取数据,插入到目标表game_list
INSERT INTO `game_list`(`uid`,`score1`,`score2`,`seat_id`, `last_update`)
SELECT `uid`, `score1`, `score2`, `seat_id`, m_now FROM `temp_list`;
end
传入参数为i_player_detail ,传入的值为多个用户的id、之前分数、之后分数、座位号,每个用户的数据用分号(;)隔开;
操作目的:将各个用户对应的属性插入到目标表对应的字段中,last_update为数据更新日期;
delimiter
use `mysql`
drop procedure if exists `game_c`
create procedure `game_d`(in i_player_detail varchar(500))
sql security invoker
begin
DROP TABLE IF EXISTS `temp_list`;
CREATE TEMPORARY TABLE `temp_list`(
`uid` INT(10) UNSIGNED NOT NULL,
`score1` INT(10) UNSIGNED NOT NULL,
`score2` INT(10) UNSIGNED NOT NULL,
`seat_id` TINYINT(3) UNSIGNED NOT NULL
);
-- 直接将传入的字符串拼接sql语句,插入到临时表;
SET m_player_detail = REPLACE(i_player_detail,';','),(');
SET @sql_str = CONCAT ('INSERT INTO `temp_list` VALUES (',m_player_detail,')');
PREPARE stm1 FROM @sql_str;
EXECUTE stm1;
DEALLOCATE PREPARE stm1;
-- 再从临时表中抽取数据,插入到目标表game_list
INSERT INTO `game_list`(`uid`,`score1`,`score2`,`seat_id`, `last_update`)
SELECT `uid`, `score1`, `score2`, `seat_id`, m_now FROM `temp_list`;
end