Prosedur tersimpan MySQL adalah objek yang berisi satu atau lebih pernyataan SQL untuk digunakan kembali. Mereka sering mengandung logika bisnis. Robert Sheldon mendemonstrasikan cara membuat dan memanggil stored procedure Show
Artikel ini adalah bagian dari rangkaian lanjutan Robert Sheldon tentang Belajar MySQL. Untuk melihat 11 item dalam seri ini, klik di sini Seperti kebanyakan sistem manajemen basis data relasional, MySQL mendukung penggunaan prosedur tersimpan yang dapat dipanggil sesuai permintaan oleh aplikasi berbasis data. Setiap prosedur tersimpan adalah objek database bernama yang berisi rutinitas yang terdiri dari satu atau lebih pernyataan SQL. Saat aplikasi memanggil stored procedure, MySQL mengeksekusi pernyataan tersebut dan mengembalikan hasilnya ke aplikasi. Rutin prosedur dapat mencakup berbagai pernyataan, termasuk bahasa definisi data (DDL) dan bahasa manipulasi data (DML). Prosedur tersimpan MySQL juga mendukung penggunaan parameter input dan output, menjadikannya alat yang sangat fleksibel untuk mengenkapsulasi logika pernyataan Prosedur tersimpan memungkinkan kode SQL untuk digunakan kembali sesering yang dibutuhkan, membantu menyederhanakan pengembangan aplikasi dan mengurangi kesalahan pernyataan. Pengembang tidak perlu menulis kueri yang rumit untuk setiap permintaan aplikasi, dan tim QA tidak perlu menghabiskan banyak waktu untuk memverifikasi kueri saat menguji aplikasi Kemampuan untuk menggunakan kembali kode juga mengurangi lalu lintas jaringan karena prosedur tersimpan dapat dipanggil dengan pernyataan Dalam artikel ini, saya mendemonstrasikan cara membuat dan memperbarui prosedur tersimpan, serta memanggilnya dengan pernyataan Mempersiapkan lingkungan MySQL AndaContoh dalam artikel ini didasarkan pada database 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 JATUH DATABASE JIKA ADA bepergian; BUAT DATABASE perjalanan; GUNAKAN bepergian; BUAT TABEL produsen ( manufacturer_id INT TIDAK DITANDATANGANI TIDAK NULL AUTO_INCREMENT, produsen VARCHAR(50) NOT NULL, buat_tanggal TIMESTAMP TIDAK NULL DEFAULT CURRENT_TIMESTAMP, last_update TIMESTAMP TIDAK NULL DEFAULT CURRENT_TIMESTAMP AKTIF UPDATE CURRENT_TIMESTAMP, UTAMA KUNCI (manufacturer_id) ) ENGINE=InnoDB AUTO_INCREMENT=1001; BUAT TABEL pesawat ( plane_id INT UNSIGNED TIDAK NULL AUTO_INCREMENT, pesawat VARCHAR(50) NOT NULL, manufacturer_id INT TIDAK DITANDATANGANI TIDAK NULL, tipe_mesin VARCHAR(50) NOT NULL, engine_count TINYINT TIDAK NULL, max_weight MEDIUMINT UNSIGNED TIDAK NULL, lebar sayap DECIMAL(5,2) NOT NULL, panjang_pesawat DECIMAL(5,2) NOT NULL, parking_area INT DIHASILKAN SELALU AS ((lebar sayap * plane_length)) STORED, icao_code CHAR(4) NOT NULL, buat_tanggal TIMESTAMP TIDAK NULL DEFAULT CURRENT_TIMESTAMP, last_update TIMESTAMP TIDAK NULL DEFAULT CURRENT_TIMESTAMP AKTIF UPDATE CURRENT_TIMESTAMP, UTAMA KUNCI (id_pesawat), KENDALA fk_manufacturer_id ASING ASING KUNCI (manufacturer_id) REFERENSI produsen (produsen_id) ) ENGINE=InnoDB AUTO_INCREMENT=101; Tabel 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 MASUKKAN KE DALAM produsen (produsen) NILAI ('Airbus'), ('Beechcraft'), ('Piper'); MASUKKAN KELUAR pesawat (pesawat, manufacturer_id, engine_type, engine_count, max_weight, lebar sayap, plane_length, icao_code) NILAI ('A380-800', 1001< . 65, 'jet', 4, 1267658, 261.65, 238. 62, 'A388'), ('A319neo Sharklet', 1001, 'jet', 2, 166449, 117.45, 111. 02, 'A319'), ('ACJ320neo (Versi Jet Perusahaan)', 1001, 'jet', 2, 174165, 117.45, 123. 27, 'A320'), ('A300-200 (A300-C4-200, F4-200)', . 08 1001, 'jet', 2, 363760, 147.08, 175. 50, 'A30B'), ('Beech 390 Premier I, IA, II (Raytheon Premier I)',< . 50 1002, 'jet', 2, 12500, 44.50, 46. 00, 'PRM1'), ('Beechjet 400 (dari/sama dengan MU-300-10 Diamond II)' . 50 1002, 'jet', 2, 15780, 43.50, 48. 42, 'BE40'), ('1900D', 1002, 'Turboprop', 2,17120, 57.75, 57. 67, 'B190'), ('PA-24-400 Comanche', 1003, 'piston', 1, 3600, 36.00, 24. 79, 'PA24'), ('PA-46-600TP Malibu Meridian, M600', 1003, 'Turboprop', 1, 6000, 43.17, 29. 60, 'P46T'), ('J-3 Cub', 1003 . 00, 'piston', 1, 1220, 38.00, 22. 42, 'J3'); Seperti pernyataan Membuat stored procedure di MySQLUntuk membuat stored procedure di MySQL, Anda harus menggunakan pernyataan Saat membuat pernyataan Sebagian besar prosedur tersimpan menggunakan pernyataan majemuk meskipun hanya berisi satu pernyataan SQL. Misalnya, rutin dalam pernyataan 1 2 3 4 5 6 7 8 9 10 11 12 13 14 DELIMITER // BUAT PROSEDUR get_plane_info() MULAI PILIH a. manufacturer_id, m. produsen, COUNT(*) AS plane_count, ROUND(AVG(a.lebar sayap), 2) AS avg_span, ROUND(AVG(a.panjang_pesawat), 2) AS avg_length DARI pesawat a INNER JOIN manufacturers m AKTIF a. manufacturer_id = m. manufacturer_id GROUP OLEH a. manufacturer_id PESAN OLEH m. produsen; AKHIR//
DELIMITER ; Contoh membuat prosedur bernama Pernyataan majemuk didefinisikan oleh sintaks Seperti yang Anda lihat, membuat stored procedure sederhana adalah proses yang cukup mudah. Minimal, Anda harus memberikan nama dan badan rutin. Namun, Anda pasti memperhatikan dimasukkannya dua pernyataan Secara default, MySQL menggunakan titik koma (;) sebagai pembatas pernyataan. Ini membantu memastikan bahwa klien mengirim pernyataan ke server secara keseluruhan tanpa membingungkannya dengan pernyataan lain. Namun, pernyataan majemuk dalam prosedur tersimpan mungkin menyertakan satu atau lebih pembatas, selain pembatas akhir definisi, dan semua pembatas ini dapat menyebabkan kebingungan saat meneruskan pernyataan Untuk mengatasi masalah ini, MySQL mendukung penggunaan pernyataan Saya juga ingin menunjukkan bahwa MySQL Workbench menyediakan alat (dalam bentuk tab) untuk membuat dan mengedit prosedur tersimpan. Alat ini mirip dengan yang digunakan untuk membuat dan mengedit tampilan. Ini memberikan rintisan untuk membuat pernyataan Gambar 1. Menambahkan prosedur tersimpan melalui Workbench GUI Untuk meluncurkan tab Prosedur Tersimpan, pilih database target di Navigator, lalu klik tombol buat prosedur tersimpan di bilah alat Workbench. (Tombolnya menyertakan tooltip Buat prosedur tersimpan baru dalam skema aktif di server yang terhubung. ) Ketika tab Prosedur Tersimpan muncul, Anda dapat mulai membuat pernyataan Anda. Setelah selesai, klik Terapkan. MySQL kemudian akan menambahkan beberapa komponen pernyataan yang diperlukan untuk membuat prosedur. Tinjau skrip terakhir, klik Terapkan sekali lagi, lalu klik Selesai. Prosedur tersimpan akan ditambahkan ke database target Tab Prosedur Tersimpan dapat berguna untuk membuat dan mengedit prosedur tersimpan, jadi saya ingin memastikan Anda mengetahuinya tersedia. Namun, saya lebih suka menggunakan tab kueri saat bekerja dengan prosedur tersimpan karena menurut saya lebih mudah dan menghemat langkah, jadi inilah pendekatan yang saya ambil untuk contoh di artikel ini Memverifikasi stored procedure yang baru dibuatSetelah Anda menjalankan pernyataan Gambar 2. Melihat prosedur tersimpan di Navigator Dari Navigator, Anda dapat membuka definisi prosedur di tab Stored Procedure dengan mengklik ikon kunci inggris di samping nama prosedur. Gambar 3 menunjukkan definisi prosedur saat Anda membuatnya, kecuali satu perbedaan. Sekarang menyertakan klausa Gambar 3. Melihat definisi prosedur pada tab Stored Procedure Klausa Selain klausa Cara lain untuk memverifikasi apakah prosedur tersimpan Anda telah dibuat adalah dengan menanyakan tampilan 1 2 PILIH * DARI skema_informasi. rutinitas DI MANA skema_rutin = 'perjalanan'; Pernyataan tersebut menyertakan klausa Anda dapat membatasi hasil lebih jauh dengan menentukan nama prosedur dalam klausa 1 2 3 4 PILIH definisi_rutin DARI skema_informasi. rutinitas DI MANA skema_rutin = 'perjalanan' DAN nama_rutin = 'get_plane_info'; Meskipun pernyataan hanya mengembalikan satu nilai, masih sulit untuk dibaca, terutama jika itu adalah pernyataan majemuk yang kompleks. Untuk melihat pernyataan secara keseluruhan, klik kanan nilai langsung di hasil dan klik Buka Nilai di Penampil. Pilih Teks jika belum dipilih. MySQL meluncurkan jendela terpisah yang menampilkan nilai, seperti yang ditunjukkan pada Gambar 4 Gambar 4. Memeriksa badan rutin prosedur tersimpan di Viewer Tentu saja, memverifikasi bahwa prosedur tersimpan ada tidak memberi tahu Anda apakah itu akan berfungsi seperti yang diharapkan. Untuk alasan ini, Anda juga harus menjalankan prosedur dan melihat hasil seperti apa yang dikembalikannya (selain menjalankannya melalui siklus QA yang tepat). Untuk ini, gunakan pernyataan 1 PANGGILAN get_plane_info; Saat Anda memanggil prosedur, MySQL menjalankan rutinitas tersimpan dan mengembalikan hasil pernyataan, yang ditunjukkan pada Gambar 5 Gambar 5. Melihat hasil setelah memanggil stored procedure Seperti yang diharapkan, pernyataan Menambahkan parameter input ke prosedur tersimpan
Pada bagian ini, saya mendemonstrasikan cara menambahkan parameter input ke definisi prosedur. (Saya akan membahas parameter keluaran di bagian selanjutnya. ) Sebelum saya membahasnya, Anda harus menyadari bahwa Anda tidak bisa begitu saja mengubah definisi prosedur seperti Anda dapat mengubah definisi tabel atau tampilan. Anda dapat memodifikasi karakteristik prosedur, tetapi tidak lebih. Untuk membuat pembaruan yang signifikan, Anda harus membatalkan prosedur dan kemudian membuatnya kembali, menggabungkan elemen baru Untuk membatalkan prosedur tersimpan, Anda dapat menggunakan pernyataan 1 JATUHKAN PROSEDUR JIKA ADA get_plane_info; Klausa 1 2 PILIH * DARI skema_informasi. rutinitas DI MANA skema_rutin = 'perjalanan'; Pernyataan itu sekarang harus mengembalikan set hasil kosong, kecuali jika Anda membuat prosedur atau fungsi tersimpan lainnya Setelah Anda menghapus prosedur tersimpan
Pernyataan 1 2 3 4 5 6 7 8 9 10 11 12 13 14 DELIMITER // BUAT PROSEDUR get_plane_info( IN in_name VARCHAR(50)) COMMENT 'mengambil informasi pesawat gabungan' MULAI PILIH a. manufacturer_id, m. produsen, COUNT(*) AS plane_count, ROUND(AVG(a.lebar sayap), 2) AS avg_span, ROUND(AVG(a.panjang_pesawat), 2) AS avg_length DARI pesawat a INNER JOIN manufacturers m AKTIF a. manufacturer_id = m. manufacturer_id DI MANA m. produsen = in_name; AKHIR// DELIMITER ; Definisi parameter dilampirkan dalam tanda kurung dan menyertakan kata kunci Pernyataan Saat memanggil prosedur tersimpan yang mengambil parameter input, Anda harus menyertakan nilai parameter dalam tanda kurung. Jika berupa nilai karakter, Anda harus menyertakannya dalam tanda kutip tunggal. Misalnya, pernyataan 1 PANGGILAN get_plane_info ('piper'); Saat MySQL menjalankan rutinitas prosedur, MySQL mengganti nilai Gambar 6. Memanggil prosedur tersimpan dengan parameter input Saat menentukan prosedur tersimpan Anda, Anda dapat menyertakan beberapa parameter Menambahkan parameter output ke prosedur tersimpanSekarang mari kita lihat cara menambahkan beberapa parameter Untuk menambahkan parameter, Anda harus membatalkan prosedur lagi dan kemudian menjalankan pernyataan 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 JATUHKAN PROSEDUR JIKA ADA get_plane_info; DELIMITER // BUAT PROSEDUR get_plane_info( IN in_name VARCHAR(50), OUT out_id INT UNSIGNED, KELUAR nama_keluar VARCHAR(50), KELUAR jumlah_pesawat SMALLINT UNSIGNED, KELUAR rata-rata_lebar sayap DECIMAL(5,2), KELUAR panjang_rata-rata DECIMAL(5,2)) COMMENT 'mengambil informasi pesawat gabungan' MULAI PILIH a. manufacturer_id, m. produsen, COUNT(*), ROUND(AVG(a.lebar sayap), 2), ROUND(AVG(a.panjang_pesawat), 2) INTO out_id, out_name, plane_count, avg_wingspan, avg_length DARI pesawat a INNER JOIN manufacturers m AKTIF a. manufacturer_id = m. manufacturer_id DI MANA m. produsen = in_name; AKHIR// DELIMITER ; Untuk setiap parameter keluaran, Anda harus menentukan kata kunci Saat Anda memanggil prosedur tersimpan yang mengembalikan parameter output, Anda dapat menangkap nilai parameter tersebut dengan mengirimkan variabel yang ditentukan pengguna untuk setiap parameter output untuk menyimpan nilainya, seperti yang ditunjukkan dalam pernyataan 1 2 HUBUNGI get_plane_info ('beechcraft', @out_id, @out_name, @jumlah_pesawat, @avg_wingspan, @avg_length); Pernyataan Cara yang tepat untuk menangani parameter keluaran dalam aplikasi Anda akan bergantung pada bahasa pemrograman yang Anda gunakan. Sementara itu, Anda kemudian dapat memverifikasi bahwa variabel Anda berisi nilai yang diharapkan dengan menjalankan pernyataan 1 PILIH @out_id, @out_name, @plane_count, @avg_wingspan, @avg_length; Gambar 7 menunjukkan hasil yang dikembalikan oleh pernyataan Gambar 7. Melihat nilai parameter keluaran rutin untuk pesawat Beechcraft Angka tersebut menunjukkan hasil saat Anda menentukan Angka 8. Melihat nilai parameter output rutin untuk pesawat Airbus Kedua parameter Mengubah prosedur tersimpan di MySQLMySQL mendukung pernyataan 1 2 3 UBAH PROSEDUR get_plane_info BACA SQL DATA SQL SECURITY INVOKER; Karakteristik Setelah Anda menjalankan pernyataan Gambar 9. Melihat definisi prosedur pada tab Stored Procedure Perhatikan bahwa pernyataan Bekerja dengan prosedur tersimpan di MySQLProsedur tersimpan dapat menawarkan banyak fleksibilitas, sambil membantu merampingkan pengembangan aplikasi. Namun, sebuah prosedur hanya seefektif rutin yang mendasarinya dan pernyataan SQL yang dikandungnya. Dalam latihan ini, saya menunjukkan cara membuat prosedur tersimpan yang rutinitasnya berisi pernyataan
Robert Sheldon 07 Oktober 2022 Robert Sheldon 07 Oktober 2022Memperkenalkan pernyataan MySQL INSERT 3 3 Lukas Vileikis 31 Januari 2023 Lukas Vileikis 31 Januari 2023Mengoptimalkan Query di MySQL. Mengoptimalkan Bacaan 1 Bisakah prosedur tersimpan mengembalikan nilai?Prosedur tersimpan menerima parameter input dan juga prosedur tersimpan mengembalikan nilai setelah eksekusi
Bisakah SQLKembalikan data menggunakan parameter keluaran. Jika Anda menentukan kata kunci output untuk parameter dalam definisi prosedur, prosedur dapat mengembalikan nilai parameter saat ini ke program pemanggil ketika prosedur keluar .
Bagaimana Anda membuat prosedur tersimpan mengembalikan nilai?Apa itu Return Value di SQL Server Stored Procedure? . Klik kanan dan pilih Jalankan Prosedur Tersimpan Jika prosedur, mengharapkan parameter, berikan nilainya dan klik OK Seiring dengan hasil yang Anda harapkan, prosedur tersimpan juga mengembalikan Nilai Pengembalian = 0 Bagaimana cara mengembalikan hasil yang diatur dalam prosedur tersimpan di MySQL?Dalam stored procedure, Anda cukup menjalankan SELECT , dan rangkaian hasil akan dikembalikan ke klien. Anda tidak dapat menggunakan set hasil tersebut di dalam rutinitas tersimpan lain di MySQL, tetapi aplikasi yang memanggil prosedur dapat menggunakannya seperti hasil kueri lainnya. |