So verwenden Sie gespeicherte MYSQL-Prozeduren und gespeicherte Funktionen

1. Was sind gespeicherte Prozeduren und gespeicherte Funktionen?

• Eine gespeicherte Prozedur (Stored Procedure) ist eine Reihe von SQL-Anweisungen, die in der Datenbank gespeichert sind. Durch die Kapselung der Geschäftslogik können gespeicherte Prozeduren die Effizienz der Datenbankausführung und die Datenzugriffssicherheit verbessern.

• Gespeicherte Funktion bezieht sich auf eine Reihe ausgeführter SQL-Anweisungen, die in einer Datenbank gespeichert sind. Der Unterschied zu gespeicherten Prozeduren besteht darin, dass gespeicherte Funktionen einen Rückgabewert haben.

2. Erstellen Sie eine gespeicherte Prozedur Jobnummer des Mitarbeiters:

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

3. Erstellen Sie eine Speicherfunktion 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的订单详情表，现在需要创建一个存储过程，向这两张表中插入一条记录。

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 ;

Angenommen, wir haben bereits eine Produkttabelle mit dem Namen product und müssen jetzt eine Speicherfunktion erstellen, die den Stückpreis abfragen kann das Produkt basierend auf der Produktnummer:

rrreee

4. Verwendung von gespeicherten Prozeduren und gespeicherten Funktionen

• Aufrufen gespeicherter Prozeduren:

• rrreee

• Aufrufen gespeicherter Funktionen:

rrreee

Verwenden des get_employ ee_info_by_id Die oben erstellte gespeicherte Prozedur kann wie folgt aussehen: 🎜rrreee🎜Mit der get_product_price_by_id kann die oben erstellte gespeicherte Funktion wie folgt aufgerufen werden: 🎜rrreee🎜Hier sind einige Beispiele für gängige gespeicherte Prozeduren und gespeicherte Prozeduren Funktionen: 🎜🎜5. Gespeicherte Prozeduren mit if-Anweisungen 🎜🎜Angenommen, wir haben bereits eine Mitarbeitertabelle mit dem Namen employe. Jetzt müssen wir eine gespeicherte Prozedur erstellen, um den Namen und das Gehalt des Mitarbeiters abzufragen größer als 5000, fügen Sie dem Ergebnis einen Hinweis hinzu: „Hohes Einkommen“. 🎜rrreee🎜6. Gespeicherte Prozedur mit Schleifenanweisung🎜🎜Angenommen, wir haben bereits eine Produkttabelle mit dem Namen product und müssen jetzt eine gespeicherte Prozedur erstellen, um die Stückpreise aller Produkte mit 1,1 zu multiplizieren. 🎜rrreee🎜7. Gespeicherte Prozedur mit Transaktionen🎜🎜Angenommen, wir haben bereits eine Bestelltabelle mit dem Namen order und eine Bestelldetailtabelle mit dem Namen order_item. Jetzt müssen Sie eine gespeicherte Prozedur erstellen um einen Datensatz in diese beiden Tabellen einzufügen. 🎜rrreee🎜8. Speicherfunktion mit Cursor🎜🎜Angenommen, wir haben bereits eine Produkttabelle mit dem Namen product und müssen jetzt eine gespeicherte Funktion erstellen, um den maximalen Stückpreis in der Produkttabelle abzufragen. 🎜rrreee🎜9. Vorteile gespeicherter Prozeduren und gespeicherter Funktionen🎜🎜🎜🎜Code kann wiederverwendet werden, um das wiederholte Schreiben von SQL-Anweisungen zu vermeiden;🎜🎜🎜🎜Flusskontrollanweisungen können in gespeicherten Prozeduren und gespeicherten Funktionen verwendet werden, um komplexe Logik zu verarbeiten;🎜🎜 🎜🎜Datenbankoperationen können durch gespeicherte Prozeduren und gespeicherte Funktionen gekapselt werden, um die Effizienz und Sicherheit zu verbessern. 🎜🎜🎜

Das obige ist der detaillierte Inhalt vonSo verwenden Sie gespeicherte MYSQL-Prozeduren und gespeicherte Funktionen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!