麥克的學習紀錄

delimiter $$

CREATE PROCEDURE myFunction()

BEGIN

        --定義變數i並給予初始值

        DECLARE i INT DEFAULT 1;

        --建立temporary table

        CREATE TEMPORARY TABLE ascii_chart

        (ascii_code int, ascii_char CHAR(1));

        --採用while…do…end while迴圈

        WHILE (i<=128) DO

                INSERT INTO ascii_chart VALUES(i,CHAR(i));

                SET i=i+1;

        END WHILE;

        select * from ascii_chart order by ascii_code;

        --刪除temporary table

        drop table ascii_chart;

END$$

delimiter ;

CREATE PROCEDURE myProc ()

BEGIN

        --定義變數

        DECLARE l_id          INT;

        DECLARE l_code1      CHAR(2);

        DECLARE l_code2      CHAR(2);

        DECLARE l_count      INT;

        DECLARE no_more_maps INT;

        --定義資料庫cursor

        DECLARE dept_csr CURSOR FOR

        SELECT id,code1,code2 FROM map;

        --宣告一個繼續作業的HANDLER，此HANDLER監聽'NOT FOUND'狀態, 如果出現'NOT FOUND'的狀態時, 就把變數的值設為1

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_maps=1;

        --將handler設為初始化設定為0

        SET no_more_maps=0;

        --開啟資料庫cursor

        OPEN dept_csr;

        --用repeat…until….end repeat的語法(至少會迴圈一次)

        dept_loop:REPEAT

                --用FETCH將cursor逐筆讀入

                FETCH dept_csr INTO l_id,l_code1,l_code2;

                IF no_more_maps=0 THEN

                        SET l_count=l_count+1;

                        select  l_id,l_code1,l_code2;

                END IF;

        UNTIL no_more_maps

        END REPEAT dept_loop;

        CLOSE dept_csr;

        SET no_more_maps=0;

END$$