Di mana prosedur tersimpan di mysql?

Tutorial MySQL STORED PROCEDURE ini menjelaskan cara membuat, memperbarui, mendaftar, menghapus, dan memanggil PROSEDUR TERSIMPAN di MySQL

MySQL Menyediakan PROSEDUR TERSIMPAN untuk memiliki kumpulan pernyataan MySQL yang dikelompokkan bersama dalam sebuah fungsi yang dapat dipanggil sesuai permintaan dengan parameter masukan tertentu

Dengan ini, Anda dapat menggunakan kembali kode dan skrip MySQL yang menghasilkan penghematan waktu dan tenaga yang cukup besar serta memberikan kualitas dan kinerja tinggi dengan struktur termodulasi ke Skrip MySQL

=> Baca SEMUA Tutorial Pelatihan MySQL Di Sini

Ini disimpan dalam database/skema mirip dengan elemen lain seperti tabel, indeks, pemicu, dll. Dalam tutorial ini, kita akan memahami berbagai elemen, parameter, body, dll untuk membuat prosedur tersimpan ini

Apa yang Akan Anda Pelajari

PROSEDUR TERSIMPAN MySQL

Keuntungan

Seperti yang telah dibahas sebelumnya, ini memungkinkan penggunaan kembali kode dalam jumlah besar dan membantu menghindari duplikasi atau mengulangi skrip SQL yang sama berulang kali di tempat yang berbeda

Beberapa keuntungan utama menggunakan ini adalah sebagai berikut

Penggunaan kembali kode
Transfer Jaringan Lebih Rendah – E. g. untuk aplikasi web - alih-alih memanggil kueri individual, prosedur yang dapat langsung mengembalikan hasil yang diinginkan dapat dijalankan.
Lebih aman – Administrator Basis Data dapat memberikan atau mencabut hak istimewa pada tingkat prosedur
Struktur kode termodulasi

Perintah MySQL CREATE PROCEDURE

Pada bagian ini, kita akan melihat bagaimana kita dapat membuatnya di MySQL. Kita akan melihat sintaks dan berbagai elemen yang merupakan bagian dari definisi dan deklarasi prosedur

Catatan. Dalam tutorial ini, kita akan menggunakan MySQL Workbench Client untuk membuat prosedur.

Atau, ini dapat dibuat dari baris perintah serta melalui eksekusi langsung. file skrip sql

Sintaksis

DELIMITER {custom delimiter}
CREATE PROCEDURE {procedureName}([optional parameters])
BEGIN
	// procedure body...
	// procedure body...
END
{custom delimiter}

Dalam bentuk paling sederhana, Anda dapat membuatnya menggunakan sintaks di atas

Di Sini

#1) namaprosedur. Ini adalah nama prosedurnya. Ini mirip dengan nama fungsi yang kita miliki di hampir semua bahasa pemrograman

Disarankan agar nama proc yang disimpan dimulai dengan sp_ atau sp untuk mengidentifikasi bahwa itu adalah proc
Selain itu, nama prosedur harus menunjukkan langkah-langkah yang terjadi di dalam badan prosedur. Misalnya – prosedur yang akan mengambil saldo untuk akun tertentu, Anda dapat menamainya sebagai – sp_GetAccountBalance atau spGetAccountBalance

#2) {pembatas khusus}. Ini digunakan saat Anda membuat proc menggunakan baris perintah atau melalui klien GUI. Sangat berguna untuk memiliki pembatas khusus sehingga server MySQL tahu kapan proc yang disimpan berakhir dan seluruh badan prosedur dapat dikirim sebagai metode tunggal

Harap perhatikan bahwa pembatas bersifat opsional dan berlebihan jika tidak ada pernyataan dalam prosedur yang diakhiri dengan titik koma ';'

#3) Badan prosedur. Ini adalah satu atau beberapa pernyataan MySQL yang akan dieksekusi setiap kali proc yang dibuat dipanggil

Menggunakan DELIMITER Saat Membuat PROSEDUR TERSIMPAN

Saat membuat ini di MySQL karena mungkin berisi satu atau lebih pernyataan SQL yang memiliki pembatas default titik koma ';', penting untuk memiliki Pembatas terpisah yang akan menandai awal dan akhir untuk meneruskan seluruh badan prosedur ke server sebagai

Untuk mengubah pembatas menjadi nilai khusus, sintaksnya adalah

