Cara Menggunakan Prosedur Tersimpan dalam MySQL

Biasanya, apabila bekerja dengan pangkalan data hubungan, anda mengeluarkan pertanyaan Bahasa Pertanyaan Berstruktur (SQL) individu untuk mendapatkan atau memanipulasi data, seperti SELECT, INSERT, UPDATE atau DELETE, terus dari dalam kod aplikasi anda. Kenyataan tersebut berfungsi dan memanipulasi jadual pangkalan data asas secara langsung. Jika sama pernyataan atau kumpulan pernyataan digunakan dalam pelbagai aplikasi mengakses pangkalan data yang sama, mereka sering diduplikasi secara individu aplikasi.

MySQL, serupa dengan banyak sistem pengurusan pangkalan data hubungan lain, menyokong penggunaan prosedur tersimpan. Prosedur tersimpan membantu kumpulan satu atau berbilang pernyataan SQL untuk digunakan semula di bawah nama biasa, merangkum logik perniagaan biasa dalam pangkalan data itu sendiri. Prosedur sedemikian boleh dipanggil daripada aplikasi yang mengakses pangkalan data untuk mendapatkan semula atau memanipulasi data dengan cara yang konsisten.

Menggunakan prosedur tersimpan, anda boleh membuat rutin boleh guna semula untuk perkara biasa tugas untuk digunakan merentas pelbagai aplikasi, menyediakan pengesahan data, atau menyampaikan lapisan tambahan keselamatan capaian data dengan mengehadkan pengguna pangkalan data daripada mengakses jadual asas secara langsung dan mengeluarkan pertanyaan sewenang-wenangnya.

Dalam tutorial ini, anda akan mempelajari apakah prosedur yang disimpan dan cara untuk cipta prosedur tersimpan asas yang mengembalikan data dan menggunakan kedua-dua input dan parameter output.

Menyambung ke MySQL dan Menyediakan Pangkalan Data Contoh

Dalam bahagian ini, anda akan menyambung ke pelayan MySQL dan mencipta sampel pangkalan data supaya anda boleh mengikuti contoh dalam panduan ini.

Untuk panduan ini, anda akan menggunakan koleksi kereta khayalan. Anda akan menyimpan butiran tentang kereta yang dimiliki pada masa ini, dengan pembuatan, model, tahun pembinaan, dan nilai.

Jika sistem pangkalan data SQL anda berjalan pada pelayan jauh, SSH ke pelayan anda dari mesin tempatan anda:

ssh sammy@your_server_ip

Kemudian buka pelayan MySQL segera, menggantikansammydengan nama pengguna MySQL anda akaun:

mysql -u sammy-p

Buat pangkalan data bernamaprosedur:

CREATE DATABASEprocedures;

Jika pangkalan data berjaya dibuat , anda akan menerima output seperti ini:

OutputQuery OK, 1 row affected (0.01 sec)

Untuk memilih pangkalan data prosedur, jalankan pernyataan USE berikut:

USEprocedures;

Anda akan menerima output berikut:

OutputDatabase changed

Selepas memilih pangkalan data, anda boleh mencipta jadual sampel di dalamnya. Kereta meja akan mengandungi data ringkas tentang kereta dalam pangkalan data. Ia akan memegang lajur berikut:

• make: Lajur ini menyimpan make untuk setiap kereta milik, dinyatakan menggunakan jenis data varchar dengan maksimum 100 aksara.
• model: Ini lajur memegang nama model kereta, dinyatakan menggunakan jenis data varchar dengan maksimum 100 aksara.
• tahun: Lajur ini menyimpan tahun binaan kereta dengan jenis data int untuk menyimpan nilai berangka.
• nilai: Lajur ini menyimpan nilai kereta menggunakan jenis data perpuluhan dengan maksimum 10 digit dan 2 digit selepas titik perpuluhan.

Buat jadual sampel dengan yang berikut arahan:

CREATE TABLE cars (    make varchar(100),    model varchar(100),    year int,    value decimal(10, 2));

Jika output berikut dicetak, jadual telah dibuat:

OutputQuery OK, 0 rows affected (0.00 sec)

Susulan itu, muatkan jadual kereta dengan beberapa data sampel oleh menjalankan operasi INSERT INTO berikut:

INSERT INTO carsVALUES('Porsche', '911 GT3', 2020, 169700),('Porsche', 'Cayman GT4', 2018, 118000),('Porsche', 'Panamera', 2022, 113200),('Porsche', 'Macan', 2019, 27400),('Porsche', '718 Boxster', 2017, 48880),('Ferrari', '488 GTB', 2015, 254750),('Ferrari', 'F8 Tributo', 2019, 375000),('Ferrari', 'SF90 Stradale', 2020, 627000),('Ferrari', '812 Superfast', 2017, 335300),('Ferrari', 'GTC4Lusso', 2016, 268000);

The INSERT INTO operasi akan menambah sepuluh contoh kereta sukan ke meja, dengan lima Porsche dan lima model Ferrari. Output berikut menunjukkan bahawa semua lima baris telah ditambah:

OutputQuery OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Dengan itu, anda sudah bersedia untuk mengikuti panduan yang lain dan mula menggunakan prosedur tersimpan dalam SQL.

Pengenalan kepada Prosedur Tersimpan

Prosedur tersimpan dalam MySQL dan dalam banyak pangkalan data hubungan lain sistem dinamakan objek yang mengandungi satu atau lebih arahan yang dibentangkan dan kemudian dilaksanakan oleh pangkalan data dalam urutan apabila dipanggil. Dalam contoh paling asas, prosedur tersimpan boleh menyimpan pernyataan biasa di bawah rutin boleh guna semula, seperti mendapatkan semula data daripada pangkalan data dengan penapis yang sering digunakan. Sebagai contoh, anda boleh membuat prosedur tersimpan untuk mendapatkan semula pelanggan kedai dalam talian yang membuat pesanan dalam tempoh yang terakhir diberikan bilangan bulan. Dalam senario yang paling kompleks, prosedur tersimpan boleh mewakili program yang luas yang menerangkan logik perniagaan yang rumit untuk aplikasi yang mantap.

Set arahan dalam prosedur tersimpan boleh termasuk pernyataan SQL biasa, seperti pertanyaan SELECT atau INSERT, yang mengembalikan atau memanipulasi data. Selain itu, prosedur tersimpan boleh menggunakan:

• Parameter yang dihantar ke prosedur yang disimpan atau dikembalikan melaluinya.
• Pembolehubah yang diisytiharkan untuk memproses data yang diambil terus dalam kod prosedur.
• Pernyataan bersyarat, yang membenarkan pelaksanaan bahagian kod prosedur disimpan bergantung pada syarat tertentu, seperti arahan IF atau CASE.
• Gelung, seperti WHILE, LOOP dan REPEAT, membenarkan melaksanakan bahagian kod beberapa kali, seperti untuk setiap baris dalam data yang diambil ditetapkan.
• Ralat mengendalikan arahan, seperti mengembalikan mesej ralat kepada pengguna pangkalan data yang mengakses prosedur.
• Panggilan ke prosedur tersimpan lain dalam pangkalan data.

Apabila prosedur dipanggil dengan namanya, enjin pangkalan data melaksanakannya seperti yang ditakrifkan, arahan melalui arahan.

Pengguna pangkalan data mesti mempunyai kebenaran yang sesuai untuk melaksanakan prosedur yang diberikan. Keperluan kebenaran ini menyediakan lapisan keselamatan, tidak membenarkan capaian pangkalan data langsung sambil memberikan akses kepada pengguna kepada prosedur individu yang dijamin selamat untuk dilaksanakan.

Prosedur tersimpan dilaksanakan secara langsung pada pelayan pangkalan data, melaksanakan semua pengiraan secara tempatan dan mengembalikan hasil kepada panggilan pengguna hanya apabila selesai.

