MySQL procedure & function 存储过程 & 存储函数

结合占位符

drop PROCEDURE if EXISTS test12;
-- 使用drop 来删除存储过程或者表

create PROCEDURE test12()
-- 创建存储过程 命名为test12 

BEGIN

  set @tableNames = CONCAT('background');
  -- '@' 先在用户变量中保存值然后在以后引用它

  set @beanId = 6;

  set @sqlStr = CONCAT('select * from ' ,@tableNames ,
   ' where background_id = ?' );
   -- 拼接查询总记录的SQL语句 

  prepare stmt from @sqlStr;
   -- 预定义一个语句，并将它赋给 stmt

  execute stmt using @beanId;
   -- 执行语句

  deallocate prepare stmt;
  -- 要释放一个预定义语句的资源
END;

call test12(); -- 存储过程的执行测试

CREATE DEFINER=`root`@`%` PROCEDURE `loop3`(table_name1 varchar(44),table_name2 varchar(44))
BEGIN
    #Routine body goes here...
    set @tableName1=concat(table_name1);
    set @tableName2=concat(table_name2);
    
    set @sqlStr=concat('select * from ',@tableName1,' limit 5');
    prepare stmt from @sqlStr;
    execute stmt;
--     deallocate prepare stmt;
    
    set @sqlStr_=concat('select * from ',@tableName2,' limit 5');
    prepare stmt from @sqlStr_;
    execute stmt;
    deallocate prepare stmt;
    
END

create PROCEDURE tests(tableName varchar(20)) 
-- 创建存储过程 命名为tests

BEGIN   -- 存储过程的开始

  set @tableNames = CONCAT(tableName); 
  -- @先在用户变量中保存值然后在以后引用它

  set @sqlStr = CONCAT('select * from ', @tableNames);
  -- 拼接查询总记录的SQL语句 

  prepare stmt from @sqlStr; -- 预定义一个语句，并将它赋给 stmt

  execute stmt ; -- 执行语句

  deallocate prepare stmt;-- 要释放一个预定义语句的资源

END;  -- 存储过程的结束