##创建存储过程 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);##执行存储过程
##创建存储过程create procedure procedure_text()begindeclare var int; if exists(select table_name from `INFORMATION_SCHEMA`.`TABLES` where table_name ='dev_inverter20160721' and TABLE_SCHEMA='chint