mysql存储过程相关

查看所有的存储过程

show procedure status where Db='databasename';

查看存储过程的创建过程

show create procedure 存储过程名;

案例1

如果最大日期和第二大日期不等，则更新表字段:

drop procedure p_name;
delimiter $$
create procedure p_name()
begin
declare first_dt varchar(32);
declare sencond_dt varchar(32);
set @first_dt=(select max(dt) from t1);
set @sencond_dt=(select max(dt) from t1 where dt not in (select max(dt) from t1));
select substr(@first_dt,1,8);
select substr(@second_dt,1,8);
if(substr(@first_dt,1,8)<>substr(@sencond_dt,1,8))
then
update t2 set d_value='0',run_type=1 where run_type=2;
else
update t2 set run_type=1 where run_type=2;
end if;
end $$
delimiter ;