Sederhananya, sebuah Stored Prosedure (“SP”) adalah prosedur (ditulis dalam SQL dan pernyataan kontrol lainnya) disimpan dalam database yang dapat dipanggil oleh mesin database dan terhubung dengan bahasa pemrograman.

Dalam tutorial ini, kita akan melihat cara membuat SP di MySQL dan jalankan di server MySQL dan PHP.

Catatan: Kita tidak akan membahas semua aspek dari SP disini,  We are not going to cover the full aspect of the SP here. Dokumen MySQL resmi selalu menjadi referensi Terbaik.

SP juga tersedia di server database umum lainnya (Postgre misalnya) sehingga apa yang akan kita bahas di sini dapat diterapkan untuk mereka juga.

Mengapa direkomendasikan untuk menggunakan

Sebagian besar dari kita sudah familiar dengan setup yang normal untuk membangun aplikasi database: membuat database, membuat tabel, mengatur indeks, CRUD data, melakukan query dari sisi klien dan melakukan proses lebih lanjut jika diperlukan.

Alur kerja tersebut bekerja dengan baik dalam banyak kasus tapi ada satu aspek penting dari pemrograman database hilang: Stored Procedure.

Setidaknya ada empat keuntungan yang saya ketahui dengan menggunakan SP dalam aplikasi database.

Pertama, mengurangi lalu lintas jaringan dan overhead. Dalam database PHP aplikasi web yang khas, ada empat lapisan:

  • Layer klien, yang mana normalnya adalah browser web. Ia menerima interaksi user dan menampilkan data di user interface.
  • Layer web server, yang menangani dan memilah permintaan user dan mengirim balik response yang diberikan ke layer klien.
  • Layer PHP, yang menangani semua interpretasi PHP, melakukan logika aplikasi, dan menghasilkan response dari PHP.
  • Layer Database, yang menangani semua query database, termasuk tidak terbatas pada sebuah query SELECT, pernyataan INSERT, dan seterusnya.

Dalam lingkungan yang khas, lapisan ini akan kemungkinan besar tidak berada pada satu mesin tunggal, bahkan mungkin tidak dalam satu jaringan, untuk aplikasi yang lebih besar.

Meskipun kecepatan jaringan telah sangat meningkat dalam beberapa tahun terakhir, masih paling lambat dan paling tidak dapat diandalkan dibandingkan dengan cara lain untuk mentransfer data (CPU cache, memori, hard disk, dll). Jadi, untuk menghemat bandwidth dan meningkatkan ketahanan, kadang-kadang ide yang baik untuk memiliki lebih banyak pengolahan dan logika yang dilakukan pada sisi server (khususnya, server MySQL) dan memiliki sedikit data yang ditransfer melalui jaringan.

Kedua, meningkatkan kinerja. SP disimpan dan dijalankan langsung di server MySQL. Hal ini dapat menjadi pra-kompilasi dan dianalisa oleh database server. Ini sangat berbeda dari mengeluarkan permintaan yang sama dari sisi client, di mana permintaan akan diurai oleh database driver, dianalisis dan dioptimalkan (jika mungkin) pada setiap kali pernyataan query disebut. Hal ini entah bagaimana mirip eksekusi bahasa dengan interpreter  (pada akhir client) dan eksekusi bahasa terkompilasi (pada akhir database server). Dan kita tahu program yang dikompilasi akan berjalan lebih cepat.

Ketiga, Tulis sekali dan Jalankan dan mana saja. SQL adalah standar dan murni 100% platform independen. Hanya mengandalkan pada server database. Pertimbangkan berapa banyak berbeda bahasa / libs ada yang bisa kita gunakan untuk menangani database. Hal ini meningkatkan efisiensi dengan menempatkan penerimaan dan pengolahan data pada akhir server daripada menulis logika pengolahan yang sama dalam sintaks yang berbeda yang disediakan oleh semua bahasa / libs, jika logika pengolahan datanya sangat umum digunakan.

Terakhir, SP merupakan aspek fundamental dari keamanan database.

Mari kita mempertimbangkan setup database sederhana. Dalam sistem informasi sumber daya manusia (HRIS), adalah wajar untuk mengasumsikan bahwa ada sebuah tabel yang memegang informasi gaji masing-masing karyawan. Seorang karyawan HR harus memiliki hak untuk mengambil beberapa angka dari tabel ini: Jumlah gaji, gaji rata-rata, dll tapi karyawan ini seharusnya tidak melihat gaji rinci dari setiap karyawan karena informasi ini akan terlalu sensitif dan seharusnya hanya tersedia untuk beberapa .