DELIMITER {custom value}

Contoh

DELIMITER //

Dengan pernyataan di atas, server MySQL akan mengubah pembatas menjadi '//' alih-alih ';' Setelah proc dibuat dan disimpan di server MySQL, Anda dapat mengalihkan kembali DELIMITER ke titik koma menggunakan perintah di bawah ini

DELIMITER ;

Ini akan mengatur ulang DELIMITER menjadi titik koma lagi

Uji Data

Kami akan membuat skema bernama stored_proc_tutorial dan menyimpan semua tabel terkait dan proc aktual dalam skema ini

Kami juga akan memiliki tabel tes bernama data siswa yang memiliki data nilai siswa

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

Membuat PROSEDUR TERSIMPAN MySQL

Di bagian ini, mari kita lihat berbagai cara untuk MEMBUAT PROSEDUR TERSIMPAN MySQL. Kita akan mempelajari cara membuat prosedur tanpa parameter apa pun dan dengan berbagai jenis parameter yang didukung

Dengan QUERY SELECT Sederhana

Sekarang mari kita lihat bagaimana kita bisa MEMBUAT untuk memilih data dari tabel StudentMarks

Jika kami hanya mendapatkan data itu melalui satu kueri, maka kami dapat menjalankan kueri di bawah ini

SELECT * FROM stored_proc_tutorial.studentMarks;

Sekarang mari kita lihat bagaimana kita dapat menjalankan kueri ini sebagai bagian dari badan prosedur

DELIMITER $$
CREATE PROCEDURE GetStudentData()
BEGIN
	SELECT * FROM studentMarks;
END$$
DELIMITER ;

Mari validasi dengan memanggil prosedur yang dibuat di atas

CALL GetStudentData();

Catatan. Untuk memanggil proc yang sudah dibuat, Anda dapat menggunakan sintaks di bawah ini.

CALL {procedureName}({inputParamsList})

Dengan Input Dan Output Parameter

Parameter Prosedur MySQL

Di MySQL, parameter prosedur dapat berupa salah satu dari ketiga jenis ini

#1) DI. IN Parameter seperti argumen fungsi lokal yang kita miliki di hampir semua bahasa pemrograman seperti Java, C#, dll

Parameter IN perlu diteruskan bersama dengan pemanggilan prosedur dan dilindungi. Artinya, yang dilindungi adalah nilai parameter IN dapat berubah selama eksekusi fungsi tetapi dipertahankan setelah aliran eksekusi selesai. Saya. e nilai yang diubah bersifat lokal untuk eksekusi fungsi

#2) KELUAR. OUT Parameter adalah parameter OUTPUT di mana prosedur seharusnya dikembalikan setelah eksekusi fungsi selesai. Nilai default dapat diubah selama eksekusi prosedur dan dikembalikan ke fungsi pemanggilan atau pernyataan

#3) MASUKAN. INOUT adalah kombinasi parameter IN dan OUT. Itu dapat ditentukan secara opsional ketika prosedur dipanggil dan dapat dimodifikasi dan dikembalikan ke pemanggil

Diberikan di bawah ini adalah sintaks untuk menentukan parameter dalam prosedur MySQL

[IN/OUT/INOUT] {parameter_name} {datatype}

#4) Jenis parameter. Bisa salah satu IN, OUT atau INOUT

#5) nama_parameter. Pengidentifikasi untuk parameter yang dideklarasikan

#6) Tipe Data. Jenis data yang akan berisi parameter yang dideklarasikan. Itu bisa berupa tipe data yang didukung MySQL

=> Periksa Di Sini Untuk Daftar Lengkap Tipe Data yang Didukung oleh MySQL

Mari kita lihat contoh semua tipe parameter yang berbeda dan bagaimana mereka dapat digunakan dalam prosedur MySQL

Membuat Prosedur dengan Parameter Input

Sekarang mari kita lihat bagaimana kita bisa melewatkan parameter input untuk mengambil hasil yang dibutuhkan

Misalnya, untuk memperluas contoh di atas untuk mengambil data siswa, mari buat prosedur untuk mengambil detail siswa dengan ID siswa yang diteruskan sebagai parameter masukan.

DELIMITER {custom value}

Dalam definisi di atas, Anda dapat melihatnya

