mysql 存儲過程demo

從沒寫過mysql 存儲過程，靠著百度和以前寫oracle存儲過程的經驗寫了一個，還算順利，留個例子吧

CREATE DEFINER=`west_brain`@`%` PROCEDURE `man_tree_area`( )
BEGIN
  -- 存儲樹狀結果處理sql變量
    DECLARE
        var_code VARCHAR ( 1000 );
    DECLARE
        var_pcode VARCHAR ( 1000 );
    DECLARE
        var_name VARCHAR ( 1000 );
    DECLARE
        var_count INT;

    -- 存儲的一些標記變量    
    DECLARE
        buf_parents VARCHAR ( 1000 ) DEFAULT '';
    DECLARE
        buf_names VARCHAR ( 1000 ) DEFAULT '';
    DECLARE
        buf_code VARCHAR ( 100 ) DEFAULT '';
    DECLARE
        buf_update INT DEFAULT FALSE;
    DECLARE 
      buf_is_leaf INT DEFAULT 0;

    -- 樹級別 根為1
    DECLARE 
      buf_tree_level int DEFAULT 0;
    -- 是否葉子節(jié)點 0 非 1是
    DECLARE 
      buf_tree_leaf int DEFAULT 1;

    -- 存儲表循環(huán)游標的變量
    DECLARE
        vcode VARCHAR ( 64 );
    DECLARE
        vparent VARCHAR ( 1000 );

    -- 游標結束的處理變量
    DECLARE
        done INT DEFAULT FALSE;

    -- 定義表循環(huán)游標
    DECLARE
        mycursor CURSOR FOR ( SELECT CODE, parent FROM adm_sys_area_info );

    -- 定義游標溢出的處理操作
    DECLARE
        CONTINUE HANDLER FOR NOT FOUND 
        SET done = TRUE;

    -- 打開游標
    OPEN mycursor;

    -- 定義游標循環(huán)
    tableloop :
    LOOP
        -- 讀取游標的一條數(shù)據(jù)到變量里
            FETCH mycursor INTO vcode,
            vparent;

        -- 如果上步游標操作沒有讀取到記錄，則done 會被設置為 TRUE，退出 名稱為myloop的循環(huán)
        IF
            done THEN
                LEAVE tableloop;
        END IF;

        -- 記錄當前記錄的區(qū)域編碼
        SET buf_code = vcode; 

        -- 判斷自己是否是葉子節(jié)點
        SELECT count(*) into var_count from adm_sys_area_info where parent  = vcode;

        if var_count = 0 then
          update adm_sys_area_info set tree_leaf = 1 where code = vcode;
        else
          update adm_sys_area_info set tree_leaf = 0 where code = vcode;
        end if;

        set var_count = 0;

    -- 循環(huán)查找自己的父節(jié)點
        treeloop :
        LOOP

          -- 判斷自己是否存在父節(jié)點，用count來判斷
            SELECT
                count( * ) INTO var_count 
            FROM
                adm_sys_area_info 
            WHERE
                CODE = vparent;

            IF
                var_count = 0 THEN
                -- 沒有上級節(jié)點了，則開始處理以前找到的父節(jié)點
                IF
                    buf_update THEN-- 更新緩存的數(shù)據(jù)
                    -- buf_update 為TRUE 則說明找到過父節(jié)點

                    -- 下面兩個記錄處理拼接的字符串末尾多的逗號的問題
                    IF
                        ( length( buf_parents ) > 0 ) THEN

                            SET buf_parents = LEFT ( buf_parents, CHAR_LENGTH( buf_parents ) - 1 );
                    END IF;
                    IF
                        ( length( buf_names ) > 0 ) THEN

                            SET buf_names = LEFT ( buf_names, CHAR_LENGTH( buf_names ) - 1 );
                    END IF;

                    -- 更新當前節(jié)點的父信息
                    UPDATE adm_sys_area_info 
                    SET parents = buf_parents,
                    tree_names = buf_names ,tree_level = buf_tree_level 
                    WHERE
                        CODE = buf_code;

                ELSE
                  -- 當前記錄是根節(jié)點
                    update adm_sys_area_info set tree_level = 1 where code = buf_code;
                END IF;

                -- 清理變量
                SET buf_parents = '';
                SET buf_names = '';
                SET vparent = '';
                SET buf_code = '';
                SET buf_update = FALSE;
                SET buf_tree_level = 1;

                -- 結束當前記錄的處理循環(huán)
                LEAVE treeloop;

                ELSE 
                  -- 查找到了父節(jié)點
                    SET buf_update = TRUE;
                    -- 查詢當前節(jié)點的父節(jié)點信息
                    SELECT CODE,
                        parent,
                        area_name INTO var_code,
                        var_pcode,
                        var_name 
                    FROM
                        adm_sys_area_info 
                    WHERE
                        CODE = vparent;

                    -- 連接字符串
                    SET buf_parents = CONCAT_WS( ',', var_code, buf_parents );
                    SET buf_names = CONCAT_WS( ',', var_name, buf_names );
                    -- 記錄當前查找到記錄的父節(jié)點code
                    SET vparent = var_pcode;
                    SET buf_tree_level = buf_tree_level + 1;

            END IF;

        END LOOP;

    END LOOP;
    CLOSE mycursor;

END