Kita tahu MySQL memiliki kontrol hak istimewa yang komprehensif. Dalam hal ini, jelas bahwa kita bahkan tidak bisa memberikan hak istimewa SELECT pada karyawan HR ini (yang, jika kita lakukan, berarti dia / dia bisa melihat gaji rinci semua orang). Tapi jika dia / dia tidak dapat mengakses tabel gaji, bagaimana karyawan ini bisa mendapatkan informasi agregasi berkaitan dengan gaji? Bagaimana kita bisa memungkinkan karyawan untuk mengambil informasi yang tanpa mengorbankan kebijakan HR?

Jawabannya adalah menggunakan Stored Prosedur yang mengembalikan informasi yang diperlukan dan memberikan karyawan yang berhak dengan hak akses Execute. (Untuk daftar rinci dan penjelasan tentang hak akses pada MySQL, silakan berkonsultasi di dokumentasi resmi. Link di sini adalah untuk MySQL 5.6. Silakan ganti 5.6 dengan versi yang Anda gunakan.)

SP sekarang adalah jembatan, menjembatani user (karyawan HR) dan tabel (gaji), yang mana pengguna tidak memiliki akses langsung.

Itu saja! Dengan SP, kita bisa mendapatkan pengguna untuk menyelesaikan tugas tanpa mengorbankan keamanan database (dan kebijakan SDM)!

Kerugian menggunakan Stored Procedures

Setelah menguraikan semua keuntungan dari menggunakan SP, kita harus jelas tentang beberapa kelemahan dan melihat apakah ada cara untuk memperbaikinya.

  • Tidak ada kontrol versi di SP itu sendiri . Ketika SP dimodifikasi , hal itu diubah , tidak ada jejak sejarah dapat disimpan di sisi server . Ini mungkin membuat beberapa frustrasi ketika pengguna ingin merollback perubahan. Saran saya adalah untuk menulis SP di sisi klien Anda dan meletakkannya di bawah kontrol versi . Ketika SP sudah siap , mudah untuk menyalin kode ke , katakanlah MySQL Workbench dan menciptakan itu pada sisi server . Dengan demikian , kita dapat memiliki beberapa tingkat kontrol versi .
  • Tidak ada cara mudah untuk melakukan “sinkronisasi ” perubahan yang diterapkan dan memaksa semua orang untuk menggunakan versi terbaru , khususnya, ketika masing-masing anggota tim memiliki database lokalnya sendiri untuk tujuan pengembangan dan pengujian . Versi kontrol dapat menjadi solusi namun masih membutuhkan intervensi manual dengan memperbarui salinan lokal dari SP di server db lokal . Cara lain adalah dengan menggunakan “mocking” . Anggota tim dapat dibagi sehingga setidaknya satu orang akan fokus pada pemeliharaan SP dan pelaksanaan panggilan kepada SP dalam kode . Semua orang lain yang membutuhkan hasil dari SP dapat mengembangkan dan menguji bagian mereka menggunakan objek mocking, yaitu , selalu dengan asumsi  panggilan “palsu” pada SP akan mengembalikan hasil yang diinginkan . Dalam tahap selanjutnya, penggabungan dapat dilakukan dengan membuang kode mocking.
  • Sulit untuk membackup/mengekspornya. SP adalah pada sisi server . Pengembang reguler hanya akan memiliki hak dasar ( SELECT , Execute , dll ) dan tidak ada hak admin untuk membackup dan mengekspor . Di satu sisi , saya tidak akan menyebutnya kelemahan melainkan aspek fundamental dari keamanan db. Tidak ada cara, dan tidak dianjurkan di area ini . Disarankan bahwa, dalam sebuah tim , DB admin khusus akan ditunjuk untuk melakukan pekerjaan tersebut . Sebuah backup db biasa juga dapat melayani tujuan backup/ekspor ( dan impor ) .

Membuat Stored Procedure di MySQL

Karena SP disimpan dalam server, dianjurkan untuk membuat SP langsung di server, yaitu, tidak dengan menggunakan PHP atau bahasa pemrograman lain untuk mengeluarkan perintah SQL untuk melakukannya.

Mari kita lihat bagaimana membuat SP di MySQL server, membuat user dan menerapkan hak dan menjalankannya (sebagai user tersebut) SP untuk melihat apakah hasilnya benar. Dalam lingkungan kerja saya, saya menggunakan MySQL Workbench. Alat-alat lain yang tersedia (PHPMyAdmin misalnya) sehingga merasa bebas untuk menggunakan alat-alat yang paling sesuai dengan Anda.

