MYSQL實(shí)例
-- 使用存儲(chǔ)過(guò)程
delimiter $$
use `log_pdk`$$
drop procedure if exists `game_c`$$
create procedure `game_c` (in i_player_detail varchar(500))
SQL SECURITY INVOKER
BEGIN
DROP TABLE IF EXISTS `temp_list`;
--創(chuàng)建臨時(shí)表,將截取的數(shù)據(jù)先插入到臨時(shí)表
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
);
-- declare str varchar(500);-- 用來(lái)拼接sql動(dòng)態(tài)語(yǔ)句
declare m_detail varchar(500);
declare m_num tinyint;
-- 當(dāng)傳入的用戶信息字符串中含有分號(hào)';',進(jìn)行截取
set m_num = position(';' in str) -- 不存在分號(hào)的時(shí)候,返回0
while m_num >= 1 do
begin
set @str = 'insert into temp_list values (' + substring(m_detail,1,m_num-1)+')' -- 截取第一個(gè)用戶的信息(第一個(gè)分號(hào)前面的字符),插入到臨時(shí)表
prepare statement1 from @str;
execute statement1;
deallocate prepare statement1;
set m_detail = substring(m_detail,m_num+1); -- 定義除去第一個(gè)用戶和分號(hào)那部門(mén)的字符串
set set m_num = position(';' in str)；
end while;
-- 從臨時(shí)表抽出所有字段,添加時(shí)間字段,插入到表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 ;