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

 

 

1. 结合占位符
   

   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(); -- 存储过程的执行测试 

    

2. 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

    

3. 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;  -- 存储过程的结束