存储过程之批量插入

存储过程之批量插入:

获得所有以“,“分割的字符串的个数，函数如下 :

DELIMITER $$

USE ccgctest$$

DROP FUNCTION IF EXISTS split_string$$

CREATE DEFINER=root@% FUNCTION split_string(
f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(128) CHARSET utf8
BEGIN
DECLARE result VARCHAR(128) DEFAULT ‘’;
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END$$

DELIMITER ;

按分割取出字符串:

DELIMITER $$

USE ccgctest$$

DROP FUNCTION IF EXISTS string_total$$

CREATE DEFINER=root@% FUNCTION string_total(
f_string VARCHAR(1000),f_delimiter VARCHAR(5)
) RETURNS INT(11)
BEGIN
RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,’’)));
END$$

DELIMITER ;

while循环:

DELIMITER $$

USE ccgctest$$

DROP PROCEDURE IF EXISTS pro1$$

CREATE DEFINER=root@localhost PROCEDURE pro1(
IN username_string VARCHAR(1000),IN age_string VARCHAR(1000),IN f_delimiter VARCHAR(5)
)
BEGIN
DECLARE cnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE num INT;

SET num = string_total(username_string,f_delimiter);
DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp (num INT NOT NULL);
WHILE i < num
DO
SET i = i + 1;
INSERT INTO fortest(username,age) VALUES ((split_string(username_string,f_delimiter,i)),(split_string(age_string,f_delimiter,i)));
END WHILE;
END$$

DELIMITER ;

loop循环:

DELIMITER $$

USE ccgctest$$

DROP PROCEDURE IF EXISTS pro2$$

CREATE DEFINER=root@localhost PROCEDURE pro2(IN username_string VARCHAR(1000),IN age_string VARCHAR(1000),IN f_delimiter VARCHAR(5))
BEGIN
DECLARE cnt INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
DECLARE j INT;
SET j =string_total(username_string,f_delimiter);
loop_label: LOOP
INSERT INTO fortest(username,age) VALUES ((split_string(username_string,f_delimiter,i)),(split_string(age_string,f_delimiter,i)));
SET i=i+1;
IF i>j
THEN
LEAVE loop_label;
END IF;
END LOOP;
END$$

DELIMITER ;

while循环:
CALL pro1(“欧阳锋,周伯通,黄药师”,”21,22,23”,”,”);
loop循环:
CALL pro2(“风清扬,无招,逍遥子”,”21,22,23”,”,”).