Nama proc yang disimpan juga menyertakan nama DB sebagai awalan – Ini menunjukkan di mana DB prosedur harus disimpan dan menjadi bagian setelah dibuat
Kami telah membuat satu parameter IN bernama studentID dari tipe INT – Penting untuk dicatat bahwa jenis parameternya adalah IN yang pada dasarnya adalah parameter INPUT yang nilainya dapat digunakan oleh proc tetapi tidak dapat diubah
Kami telah menggunakan parameter IN dalam kueri SELECT kami untuk mengambil detail siswa untuk id yang disediakan sebagai parameter IN

Untuk memastikan bahwa prosedur berhasil dibuat, Anda dapat merujuk ke daftar Skema Meja Kerja MySQL

Pilih skema/database tempat proc dibuat
Pilih menu Prosedur Tersimpan
Anda akan melihat daftar prosedur yang dibuat

Sekarang mari kita panggil prosedur ini dan lihat, bagaimana kita dapat mengambil detail siswa tertentu dengan ID yang diberikan

DELIMITER {custom value}

//Keluaran

Membuat Prosedur dengan Parameter Keluaran

Di bagian sebelumnya, kita belajar tentang penggunaan parameter Input (IN) yang disebutkan saat dipanggil

Sekarang mari kita lihat bagaimana kita dapat menggunakan parameter Output atau OUT

Misalnya. Misalkan kita ingin menghitung nilai rata-rata semua siswa dari tabel StudentMarks dan mengembalikan nilai rata-rata sebagai bidang OUT.

DELIMITER {custom value}

Di sini, Anda dapat melihat bahwa kami telah membuat prosedur bernama spGetAverageMarks dan menetapkan parameter bernama rata-rata dengan tipe data DECIMAL

Sekarang mari kita lihat bagaimana kita dapat memanggil prosedur ini. Ini dapat dilakukan dengan cara yang sama, seperti yang kita lakukan untuk prosedur yang tidak memiliki parameter atau memiliki parameter IN

Namun, di sini karena kami memiliki parameter OUT yang akan mengembalikan nilai, kami dapat menentukan placeholder yang diawali dengan '@'

Misalnya. Dalam hal ini, kami telah menetapkan “@average_marks” yang akan menyimpan nilai rata-rata parameter OUT seperti yang dihasilkan oleh eksekusi.

DELIMITER {custom value}

Setelah dijalankan, Anda dapat menjalankan SELECT untuk variabel OUT, untuk mengambil hasilnya

DELIMITER {custom value}

Prosedur Dengan PARAMETER INOUT

Parameter INOUT adalah jenis parameter khusus yang merupakan kombinasi dari IN i. e. INPUT dan OUT i. e. parameter KELUARAN

Apa artinya ini pada dasarnya adalah bahwa PANGGILAN ke prosedur dapat memberikan nilai ke parameter ini dan dapat mengubah nilai dan meneruskannya kembali ke pengguna atau pemanggil

Mari kita coba memahami ini dengan sebuah contoh

Misalkan kita perlu memiliki fungsi yang mengambil nilai awal dari penghitung dan menaikkannya dengan angka yang diberikan

DELIMITER {custom value}

Anda dapat melihat di atas bahwa kami telah mendeklarasikan 2 parameter – penghitung tipe INOUT dan penambahan tipe IN

Jadi di sini untuk parameter INOUT – kami menggunakan nilai yang diteruskan dan memperbaruinya untuk digunakan pemanggil

Untuk mengeksekusi ini, kami menyetel nilai parameter INOUT @counter ke 10 dan meneruskan 3 untuk parameter increment IN

Hasil yang diharapkan adalah => 10 + 3 => 13 (yang dikembalikan dalam variabel @counter dalam eksekusi prosedur)

DELIMITER {custom value}

MySQL STORED PROSEDUR Variabel Lokal

Sama seperti bagaimana kita membahas berbagai jenis parameter yang didukung, kita juga dapat menggunakan Variabel Lokal di dalam prosedur untuk penyimpanan sementara yang ruang lingkupnya terbatas hanya pada prosedur itu sendiri

Sintaksis

DELIMITER {custom value}

Mari kita pahami penggunaan variabel lokal dengan bantuan sebuah contoh

Misalkan kita ingin mencari jumlah siswa yang memiliki nilai di bawah nilai rata-rata semua siswa

Mari membuat proc

