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 use
SELECT 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