mysql存储过程

1.存储过程判断table是否存在

##创建存储过程
create procedure procedure_text()
begin
declare var int;
   if exists(select table_name from `INFORMATION_SCHEMA`.`TABLES` where table_name ='table_name' and TABLE_SCHEMA='test') then##判断表是否存在
      SET var = 0;
ELSE 
SET var = 1;
end if;
SELECT var;
end;
##调用存储过程
call procedure_text();
##删除存储过程
DROP PROCEDURE procedure_text;
##查询存储过程
show procedure status;

2.循环执行

##循环
create procedure procedure_text()
BEGIN
declare var int;##声明变量
SET var = 0;
WHILE var < 6 DO ##循环判断条件
INSERT INTO text VALUES(var);##执行语句
SET var = var + 1;
END WHILE;
END;

3.查询a表数据插入b表

create procedure procedure_text(IN p_index INT)
BEGIN

##拼接sql语句
SET @sqlstr = concat(
'insert into dev_inverter',
date_format(adddate(now() ,- p_index), '%Y%m%d'),
' SELECT * FROM dev_inverter as dev WHERE date_format(dev_lastsyn,''%Y%m%d'') = ',
"'",
date_format(adddate(now() ,- p_index), '%Y%m%d'),
"'"
);
PREPARE stmt1
FROM @sqlstr;
EXECUTE stmt1;
END

set @p_index = 1;
call procedure_text(@p_index);##执行存储过程