如果仅仅是动态表名的查询,可以通过函数等方式获取表名存储在变量@table_name之中,通过EXECUTE执行字符串即可
set @sqlStr := concat('SELECT tradedate from ',@table_name,' where ...'); PREPARE stmt from @sqlStr; EXECUTE stmt;
如果是动态游标则有些麻烦,因为游标在声明时select语句必须指定,而游标的声明又必须在begin之后,其他语句之前
经过资料查询,可以使用临时视图的方式解决:声明时使用未创建的临时视图名,其后使用前述方法动态创建视图,然后再使用游标,最后清除视图即可
DECLARE order_cursor cursor for select tradedate from tmp_view_address where ...; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SET @table_name = ...; SET @v = concat('CREATE OR REPLACE VIEW tmp_view_address as SELECT * FROM ',@table_name); PREPARE stm FROM @v; EXECUTE stm; DEALLOCATE PREPARE stm; open order_cursor; #... close order_cursor; drop view tmp_view_address;
文章评论