DROP TABLE IF EXISTS my_bundles;
CREATE TABLE my_bundles(seq int auto_increment, total int, bundle text, PRIMARY KEY (seq));
INSERT INTO my_bundles(total,bundle) VALUES (3,"JAVASCRIPT,ACTIONSCRIPT,HTML"),(1,"C++"),(2,"ALGOL,C#"),(7,"C,PHP,JSP,LISP,BASIC,ADA,PYTHON"),(6,"JAVA,RUBY,PASCAL,COBOL,FORTRAN,PERL"),(5,"DELPHI,PROLOG,SMALLTALK,PERL,COLDFUSION");
SELECT * FROM my_bundles;
DROP TABLE IF EXISTS my_items;
CREATE TABLE my_items(seq int auto_increment, language varchar(32), PRIMARY KEY (seq));
DELIMITER $$
DROP PROCEDURE IF EXISTS tokenizer $$
CREATE PROCEDURE tokenizer(
INOUT input_string varchar(1025), OUT token varchar(1025), IN boundary varchar(16)
) READS SQL DATA
BEGIN
SELECT char_length(boundary) INTO @boundry_length;
SET @idx = LOCATE(boundary,input_string);
IF (@idx = 0) THEN
SET token = input_string;
SET input_string = NULL;
ELSE
SET token = SUBSTR(input_string,1,@idx-1);
SET input_string = SUBSTR(input_string,@idx + @boundry_length);
END IF;
END
$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS insert_items $$
CREATE PROCEDURE insert_items(IN my_str varchar(1024)) MODIFIES SQL DATA
BEGIN
SELECT my_str INTO @org_string;
CALL tokenizer(@org_string, @tkn_str, ',');
WHILE (@tkn_str IS NOT NULL) DO
INSERT INTO my_items(language) VALUES (@tkn_str);
CALL tokenizer(@org_string, @tkn_str, ',');
END WHILE;
END
$$
DELIMITER ;
CALL insert_items('a,b,c');
SELECT * FROM my_items;
DELIMITER $$
DROP PROCEDURE IF EXISTS insert_cols_items $$
CREATE PROCEDURE insert_cols_items()
BEGIN
DECLARE ok INT DEFAULT '0';
DECLARE tmp_seq INT DEFAULT '0';
DECLARE tmp_total INT DEFAULT '0';
DECLARE tmp_bundle TEXT DEFAULT '';
DECLARE tot INT DEFAULT '0';
DECLARE cur CURSOR FOR SELECT seq, total, bundle FROM my_bundles;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ok = 1;
OPEN cur;
REPEAT
FETCH cur INTO tmp_seq, tmp_total, tmp_bundle;
IF NOT ok THEN
CALL insert_items(tmp_bundle);
SET tot = tot + 1;
END IF;
UNTIL ok END REPEAT;
CLOSE cur;
IF tot > 0 THEN
SELECT tot;
ELSE
SELECT 0;
END IF;
END
$$
DELIMITER ;
TRUNCATE my_items;
CALL insert_cols_items();
SELECT * FROM my_items; |
텍스트큐브