Prosedur Tersimpan dalam MySQL: Panduan Lengkap

Informatec Digital » Pangkalan Data » Prosedur Tersimpan dalam MySQL: Panduan Lengkap

Prosedur yang disimpan dalam penyata SQL kumpulan MySQL ke dalam unit boleh guna semula.
Mereka meningkatkan prestasi apabila berjalan pada pelayan, mengurangkan trafik rangkaian.
Mereka membenarkan keselamatan yang lebih besar dengan mengawal akses kepada data melalui kebenaran tertentu.
Mereka memudahkan pengoptimuman prestasi melalui penggunaan indeks dan mengelakkan kursor.

Apakah prosedur tersimpan dalam MySQL?

Prosedur tersimpan dalam MySQL ialah jujukan skrip atau blok kod SQL yang disimpan pada pelayan pangkalan data dan dilaksanakan apabila dipanggil. Ia merupakan cara yang ampuh untuk mengumpulkan pernyataan SQL yang berkaitan ke dalam satu unit logik yang boleh diguna semula. Prosedur tersimpan memudahkan logik pengaturcaraan, meningkatkan prestasi dan mempertingkatkan keselamatan pangkalan data.

Kelebihan menggunakan prosedur tersimpan

Prosedur tersimpan menawarkan banyak kelebihan dalam pembangunan aplikasi dan pentadbiran pangkalan data. Beberapa kelebihan utama ialah:

Modulariti dan penggunaan semula kodProsedur tersimpan membolehkan anda mengumpulkan pernyataan SQL yang berkaitan ke dalam unit logik, menjadikannya lebih mudah untuk menggunakannya semula dalam bahagian aplikasi yang berbeza. Ini meningkatkan kebolehselenggaraan kod dan mengurangkan pertindihan kod.
Prestasi yang lebih baik: Apabila dilaksanakan dalam pelayan pangkalan data Untuk penyimpanan data, prosedur tersimpan mengelakkan keperluan untuk menghantar berbilang pertanyaan daripada aplikasi klien. Ini mengurangkan trafik rangkaian dan meningkatkan prestasi aplikasi keseluruhan.
KeselamatanProsedur tersimpan boleh digunakan untuk mengawal akses kepada data dan menguatkuasakan peraturan keselamatan tertentu. Kebenaran pelaksanaan untuk prosedur tersimpan boleh diberikan kepada peranan pengguna, memberikan tahap keselamatan tambahan.
pengurangan ralatDengan merangkum logik pengaturcaraan dalam prosedur tersimpan, anda mengurangkan peluang untuk membuat ralat dalam aplikasi anda. Ini kerana prosedur tersimpan diuji dan dinyahpepijat sekali, dan kemudiannya boleh digunakan oleh berbilang aplikasi tanpa mengubah suai kod sumber.

Mencipta Prosedur Tersimpan

Mencipta prosedur tersimpan dalam MySQL adalah proses yang agak mudah. Untuk membuat prosedur tersimpan, pernyataan digunakan CREATE PROCEDURE. Di bawah ialah contoh asas untuk mencipta prosedur tersimpan yang memaparkan semua rekod dalam jadual:

Jenis Data dalam MySQL: Ciri dan Contoh

BUAT PROSEDUR sp_show_records()
BEGIN
PILIH * DARI jadual;
AKHIR

Dalam contoh ini, sp_mostrar_registros ialah nama prosedur tersimpan. Blok itu BEGIN y END mentakrifkan badan prosedur, yang dalam kes ini terdiri daripada pertanyaan mudah SELECT untuk memaparkan semua rekod dalam jadual bernama "jadual".

Parameter dalam prosedur tersimpan

Prosedur tersimpan boleh menerima parameter, membolehkannya menerima nilai luaran pada masa pelaksanaan. Parameter ditakrifkan dalam deklarasi prosedur dan digunakan dalam badan prosedur. Berikut ialah contoh prosedur tersimpan yang menerima dua parameter dan melaksanakan pertanyaan bersyarat :

