MySQL通过视图（或临时表）实现动态SQL（游标）

>参考de优秀文章

MySQL通过视图（或临时表）实现动态SQL（游标）。

因在实现中，需要通过DDL语句创建视图（或临时表）、删除视图（或临时表），故，只适合在一些一次性的脚本中使用，比如调试一些数据问题、做一些简单的数据初始化。

不适合使用在涉及业务操作的程序中。

>使用view实现

drop procedure if exists p_simulate_dynamic_cursor;

create procedure p_simulate_dynamic_cursor()

begin

    declare v_sql varchar(4000);

        declare v_field varchar(4000);

        declare v_result varchar(4000) default '';

        declare cur_temp cursor for select v.* from view_temp_20150701 v;

        declare continue handler for not found set v_field = null;

        set v_sql = 'create view view_temp_20150701 as select t.id from t_user t';

        set @v_sql = v_sql;

        prepare statement from @v_sql;

        execute statement;

        deallocate prepare statement;

        open cur_temp;

        fetch cur_temp into v_field;

        while (v_field is not null) do

                set v_result = concat(v_result, v_field, ',');

                fetch cur_temp into v_field;

        end while;

        close cur_temp;

        select v_result;

        drop view if exists view_temp_20150701;

end;

call p_simulate_dynamic_cursor();

>使用temporary table实现

drop procedure if exists p_simulate_dynamic_cursor_by_temp_table;

create procedure p_simulate_dynamic_cursor_by_temp_table()

begin

    declare v_sql varchar(4000);

        declare v_field varchar(4000);

        declare v_result varchar(4000) default '';

        declare cur_temp cursor for select t.* from t_temp_20150701 t;

        declare continue handler for not found set v_field = null;

        set v_sql = 'create temporary table t_temp_20150701 as select t.id from t_user t limit 0, 100';

        set @v_sql = v_sql;

        prepare statement from @v_sql;

        execute statement;

        deallocate prepare statement;

        open cur_temp;

        fetch cur_temp into v_field;

        while (v_field is not null) do

                set v_result = concat(v_result, v_field, ',');

                fetch cur_temp into v_field;

        end while;

        close cur_temp;

        select v_result;

        drop temporary table if exists t_temp_20150701;

end;

call p_simulate_dynamic_cursor_by_temp_table();

巴特西

MySQL通过视图（或临时表）实现动态SQL（游标）

>参考de优秀文章

>使用view实现

>使用temporary table实现

最新文章

热门文章