Cara menggunakan prosedur tersimpan MYSQL dan fungsi tersimpan-tutorial mysql-php.cn

Cara menggunakan prosedur tersimpan MYSQL dan fungsi tersimpan

WBOY

Lepaskan： 2023-06-03 16:55:21

ke hadapan

1065 orang telah melayarinya

1. Apakah prosedur tersimpan dan fungsi tersimpan

Prosedur tersimpan (Prosedur Tersimpan) ialah satu set pernyataan SQL yang disimpan dalam pangkalan data. Dengan merangkum logik perniagaan, prosedur tersimpan boleh meningkatkan kecekapan pelaksanaan pangkalan data dan keselamatan capaian data.
Fungsi Tersimpan merujuk kepada set pernyataan SQL yang dilaksanakan yang disimpan dalam pangkalan data Perbezaan daripada prosedur tersimpan ialah fungsi tersimpan mempunyai nilai pulangan.

2 Cipta prosedur tersimpan

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

Salin selepas log masuk

Andaikan kita sudah mempunyai jadual pekerja bernama employee, dan kini kita perlu mencipta prosedur tersimpan yang boleh Membuat Pertanyaan nama dan gaji pekerja:

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

Salin selepas log masuk

3. Buat fungsi tersimpan

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;

Salin selepas log masuk

Katakan kita sudah mempunyai jadual produk bernama product, dan sekarang kita perlu mencipta fungsi tersimpan, yang boleh Tanya harga unit produk mengikut nombor produk:

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 ;

Salin selepas log masuk

4 Penggunaan prosedur tersimpan dan fungsi tersimpan

Panggil prosedur tersimpan:
<🎜. >

CALL procedure_name([parameter_name]);

Salin selepas log masuk

Panggil fungsi tersimpan:

SELECT function_name([parameter_name]);

Salin selepas log masuk

Menggunakan

yang dibuat di atas, prosedur tersimpan boleh dipanggil seperti ini: get_employee_info_by_id

CALL get_employee_info_by_id(1);

Salin selepas log masuk

Menggunakan yang dibuat di atas

Fungsi tersimpan boleh dipanggil seperti ini: get_product_price_by_id

SELECT get_product_price_by_id(1001);

Salin selepas log masuk

Berikut adalah beberapa contoh biasa prosedur tersimpan dan fungsi tersimpan:

5. Prosedur tersimpan dengan penyata if

Katakan kita sudah mempunyai jadual pekerja bernama

Sekarang kita perlu membuat prosedur tersimpan untuk menanyakan nama dan gaji pekerja Jika gaji lebih daripada 5000, tambah nota kepada keputusan: "Pendapatan Tinggi". employee

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

Salin selepas log masuk

6. Prosedur tersimpan dengan pernyataan gelung

Katakan kita sudah mempunyai jadual produk bernama

, dan kini kita perlu mencipta prosedur tersimpan untuk mendarabkan semua harga unit produk 1.1. product

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 ;

Salin selepas log masuk

7. Prosedur tersimpan dengan transaksi

Katakan kita sudah mempunyai jadual pesanan bernama

dan jadual butiran pesanan bernama order, dan kini kita perlu mencipta sisipan prosedur tersimpan rekod ke dalam dua jadual ini. 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 ;

Salin selepas log masuk

8. Fungsi storan dengan kursor

Katakan kita sudah mempunyai jadual produk bernama

, dan kini kita perlu mencipta fungsi storan untuk menanyakan harga unit maksimum dalam jadual produk . 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 ;

Salin selepas log masuk

9. Kelebihan prosedur tersimpan dan fungsi tersimpan

Kod boleh digunakan semula untuk mengelakkan pernyataan SQL berulang
Pernyataan kawalan aliran boleh digunakan dalam prosedur tersimpan dan fungsi tersimpan untuk mengendalikan logik yang kompleks;

Atas ialah kandungan terperinci Cara menggunakan prosedur tersimpan MYSQL dan fungsi tersimpan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!