mysql stored procedure stored function

MySQL ialah sistem pengurusan pangkalan data hubungan popular yang menyokong penciptaan dan penggunaan objek pangkalan data seperti prosedur tersimpan dan fungsi tersimpan. Dalam artikel ini, kita akan menyelami konsep, kegunaan, sintaks dan contoh prosedur tersimpan MySQL dan fungsi tersimpan.

1. Apakah prosedur tersimpan dan fungsi tersimpan?

Prosedur tersimpan dan fungsi tersimpan ialah dua jenis objek pangkalan data dalam MySQL. Mereka semua boleh dibuat di sisi pelayan MySQL dan boleh dipanggil dan dilaksanakan oleh program klien lain. Apa yang mereka ada persamaan ialah mereka boleh merangkum satu siri pernyataan SQL dan melaksanakan beberapa operasi pangkalan data atau logik perniagaan yang kompleks.

Prosedur tersimpan ialah koleksi pernyataan SQL yang dipratentukan, serupa dengan subrutin atau fungsi dalam bahasa pengaturcaraan lain. Ia ditulis sebagai satu pernyataan SQL dan disimpan pada pelayan, dan boleh dipanggil apabila diperlukan. Prosedur tersimpan boleh digunakan untuk melaksanakan beberapa keperluan perniagaan tertentu, seperti pemprosesan kumpulan data, pelaksanaan transaksi, pertanyaan pantas, dsb. Prosedur tersimpan boleh menerima parameter apabila dilaksanakan dan juga boleh mengembalikan hasil selepas pelaksanaan.

Fungsi tersimpan ialah prosedur yang boleh dipanggil dalam pertanyaan SQL, yang menerima parameter input dan mengembalikan satu nilai. Sama seperti prosedur tersimpan, fungsi tersimpan juga merupakan koleksi pernyataan SQL. Fungsi tersimpan biasanya digunakan untuk mengira, menukar, atau memanipulasi nilai dan mengembalikan hasilnya kepada pemanggil. Tidak seperti prosedur tersimpan, fungsi tersimpan hanya boleh mengembalikan satu nilai dan tidak boleh mengembalikan set hasil. Fungsi tersimpan digunakan secara meluas dalam pertanyaan dan laporan.

2. Kelebihan prosedur tersimpan dan fungsi tersimpan

1. Meningkatkan prestasi pangkalan data: Prosedur tersimpan dan fungsi tersimpan boleh dijalankan pada pelayan, mengurangkan beban penghantaran data dan komunikasi rangkaian , sekali gus meningkatkan prestasi pangkalan data.
2. Keselamatan yang lebih baik: Prosedur tersimpan dan fungsi tersimpan boleh bergantung pada kawalan akses untuk mengawal akses kepada jadual. Oleh kerana kod SQL disimpan dalam pelayan pangkalan data, pengguna tidak boleh mengaksesnya secara langsung dan hanya boleh menggunakannya dengan kebenaran tertentu.
3. Kod ringkas: Prosedur tersimpan dan fungsi tersimpan boleh menggunakan semula kod SQL, mengurangkan pertanyaan SQL berulang dan penulisan kod logik, mengurangkan jumlah kod dan menjadikannya lebih boleh diselenggara.
4. Kemudahalihan: Prosedur tersimpan dan fungsi tersimpan boleh digunakan semula dalam aplikasi yang berbeza dan mudah alih antara sistem pengendalian dan platform pangkalan data yang berbeza.

3. Cipta dan gunakan prosedur tersimpan

1. Buat prosedur tersimpan:

Sintaks untuk mencipta prosedur tersimpan adalah seperti berikut:

CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter datatype [, …])
BEGIN
SQL statements;
END;

• procedure_name: Nama prosedur tersimpan
• parameter: Parameter prosedur tersimpan, yang boleh menjadi parameter input IN (lalai), parameter output OUT dan INOUT (serentak input dan output) parameter.
• datatype: jenis data parameter
• Pernyataan SQL: pernyataan badan prosedur tersimpan

