Kami mempertimbangkan situasi di mana prosedur tersimpan dapat menurunkan kinerja kueri.
Saat mengkompilasi prosedur tersimpan di MS SQL Server 2000, prosedur tersimpan ditempatkan di cache prosedur, yang dapat membantu meningkatkan kinerja saat dijalankan dengan menghilangkan kebutuhan untuk menguraikan, mengoptimalkan, dan mengkompilasi kode prosedur tersimpan.
Di sisi lain, ada jebakan dalam menyimpan kode yang dikompilasi dari prosedur tersimpan yang dapat memiliki efek sebaliknya.
Faktanya adalah bahwa ketika mengkompilasi prosedur tersimpan, rencana eksekusi dari pernyataan-pernyataan yang membentuk kode prosedur dikompilasi, masing-masing, jika prosedur tersimpan yang dikompilasi di-cache, maka rencana eksekusinya juga di-cache, dan oleh karena itu prosedur tersimpan tidak akan dioptimalkan untuk situasi tertentu dan parameter kueri.
Akan melakukan sedikit percobaan untuk menunjukkan ini.
LANGKAH 1. Pembuatan basis data.
Untuk percobaan, mari buat database terpisah.
BUAT DATABASE test_sp_perf
AKTIF (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
MASUK (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
LANGKAH 2. Membuat tabel.
BUAT TABEL sp_perf_test(column1 int, column2 char(5000))
LANGKAH 3. Mengisi tabel dengan string tes. Baris duplikat sengaja ditambahkan ke tabel. 10.000 baris dengan angka dari 1 hingga 10000, dan 10.000 baris dengan angka 50000.
MENYATAKAN @i int
SET @i=1
Ketika saya<10000)
MULAI
INSERT INTO sp_perf_test(column1, column2) VALUES(@i,"Test string #"+CAST(@i as char(8)))
INSERT INTO sp_perf_test(column1, column2) VALUES(50000,"Test string #"+CAST(@i as char(8)))
SET @i= @i+1
AKHIR
PILIH JUMLAH(*) DARI sp_perf_test
PERGILAH
LANGKAH 4. Buat indeks yang tidak berkerumun. Karena rencana eksekusi di-cache dengan prosedur, indeks akan digunakan dengan cara yang sama untuk semua panggilan.
BUAT INDEKS CL_perf_test NONNCLUSTERED PADA sp_perf_test(kolom1)
PERGILAH
LANGKAH 5. Buat prosedur tersimpan. Prosedur hanya mengeksekusi pernyataan SELECT dengan kondisi.
BUAT PROC proc1 (@paramint)
SEBAGAI
PILIH kolom1, kolom2 DARI sp_perf_test WHERE [dilindungi email]
PERGILAH
LANGKAH 6. Jalankan prosedur tersimpan. Saat memulai prosedur yang rentan, parameter selektif digunakan secara khusus. Sebagai hasil dari prosedur, kami mendapatkan 1 baris. Rencana eksekusi menunjukkan penggunaan indeks non-cluster, seperti kueri selektif dan ini adalah cara terbaik untuk mengekstrak string. Prosedur yang dioptimalkan untuk mengambil satu baris disimpan dalam cache prosedur.
EXEC proc1 1234
PERGILAH
LANGKAH 7. Menjalankan prosedur tersimpan dengan parameter non-selektif. Nilai 50000 digunakan sebagai parameter. Baris dengan nilai kolom pertama sekitar 10.000, masing-masing, menggunakan indeks non-cluster dan operasi pencarian bookmark tidak efisien, tetapi karena kode yang dikompilasi dengan rencana eksekusi disimpan dalam prosedural cache, itu akan digunakan. Rencana eksekusi menunjukkan ini, serta fakta bahwa operasi pencarian bookmark dilakukan untuk 9999 baris.
EXEC proc1 50000
PERGILAH
LANGKAH 8. Melakukan pemilihan baris dengan bidang pertama sama dengan 50000. Ketika kueri terpisah dijalankan, kueri dioptimalkan dan dikompilasi dengan nilai tertentu dari kolom pertama. Akibatnya, pengoptimal kueri menentukan bahwa bidang diduplikasi berkali-kali dan memutuskan untuk menggunakan operasi pemindaian tabel, yang dalam hal ini jauh lebih efisien daripada menggunakan indeks non-cluster.
PILIH kolom1, kolom2 DARI sp_perf_test WHERE kolom1=50000
PERGILAH
Dengan demikian, kita dapat menyimpulkan bahwa penggunaan prosedur tersimpan mungkin tidak selalu meningkatkan kinerja kueri. Anda harus sangat berhati-hati dengan prosedur tersimpan yang bekerja dengan hasil dengan jumlah baris yang bervariasi dan menggunakan rencana eksekusi yang berbeda.
Anda dapat menggunakan skrip untuk mengulangi percobaan di server MS SQL Anda sendiri.
Jenis Prosedur Tersimpan
Ada beberapa jenis di SQL Server prosedur tersimpan.
- sistemik prosedur tersimpan dirancang untuk melakukan berbagai tindakan administratif. Hampir semua tindakan administrasi server dilakukan dengan bantuan mereka. Kita dapat mengatakan bahwa sistem prosedur tersimpan adalah antarmuka yang menyediakan pekerjaan dengan tabel sistem, yang, pada akhirnya, turun untuk mengubah, menambah, menghapus, dan mengambil data dari tabel sistem dari database pengguna dan sistem. sistemik prosedur tersimpan diawali dengan sp_ , disimpan dalam database sistem, dan dapat dipanggil dalam konteks database lainnya.
- Kebiasaan prosedur tersimpan melaksanakan tindakan tertentu. Prosedur tersimpan- objek database yang lengkap. Akibatnya, masing-masing prosedur tersimpan terletak di database tertentu, di mana ia dieksekusi.
- Sementara prosedur tersimpan hanya ada untuk waktu yang singkat, setelah itu mereka secara otomatis dihancurkan oleh server. Mereka dibagi menjadi lokal dan global. lokal sementara prosedur tersimpan hanya dapat dipanggil dari koneksi di mana mereka dibuat. Saat membuat prosedur seperti itu, harus diberi nama yang dimulai dengan karakter # tunggal. Seperti semua objek sementara, prosedur tersimpan jenis ini secara otomatis dihapus ketika pengguna memutuskan sambungan, memulai ulang, atau menghentikan server. Global sementara prosedur tersimpan tersedia untuk koneksi server apa pun yang memiliki prosedur yang sama. Untuk mendefinisikannya, cukup dengan memberinya nama yang dimulai dengan karakter ## . Prosedur ini akan dihapus saat server dimulai ulang atau dihentikan, atau saat sambungan yang konteksnya dibuat ditutup.
Membuat, Memodifikasi, dan Menghapus Prosedur Tersimpan
Penciptaan prosedur tersimpan melibatkan penyelesaian tugas-tugas berikut:
- menentukan jenis prosedur tersimpan: sementara atau adat. Selain itu, Anda dapat membuat sistem Anda sendiri prosedur tersimpan, memberinya nama dengan awalan sp_, dan menempatkannya di database sistem. Prosedur seperti itu akan tersedia dalam konteks database apa pun di server lokal;
- perencanaan akses. Saat membuat prosedur tersimpan perlu diingat bahwa itu akan memiliki hak akses yang sama ke objek database sebagai pengguna yang membuatnya;
- definisi parameter prosedur tersimpan. Seperti prosedur yang termasuk dalam kebanyakan bahasa pemrograman, prosedur tersimpan mungkin memiliki parameter input dan output;
- pengembangan kode prosedur tersimpan. Kode prosedur dapat berisi urutan perintah SQL apa pun, termasuk memanggil orang lain. prosedur tersimpan.
Membuat yang baru dan memodifikasi yang sudah ada prosedur tersimpan dilakukan dengan perintah berikut:
<определение_процедуры>::= (BUAT | ALTER ) nama_prosedur [;angka] [(@parameter_name tipe data ) [=default] ][,...n] AS sql_statement [...n]
Pertimbangkan parameter dari perintah ini.
Menggunakan awalan sp_ , # , ## , prosedur yang dibuat dapat didefinisikan sebagai sistem atau prosedur sementara. Seperti yang Anda lihat dari sintaks perintah, tidak diperbolehkan untuk menentukan nama pemilik yang menjadi milik prosedur yang dibuat, serta nama database di mana ia harus ditempatkan. Jadi, untuk mengakomodasi yang dibuat prosedur tersimpan dalam database tertentu, Anda harus menjalankan perintah CREATE PROCEDURE dalam konteks database tersebut. Ketika ditangani dari tubuh prosedur tersimpan Nama yang disingkat dapat digunakan untuk objek dalam database yang sama, yaitu tanpa menentukan nama database. Saat Anda ingin merujuk ke objek yang terletak di database lain, menentukan nama database diperlukan.
Nomor dalam nama adalah nomor identifikasi prosedur tersimpan, yang secara unik mendefinisikannya dalam sekelompok prosedur. Untuk kenyamanan mengelola prosedur, secara logis tipe yang sama prosedur tersimpan dapat dikelompokkan dengan memberi mereka nama yang sama tetapi nomor identifikasi yang berbeda.
Untuk melewatkan data input dan output dalam file yang dibuat prosedur tersimpan parameter dapat digunakan, yang namanya, seperti nama variabel lokal, harus dimulai dengan simbol @. Satu prosedur tersimpan Anda dapat menentukan beberapa opsi yang dipisahkan dengan koma. Badan prosedur tidak boleh menggunakan variabel lokal yang namanya sama dengan nama parameter prosedur.
Untuk menentukan tipe data yang sesuai parameter prosedur tersimpan, semua tipe data SQL baik-baik saja, termasuk yang ditentukan pengguna. Namun, tipe data CURSOR hanya dapat digunakan sebagai parameter keluaran prosedur tersimpan, yaitu dengan kata kunci OUTPUT .
Kehadiran kata kunci OUTPUT berarti bahwa parameter yang sesuai dimaksudkan untuk mengembalikan data dari prosedur tersimpan. Namun, ini tidak berarti sama sekali bahwa parameter tersebut tidak cocok untuk meneruskan nilai ke prosedur tersimpan. Menentukan kata kunci OUTPUT menginstruksikan server untuk keluar dari prosedur tersimpan menetapkan nilai parameter saat ini ke variabel lokal yang ditentukan saat prosedur dipanggil sebagai nilai parameter. Perhatikan bahwa ketika menentukan kata kunci OUTPUT, nilai parameter yang sesuai saat memanggil prosedur hanya dapat diatur menggunakan variabel lokal. Ekspresi atau konstanta apa pun yang diizinkan untuk parameter normal tidak diizinkan.
Kata kunci VARYING digunakan bersama dengan
Prosedur tersimpan prosedur tersimpan) adalah objek program database bernama. SQL Server memiliki beberapa jenis prosedur tersimpan.
Prosedur tersimpan sistem prosedur tersimpan sistem) disediakan oleh pengembang DBMS dan digunakan untuk melakukan tindakan pada katalog sistem atau mendapatkan informasi sistem. Nama mereka biasanya dimulai dengan awalan "sp_". Prosedur tersimpan dari semua jenis dijalankan dengan perintah EXECUTE, yang dapat disingkat menjadi EXEC. Misalnya, prosedur tersimpan sp_helplogins, dijalankan tanpa parameter, menghasilkan dua laporan nama akun (Bahasa Inggris) login) dan pengguna yang sesuai di setiap database (Bahasa Inggris) pengguna).
EXEC sp_helplogin;
Untuk memberikan gambaran tentang tindakan yang dilakukan menggunakan prosedur tersimpan sistem, dalam tabel. 10.6 menunjukkan beberapa contoh. Secara total, ada lebih dari seribu prosedur tersimpan sistem di SQL Server.
Tabel 10.6
Contoh Prosedur Tersimpan Sistem SQL Server
Pengguna dapat membuat prosedur tersimpan di database pengguna dan dalam database untuk objek sementara. Dalam kasus terakhir, prosedur tersimpan adalah sementara. Seperti tabel sementara, nama prosedur tersimpan sementara harus dimulai dengan awalan "#" jika prosedur tersimpan sementara lokal, atau "##" jika prosedur global. Prosedur sementara lokal hanya dapat digunakan dalam koneksi yang dibuat, prosedur global juga dapat digunakan dalam koneksi lain.
Objek SQL Server yang dapat diprogram dapat dibuat menggunakan alat Transact-SQL atau menggunakan rakitan (Bahasa Inggris) assembly) di lingkungan CRL (Common Language Runtime) dari Microsoft .Net Framework. Dalam tutorial ini, hanya metode pertama yang akan dipertimbangkan.
Untuk membuat prosedur tersimpan, gunakan pernyataan CREATE PROCEDURE (dapat disingkat PROC), formatnya diberikan di bawah ini:
BUAT (PROSEDUR I PROSEDUR) proc_name [ ; nomor]
[(tipe_data gparameter )
[“bawaan] |
[DENGAN
[ UNTUK REPLIKASI ]
AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )
Jika prosedur tersimpan (atau pemicu, fungsi, tampilan) dibuat dengan opsi ENKRIPSI, kodenya diubah sedemikian rupa sehingga teks menjadi tidak dapat dibaca. Pada saat yang sama, seperti disebutkan dalam , algoritme yang digunakan dibawa dari versi SQL Server sebelumnya dan tidak dapat dianggap sebagai algoritme perlindungan yang andal - ada utilitas yang memungkinkan Anda melakukan konversi terbalik dengan cepat.
Opsi RECOMPILE menentukan bahwa setiap kali prosedur dipanggil, sistem akan mengkompilasi ulang teks. Dalam kasus biasa, prosedur yang dikompilasi saat pertama kali dijalankan disimpan dalam cache, yang memungkinkan Anda untuk meningkatkan kinerja.
EXECUTE AS mendefinisikan konteks keamanan di mana prosedur akan dieksekusi. Selanjutnya, salah satu nilai f CALLER | DIRI | PEMILIK | "nama belakang"). CALLER adalah nilai default dan berarti kode akan dieksekusi dalam konteks keamanan pengguna yang memanggil modul ini. Oleh karena itu, pengguna harus memiliki izin tidak hanya untuk objek yang dapat diprogram itu sendiri, tetapi juga untuk objek database lain yang terpengaruh olehnya. EXECUTE AS SELF berarti menggunakan konteks pengguna yang membuat atau memodifikasi objek yang dapat diprogram. OWNER menetapkan bahwa kode akan dieksekusi dalam konteks pemilik prosedur saat ini. Jika tidak ada pemilik yang ditentukan untuk itu, maka pemilik skema yang menjadi miliknya diasumsikan. EXECUTE AS "user_name" memungkinkan Anda untuk secara eksplisit menentukan nama pengguna (dalam tanda kutip tunggal).
Parameter dapat ditentukan untuk suatu prosedur. Ini adalah variabel lokal yang digunakan untuk meneruskan nilai ke prosedur. Jika sebuah parameter dideklarasikan dengan kata kunci OUTPUT (atau disingkat OUT), itu adalah output: nilai yang diberikan padanya dalam prosedur dapat digunakan oleh program yang memanggil prosedur setelah prosedur berakhir. Kata kunci READONLY berarti bahwa nilai parameter tidak dapat diubah di dalam prosedur tersimpan.
Parameter dapat diberikan nilai default, yang akan digunakan jika nilai parameter tidak ditentukan secara eksplisit saat memanggil prosedur. Pertimbangkan sebuah contoh:
BUAT surma PROC (@a int, @b int=0,
©hasil int OUTPUT) AS
SET @hasil=0a+0b
Kami telah membuat prosedur dengan tiga parameter, dan parameter @b memiliki nilai default =0, dan parameter @result adalah parameter keluaran: melaluinya, nilai dikembalikan ke program pemanggil. Tindakan yang dilakukan cukup sederhana - parameter keluaran menerima nilai jumlah dari dua masukan.
Saat bekerja di SQL Server Management Studio, prosedur tersimpan yang dibuat dapat ditemukan di bagian objek database yang dapat diprogram (Bahasa Inggris) Programabilitas) di bagian untuk prosedur tersimpan (Gambar 10.2).
Saat memanggil prosedur, variabel dan konstanta dapat digunakan sebagai parameter input. Mari kita pertimbangkan dua contoh. Yang pertama, parameter input prosedur secara eksplisit ditetapkan oleh konstanta, kata kunci OUTPUT ditentukan untuk parameter output dalam panggilan. Pada opsi kedua, nilai variabel digunakan sebagai parameter input pertama, dan parameter kedua ditentukan menggunakan kata kunci DEFAULT bahwa nilai default harus digunakan:
Beras. 10.2.
MENYATAKAN @dengan int;
EXEC summa 10,5,@c OUTPUT;
PRINT0c; - 15 akan ditampilkan
MENYATAKAN Gi int = 5;
- saat menelepon, gunakan nilai default
EXEC summa Gi,DEFAULT , OUTPUT 0s;
PRINT0c; - 5 akan ditampilkan
Pertimbangkan sekarang contoh dengan analisis kode kembali yang prosedurnya berakhir. Biarlah perlu untuk menghitung berapa banyak buku dalam tabel Bookl yang diterbitkan dalam rentang tahun tertentu. Dalam hal ini, jika tahun awal ternyata lebih besar dari yang terakhir, prosedur mengembalikan "1" dan tidak dihitung, jika tidak, kami menghitung jumlah buku dan mengembalikan 0:
BUAT PROC dbo.rownum(0Tahun pertama int, GLastYear int, 0hasil int OUTPUT) SEBAGAI
JIKA 0Tahun Pertama>0Tahun Terakhir RETURN 1
SET @result= (PILIH JUMLAH(*) DARI dbo.Bookl
DIMANA ANTARA 0Tahun Pertama DAN 0Tahun Terakhir) ;
Pertimbangkan varian pemanggilan prosedur ini, di mana kode kembali disimpan dalam variabel integer 0ret, setelah itu nilainya dianalisis (dalam hal ini adalah 1). Fungsi CAST yang digunakan dalam pernyataan PRINT digunakan untuk mengubah nilai variabel integer Gres menjadi tipe string:
MENYATAKAN 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
JIKA 0ret=l PRINT "Tahun awal lebih besar dari tahun akhir"
PRINT "Jumlah buku"+ CAST(Gres sebagai varchar(20))
Prosedur tersimpan tidak hanya dapat membaca data dari tabel, tetapi juga mengubah data dan bahkan membuat tabel dan sejumlah objek database lainnya.
Namun, skema, fungsi, pemicu, prosedur, dan tampilan tidak dapat dibuat dari prosedur tersimpan.
Contoh berikut mengilustrasikan kemungkinan dan masalah yang terkait dengan ruang lingkup objek sementara. Prosedur tersimpan berikut memeriksa keberadaan tabel sementara #Tab2; jika tabel ini tidak ada, itu membuatnya. Setelah itu, nilai dua kolom dimasukkan ke dalam tabel #Tab2, dan isi tabel ditampilkan oleh pernyataan SELECT:
BUAT PROC My_Procl (@id int, @nama varchar(30))
JIKA OBJECT_ID("tempdb.dbo.#Tab21) NULL
MASUKKAN KE dbo.#Tab2 (id, nama) NILAI (0id,0nama)
PILIH * DARI dbo. #Tab2 -#1
Sebelum panggilan pertama ke prosedur tersimpan, mari buat tabel sementara #Tab2 yang digunakan di dalamnya. Perhatikan operator EXEC. Dalam contoh sebelumnya, parameter diteruskan ke prosedur "berdasarkan posisi", tetapi dalam kasus ini, format yang berbeda untuk melewati parameter digunakan - "berdasarkan nama", nama parameter dan nilainya secara eksplisit ditunjukkan:
CREATE TABLE dbo.#Tab2 (id int, nama varchar(30));
EXEC My_Procl 0name="lvan", 0id=2;
PILIH * DARI dbo.#Tab2; –#2
Dalam contoh di atas, pernyataan SELECT akan dieksekusi dua kali: pertama kali - di dalam prosedur, kedua kali - dari fragmen kode panggilan (ditandai dengan komentar "No. 2").
Sebelum pemanggilan prosedur kedua, mari kita hapus tabel sementara #Tab2. Kemudian tabel sementara dengan nama yang sama akan dibuat dari prosedur tersimpan:
DROP TABLE dbo.#Tab2;
EXEC My_Procl 0name="Ivan", 0id=2;
PILIH * DARI dbo.#Tab2; –#2
Dalam hal ini, hanya pernyataan SELECT di dalam prosedur (dengan komentar "Xa 1") yang akan menampilkan data. Menjalankan SELECT "#2" akan menghasilkan kesalahan, karena tabel sementara yang dibuat dalam prosedur tersimpan sudah akan dihapus dari database tempdb saat prosedur kembali.
Anda dapat menghapus prosedur tersimpan menggunakan pernyataan DROP PROCEDURE. Formatnya ditunjukkan di bawah ini. Dengan satu pernyataan, Anda dapat menghapus beberapa prosedur tersimpan dengan mencantumkannya dipisahkan dengan koma:
DROP (PROCEDUR I) ( prosedur ) [
Misalnya, mari kita hapus prosedur summa yang dibuat sebelumnya:
JATUH PROC summa;
Anda dapat membuat perubahan pada prosedur yang ada (pada kenyataannya, menimpanya) menggunakan pernyataan ALTER PROCEDURE (izinkan
singkatan PROC). Dengan pengecualian kata kunci ALTER, format pernyataan hampir sama dengan CREATE PROCEDURE. Sebagai contoh, mari kita ubah prosedur dbo. rownum dengan mengaturnya untuk dieksekusi dalam konteks keamanan pemilik:
ALTER PROC dbo.rownum(STahun pertama int,
SLastTahun int, Hasil int OUTPUT)
DENGAN EXECUTE SEBAGAI Pemilik - atur opsi
JIKA 0Tahun Pertama>0Tahun Terakhir PENGEMBALIAN 1 LAGI DIMULAI
SET 0result= (PILIH JUMLAH(*) DARI dbo.Bookl
DIMANA ANTARA STahun Pertama DAN TAHUN TERAKHIR);
Dalam beberapa kasus, mungkin perlu untuk menghasilkan perintah secara dinamis dan menjalankannya di server database. Tugas ini juga dapat diselesaikan dengan menggunakan operator EXEC. Contoh berikut memilih rekaman dari tabel Bookl berdasarkan kondisi bahwa atribut Tahun sama dengan nilai yang ditentukan oleh variabel:
MENYATAKAN 0y int = 2000;
EXEC("PILIH * FROM dbo.Bookl WHERE = " [dilindungi email]) ;
Eksekusi instruksi yang dihasilkan secara dinamis menciptakan prasyarat untuk implementasi serangan komputer seperti "injeksi SQL" (Bahasa Inggris) injeksi SQL). Inti dari serangan itu adalah pelaku menyuntikkan kode SQL-nya sendiri ke dalam kueri yang dibuat secara dinamis. Ini biasanya terjadi ketika parameter inline diambil dari hasil input pengguna.
Mari kita ubah sedikit contoh sebelumnya:
MENYATAKAN 0y varchar(100);
SET 0y="2OOO"; - inilah yang kami terima dari pengguna
Jika kita berasumsi bahwa kita menerima nilai string yang ditetapkan dalam pernyataan SET dari pengguna (tidak peduli bagaimana, misalnya, melalui aplikasi web), maka contoh tersebut menggambarkan perilaku "biasa" dari kode kita.
MENYATAKAN 0y varchar(100);
SET 0y="2000; HAPUS DARI dbo.Book2"; – injeksi
EXEC("PILIH * DARI dbo.Book2 WHERE="+0y);
Direkomendasikan, bila memungkinkan, untuk menggunakan prosedur tersimpan sistem sp_executcsql dalam kasus seperti itu, yang memungkinkan Anda untuk mengontrol jenis parameter, yang merupakan salah satu hambatan untuk injeksi SQL. Tanpa mempertimbangkan formatnya secara rinci, kami akan menganalisis contoh yang serupa dengan yang disajikan sebelumnya:
EXECUTE sp_executesql
N"PILIH * FROM dbo.Bookl WHERE =0y",
Ini secara eksplisit menentukan jenis parameter yang digunakan dalam kueri, dan SQL Server akan mengontrolnya selama eksekusi. Huruf "N" sebelum tanda kutip menunjukkan bahwa ini adalah konstanta literal Unicode, seperti yang disyaratkan oleh prosedur. Parameter tidak hanya dapat diberikan nilai konstan, tetapi juga nilai variabel lain.
Prosedur tersimpan adalah tipe khusus dari kumpulan pernyataan Transact-SQL yang dibuat menggunakan bahasa SQL dan ekstensi prosedural. Perbedaan utama antara paket dan prosedur tersimpan adalah bahwa yang terakhir disimpan sebagai objek database. Dengan kata lain, prosedur tersimpan disimpan di sisi server untuk meningkatkan kinerja dan konsistensi tugas yang berulang.
Mesin Database mendukung prosedur tersimpan dan prosedur sistem. Prosedur tersimpan dibuat dengan cara yang sama seperti semua objek database lainnya, mis. menggunakan bahasa DDL. Prosedur Sistem disediakan oleh Database Engine dan dapat digunakan untuk mengakses dan mengubah informasi dalam katalog sistem.
Saat membuat prosedur tersimpan, Anda dapat menentukan daftar parameter opsional. Dengan demikian, prosedur akan mengambil argumen yang sesuai setiap kali dipanggil. Prosedur tersimpan dapat mengembalikan nilai yang berisi informasi yang ditentukan pengguna atau, jika terjadi kesalahan, pesan kesalahan yang sesuai.
Prosedur tersimpan dikompilasi terlebih dahulu sebelum disimpan sebagai objek dalam database. Bentuk prosedur yang telah dikompilasi disimpan dalam database dan digunakan setiap kali dipanggil. Properti prosedur tersimpan ini memberikan manfaat penting untuk menghilangkan (dalam hampir semua kasus) kompilasi ulang prosedur dan peningkatan kinerja yang sesuai. Properti prosedur tersimpan ini juga memiliki efek positif pada jumlah data yang dipertukarkan antara sistem database dan aplikasi. Secara khusus, panggilan ke prosedur tersimpan beberapa ribu byte mungkin memerlukan kurang dari 50 byte. Ketika banyak pengguna melakukan tugas berulang menggunakan prosedur tersimpan, efek kumulatif dari penghematan ini bisa menjadi signifikan.
Prosedur tersimpan juga dapat digunakan untuk tujuan berikut:
untuk membuat log log tentang tindakan dengan tabel database.
Penggunaan prosedur tersimpan memberikan tingkat kontrol keamanan yang melampaui keamanan yang diberikan oleh penggunaan pernyataan GRANT dan REVOKE, yang memberikan hak akses berbeda kepada pengguna. Ini dimungkinkan karena otorisasi untuk menjalankan prosedur tersimpan tidak bergantung pada otorisasi untuk memodifikasi objek yang terdapat dalam prosedur tersimpan, seperti yang dijelaskan di bagian berikutnya.
Prosedur tersimpan yang log menulis dan/atau membaca ke tabel memberikan keamanan tambahan untuk database. Dengan menggunakan prosedur tersebut, administrator database dapat melacak modifikasi yang dibuat ke database oleh pengguna atau aplikasi.
Membuat dan Menjalankan Prosedur Tersimpan
Prosedur tersimpan dibuat menggunakan pernyataan BUAT PROSEDUR, yang memiliki sintaks berikut:
CREATE PROC proc_name [((@param1) type1 [ BERBAGAI] [= default1] )] (, …) SEBAGAI batch | NAMA EKSTERNAL nama_metode Konvensi sintaksis
Parameter schema_name menentukan nama skema yang ditetapkan oleh pemilik prosedur tersimpan yang dihasilkan. Parameter proc_name menentukan nama prosedur tersimpan. Parameter @param1 adalah parameter prosedur (argumen formal) yang tipe datanya ditentukan oleh parameter tipe1. Parameter prosedur bersifat lokal di dalam prosedur, sama seperti variabel lokal bersifat lokal di dalam sebuah paket. Parameter prosedur adalah nilai yang dilewatkan oleh pemanggil ke prosedur untuk digunakan di dalamnya. Parameter default1 menentukan nilai default untuk parameter prosedur yang sesuai. (Nilai default juga bisa NULL.)
pilihan KELUARAN menentukan bahwa parameter prosedur dapat dikembalikan dan dapat digunakan untuk mengembalikan nilai dari prosedur tersimpan ke prosedur atau sistem pemanggil.
Seperti disebutkan sebelumnya, bentuk prosedur yang telah dikompilasi disimpan dalam database dan digunakan setiap kali dipanggil. Jika, untuk beberapa alasan, prosedur tersimpan perlu dikompilasi setiap kali dipanggil, deklarasi prosedur menggunakan opsi DENGAN REKOMPIL. Menggunakan opsi WITH RECOMPILE meniadakan salah satu manfaat terpenting dari prosedur tersimpan: peningkatan kinerja karena kompilasi tunggal. Oleh karena itu, opsi WITH RECOMPILE hanya boleh digunakan jika sering terjadi perubahan pada objek database yang digunakan oleh prosedur tersimpan.
EKSEKUSI SEBAGAI penawaran mendefinisikan konteks keamanan di mana prosedur tersimpan harus dijalankan setelah dipanggil. Dengan menyetel konteks ini, Mesin Database dapat mengontrol pemilihan akun pengguna untuk memeriksa izin akses pada objek yang dirujuk oleh prosedur tersimpan ini.
Secara default, hanya anggota peran server tetap sysadmin dan peran basis data tetap db_owner atau db_ddladmin yang dapat menggunakan pernyataan CREATE PROCEDURE. Namun, anggota peran ini dapat memberikan hak ini kepada pengguna lain menggunakan instruksi PROSEDUR PEMBUATAN HIBAH.
Contoh di bawah ini menunjukkan cara membuat prosedur tersimpan sederhana untuk bekerja dengan tabel Proyek:
GUNAKAN SampleDb; GO CREATE PROCEDURE Meningkatkan Anggaran (@persen INT=5) SEBAGAI UPDATE Project SET Anggaran = Anggaran + Anggaran * @persen/100;
Seperti disebutkan sebelumnya, untuk memisahkan dua paket digunakan instruksi PERGI. Pernyataan CREATE PROCEDURE tidak dapat digabungkan dengan pernyataan Transact-SQL lainnya dalam batch yang sama. Prosedur tersimpan PeningkatanBudget meningkatkan anggaran untuk semua proyek dengan persentase tertentu, yang ditentukan oleh parameter @percent. Prosedur juga mendefinisikan nilai persentase default (5) yang diterapkan jika argumen ini tidak ada selama eksekusi prosedur.
Prosedur tersimpan dapat mengakses tabel yang tidak ada. Properti ini memungkinkan Anda untuk men-debug kode prosedur tanpa terlebih dahulu membuat tabel yang sesuai dan bahkan tanpa menghubungkan ke server tujuan.
Tidak seperti prosedur tersimpan dasar, yang selalu disimpan dalam database saat ini, dimungkinkan untuk membuat prosedur tersimpan sementara, yang selalu ditempatkan di tempdb database sistem sementara. Salah satu alasan untuk membuat prosedur tersimpan sementara mungkin untuk menghindari eksekusi berulang dari kelompok pernyataan tertentu saat menghubungkan ke database. Anda dapat membuat prosedur sementara lokal atau global. Untuk melakukannya, nama prosedur lokal ditentukan dengan karakter # tunggal (#proc_name), dan nama prosedur global ditentukan dengan karakter ganda (##proc_name).
Prosedur tersimpan sementara lokal hanya dapat dijalankan oleh pengguna yang membuatnya, dan hanya selama koneksi ke database tempat prosedur tersebut dibuat. Prosedur sementara global dapat dijalankan oleh semua pengguna, tetapi hanya sampai koneksi terakhir yang menjalankannya (biasanya koneksi pembuat prosedur) dihentikan.
Siklus hidup prosedur tersimpan terdiri dari dua fase: pembuatannya dan eksekusinya. Setiap prosedur dibuat sekali dan dieksekusi berkali-kali. Prosedur tersimpan dijalankan oleh EKSEKUSI pernyataan pengguna yang memiliki prosedur atau memiliki hak EXECUTE untuk mengakses prosedur. Pernyataan EXECUTE memiliki sintaks berikut:
[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] nilai | [@parameter1=] @variable ] | DEFAULT).. Konvensi Sintaks
Dengan pengecualian parameter return_status, semua parameter pernyataan EXECUTE memiliki nilai boolean yang sama dengan parameter pernyataan CREATE PROCEDURE dengan nama yang sama. Parameter return_status mendefinisikan variabel integer yang menyimpan status pengembalian prosedur. Nilai dapat diberikan ke parameter menggunakan konstanta (nilai) atau variabel lokal (@variabel). Urutan nilai parameter bernama tidak penting, tetapi nilai parameter tanpa nama harus diberikan dalam urutan yang ditentukan dalam pernyataan CREATE PROCEDURE.
klausa DEFAULT memberikan nilai default untuk parameter prosedur yang ditentukan dalam definisi prosedur. Ketika prosedur mengharapkan nilai untuk parameter yang tidak ada nilai default yang telah ditentukan dan parameternya hilang, atau kata kunci DEFAULT ditentukan, kesalahan terjadi.
Ketika pernyataan EXECUTE adalah pernyataan pertama dalam sebuah batch, kata kunci EXECUTE dapat dihilangkan. Namun, lebih aman untuk memasukkan kata ini di setiap paket. Penggunaan pernyataan EXECUTE ditunjukkan pada contoh di bawah ini:
GUNAKAN SampleDb; EXECUTE Kenaikan Anggaran 10;
Pernyataan EXECUTE dalam contoh ini menjalankan prosedur yang tersimpan Peningkatan Anggaran, yang meningkatkan anggaran semua proyek sebesar 10%.
Contoh berikut menunjukkan cara membuat prosedur tersimpan untuk memproses data dalam tabel Employee dan Works_on:
Prosedur ModifyEmpId dalam contoh menggambarkan penggunaan prosedur tersimpan sebagai bagian dari proses integritas referensial (dalam hal ini antara tabel Employee dan Works_on). Prosedur tersimpan semacam itu dapat digunakan di dalam definisi pemicu, yang sebenarnya memberlakukan integritas referensial.
Contoh berikut menunjukkan penggunaan klausa OUTPUT dalam prosedur tersimpan:
Prosedur tersimpan ini dapat dijalankan dengan menggunakan pernyataan berikut:
MENYATAKAN @quantityDeleteEmployee INT; EXECUTE HapusKaryawan @empId=18316, @ [dilindungi email] KELUARAN; PRINT N"Karyawan dihapus: " + convert(nvarchar(30), @quantityDeleteEmployee);
Prosedur ini menghitung jumlah proyek yang dikerjakan oleh karyawan dengan nomor personel @empId dan memberikan nilai yang dihasilkan ke parameter ©counter. Setelah menghapus semua baris untuk nomor personel tertentu dari tabel Employee dan Works_on, nilai yang dihitung ditetapkan ke variabel @quantityDeleteEmployee.
Nilai parameter dikembalikan ke prosedur pemanggilan hanya jika opsi OUTPUT ditentukan. Pada contoh di atas, prosedur DeleteEmployee meneruskan parameter @counter ke prosedur pemanggilan, sehingga prosedur tersimpan mengembalikan nilai ke sistem. Oleh karena itu, parameter @counter harus ditentukan baik dalam opsi OUTPUT saat mendeklarasikan prosedur, dan dalam pernyataan EXECUTE saat memanggilnya.
klausa WITH RESULTS SETS dari pernyataan EXECUTE
Di SQL Server 2012, pernyataan EXECUTE diketik DENGAN klausa RESULTS SETS A yang, dalam kondisi tertentu, dapat mengubah bentuk kumpulan hasil dari prosedur tersimpan.
Dua contoh berikut akan membantu menjelaskan kalimat ini. Contoh pertama adalah contoh pengantar yang menunjukkan seperti apa hasilnya jika klausa WITH RESULTS SETS dihilangkan:
Prosedur EmployeeInDept adalah prosedur sederhana yang menampilkan nomor personel dan nama belakang semua karyawan yang bekerja di departemen tertentu. Nomor departemen adalah parameter prosedur dan harus ditentukan saat memanggil prosedur. Menjalankan prosedur ini menghasilkan tabel dengan dua kolom yang judulnya cocok dengan nama kolom yang sesuai dalam tabel database, mis. id dan nama belakang. Untuk mengubah header kolom hasil (serta tipe datanya), SQL Server 2012 menggunakan klausa WITH RESULTS SETS yang baru. Penerapan klausa ini ditunjukkan pada contoh di bawah ini:
GUNAKAN SampleDb; EXEC EmployeeInDept "d1" DENGAN RESULT SETS (( INT NOT NULL, [Last Name] CHAR(20) NOT NULL));
Hasil dari menjalankan prosedur tersimpan yang dipanggil dengan cara ini adalah sebagai berikut:
Seperti yang Anda lihat, menjalankan prosedur tersimpan menggunakan klausa WITH RESULT SETS dalam pernyataan EXECUTE memungkinkan Anda mengubah nama dan tipe data kolom kumpulan hasil yang dihasilkan oleh prosedur. Dengan demikian, fungsionalitas baru ini memberikan lebih banyak fleksibilitas dalam menjalankan prosedur tersimpan dan menempatkan hasilnya di tabel baru.
Mengubah Struktur Prosedur Tersimpan
Mesin Database juga mendukung pernyataan MENGUBAH PROSEDUR untuk memodifikasi struktur prosedur tersimpan. Pernyataan ALTER PROCEDURE biasanya digunakan untuk memodifikasi pernyataan Transact-SQL dalam suatu prosedur. Semua parameter dari pernyataan ALTER PROCEDURE memiliki arti yang sama dengan parameter dari pernyataan CREATE PROCEDURE dengan nama yang sama. Tujuan utama menggunakan pernyataan ini adalah untuk menghindari mengesampingkan izin prosedur tersimpan yang ada.
Mesin Database mendukung Tipe data KURSOR. Tipe data ini digunakan untuk mendeklarasikan kursor dalam prosedur tersimpan. kursor adalah konstruksi pemrograman yang digunakan untuk menyimpan hasil kueri (biasanya sekumpulan baris) dan untuk memungkinkan pengguna menampilkan hasil tersebut baris demi baris.
Untuk menghapus satu atau sekelompok prosedur tersimpan, gunakan DROP PROSEDUR pernyataan. Hanya pemilik prosedur tersimpan atau anggota peran tetap db_owner dan sysadmin yang dapat menghapus prosedur tersimpan.
Prosedur tersimpan dan runtime bahasa umum
SQL Server mendukung Common Language Runtime (CLR), yang memungkinkan Anda untuk mengembangkan berbagai objek database (prosedur tersimpan, fungsi yang ditentukan pengguna, pemicu, agregat yang ditentukan pengguna, dan tipe data yang ditentukan pengguna) menggunakan C# dan Visual Basic. Runtime bahasa umum juga memungkinkan objek ini dieksekusi menggunakan sistem runtime umum.
Runtime bahasa umum diaktifkan dan dinonaktifkan melalui opsi clr_enabled prosedur sistem sp_configure, yang diluncurkan untuk dieksekusi oleh instruksi KONFIGURASI ULANG. Contoh berikut menunjukkan bagaimana Anda dapat mengaktifkan runtime bahasa umum menggunakan prosedur sistem sp_configure:
GUNAKAN SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE
Membuat, mengkompilasi, dan menyimpan prosedur menggunakan CLR memerlukan urutan langkah-langkah berikut, dalam urutan yang tercantum:
Buat prosedur tersimpan dalam C# atau Visual Basic, lalu kompilasi menggunakan kompiler yang sesuai.
Menggunakan instruksi BUAT PERAKITAN, buat executable yang sesuai.
Jalankan prosedur menggunakan pernyataan EXECUTE.
Gambar di bawah menunjukkan diagram grafis dari langkah-langkah yang diuraikan sebelumnya. Berikut ini adalah penjelasan lebih rinci dari proses ini.
Pertama, buat program yang diinginkan di lingkungan pengembangan seperti Visual Studio. Kompilasi program yang telah selesai ke kode objek menggunakan compiler C# atau Visual Basic. Kode ini disimpan dalam file pustaka tautan dinamis (.dll) yang berfungsi sebagai sumber untuk pernyataan CREATE ASSEMBLY, yang membuat kode yang dapat dieksekusi perantara. Selanjutnya, keluarkan pernyataan CREATE PROCEDURE untuk menyimpan kode yang dieksekusi sebagai objek database. Terakhir, jalankan prosedur menggunakan pernyataan EXECUTE yang sudah dikenal.
Contoh di bawah ini menunjukkan kode sumber prosedur tersimpan di C#:
Menggunakan System.Data.SqlClient; menggunakan Microsoft.SqlServer.Server; kelas parsial publik StoredProcedures ( public static int CountEmployees() ( baris int; koneksi SqlConnection = new SqlConnection("Koneksi Konteks=true"); koneksi.Buka(); SqlCommand cmd = koneksi.CreateCommand(); cmd.CommandText = "pilih count(*) sebagai "Jumlah karyawan" " + "dari Karyawan"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )
Prosedur ini mengimplementasikan kueri untuk menghitung jumlah baris dalam tabel Karyawan. Menggunakan arahan di awal program menentukan ruang nama yang diperlukan untuk eksekusinya. Penggunaan arahan ini memungkinkan Anda untuk menentukan nama kelas dalam kode sumber tanpa secara eksplisit menentukan ruang nama yang sesuai. Selanjutnya, kelas StoredProcedures didefinisikan, yang Atribut SqlProcedure, yang menginformasikan kompiler bahwa kelas ini adalah prosedur tersimpan. Di dalam kode kelas, metode CountEmployees() didefinisikan. Koneksi ke sistem database dibuat melalui instance kelas Koneksi Sql. Untuk membuka koneksi, metode Open() dari instance ini digunakan. TETAPI CreateCommand() metode memungkinkan Anda mengakses instance kelas SqlCommnd, yang dilewati perintah SQL yang diinginkan.
Dalam cuplikan kode berikut:
Cmd.CommandText = "pilih count(*) sebagai "Jumlah karyawan" " + "dari Karyawan";
menggunakan pernyataan SELECT untuk menghitung jumlah baris dalam tabel Karyawan dan menampilkan hasilnya. Teks perintah ditentukan dengan menyetel properti CommandText dari variabel cmd ke instance yang dikembalikan oleh metode CreateCommand(). Selanjutnya disebut ExecuteScalar() metode contoh SqlCommand. Metode ini mengembalikan nilai skalar yang dikonversi ke tipe data integer int dan ditetapkan ke variabel baris.
Anda sekarang dapat mengkompilasi kode ini menggunakan Visual Studio. Saya menambahkan kelas ini ke proyek dengan nama CLRStoredProcedures, jadi Visual Studio akan mengkompilasi rakitan dengan nama yang sama dengan ekstensi *.dll. Contoh di bawah ini menunjukkan langkah selanjutnya dalam membuat prosedur tersimpan: membuat kode untuk dijalankan. Sebelum mengeksekusi kode dalam contoh ini, Anda perlu mengetahui lokasi file .dll yang dikompilasi (biasanya terletak di folder Debug proyek).
GUNAKAN SampleDb; PERGI CREATE ASSEMBLY CLRStoredProcedures DARI "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" DENGAN PERMISSION_SET = AMAN
Pernyataan CREATE ASSEMBLY mengambil kode terkelola sebagai input dan membuat objek yang sesuai untuknya Anda dapat membuat prosedur tersimpan runtime bahasa umum (CLR), fungsi yang ditentukan pengguna, dan pemicu. Instruksi ini memiliki sintaks berikut:
CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] DARI (dll_file) konvensi sintaks
Parameter assembly_name menentukan nama rakitan. Klausa AUTHORIZATION opsional menentukan nama peran sebagai pemilik Majelis ini. Klausa FROM menentukan jalur di mana Majelis untuk memuat berada.
DENGAN klausa PERMISSION_SET adalah klausa yang sangat penting dari pernyataan CREATE ASSEMBLY dan harus selalu ditentukan. Ini mendefinisikan set hak akses yang diberikan ke kode perakitan. Kumpulan hak AMAN adalah yang paling membatasi. Kode perakitan yang memiliki hak ini tidak dapat mengakses sumber daya sistem eksternal seperti file. Kumpulan hak EXTERNAL_ACCESS memungkinkan kode perakitan untuk mengakses sumber daya sistem eksternal tertentu, sedangkan kumpulan hak UNSAFE menyediakan akses tak terbatas ke sumber daya, baik di dalam maupun di luar sistem basis data.
Untuk menyimpan informasi kode rakitan, pengguna harus dapat mengeluarkan pernyataan CREATE ASSEMBLY. Majelis dimiliki oleh pengguna (atau peran) yang mengeksekusi instruksi. Anda dapat mengubah pemilik rakitan dengan menggunakan klausa AUTHORIZATION dari pernyataan CREATE SCHEMA.
Mesin Database juga mendukung pernyataan ALTER ASSEMBLY dan DROP ASSEMBLY. ALTER ASSEMBLY Pernyataan digunakan untuk memperbarui Majelis ke versi terbaru. Instruksi ini juga menambah atau menghapus file yang terkait dengan perakitan yang sesuai. Pernyataan JATUH PERAKITAN menghapus perakitan yang ditentukan dan semua file terkait dari database saat ini.
Contoh berikut menunjukkan cara membuat prosedur tersimpan berdasarkan kode terkelola yang diterapkan sebelumnya:
GUNAKAN SampleDb; GO CREATE PROCEDURE CountKaryawan SEBAGAI NAMA EKSTERNAL CLRStoredProcedures.StoredProcedures.CountEmployeees
Pernyataan CREATE PROCEDURE dalam contoh berbeda dari pernyataan yang sama pada contoh sebelumnya karena mengandung: Parameter NAMA EKSTERNAL. Opsi ini menetapkan bahwa kode dihasilkan oleh CLR. Nama dalam kalimat ini terdiri dari tiga bagian:
assembly_name.class_name.method_name
assembly_name - menentukan nama majelis;
class_name - menentukan nama kelas umum;
method_name - bagian opsional, menentukan nama metode yang diatur di dalam kelas.
Eksekusi prosedur CountEmployees ditunjukkan pada contoh di bawah ini:
GUNAKAN SampleDb; DECLARE @count INT EXECUTE @count = CountEmployeees PRINT @count -- Mengembalikan 7
Pernyataan PRINT mengembalikan jumlah baris saat ini dalam tabel Karyawan.
Di Microsoft SQL Server untuk mengimplementasikan dan mengotomatisasi algoritma mereka sendiri ( perhitungan) Anda dapat menggunakan prosedur tersimpan, jadi hari ini kita akan berbicara tentang bagaimana prosedur tersebut dibuat, dimodifikasi, dan dihapus.
Tapi pertama-tama, sedikit teori agar Anda memahami apa itu prosedur tersimpan dan untuk apa prosedur itu di T-SQL.
Catatan! Untuk programmer pemula, saya merekomendasikan materi bermanfaat berikut tentang topik T-SQL:
- Untuk studi yang lebih rinci tentang bahasa T-SQL, saya juga merekomendasikan membaca buku - The Way of the T-SQL Programmer. Tutorial Transact-SQL.
Apa prosedur tersimpan di T-SQL?
Prosedur tersimpan- Ini adalah objek database di mana algoritma tertanam dalam bentuk satu set instruksi SQL. Dengan kata lain, kita dapat mengatakan bahwa prosedur tersimpan adalah program dalam database. Prosedur tersimpan digunakan untuk menyimpan kode yang dapat digunakan kembali di server, misalnya, Anda menulis algoritme, perhitungan berurutan, atau pernyataan SQL multi-langkah, dan agar tidak mengeksekusi semua instruksi yang disertakan dalam algoritme ini setiap kali, Anda dapat mengatur sebagai prosedur tersimpan. Dalam hal ini, saat Anda membuat prosedur SQL, server mengkompilasi kode, dan kemudian, setiap kali Anda menjalankan prosedur ini, server SQL tidak akan lagi mengompilasinya.
Untuk menjalankan prosedur tersimpan di SQL Server, perlu untuk menulis perintah EXECUTE sebelum namanya, juga memungkinkan untuk menulis perintah EXEC ini dengan singkatan. Panggil prosedur tersimpan dalam pernyataan SELECT, misalnya, karena suatu fungsi tidak akan berfungsi lagi, mis. prosedur dijalankan secara terpisah.
Dalam prosedur tersimpan, tidak seperti fungsi, sudah dimungkinkan untuk melakukan operasi modifikasi data seperti: UNSERT, UPDATE, DELETE. Juga, dalam prosedur, Anda dapat menggunakan hampir semua jenis pernyataan SQL, misalnya, CREATE TABLE untuk membuat tabel atau EXECUTE, mis. memanggil prosedur lain. Pengecualian adalah beberapa jenis instruksi seperti: membuat atau mengubah fungsi, tampilan, pemicu, membuat skema dan beberapa instruksi serupa lainnya, misalnya, juga tidak mungkin untuk mengganti konteks koneksi database (USE) dalam prosedur tersimpan.
Prosedur tersimpan mungkin memiliki parameter input dan parameter output, mungkin mengembalikan data tabular, mungkin tidak mengembalikan apa pun, tetapi hanya menjalankan instruksi yang terkandung di dalamnya.
Prosedur tersimpan sangat berguna, mereka membantu kami mengotomatiskan atau menyederhanakan banyak operasi, misalnya, Anda terus-menerus perlu membuat berbagai laporan analitik kompleks menggunakan tabel pivot, mis. operator PIVOT. Untuk menyederhanakan pembentukan kueri dengan operator ini ( seperti yang Anda tahu, sintaks PIVOT agak rumit), Anda dapat menulis prosedur yang secara dinamis akan menghasilkan laporan ringkasan untuk Anda, misalnya, dalam materi "PIVOT Dinamis dalam T-SQL", contoh penerapan fitur ini dalam bentuk prosedur tersimpan disajikan.
Contoh bekerja dengan prosedur tersimpan di Microsoft SQL Server
Data awal untuk contoh
Semua contoh di bawah ini akan dijalankan di Microsoft SQL Server 2016 Express. Untuk mendemonstrasikan bagaimana prosedur tersimpan bekerja dengan data nyata, kita memerlukan data ini, mari kita buat. Misalnya, mari kita buat tabel pengujian dan tambahkan beberapa catatan ke dalamnya, katakanlah itu akan menjadi tabel yang berisi daftar produk dengan harganya.
Pernyataan pembuatan tabel CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Tambahkan pernyataan data INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "Mouse", 100), (1, "Keyboard", 200), (2, "Phone", 400) GO -- SELECT * FROM kueri TestTable
Ada data, sekarang mari kita beralih ke pembuatan prosedur tersimpan.
Membuat prosedur tersimpan di T-SQL - pernyataan CREATE PROCEDURE
Prosedur tersimpan dibuat menggunakan pernyataan BUAT PROSEDUR, setelah instruksi ini Anda harus menulis nama prosedur Anda, kemudian, jika perlu, tentukan parameter input dan output dalam tanda kurung. Setelah itu, Anda menulis kata kunci AS dan membuka blok instruksi dengan kata kunci BEGIN, tutup blok ini dengan kata END. Di dalam blok ini, Anda menulis semua instruksi yang mengimplementasikan algoritme Anda atau semacam perhitungan berurutan, dengan kata lain, Anda memprogram dalam T-SQL.
Misalnya, mari kita tulis prosedur tersimpan yang akan menambahkan catatan baru, mis. item baru ke bagan pengujian kami. Untuk melakukan ini, kami akan menentukan tiga parameter input: @CategoryId - pengenal kategori produk, @ProductName - nama produk dan @Harga - harga produk, parameter ini akan menjadi opsional bagi kami, mis. itu tidak dapat diteruskan ke prosedur ( misalnya kita belum tahu harganya), untuk ini kami akan menetapkan nilai default dalam definisinya. Parameter ini ada di tubuh prosedur, mis. di blok BEGIN…END dapat digunakan dengan cara yang sama seperti variabel biasa ( seperti yang Anda tahu, variabel dilambangkan dengan tanda @). Jika Anda perlu menentukan parameter keluaran, maka setelah nama parameter, tentukan kata kunci OUTPUT ( atau disingkat OUT).
Di blok BEGIN…END, kami akan menulis instruksi untuk menambahkan data, dan juga di akhir prosedur, pernyataan SELECT sehingga prosedur tersimpan mengembalikan data tabular tentang produk dalam kategori yang ditentukan, dengan mempertimbangkan yang baru, baru saja menambahkan produk. Juga dalam prosedur tersimpan ini, saya menambahkan pemrosesan parameter yang masuk, yaitu, menghapus spasi tambahan di awal dan akhir string teks untuk menghindari situasi ketika beberapa spasi dimasukkan secara tidak sengaja.
Berikut adalah kode untuk prosedur ini Saya juga mengomentarinya).
Buat prosedur CREATE PROCEDURE TestProcedure (--Parameter masuk @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS MULAI --Instruksi yang mengimplementasikan algoritme Anda --Memproses parameter yang masuk --Menghapus spasi ekstra di awal dan di akhir string teks SET @ProductName = LTRIM(RTRIM(@ProductName)); --Tambahkan entri baru INSERT INTO TestTable(CategoryId, ProductName, Price) NILAI (@CategoryId, @ProductName, @Price) --Kembalikan data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Menjalankan Prosedur Tersimpan di T-SQL - Perintah EXECUTE
Anda dapat menjalankan prosedur tersimpan, seperti yang telah saya catat, menggunakan perintah EXECUTE atau EXEC. Parameter yang masuk diteruskan ke prosedur dengan hanya menghitungnya dan memberikan nilai yang sesuai setelah nama prosedur ( untuk parameter output, Anda juga harus menentukan perintah OUTPUT). Namun, nama parameter mungkin tidak ditentukan, tetapi dalam hal ini perlu mengikuti urutan penentuan nilai, mis. tentukan nilai dalam urutan di mana parameter input didefinisikan ( ini juga berlaku untuk parameter keluaran).
Parameter yang memiliki nilai default mungkin tidak ditentukan, inilah yang disebut parameter opsional.
Berikut adalah beberapa cara yang berbeda tetapi setara untuk menjalankan prosedur tersimpan, khususnya prosedur pengujian kami.
1. Panggil prosedur tanpa menentukan harga EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" ---2. Kami memanggil prosedur dengan harga yang ditentukan EXEC TestProcedure @CategoryId = 1, @ProductName = "Test product 2", @Price = 300 ---3. Kami memanggil prosedur tanpa menentukan nama parameter EXEC TestProcedure 1, "Tes item 3", 400
Mengubah Prosedur Tersimpan ke T-SQL - ALTER PROCEDURE Statement
Anda dapat membuat perubahan pada algoritme prosedur menggunakan instruksi MENGUBAH PROSEDUR. Dengan kata lain, untuk mengubah prosedur yang sudah ada, Anda hanya perlu menulis ALTER PROCEDURE alih-alih CREATE PROCEDURE, dan ubah yang lainnya seperlunya.
Katakanlah kita perlu membuat perubahan pada prosedur pengujian kita, misalnya parameter @Price, mis. harga, kami akan membuatnya wajib, untuk ini kami akan menghapus nilai default, dan juga membayangkan bahwa kami tidak perlu lagi mendapatkan kumpulan data yang dihasilkan, untuk ini kami hanya akan menghapus pernyataan SELECT dari prosedur tersimpan.
Ubah prosedur ALTER PROCEDURE TestProcedure (--Parameter masuk @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) SEBAGAI MULAI --Instruksi yang mengimplementasikan algoritme Anda --Memproses parameter yang masuk --Hapus spasi tambahan di awal dan akhir dari baris teks SET @ProductName = LTRIM(RTRIM(@ProductName)); --Tambahkan catatan baru INSERT INTO TestTable(CategoryId, ProductName, Price) NILAI (@CategoryId, @ProductName, @Price) END GO
Menghapus prosedur tersimpan di T-SQL - pernyataan DROP PROCEDURE
Jika perlu, Anda dapat menghapus prosedur tersimpan, ini dilakukan dengan menggunakan pernyataan PROSEDUR DROP.
Misalnya, mari kita hapus prosedur pengujian yang kita buat.
PROSEDUR DROP Prosedur Tes
Saat menghapus prosedur tersimpan, perlu diingat bahwa jika prosedur tersebut direferensikan oleh prosedur lain atau pernyataan SQL, setelah menghapusnya, prosedur tersebut akan gagal dengan kesalahan, karena prosedur yang mereka rujuk sudah tidak ada lagi.
Saya memiliki segalanya, saya harap materinya menarik dan bermanfaat bagi Anda, sampai jumpa!
