mysql5 存储过程和触发器示例

一、存储过程

1.1 语法

CREATE[DEFINER = { user | CURRENT_USER }]　PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...] routine_body proc_parameter:[ IN | OUT | INOUT ] param_name type characteristic:COMMENT 'string'  | LANGUAGE SQL  | [NOT] DETERMINISTIC  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER } routine_body:　　Valid SQL routine statement [begin_label:] BEGIN　　[statement_list]　　　　……END [end_label]

1.2 示例

CREATE DEFINER=`xrh`@`%` PROCEDURE `proc_sumArticleVup`(IN `site` varchar(50), OUT `total` int)#BEGIN单条语句不能用BEGIN END;包裹，否则无法执行，会抛出 ERROR 1064 (42000): You have an error in your SQL syntax; 
#check the manual that corresponds to your MySQL server version for the right syntax to useSELECT Sum(articleVup) into total FROM cms_article WHERE opSite=site limit 100;#END;

二、触发器：

2.1 语法

CREATE TRIGGER <触发器名称>{ BEFORE | AFTER }{ INSERT | UPDATE | DELETE } ON <表名称>FOR EACH ROW<触发的SQL语句>

2.2 示例

DROP TRIGGER IF EXISTS sslm_reply_insert_oper_check_data;

CREATE TRIGGER sslm_reply_insert_oper_check_data
AFTER INSERT ON
sslm_reply
FOR EACH ROW
BEGIN
  if new.checkState='0' then
    insert into oper_check_data (code,dataId,createTime) values ('sslm_reply', new.id, now());
  end if;
END

DROP TRIGGER IF EXISTS sslm_reply_update_oper_check_data;

CREATE TRIGGER sslm_reply_update_oper_check_data
AFTER UPDATE ON
sslm_reply
FOR EACH ROW
BEGIN
  if old.checkState='0' then
    insert into oper_check_data (code,dataId,createTime) values ('sslm_reply', old.id, now());
  end if;
END