笔记-mysql 过程和触发器

/*****************************教研库 同步学校过程*********************************************************/
/**********************************学校 insert trigger****************************************************/
delimiter $
    create trigger jyd_preposition.add_school_info_trigger 
    after insert on jyd_preposition.school_info for each row
	BEGIN
      DECLARE school_id VARCHAR(15);
      DECLARE school_add VARCHAR(200);
      set school_add = NEW.zdmc;
      set school_id = NEW.xxdm;
      call bdschool.add_school_info(school_add,school_id);
	END
delimiter 
/**********************************学校 update trigger****************************************************/
delimiter $
    create trigger jyd_preposition.update_school_info_trigger 
    after update on jyd_preposition.school_info for each row
    begin
	   DECLARE w_id1 varchar(180) character set utf8;
       DECLARE w_id2 varchar(15) character set utf8;
       DECLARE w_id3 varchar(15) character set utf8;
       SET w_id1 = NEW.xxdz;
       SET w_id2 = NEW.xxmc;
       SET w_id3 = NEW.xxdm;
       call bdschool.update_school_info(w_id1,w_id2,w_id3);
    end$
delimiter


/**********************************学校 delete trigger****************************************************/


delimiter $
    create trigger jyd_preposition.del_school_info_trigger 
    after delete on jyd_preposition.school_info for each row
    begin
       DECLARE w_id varchar(15) character set utf8;
       SET w_id = OLD.xxdm;
       DELETE FROM bdschool.sys_dict WHERE dict_code=w_id;
    end$
delimiter


/**********************************学校 insert procedure****************************************************/
delimiter $ 	
    create procedure bdschool.add_school_info(in str varchar(15))
    begin
      DECLARE w_id int default 0;
      DECLARE w_id1 int default 0;
      DECLARE w_id3 varchar(15) default 'schoolType';
      if (str is not null) then
          if(str1 is not null) then
            set @w_id = (select b.dict_code from bdschool.sys_dict b where 1=1 and b.code='cityName' and b.dict_name=str);
            set @w_id1= (select count(b.id) from bdschool.sys_dict b where 1=1 and b.code='schoolType');
            set @w_id3= 'schoolType';
            insert into bdschool.sys_dict(create_date,modify_date,code,description,dict_code,dict_name,order_id,remark,status) 
                select NOW(),j.jfsj,@w_id3,j.zdmc,j.xxdm,j.xxmc,@w_id1+1,@w_id,1 from jyd_preposition.school_info j where 1=1 AND j.xxdm=str1;
          end if;
      end if;
    end$
delimiter


/**********************************学校 update procedure****************************************************/
delimiter $ 	
    create procedure bdschool.update_school_info(in str varchar(15), in str1 varchar(15), in str2 varchar(15))
    begin
      if (str is not null) then
      IF(str1 is not null) then
        IF(str2 is not null) then
		  UPDATE bdschool.sys_dict SET modify_date=NOW(),description=str,dict_name=str1 WHERE dict_code=str2;
         end if;
       end if;
	  end if;
    end$
delimiter 

//*************查看数据库编码方式******************//
show variables like 'character%'
select version()