Oracle でストアド プロシージャを作成および実行する方法の例

Oracle は、多くの高度な機能を備えた非常に強力なデータベース管理システムであり、ストアド プロシージャもその 1 つです。ストアド プロシージャは、後で呼び出して使用できるようにデータベースに保存できる、データベース操作用の事前定義された SQL ステートメントのセットです。

Oracle では、ストアド プロシージャは、SQL とプログラミングを組み合わせた言語である PL/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 コード ブロック。ストアド プロシージャの特定のロジック実装が含まれます。

次のコード例は、2 つのパラメーターを受け入れ、その合計を出力する単純なストアド プロシージャを作成する方法を示しています。

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

ストアド プロシージャを実行します。

In In Oracle 、ストアド プロシージャを実行するには、EXECUTE または EXECUTE IMMEDIATE ステートメントが必要です。たとえば、上記のサンプル プログラムを実行するには、次のステートメントを使用できます。

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

ここでは、DECLARE ステートメントを使用して、使用する必要がある変数結果を宣言し、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;

上記のコードでは、3 つのパラメーターを含むストアド プロシージャ my_proc を宣言します。最初のパラメーター num は入力パラメーターで、2 番目のパラメーター str は双方向です。パラメーター、 3 番目のパラメーター cur は出力パラメーターです。

レコード セットの処理

ストアド プロシージャを使用してデータを操作する場合、多くの場合、クエリ結果のリストを返す必要があります。 Oracle は、カーソルと PL/SQL テーブルの 2 種類のレコードセットを提供します。

カーソル

カーソルは、クエリ結果を横断できる結果セットを返すデータ構造です。カーソルは明示的または暗黙的です。明示的カーソルでは、コード内でカーソル変数を宣言し、開いたり閉じたりする必要があります。暗黙的カーソルは、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;

この例では、従業員のカンマ区切りリストを受け入れる 2 つのパラメータを指定してストアド プロシージャ 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;

ここでは、PL/SQL を宣言する my_package という名前のパッケージを作成します。 num_list という名前のテーブル型と、その型を使用するストアド プロシージャ sum_nums です。 sum_nums は、num_list 型の引数を受け取り、それらの合計を計算します。

結論

Oracle では、ストアド プロシージャはデータベースを保守するための重要なツールの 1 つであり、効率的な実行機能とダイナミクスを備えています。また、ストアド プロシージャを使用すると、単一の SQL ステートメントを実行するだけでなく、いくつかのビジネス ロジックを実行できるため、再利用性と保守性が向上します。データベースに保存して共有し、複数のアプリケーションやプロセスからアクセスできるためです。ストアド プロシージャを使用することには多くのメリットがあり、短い記事ですべてを説明することは困難ですが、深く理解し、適用することができれば、実際の業務で大きなメリットが得られると考えています。

以上がOracle でストアド プロシージャを作成および実行する方法の例の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。