Prosedur tersimpan (Mesin Database)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

Prosedur tersimpan di SQL Server adalah sekelompok dari satu atau beberapa pernyataan Transact-SQL, atau referensi ke metode bahasa runtime umum (CLR) Microsoft .NET Framework. Prosedur menyerupai konstruksi dalam bahasa pemrograman lain karena dapat:

  • Terima parameter input dan kembalikan beberapa nilai dalam bentuk parameter output ke program panggilan.

  • Berisi pernyataan pemrograman yang melakukan operasi dalam database. Ini termasuk memanggil prosedur lain.

  • Mengembalikan nilai status ke program panggilan untuk menunjukkan keberhasilan atau kegagalan (dan alasan kegagalan).

Manfaat menggunakan prosedur tersimpan

Daftar berikut ini menjelaskan beberapa manfaat menggunakan prosedur.

Mengurangi lalu lintas jaringan server/klien

Perintah dalam prosedur dieksekusi sebagai satu kumpulan kode. Hal ini dapat secara signifikan mengurangi lalu lintas jaringan antara server dan klien karena hanya panggilan untuk menjalankan prosedur yang dikirim melalui jaringan. Tanpa enkapsulasi kode yang disediakan oleh prosedur, setiap baris kode individu harus melintasi jaringan.

Keamanan yang makin kuat

Beberapa pengguna dan program klien dapat melakukan operasi pada objek database yang mendasari melalui prosedur, bahkan jika pengguna dan program tidak memiliki izin langsung pada objek yang mendasarinya. Prosedur mengontrol proses dan aktivitas apa yang dilakukan dan melindungi objek database yang mendasarinya. Hal ini menghilangkan persyaratan untuk memberikan izin pada tingkat objek individual dan menyederhanakan lapisan keamanan.

Klausa EXECUTE AS dapat ditentukan dalam CREATE PROCEDURE pernyataan untuk memungkinkan peniruan identitas pengguna lain, atau memungkinkan pengguna atau aplikasi untuk melakukan aktivitas database tertentu tanpa memerlukan izin langsung pada objek dan perintah yang mendasar. Misalnya, beberapa tindakan seperti TRUNCATE TABLE tidak memiliki izin yang dapat diberikan. Untuk menjalankan TRUNCATE TABLE, pengguna harus memiliki ALTER izin pada tabel yang ditentukan. Memberikan izin pengguna ALTER pada tabel mungkin tidak ideal, karena pengguna secara efektif memiliki izin di luar kemampuan untuk memotong tabel. Dengan menggabungkan TRUNCATE TABLE pernyataan dalam modul dan menentukan bahwa modul dijalankan sebagai pengguna yang memiliki izin untuk mengubah tabel, Anda dapat memperluas izin untuk memotong tabel kepada pengguna yang Anda berikan EXECUTE izin pada modul.

Ketika aplikasi memanggil prosedur melalui jaringan, hanya panggilan untuk menjalankan prosedur yang terlihat. Oleh karena itu, pengguna berbahaya tidak dapat melihat nama objek tabel dan database, menyematkan pernyataan Transact-SQL mereka sendiri, atau mencari data penting.

Menggunakan parameter prosedur membantu melindungi dari serangan injeksi SQL. Karena input parameter diperlakukan sebagai nilai harfiah dan bukan sebagai kode yang dapat dieksekusi, lebih sulit bagi penyerang untuk memasukkan perintah ke dalam pernyataan Transact-SQL di dalam prosedur dan membahayakan keamanan.

Prosedur dapat dienkripsi, membantu mengaburkan kode sumber. Untuk informasi selengkapnya, lihat Enkripsi SQL Server.

Penggunaan kembali kode

Kode untuk setiap operasi database berulang adalah kandidat yang sempurna untuk enkapulasi dalam prosedur. Ini menghilangkan penulisan ulang kode yang sama tanpa perlu, mengurangi ketidakkonsistensian kode, dan memungkinkan akses dan eksekusi kode oleh pengguna atau aplikasi apa pun yang memiliki izin yang diperlukan.

Pemeliharaan yang lebih mudah

Ketika aplikasi klien memanggil prosedur dan menyimpan operasi database di tingkat data, hanya prosedur yang harus diperbarui untuk setiap perubahan dalam database yang mendasarinya. Tingkat aplikasi tetap terpisah dan tidak perlu tahu bagaimana perubahan apa pun pada tata letak database, hubungan, atau proses.

Meningkatkan performa

Secara default, prosedur mengkompilasi pertama kali dijalankan, dan membuat rencana eksekusi yang digunakan kembali untuk eksekusi berikutnya. Karena prosesor kueri tidak perlu membuat paket baru, biasanya membutuhkan lebih sedikit waktu untuk memproses prosedur.

