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; //