Jika anda ingin menukar tingkah laku prosedur, anda boleh mengemas kini prosedur dalam pangkalan data, dan aplikasi yang menggunakannya akan secara automatik mengambil versi baharu. Semua pengguna akan segera bermula menggunakan kod prosedur baharu tanpa perlu menyesuaikannya aplikasi.

Berikut ialah struktur umum kod SQL yang digunakan untuk mencipta prosedur tersimpan:

DELIMITER //CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)BEGIN    instruction_1;    instruction_2;    . . .    instruction_n;END //DELIMITER ;

Arahan pertama dan terakhir dalam serpihan kod ini ialah DELIMITER // dan DELIMITER ;. Biasanya MySQL menggunakan simbol titik bertitik (;) untuk mengehadkan pernyataan dan menunjukkan bila ia bermula dan berakhir. jika anda laksanakan berbilang kenyataan dalam konsol MySQL yang dipisahkan dengan koma bertitik, ia akan dianggap sebagai arahan berasingan dan dilaksanakan secara bebas, satu demi satu. Walau bagaimanapun, prosedur tersimpan boleh sertakan berbilang arahan yang akan dilaksanakan secara berurutan apabila ia dipanggil. Ini menimbulkan kesukaran apabila cuba memberitahu MySQL untuk mencipta prosedur baru. Enjin pangkalan data akan menemui tanda titik koma dalam badan prosedur tersimpan dan fikir ia harus berhenti melaksanakan kenyataan. Dalam situasi ini, pernyataan yang dimaksudkan adalah keseluruhan kod penciptaan prosedur, bukan satu arahan dalam prosedur sendiri, jadi MySQL akan menyalahtafsirkan niat anda.

Untuk mengatasi had ini, anda menggunakan perintah DELIMITER untuk menukar pembatas dari ; ke // untuk tempoh PROSEDUR CIPTA panggilan. Kemudian, semua koma bertitik di dalam badan prosedur tersimpan akan dihantar ke pelayan apa adanya. Selepas keseluruhan prosedur selesai, pembatas ditukar kembali kepada ; dengan DELIMITER terakhir ;.

Inti kod untuk mencipta prosedur baharu ialah panggilan CREATE PROCEDURE diikuti dengan nama prosedur: procedure_name dalam contoh. Nama prosedur diikuti dengan senarai pilihan parameter yang akan diterima oleh prosedur. Bahagian terakhir ialah prosedur badan, disertakan dalam pernyataan BEGIN dan END. Di dalamnya ialah kod prosedur, yang boleh mengandungi satu pernyataan SQL seperti pertanyaan SELECT atau kod yang lebih kompleks.

Arahan END berakhir dengan //, pembatas sementara, bukannya koma bertitik biasa.

Dalam bahagian seterusnya, anda akan mencipta prosedur tersimpan asas tanpa parameter yang menyertakan satu pertanyaan.

Mencipta Simpanan Prosedur Tanpa Parameter

Dalam bahagian ini, anda akan mencipta prosedur tersimpan pertama anda yang merangkumi satu pernyataan SQL SELECT untuk mengembalikan senarai kereta milik yang ditempah mengikut jenama dan nilainya dalam susunan menurun.

Mulakan dengan melaksanakan pernyataan SELECT yang anda akan lakukan gunakan:

SELECT * FROM cars ORDER BY make, value DESC;

Pangkalan data akan mengembalikan senarai kereta daripada jadual kereta, mula-mula dipesan mengikut pembuatan dan kemudian, dalam satu pembuatan, mengikut nilai dalam tertib menurun:

Output --------- --------------- ------ ----------- 
| make    | model         | year | value     |
 --------- --------------- ------ ----------- 
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo    | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso     | 2016 | 268000.00 |
| Ferrari | 488 GTB       | 2015 | 254750.00 |
| Porsche | 911 GT3       | 2020 | 169700.00 |
| Porsche | Cayman GT4    | 2018 | 118000.00 |
| Porsche | Panamera      | 2022 | 113200.00 |
| Porsche | 718 Boxster   | 2017 |  48880.00 |
| Porsche | Macan         | 2019 |  27400.00 |
 --------- --------------- ------ ----------- 
