DELIMITER $
DROP PROCEDURE IF EXISTS exec1$
CREATE PROCEDURE exec1(
IN SQL_VARCHAR TEXT,
IN LOOP_COUNT_MAX INT,
IN VAL TEXT
)
BEGIN
DECLARE LOOP_COUNT INT DEFAULT 1;
SET @STMT = SQL_VARCHAR;
PREPARE STMT FROM @STMT;
WHILE LOOP_COUNT < LOOP_COUNT_MAX
DO
SET @P1 = SUBSTRING_INDEX(SUBSTRING_INDEX(VAL, ':', LOOP_COUNT), ':', -1);
EXECUTE STMT USING @P1;
SET LOOP_COUNT = LOOP_COUNT + 1;
END WHILE;
END;
$
DELIMITER ;
サンプル用
DROP TABLE IF EXISTS `prefs`;
CREATE TABLE `prefs` (
`id` int(2) ZEROFILL PRIMARY KEY AUTO_INCREMENT ,
`name` varchar(250) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT='都道府県テーブル';
CALL exec1('INSERT INTO prefs (name) values (?)', 48, '北海道:青森:岩手:宮城:秋田:山形:福島:東京:神奈川:埼玉:千葉:茨城:栃木:群馬:山梨:新潟:長野:富山:石川:福井:愛知:岐阜:静岡:三重:大阪:兵庫:京都:滋賀:奈良:和歌山:鳥取:島根:岡山:広島:山口:四国:徳島:香川:愛媛:高知:福岡:佐賀:長崎:熊本:大分:宮崎:鹿児島:沖縄:未指定');
SELECT * FROM `prefs`;
id |
name |
created |
modified |
01 |
北海道 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
02 |
青森 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
03 |
岩手 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
04 |
宮城 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
05 |
秋田 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
06 |
山形 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
07 |
福島 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
08 |
東京 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
09 |
神奈川 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
10 |
埼玉 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
11 |
千葉 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
12 |
茨城 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
13 |
栃木 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
14 |
群馬 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
15 |
山梨 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
16 |
新潟 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
17 |
長野 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
18 |
富山 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
19 |
石川 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
20 |
福井 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
21 |
愛知 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
22 |
岐阜 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
23 |
静岡 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
24 |
三重 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
25 |
大阪 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
26 |
兵庫 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
27 |
京都 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
28 |
滋賀 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
29 |
奈良 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
30 |
和歌山 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
31 |
鳥取 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
32 |
島根 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
33 |
岡山 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
34 |
広島 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
35 |
山口 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
36 |
徳島 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
37 |
香川 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
38 |
愛媛 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
39 |
高知 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
40 |
福岡 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
41 |
佐賀 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
42 |
長崎 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
43 |
熊本 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
44 |
大分 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
45 |
宮崎 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
46 |
鹿児島 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |
47 |
沖縄 |
2014-07-18 16:43:39 |
2014-07-18 16:43:39 |