Oracle基本修練: PL/SQL Cursor

ChunJen Wang

Published in

jimmy-wang

16 min readSep 12, 2021

本篇記錄PL/SQL Cursor做法，與其如何具備像pointer功能，以範例進行練習。

Content

Implicit cursors and Explicit cursors
How to use a cursor
Cursor FOR LOOP statement
Cursor with Parameters
Cursor Variables with REF CURSOR
CURSOR FOR UPDATE

Let’s Start!

Cursor 是PL/SQL內建的pointer，可用來擷取整個資料集合，並進行逐筆資料存取。

分為implicit(內隱) cursor與explicit(外顯) cursor。

Implicit Cursors and Explicit Cursors

事實上，一般我們在執行SQL statements，如SELECT INTO,INSERT,UPDATE 等，其背後會自動建立內隱游標，也就是implicit cursors。
此游標涵蓋了query內執行週期、游標訊息與狀態，例如搜尋哪一行，有無符合資料等，SQL%ROWCOUNT,SQL%ISOPEN,SQL%FOUND,SQL%NOTFOUND。

當query返回 0筆或多列導致報錯NO_DATA_FOUND ,TOO_MANY_ROWS 代表implicit cursor is not elegant。

2. 相對來說，外顯游標就必須指定OPEN,FETCH,CLOSE ，由此query便會依據游標進行查詢。

How to use a cursor

使用步驟就以命名CURSOR，再定義其查詢的內容。[DECLARE]
而使用CURSOR前，也必須先將其狀態打開。[OPEN]
(打開游標時，Oracle 會進行查詢、綁定變量並執行關聯的 SQL 語句。)
並將其放入變量。[FETCH]
最後再關閉CURSOR。[CLOSE]

CURSOR cursor_name IS query;
OPEN cursor_name;
FETCH cursor_name INTO variable_list;
CLOSE cursor_name;

而cursor有基本四種屬性

%ISOPEN：查看cursor狀態為開啟或關閉。
%FOUND：NULL , 若有 record FETCH成功TRUE ,若無FALSE 。
%NOTFOUND：NULL , 若有 record FETCH成功TRUE ,若無FALSE 。
%ROWCOUNT：%ROWCOUNT 會返回有多少rows資料從cursor FETCH。

若cursor狀態為關閉，則會跳INVALID_CURSOR 。

使用範例資料庫來demo cursor：

首先先建立一個view，包含銷售收入與顧客資料

CREATE VIEW sales AS
SELECT customer_id,
       SUM(unit_price * quantity) total,
       ROUND(SUM(unit_price * quantity) * 0.05) credit
FROM order_items
INNER JOIN orders USING (order_id)
WHERE status = 'Shipped'
GROUP BY customer_id;

接著我們要做的有兩件事:
1) 將顧客的credit歸零，
2) 根據銷售狀況重新給予credit，且新的credit有預算限制(1 million)。

DECLARE
  l_budget NUMBER := 1000000;
   -- cursor
  CURSOR c_sales IS
  SELECT  *  FROM sales  
  ORDER BY total DESC;
   -- record    
   r_sales c_sales%ROWTYPE;
BEGIN  -- reset credit limit of all customers      步驟一
  UPDATE customers SET credit_limit = 0;OPEN c_sales;LOOP
    FETCH  c_sales  INTO r_sales;             透過cursor將資料FETCH上來
    EXIT WHEN c_sales%NOTFOUND;    -- update credit for the current customer 步驟二
    UPDATE 
        customers
    SET  
        credit_limit = 
            CASE WHEN l_budget > r_sales.credit 
                        THEN r_sales.credit 
                            ELSE l_budget
            END
    WHERE 
        customer_id = r_sales.customer_id;    --  reduce the budget for credit limit
    l_budget := l_budget - r_sales.credit;DBMS_OUTPUT.PUT_LINE( 'Customer id: ' ||r_sales.customer_id || 
' Credit: ' || r_sales.credit || ' Remaining Budget: ' || l_budget );    -- check the budget
    EXIT WHEN l_budget <= 0;
  END LOOP;CLOSE c_sales;
