Bagikan melalui

Prosedur tersimpan yang diperluas Mesin Database Pemrograman

  • Artikel

Berlaku untuk: SQL Server

Penting

Fitur ini akan dihapus dalam versi SQL Server yang akan datang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini. Gunakan integrasi CLR sebagai gantinya.

Cara kerja prosedur tersimpan yang diperluas

Proses di mana prosedur tersimpan yang diperluas bekerja adalah:

  1. Ketika klien menjalankan prosedur tersimpan yang diperluas, permintaan ditransmisikan dalam format aliran data tabular (TDS) atau Simple Object Access Protocol (SOAP) dari aplikasi klien ke SQL Server.

  2. SQL Server mencari DLL yang terkait dengan prosedur tersimpan yang diperluas, dan memuat DLL jika belum dimuat.

  3. SQL Server memanggil prosedur tersimpan yang diperluas yang diminta (diimplementasikan sebagai fungsi di dalam DLL).

  4. Prosedur tersimpan yang diperluas meneruskan kumpulan hasil dan mengembalikan parameter kembali ke server melalui EXTENDED Stored Procedure API.

Di masa lalu, Open Data Services digunakan untuk menulis aplikasi server, seperti gateway ke lingkungan database non-SQL Server. SQL Server tidak mendukung bagian usang dari OPEN Data Services API. Satu-satunya bagian dari API Open Data Services asli yang masih didukung oleh SQL Server adalah fungsi prosedur tersimpan yang diperluas, sehingga API diganti namanya menjadi EXTENDED Stored Procedure API.

Dengan munculnya kueri terdistribusi dan integrasi CLR, kebutuhan akan aplikasi Extended Stored Procedure API sebagian besar telah diganti.

Jika Anda memiliki aplikasi gateway yang ada, Anda tidak dapat menggunakan opends60.dll yang dikirim dengan SQL Server untuk menjalankan aplikasi. Aplikasi gateway tidak lagi didukung.

Prosedur tersimpan yang diperluas vs. Integrasi CLR

Integrasi CLR memberikan alternatif yang lebih kuat untuk menulis logika sisi server yang sulit diekspresikan atau tidak mungkin untuk ditulis dalam Transact-SQL. Dalam rilis SQL Server sebelumnya, prosedur tersimpan yang diperluas (XP) menyediakan satu-satunya mekanisme yang tersedia bagi pengembang aplikasi database untuk menulis kode tersebut.

Dengan integrasi CLR, logika yang digunakan untuk ditulis dalam bentuk prosedur tersimpan sering kali lebih baik dinyatakan sebagai fungsi bernilai tabel, yang memungkinkan hasil yang dibangun oleh fungsi untuk dikueri dalam SELECT pernyataan dengan menyematkannya dalam FROM klausul.

Untuk informasi selengkapnya, lihat Gambaran umum integrasi CLR.

Karakteristik eksekusi prosedur tersimpan yang diperluas

Eksekusi prosedur tersimpan yang diperluas memiliki karakteristik berikut:

  • Fungsi prosedur tersimpan yang diperluas dijalankan di bawah konteks keamanan SQL Server.

  • Fungsi prosedur tersimpan yang diperluas berjalan di ruang proses SQL Server.

  • Utas yang terkait dengan eksekusi prosedur tersimpan yang diperluas sama dengan yang digunakan untuk koneksi klien.

Penting

Sebelum menambahkan prosedur tersimpan yang diperluas ke server dan memberikan izin eksekusi kepada pengguna lain, administrator sistem harus meninjau secara menyeluruh setiap prosedur tersimpan yang diperluas untuk memastikan bahwa prosedur tersebut tidak berisi kode berbahaya atau berbahaya.

Setelah DLL prosedur tersimpan yang diperluas dimuat, DLL tetap dimuat di ruang alamat server sampai SQL Server dihentikan atau administrator secara eksplisit membongkar DLL dengan menggunakan DBCC <DLL_name> (FREE).

Prosedur tersimpan yang diperluas dapat dijalankan dari Transact-SQL sebagai prosedur tersimpan EXECUTE dengan menggunakan pernyataan :

