I've telah belajar fungsi-Fungsi dan Prosedur yang Tersimpan cukup lama tapi aku don't tahu mengapa dan kapan saya harus menggunakan fungsi atau prosedur yang tersimpan. Mereka terlihat sama bagi saya, mungkin karena saya agak newbie tentang itu.
Dapatkah seseorang memberitahu saya mengapa?
Fungsi dihitung nilai-nilai yang permanen dan tidak dapat melakukan perubahan lingkungan untuk SQL Server (yaitu tidak ada INSERT atau UPDATE laporan diperbolehkan).
Fungsi dapat digunakan inline dalam pernyataan SQL jika ia mengembalikan nilai skalar, atau dapat bergabung atas jika itu mengembalikan hasil yang ditetapkan.
Sebuah titik yang patut dicatat dari komentar, yang merangkum jawaban. Terima kasih untuk @Sean K Anderson:
Fungsi tindak komputer-ilmiah definisi dalam bahwa mereka HARUS mengembalikan nilai dan tidak dapat mengubah data yang mereka terima sebagai parameter (argumen). Fungsi-fungsi yang tidak diperbolehkan untuk mengubah apa pun, harus memiliki setidaknya satu parameter, dan mereka harus mengembalikan nilai. Disimpan procs tidak harus memiliki parameter, dapat mengubah objek database, dan tidak harus mengembalikan nilai.
Perbedaan antara SP dan UDF tercantum di bawah ini:
+---------------------------------+----------------------------------------+
| Stored Procedure (SP) | Function (UDF - User Defined |
| | Function) |
+---------------------------------+----------------------------------------+
| SP can return zero , single or | Function must return a single value |
| multiple values. | (which may be a scalar or a table). |
+---------------------------------+----------------------------------------+
| We can use transaction in SP. | We can't use transaction in UDF. |
+---------------------------------+----------------------------------------+
| SP can have input/output | Only input parameter. |
| parameter. | |
+---------------------------------+----------------------------------------+
| We can call function from SP. | We can't call SP from function. |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/ | We can use UDF in SELECT/ WHERE/ |
| WHERE/ HAVING statement. | HAVING statement. |
+---------------------------------+----------------------------------------+
| We can use exception handling | We can't use Try-Catch block in UDF. |
| using Try-Catch block in SP. | |
+---------------------------------+----------------------------------------+
Fungsi dan prosedur yang tersimpan berfungsi memisahkan tujuan. Meskipun itu's bukan yang terbaik analogi, fungsi dapat dilihat secara harfiah sebagai fungsi lainnya anda'd gunakan dalam bahasa pemrograman apapun, tapi disimpan procs lebih seperti program individu atau batch script.
Fungsi biasanya memiliki output dan input opsional. Output yang kemudian dapat digunakan sebagai input untuk fungsi lain (SQL Server built-in seperti DATEDIFF, LEN, dll) atau sebagai predikat untuk Query SQL - misalnya, PILIH a, b, dbo.MyFunction(c) DARI tabel
atau PILIH a, b, c DARI tabel di MANA a = dbo.MyFunc(c)
.
Disimpan procs digunakan untuk mengikat query SQL bersama-sama dalam suatu transaksi, dan antarmuka dengan dunia luar. Kerangka kerja seperti ADO.NET, dll. bisa't memanggil fungsi secara langsung, tetapi mereka dapat memanggil stored proc ini diatas.
Fungsi-fungsi yang tidak memiliki bahaya tersembunyi meskipun mereka dapat disalahgunakan dan menyebabkan agak menjijikkan masalah kinerja: pertimbangkan query ini:
SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)
Di mana MyFunction dinyatakan sebagai:
CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
DECLARE @retval INTEGER
SELECT localValue
FROM dbo.localToNationalMapTable
WHERE nationalValue = @someValue
RETURN @retval
END
Apa yang terjadi di sini adalah bahwa fungsi MyFunction disebut untuk setiap baris dalam tabel MyTable. Jika MyTable memiliki 1000 baris, maka yang's 1000 ad-hoc query terhadap database. Demikian pula, jika fungsi ini dipanggil saat yang ditentukan dalam kolom spec, maka fungsi akan dipanggil untuk setiap baris yang dikembalikan oleh PILIH.
Jadi anda perlu berhati-hati menulis fungsi. Jika anda MEMILIH dari meja di sebuah fungsi, anda perlu bertanya pada diri sendiri apakah itu dapat lebih baik dilakukan dengan BERGABUNG dalam induk disimpan proc atau beberapa lainnya SQL membangun (seperti KASUS ini ... KETIKA ... ELSE ... END).
Perbedaan antara disimpan prosedur dan user-defined functions:
RAISEERROR
ATAU @@ERROR
tidak diperbolehkan di UDFs.GETDATE()
tidak dapat digunakan dalam UDFs.Menulis fungsi yang ditetapkan pengguna ketika anda ingin menghitung dan mengembalikan nilai untuk digunakan di lain pernyataan SQL; menulis prosedur yang disimpan ketika anda inginkan, bukan untuk kelompok a mungkin-kompleks set pernyataan SQL. Ini adalah dua cukup kasus penggunaan yang berbeda, setelah semua!
STORE PROCEDURE FUNCTION (USER DEFINED FUNCTION)
* Procedure can return 0, single or | * Function can return only single value
multiple values. |
|
* Procedure can have input, output | * Function can have only input
parameters. | parameters.
|
* Procedure cannot be called from | * Functions can be called from
function. | procedure.
|
* Procedure allows select as well as | * Function allows only select statement
DML statement in it. | in it.
|
* Exception can be handled by | * Try-catch block cannot be used in a
try-catch block in a procedure. | function.
|
* We can go for transaction management| * We can't go for transaction
in procedure. | management in function.
|
* Procedure cannot be utilized in a | * Function can be embedded in a select
select statement | statement.
|
* Procedure can affect the state | * Function can not affect the state
of database means it can perform | of database means it can not
CRUD operation on database. | perform CRUD operation on
| database.
|
* Procedure can use temporary tables. | * Function can not use
| temporary tables.
|
* Procedure can alter the server | * Function can not alter the
environment parameters. | environment parameters.
|
* Procedure can use when we want | * Function can use when we want
instead is to group a possibly- | to compute and return a value
complex set of SQL statements. | for use in other SQL
| statements.
Perbedaan Mendasar
Fungsi harus mengembalikan suatu nilai tetapi dalam Prosedur yang Tersimpan itu adalah opsional( Prosedur dapat kembali nol atau n nilai-nilai).
Fungsi hanya dapat memiliki parameter input untuk itu sedangkan Prosedur dapat memiliki input/output parameter .
Fungsi mengambil satu masukan parameter ini adalah wajib, tetapi Prosedur yang Tersimpan dapat mengambil o untuk n masukan parameter..
Fungsi dapat dipanggil dari Prosedur, sedangkan Prosedur tidak dapat dipanggil dari Fungsi.
Muka Perbedaan
Prosedur ini memungkinkan PILIH serta DML(INSERT/UPDATE/DELETE) pernyataan di dalamnya sedangkan Fungsi memungkinkan hanya MEMILIH pernyataan di dalamnya.
Prosedur tidak bisa dimanfaatkan dalam sebuah pernyataan SELECT sedangkan Fungsi dapat tertanam dalam sebuah pernyataan SELECT.
Prosedur yang tersimpan tidak dapat digunakan dalam pernyataan SQL di mana saja di MANA/MEMILIKI/PILIH bagian sedangkan Fungsi dapat.
Fungsi yang mengembalikan tabel dapat diperlakukan sebagai satu rowset. Hal ini dapat digunakan dalam Bergabung dengan tabel lain.
Fungsi Inline dapat meskipun sebagai pandangan yang mengambil parameter dan dapat digunakan dalam Bergabung dan lain Rowset operasi.
Pengecualian dapat ditangani dengan try-catch blok dalam Prosedur sedangkan try-catch blok tidak dapat digunakan dalam sebuah Fungsi.
Kita bisa pergi untuk Manajemen Transaksi dalam Prosedur sedangkan kita bisa't pergi dalam Fungsi.
User Defined Function adalah alat penting yang tersedia untuk sql server programmer. Anda dapat menggunakannya inline dalam sebuah pernyataan SQL seperti begitu
SELECT a, lookupValue(b), c FROM customers
di mana lookupValue
akan UDF. Fungsi semacam ini adalah tidak mungkin bila menggunakan prosedur yang disimpan. Pada saat yang sama anda tidak dapat melakukan hal-hal tertentu di dalam UDF. Hal dasar yang perlu diingat di sini adalah bahwa UDF's:
stored procedure dapat melakukan hal-hal tersebut.
Bagi saya inline penggunaan UDF adalah yang paling penting penggunaan UDF.
Disimpan Prosedur digunakan sebagai script. Mereka menjalankan serangkaian perintah untuk anda dan anda dapat menjadwalkan mereka untuk berjalan pada waktu-waktu tertentu.
Fungsi yang digunakan sebagai metode. Anda lulus sesuatu dan mengembalikan hasilnya. Harus kecil dan cepat - apa itu on the fly.
Stored procedure:
EXEC
atau MENGEKSEKUSI
pernyataan.KELUAR
parameter.Fungsi:
PILIH dbo.functionname('Parameter1')
atau
PILIH Nama, dbo.Functionname('Parameter1') FROM sysObjects
BERGABUNG
klausul dalam fungsi anda. Jika anda
fungsi memiliki BERGABUNG
klausul dan yang anda sebut itu dari yang lain pilih
pernyataan yang hasilnya banyak, bahwa pemanggilan fungsi ini akan BERGABUNG
tabel tersebut bersama-sama untuk setiap baris yang dikembalikan dalam hasil set. Jadi
meskipun mereka dapat membantu dalam menyederhanakan beberapa logika, mereka juga dapat menjadi
hambatan kinerja jika mereka're tidak digunakan dengan benar.KELUAR
parameter.Fungsi SQL Server, seperti kursor, dimaksudkan untuk digunakan sebagai senjata terakhir! Mereka memiliki masalah kinerja dan oleh karena itu menggunakan table-valued function harus dihindari sebisa mungkin. Berbicara tentang kinerja adalah berbicara tentang sebuah tabel dengan lebih dari 1.000.000 catatan yang di-host pada server kelas menengah hardware; jika tidak, anda don't perlu khawatir tentang performa yang disebabkan oleh fungsi.
untuk referensi lebih lanjut lihat: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html
Untuk memutuskan kapan harus menggunakan apa poin-poin berikut ini mungkin bisa membantu-
Disimpan prosedur dapat't kembali tabel variabel dimana fungsi yang dapat melakukan itu.
Anda dapat menggunakan prosedur yang tersimpan untuk mengubah server parameter lingkungan di mana dengan menggunakan fungsi-fungsi yang dapat anda't.
cheers
Mulai dengan fungsi yang mengembalikan nilai tunggal. Hal yang baik adalah anda dapat menempatkan sering digunakan kode menjadi satu fungsi dan mengembalikan mereka sebagai kolom dalam menetapkan hasil.
Kemudian, anda dapat menggunakan fungsi untuk parameterized daftar kota-kota. dbo.GetCitiesIn("NY") Yang mengembalikan tabel yang dapat digunakan sebagai bergabung.
It's cara mengatur kode. Mengetahui ketika ada sesuatu yang dapat digunakan kembali dan ketika itu adalah buang-buang waktu adalah sesuatu yang hanya diperoleh melalui trial and error dan pengalaman.
Juga, fungsi ini adalah ide yang baik dalam SQL Server. Mereka lebih cepat dan bisa sangat kuat. Inline dan langsung memilih. Hati-hati untuk tidak berlebihan.
Berikut ini's alasan praktis untuk memilih fungsi dari prosedur yang tersimpan. Jika anda memiliki disimpan prosedur yang membutuhkan hasil lain prosedur yang tersimpan, anda harus menggunakan insert-exec pernyataan. Ini berarti bahwa anda harus membuat tabel temp dan menggunakan exec
pernyataan untuk memasukkan hasil dari prosedur yang tersimpan ke dalam tabel temp. It's berantakan. Satu masalah dengan ini adalah bahwa insert-eksekutif tidak dapat bersarang.
Jika anda're terjebak dengan prosedur yang tersimpan panggilan yang lain disimpan prosedur, anda mungkin mengalami hal ini. Jika bersarang prosedur tersimpan yang hanya mengembalikan dataset, dapat diganti dengan table-valued function dan anda'll tidak lagi mendapatkan kesalahan ini.
(ini adalah satu lagi alasan kita harus menjaga logika bisnis dari database)
User Defined Function.
Stored Procedure
Fungsi dapat digunakan dalam memilih pernyataan mana karena prosedur tidak.
Disimpan prosedur mengambil kedua parameter input dan output tetapi Fungsi hanya membutuhkan input parameter.
Fungsi-fungsi yang tidak mengembalikan nilai-nilai dari jenis teks, ntext, gambar & cap waktu dimana prosedur dapat.
Fungsi dapat digunakan sebagai pengguna didefinisikan tipe data dalam membuat tabel, tapi prosedur tidak.
***Misalnya:-buat tabel <namatabel>(nama varchar(10),gaji getsal(nama))
Di sini getsal adalah user defined function yang mengembalikan gaji tipe, ketika tabel dibuat tidak ada penyimpanan yang dialokasikan untuk gaji jenis, dan getsal fungsi ini juga tidak dieksekusi, Tapi ketika kita mengambil nilai-nilai dari tabel ini, getsal fungsi bisa dijalankan dan kembali Jenis dikembalikan sebagai hasil yang ditetapkan.
Saya menyadari ini adalah pertanyaan kuno, tapi aku don't melihat satu aspek penting yang disebutkan dalam jawaban: inlining ke query rencana.
Fungsi dapat...
MEMBUAT FUNGSI ... KEMBALI scalar_type SEBAGAI BEGIN ... END
MEMBUAT FUNGSI ... KEMBALI @r TABEL(...) SEBAGAI BEGIN ... END
MEMBUAT FUNGSI ... KEMBALI TABEL SEBAGAI KEMBALI PILIH ...
Jenis ketiga (inline table-valued) diperlakukan oleh query optimizer pada dasarnya sebagai (parametrized) pandangan, yang berarti bahwa referensi fungsi dari query yang mirip dengan copy-paste fungsi's SQL tubuh (tanpa benar-benar copy-paste), yang mengarah ke manfaat sebagai berikut:
Hal di atas dapat menyebabkan berpotensi signifikan kinerja tabungan, terutama ketika menggabungkan beberapa tingkat fungsi.
CATATAN: Terlihat seperti SQL Server 2019 akan memperkenalkan beberapa bentuk skalar fungsi inlining juga.
Di SQL Server, fungsi dan prosedur yang tersimpan adalah dua jenis yang berbeda dari badan.
Fungsi: Dalam SQL Server database, fungsi-fungsi yang digunakan untuk melakukan beberapa tindakan dan aksi kembali hasil segera. Fungsi dua jenis:
Sistem didefinisikan
Pengguna didefinisikan
Disimpan Prosedur: Di SQL Server disimpan prosedur yang disimpan di server dan dapat kembali nol, satu dan beberapa nilai-nilai. Disimpan Prosedur dua jenis: