Mysql 存储过程 简单实例

2015-12-26 • • No Comments

BEGIN
    DECLARE
        null_count INT DEFAULT 0;

    DECLARE
        tmp_user_id_fix INT DEFAULT 0;

    DECLARE
        tmp_user_id INT DEFAULT 0;

    DECLARE
        tmp_heigher_user_name VARCHAR (60) DEFAULT '';

    DECLARE
        tmp_grade INT DEFAULT 0;

    SELECT
        COUNT(*) INTO null_count
    FROM
        vz_user
    WHERE
        heigher_user_id IS NULL
    AND user_id != 1
    AND (
        main_user_id = 0
        OR main_user_id IS NULL
    );


    WHILE null_count > 0 DO
        SELECT
            heigher_user_name,
            user_id INTO tmp_heigher_user_name,
            tmp_user_id_fix
        FROM
            vz_user
        WHERE
            heigher_user_id IS NULL
        AND user_id != 1
        AND (
            main_user_id = 0
            OR main_user_id IS NULL
        )
        ORDER BY
            user_id ASC
        LIMIT 1;

    SELECT
        user_id,
        grade_id INTO tmp_user_id,
        tmp_grade
    FROM
        vz_user
    WHERE
        user_name = tmp_heigher_user_name;

    UPDATE vz_user
    SET heigher_user_id = tmp_user_id,
     grade_id = tmp_grade + 1
    WHERE
        user_id = tmp_user_id_fix;

    SET null_count = null_count - 1;

    END WHILE;

END

粤公网安备 44011102000950号