Kita dapat membuat stored procedure dengan parameter IN dan OUT untuk mendapatkan beberapa nilai dari tabel MySQL. Untuk membuatnya mengerti, kami mengambil contoh tabel bernama 'student_info' yang memiliki data berikut -
mysql> Select * from student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Jaipur | Literature | | 110 | Rahul | Chandigarh | History | | 125 | Raman | Bangalore | Computers | +------+---------+------------+------------+ 4 rows in set (0.01 sec)Sekarang, dengan membuat prosedur bernama 'select_studentinfo' sebagai berikut, kita dapat memilih nilai dari tabel 'student_info' dengan memberikan nilai 'id' -
mysql> DELIMITER // ; mysql> Create Procedure Select_studentinfo ( IN p_id INT, OUT p_name varchar(20),OUT p_address varchar(20), OUT p_subject varchar(20)) -> BEGIN -> SELECT name, address, subject INTO p_name, p_address, p_subject -> FROM student_info -> WHERE id = p_id; -> END // Query OK, 0 rows affected (0.03 sec)_Dalam kueri di atas, bersama dengan 1 parameter IN, ia juga mengambil 4 parameter OUT. Sekarang, aktifkan prosedur dengan nilai yang ingin kita berikan sebagai kondisi sebagai berikut -
Pada artikel ini, kita akan belajar tentang prosedur tersimpan di. Pada artikel ini, saya membahas dasar-dasar prosedur tersimpan yang mencakup hal-hal berikut
- Ringkasan Prosedur Tersimpan MySQL
- Buat prosedur tersimpan menggunakan Query dan meja kerja MySQL
- Buat prosedur tersimpan berparameter
- Jatuhkan Prosedur Tersimpan menggunakan kueri dan meja kerja MySQL
Prosedur tersimpan adalah pernyataan SQL yang dibungkus dalam pernyataan CREATE PROCEDURE. Prosedur tersimpan mungkin berisi pernyataan bersyarat seperti IF atau CASE atau Loops. Prosedur tersimpan juga dapat menjalankan prosedur tersimpan lain atau fungsi yang memodulasi kode
Berikut ini adalah manfaat dari stored procedure
- Mengurangi Lalu Lintas Jaringan. Beberapa Pernyataan SQL dienkapsulasi dalam prosedur tersimpan. Saat Anda menjalankannya, alih-alih mengirimkan banyak kueri, kami hanya mengirimkan nama dan parameter prosedur tersimpan
- Mudah dirawat. Prosedur tersimpan dapat digunakan kembali. Kami dapat mengimplementasikan logika bisnis dalam SP, dan dapat digunakan oleh aplikasi beberapa kali, atau modul aplikasi yang berbeda dapat menggunakan prosedur yang sama. Dengan cara ini, stored procedure membuat database lebih konsisten. Jika ada perubahan yang diperlukan, Anda hanya perlu melakukan perubahan pada prosedur tersimpan
- Aman. Prosedur tersimpan lebih aman daripada kueri AdHoc. Izin dapat diberikan kepada pengguna untuk menjalankan prosedur tersimpan tanpa memberikan izin ke tabel yang digunakan dalam prosedur tersimpan. Prosedur tersimpan membantu mencegah database dari SQL Injection
Sintaks untuk membuat prosedur MySQL Stored adalah sebagai berikut
Buat Prosedur [Nama Prosedur] ([Parameter 1], [Parameter 2], [Parameter 3] )
Mulai
Kueri SQL
Akhir
Dalam sintaks
- Nama prosedur harus ditentukan setelah kata kunci Create Procedure
- Setelah nama prosedur, daftar parameter harus ditentukan dalam tanda kurung. Daftar parameter harus dipisahkan koma
- Kueri dan kode SQL harus ditulis di antara kata kunci BEGIN dan END
Untuk menjalankan stored procedure, Anda dapat menggunakan kata kunci CALL. Di bawah ini adalah sintaks
HUBUNGI [Nama Prosedur] ([Parameter]. )
Dalam sintaks
- Nama prosedur harus ditentukan setelah kata kunci CALL
- Jika prosedur memiliki parameter, maka nilai parameter harus ditentukan dalam tanda kurung
Mari kita membuat prosedur tersimpan dasar. Untuk demonstrasi, saya menggunakan database sakila
Buat prosedur tersimpan sederhana
Misalkan Anda ingin mengisi daftar film. Output harus berisi film_id, judul, deskripsi, tahun rilis, dan kolom rating. Kode prosedurnya adalah sebagai berikut
1
2
3
4
5
6
7
DELIMITER //
BUAT PROSEDUR sp_GetMovies()
MULAI
pilih judul,deskripsi,release_year,rating from film;
AKHIR //
DELIMITER ;
Untuk membuat Prosedur Tersimpan MySQL, buka meja kerja MySQL Hubungkan ke Database MySQL salin-tempel kode di jendela editor kueri klik Jalankan
Anda dapat melihat prosedur di bawah prosedur tersimpan. Lihat tangkapan layar di bawah ini
Untuk menjalankan prosedur, jalankan perintah di bawah ini
1
HUBUNGI sp_GetMovies
Di bawah ini adalah tangkapan layar sebagian dari output
Buat prosedur menggunakan wizard meja kerja MySQL
Kita dapat menggunakan wizard meja kerja MySQL untuk membuat prosedur tersimpan. Misalkan Anda ingin mendapatkan daftar pelanggan dari database sakila. Untuk melakukannya, perluas skema sakila Klik kanan pada Stored Procedures Pilih Create a Stored procedure
Di jendela Prosedur baru, template buat telah dibuat. Di template, ganti nama prosedur dengan sp_getCustomers. Di blok kode, masukkan kueri berikut
1
pilih store_id, nama_depan, last_name, email, create_date, last_update from customer
Klik Terapkan. Kotak dialog, Terapkan skrip ke database terbuka. Pada layar Tinjau skrip, Anda dapat melihat kode prosedur tersimpan. Klik Terapkan
Skrip berhasil diterapkan, dan prosedur tersimpan sp_GetCustomer telah berhasil dibuat
Di MySQL Workbench, Anda dapat melihat stored procedure di bawah folder Stored Procedures dari skema sakila
Buat prosedur tersimpan berparameter
Parameter MySQL Stored Procedure memiliki tiga mode. IN, OUT, dan INOUT. Saat kita mendeklarasikan parameter tipe IN, aplikasi harus memberikan argumen ke stored procedure. Ini adalah mode default. Parameter tipe OUT, prosedur tersimpan mengembalikan output akhir yang dihasilkan oleh Pernyataan SQL. Saat kita mendeklarasikan parameter tipe INPUT, aplikasi harus memberikan argumen, dan berdasarkan argumen input;
Saat kami membuat prosedur tersimpan, parameter harus ditentukan dalam tanda kurung. Sintaksnya mengikuti
1
(IN . KELUAR . MASUK) (Parameter Nama [datatype(length)])
Dalam sintaks
- Tentukan jenis parameternya. Bisa IN, OUT atau INOUT
- Tentukan nama dan tipe data parameter
Contoh parameter IN
Misalkan kita ingin mendapatkan daftar film berdasarkan rating. Param_rating adalah parameter input, dan tipe datanya adalah varchar. Kode prosedurnya adalah sebagai berikut
1
2
3
4
5
6
DELIMITER //
BUAT PROSEDUR sp_GetMoviesByRating(IN rating varchar(50))
MULAI
pilih judul,deskripsi,release_year,rating from film where rating=rating;
AKHIR //
DELIMITER ;
Untuk mengisi daftar film dengan peringkat NC-17, kami meneruskan nilai NC-17 ke prosedur sp_getMoviesByRating()
1
HUBUNGI sp_GetMoviesByRating('NC-17')
Keluaran
Contoh parameter KELUAR
Misalkan kita ingin menghitung film yang memiliki rating PG-13. Total_Movies adalah parameter keluaran, dan tipe datanya adalah bilangan bulat. Jumlah film ditetapkan ke variabel OUT (Total_Movies) menggunakan kata kunci INTO. Kode prosedurnya adalah sebagai berikut
1
2
3
4
5
6
DELIMITER //
BUAT PROSEDUR sp_CountMoviesByRating(KELUAR Total_Movies int)
MULAI
pilih hitung(judul) INTO Total_Movies from film where rating='PG-13';
AKHIR //
DELIMITER ;
Untuk menyimpan nilai yang dikembalikan oleh prosedur, berikan variabel sesi bernama @PGRatingMovies
1
2
HUBUNGI sp_CountMoviesByRating(@PGRatingMovies)
Pilih @PGRatingMovies sebagai Film
Contoh parameter INOUT
Misalkan kita ingin mendapatkan jumlah total film berdasarkan rating. Parameter inputnya adalah param_rating dalam prosedur, dan tipe datanya adalah varchar(10). Parameter keluarannya adalah Movies_count, dan tipe datanya adalah bilangan bulat
Kode prosedur
1
2
3
4
5
6
DELIMITER //
BUAT PROSEDUR sp_CountMoviesByRating_Inout(masuk Movies_count int, In param_rating varchar(10))
MULAI
pilih hitung(judul) INTO Movies_count from film where rating=param_rating ;
AKHIR //
DELIMITER ;
Jalankan prosedur menggunakan kata kunci CALL dan simpan hasilnya dalam variabel sesi bernama @MoviesCount
1
2
HUBUNGI sp_CountMoviesByRating_Inout(@T,'PG-13');
Pilih @T sebagai Film
Lihat daftar stored procedure dalam database menggunakan query
Untuk melihat daftar stored procedure, Anda dapat melakukan query information_schema. tabel rutin. Ini berisi daftar prosedur tersimpan dan fungsi tersimpan yang dibuat di database. Untuk melihat daftar stored procedure yang dibuat di database sakila, jalankan query berikut. Selain itu, ini juga menyediakan pemilik, tanggal pembuatan, tipe keamanan, dan akses data SQL ke prosedur tersimpan
1
pilih nama_rutin, tipe_rutin,definer,created,security_type,SQL_Data_Access from information_schema.rutin di mana tipe_rutin=' and routine_schema='sakila';
Jatuhkan Prosedur Tersimpan
Untuk menghapus stored procedure, Anda dapat menggunakan perintah drop procedure. Sintaksnya mengikuti
Drop procedure [IF EXISTS]
Dalam sintaks, nama stored procedure harus diikuti dengan kata kunci Drop Procedure. Jika Anda ingin menghapus prosedur sp_getCustomers dari database sakila, Anda dapat menjalankan kueri berikut
1
Jatuhkan prosedur sp_getCustomers
Saat Anda mencoba menghapus prosedur yang tidak ada di database, kueri menunjukkan kesalahan
KESALAHAN 1305 (42000). PROSEDUR sakila. getCustomer tidak ada
Untuk menghindarinya, Anda dapat menyertakan opsi [JIKA ADA] dalam perintah prosedur jatuhkan. Saat Anda menyertakan kata kunci JIKA ADA, alih-alih kesalahan, kueri mengembalikan peringatan
Kueri OK, 0 baris terpengaruh, 1 peringatan (0. 01 dtk) 1305 PROSEDUR sakila. getCustomer tidak ada
Jatuhkan Prosedur Tersimpan menggunakan wizard meja kerja MySQL
Anda dapat menggunakan wizard meja kerja MySQL untuk membatalkan prosedur. Untuk membatalkan prosedur apa pun, luaskan skema sabila Perluas Prosedur Tersimpan Klik kanan pada sp_GetMovies Klik pada Prosedur Tersimpan
Kotak dialog terbuka. Anda dapat memilih untuk meninjau prosedur sebelum menjatuhkannya, atau Anda dapat membatalkannya tanpa meninjaunya. Merupakan praktik yang baik untuk meninjau objek database sebelum menghapusnya, jadi pilih Tinjau SQL