PL/SQL Cursor 解析 : Oracle Store Procedure & Cursor

-
PL/SQL 游標深入解析：從基礎到作用域

PL/SQL 游標深入解析：從基礎到作用域，一篇文章帶你徹底搞懂

大家好，我是Jayson。今天我們來聊聊 PL/SQL 中一個非常基礎但至關重要的概念——游標（Cursor）。無論你是剛接觸 Oracle 資料庫的萌新，還是已經寫過幾年 SQL 的老手，游標都是繞不開的話題。尤其是游標的作用範圍（Scope），很多人在實際開發中容易踩坑。

先來看一段簡單的範例程式碼：

CREATE OR REPLACE PROCEDURE TESTING
AS
    F_DATE VARCHAR2(10);
    CURSOR Hello_Cursor IS
        SELECT A FROM <your table> WHERE A = 'ABC';
BEGIN
    OPEN Hello_Cursor;
    FETCH Hello_Cursor INTO F_DATE;
    DBMS_OUTPUT.PUT_LINE(F_DATE);
    CLOSE Hello_Cursor;
END;

這段程式碼看起來很簡單，但它背後隱藏著游標使用的完整流程。接下來我們就從這段程式碼出發，逐步拆解游標的方方面面，最後深入討論游標的作用範圍。

一、什麼是游標？

簡單來說，游標是 PL/SQL 中用於處理 SQL 查詢結果集的一種機制。當你執行一個 SELECT 語句，回傳的結果可能包含多行資料，而 PL/SQL 是面向單行處理的，這時候就需要游標來逐行讀取資料。

游標分為兩類：

  • 隱式游標（Implicit Cursor）：由 PL/SQL 自動管理，用於單行查詢或 DML 操作。
  • 顯式游標（Explicit Cursor）：由程式設計師顯式定義，用於處理多行結果集。

今天我們重點講解顯式游標，因為它是我們手動控制的核心。

二、顯式游標的四步曲

使用顯式游標通常需要四個步驟：

  1. 宣告游標（Declare）：定義游標名稱和對應的 SELECT 語句。
  2. 開啟游標（Open）：執行 SELECT 語句，將結果集載入到記憶體。
  3. 提取資料（Fetch）：從結果集中逐行取出資料，存入變數。
  4. 關閉游標（Close）：釋放資源。

對應到範例程式碼：

  • 宣告：CURSOR Hello_Cursor IS SELECT A FROM <your table> WHERE A = 'ABC';
  • 開啟：OPEN Hello_Cursor;
  • 提取：FETCH Hello_Cursor INTO F_DATE;
  • 關閉：CLOSE Hello_Cursor;

細節解析

  • F_DATE VARCHAR2(10); 是一個區域變數（Local Variable），用來接收游標提取出的值。
  • 提取時，FETCH 語句將當前行的 A 欄位值賦給 F_DATE。
  • 如果查詢結果有多行，單次 FETCH 只取一行。要取所有行，需要迴圈提取。
  • 最後用 DBMS_OUTPUT.PUT_LINE 印出結果。

這裡有個小問題：如果查詢結果為空，F_DATE 會是什麼？答案是 NULL，並且 PUT_LINE 不會報錯，只是印出空行。但更嚴謹的做法應該檢查游標的狀態。

三、游標的作用範圍（Scope）

題目中特別提到了「cursor 範圍用法」，也就是游標及其相關變數的作用域。我們來仔細分析一下。

1. 變數的作用域

在範例中，F_DATE 是在程序的 AS 部分宣告的，它的作用域是整個 TESTING 程序。也就是說，在 BEGIN...END 區塊內的任何地方都可以存取它。一旦程序結束，該變數就消失了。

2. 游標的作用域

游標 Hello_Cursor 也是在 AS 部分宣告的，所以它的作用域也是整個程序。在程序內部，你可以開啟、提取、關閉它。但是，游標本身是一個資料物件，它並不像普通變數那樣儲存資料，而是指向結果集的記憶體區域。

