mysql - 简单的触发器和存储过程

delimiter //
drop PROCEDURE proc_member_preprocessor;//
CREATE PROCEDURE proc_member_preprocessor (IN param_uid int(10))
main:BEGIN 
    SELECT COUNT(uid) INTO @proc_has_user FROM pre_common_member WHERE uid=param_uid;

    IF @proc_has_user < 1 THEN
        LEAVE main;
    END IF;

    INSERT INTO pre_common_member_tongji (uid,username,friends,posts,doings) (SELECT uid,'admin',friends,posts,doings FROM pre_common_member_count WHERE uid=param_uid);
END;//

CREATE TRIGGER trigger_pre_post AFTER INSERT ON pre_forum_thread FOR EACH ROW CALL proc_member_preprocessor(NEW.authorid);

 解释下触发器的语法：

CREATE TRIGGER TIME(BEFORE | AFTER) EVENT(INSERT | UPDATE | DELETE) ON table FOR EACH ROW sql 

UPDATE包含（NEW,OLD）INSERT（NEW）DELETE只有（OLD）

 

http://www.cnblogs.com/amboyna/archive/2008/01/27/1055178.html 这个文章里面说的非常详细。

 

/*定义存储过程*/
delimiter //
DROP PROCEDURE pro_demo; //
CREATE PROCEDURE pro_demo()
    SELECT * FROM pre_forum_thread;

//


/*传入变量*/
delimiter //
DROP PROCEDURE pro_demo; //
CREATE PROCEDURE pro_demo(IN param_user varchar(30))
    SET @username = param_user;
//


/*传出变量*/
delimiter //
DROP PROCEDURE pro_demo; //
CREATE PROCEDURE pro_demo(OUT param_uid int)
BEGIN
    SET param_uid = 5;
END; //
CALL pro_demo(@y);//

/*赋值并插入到数据表*/
delimiter //
DROP PROCEDURE pro_demo; //
CREATE PROCEDURE pro_demo()
BEGIN
    SET @a = '6';
    SET @b = 'xixi';
    INSERT INTO pre_proc_demo (k,v) VALUES (@a,@b);
END; //

/* 定义变量 @ INT,CHAR,VARCHAR..*/
delimiter //
DROP PROCEDURE pro_demo; //
CREATE PROCEDURE pro_demo()
BEGIN
    DECLARE a VARCHAR(5);
    DECLARE b VARCHAR(5);
    SET a='12345';
    SET b='awddwaadw';
    INSERT INTO pre_proc_demo (k,v) VALUES (a,b);
END; //

 

/* 存储过程中if else的使用 */
delimiter //
DROP PROCEDURE sp_demo; //
CREATE PROCEDURE sp_demo(IN param_var int(10))
BEGIN
    DECLARE a INT(10);
    SET a = param_var + 1;
    
    if a = 0 then
    SELECT 0;
    elseif a % 2 = 0 then
    SELECT 2;
    else
    SELECT 1;
    end if;
END;//

/* case语句的使用 */
delimiter //
DROP PROCEDURE sp_demo; //
CREATE PROCEDURE sp_demo(IN param_uid int(10))
BEGIN
    case param_uid
    when 0 then SELECT "这是0";
    when 1 then SELECT "这是1";
    else SELECT "没找到";
    end case;
END; //

/* while do..while的使用 */
delimiter //
DROP PROCEDURE sp_demo; //
CREATE PROCEDURE sp_demo(IN param_i int(10))
BEGIN
    while param_i < 10 
    do
    SELECT param_i;
    set param_i=param_i+1;
    end while;
END; //

/* repeat的使用 */
delimiter //
DROP PROCEDURE sp_demo; //
CREATE PROCEDURE sp_demo(IN param_i int(10))
BEGIN
    repeat 
    SELECT param_i;
    set param_i = param_i + 1;
    until param_i > 10
    end repeat;
END; //

/* loop的使用 */
delimiter //
DROP PROCEDURE sp_demo; //
CREATE PROCEDURE sp_demo(IN param_i int(10))
BEGIN

    LOOP_DEMO:loop
    SELECT param_i;
    set param_i = param_i + 1;

    if param_i > 5 then
    leave LOOP_DEMO;
    end if;

    end loop;
END; //