EXECUTE @retval = xp_extendedProcName @param1, @param2 OUTPUT;

Parameter

@ retval

Nilai yang dikembalikan.

@ param1

Parameter input.

@ param2

Parameter input/output.

Perhatian

Prosedur tersimpan yang diperluas menawarkan peningkatan performa dan memperluas fungsionalitas SQL Server. Namun, karena DLL prosedur tersimpan yang diperluas dan SQL Server memiliki ruang alamat yang sama, prosedur masalah dapat berdampak buruk pada fungsi SQL Server. Meskipun pengecualian yang dilemparkan oleh DLL prosedur tersimpan yang diperluas ditangani oleh SQL Server, dimungkinkan untuk merusak area data SQL Server. Sebagai tindakan pencegahan keamanan, hanya administrator sistem SQL Server yang dapat menambahkan prosedur tersimpan yang diperluas ke SQL Server. Prosedur ini harus diuji secara menyeluruh sebelum diinstal.

Mengirim tataan hasil ke server dengan EXTENDED Stored Procedure API

Saat mengirim hasil yang diatur ke SQL Server, prosedur tersimpan yang diperluas harus memanggil API yang sesuai sebagai berikut:

  • Fungsi srv_sendmsg ini mungkin dipanggil dalam urutan apa pun sebelum atau sesudah semua baris (jika ada) dengan srv_sendrow. Semua pesan harus dikirim ke klien sebelum status penyelesaian dikirim dengan srv_senddone.

  • Fungsi ini srv_sendrow dipanggil sekali untuk setiap baris yang dikirim ke klien. Semua baris harus dikirim ke klien sebelum pesan, nilai status, atau status penyelesaian dikirim dengan srv_sendmsg, srv_status argumen , srv_pfieldatau srv_senddone.

  • Mengirim baris yang tidak memiliki semua kolomnya yang ditentukan dengan srv_describe menyebabkan aplikasi memunculkan pesan kesalahan informasi dan kembali FAIL ke klien. Dalam hal ini, baris tidak dikirim.

Membuat prosedur tersimpan yang diperluas

Prosedur tersimpan yang diperluas adalah fungsi C/C++ dengan prototipe:

SRVRETCODE xp_extendedProcName ( SRVPROC *);

Menggunakan awalan xp_ bersifat opsional. Nama prosedur tersimpan yang diperluas peka huruf besar/kecil saat direferensikan dalam pernyataan Transact-SQL, terlepas dari halaman kode/urutan pengurutan yang diinstal di server. Saat Anda membangun DLL:

  • Jika titik masuk diperlukan, tulis DllMain fungsi.

    Fungsi ini bersifat opsional. Jika Anda tidak menyediakannya dalam kode sumber, pengkompilasi menautkan versinya sendiri, yang tidak melakukan apa pun selain mengembalikan TRUE. Jika Anda menyediakan DllMain fungsi, sistem operasi memanggil fungsi ini ketika utas atau proses dilampirkan atau terlepas dari DLL.

  • Semua fungsi yang dipanggil dari luar DLL (semua prosedur tersimpan yang diperluas Efunctions) harus diekspor.

    Anda dapat mengekspor fungsi dengan mencantumkan namanya di EXPORTS bagian .def file, atau Anda dapat mengawali nama fungsi dalam kode sumber dengan __declspec(dllexport), ekstensi pengkompilasi Microsoft (__declspec() dimulai dengan dua garis bawah).

File-file ini diperlukan untuk membuat DLL prosedur tersimpan yang diperluas.

File Deskripsi
srv.h File header API Prosedur Tersimpan yang Diperluas
opends60.lib Impor pustaka untuk opends60.dll

Untuk membuat DLL prosedur tersimpan yang diperluas, buat proyek jenis Pustaka Dynamic Link. Untuk informasi selengkapnya tentang membuat DLL, lihat dokumentasi lingkungan pengembangan.

Semua DLL prosedur tersimpan yang diperluas harus menerapkan dan mengekspor fungsi berikut:

__declspec(dllexport) ULONG __GetXpVersion()
{
   return ODS_VERSION;
}