3. 游標的可見性

  • 在宣告它的區塊內：整個區塊及巢狀子區塊中都可以使用該游標。
  • 在區塊外：不可見。如果你在另一個程序中想使用同一個游標，必須重新宣告。
  • 套件級游標（Package-level Cursor）：如果你在套件的規格（Specification）或主體（Body）中宣告游標，那麼它的作用域可以擴充到整個套件，甚至透過套件名稱在其他地方存取（如果宣告在規格中）。

所以，游標的作用域遵循 PL/SQL 的區塊結構作用域規則：內部區塊可以存取外部區塊的游標，反之則不行。

4. 游標與例外處理

如果在游標操作過程中發生例外（Exception），游標是否會自動關閉？不會。你需要在例外處理部分顯式關閉游標，否則可能造成資源洩漏（Resource Leak）。這也是作用域的一個體現：例外處理區塊仍然在同一個作用域內，所以可以存取游標並關閉它。

四、進階：游標屬性與迴圈提取

上面的範例只提取了一行，如果查詢結果有多行，我們需要迴圈。同時，我們可以利用游標的屬性（Attributes）來獲取狀態資訊。

游標常用屬性

  • %FOUND：上次 FETCH 是否有回傳一行（布林值）。
  • %NOTFOUND：與 %FOUND 相反。
  • %ROWCOUNT：目前已經提取的行數。
  • %ISOPEN：游標是否已開啟。

使用 LOOP 提取所有行

CREATE OR REPLACE PROCEDURE TESTING_ALL
AS
    F_DATE VARCHAR2(10);
    CURSOR Hello_Cursor IS
        SELECT A FROM your_table WHERE A LIKE 'A%';
BEGIN
    OPEN Hello_Cursor;
    LOOP
        FETCH Hello_Cursor INTO F_DATE;
        EXIT WHEN Hello_Cursor%NOTFOUND;  -- 沒有更多行時退出
        DBMS_OUTPUT.PUT_LINE(F_DATE);
    END LOOP;
    CLOSE Hello_Cursor;
END;

這裡用 %NOTFOUND 判斷是否結束迴圈，避免了無窮迴圈。

使用 FOR 迴圈簡化游標

PL/SQL 提供了 FOR 迴圈自動管理游標的開啟、提取和關閉，大大簡化了程式碼：

CREATE OR REPLACE PROCEDURE TESTING_FOR
AS
BEGIN
    FOR rec IN (SELECT A FROM your_table WHERE A LIKE 'A%') LOOP
        DBMS_OUTPUT.PUT_LINE(rec.A);
    END LOOP;
END;

rec 是隱式宣告的記錄變數（Record Variable），作用域僅限於迴圈內部。這種寫法最簡潔，推薦在不需要精細控制時使用。

五、參數化游標（Parameterized Cursor）

有時我們需要根據不同的參數執行相同的查詢，這時可以定義帶參數的游標：

CREATE OR REPLACE PROCEDURE TESTING_PARAM (p_prefix VARCHAR2)
AS
    CURSOR Hello_Cursor (cp_prefix VARCHAR2) IS
        SELECT A FROM your_table WHERE A LIKE cp_prefix || '%';
    v_date VARCHAR2(10);
BEGIN
    OPEN Hello_Cursor(p_prefix);
    LOOP
        FETCH Hello_Cursor INTO v_date;
        EXIT WHEN Hello_Cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_date);
    END LOOP;
    CLOSE Hello_Cursor;
END;

參數的作用域僅限於游標內部，每次開啟時傳入不同的值。

六、例外處理（Exception Handling）

當使用顯式游標時，常見的例外有：

  • NO_DATA_FOUND：通常出現在隱式游標中，顯式游標的 FETCH 不會引發此例外，只會導致 %NOTFOUND 為真。
  • TOO_MANY_ROWS：出現在隱式游標中，當 SELECT INTO 回傳多行時觸發。
  • INVALID_CURSOR：當游標未開啟就進行 FETCH 或 CLOSE 時觸發。