10 rows in set (0.00 sec)

Ferrari yang paling berharga berada di bahagian atas senarai, dan Porsche yang paling tidak bernilai muncul di bawah.

Andaikan pertanyaan ini akan digunakan dengan kerap dalam berbilang aplikasi atau oleh berbilang pengguna dan andaikan anda ingin memastikan semua orang akan menggunakan cara yang sama untuk memesan keputusan. Untuk berbuat demikian, anda ingin membuat a prosedur tersimpan yang akan menyimpan pernyataan itu di bawah nama yang boleh digunakan semula prosedur.

Untuk mencipta prosedur tersimpan ini, laksanakan serpihan kod berikut:

DELIMITER //CREATE PROCEDUREget_all_cars()BEGIN    SELECT * FROM cars ORDER BY make, value DESC;END //DELIMITER ;

Seperti yang diterangkan dalam bahagian sebelumnya, perintah pertama dan terakhir (DELIMITER / / dan DELIMITER ;) beritahu MySQL untuk berhenti merawat aksara koma bertitik sebagai pembatas pernyataan untuk tempoh prosedur penciptaan.

Arahan SQL CREATE PROCEDURE diikuti dengan nama prosedurget_all_cars, yang anda boleh takrifkan untuk menerangkan dengan terbaik perkara yang dilakukan oleh prosedur. Selepas nama prosedur, terdapat sepasang kurungan () di mana anda boleh menambah parameter. Dalam contoh ini, prosedurnya tidak gunakan parameter, jadi kurungan kosong. Kemudian, antara perintah BEGIN dan END yang mentakrifkan permulaan dan akhir blok kod prosedur, pernyataan SELECT yang digunakan sebelum ini ditulis secara verbatim.

Pangkalan data akan bertindak balas dengan mesej kejayaan:

OutputQuery OK, 0 rows affected (0.02 sec)

Prosedurget_all_carskini disimpan dalam pangkalan data, dan apabila dipanggil, ia akan melaksanakan pernyataan yang disimpan seperti sedia ada.

Untuk melaksanakan prosedur tersimpan yang disimpan, anda boleh menggunakan perintah CALL SQL diikuti dengan nama prosedur. Cuba jalankan prosedur yang baru dibuat seperti ini:

CALLget_all_cars;

Nama prosedur,get_all_cars, adalah semua yang anda perlukan untuk menggunakan prosedur. Anda tidak perlu lagi menaip secara manual mana-mana bahagian pernyataan SELECT yang anda gunakan sebelum ini. Pangkalan data akan memaparkan keputusan sama seperti output daripada pernyataan SELECT dijalankan sebelum ini:

Output --------- --------------- ------ ----------- 
| make    | model         | year | value     |
 --------- --------------- ------ ----------- 
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo    | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso     | 2016 | 268000.00 |
| Ferrari | 488 GTB       | 2015 | 254750.00 |
| Porsche | 911 GT3       | 2020 | 169700.00 |
| Porsche | Cayman GT4    | 2018 | 118000.00 |
| Porsche | Panamera      | 2022 | 113200.00 |
| Porsche | 718 Boxster   | 2017 |  48880.00 |
| Porsche | Macan         | 2019 |  27400.00 |
 --------- --------------- ------ ----------- 
10 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Anda kini telah berjaya mencipta prosedur tersimpan tanpa sebarang parameter yang mengembalikan semua kereta dari jadual kereta yang dipesan dengan cara tertentu . Anda boleh menggunakan prosedur merentas berbilang aplikasi.

Dalam bahagian seterusnya, anda akan membuat prosedur yang menerima parameter untuk menukar tingkah laku prosedur bergantung pada input pengguna.

Atas ialah kandungan terperinci Cara Menggunakan Prosedur Tersimpan dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!