Jika ada perubahan signifikan pada tabel atau data yang direferensikan oleh prosedur, rencana yang telah dikompilasi mungkin benar-benar menyebabkan prosedur berjalan lebih lambat. Dalam hal ini, kompilasi ulang prosedur dan memaksa rencana eksekusi baru dapat meningkatkan performa.

Jenis prosedur tersimpan

Ditentukan pengguna

Prosedur yang ditentukan pengguna dapat dibuat dalam database yang ditentukan pengguna atau di semua database sistem kecuali Resource database. Prosedur ini dapat dikembangkan baik dalam Transact-SQL, atau sebagai referensi ke metode bahasa runtime umum (CLR) Microsoft .NET Framework.

Sementara

Prosedur sementara adalah bentuk prosedur yang ditentukan pengguna. Prosedur sementara seperti prosedur permanen, kecuali prosedur tersebut disimpan di tempdb. Ada dua jenis prosedur sementara: lokal dan global. Mereka berbeda satu sama lain dalam nama mereka, visibilitas mereka, dan ketersediaannya. Prosedur sementara lokal memiliki tanda angka tunggal (#) sebagai karakter pertama nama mereka; mereka hanya terlihat oleh koneksi pengguna saat ini, dan mereka dihapus saat koneksi ditutup. Prosedur sementara global memiliki dua tanda angka (##) sebagai dua karakter pertama nama mereka; mereka terlihat oleh pengguna mana pun setelah dibuat, dan dihapus di akhir sesi terakhir menggunakan prosedur.

Sistem

Prosedur sistem disertakan dengan Mesin Database. Mereka disimpan secara fisik dalam database internal yang tersembunyi Resource dan secara logis muncul dalam sys skema setiap database yang ditentukan sistem dan ditentukan pengguna. Selain itu, msdb database juga berisi prosedur tersimpan sistem dalam dbo skema yang digunakan untuk menjadwalkan pemberitahuan dan pekerjaan. Karena prosedur sistem dimulai dengan awalan sp_, kami sarankan Anda tidak menggunakan awalan ini saat menamai prosedur yang ditentukan pengguna. Untuk daftar lengkap prosedur sistem, lihat Prosedur tersimpan sistem (Transact-SQL).

SQL Server mendukung prosedur sistem yang menyediakan antarmuka dari SQL Server ke program eksternal untuk berbagai aktivitas pemeliharaan. Prosedur yang diperluas ini menggunakan awalan xp_ . Untuk daftar lengkap prosedur yang diperluas, lihat Prosedur tersimpan umum yang diperluas (Transact-SQL).

Diperluas yang ditentukan pengguna

Prosedur yang diperluas memungkinkan pembuatan rutinitas eksternal dalam bahasa pemrograman seperti C. Prosedur ini adalah DLL yang dapat dimuat dan dijalankan secara dinamis oleh instans SQL Server.

Catatan

Prosedur tersimpan yang diperluas akan dihapus dalam versi SQL Server yang akan datang. Jangan gunakan fitur ini dalam pekerjaan pengembangan baru, dan ubah aplikasi yang saat ini menggunakan fitur ini sesegera mungkin. Buat prosedur CLR sebagai gantinya. Metode ini menyediakan alternatif yang lebih kuat dan aman untuk menulis prosedur yang diperluas.

Deskripsi tugas Artikel
Menjelaskan cara membuat prosedur tersimpan. Membuat prosedur tersimpan
Menjelaskan cara mengubah prosedur tersimpan. Mengubah prosedur tersimpan
Menjelaskan cara menghapus prosedur tersimpan. Menghapus prosedur tersimpan
Menjelaskan cara menjalankan prosedur tersimpan. Menjalankan prosedur tersimpan
Menjelaskan cara memberikan izin pada prosedur tersimpan. Memberikan Izin pada prosedur tersimpan
Menjelaskan cara mengembalikan data dari prosedur tersimpan ke aplikasi. Mengembalikan data dari prosedur tersimpan
Menjelaskan cara mengkombinasikan ulang prosedur tersimpan. Kompilasi ulang prosedur tersimpan
Menjelaskan cara mengganti nama prosedur tersimpan. Mengganti nama prosedur tersimpan
Menjelaskan cara melihat definisi prosedur tersimpan. Melihat definisi prosedur tersimpan
Menjelaskan cara melihat dependensi pada prosedur tersimpan. Menampilkan dependensi prosedur tersimpan
Menjelaskan bagaimana parameter digunakan dalam prosedur tersimpan. Parameter

Konten terkait