Asumsikan kita memiliki tabel seperti ini:

CREATE TABLE `salary` (
  `empid` int(11) NOT NULL,
  `sal` int(11) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Dan untuk karyawan HR yang perlu mendapatkan informasi yang dikumpulkan di gaji (rata-rata, max, min, dll) dari tabel itu, pertama-tama kita membuat user ‘tr’ seperti ini:

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

and for this user, we only grant EXECUTE privilege to the schema where the salary table resides:

grant execute on hris.*  to tr@`%`

Kita dapat memverifikasi hak istimewa yang diperlukan, semata dengan mengunjungi “Pengguna dan Privileges” di MySQL Bench:

Sekarang kita bikin SP seperti berikut ini:

DELIMITER $$

CREATE PROCEDURE `avg_sal`(out avg_sal decimal)
BEGIN
    select avg(sal) into avg_sal from salary;

END

Catatan: Semua operasi di atas akan membutuhkan peran admin di server MySQL.

Setelah mengeluarkan perintah di MySQL Workbench, SP avg_sal  akan dibuat dan siap untuk dipanggil. Ini akan mengembalikan gaji rata-rata dari tabel gaji.

Untuk menguji apakah pengguna tr benar-benar dapat menjalankan SP tapi tidak harus dapat mengakses tabel gaji, kita dapat beralih peran dengan login ke server MySQL menggunakan pengguna tr. Hal ini dapat dilakukan dengan menciptakan koneksi lain di MySQL Workbench menggunakan sepasang user / password yang berbeda.

Setelah log in sebagai tr, hal pertama yang akan kita perhatikan adalah bahwa pengguna tidak akan dapat melihat tabel dan hanya bisa melihat SP:

Hal ini jelas bahwa pengguna tr tidak akan dapat mendapatkan apa pun dari setiap tabel (sehingga tidak dapat melihat jumlah gaji rinci tabel gaji) tetapi dia mampu mengeksekusi SP yang baru kita buat dan mendapatkan gaji rata-rata perusahaan:

call avg_sal(@out);
select @out;

Gaji rata-rata akan ditampilkan.

Kami sejauh ini melakukan semua persiapan untuk membuat user, memberikan hak istimewa, membuat SP dan menguji menjalankan SP. Selanjutnya kita akan menunjukkan bagaimana memanggil SP yang dari dalam PHP.

Memanggil Stored Procedure dari PHP

Dengan PDO, memanggil SP sangatlah mudah. Kode PHPnya adalah sebagai berikut:

$dbms = 'mysql';

//Replace the below connection parameters to fit your environment
$host = '192.168.1.8';
$db = 'hris';
$user = 'tr';
$pass = 'mypass';
$dsn = "$dbms:host=$host;dbname=$db";

$cn=new PDO($dsn, $user, $pass);

$q=$cn->exec('call avg_sal(@out)');
$res=$cn->query('select @out')->fetchAll();
print_r($res);

$res akan berisi gaji rata-rata dari tabel gaji. Pengguna dapat memproses lebih lanjut outputnya dengan PHP sekarang.

Kesimpulan

Pada artikel ini, kami meninjau komponen lama yang terlupakan dalam database MySQL: Stored Procedures. Keuntungan untuk menggunakan SP yang jelas dan biarkan aku kembali menekankan:. Stored Procedures memungkinkan kita untuk menerapkan kontrol akses database secara kuat ke data tertentu agar sesuai dengan kebutuhan bisnis

Kami juga menggambarkan langkah-langkah dasar untuk membuat stored procedure, membuat pengguna dan menetapkan hak istimewa, dan bagaimana untuk menyebutnya dalam PHP.

Artikel ini tidak mencakup secara penuh mengenai Stored Procedures. Beberapa aspek penting seperti parameter input/output, pernyataan kontrol, kursor, sintaks lengkap, dll tidak dibahas dalam artikel singkat ini.

Jika Anda merasa tertarik, silakan tinggalkan komentar Anda di sini dan kami akan senang untuk membawa lebih banyak artikel mendalam tentang aspek yang berguna dan kuat ini dari MySQL.

http://www.sitepoint.com/stored-procedures-mysql-php/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+PHPMaster_feed+%28PHPMaster%29

Advertisements

About phpgeek programmer

pemimpi yang berharap menjadi the best programmer di zamannya

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s