mysqlストアドプロシージャカーソル-mysql チュートリアル-php.cn

mysqlストアドプロシージャカーソル

王林

リリース： 2023-05-12 09:44:36

オリジナル

4146 人が閲覧しました

MySQL ストアドプロシージャとカーソルの詳細な説明

MySQL データベースでは、ストアドプロシージャはコンパイル済みの SQL ステートメントのセットであり、データベース内のサブルーチンとみなすことができます。ストアドプロシージャは通常、複雑なビジネスロジックを処理し、アプリケーションとデータベース間の対話を簡素化し、データ処理の効率とセキュリティを向上させるために使用されます。カーソルは、ストアドプロシージャでクエリ結果セットを処理するために使用されるメカニズムです。

この記事では、MySQL ストアドプロシージャとカーソルの使い方と特徴を詳しく紹介します。

ストアドプロシージャ

1.1 ストアドプロシージャの構文構造

MySQL ストアドプロシージャの基本的な構文構造は次のとおりです:

CREATE PROCEDURE procedure_name [ (parameter_list) ] BEGIN
    -- 存储过程的逻辑实现
END;

ログイン後にコピー

where ,

procedure_name: ストアドプロシージャの名前;
parameter_list: ストアドプロシージャのパラメータリスト (0 個以上のパラメータを含めることができます。各パラメータは、パラメータ名とパラメータのタイプ構成;
BEGIN と END の間は、ストアドプロシージャの論理実装部分です。

たとえば、student テーブル (student) 内のすべてのレコードをクエリして次を返す単純なストアドプロシージャを定義します。

CREATE PROCEDURE get_all_students()
BEGIN
    SELECT * FROM student;
END;

ログイン後にコピー

1.2 ストアドプロシージャのパラメータ受け渡し

ストアドプロシージャでは、ストアドプロシージャを呼び出すときに渡すことができるパラメータを事前定義できます。 MySQL ストアドプロシージャは、IN、OUT、INOUT という 3 つのパラメータ受け渡しメソッドをサポートしています。

IN: 入力パラメータとして、ストアドプロシージャを呼び出すときにパラメータ値が渡されます。
OUT: 出力パラメーターとして、結果が計算され、ストアドプロシージャ内で返されます。
INOUT: 入力パラメータと出力パラメータの両方であり、ストアドプロシージャを呼び出すときにパラメータ値が渡され、ストアドプロシージャによって計算された結果が受け取ります。

次は、パラメーターの受け渡しを使用するストアドプロシージャの例です。

CREATE PROCEDURE add_two_numbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
    SET result = num1 + num2;
END;

ログイン後にコピー

上の例では、add_two_numbers ストアドプロシージャは 2 つの入力パラメーター num1 と num2 と、その結果を受け取ります。それらを追加することは、出力パラメーターの結果に値を割り当てることです。

次のステートメントを使用して、このストアドプロシージャを呼び出すことができます。

CALL add_two_numbers(2, 3, @result);
SELECT @result; -- 输出 5

ログイン後にコピー

1.3 ストアドプロシージャのプロセス制御

MySQL ストアドプロシージャは、IF、 CASE、WHILE、LOOP、LEAVEなどこれらのステートメントを使用して、複雑なロジック制御とビジネス処理を実装します。

次に、IF ステートメントと WHILE ステートメントを使用したストアドプロシージャの例を示します。

CREATE PROCEDURE calc_factorial(IN num INT, OUT result BIGINT)
BEGIN
    SET result = 1;
    WHILE num > 1 DO
        SET result = result * num;
        SET num = num - 1;
    END WHILE;
END;

ログイン後にコピー

上の例では、入力パラメータ num が 1 より大きい場合、WHILE ループを使用して計算が行われます。 num の階乗値を取得し、その結果を出力パラメータの結果に格納します。

次のステートメントを使用して、このストアドプロシージャを呼び出すことができます:

CALL calc_factorial(6, @result);
SELECT @result; -- 输出 720

ログイン後にコピー

Cursor

2.1 カーソルの基本概念

MySQL ストアドプロシージャでは、カーソルはストアドプロシージャ内のクエリ結果セットを走査するために使用されるメカニズムです。カーソルはクエリ結果セット内の現在の行を追跡し、必要に応じて行ごとに移動して、その行のデータを読み取ります。

カーソルを使用してカーソルを宣言、オープン、クローズ、および操作するには、次の 4 つのコマンドが必要です。

DECLARE: カーソルの宣言、カーソルの名前の定義、クエリステートメントとカーソルの種類など。
OPEN: カーソルを開き、クエリ結果セットをカーソルが指すバッファに保存します。
FETCH: カーソルが指す現在の行を取得し、現在の行の値を対応する変数に保存します。
CLOSE: カーソルを閉じ、カーソルが占有していたメモリを解放します。

次に、カーソルを使用するストアドプロシージャの例を示します。

CREATE PROCEDURE get_all_students()
BEGIN
    DECLARE done INT DEFAULT FALSE; -- 定义游标是否结束的标志
    DECLARE s_id INT; -- 存储查询结果中的学生编号
    DECLARE s_name VARCHAR(255); -- 存储查询结果中的学生姓名
    DECLARE cursor_students CURSOR FOR SELECT id, name FROM student; -- 声明游标，查询表 student 中的所有数据
    -- 打开游标
    OPEN cursor_students;
    -- 遍历游标指向的结果集
    read_loop: LOOP
        -- 获取游标指向的当前行
        FETCH cursor_students INTO s_id, s_name;
        -- 如果游标到达结果集的末尾，则跳出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 输出当前行的值
        SELECT s_id, s_name;
    END LOOP;
    -- 关闭游标
    CLOSE cursor_students;
END;

ログイン後にコピー

上の例では、cursor_students という名前のカーソルが宣言され、テーブル Student 内のすべてのデータをクエリします。カーソルを開いた後、read_loop ループ本体を使用してクエリ結果セットを走査し、FETCH コマンドを使用して現在の行の値を取得し、それを変数 s_id および s_name に格納して、コンソールに出力します。カーソルが結果セットの最後に到達すると、ループから抜け出します。カーソルの使用が終了したら、CLOSE コマンドを使用してカーソルを閉じる必要があります。

2.2 カーソルの特性とアプリケーションシナリオ

カーソルは開発者に結果セットをクエリする便利な方法を提供しますが、追加のメモリとリソースが必要なため、特別な料金を支払う必要があります。次の問題に注意してください。

カーソルは、特に大規模なデータセットを処理する場合にパフォーマンスに影響します。大規模な環境や同時実行性の高い環境ではカーソルを使用することはお勧めできません。サブクエリや JOIN 操作などの他の方法を優先する必要があります。
カーソルはストアドプロシージャ内でのみ使用でき、SQL ステートメント内で直接使用することはできません。
カーソルを使用するときは注意が必要です。カーソルが正しく閉じられていないと、MySQL データベースが大量のメモリリソースを占有し、場合によってはクラッシュする可能性があるためです。

通常、カーソルは次のシナリオに適しています。

複雑なクエリロジックをストアドプロシージャに実装する必要がある状況。
大規模なデータセットをバッチで処理する必要がある状況。
クエリ結果セットを行ごとに処理する必要がある状況。

概要

この記事では、MySQL データベースにおけるストアドプロシージャとカーソルの使い方と特徴を主に紹介します。ストアドプロシージャにより、アプリケーションとデータベース間の対話の効率とセキュリティが向上し、カーソルはクエリ結果セットを簡単に横断できます。ただし、カーソルを使用する場合は、メモリリークやパフォーマンスの問題を避けるために注意する必要があることに注意してください。

参考文献: