mysql存储过程表作为变量

浅浅的花香味﹌ 2023-01-04 11:54 183阅读 0赞

CREATE DEFINER=`root`@`%` PROCEDURE `create_table_by_unit_top_id`( out  out_result int(8))
    BEGIN
         
    
    		
    		#结束标识定义
    		DECLARE edone INT DEFAULT 0 ;
    		 
    	 
    		#结果集
    		declare result_date VARCHAR(5000) DEFAULT '';
    	
     
    		
    		########替换变量开始-------------------
    		#定义游标临时变量-------------------开始
     
     
        DECLARE index_merchant_id VARCHAR (36);
     
    		
    		#定义游标临时变量===================结束
    		#声明游标 ----------------开始
    		#声明游标cursor_name（cursor_name是个多行结果集） 
        DECLARE cursor_name CURSOR FOR 
    		
    		select DISTINCT unit_top_id  FROM sys_unit ;
     
    
    		#声明游标==================结束
    		########替换变量结束===================
    	
    		#设置终止标志
    		#DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; 
    		DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = 1;
    
     
    				#重置游标 
    				set  edone = 0; 
    					
    					
    					#二级循环开始
    					#打开游标 
    					OPEN cursor_name;
     					 
    										#遍历游标
    										while edone <> 1 do
    													 #######替换变量开始------------------------------
    														#获取游标当前指针的记录，读取一行数据并传给变量a,b 
    														FETCH cursor_name INTO  index_merchant_id;
    														 SET @tbl_name=CONCAT("order_detail_item_",index_merchant_id);
    														 
    														SET @statement = CONCAT("CREATE TABLE ",@tbl_name," like order_detail_item; ");
    														PREPARE stmt FROM @statement;
    														EXECUTE stmt;
    											  
    											#sql开始
    									    SELECT  index_merchant_id;
    										set result_date  =  concat(result_date,',',index_merchant_id);
    													
    											#sql结束		
    								  
    								   
    						 
    								 #结束游标变成了1,游标只要有值就不会走end whil
    								 end while;
    	 
    									
    						#关闭游标 
    						CLOSE cursor_name;
    					 #二级循环结束
    					 
     
    	  SELECT  edone;
    		--  赋值结果,先查询再赋值
    		SELECT result_date;
    		set out_result=result_date;
     
    END