MySQL で一括登録に便利なProcedure (必要項目１つ)

# 作成SQL

```sql
DROP PROCEDURE IF EXISTS exec1;
DELIMITER ;;

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 ;
```

# サンプル用

```sql

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|