Prosedur Tersimpan MySQL (Buat, Daftar, Ubah, &Jatuhkan)

Pendahuluan

Prosedur tersimpan MySQL mengelompokkan banyak tugas menjadi satu dan simpan tugas di server untuk digunakan di masa mendatang.

Prosedur tersimpan menyederhanakan manajemen basis data dan mengurangi lalu lintas jaringan. Misalnya, mengeluarkan kueri ke server MySQL memproses kueri dan mengembalikan hasilnya. Menggunakan prosedur tersimpan menyimpan kueri di server sehingga dapat dieksekusi nanti.

Dalam tutorial ini, Anda akan belajar membuat, membuat daftar, mengubah, dan menghapus prosedur tersimpan.

Prasyarat

• MySQL Server dan MySQL Workbench terinstal
• Akun pengguna MySQL dengan hak akses root

Apakah Prosedur Tersimpan di MySQL?

Prosedur tersimpan MySQL sudah dikompilasi Pernyataan SQL disimpan dalam sebuah database. Mereka adalah subrutin yang berisi nama, daftar parameter, dan pernyataan SQL.

Semua sistem basis data relasional mendukung prosedur tersimpan dan tidak memerlukan paket runtime-environment tambahan.

Bagaimana Cara Menggunakan Prosedur Tersimpan?

Untuk memanggil prosedur tersimpan, Anda dapat menggunakan CALL pernyataan atau prosedur tersimpan lainnya. Pertama kali prosedur tersimpan dipanggil, MySQL mencarinya di katalog database, mengkompilasi kode, menempatkannya di memori cache , dan menjalankannya.

Proses selanjutnya dalam sesi yang sama mengeksekusi prosedur tersimpan dari memori cache, membuatnya sangat berguna untuk tugas yang berulang.

Prosedur tersimpan menggunakan parameter untuk memberikan nilai dan menyesuaikan hasil. Parameter digunakan untuk menentukan kolom dalam tabel tempat kueri beroperasi dan mengembalikan hasil.

Prosedur tersimpan juga dapat menyertakan IF , CASE , dan LOOP pernyataan aliran kontrol yang mengimplementasikan kode secara prosedural.

Buat Prosedur Tersimpan

Buat prosedur tersimpan dengan dua cara:

1. Gunakan MySQL Shell

Gunakan sintaks berikut untuk membuat prosedur tersimpan di MySQL:

DELIMITER //
CREATE PROCEDURE procedure_name ( IN | OUT | INOUT parameter_name parameter_datatype (length), … )
BEGIN    
    SQL statements
END //
DELIMITER ;

Secara default, sintaks dikaitkan dengan database yang digunakan, tetapi Anda juga dapat menggunakan sintaks untuk database lain dengan menentukan nama database dengan cara berikut:database_name.procedure_name .

Di sini, DELIMITER pertama argumen menyetel pembatas default ke // , sedangkan yang terakhir DELIMITER argumen mengembalikannya ke titik koma ; . Untuk menggunakan beberapa pernyataan, tentukan pembatas yang berbeda seperti $$ .

Nama prosedur muncul setelah CREATE PROCEDURE argumen. Setelah nama prosedur, gunakan tanda kurung untuk menentukan parameter yang akan digunakan dalam prosedur, nama parameter, tipe data, dan panjang data. Pisahkan setiap parameter dengan koma.

Mode parameter adalah:

• IN – Gunakan untuk melewatkan parameter sebagai input. Ketika didefinisikan, kueri meneruskan argumen ke prosedur tersimpan. Nilai parameter selalu dilindungi.
• OUT – Gunakan untuk melewatkan parameter sebagai output. Anda dapat mengubah nilai dalam prosedur tersimpan, dan nilai baru akan diteruskan kembali ke program pemanggil.
• INOUT – Kombinasi IN dan OUT parameter. Program pemanggil meneruskan argumen, dan prosedur dapat memodifikasi INOUT parameter, meneruskan nilai baru kembali ke program.

Misalnya:

Jalankan prosedur tersimpan dengan memanggilnya:

CALL procedure_name;

Kueri mengembalikan hasil untuk prosedur tersimpan.

2. Gunakan MySQL Workbench

