SQL - 遊標

---

---

資料庫遊標解決了阻抗不匹配的問題。它充當 SQL 查詢結果與處理此結果的語句之間的過濾器。

SQL 中的遊標

遊標是在執行表上的數據操縱語言操作（例如 INSERT、UPDATE 和 DELETE 等）時由資料庫伺服器分配的臨時記憶體。它用於檢索和操作儲存在 SQL 表中的資料。

在 MySQL 中，不能直接在儲存過程或函式外部宣告遊標。遊標通常在 MySQL 資料庫中的儲存過程、函式或 SQL 程式碼塊中宣告。

使用遊標，我們可以對結果集的每一行執行多個操作，無論是否返回原始資料。

遊標的屬性

以下是 MySQL 遊標的屬性：

• 只讀 - 我們不能使用 MySQL 遊標更新或修改表中的任何記錄。我們只能從表中提取和處理資料。

• 不可滾動 - 我們只能單向檢索表中的記錄，即從第一條記錄或最後一條記錄開始。我們不能向後移動或跳轉到結果集中的特定位置。

• 非敏感遊標 - 非敏感遊標直接操作資料庫中的實際資料，它不會建立資料的副本。如果其他連線對資料進行了任何更改，則可能會影響遊標正在處理的資料。

除了非敏感遊標之外，還有另一種稱為不敏感遊標的型別。不敏感遊標使用資料的臨時副本。因此，這些遊標對錶中所做的更改不敏感（不受影響）。

遊標的生命週期

管理這些遊標有四個步驟。下圖說明了 SQL 遊標的生命週期：

現在，讓我們逐一討論遊標生命週期的各個階段。

宣告遊標語句

在 MySQL 中，我們可以使用DECLARE語句宣告遊標，並將其與 SELECT 語句關聯以從資料庫表中檢索記錄。

但是，與遊標關聯的此 SELECT 語句不使用 INTO 子句，因為其目的是提取和處理行，而不是將值賦給變數。

語法

以下是宣告 MySQL 資料庫中游標的語法：

DECLARE cursor_name CURSOR FOR select_statement;

開啟遊標語句

在 MySQL 中宣告遊標後，下一步是使用 OPEN 語句開啟遊標。它初始化結果集，允許我們從遊標中關聯的 SELECT 語句提取和處理行。

語法

以下是開啟 MySQL 資料庫中游標的語法：

OPEN cursor_name;

提取遊標語句

然後，我們可以使用 FETCH 語句檢索遊標指向的當前行，並且每次 FETCH 時，遊標都會移動到結果集中的下一行。這允許我們逐行處理。

語法

以下是提取 MySQL 資料庫中游標的語法：

FETCH cursor_name INTO variable_list;

關閉遊標語句

一旦提取所有行，我們必須關閉遊標以釋放與其關聯的記憶體。我們可以使用 CLOSE 語句執行此操作。

語法

以下是關閉 MySQL 資料庫中游標的語法：

CLOSE cursor_name;

示例

在此示例中，讓我們看看如何在儲存過程中管理遊標。

假設我們使用CREATE TABLE語句建立了一個名為CUSTOMERS的表，如下所示：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

現在，讓我們使用INSERT語句向CUSTOMERS表中插入一些記錄，如下所示：

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 );

現在，我們將建立一個名為“CUSTOMERS_BACKUP”的備份表來儲存客戶資料：

CREATE TABLE CUSTOMERS_BACKUP (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   PRIMARY KEY (ID)
);

在這裡，我們正在建立一個名為FetchCustomers的儲存過程，用於從CUSTOMERS表中提取客戶姓名，並將其逐個插入到BACKUP表中。我們使用遊標來迭代行，並使用處理程式來檢測結果集的結尾，確保所有名稱都被處理：

DELIMITER //
CREATE PROCEDURE FetchCustomers()
BEGIN
   DECLARE done INT DEFAULT FALSE;
   DECLARE customer_id INT;
   DECLARE customer_name VARCHAR(255);
   DECLARE auto_id INT; 

   -- Declare cursor
   DECLARE MY_CURSOR CURSOR FOR
   SELECT id, name FROM CUSTOMERS;

   -- Declare exit handler
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   -- Open cursor
   OPEN MY_CURSOR;

   -- Fetch and insert rows
   read_loop: LOOP
      FETCH MY_CURSOR INTO customer_id, customer_name;
      IF done = 1 THEN
         LEAVE read_loop;
      END IF;

      -- Insert the fetched data into the backup table
      INSERT INTO customers_backup VALUES (customer_id, customer_name);
		        
      -- Get the last auto-generated ID used in the insertion
      SET auto_id = LAST_INSERT_ID();

   END LOOP;
	
    -- Close cursor
    CLOSE MY_CURSOR;
END //
DELIMITER ;

成功建立過程後，我們可以使用 CALL 語句執行它，如下所示：

CALL FetchCustomers();

驗證

您可以使用 SELECT 語句驗證 CUSTOMERS_BACKUP 表的內容，如下所示：

SELECT * FROM CUSTOMERS_BACKUP;

表的內容將是：