MYSQL ストアド プロシージャとストアド関数の使用方法

1. ストアド プロシージャとストアド ファンクションとは

• ストアド プロシージャ (ストアド プロシージャ) は、データベースに保存される SQL ステートメントの集合です。ビジネス ロジックをカプセル化することにより、ストアド プロシージャはデータベースの実行効率とデータ アクセスのセキュリティを向上させることができます。

• ストアド ファンクション (ストアド ファンクション) は、データベースに保存された実行された一連の SQL ステートメントを指します。ストアド プロシージャとの違いは、ストアド ファンクションが戻り値を持つことです。

2. ストアド プロシージャ

CREATE PROCEDURE procedure_name([IN/OUT] parameter_name data_type)
BEGIN
    SQL Statement;
END;

を作成する employee という名前の従業員テーブルがすでにあり、ストアド プロシージャを作成する必要があるとします。 , 従業員の役職番号に基づいて従業員の名前と給与をクエリできます:

DELIMITER //
CREATE PROCEDURE get_employee_info_by_id(IN emp_id INT)
BEGIN
    SELECT name, salary FROM employee WHERE id = emp_id;
END //
DELIMITER ;

3. ストレージ関数を作成します

CREATE FUNCTION function_name([IN/OUT] parameter_name data_type) RETURNS data_type
BEGIN
    DECLARE variable_name data_type;
    SQL Statement;
    RETURN variable_name;
END;

すでに product## という名前の製品テーブルがあるとします。 #、今度は、製品番号に基づいて製品の単価をクエリできるストアド関数を作成する必要があります:

DELIMITER //
CREATE FUNCTION get_product_price_by_id(IN product_id INT) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE price DECIMAL(10,2);
    SELECT unit_price INTO price FROM product WHERE id = product_id;
    RETURN price;
END //
DELIMITER ;

4. ストアド プロシージャとストアド関数の使用

• ストアド プロシージャの呼び出し:

CALL procedure_name([parameter_name]);

• ストアド関数の呼び出し:

SELECT function_name([parameter_name]);

の使用上記で作成した get_employee_info_by_id ストアド プロシージャは次のように呼び出すことができます:

CALL get_employee_info_by_id(1);

上記で作成した

get_product_price_by_id ストアド関数を使用すると、次のように呼び出すことができます:

SELECT get_product_price_by_id(1001);

以下は一般的なストアド プロシージャとストアド関数の例:

5. if ステートメントを使用したスト​​アド プロシージャ

employee という名前の従業員テーブルがすでにあるとします。従業員の名前と給与をクエリするストアド プロシージャを作成します。給与が 5000 より大きい場合は、結果に「高収入」というメモを追加します。

DELIMITER //
CREATE PROCEDURE get_employee_info_with_note()
BEGIN
    SELECT name, salary, IF(salary > 5000, '高收入', '') AS note FROM employee;
END //
DELIMITER ;

6. ループ ステートメントを含むストアド プロシージャ

product という名前の製品テーブルがすでにあると仮定し、製品を保存するストアド プロシージャを作成する必要があるとします。すべての単価に 1.1 を掛けます。

DELIMITER //
CREATE PROCEDURE update_all_product_price()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE pid INT;
    DECLARE price DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT id, unit_price FROM product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO pid, price;
        IF done THEN
            LEAVE read_loop;
        END IF;
        UPDATE product SET unit_price = price * 1.1 WHERE id = pid;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

7. トランザクションを伴うストアド プロシージャ

order という名前の注文テーブルと order_item という注文詳細テーブルという名前の注文テーブルがすでにあると仮定します。次に、これら 2 つのテーブルにレコードを挿入するストアド プロシージャを作成する必要があります。

DELIMITER //
CREATE PROCEDURE insert_order(IN order_id INT, IN item_name VARCHAR(50), IN item_price DECIMAL(10,2), IN item_quantity INT)
BEGIN
    START TRANSACTION;
    INSERT INTO `order`(id) VALUES(order_id);
    SET @last_order_id = LAST_INSERT_ID();
    INSERT INTO order_item(order_id, item_name, item_price, item_quantity) VALUES(@last_order_id, item_name, item_price, item_quantity);
    COMMIT;
END //
DELIMITER ;

8. カーソルを使用したスト​​レージ関数

すでに

product という名前の製品テーブルがあり、次に製品テーブルにクエリを実行するストレージ関数を作成する必要があるとします。最大単価。

DELIMITER //
CREATE FUNCTION get_max_product_price() RETURNS DECIMAL(10,2)
BEGIN
    DECLARE max_price DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT unit_price FROM product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET max_price = 0;
    OPEN cur;
    FETCH cur INTO max_price;
    read_loop: LOOP
        FETCH cur INTO max_price;
        IF max_price IS NULL THEN
            LEAVE read_loop;
        END IF;
        IF max_price > @max_price THEN 
            SET @max_price = max_price;
        END IF;
    END LOOP;
    CLOSE cur;
    RETURN max_price;
END //
DELIMITER ;

9. ストアド プロシージャとストアド ファンクションの利点

• コードを再利用できるため、SQL ステートメントを繰り返し記述する必要がなくなります。

• フロー制御ステートメントをストアド プロシージャやストアド関数で使用して、複雑なロジックを処理できます。

• ストアド プロシージャやストアド関数を通じてデータベース操作をカプセル化し、効率とセキュリティを向上させることができます。

以上がMYSQL ストアド プロシージャとストアド関数の使用方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。