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

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

PHPz

リリース： 2023-04-25 17:27:02

オリジナル

4674 人が閲覧しました

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 サイトの他の関連記事を参照してください。