mysql 預存程序遊標-mysql教程-PHP中文網

MySQL 預存程序及遊標詳解

在 MySQL 資料庫中，預存程序是一組事先編譯好的 SQL 語句，可以視為資料庫中的子程式。預存程序通常用於處理複雜的業務邏輯，簡化了應用程式與資料庫之間的交互，提高了資料處理效率和安全性。而遊標(cursor)則是用來在預存程序中處理查詢結果集的機制。

本文將詳細介紹 MySQL 預存程序及遊標的使用方法與特性。

預存程序

1.1 預存程序的語法結構

以下是MySQL 預存程序的基本語法結構：

CREATE PROCEDURE procedure_name [ (parameter_list) ] BEGIN
    -- 存储过程的逻辑实现
END;

登入後複製

其中，

procedure_name：預存程序的名稱；
parameter_list：預存程序的參數列表，可以包含0 個或多個參數，每個參數都由參數名稱和參數類型組成；
BEGIN 和END 之間是預存程序的邏輯實作部分。

例如，定義一個簡單的預存程序，用於查詢學生表(student)中的所有記錄並傳回：

CREATE PROCEDURE get_all_students()
BEGIN
    SELECT * FROM student;
END;

登入後複製

1.2 預存程序的參數傳遞

預存程序可以預先定義參數，這些參數可以在呼叫預存程序時傳遞。 MySQL 預存程序支援 3 種參數傳遞方式，分別為：IN、OUT 和 INOUT。

IN：作為輸入參數，在呼叫預存程序時傳遞參數值。
OUT：作為輸出參數，在預存程序內部計算並傳回結果。
INOUT：既是輸入參數又是輸出參數，在呼叫預存程序時傳遞參數值並接收預存程序計算後的結果。

以下是使用參數傳遞的預存程序範例：

CREATE PROCEDURE add_two_numbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
    SET result = num1 + num2;
END;

登入後複製

在上面的範例中，add_two_numbers 預存程序接收兩個輸入參數num1 和num2，將它們相加的結果賦值給輸出參數result。

呼叫該預存程序可以使用下列語句：

CALL add_two_numbers(2, 3, @result);
SELECT @result; -- 输出 5

登入後複製

1.3 預存程序的流程控制

MySQL 預存程序中支援常見的流程控制語句，例如IF、CASE、 WHILE、LOOP 和LEAVE 等。使用這些語句可以實現複雜的邏輯控制和業務處理。

以下是使用IF 和WHILE 語句的預存程序範例：

CREATE PROCEDURE calc_factorial(IN num INT, OUT result BIGINT)
BEGIN
    SET result = 1;
    WHILE num > 1 DO
        SET result = result * num;
        SET num = num - 1;
    END WHILE;
END;

登入後複製

在上面的範例中，如果輸入參數num 大於1，則使用WHILE 迴圈計算num 的階乘值，並將結果儲存到輸出參數result 中。

呼叫該預存程序可以使用以下語句：

CALL calc_factorial(6, @result);
SELECT @result; -- 输出 720

登入後複製

遊標

2.1 遊標的基本概念

在MySQL 儲存過程中，遊標是用於在預存程序中遍歷查詢結果集的機制。遊標可以追蹤查詢結果集中的目前行，並根據需要逐行移動，讀取該行中的資料。

使用遊標宣告、開啟、關閉和操作遊標都需要用到以下4 種指令：

DECLARE：宣告遊標，定義遊標的名稱、查詢語句和遊標類型等。
OPEN：開啟遊標，將查詢結果集儲存到遊標指向的緩衝區。
FETCH：取得遊標指向的目前行，並將目前行的值儲存到對應的變數中。
CLOSE：關閉遊標，並釋放遊標的記憶體佔用。

以下是使用遊標的預存程序範例：

CREATE PROCEDURE get_all_students()
BEGIN
    DECLARE done INT DEFAULT FALSE; -- 定义游标是否结束的标志
    DECLARE s_id INT; -- 存储查询结果中的学生编号
    DECLARE s_name VARCHAR(255); -- 存储查询结果中的学生姓名
    DECLARE cursor_students CURSOR FOR SELECT id, name FROM student; -- 声明游标，查询表 student 中的所有数据
    -- 打开游标
    OPEN cursor_students;
    -- 遍历游标指向的结果集
    read_loop: LOOP
        -- 获取游标指向的当前行
        FETCH cursor_students INTO s_id, s_name;
        -- 如果游标到达结果集的末尾，则跳出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 输出当前行的值
        SELECT s_id, s_name;
    END LOOP;
    -- 关闭游标
    CLOSE cursor_students;
END;

登入後複製

在上面的範例中，宣告了一個名為 cursor_students 的遊標，用於查詢表 student 中的所有資料。開啟遊標後，使用 read_loop 迴圈體遍歷查詢結果集，並使用 FETCH 指令取得目前行的值儲存到變數 s_id 和 s_name 中，並輸出到控制台。如果遊標到達結果集的末尾，則跳出循環。當遊標使用完畢後，需要使用 CLOSE 指令關閉遊標。

2.2 遊標的特點和應用場景

雖然遊標為開發者提供了一種方便的查詢結果集的方式，但由於其需要使用額外的記憶體和資源，因此在使用時需要格外注意以下幾個問題：