Bagikan melalui

Pemrograman Mesin Database prosedur tersimpan yang diperluas

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 diperpanjang

Proses kerja prosedur tersimpan yang diperpanjang adalah:

  1. Saat klien menjalankan prosedur tersimpan yang diperluas, permintaan ditransmisikan dalam format aliran data tabular (TDS) atau Protokol Akses Objek Sederhana (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 ke server melalui API Prosedur Tersimpan yang Diperpanjang.

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 API Prosedur Tersimpan yang Diperluas.

Dengan munculnya kueri terdistribusi dan integrasi CLR, kebutuhan akan aplikasi API Prosedur Tersimpan yang Diperpanjang sebagian besar telah diganti.

Jika Anda memiliki aplikasi gateway yang ada, Anda tidak dapat menggunakan opends60.dll aplikasi yang dikirimkan 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 diungkapkan atau tidak mungkin ditulis di Transact-SQL. Dalam rilis SQL Server sebelumnya, prosedur tersimpan yang diperpanjang (XP) menyediakan satu-satunya mekanisme yang tersedia bagi pengembang aplikasi database untuk menulis kode tersebut.

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

Untuk informasi selengkapnya, lihat Gambaran umum integrasi CLR.

Karakteristik eksekusi prosedur tersimpan yang diperpanjang

Eksekusi prosedur tersimpan yang diperpanjang 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 adalah yang 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 diperpanjang untuk memastikan bahwa prosedur tersebut tidak berisi kode berbahaya atau berbahaya.

Setelah DLL prosedur tersimpan yang diperpanjang dimuat, DLL tetap dimuat di ruang alamat server hingga 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 dengan menggunakan EXECUTE pernyataan:

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

Parameter-parameternya

@ pengambilan

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 berbagi ruang alamat yang sama, prosedur masalah dapat memengaruhi fungsi SQL Server. Meskipun pengecualian yang dilemparkan oleh DLL prosedur tersimpan yang diperpanjang 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 dipasang.

Mengirim kumpulan hasil ke server dengan API Prosedur Tersimpan yang Diperluas

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

  • Fungsi ini srv_sendmsg dapat 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, argumen srv_statussrv_pfield, atau 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 membuat DLL:

  • Jika titik masuk diperlukan, tulis fungsi DllMain .

    Fungsi ini bersifat opsional. Jika Anda tidak menyediakannya dalam kode sumber, kompiler menautkan versinya sendiri, yang tidak melakukan apa-apa selain mengembalikan TRUE. Jika Anda menyediakan DllMain fungsi, sistem operasi memanggil fungsi ini saat utas atau proses melekat 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 kompiler Microsoft (__declspec() dimulai dengan dua garis bawah).

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

File Description
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 Tautan Dinamis. Untuk informasi selengkapnya tentang membuat DLL, lihat dokumentasi lingkungan pengembangan.

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

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

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

Saat SQL Server dimulai dengan bendera -T260 pelacakan atau jika pengguna dengan hak istimewa administrator sistem menjalankan 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 diperpanjang 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 menampilkan nilai yang salah dari __GetXpVersion(), atau Anda mengkompilasi dengan versi srv.hlama .

Nota

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

Prosedur tersimpan yang diperpanjang yang berjalan lama harus dipanggil srv_got_attention secara berkala, sehingga prosedur dapat dihentikan sendiri jika koneksi terputus, atau batch dibatalkan.

Untuk men-debug DLL prosedur tersimpan yang diperluas, salin ke direktori SQL Server \Binn . Untuk menentukan yang dapat dieksekusi untuk sesi penelusuran kesalahan, masukkan jalur dan nama 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 diperpanjang disalin ke server, administrator sistem SQL Server harus mendaftarkan ke SQL Server setiap fungsi prosedur tersimpan yang diperluas di DLL. Ini dilakukan dengan menggunakan prosedur tersimpan sp_addextendedproc sistem.

Penting

Administrator sistem harus meninjau secara menyeluruh prosedur tersimpan yang diperluas 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 pertama menentukan sp_addextendedproc nama fungsi, dan parameter kedua menentukan nama DLL tempat fungsi tersebut berada. Anda harus menentukan jalur lengkap DLL.

Nota

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 dalam sp_addextendedproc harus persis sama, termasuk kasus, dengan nama fungsi dalam 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 di sp_addextendedproc tidak persis cocok dengan nama fungsi di DLL, nama baru terdaftar di SQL Server, tetapi namanya tidak dapat digunakan. Misalnya, meskipun xp_Hello terdaftar sebagai prosedur tersimpan yang diperluas 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 dalam sp_addextendedproc cocok persis dengan nama fungsi dalam DLL, dan kolase instans SQL Server tidak peka huruf besar/kecil, pengguna dapat memanggil prosedur tersimpan yang diperluas menggunakan kombinasi huruf kecil dan besar 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 persis sama 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.

Mengkueri prosedur tersimpan yang diperluas yang diinstal di SQL Server

Pengguna yang diautentikasi SQL Server dapat menampilkan prosedur tersimpan yang diperluas yang saat ini ditentukan dan nama DLL yang masing-masing dimiliki 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 menghapus setiap fungsi prosedur tersimpan yang diperluas dalam DLL prosedur tersimpan yang diperpanjang yang ditentukan pengguna, administrator sistem SQL Server harus menjalankan prosedur tersimpan sp_dropextendedproc 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 menjatuhkan prosedur tersimpan yang diperpanjang sistem. Sebagai gantinya, administrator sistem harus menolak EXECUTE izin pada prosedur tersimpan yang diperluas untuk peran publik .

Membongkar DLL prosedur tersimpan yang diperpanjang

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

DBCC xp_hello(FREE);

Konten terkait

  • Last updated on