Cara membuat prosedur tersimpan di mysql

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;

Di sini, argumen

CALL procedure_name;

CALL procedure_name;

CALL procedure_name;

CALL procedure_name;

CALL procedure_name;

Nama prosedur muncul setelah

CALL procedure_name;

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]

Gulir melalui output untuk menemukan prosedur yang saat ini ada di server

Argumen

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

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

Sebagai contoh

Argumen

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

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]

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]

Sintaks dasarnya adalah

DROP PROCEDURE [IF EXISTS] stored_procedure_name;

Parameter

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

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

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';

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 banyak 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

Bagaimana cara membuat prosedur tersimpan di MySQL?

Bagaimana cara membuat prosedur tersimpan?

Bagaimana cara membuat prosedur tersimpan di MySQL dengan parameter?

Bagaimana cara membuat prosedur tersimpan di SQL?