BUAT PROSEDUR sp_search_product(DALAM nama VARCHAR(50), DALAM PERPULUHAN harga(8,2))
BEGIN
PILIH * DARI produk DI MANA nama SEPERTI CONCAT('%', nama, '%') DAN harga <= harga;
AKHIR

Dalam contoh ini, prosedur tersimpan sp_buscar_producto menerima dua parameter: nombre y precio. Perundingan itu SELECT Dalam kandungan prosedur, gunakan parameter ini untuk menapis rekod dalam jadual "produk" berdasarkan nama separa dan harga maksimum.

Pembolehubah tempatan dan kawalan aliran

Prosedur tersimpan dalam MySQL boleh menggunakan pembolehubah tempatan dan struktur kawalan aliran seperti syarat dan gelung. Ini membolehkan operasi yang lebih kompleks dan bersyarat dalam prosedur tersimpan. Di bawah ialah contoh prosedur tersimpan yang menggunakan pembolehubah dan kawalan aliran:

BUAT PROSEDUR sp_actualizar_stock(IN producto_id INT, IN cantidad INT)
BEGIN
ISYTIHKAN stok_sebenar INT;

PILIH stok KE DALAM stok semasa DARI produk WHERE id = product_id;

JIKA stok_sebenar >= kuantiti MAKA
KEMASKINI produk SET stok = stok – kuantiti WHERE id = product_id;
ELSE
PILIH 'Stok Tidak Mencukupi' SEBAGAI mesej;
TAMAT JIKA;
AKHIR

Dalam contoh ini, prosedur tersimpan sp_actualizar_stock mengambil dua parameter: producto_id y cantidad. Menggunakan pembolehubah tempatan yang dipanggil stock_actual untuk menyimpan nilai stok semasa produk. Kemudian gunakan struktur kawalan IF untuk menyemak sama ada stok mencukupi dan melakukan kemas kini dalam jadual "produk" atau memaparkan mesej ralat sebaliknya.

Berfungsi dalam prosedur tersimpan

Selain melaksanakan pertanyaan SQL, prosedur tersimpan dalam MySQL juga boleh mengandungi fungsi . Fungsi membolehkan anda melakukan pengiraan dan mengembalikan nilai dan bukannya hanya memaparkan hasil pertanyaan. Berikut ialah contoh prosedur tersimpan yang menggunakan fungsi untuk mengira jumlah harga pesanan:

Normalisasi Pangkalan Data: Panduan Lengkap dan Contoh Langkah demi Langkah

BUAT FUNGSI fn_kira_jumlah_harga(id_pesanan INT) MENGEMBALIKAN PERPULUHAN(8,2)
BEGIN
ISYTIHARKAN jumlah PERPULUHAN(8,2);

PILIH JUMLAH(harga * kuantiti) KE jumlah DARI butiran_pesanan DI MANA order_id = pesanan_id;

PULANGAN jumlah;
AKHIR

Dalam contoh ini, prosedur tersimpan mengandungi fungsi yang dipanggil fn_calcular_precio_total yang menerima parameter pedido_id dan mengembalikan jumlah harga pesanan. Fungsi ini menggunakan pembolehubah tempatan total untuk menyimpan hasil pengiraan dan kemudian mengembalikannya menggunakan pernyataan RETURN.

Prosedur Tersimpan Pratakrif

MySQL menyediakan beberapa prosedur tersimpan yang telah ditetapkan yang merangkumi pelbagai tugasan biasa. Prosedur tersimpan ini boleh digunakan secara langsung dalam pangkalan data tanpa menulis kod tambahan. Antara prosedur tersimpan yang telah ditetapkan yang paling biasa digunakan ialah:

COUNT(): Mengembalikan bilangan baris yang sepadan dengan syarat yang ditentukan.
SUM(): Mengira jumlah nilai dalam lajur tertentu.
AVG(): Mengira purata nilai dalam lajur tertentu.
MAX(): Mengembalikan nilai maksimum dalam lajur yang ditentukan.
MIN(): Mengembalikan nilai minimum dalam lajur yang ditentukan.

Prosedur tersimpan yang dipratentukan ini sangat dioptimumkan dan menawarkan prestasi yang lebih baik berbanding dengan menulis pertanyaan SQL yang setara dari awal.