建議在程序中加入例外處理，確保資源釋放：

CREATE OR REPLACE PROCEDURE TESTING_SAFE
AS
    F_DATE VARCHAR2(10);
    CURSOR Hello_Cursor IS SELECT A FROM your_table WHERE A = 'ABC';
BEGIN
    OPEN Hello_Cursor;
    FETCH Hello_Cursor INTO F_DATE;
    DBMS_OUTPUT.PUT_LINE(F_DATE);
    CLOSE Hello_Cursor;
EXCEPTION
    WHEN OTHERS THEN
        IF Hello_Cursor%ISOPEN THEN
            CLOSE Hello_Cursor;
        END IF;
        RAISE;  -- 重新丟出例外
END;

七、最佳實踐小結

  1. 能用 FOR 迴圈就用 FOR 迴圈，程式碼簡潔，避免忘記關閉游標。
  2. 注意作用域，不要試圖在游標宣告之外存取它。
  3. 及時關閉游標，尤其是在例外處理中確保關閉。
  4. 使用游標屬性檢查狀態，避免無效操作。
  5. 參數化游標提高複用性，減少硬解析（Hard Parsing）。

八、總結

今天我們透過一個簡單的範例，詳細講解了 PL/SQL 顯式游標的宣告、開啟、提取、關閉四個步驟，並深入分析了游標及其變數的作用範圍。我們還介紹了游標屬性、迴圈提取、參數化游標和例外處理等進階內容。

游標是 PL/SQL 開發的基礎，掌握好游標的作用域和生命週期，能幫助你寫出更強健、更高效的程式碼。希望這篇文章對你有所幫助！如果你有任何問題或見解，歡迎在留言區討論。

參考資料：

  • Oracle 官方文件：PL/SQL Language Reference
  • 《Oracle PL/SQL Programming》by Steven Feuerstein

下次我們將探討游標變數（REF CURSOR），敬請期待！

本文由［Jayson Chan］原創，轉載請註明出處: freecodeinfosite.blogspot.com

留言

張貼留言

這個網誌中的熱門文章

8-Bit Plane Slicing 位元平面分割 詳細解說 # 附 Python 程式碼

-
圖片
[影像處理 Image Processing] 8-Bit Plane Slicing 位元平面分割 詳細解說 # 附 Python 程式碼 觀念解說︰ 8-bit plane slicing 主要作用於進行影像處理時，在影像中 切割出高頻與低頻的資訊 。 可以參考這張圖片幫助理解 8-bit plane slicing 分割抽像圖 如上圖所示，你可以想像，在一張灰階(8-bits)圖片的每一個像素pixel將被分層切割(0-7層)， 總共8層 ，像漢堡一樣，這就是 8-bit plane slicing 。 在一張 灰階圖片 ，其像素值範圍是 0 - 255 ，分別用於表達不同的深淺灰度，而圖片中每一個像素點(pixel)，都會有一個像素值(pixel value)。這一章將會以灰階圖片作為示例。 像素點、像素值的解釋，可以參考這篇文章。 其實圖片在數值處理上，就像一張 網格 ， 網格裡每一個格子都有不同的數值，代表著不同的顏色 。(RGB色彩圖示則用了三層的網格去表示，每一格由 RED, GREEN, BLUE 三種顏色組成) 計算方法︰ 8-bit plane slicing 其實就是將每一個像素值(pixel value)的二進制值，以其每個位數 (2 0 - 2 7 ) 進行過濾。 假設以下表格是一張 灰階圖片 的像素值(數值範圍0-255)︰ 217 97 85 21 219 115 177 204 104 e.g. 網格裡每一個格子都有不同的數值，代表著不同的顏色。(如上所示) ( 0 - 255) 1. 首先我們可以將灰階圖片裡的所有像素值 (範圍︰0 - 255) 轉為2進制表達。 十進制 二進制 0 0000 0000 1 0000 0001 2 0000 0010 3 0000 0011 ...... ...... 127 0111 1111 ...... ...... 255 1111 1111 2. 然後我們將圖片裡的每一格像素(pixel)，抽取其二進制值每個位數的值，成為Bit-plane。我將會以下方像素值為例︰ 217 97 85 21 219 115 177 204 104 像素...
Read more »

