Prosedur tersimpan adalah sub rutin, segmen pernyataan SQL yang disimpan dalam katalog SQL. Semua aplikasi yang dapat mengakses basis data Relasional (Java, Python, PHP dll. ), dapat mengakses prosedur tersimpan
Prosedur tersimpan berisi parameter IN dan OUT atau keduanya. Mereka mungkin mengembalikan kumpulan hasil jika Anda menggunakan pernyataan SELECT. Prosedur tersimpan dapat mengembalikan beberapa set hasil
Anda dapat membuat prosedur menggunakan pernyataan MySQL CREATE PROCEDURE
Sintaksis
Berikut ini adalah sintaks pernyataan CREATE PROCEDURE −
CREATE PROCEDURE procedure_name (proc_parameter[,...]) routine_bodyDimana, procedure_name adalah nama fungsi yang perlu Anda buat, input_arguments adalah nilai input dari fungsi dan output_parameter adalah nilai kembalian dari fungsi
Contoh
Asumsikan kita telah membuat tabel bernama Karyawan di database MySQL menggunakan CREATE TABLE seperti yang ditunjukkan di bawah ini −
mysql> CREATE TABLE Employee(Name VARCHAR(255), Salary INT NOT NULL, Location VARCHAR(255)); Query OK, 0 rows affected (2.74 sec) _Berikut adalah contoh stored procedure MySQL. Di sini kita menyisipkan kolom ke dalam tabel Karyawan yang mengambil nilai dari pengguna. (melalui parameter input)
Prosedur tersimpan MySQL mengelompokkan banyak tugas menjadi satu dan menyimpan 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 membuang prosedur tersimpan
Prasyarat
- MySQL Server dan MySQL Workbench diinstal
- Akun pengguna MySQL dengan hak akses root
Apa itu Prosedur Tersimpan di MySQL?
Prosedur tersimpan MySQL adalah pernyataan SQL yang telah dikompilasi sebelumnya yang disimpan dalam database. Mereka adalah subrutin yang berisi nama, daftar parameter, dan pernyataan SQL
Semua sistem database relasional mendukung prosedur tersimpan dan tidak memerlukan paket runtime-environment tambahan
Bagaimana Cara Menggunakan Prosedur Tersimpan?
Untuk menjalankan prosedur tersimpan, Anda dapat menggunakan pernyataan CALL atau prosedur tersimpan lainnya. Pertama kali stored procedure dipanggil, MySQL mencarinya di katalog database, mengkompilasi kode, menempatkannya di memori cache, dan mengeksekusinya.
Proses selanjutnya dalam sesi yang sama mengeksekusi prosedur tersimpan dari memori cache, menjadikannya sangat berguna untuk tugas berulang
Prosedur tersimpan menggunakan parameter untuk meneruskan nilai dan menyesuaikan hasil. Parameter digunakan untuk menentukan kolom dalam tabel tempat kueri beroperasi dan mengembalikan hasil
Prosedur tersimpan juga dapat mencakup IF, CASE, dan LOOP pernyataan aliran kontrol yang mengimplementasikan kode secara prosedural
Buat Prosedur Tersimpan
Buat prosedur tersimpan dengan dua cara
1. Gunakan Shell MySQL
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. CALL procedure_name;0
Di sini, argumen CALL procedure_name;1 pertama menetapkan pembatas default ke CALL procedure_name;2, sedangkan argumen CALL procedure_name;1 terakhir mengembalikannya ke titik koma CALL procedure_name;4. Untuk menggunakan beberapa pernyataan, tentukan pembatas yang berbeda seperti CALL procedure_name;5
Nama prosedur muncul setelah CALL procedure_name;_6 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 parameternya adalah
- CALL procedure_name;7 – Gunakan untuk melewatkan parameter sebagai masukan. Saat ditentukan, kueri meneruskan argumen ke prosedur tersimpan. Nilai parameter selalu dilindungi
- CALL procedure_name;8 – Gunakan untuk melewatkan parameter sebagai output. Anda dapat mengubah nilai di dalam stored procedure, dan nilai baru dikembalikan ke program pemanggil
- CALL procedure_name;9 – Kombinasi dari CALL procedure_name;7 dan CALL procedure_name;8 parameter. Program pemanggil melewatkan argumen, dan prosedur dapat memodifikasi parameter CALL procedure_name;9, meneruskan nilai baru kembali ke program
Sebagai contoh
Jalankan prosedur tersimpan dengan memanggilnya
CALL procedure_name;Kueri mengembalikan hasil untuk prosedur tersimpan
2. Gunakan Meja Kerja MySQL
Cara lain untuk membuat stored procedure adalah dengan menggunakan MySQL Workbench Wizard. Wizard ini intuitif dan menyederhanakan prosesnya karena Anda tidak perlu menempatkan pembatas atau mengkhawatirkan formatnya
Ikuti langkah ini
Langkah 1. Klik kanan Stored Procedures di jendela Navigator MySQL Workbench dan pilih Create Stored Procedure… untuk memulai wizard
Langkah 2. Tentukan nama prosedur dan masukkan kode di dalam blok BEGIN … END
Langkah 3. Tinjau kode dan klik Terapkan
Langkah 4. Konfirmasikan eksekusi dengan mengklik Terapkan dan buat prosedur dengan mengklik Selesai
Langkah 5. Jalankan prosedur untuk melihat apakah itu berfungsi. Buat tab SQL baru untuk menjalankan kueri
Langkah 6. CALL prosedur di tab SQL dan klik Execute
Jika tidak ada kesalahan kembali, MySQL mengeksekusi prosedur tersimpan dan menampilkan hasilnya
Daftar Prosedur Tersimpan
Ada tiga cara untuk melihat daftar semua prosedur tersimpan
1. Gunakan Shell MySQL
Untuk mendapatkan daftar semua prosedur tersimpan yang dapat Anda akses, termasuk karakteristiknya, gunakan sintaks berikut
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]Pernyataan SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]_4 mengembalikan output yang panjang. Pernyataan tersebut menampilkan nama dan karakteristik prosedur tersimpan yang dapat Anda akses di server
Gulir melalui output untuk menemukan prosedur yang saat ini ada di server
Argumen SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]_5 menemukan prosedur tersimpan yang berisi kata tertentu dalam namanya. Gunakan SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]_6 untuk mengganti sejumlah karakter, termasuk nol
Sebagai contoh
Argumen SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]_7 memungkinkan Anda untuk mencantumkan prosedur tersimpan hanya dalam database tertentu
Sebagai contoh
Dalam contoh ini, pernyataan hanya mengembalikan prosedur tersimpan untuk database 'customer_list'
2. Gunakan Kamus Data
Database information_schema berisi tabel yang disebut routines, yang memiliki informasi tentang prosedur tersimpan dan fungsi 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 Meja Kerja MySQL
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 bagian Navigator
Langkah 2. Luaskan item tarik-turun Prosedur Tersimpan
Item ini menunjukkan semua prosedur tersimpan untuk database saat ini
Mengubah Prosedur Tersimpan
Mengubah stored procedure berarti mengubah karakteristik prosedur. Tidak ada pernyataan di MySQL untuk memodifikasi parameter atau badan prosedur tersimpan. Untuk mengubah parameter atau badan, jatuhkan prosedur tersimpan dan buat yang baru
Ubah prosedur tersimpan dengan dua cara
1. Gunakan Shell MySQL
Ubah karakteristik prosedur dengan menggunakan pernyataan SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]8. Misalnya, kita dapat menambahkan komentar pada prosedur yang telah kita buat sebelumnya. Sintaksnya adalah
ALTER PROCEDURE procedure_name COMMENT 'Insert comment here';2. Gunakan Meja Kerja MySQL
GUI MySQL Workbench memungkinkan pengguna untuk mengubah prosedur tersimpan di mana pengguna dapat menambahkan parameter atau mengubah kode. MySQL Workbench menghapus prosedur tersimpan yang ada dan membuat yang baru setelah perubahan dilakukan
Ikuti langkah ini
Langkah 1. Di bagian Navigator, klik kanan stored procedure yang ingin Anda ubah. Pilih item Alter Stored procedure…
Langkah 2. Saat tab terbuka, buat perubahan yang diinginkan pada prosedur tersimpan yang ada dan klik Terapkan
Langkah 3. Jendela tinjauan SQL Script muncul menunjukkan proses - menghapus prosedur tersimpan yang ada dan membuat yang baru berisi perubahan
Klik Terapkan lalu Selesai di jendela berikutnya untuk menjalankan skrip
Jatuhkan Prosedur Tersimpan
Untuk menjatuhkan (menghapus) suatu prosedur
1. Gunakan Shell MySQL
Hapus prosedur tersimpan dari server dengan menggunakan pernyataan SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]9
Sintaks dasarnya adalah
Parameter SELECT routine_name FROM information_schema.routines WHERE routine_type = 'PROCEDURE' AND routine_schema = 'database_name'; 0 menghapus prosedur tersimpan hanya jika ada di server. Masukkan nama prosedur tersimpan di tempat SELECT routine_name FROM information_schema.routines WHERE routine_type = 'PROCEDURE' AND routine_schema = 'database_name'; 1 sintaks
Sebagai contoh
Karena tidak ada prosedur bernama 'tes' di server, output menyatakan bahwa 0 baris terpengaruh dan prosedur yang ditentukan tidak ada
Menjatuhkan prosedur yang tidak ada tanpa SELECT routine_name FROM information_schema.routines WHERE routine_type = 'PROCEDURE' AND routine_schema = 'database_name'; 0 parameter mengembalikan kesalahan
2. Gunakan Meja Kerja MySQL
Untuk menghapus prosedur tersimpan dengan MySQL Workbench, ikuti langkah-langkah berikut
Langkah 1. Luaskan item Stored Procedures di bagian Navigator. Klik kanan stored procedure yang ingin Anda hapus dan pilih Drop Stored Procedure… di menu konteks
Langkah 2. Di jendela konfirmasi, klik Jatuhkan Sekarang untuk menghapus prosedur tersimpan
Tindakan ini secara permanen menghapus prosedur
Kelebihan dan Kekurangan MySQL Stored Procedures
Prosedur tersimpan memiliki beberapa kelebihan dan kekurangan karena disesuaikan dengan kebutuhan khusus. Di bawah ini adalah beberapa kelebihan dan kekurangannya
Keuntungan Menggunakan Prosedur Tersimpan
Keuntungan dari stored procedure adalah
Pengurangan Lalu Lintas Jaringan
Prosedur tersimpan membantu mengurangi lalu lintas jaringan antara aplikasi dan Server MySQL dengan menyimpan semua logika pemrograman di server. Alih-alih mengirimkan beberapa hasil kueri melalui jaringan, aplikasi hanya mengirimkan nama prosedur dan masukan parameter
Keamanan yang Ditingkatkan
Administrator basis data 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 beberapa aplikasi. Itu membantu mengurangi duplikasi logika yang sama di banyak aplikasi berbeda dan membuat database lebih konsisten
Kerugian Menggunakan Prosedur Tersimpan
Kerugian dari stored procedure adalah
Penggunaan sumber daya
Menggunakan banyak prosedur tersimpan dan operasi logis menyebabkan penggunaan memori dan CPU meningkat secara signifikan untuk setiap koneksi
Tidak Ada Portabilitas
Tidaklah mudah untuk mem-port stored procedure yang ditulis dalam bahasa tertentu dari satu instalasi ke instalasi lainnya. Mengandalkan stored procedure juga mengikat pengguna ke database tertentu
Pemecahan Masalah dan Pengujian
MySQL tidak menyediakan utilitas untuk menguji dan men-debug prosedur tersimpan, sehingga sulit untuk men-debugnya. Mengembangkan dan memelihara stored procedure membutuhkan pengetahuan yang luas. Ini merupakan tantangan bagi pengembang baru dan menghasilkan tambahan biaya pemeliharaan
Kesimpulan
Setelah membaca artikel ini, Anda tahu apa itu prosedur tersimpan dan kapan menggunakannya. Anda juga tahu cara membuat, memodifikasi, melihat semua prosedur tersimpan yang tersedia, dan menghapus prosedur yang tidak lagi Anda perlukan