Cara lain untuk membuat prosedur tersimpan adalah dengan menggunakan MySQL Workbench Wizard. Wizard ini intuitif dan menyederhanakan prosesnya karena Anda tidak perlu menempatkan pembatas atau mengkhawatirkan formatnya.

Ikuti langkah-langkah berikut:

Langkah 1: Klik kanan Prosedur Tersimpan di jendela Navigator MySQL Workbench dan pilih Create Stored Procedure… untuk memulai wizard.

Langkah 2: Tentukan nama prosedur dan masukkan kode di dalam BEGIN … AKHIR blokir.

Langkah 3: Tinjau kode dan klik Terapkan .

Langkah 4: Konfirmasikan eksekusi dengan mengeklik Terapkan dan buat prosedurnya dengan mengeklik Selesai .

Langkah 5: Jalankan prosedur untuk melihat apakah itu berhasil. Buat tab SQL baru untuk mengeksekusi kueri.

Langkah 6: CALL prosedur di tab SQL dan klik Jalankan .

Jika tidak ada kesalahan yang kembali, MySQL menjalankan prosedur tersimpan dan menampilkan hasilnya.

Mendaftar Prosedur Tersimpan

Ada tiga cara untuk melihat daftar semua prosedur tersimpan:

1. Gunakan MySQL Shell

Untuk mendapatkan daftar semua prosedur tersimpan yang dapat Anda akses, termasuk karakteristiknya, gunakan sintaks berikut:

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]

SHOW PROCEDURE STATUS pernyataan mengembalikan output yang panjang. Pernyataan tersebut menampilkan nama dan karakteristik prosedur tersimpan yang Anda akses di server.

Gulir melalui output untuk menemukan prosedur saat ini di server.

LIKE argumen menemukan prosedur tersimpan yang berisi kata tertentu dalam namanya. Gunakan % untuk mengganti sejumlah karakter, termasuk nol.

Misalnya:

WHERE argumen memungkinkan Anda untuk membuat daftar prosedur tersimpan hanya dalam database tertentu.

Misalnya:

Dalam contoh ini, pernyataan hanya mengembalikan prosedur tersimpan untuk 'daftar_pelanggan ’ basis data.

2. Gunakan Kamus Data

skema_informasi database berisi tabel yang disebut rutin , yang memiliki informasi tentang prosedur dan fungsi tersimpan yang terkait dengan semua database di server MySQL saat ini.

Gunakan sintaks berikut untuk melihat semua prosedur tersimpan untuk database:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE'
        AND routine_schema = 'database_name';

3. Gunakan MySQL Workbench

Untuk pendekatan GUI untuk melihat prosedur tersimpan, gunakan MySQL Workbench. Ikuti langkah-langkah ini untuk melihat prosedur tersimpan:

Langkah 1 :Klik dua kali database yang ingin Anda gunakan di Navigator bagian.

Langkah 2 :Perluas Stored Procedures item tarik-turun.

Item ini menunjukkan semua prosedur tersimpan untuk database saat ini.

Ubah Prosedur Tersimpan

Mengubah prosedur tersimpan berarti mengubah karakteristik dari sebuah prosedur. Tidak ada tidak ada pernyataan di MySQL untuk memodifikasi parameter atau badan dari prosedur tersimpan. Untuk mengubah parameter atau isi, hapus prosedur tersimpan dan buat yang baru.

Ubah prosedur tersimpan dengan dua cara:

1. Gunakan MySQL Shell

Ubah karakteristik prosedur dengan menggunakan ALTER PROCEDURE penyataan. Misalnya, kita dapat menambahkan komentar ke prosedur yang telah kita buat sebelumnya. Sintaksnya adalah:

ALTER PROCEDURE procedure_name
COMMENT 'Insert comment here';

2. Gunakan MySQL Workbench

MySQL Workbench GUI memungkinkan pengguna untuk mengubah prosedur tersimpan di mana pengguna dapat menambahkan parameter atau mengubah kode. MySQL Workbench jatuh prosedur tersimpan yang ada dan membuat yang baru setelah perubahan dilakukan.

Ikuti langkah-langkah berikut:

Langkah 1: Di bagian Navigator, klik kanan prosedur tersimpan yang ingin Anda ubah. Pilih Ubah prosedur Tersimpan… barang.