DELIMITER {custom value}

Di sini Anda dapat melihat bahwa kami telah mendeklarasikan variabel lokal bernama avgMarks

DELIMITER {custom value}

Variabel ini akan menyimpan nilai rata-rata terhitung dari kueri SELECT pertama

DELIMITER //

Penting juga untuk dicatat bahwa nilai juga dapat diberikan ke variabel lokal menggunakan SET jika itu adalah nilai pra-komputasi statis

Misalnya

DELIMITER //

Setelah nilai ditetapkan, kami kemudian menggunakan nilai dari variabel lokal, untuk menemukan jumlah siswa yang memiliki total_marks di bawah nilai yang dihitung (menjadi avgMarks)

DELIMITER //

Sekarang mari kita lihat hasil pemanggilan prosedur ini

DELIMITER //

//Keluaran

Daftar Semua PROSEDUR TERSIMPAN

Untuk melihat semua prosedur dalam database, melalui klien GUI seperti MySQL Workbench, Anda selalu dapat menavigasi ke skema yang diinginkan dan memperluas node storedProcedures untuk mendapatkan daftar semua prosedur yang dibuat di DB tersebut

Namun, terkadang diinginkan untuk memiliki daftar prosedur dengan kondisi pencarian tertentu. Misalnya. Temukan semua proc yang namanya dimulai dengan sp.

DELIMITER //

//Keluaran

Anda dapat melihat bahwa output mencantumkan semua procs yang namanya dimulai dengan 'sp'

Kami juga dapat memfilternya dengan DB

DELIMITER //

Memperbarui PROSEDUR TERSIMPAN

Mirip dengan memperbarui tabel yang ada, atau memperbarui tipe data bidang kolom, kadang-kadang, mungkin diperlukan untuk MEMPERBARUI prosedur yang ada

Tetapi memperbarui yang sudah ada tidak semudah perintah ALTER TABLE sederhana

Ada 2 level update yang bisa dilakukan terhadap prosedur yang sudah ada

#1) Memperbarui komentar, jenis keamanan, dll. Untuk melakukan ini, kita dapat menggunakan perintah ALTER PROCEDURE tetapi hanya mendukung hal-hal yang sangat terbatas yang dapat diperbarui

Sintaksis

DELIMITER //

Di Sini

{nama_prosedur}. Ini adalah nama prosedur yang sepenuhnya memenuhi syarat yang ingin kami perbarui
Ciri. Ini adalah daftar nilai yang ingin kita ubah. Beberapa yang didukung adalah – COMMENT, LANGUAGE, SQL SECURITY, dll

Mari kita coba perbarui komentar melalui perintah ALTER PROCEDURE

Kami membuat prosedur baru tanpa komentar

DELIMITER //

Sekarang mari gunakan ALTER untuk menambahkan komentar

DELIMITER //

Kami sekarang dapat menjalankan SHOW CREATE PROCEDURE, untuk melihat definisi dengan komentar yang diperbarui

DELIMITER //

Dalam tampilan Editor Formulir, Anda dapat melihat definisi terbaru dari CREATE PROCEDURE dengan komentar yang diperbarui

#2) Memperbarui badan prosedur, jumlah, dan jenis parameter. Misalnya, Anda ingin menambahkan parameter IN baru, atau mengubah tipe data dari parameter yang ada, dll, maka tidak ada cara untuk memperbarui secara langsung .

Satu-satunya pilihan dalam kasus seperti itu adalah

JATUHKAN prosedur yang ada
BUAT prosedur baru dengan daftar badan/parameter yang diperbarui, dll

Menghapus PROSEDUR TERSIMPAN

Perintah DROP PROCEDURE dapat digunakan untuk menghapus prosedur dari database yang diberikan

Sintaksis

DELIMITER ;

Contoh. Misalkan kita ingin menghapus proc tersimpan bernama spGetAverageMarks.

DELIMITER ;

Kami juga dapat MENGHAPUS atau MENGHAPUS prosedur dari klien GUI seperti MySQL Workbench

#1) Cukup luaskan opsi StoredProcedures dalam skema tertentu

#2) Pilih proc yang akan dihapus dan klik kanan

#3) Pilih Opsi Drop Stored Procedure

#4) Pilih DROP NOW untuk segera DROP atau REVIEW untuk melihat skrip SQL dan kemudian jalankan

