mysql存储过程求大神(拼接字符串)

发布网友发布时间：2022-04-22 01:23

共1个回答

热心网友时间：2022-04-07 19:28

DROP PROCEDURE IF EXISTS `p_create_view`

CREATE  PROCEDURE p_create_view()
BEGIN
        DECLARE tableName VARCHAR (100);
        DECLARE stopFlag INT;
        DECLARE sql1 VARCHAR (1000);
        DECLARE cursor_name CURSOR  FOR SELECT TABLE_NAME  FROM information_schema.tables WHERE TABLE_NAME  LIKE 'test_2014%';
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'  SET stopFlag=1;
        SET @sql1='create  view test as select id,name from ';
        OPEN cursor_name;
        REPEAT
        FETCH cursor_name INTO tableName;
        SELECT   CONCAT(@sql1,tableName,' UNION ALL SELECT  id,name  FROM ') INTO @sql1 FROM DUAL;
        UNTIL stopFlag  END REPEAT;
        CLOSE cursor_name;
        SELECT SUBSTR(@sql1,1,LENGTH(@sql1)-31) INTO @sql1 FROM DUAL;
        PREPARE step FROM @sql1;
        EXECUTE step;
END;