Kebenaran keselamatan dan pelaksanaan

Dalam MySQL, kebenaran pelaksanaan untuk prosedur tersimpan boleh diberikan kepada peranan pengguna. Ini membolehkan anda mengawal akses kepada prosedur tersimpan dan melindungi integriti dan keselamatan data . Kebenaran pelaksanaan diuruskan melalui sistem pengurusan pengguna dan keistimewaan MySQL.

Adalah penting untuk memberikan kebenaran pelaksanaan untuk prosedur yang disimpan dengan sewajarnya bagi mengelakkan akses tanpa kebenaran dan memastikan kerahsiaan data. Pentadbir pangkalan data harus memberikan kebenaran pelaksanaan secara terhad dan menyemak semula keistimewaan pengguna secara berkala untuk mengekalkan persekitaran yang selamat.

Mengoptimumkan prosedur tersimpan

Untuk memastikan prestasi optimum, adalah penting untuk mengoptimumkan prosedur tersimpan dalam MySQL. Beberapa teknik pengoptimuman biasa termasuk:

Menggunakan indeks: Indeks pada lajur yang digunakan dalam pertanyaan dalam prosedur tersimpan boleh meningkatkan prestasi dengan ketara. Indeks mempercepatkan carian dan pengambilan data dengan mengurangkan masa pelaksanaan prosedur yang disimpan.
Hadkan penggunaan kursor: Penggunaan kursor yang berlebihan boleh memberi kesan negatif terhadap prestasi prosedur tersimpan. Sebaliknya, adalah disyorkan untuk menggunakan operasi set seperti JOIN y GROUP BY untuk memanipulasi data dan bukannya menggelung melalui baris satu demi satu.
Elakkan penggunaan subkueri secara berlebihan: Subkueri boleh berguna dalam senario tertentu, tetapi penggunaan berlebihan boleh menyebabkan prestasi yang lemah. Sebaliknya, disyorkan untuk digunakan JOIN y GROUP BY untuk menggabungkan dan memanipulasi data dengan cekap.
Lakukan ujian dan pelarasan: Adalah penting untuk melakukan ujian prestasi yang menyeluruh dan menala prosedur tersimpan seperti yang diperlukan. Ini melibatkan mengenal pasti kesesakan, mengukur masa pelaksanaan dan membuat perubahan pada reka bentuk atau logik prosedur untuk meningkatkan prestasi.

MySQL lwn. MariaDB: Duel of First Cousins

Kesimpulan

Kesimpulannya, prosedur tersimpan dalam MySQL adalah alat yang berkuasa untuk memudahkan logik pengaturcaraan, meningkatkan prestasi, dan meningkatkan keselamatan dalam pembangunan aplikasi dan pentadbiran pangkalan data. Mereka membenarkan pengumpulan pernyataan SQL yang berkaitan ke dalam unit logik dan boleh diguna semula, menawarkan kelebihan seperti modulariti, penggunaan semula kod, prestasi dan keselamatan yang lebih baik.

Mencipta prosedur tersimpan dalam MySQL adalah mudah, menggunakan pernyataan itu CREATE PROCEDURE, dan boleh menerima parameter untuk disesuaikan dengan senario yang berbeza. Selain itu, prosedur tersimpan boleh mengandungi pembolehubah tempatan, kawalan aliran dan fungsi, menjadikannya lebih fleksibel dan berkuasa.

Adalah penting untuk mengoptimumkan prosedur tersimpan menggunakan teknik seperti menggunakan indeks, mengehadkan penggunaan kursor dan subkueri, dan melaksanakan ujian prestasi yang meluas. Ini memastikan prestasi optimum dan pengalaman yang cekap untuk pengguna akhir.

Ringkasnya, prosedur tersimpan dalam MySQL ialah alat yang berharga untuk pembangun dan pentadbir pangkalan data, dan pelaksanaan serta pengoptimuman yang betul boleh membuat perbezaan dalam prestasi dan keselamatan aplikasi dan sistem pangkalan data.

Artikel berkaitan:

Prosedur Tersimpan dalam MySQL: Bagaimana untuk menggunakannya?