Catatan. Untuk menjalankan perintah DROP PROCEDURE, pengguna harus memiliki hak istimewa dari perintah ALTER ROUTINE.

Memanggil Prosedur Dari PROSEDUR TERSIMPAN Lain

Kita dapat menggunakan konsep prosedur bersarang jika memungkinkan untuk memanggil prosedur dari dalam prosedur lain

Mari kita pahami ini dengan bantuan sebuah contoh

Kami akan memanggil prosedur dari prosedur lain untuk mengembalikan hasil keseluruhan siswa. Jika nilai siswa di atas rata-rata – maka hasilnya akan LULUS lagi – GAGAL

Kami akan membuat 2 procs

#1) Pertama, bernama spGetIsAboveAverage akan mengembalikan nilai Boolean jika nilai siswa di atas rata-rata atau tidak

Hitung AVERAGE menggunakan fungsi AVG dan simpan hasilnya dalam variabel lokal
Ambil nilai untuk ID siswa yang diteruskan dalam pemanggilan fungsi
Bandingkan StudentMarks dengan nilai rata-rata dan kembalikan hasilnya sebagai 0 atau 1

#2) Yang kedua bernama spGetStudentResult – Ini akan meneruskan studentId sebagai input (IN) dan mengharapkan hasil sebagai parameter output (OUT)

Memanggil prosedur pertama spGetIsAboveAverage
Gunakan hasil yang dikembalikan dari langkah 1) dan setel hasilnya ke LULUS atau GAGAL tergantung pada nilai dari langkah 1) masing-masing menjadi 1 atau 0

Mari kita lihat pernyataan kode untuk kedua prosedur

Kode untuk Prosedur 1

DELIMITER ;

Kode untuk Prosedur 2

DELIMITER ;

Seperti yang Anda lihat di atas, kami memanggil prosedur spGetIsAboveAverage dari dalam spGetStudentResult

Sekarang mari kita panggil prosedur untuk mengambil hasil. (Nilai rata-rata untuk semua entri di StudentTable adalah 323. 6)

Untuk LULUS – Kami akan menggunakan StudentID 2 – memiliki nilai total – 450
Sejak 450 > 323. 6 - kami akan mengharapkan hasilnya "LULUS"

DELIMITER ;

Untuk hasil FAIL kita akan menggunakan StudentId – 10 dengan nilai total – 150
Sejak 150 < 323. 6 - kami akan mengharapkan hasilnya "LULUS"

DELIMITER ;

Menggunakan Pernyataan Bersyarat

Sekarang mari kita lihat bagaimana kita dapat menggunakan pernyataan bersyarat seperti IF-ELSE atau CASE dll dalam sebuah prosedur

Misalnya, kita ingin menulis prosedur untuk mengambil studentId dan bergantung pada StudentMarks kita perlu mengembalikan kelas sesuai dengan kriteria di bawah ini.

Nilai >= 400. Kelas – Kelas Satu
Nilai >= 300 dan Nilai < 400 – Kelas Dua
Nilai < 300 – Gagal

Mari kita coba membuat prosedur seperti itu menggunakan pernyataan IF-ELSE

DELIMITER ;

Seperti yang terlihat di atas, kami telah menggunakan

JIKA - ELSEIF memblokir di dalam tubuh untuk menanyakan nilai siswa dan SET parameter KELUAR bernama kelas yang dikembalikan ke pemanggil prosedur
studentId dilewatkan sebagai parameter IN
Kami telah mendeklarasikan variabel lokal bernama 'marks' yang akan mengambil total_marks dari tabel studentMarks untuk ID yang diberikan dan menyimpannya sementara
Variabel lokal digunakan dibandingkan dengan blok IF ELSE-IF

Mari coba jalankan prosedur di atas untuk masukan yang berbeda dan lihat hasilnya

Untuk ID siswa – 1 – total_marks adalah 450 – maka hasil yang diharapkan adalah FIRST CLASS

DELIMITER ;

Untuk ID siswa – 6 – total_marks adalah 380 – Maka hasil yang diharapkan adalah KELAS KEDUA

DELIMITER ;

Untuk ID siswa – 11 – total_marks adalah 220 – Maka hasil yang diharapkan adalah GAGAL

DELIMITER ;

Penanganan Kesalahan dalam PROSEDUR TERSIMPAN

