Mysql stored procedure pilih beberapa baris ke dalam variabel

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

1. Ringkasan Prosedur Tersimpan MySQL
2. Buat prosedur tersimpan menggunakan Query dan meja kerja MySQL
3. Buat prosedur tersimpan berparameter
4. 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

1. 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
2. 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
3. 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

1. Nama prosedur harus ditentukan setelah kata kunci Create Procedure
2. Setelah nama prosedur, daftar parameter harus ditentukan dalam tanda kurung. Daftar parameter harus dipisahkan koma
3. 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

1. Nama prosedur harus ditentukan setelah kata kunci CALL
2. 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

1. Tentukan jenis parameternya. Bisa IN, OUT atau INOUT
2. 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

Bagaimana cara menyimpan banyak baris dalam variabel di MySQL?

Bagaimana cara meneruskan beberapa nilai ke satu parameter dalam prosedur tersimpan di SQL?

Bagaimana cara MEMILIH banyak baris dalam prosedur tersimpan?

Bagaimana cara melipatgandakan baris menjadi satu variabel dalam SQL?