萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

MySQL 預存程序入門:建立、游標與暫存表實戰

預存程序基本結構

CREATE PROCEDURE procedure_name()
BEGIN
  -- 宣告區
  DECLARE var_name data_type [DEFAULT value];
  
  -- 游標區
  DECLARE cursor_name CURSOR FOR SELECT ...;
  
  -- 處理區
  OPEN cursor_name;
  FETCH cursor_name INTO var_name;
  -- 迴圈處理
  CLOSE cursor_name;
  DEALLOCATE cursor_name;
END

完整範例:交叉合併兩個查詢結果

CREATE PROCEDURE test_cross_join()
BEGIN
  -- 不返回影響行數
  SET NOCOUNT ON;
  
  -- 宣告變數
  DECLARE @col1 VARCHAR(20), @col2 VARCHAR(20);
  DECLARE @index INT DEFAULT 1;
  
  -- 建立暫存表儲存結果
  CREATE TEMPORARY TABLE #tmptbl (
    colID INT AUTO_INCREMENT PRIMARY KEY,
    col1 VARCHAR(20),
    col2 VARCHAR(20)
  );
  
  -- 宣告兩個游標
  DECLARE cur1 CURSOR FOR SELECT customerid FROM orders LIMIT 2;
  DECLARE cur2 CURSOR FOR SELECT regiondescription FROM region LIMIT 2;
  
  OPEN cur1;
  OPEN cur2;
  
  FETCH cur2 INTO @col2;
  FETCH cur1 INTO @col1;
  
  WHILE @@FETCH_STATUS = 0 DO
    INSERT INTO #tmptbl (col1, col2) VALUES (@col1, @col2);
    FETCH NEXT FROM cur1 INTO @col1;
    FETCH NEXT FROM cur2 INTO @col2;
  END WHILE;
  
  CLOSE cur1;
  CLOSE cur2;
  DEALLOCATE cur1;
  DEALLOCATE cur2;
  
  SELECT * FROM #tmptbl;
  DROP TEMPORARY TABLE #tmptbl;
END

關鍵點解析

  • @@FETCH_STATUS:游標擷取狀態
    • 0 = 成功
    • 1 = 失敗或無資料
    • 2 = 行不存在
  • TEMPORARY TABLE:連線結束自動刪除,避免殘留
  • 游標**必須**在 BEGIN...END 區塊內宣告
  • MySQL 使用 DELIMITER 變更結束符號以支援多語句

MySQL 特有語法調整

DELIMITER //

CREATE PROCEDURE test_cross_join()
BEGIN
  -- 程式碼內容(同上,但去除 SQL Server 特有語法)
  -- 注意:MySQL 不支援 SET NOCOUNT ON,改用不顯示行數的客戶端設定
  -- MySQL 暫存表語法:CREATE TEMPORARY TABLE ...
  -- 變數宣告:DECLARE var_name TYPE DEFAULT value;
END //

DELIMITER ;

呼叫預存程序

CALL test_cross_join();

優缺點權衡

優點缺點
減少網路往返(批次處理)除錯困難、工具支援較弱
封裝商業邏輯、提升安全性版本控管、部署較複雜
伺服器端執行、效能較好可攜性差(方言差異大)

現代替代方向

微服務架構下,傾向將商業邏輯移至**應用層**(Node.js、Java、Go 等),資料庫專注於資料存取,預存程序使用場景逐漸縮減。

copyright © 萬盛學電腦網 all rights reserved