Seperti bahasa pemrograman lainnya, MySQL juga dapat membuang kesalahan dan pengecualian saat menjalankan kueri

Jadi, saat menjalankan kueri individual di terminal atau klien, Anda bisa mendapatkan respons sukses atau kesalahan yang menentukan apa yang salah

Demikian pula, dalam prosedur MySQL, kesalahan ini dapat terjadi

Kita dapat menambahkan penangan untuk menangani kode kesalahan generik atau spesifik apa pun yang dilemparkan selama eksekusi prosedur. Penangan kesalahan ini dapat mengarahkan mesin eksekusi untuk melanjutkan eksekusi prosedur atau keluar dengan beberapa pesan

Mendeklarasikan Handler untuk Kesalahan

Untuk menangani pengecualian atau kesalahan, Anda perlu mendeklarasikan HANDLER di dalam badan prosedur

Sintaks untuk mendeklarasikan handler adalah

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

{action} dapat memiliki nilai

MELANJUTKAN. Ini masih akan terus menjalankan prosedur saat ini
KELUAR. Eksekusi prosedur ini akan berhenti dan aliran akan dihentikan

{kondisi}. Ini adalah peristiwa yang akan menyebabkan HANDLER dipanggil

Ex – dan kode kesalahan MySQL – ex. MySQL akan melempar kode kesalahan 1062 untuk pelanggaran PRIMARY KEY duplikat
SQLWARNING / NOTFOUND – dll digunakan untuk kasus yang lebih umum. Misalnya , kondisi SQLWARNING dipanggil setiap kali mesin MySQL mengeluarkan peringatan apa pun untuk pernyataan yang dieksekusi. Contoh, PEMBARUAN dilakukan tanpa klausa WHERE.

{pernyataan} – Bisa berupa satu atau beberapa pernyataan, yang ingin kita jalankan saat handler mengeksekusi. Itu akan dilampirkan dalam kata kunci BEGIN dan END yang mirip dengan badan PROSEDUR yang sebenarnya

Catatan. Penting untuk dicatat bahwa, Anda dapat mendeklarasikan banyak penangan dalam definisi badan prosedur MySQL yang diberikan. Ini analog dengan memiliki banyak blok tangkapan untuk berbagai jenis pengecualian di banyak bahasa pemrograman seperti Java, C#, dll.

Mari kita lihat contoh deklarasi HANDLER untuk DUPLICATE KEY INSERT (yaitu kode kesalahan 1062)

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

Seperti yang terlihat di atas, kami telah membuat penangan untuk kesalahan 1062. Segera setelah kondisi ini dipanggil, pernyataan antara blok BEGIN dan END akan dieksekusi

Dalam hal ini, pernyataan SELECT akan mengembalikan errorMessage sebagai 'DUPLICATE KEY ERROR'

Kami dapat menambahkan beberapa pernyataan sesuai kebutuhan dalam BEGIN ini. END blok

Menggunakan Handler Dalam Tubuh PROSEDUR TERSIMPAN

Sekarang mari kita pahami bagaimana penangan ini dapat digunakan di dalam tubuh prosedur MySQL

Kami akan memahami ini dengan bantuan sebuah contoh

Mari buat prosedur yang akan menyisipkan record di tabel studentMarks dan memiliki parameter IN sebagai studentId, total_marks, dan grade. Kita juga menambahkan parameter OUT bernama rowCount yang akan mengembalikan jumlah total record dalam tabel studentMarks

Mari tambahkan juga Error Handler untuk Duplicate Key record i. e. jika seseorang memanggilnya untuk menyisipkan record dengan studentID yang ada, maka Error handler akan dipanggil dan akan mengembalikan error yang sesuai

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

Sekarang mari kita panggil Prosedur ini dengan id siswa yang ada

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

Output akan menampilkan pesan kesalahan yang ditentukan dalam penangan kesalahan

Juga karena ini adalah penangan keluar, alur prosedur utama tidak akan dilanjutkan. Oleh karena itu, dalam hal ini parameter rowCount OUT tidak akan diperbarui karena pernyataan ini setelah pernyataan INSERT yang menghasilkan kondisi kesalahan

Jadi, jika Anda mencoba mengambil nilai dari rowCount, Anda akan mendapatkan NULL

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

Mari JATUHKAN prosedur ini, dan buat ulang dengan tindakan LANJUTKAN alih-alih KELUAR untuk penangan kesalahan

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

