MYSQL存储过程模板

drop PROCEDURE if EXISTS a;
delimiter $$

create  PROCEDURE a (in c int,inout d int,out f int)-- 在例子中c=12,d=10,f未赋值
BEGIN
DECLARE e int;-- 存储过程中声明不需要@
set e=d;-- e=10

set d=c+10;-- @n=22
set f=e;-- @x=10
set c=c+1;-- c为in模式，修改它的值不影响@m，@m仍为12
-- 最后一定要提交
commit;
end $$
delimiter ;
-- ------------------------------------------------------------------------------------
set @m=12,@n=10;
call a(@m,@n,@x);
select @m,@n,@x;

drop PROCEDURE if EXISTS update_feibiao;  
DELIMITER ;;  
  
create  PROCEDURE update_feibiao()  
BEGIN  
  
drop table if exists temp.hff_zhanghu;  
  
create table temp.hff_zhanghu  
select cust_id,company_name,op_unit_name1  
from zhixiao_acct.acct_info;  
  
update temp.hff_feibiao  
set 消费=7920000;   

-- 最后一定要提交
commit;
end;;  
  
DELIMITER ;  
  
-- ---------------------------------  
call update_feibiao();

-- 存储过程传入参数作为表名========================================================
drop PROCEDURE if EXISTS zhixiao_cash.test;
delimiter $$
create PROCEDURE zhixiao_cash.test( IN table_name varchar(255))
BEGIN

set @str=concat('UPDATE ',table_name,' a left JOIN  ',table_name,'  b ON a.table_name=b.table_name SET a.size_table=888;');
select @str;
PREPARE stmt from @str;
EXECUTE stmt;


-- 注意最后一定要提交！！！
commit;
end $$
delimiter ;

-- 存储过程========================================================
drop PROCEDURE if EXISTS test;
delimiter $$
create PROCEDURE test( IN input varchar(255))
BEGIN
select * from zhixiao_la.acct_info_2018q2 where op_unit_name= input;
select * from zhixiao_la.acct_info_2018q2 where op_unit_name!= input;
-- 注意最后一定要提交！！！
commit;
end $$
delimiter ;

-- 事件============================================================
drop event if EXISTS xzq_update_acct_info_dazhaxie_2018;
delimiter $$
CREATE  EVENT xzq_update_acct_info_dazhaxie_2018 ON SCHEDULE EVERY 1 DAY STARTS '2018-09-10 20:18:00' ON COMPLETION NOT PRESERVE ENABLE DO 
BEGIN
drop  table IF EXISTS zhixiao_acct.acct_info_dazhaxie_2018;
create TABLE zhixiao_acct.acct_info_dazhaxie_2018
select * from suzhou.acct_info_dazhaxie18;
-- 注意最后一定要提交！！！
commit;
end $$
delimiter ;