Cara menggunakan prosedur tersimpan MYSQL dan fungsi tersimpan

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;

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 ;

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;

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 ;

4 Penggunaan prosedur tersimpan dan fungsi tersimpan

• Panggil prosedur tersimpan:

CALL procedure_name([parameter_name]);

• Panggil fungsi tersimpan:

SELECT function_name([parameter_name]);

Menggunakan

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

CALL get_employee_info_by_id(1);

Menggunakan yang dibuat di atas

Fungsi tersimpan boleh dipanggil seperti ini: get_product_price_by_id

SELECT get_product_price_by_id(1001);

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, '高收入', '') AS note FROM employee;
END //
DELIMITER ;

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 ;

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 ;

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 ;

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!