存储过程游标嵌套循环

最新推荐文章于 2023-09-25 12:48:31 发布

天下迎春

最新推荐文章于 2023-09-25 12:48:31 发布

阅读量586

点赞数 1

文章标签： mysql 存储过程双重循环

本文链接：https://blog.csdn.net/qq_42173978/article/details/102579719

版权

循环建表

DELIMITER //
DROP PROCEDURE
IF
	EXISTS T_SP_AD1;
CREATE PROCEDURE T_SP_AD1 ( ) BEGIN
	DECLARE
		t_name1 TINYTEXT;
	DECLARE
		l_id TINYTEXT;
	DECLARE
		s INT DEFAULT 0;
	DECLARE
		sw2 CURSOR FOR SELECT
		bkid 
	FROM
		f_form_type 
	ORDER BY
		bkid;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET s = 1;
	OPEN sw2;-- 循环游标sw2
	s2_loop :
	LOOP
			FETCH sw2 INTO t_name1;
		IF
			s = 1 THEN
				LEAVE s2_loop;
			
		END IF;

		SET @iid =(SELECT IFNULL(MAX(item_id),0) item_id FROM f_run_data where f_form_type_id=t_name1);
		SET @str = CONCAT( 'CREATE TABLE IF not EXISTS f_run_data_', t_name1, ' (
			`bkid` bigint(20) NOT NULL auto_increment,
			`nver` int(11) DEFAULT NULL,
			`bfail` char(1) DEFAULT NULL,
			`status` char(1) DEFAULT NULL,
			`editime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			`createtime` datetime DEFAULT NULL,
			`uecode` varchar(200) DEFAULT NULL,
			`ngroup` int(11) DEFAULT NULL,
			`norder` int(11) DEFAULT NULL,
			`parentid` bigint(20) DEFAULT NULL,
			`formid` bigint(20) DEFAULT NULL,
			`flowid` bigint(20) DEFAULT NULL,
			`runid` bigint(20) DEFAULT NULL,
		`prcsid` bigint(20) DEFAULT NULL' );
		SET @icount=0;
		s3_loop :
		LOOP
			IF
				@icount = @iid THEN
					LEAVE s3_loop;
				
			END IF;-- INSERT 语句拼接
			SET @icount= @icount+1;
			SET @str = CONCAT( @str, ',`data_', @icount, '` text' );
		
		END LOOP s3_loop;
		
		SET @str = CONCAT( @str, ',PRIMARY KEY  (`bkid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;' );
		PREPARE a 
		FROM
			@str;
		EXECUTE a;
		DEALLOCATE PREPARE a;
		
	END LOOP s2_loop;
	CLOSE sw2;
	
END //
DELIMITER;
CALL T_SP_AD1;

双重循环增加字段插入字段值

DELIMITER //
DROP PROCEDURE
IF
	EXISTS T_SP_AD2;
CREATE PROCEDURE T_SP_AD2 ( ) BEGIN
	DECLARE
		t_name1 TINYTEXT;
	DECLARE
		s INT DEFAULT 0;
	DECLARE
		sw2 CURSOR FOR SELECT
		bkid 
	FROM
		f_form_type 
	ORDER BY
		bkid;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET s = 1;
	OPEN sw2;-- 循环游标sw2
	s2_loop :
	LOOP
			FETCH sw2 INTO t_name1;
		IF
			s = 1 THEN
				LEAVE s2_loop;
			
		END IF;
		
		
		SET @iid = (
			SELECT
				IFNULL(max( a.item_id ) ,0)
			FROM
				f_run_data a
				INNER JOIN f_run b ON a.run_id = b.bkid
				INNER JOIN f_type c ON b.flow_id = c.bkid 
			WHERE
				form_id = t_name1 
			);
		
		SET @str = CONCAT( 'ALTER TABLE f_run_data_', t_name1 );
		
		SET @str2 = "INSERT INTO `f_form_field` (`form_id`, `id`, `name`, `title`, `fieldstyle`, `uistyle`, `bfail`) VALUES";
		
		SET @icount = 0;
		s3_loop :
		LOOP
			IF
				@icount = @iid THEN
					LEAVE s3_loop;
				
			END IF;-- 语句拼接
			
			SET @icount = @icount + 1;
			
			SET @str = CONCAT( @str, ' add `data_', @icount, '` text' );
			
			SET @str2 = CONCAT( @str2, "(", t_name1, ",", @icount, ",'data_", @icount, "','', 'text', 'text', '0')" );
			IF
				@icount < @iid THEN
					
					SET @str = CONCAT( @str, ',' );
				
				SET @str2 = CONCAT( @str2, ',' );
				
			END IF;
			
		END LOOP s3_loop;
		IF
			@icount <> 0 THEN
				PREPARE a 
			FROM
				@str;
			EXECUTE a;
			DEALLOCATE PREPARE a;
			PREPARE b 
			FROM
				@str2;
			EXECUTE b;
			DEALLOCATE PREPARE b;
			
		END IF;
		
	END LOOP s2_loop;
	CLOSE sw2;
	
END //
DELIMITER ;
CALL T_SP_AD2;