mysql存储过程写法—动态参数运用

  --删除

drop procedure if exists up_common_select

        --创建

CREATE  PROCEDURE `up_common_select`         (          in t_name varchar(50)         )         begin          declare v_sql varchar(500);          set v_sql= concat('select * from ',t_name);          select v_sql;         --注意:prepare(预处理)execute stmt using @var,只能跟@var变量,declare和传入的变量不行!!!          set @v_sql=v_sql;          prepare stmt from @v_sql;          EXECUTE stmt ;          deallocate prepare stmt;         end;

        --调用

call up_common_select('admin_authority');

        注意事项

        1  mysql5.0.13之后支持在存储过程中调用prepare

        2  prepare stmt from 'select * from ?';  (错)

            mysql5.0.24,prepare尚不支持 表名做变量!

            解决方案:用 contat()函数,组合字符串

        3  execute stmt [using @var,@var2]

             必须是@var形式的变量,传入的参数变量,declare变量不行

        4.  deallocate prepare stmt; 显式的释放prepare,如果不释放,mysql会释放,!