Langkah 2: Saat tab terbuka, buat perubahan yang diinginkan pada prosedur tersimpan yang ada dan klik Terapkan .

Langkah 3: Jendela tinjauan Skrip SQL muncul menunjukkan proses – menghapus prosedur tersimpan yang ada dan membuat prosedur baru yang berisi perubahan.

Klik Terapkan lalu Selesai di jendela berikutnya untuk menjalankan skrip.

Prosedur Drop Stored

Untuk menghapus (menghapus) prosedur:

1. Gunakan MySQL Shell

Hapus prosedur tersimpan dari server dengan menggunakan DROP PROCEDURE pernyataan.

Sintaks dasarnya adalah:

DROP PROCEDURE [IF EXISTS] stored_procedure_name;

IF EXISTS parameter menjatuhkan prosedur tersimpan hanya jika ada di server. Masukkan nama prosedur tersimpan sebagai ganti stored_procedure_name sintaks.

Misalnya:

Karena tidak ada prosedur bernama 'test ' di server, output menyatakan bahwa 0 baris terpengaruh dan prosedur yang ditentukan tidak ada.

Menghapus prosedur yang tidak ada tanpa IF EXISTS parameter mengembalikan kesalahan.

2. Gunakan MySQL Workbench

Untuk menghapus prosedur tersimpan dengan MySQL Workbench, ikuti langkah-langkah berikut:

Langkah 1: Perluas item Prosedur Tersimpan di bagian Navigator. Klik kanan prosedur tersimpan yang ingin Anda hapus dan pilih Lepaskan Prosedur Tersimpan… di menu konteks.

Langkah 2: Di jendela konfirmasi, klik Lepas Sekarang untuk menghapus prosedur tersimpan.

Tindakan ini menghapus secara permanen prosedurnya.

Keuntungan dan Kerugian Prosedur Tersimpan MySQL

Prosedur tersimpan memiliki beberapa kelebihan dan kekurangan karena disesuaikan dengan kebutuhan spesifik. Berikut adalah beberapa kelebihan dan kekurangannya.

Keuntungan Menggunakan Prosedur Tersimpan

Keuntungan dari prosedur tersimpan adalah:

Pengurangan Lalu Lintas Jaringan

Prosedur tersimpan membantu mengurangi lalu lintas jaringan antara aplikasi dan Server MySQL dengan menjaga semua logika pemrograman di server. Alih-alih mengirim beberapa hasil kueri ke seluruh jaringan, aplikasi hanya mengirim nama prosedur dan input parameter.

Keamanan yang Lebih Baik

Administrator database memberikan hak istimewa kepada aplikasi untuk memanggil dan mengakses hanya prosedur tersimpan tertentu tanpa memberi mereka akses langsung ke tabel. Prosedur tersimpan membantu mencegah serangan injeksi skrip karena parameter input diperlakukan sebagai nilai dan bukan sebagai kode yang dapat dieksekusi.

Logika Bisnis Terpusat

Prosedur tersimpan merangkum logika bisnis yang dapat digunakan kembali oleh banyak aplikasi. Itu membantu mengurangi duplikasi logika yang sama di banyak aplikasi berbeda dan membuat database lebih konsisten.

Kerugian Menggunakan Prosedur Tersimpan

Kerugian dari prosedur tersimpan adalah:

Penggunaan Sumber Daya

Menggunakan banyak prosedur tersimpan dan operasi logis menyebabkan penggunaan memori dan CPU meningkat secara signifikan untuk setiap koneksi.

Tanpa Portabilitas

Tidak mudah untuk mem-port prosedur tersimpan yang ditulis dalam bahasa tertentu dari satu instalasi ke instalasi lainnya. Mengandalkan prosedur tersimpan juga mengikat pengguna ke database tertentu.

Pemecahan Masalah dan Pengujian

MySQL tidak menyediakan utilitas untuk menguji dan men-debug prosedur tersimpan, sehingga mungkin sulit untuk men-debugnya. Mengembangkan dan memelihara prosedur tersimpan membutuhkan pengetahuan yang luas. Ini merupakan tantangan bagi pengembang baru dan mengakibatkan biaya pemeliharaan tambahan.