__declspec(dllexport) adalah ekstensi kompilator khusus Microsoft. Jika pengkompilasi Anda tidak mendukung arahan ini, Anda harus mengekspor fungsi ini dalam file Anda DEF di bawah bagian .EXPORTS

Ketika SQL Server dimulai dengan bendera -T260 pelacakan atau jika pengguna dengan hak istimewa administrator sistem berjalan DBCC TRACEON (260), dan jika DLL prosedur tersimpan yang diperluas tidak mendukung __GetXpVersion(), pesan peringatan berikut dicetak ke log kesalahan (__GetXpVersion() dimulai dengan dua garis bawah).

Error 8131: Extended stored procedure DLL '%' does not export __GetXpVersion().

Jika DLL prosedur tersimpan yang diperluas mengekspor __GetXpVersion(), tetapi versi yang dikembalikan oleh fungsi kurang dari versi yang diperlukan oleh server, pesan peringatan yang menyatakan versi yang dikembalikan oleh fungsi dan versi yang diharapkan oleh server dicetak ke log kesalahan. Jika Anda mendapatkan pesan ini, Anda mengembalikan nilai yang salah dari __GetXpVersion(), atau Anda mengkompilasi dengan versi lama .srv.h

Catatan

SetErrorMode, fungsi Win32, tidak boleh dipanggil dalam prosedur tersimpan yang diperpanjang.

Prosedur tersimpan yang diperpanjang yang berjalan lama harus memanggil srv_got_attention secara berkala, sehingga prosedur dapat mengakhiri dirinya sendiri jika koneksi mati, atau batch dibatalkan.

Untuk men-debug DLL prosedur tersimpan yang diperluas, salin ke direktori SQL Server \Binn . Untuk menentukan executable untuk sesi debugging, masukkan jalur dan nama file file file yang dapat dieksekusi SQL Server (misalnya, C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\sqlservr.exe). Untuk informasi tentang sqlservr argumen, lihat Aplikasi sqlservr.

Menambahkan prosedur tersimpan yang diperluas ke SQL Server

DLL yang berisi fungsi prosedur tersimpan yang diperluas bertindak sebagai ekstensi ke SQL Server. Untuk menginstal DLL, salin file ke direktori, seperti yang berisi file DLL SQL Server standar (C:\Program Files\Microsoft SQL Server\MSSQL16.0.<x>\MSSQL\Binn secara default).

Setelah DLL prosedur tersimpan yang diperluas disalin ke server, administrator sistem SQL Server harus mendaftar ke SQL Server setiap fungsi prosedur tersimpan yang diperluas di DLL. Ini dilakukan menggunakan prosedur tersimpan sp_addextendedproc sistem.

Penting

Administrator sistem harus meninjau prosedur tersimpan yang diperluas secara menyeluruh untuk memastikan bahwa prosedur tersebut tidak berisi kode berbahaya atau berbahaya sebelum menambahkannya ke server dan memberikan izin eksekusi kepada pengguna lain. Validasi semua input pengguna. Jangan menggabungkan input pengguna sebelum memvalidasinya. Jangan pernah menjalankan perintah yang dibangun dari input pengguna yang tidak valid.

Parameter sp_addextendedproc pertama menentukan nama fungsi, dan parameter kedua menentukan nama DLL tempat fungsi tersebut berada. Anda harus menentukan jalur lengkap DLL.

Catatan

DLL yang ada yang tidak terdaftar dengan jalur lengkap tidak berfungsi setelah memutakhirkan ke SQL Server 2005 (9.x) atau versi yang lebih baru. Untuk memperbaiki masalah, gunakan sp_dropextendedproc untuk membatalkan pendaftaran DLL, lalu daftarkan ulang dengan sp_addextendedproc, menentukan jalur lengkap.

Nama fungsi yang ditentukan di sp_addextendedproc harus sama persis, termasuk kasus, sebagai nama fungsi di DLL. Misalnya, perintah ini mendaftarkan fungsi xp_hello, yang terletak di dll bernama xp_hello.dll, sebagai prosedur tersimpan yang diperluas SQL Server:

sp_addextendedproc 'xp_hello', 'c:\Program Files\Microsoft SQL Server\MSSQL13.0.MSSQLSERVER\MSSQL\Binn\xp_hello.dll';

Jika nama fungsi yang ditentukan sp_addextendedproc tidak sama persis dengan nama fungsi di DLL, nama baru terdaftar di SQL Server, tetapi namanya tidak dapat digunakan. Misalnya, meskipun xp_Hello terdaftar sebagai prosedur tersimpan diperpanjang SQL Server yang terletak di xp_hello.dll, SQL Server tidak dapat menemukan fungsi di DLL jika Anda menggunakan xp_Hello untuk memanggil fungsi nanti.

-- Register the function (xp_hello) with an initial upper case
sp_addextendedproc 'xp_Hello', 'c:\xp_hello.dll';

-- Use the newly registered name to call the function
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;

Berikut pesan kesalahannya:

Server: Msg 17750, Level 16, State 1, Procedure xp_Hello, Line 1
Could not load the DLL xp_hello.dll, or one of the DLLs it references. Reason: 127(The specified procedure could not be found.).

Jika nama fungsi yang ditentukan sama sp_addextendedproc persis dengan nama fungsi di DLL, dan kolase instans SQL Server tidak peka huruf besar/kecil, pengguna dapat memanggil prosedur tersimpan yang diperluas menggunakan kombinasi huruf kecil dan atas nama.

-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';

-- The following example succeeds in calling xp_hello
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;

DECLARE @txt VARCHAR(33);
EXEC xp_HelLO @txt OUTPUT;

DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;

Ketika kolase instans SQL Server peka huruf besar/kecil, SQL Server tidak dapat memanggil prosedur tersimpan yang diperluas jika prosedur dipanggil dengan kasus yang berbeda. Ini benar bahkan jika terdaftar dengan nama dan kolase yang sama persis dengan fungsi di DLL.

-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';

-- The following example results in an error
DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;

Berikut pesan kesalahannya:

Server: Msg 2812, Level 16, State 62, Line 1

Anda tidak perlu menghentikan dan memulai ulang SQL Server.

Kueri prosedur tersimpan yang diperluas diinstal di SQL Server

Pengguna terautentikasi SQL Server dapat menampilkan prosedur tersimpan yang diperluas yang saat ini ditentukan dan nama DLL tempat masing-masing berada dengan menjalankan sp_helpextendedproc prosedur sistem. Misalnya, contoh berikut mengembalikan DLL yang xp_hello dimiliki:

sp_helpextendedproc 'xp_hello';

Jika sp_helpextendedproc dijalankan tanpa menentukan prosedur tersimpan yang diperluas, semua prosedur tersimpan yang diperluas dan DLL-nya ditampilkan.

Menghapus prosedur tersimpan yang diperluas dari SQL Server

Untuk menghilangkan setiap fungsi prosedur tersimpan yang diperluas dalam DLL prosedur tersimpan yang ditentukan pengguna, administrator sistem SQL Server harus menjalankan sp_dropextendedproc prosedur tersimpan sistem, menentukan nama fungsi dan nama DLL tempat fungsi tersebut berada. Misalnya, perintah ini menghapus fungsi xp_hello, yang terletak di DLL bernama xp_hello.dll, dari SQL Server:

sp_dropextendedproc 'xp_hello';

sp_dropextendedproc tidak menghilangkan prosedur tersimpan yang diperluas sistem. Sebagai gantinya, administrator sistem harus menolak EXECUTE izin pada prosedur tersimpan yang diperluas ke peran publik .

Membongkar DLL prosedur tersimpan yang diperluas

SQL Server memuat DLL prosedur tersimpan yang diperluas segera setelah panggilan dilakukan ke salah satu fungsi DLL. DLL tetap dimuat hingga server dimatikan atau hingga administrator sistem menggunakan DBCC pernyataan untuk membongkarnya. Misalnya, perintah ini membongkar xp_hello.dll, yang memungkinkan administrator sistem menyalin versi file ini yang lebih baru ke direktori tanpa mematikan server:

DBCC xp_hello(FREE);

Konten terkait