END;

而這一個LOOP就是透過cursor將每一row資料FETCH上來，在每一次循環過程中，依據顧客資料，逐筆進行更新(update)，並計算預算。

最終我們可以得到結果

SELECT customer_id,
       name,
       credit_limit
FROM customers
ORDER BY credit_limit DESC;

當然也可以確認預算是否正確

SELECT
  SUM( credit_limit )
FROM
  customers;

Cursor FOR LOOP statement

事實上 cursor FOR LOOP statement為numeric FOR LOOP statement相對elegant的寫法，後者針對指定的數值範圍進行LOOP，而前者是透過cursor進行關聯查詢來LOOP。

其結構為

FOR record IN cursor_name
LOOP
    process_record_statements;
END LOOP;

如我們希望透過cursor來幫我們逐筆印出record的產品名稱與價格

DECLARE
  CURSOR c_product
  IS
    SELECT 
        product_name, list_price
    FROM 
        products 
    ORDER BY 
        list_price DESC;
BEGIN
  FOR r_product IN c_product
  LOOP
    dbms_output.put_line( r_product.product_name || ': $' ||  r_product.list_price );
  END LOOP;
END;

或者我們也可以採用SELECT statement取代命名，可以得到一樣的結果
其結構為

FOR record IN (select_statement)
LOOP
    process_record_statements;
END LOOP;

實際操作為

BEGIN
  FOR r_product IN (
        SELECT 
            product_name, list_price 
        FROM 
            products
        ORDER BY list_price DESC
    )
  LOOP
     dbms_output.put_line( r_product.product_name ||
        ': $' || 
        r_product.list_price );
  END LOOP;
END;

差別就在於是否需要事先宣告cursor。後者雖然較簡短，但可讀性也相對前者較低，這點就依據開發團隊來進行設計。

Cursor with Parameters

而CURSOR也可以採用帶參數的方式來進行。

DECLARE
  CURSOR cursor_name (parameter_list) 
  IS
  cursor_query;
  ...BEGIN
  OPEN cursor_name (value_list);
  ...

例如，我希望透過CURSOR來幫助我們找到不同價錢定位區間的產品訊息
在此分為mass products(價錢50-100)與luxury products(價錢800-1000)。

DECLARE
    r_product products%rowtype;
    CURSOR c_product (low_price NUMBER, high_price NUMBER)
    IS
        SELECT *
        FROM products
        WHERE list_price BETWEEN low_price AND high_price;
BEGIN
    -- show mass products
    dbms_output.put_line('Mass products: ');
    OPEN c_product(50,100);
    LOOP
        FETCH c_product INTO r_product;
        EXIT WHEN c_product%notfound;
        dbms_output.put_line(r_product.product_name || ': ' ||r_product.list_price);
    END LOOP;
    CLOSE c_product;    -- show luxury products
    dbms_output.put_line('Luxury products: ');
    OPEN c_product(800,1000);
    LOOP
        FETCH c_product INTO r_product;
        EXIT WHEN c_product%notfound;
        dbms_output.put_line(r_product.product_name || ': ' ||r_product.list_price);
    END LOOP;
    CLOSE c_product;END;
/

當然這種帶參數的CURSOR，我們也可以給予其default_value，結構如

CURSOR cursor_name (
    parameter_name datatype := default_value, 
    parameter_name datatype := default_value, 
    ...
) IS 
    cursor_query;

Cursor Variables with REF CURSOR

REF CURSOR最大的好處就是，一般我們在進行query時，需要透過一般cursor FETCH所有資料到一個變量上，但有了REF CURSOR，我們就可以針對需要的資料進行FETCH，而其又分為strong typed 與 weak typed。

-- strong typed，就是有指定record structure或type的
DECLARE
    TYPE customer_t IS REF CURSOR RETURN customers%ROWTYPE;
    c_customer customer_t;-- strong typed 的另一種寫法(從Oracle 9i開始可用)
DECLARE
    c_customer SYS_REFCURSOR;-- weak typed
DECLARE
    TYPE customer_t IS REF CURSOR;
    c_customer customer_t;

範例

CREATE OR REPLACE FUNCTION get_direct_reports(
      in_manager_id IN employees.manager_id%TYPE)
   RETURN SYS_REFCURSOR
AS
   c_direct_reports SYS_REFCURSOR;
BEGINOPEN c_direct_reports FOR 
   SELECT 
      employee_id, 
      first_name, 
      last_name, 
      email
   FROM 
      employees 
   WHERE 
      manager_id = in_manager_id 
   ORDER BY 
         first_name,   
         last_name;RETURN c_direct_reports;
END;
---------------------------------------------------------------DECLARE
   c_direct_reports SYS_REFCURSOR;
   l_employee_id employees.employee_id%TYPE;
   l_first_name  employees.first_name%TYPE;
   l_last_name   employees.last_name%TYPE;
   l_email       employees.email%TYPE;
BEGIN
   -- get the ref cursor from function
   c_direct_reports := get_direct_reports(46);     -- 調用自訂func
   
   -- process each employee
   LOOP
      FETCH
         c_direct_reports
      INTO
         l_employee_id,
         l_first_name,
         l_last_name,
         l_email;
      EXIT
   WHEN c_direct_reports%notfound;
      dbms_output.put_line(l_first_name || ' ' || l_last_name || ' - ' ||    l_email );
   END LOOP;
   -- close the cursor
   CLOSE c_direct_reports;
END;
/

CURSOR FOR UPDATE

在CURSOR進行到特定行時，進行更新，此時我們會需要有WHERE來鎖定要更新的rows。

-- 無針對特定欄位更新
CURSOR cursor_name IS
    SELECT select_clause
    FROM from_clause
    WHERE where_clause
    FOR UPDATE;-- 有針對特定欄位更新
CURSOR cursor_name IS
    SELECT select_clause
    FROM from_clause
    WHERE where_clause
    FOR UPDATE OF column_name;

範例

首先，
接下來，遍歷游標中的行。
然後，獲取每個客戶的訂單數。
之後，根據訂單數量分配信用增量。
最後，更新客戶的信用。

DECLARE
    -- customer cursor 宣告一個可更新游標，用於更新credit_limit大於零顧客。
    CURSOR c_customers IS 
        SELECT 
            customer_id, 
            name, 
            credit_limit
        FROM 
            customers
        WHERE 
            credit_limit > 0 
        FOR UPDATE OF credit_limit;
    -- local variables
    l_order_count PLS_INTEGER := 0;
    l_increment   PLS_INTEGER := 0;
    
BEGIN
    FOR r_customer IN c_customers
    LOOP
        -- 獲取每個客戶的訂單數
        SELECT COUNT(*)
        INTO l_order_count
        FROM orders
        WHERE customer_id = r_customer.customer_id;        IF l_order_count >= 5 THEN
            l_increment := 5;
        ELSIF l_order_count < 5 AND l_order_count >=2 THEN
            l_increment := 2;
        ELSIF l_increment = 1 THEN
            l_increment := 1;
        ELSE 
            l_increment := 0;
        END IF;
        
        IF l_increment > 0 THEN
            -- 根據訂單數量來分配credit_limit可以增加的量，並更新(update)
            UPDATE 
                customers
            SET 
                credit_limit = credit_limit * ( 1 +  l_increment/ 100)
            WHERE 
                customer_id = r_customer.customer_id;
            
            -- 確認增加的量
            dbms_output.put_line('Increase credit for customer ' 
                || r_customer.NAME || ' by ' 
                || l_increment || '%' );
        END IF;
    END LOOP;
    
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Error code:' || SQLCODE);
            dbms_output.put_line('Error message:' || sqlerrm);
            RAISE;
            
END;
/

以上為PL/SQL的Cursor使用方式。

資料來源:
https://www.oracletutorial.com/plsql-tutorial/plsql-cursor/
https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-for-loop/
https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-with-parameters/
https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-variables/
https://www.oracletutorial.com/plsql-tutorial/oracle-cursor-for-update/