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

/*定义存储过程*/

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 = '';

    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='';

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