DELIMITER '$$'
DROP VIEW IF EXISTS TempView$$
DROP PROCEDURE IF EXISTS procDoitCombine$$
CREATE PROCEDURE procDoitCombine( )
BEGIN
        DECLARE mTableName VARCHAR(128);
        DECLARE mColumnName VARCHAR(128);
        DECLARE mDone INT DEFAULT 0;

        DECLARE mTblApID INT(6) UNSIGNED;
        DECLARE mTblData VARCHAR(512);
   
        #######################################################################
        # MotherTable로부터 관련 Table/cuolumn항목을 Cursor로 Open.
        # 해당 Table에는 각 Sub 정보 존재.
        #######################################################################
        DECLARE mo_Cursor CURSOR FOR
        SELECT table_name, column_name  FROM MotherTable;
   
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET mDone=1; 
        OPEN mo_Cursor;

        REPEAT

                FETCH NEXT FROM mo_Cursor INTO mTableName, mColumnName;
  
                IF NOT mDone THEN
      
                        BLOCK1: BEGIN # 이중 커서를 위한 블럭 지정 (없으면 실행 안됨)     
     
                        DECLARE mTblDone INT DEFAULT 0;
                        DECLARE Nested_Cursor CURSOR FOR SELECT * FROM TempView;   
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET mTblDone=1;

                        SET mTblApID = 0;
                        SET mTblData = '';   
    
                        # 아래와 같이 하면 FROM mTableName 구문에서 오류가 나는데
                        # 이유는 CURSOR 는 동적(dynamic)으로 생성될 수 없기 때문다.
                        # 대신 dynamic SQL 을 이용하여 View는 생성이 가능한데,
                        # 이를 이용해서 TempView를 생성하고 그 View Name을 이용하여 CURSOR를 생성하도록 한다.
                        #DECLARE Nested_Cursor CURSOR FOR
                        #SELECT ApID, CAST(mColumnName AS CHAR) As mColumnName
                        #FROM mTableName
    
            
                        SET @mQuery= CONCAT('CREATE VIEW TempView As Select ApID, CAST(',mColumnName,' AS CHAR) FROM ', mTableName);    
    
                     
                        PREPARE stmt FROM @mQuery;
                        EXECUTE stmt;
                        DEALLOCATE PREPARE stmt;
    
                        OPEN Nested_Cursor;
   
      
                        REPEAT     
                                FETCH NEXT FROM Nested_Cursor INTO mTblApID, mTblData;    # 이 Data가 항상 같은 Table의 Column 값만 받아옴.

                                IF NOT mTblDone THEN
                                        INSERT INTO `CombineTable` VALUES(mTblApID, mTblData);
                                END IF;

                        UNTIL mTblDone END REPEAT;

                        CLOSE Nested_Cursor;    
                        DROP VIEW TempView;

    
                        END BLOCK1;

                END IF;

        UNTIL mDone END REPEAT; 
   
        CLOSE mo_Cursor;
 
END $$

DELIMITER ';'

CALL procDoitCombine();

DROP PROCEDURE procDoitCombine;