Sebagai contoh, prosedur tersimpan berikut akan berlalu dalam amaun dan ID pelanggan, dan Tambah amaun pada baki akaun pelanggan:

CREATE PROCEDURE add_amount(IN amt INT, IN cust_id INT)
BEGIN
UPDATE customers SET balance = balance + amt WHERE id = cust_id;
END;

2. Panggil prosedur tersimpan:

Sintaks untuk memanggil prosedur tersimpan adalah seperti berikut:

CALL procedure_name([parameter_value]);

Contohnya, kod berikut Prosedur tersimpan add_amount() yang baru dibuat akan dipanggil:

CALL add_amount(100, 1);

Kod di atas akan meningkatkan baki pelanggan dengan ID 1 sebanyak 100 yuan dalam meja pelanggan.

4. Cipta dan gunakan fungsi tersimpan

1. Buat fungsi tersimpan:

Sintaks untuk mencipta fungsi tersimpan adalah seperti berikut:

CREATE FUNCTION function_name ([parameter datatype [, …]])
RETURNS datatype
BEGIN
SQL statements;
RETURN return_value;
END;

• function_name: nama fungsi yang disimpan
• parameter: parameter fungsi yang disimpan
• datatype: jenis data parameter
• SQL penyata: penyataan utama fungsi tersimpan
• return_value: Nilai pulangan bagi fungsi tersimpan

Sebagai contoh, contoh berikut mencipta fungsi tersimpan yang mengira purata:

CREATE FUNCTION average (a INT, b INT)
RETURNS INT
BEGIN
DECLARE result INT;
SET result = (a + b) / 2;
RETURN result;
END;

2. Panggil fungsi tersimpan:

Sintaks untuk memanggil fungsi tersimpan adalah serupa dengan memanggil prosedur tersimpan:

SELECT function_name([parameter_value]);

Sebagai contoh, kod berikut akan memanggil fungsi tersimpan average() yang baru dibuat:

SELECT average(10, 20);

Kod di atas akan Mengembalikan 15, iaitu hasil daripada (10+20)/2.

5. Contoh prosedur tersimpan dan fungsi tersimpan

1. Contoh prosedur tersimpan

Prosedur tersimpan berikut akan mengira purata umur semua pelanggan secara automatik , dan letakkan hasilnya ke dalam jadual lain:

CREATE PROCEDURE calc_avg_age()
BEGIN
DECLARE avg_age FLOAT;
SELECT AVG(YEAR(CURDATE()) - YEAR(birth_date)) INTO avg_age FROM customers;
INSERT INTO statistics (name, value) VALUES ('avg_age', avg_age);
END;

2. Contoh fungsi tersimpan

Fungsi tersimpan berikut akan mengembalikan bilangan hari antara dua tarikh:

CREATE FUNCTION days_between (date1 DATE, date2 DATE)
RETURNS INT
BEGIN
DECLARE num_days INT;
SET num_days = DATEDIFF(date2, date1);
RETURN num_days;
END;

Sekarang, saya ingin tahu bilangan hari antara Krismas dan Tahun Baru 2022, kita boleh menggunakan kod berikut untuk memanggil fungsi yang disimpan di atas:

SELECT days_between('2022-12-25', '2023-01-01');

Kod di atas akan mengembalikan 7, iaitu antara dua tarikh bilangan hari.

6. Kesimpulan

Setakat ini, kami telah memahami konsep, sintaks dan contoh prosedur tersimpan MySQL dan fungsi tersimpan. Objek ini boleh membantu kami menyusun kod SQL dengan lebih baik dan meningkatkan prestasi pangkalan data serta kebolehselenggaraan. Dalam aplikasi praktikal, prosedur tersimpan dan fungsi tersimpan digunakan secara meluas dalam gudang data, risikan perniagaan, aplikasi dan bidang lain. Walau bagaimanapun, berhati-hati untuk tidak terlalu menggunakannya, kerana ini boleh mengurangkan kebolehbacaan dan kebolehselenggaraan kod anda.

Atas ialah kandungan terperinci mysql stored procedure stored function. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!