Seperti yang Anda lihat di atas, alih-alih `DECLARE EXIT', kami sekarang menggunakan 'DECLARE CONTINUE' yang akan melanjutkan eksekusi setelah menangani kode kesalahan

Mari kita coba panggil prosedur ini dengan ID siswa yang ada

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

Anda masih akan melihat kesalahan yang sama, tetapi kali ini rowCount akan diperbarui, karena kami telah menggunakan tindakan CONTINUE alih-alih EXIT

Mari kita coba mengambil nilai dari parameter rowCount OUT

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

PROSEDUR TERSIMPAN MySQL vs FUNGSI

MySQL menyediakan 2 cara untuk membuat method atau kode untuk digunakan kembali berupa FUNGSI dan PROSEDUR

Namun, ada perbedaan tertentu di antara keduanya

PROSEDURFUNGSIMendukung berbagai jenis parameter seperti IN, OUT dan INOUT. Hanya mendukung parameter masukan. Mereka dapat memanggil fungsi. Fungsi tidak dapat memanggil prosedur. Pengecualian dapat ditangani dalam prosedur. Tidak ada penanganan pengecualian yang mungkin dilakukan di FUNCTIONS. Mungkin atau mungkin tidak mengembalikan nilai. FUNGSI diharapkan selalu mengembalikan hasil. Ini tidak dapat dipanggil dari dalam pernyataan SELECT. Fungsi dapat dipanggil dari dalam pernyataan SELECT. Mereka terutama digunakan untuk memproses tugas yang berulang. FUNGSI digunakan untuk menghitung nilai dan mengembalikan hasil ke pemanggil. Ini adalah pra-kompilasi - i. e. mereka dikompilasi sekali dan kode yang dikompilasi digunakan kembali untuk panggilan berikutnya yang dilakukan ke prosedur. FUNCTIONS dikompilasi setiap kali dipanggil

Pertanyaan yang Sering Diajukan

T #1) Apakah MySQL memiliki prosedur tersimpan?

Menjawab. MySQL memiliki PROSEDUR TERSIMPAN yang disimpan sebagai bagian dari database/skema seperti entitas lain seperti tabel, indeks, dll.

Ini dapat dibuat menggunakan perintah CREATE PROCEDURE dan dapat memiliki parameter opsional yang ditentukan untuk meneruskan data serta mendapatkan hasil sebagai hasil dari eksekusi prosedur

T #2) Bagaimana cara melihat stored procedure di MySQL?

Menjawab. Ini umumnya terkait dengan database di MySQL (pengecualian dapat berupa prosedur yang dihasilkan sistem)

Untuk membuat daftar semua prosedur, Anda bisa

Lihat melalui klien GUI seperti meja kerja MySQL, dengan memperluas opsi StoredProcedures dalam database atau skema
Dapatkan daftar dengan menjalankan perintah SHOW PROCEDURE STATUS

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

Perintah di atas akan mencantumkan semua prosedur yang memiliki kata Student di namanya

T #3) Mengapa menggunakan prosedur tersimpan di MySQL?

Menjawab. Mereka adalah sarana untuk memiliki struktur modular yang kuat untuk skrip SQL berulang yang membantu

Menggunakan kembali skrip yang ada
Performa yang dioptimalkan dengan mengurangi transfer Jaringan
Mudah disimpan sebagai skrip berversi sebagai bagian dari repositori kode, dll

Namun, untuk bekerja dengan mereka, mungkin membutuhkan pengetahuan / keterampilan yang baik dan karenanya akan melibatkan sejumlah kurva pembelajaran

T #4) Apa parameter keluaran dalam prosedur tersimpan?

Menjawab. MySQL mendukung parameter keluaran yang digunakan oleh kata kunci OUT. Ini adalah parameter yang akan digunakan pemanggil dan mengharapkan prosedur yang dipanggil untuk diperbarui

Contoh. Kami memiliki prosedur untuk mengambil nilai tertinggi dari tabel data siswa. Kita dapat memiliki yang seperti ini dengan nilai tertinggi yang disimpan dalam parameter OUT.

SELECT * FROM stored_proc_tutorial.studentMarks;

T #5) Bagaimana cara meneruskan jenis parameter yang berbeda ke prosedur tersimpan SQL?

Menjawab. MySQL mendukung 3 jenis parameter yang bisa dilewatkan saat prosedur dipanggil

Sintaksnya sama untuk semua

SELECT * FROM stored_proc_tutorial.studentMarks;

Jenis parameter yang berbeda memiliki utilitas yang berbeda

DI DALAM. Jenis ini digunakan ketika pemanggil prosedur perlu melewatkan beberapa data yang akan digunakan oleh prosedur

KELUAR. Jenis ini digunakan ketika pemanggil mengharapkan beberapa nilai untuk dihitung oleh prosedur yang dipanggil. Misalnya, mencari Nilai tertinggi, Nilai rata-rata dari tabel data Siswa, dll.

KELUAR. Ini adalah jenis parameter khusus yang dapat diteruskan oleh pemanggil serta diperbarui oleh eksekusi prosedur dan dikirim kembali ke pemanggil. Salah satu contohnya adalah – Penelepon memberikan nilai awal parameter dan prosedur yang dipanggil melakukan beberapa manipulasi dan memperbarui nilai awal

T #6) Di mana MySQL menyimpan prosedur tersimpan?

Menjawab. Di MySQL, ini disimpan dalam skema informasi tabel Sistem. rutinitas`