[實用工具]分租式單位電費單計算機

-
[實用工具] 分租式單位電費單計算機｜Jayson Chan 📋 分租式單位電費單計算機｜房東必備工具 作者：Jayson Chan · 實用工具 · 輕鬆分攤電費 📌 為什麼需要這個工具？ 對於分租套房或公寓的房東，每個月的電費單總是讓人心煩——總電費要如何公平地分攤給每一位房客？台電的電費單只有一個總金額和總用電度數，若每個房間都有獨立電錶，就可以透過「房間用電 + 公共用電」的方式精確計算。這個計算機正是為此而生，只要輸入總電費、總度數、房間數量及每間房間的抄錶讀數，就能自動算出每間房應繳的金額，包含個人用電與分攤的公共電費。 🧮 計算邏輯說明 每度電單價 = 總金額 ÷ 總用電度數 房間使用度數 = 本期抄錶 − 上期抄錶 房間個人電費 = 房間使用度數 × 每度單價 公共用電度數 = 總用電度數 − 所有房間使用度數總和 公共電費 = 公共用電度數 × 每度單價 每間分攤公共電費 = 公共電費 ÷ 房間數 房間總費用 = 房間個人電費 + 分攤公共電費 計算結果會以表格詳細列出每間房的抄錶資料、用電度數、個人電費、公共電費分攤及應繳總額。 ✍️ 使用步驟（圖文對照） 填寫基本資料 ：在下方表單輸入「應繳總金額」和「總用電度數」（可參考電費單），以及「分租單位數（房間數）」。 產生房間輸入表格 ：輸入房間數後，下方會自動出現對應房號的「本期抄錶」與「上期抄錶」欄位。請依序填入每個房間的獨立電錶讀數。（注意：本期抄錶必須大於或等於上期抄錶，否則會出現錯誤提示） 按下「計算開始」 ：系統會檢查所有欄位是否填寫正確（例如所有房間使用度數總和不得超過總...
Read more »

TIBC Spotfire SSL 證書安裝 / HTTPS 設定

-
圖片
TIBCO Spotfire SSL 證書安裝 / HTTPS 設定 TIBCO Spotfire SSL 證書安裝 / HTTPS 設定 大家好，我是 Jayson Chan 。在企業內部部署 TIBCO Spotfire Server 時，啟用 HTTPS 加密連線是常見的安全性要求。這篇文章將分享我實際處理一張 PFX 格式 SSL 證書的經驗，並點出最容易忽略的細節—— keystore 的 alias 。希望透過真實案例與圖文說明，幫助你順利完成 Spotfire 的 HTTPS 設定。 一、SSL 證書的常見格式 在開始之前，先認識一下 SSL 證書的各種檔案格式，因為接下來的操作會用到這些概念。以下是常見的檔案後綴及其說明： 檔案後綴 檔案類型 說明 *.DER 或 *.CER 二進制格式 只含有證書資訊，不包含私鑰。 *.CRT 二進制格式或文本格式 只含有證書資訊，不包含私鑰。 *.PEM 文本格式 一般存放證書或私鑰，或同時包含證書和私鑰。*.PEM 如果只包含私鑰，通常會用 *.KEY 作為檔名。 *.PFX 或 *.P12 二進制格式 同時包含證書和私鑰，且通常有密碼保護。這是 Windows 環境常見的交換格式。 ...
Read more »