[DBMS] MySQL 프로시져(PROCEDURE) 활용 샘플 소스 코드

아래 소개된 SQL 소스 코드는 3가지의 MySQL 프로시져(PROCEDURE)를 이용해서 테이블 중에 개별 레코드의 특정 컬럼의 문자열을 구분자로 분리해서 별도의 테이블에 각각 하나의 레코드로 기록할 수 있도록 합니다. 

출처 : http://phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=64862&page=2

---

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;

---