Untuk melihat semua entri dalam tabel ini, kita dapat menjalankan kueri SELECT pada tabel ini ( Catatan. Izin ini biasanya tersedia untuk pengguna root atau administrator sistem).

SELECT * FROM stored_proc_tutorial.studentMarks;

Referensi => Skema informasi. tabel rutin

T #7) Bagaimana cara memanggil stored procedure di stored procedure lain di MySQL?

Menjawab. Sangat valid untuk memanggil prosedur dari dalam yang lain. Satu poin penting, Anda perlu memastikan bahwa prosedur yang dipanggil harus ada dan harus valid sebelum dipanggil di prosedur lain. Ini seperti memanggil fungsi di dalam fungsi lain di bahasa pengkodean lain seperti Java, C#

Kesimpulan

Dalam tutorial ini, kami membahas Pengenalan PROSEDUR TERSIMPAN di MySQL

Kami belajar tentang membuat prosedur sederhana, konsep menggunakan DELIMITER dalam definisi prosedur, dan berbagai jenis Parameter yang didukungnya

Kita juga belajar tentang bagaimana ini bisa dipanggil dan bagaimana nilai yang dikembalikan dari eksekusi prosedur bisa digunakan

Selain itu, kami belajar tentang menangani kode kesalahan dalam badan prosedur melalui penangan kesalahan dan bagaimana kami dapat memanggil prosedur yang ada dari dalam definisi atau badan orang lain.

Ini banyak digunakan di banyak proyek matang yang melakukan interaksi dan perhitungan basis data yang sangat berat. Ini sebagian besar dapat memengaruhi kinerja aplikasi dan dapat sangat meningkatkan kemampuan penggunaan kembali skrip dan mengurangi transfer data Jaringan untuk kinerja yang dioptimalkan

Di mana prosedur tersimpan berada?

Di dalam SQL Server Studio, prosedur tersimpan, atau singkatnya prosedur, berada di dalam basis data apa pun, di bawah subdirektori kemampuan program .

Di mana prosedur tersimpan disimpan dalam database?

Prosedur tersimpan adalah kumpulan pernyataan Structured Query Language (SQL) dengan nama yang ditetapkan, yang disimpan dalam sistem manajemen basis data relasional (RDBMS) as a group, so it can be reused and shared by multiple programs.

Bagaimana cara menemukan prosedur tersimpan di SQL?

Di bawah ini adalah langkah-langkah untuk menggunakan pengaturan filter untuk menemukan prosedur tersimpan. .

Di Object Explorer di SQL Server Management Studio, buka database dan perluas

Perluas folder Programabilitas

Klik kanan folder Prosedur Tersimpan

Dari menu klik kanan, pilih Filter di menu klik kanan

Apakah ada Prosedur Tersimpan di MySQL?

MySQL mendukung rutinitas tersimpan (prosedur dan fungsi) . Rutin tersimpan adalah sekumpulan pernyataan SQL yang dapat disimpan di server. Setelah ini selesai, klien tidak perlu terus menerbitkan kembali pernyataan individu tetapi dapat merujuk ke rutinitas yang disimpan sebagai gantinya.