mysqlはストアドプロシージャを使用します-mysql チュートリアル-php.cn

mysqlはストアドプロシージャを使用します

王林

リリース： 2023-05-20 10:25:37

オリジナル

1524 人が閲覧しました

MySQL は現在最も人気のあるリレーショナルデータベース管理システムであり、ストアドプロシージャを使用して SQL ステートメントとプログラムの実行をより適切に維持および管理します。ストアドプロシージャは、よく使用される SQL ステートメントをカプセル化して複数の実行時の効率を向上させ、ランタイムリソースの使用量を削減できる、プリコンパイルされた SQL コードブロックです。

MySQL でのストアドプロシージャの使用

1. ストアドプロシージャの作成

基本的に言えば、ストアドプロシージャの作成とは、SQL ステートメントブロックを記述してシステムデータベースに作成することです。このコードブロックを保存します。具体的な例を次に示します。

CREATE PROCEDURE `procedure_name` (`parameter_list`)
BEGIN
    -- SQL 语句块
END

ログイン後にコピー

このうち、 procedure_name はストアドプロシージャの名前、 paramter_list はストアドプロシージャのパラメータリストです。入力パラメータと出力パラメータを含めることも、パラメータを含めないこともできます。

たとえば、ユーザー情報を取得するストアドプロシージャを作成します。

CREATE PROCEDURE `get_user_info`(user_id INT)
BEGIN
    SELECT * FROM `user` WHERE `user_id` = user_id;
END

ログイン後にコピー

ストアドプロシージャの SQL ステートメントブロックでは、MySQL でサポートされるすべての SQL ステートメント (SELECT、 INSERT、UPDATE、DELETE など。IF、WHILE、LOOP などの制御フローステートメントを使用して、より複雑なデータロジック処理を実現することもできます。

2. ストアドプロシージャを呼び出す

ストアドプロシージャを作成した後、CALL ステートメントを通じてストアドプロシージャを呼び出し、それにパラメータを渡すことができます。次の方法で上記で定義した

get_user_info

ストアドプロシージャを呼び出します: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>CALL procedure_name(param1, param2, ...)</pre><div class="contentsignin">ログイン後にコピー</div></div>3. ストアドプロシージャを削除します

時間が経つと、ストアドプロシージャが削除されます。必要に応じて、DROP PROCEDURE ステートメントを使用して削除できます:

CALL `get_user_info`(1);

ログイン後にコピー

例:

DROP PROCEDURE `procedure_name`;

ログイン後にコピー

4. ストアドプロシージャインスタンス

完全なストアドプロシージャインスタンスを見てみましょう。学生のテストのスコアを保存する

score

テーブルがあり、各学生の合計スコアと平均スコアを計算し、合計スコアに従って高い順に並べる必要があるとします。現時点では、ストアドプロシージャを使用して次のことを実現できます。 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>DROP PROCEDURE `get_user_info`;</pre><div class="contentsignin">ログイン後にコピー</div></div>このストアドプロシージャでは、最初に、使用する必要があるいくつかの変数を定義します。

total_score

は、スコアの合計を表します。 Student、avg_score は特定の生徒の平均点を表し、stu_id は特定の生徒の番号を表し、stu_name は特定の生徒の名前を表します。は一時テーブル tmp_score です。次に、

DECLARE CURSOR

ステートメントを使用して、学生テーブル内の学生番号をクエリするために使用されるカーソル変数 cursor_stu_id を宣言しました。このループでは、生徒番号に基づいてスコアテーブルをクエリし、生徒の合計スコアと平均スコアを計算し、それらを一時テーブルに保存します。最後に、SELECT ステートメントを使用して一時テーブルをクエリし、合計スコアを高いものから低いものに並べ替え、最後に DROP TABLE ステートメントを使用して一時テーブルを削除します。最後に、

CALL

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

CREATE PROCEDURE `calc_stu_score`()
BEGIN
     DECLARE `total_score` INT;
     DECLARE `avg_score` FLOAT;
     DECLARE `stu_id` INT DEFAULT 0;
     DECLARE `stu_name` VARCHAR(255);
     DECLARE `total` INT DEFAULT 0;
    DECLARE `cursor_stu_id` CURSOR FOR SELECT `stu_id` FROM `score` GROUP BY `stu_id`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

    DROP TEMPORARY TABLE IF EXISTS `tmp_score`;
    CREATE TEMPORARY TABLE `tmp_score` (
        `stu_id` INT NOT NULL,
        `stu_name` VARCHAR(255) NOT NULL,
        `total_score` INT NOT NULL,
        `avg_score` FLOAT NOT NULL,
        PRIMARY KEY (`stu_id`)
    );

    OPEN cursor_stu_id;

    stu_loop: LOOP
        FETCH cursor_stu_id INTO stu_id;
        IF finished = TRUE THEN 
            LEAVE stu_loop;
        END IF;

        SELECT `name` INTO stu_name FROM `student` WHERE `stu_id` = stu_id;

        SELECT SUM(`sorce`), COUNT(*) INTO total_score, total FROM `score` WHERE `stu_id` = stu_id;

        SET avg_score = total_score / NULLIF(total, 0);

        INSERT INTO `tmp_score` (`stu_id`, `stu_name`, `total_score`, `avg_score`) VALUES (stu_id, stu_name, total_score, avg_score);

    END LOOP;

    CLOSE cursor_stu_id;

    SELECT * FROM `tmp_score` ORDER BY `total_score` DESC;

    DROP TEMPORARY TABLE `tmp_score`;

END

ログイン後にコピー

summary ストアド プロシージャは、MySQL の非常に強力なツールです。これにより、データベースの運用プロセスが大幅に簡素化され、データベースのパフォーマンスとセキュリティが向上します。実際のアプリケーションでは、ストアド プロシージャを使用して多くの複雑なデータ処理操作を完了し、ビジネス ニーズをより適切に満たし、効率を向上させ、コストを削減できます。 
以上がmysqlはストアドプロシージャを使用しますの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。