實例講解如何在 Oracle 中建立和執行預存程序

Oracle 是一個非常強大的資料庫管理系統，它擁有許多高級的功能和特性，其中預存程序是其中之一。預存程序是一組針對資料庫操作的預先定義的 SQL 語句，它可以儲存在資料庫中，以供以後呼叫使用。

在 Oracle 中，預存程序以 PL/SQL 語言編寫，它是一種結合了 SQL 和程式設計的語言。 PL/SQL 具有很強的資料操作能力和過程控制能力，可以方便地編寫出高效的預存程序來。

預存程序的好處

預存程序的主要好處是可以增加資料庫的執行效率，並減少網路通訊的開銷。因為預存程序已經預先編譯和最佳化，所以在執行時不需要重複進行解析和最佳化，可以直接呼叫執行。此外，預存程序還可以透過參數來實現動態化的操作，不僅可以簡化程式碼，還可以避免 SQL 注入等風險。

預存程序的建立與執行

以下介紹如何在 Oracle 中建立和執行預存程序。

建立預存程序

在Oracle 中，建立預存程序需要使用CREATE PROCEDURE 語句，語法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] parameter_type [, ...])]
[IS | AS]
BEGIN
      pl/sql_code_block;
END [procedure_name];

其中：

• #CREATE PROCEDURE：建立預存程序的語句。
• OR REPLACE：可選參數，如果指定了該參數，則表示建立的預存程序已存在時，將其替換。
• procedure_name：預存程序的名稱。
• parameter_name：可選的輸入和/或輸出參數，用於指定預存程序的輸入和輸出。
• parameter_type：參數的類型，可以是資料型別如 VARCHAR2、NUMBER，也可以是遊標類型，如 SYS_REFCURSOR。
• IS | AS：可選參數，用於指定預存程序的語言類型，IS 表示開始（PL/SQL 區塊），AS 表示結束（PL/SQL 區塊）。
• pl/sql_code_block：PL/SQL 程式碼區塊，它包含了預存程序的特定邏輯實作。

下面範例程式碼示範如何建立一個簡單的儲存過程，它接受兩個參數並輸出它們的和：

CREATE OR REPLACE PROCEDURE add_nums(
    num1 IN NUMBER,
    num2 IN NUMBER,
    sum OUT NUMBER
)
IS
BEGIN
    sum := num1 + num2;
END add_nums;

執行預存程序

在Oracle 中，執行預存程序需要使用EXECUTE 或EXECUTE IMMEDIATE 語句。例如，執行上述範例程序，可以使用如下的語句：

DECLARE
    result NUMBER;
BEGIN
    add_nums(10, 20, result);
    DBMS_OUTPUT.PUT_LINE('The sum is: ' || result);
END;

這裡我們使用 DECLARE 語句來宣告需要使用的變數 result，並呼叫 add_nums 儲存過程，並將結果輸出到螢幕上。

參數類型

在預存程序中，參數可以是輸入參數、輸出參數或雙向參數。

• 輸入參數：指定預存程序的輸入。
• 輸出參數：指定預存程序的輸出。
• 雙向參數：既可以進行輸入，也可以進行輸出。

宣告參數類型的方法如下：

(param_name [IN | OUT | IN OUT] param_type [, ...])

在這個宣告中，[IN | OUT | IN OUT] 是可選的參數，用來指定參數的型別。如果不指定參數類型，則預設為 IN 類型，即輸入參數。

範例程式碼：

CREATE OR REPLACE PROCEDURE my_proc (
    num IN NUMBER,
    str IN OUT VARCHAR2,
    cur OUT SYS_REFCURSOR
)
IS
BEGIN
    -- 逻辑实现
END my_proc;

在上述程式碼中，我們宣告了一個包含三個參數的預存程序my_proc，第一個參數num 是輸入參數，第二個參數str 是雙向參數，第三個參數cur 是輸出參數。

紀錄集處理

用預存程序來操作資料時常常需要傳回查詢結果清單。 Oracle 提供了兩種類型的紀錄集：遊標和 PL/SQL 表。

遊標

遊標是一種傳回結果集的資料結構，它可以遍歷查詢結果。遊標可以是顯式或隱式的，顯式遊標需要聲明一個遊標變量，並在程式碼中開啟和關閉它，隱式遊標則由 Oracle 自動建立和管理。

下面是一個示範如何使用遊標的預存程序：

CREATE OR REPLACE PROCEDURE get_employee(
    id_list IN VARCHAR2,
    emp_cur OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN emp_cur FOR 'SELECT * FROM employees WHERE id IN (' || id_list || ')';
END get_employee;

在這個例子中，我們宣告了一個包含兩個參數的預存程序get_employee，它接受一個以逗號分隔的員工ID 清單作為輸入參數，傳回一個包含所選員工資訊的遊標emp_cur。

PL/SQL 表

PL/SQL 表是一種類似陣列的資料結構，它可以儲存一組值。 PL/SQL 表在預存程序中有很多實際應用，例如將一組資料傳遞給預存程序等。

在Oracle 中，可以在預存程序中宣告並使用PL/SQL 表，例如以下程式碼：

CREATE OR REPLACE PACKAGE my_package
IS
    TYPE num_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

    PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER);
END my_package;

CREATE OR REPLACE PACKAGE BODY my_package
IS
    PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER)
    IS
        total NUMBER := 0;
    BEGIN
        FOR indx IN 1 .. nums.COUNT LOOP
            total := total + nums(indx);
        END LOOP;
        sum := total;
    END sum_nums;
END my_package;

在這裡，我們建立了一個名為my_package 的包，其中聲明了一個名為num_list 的PL/SQL 表類型和一個使用該類型的預存程序sum_nums。 sum_nums 接受一個 num_list 類型的參數，並計算它們的總和。

結論

在 Oracle 中，預存程序是重要的維護資料庫的工具之一，它具有高效的執行能力和動態性。我們也可以透過預存程序讓其執行一些業務邏輯，而不是只執行單一的 SQL 語句，如此能夠提高可重複使用性和可維護性。因為它們可以被儲存在資料庫中，並且能夠被多個應用程式或程序共享和存取。使用預存程序的好處很多，僅靠短短的文章很難覆蓋它們的全部，但是我們相信，只要深入了解和應用，就會在實際工作中獲益匪淺。

以上是實例講解如何在 Oracle 中建立和執行預存程序的詳細內容。更多資訊請關注PHP中文網其他相關文章！