sql - with - stored procedure syntax




函數與SQL Server中的存儲過程 (11)

我一直在學習函數和存儲過程很長一段時間,但我不知道為什麼以及何時應該使用函數或存儲過程。 他們看起來和我一樣,也許是因為我有點新手。

有人能告訴我為什麼?


SP和UDF的區別如下:

+---------------------------------+----------------------------------------+
| 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.    |                                        |
+---------------------------------+----------------------------------------+

基本差異

函數必須返回一個值,但是在存儲過程中它是可選的(過程可以返回零或n值)。

函數只能有輸入參數,而程序可以有輸入/輸出參數。

函數採用一個輸入參數,但它是強制性的,但存儲過程可能需要o至n個輸入參數。

函數可以從程序中調用,而程序不能從函數調用。

先進的差異

過程允許SELECT以及DML(INSERT / UPDATE / DELETE)語句,而Function只允許SELECT語句。

過程不能在SELECT語句中使用,而函數可以嵌入到SELECT語句中。

存儲過程不能用於WHERE / HAVING / SELECT部分​​中任何位置的SQL語句,而Function可以是。

返回表的函數可以被視為另一個行集。 這可以在與其他表的JOIN中使用。

內聯函數可以作為帶參數的視圖,並可用於JOIN和其他行集操作。

異常可以通過Procedure中的try-catch塊來處理,而try-catch塊不能在函數中使用。

我們可以在程序中進行事務管理,但我們無法進入函數。

source


像游標這樣的SQL Server函數是用來作為最後的武器的! 它們確實存在性能問題,因此應該盡可能地避免使用表值函數。 談論性能是指在中級硬件服務器上託管超過1,000,000條記錄的表格; 否則你不需要擔心功能造成的性能下降。

  1. 切勿使用函數將結果集返回給外部代碼(如ADO.Net)
  2. 盡可能使用views / stored procs組合。 您可以使用DTA(數據庫優化顧問)為您提供的建議(如索引視圖和統計信息) - 有時可以從未來的增長性能問題中恢復過來。

進一步參考見: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html : http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html


函數和存儲過程用於不同的目的。 雖然這不是最好的比喻,但函數可以從字面上看作任何其他編程語言中使用的函數,但存儲過程更像個別程序或批處理腳本。

函數通常具有輸出和可選的輸入。 然後,輸出可以用作另一個函數(內置的SQL Server,如DATEDIFF,LEN等)的輸入,或者作為S​​QL查詢的謂詞 - 例如SELECT a, b, dbo.MyFunction(c) FROM tableSELECT a, b, c FROM table WHERE a = dbo.MyFunc(c)

存儲過程用於在事務中將SQL查詢綁定在一起,並與外界交互。 ADO.NET等框架不能直接調用函數,但可以直接調用存儲過程。

函數確實有一個隱藏的危險:它們可能會被濫用並導致相當惡劣的性能問題:請考慮以下查詢:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

MyFunction被聲明為:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

這裡發生的是MyFunction中的每一行都調用MyFunction函數。 如果MyTable具有1000行,那麼對數據庫的另外1000個即席查詢。 同樣,如果函數在column spec中被指定時被調用,那麼將為SELECT返回的每一行調用該函數。

所以你需要小心編寫函數。 如果你從一個函數的表中做SELECT,你需要問自己,在父存儲過程還是其他一些SQL構造(例如CASE ... WHEN ... ELSE ...結束)。


在SQL Server中,函數和存儲過程是兩種不同類型的實體。

功能:在SQL Server數據庫中,函數用於執行一些操作,並且操作立即返回結果。 函數有兩種類型:

  1. 系統已定義

  2. 用戶自定義

存儲過程:在SQL Server中,存儲過程存儲在服務器中,可以返回零值,單值和多值。 存儲過程有兩種類型:

  1. 系統存儲過程
  2. 用戶定義的程序

存儲過程和用戶定義函數之間的區別:

  • 存儲過程不能在Select語句中使用。
  • 存儲過程支持延遲名稱解析。
  • 存儲過程通常用於執行業務邏輯。
  • 存儲過程可以返回任何數據類型。
  • 存儲過程可以接受比用戶定義的功能更多的輸入參數。 存儲過程最多可以有21,000個輸入參數。
  • 存儲過程可以執行動態SQL。
  • 存儲過程支持錯誤處理。
  • 非確定性函數可用於存儲過程。
  • 用戶定義的函數可以在Select語句中使用。
  • 用戶定義的函數不支持延遲名稱解析。
  • 用戶定義的函數通常用於計算。
  • 用戶定義的函數應該返回一個值。
  • 用戶定義的功能不能返回圖像。
  • 用戶定義的函數接受比存儲過程更少的輸入參數。 UDF最多可以有1,023個輸入參數。
  • 臨時表格不能用於用戶定義的功能。
  • 用戶定義的函數不能執行動態SQL。
  • 用戶定義的函數不支持錯誤處理。 在UDF中不允許RAISEERROR@@ERROR
  • 非確定性函數不能用於UDF。 例如, GETDATE()不能用於UDF。

從返回單個值的函數開始。 好的是,您可以將經常使用的代碼放入函數中,並將它們作為結果集中的列返回。

然後,你可以使用一個函數作為參數化的城市列表。 dbo.GetCitiesIn(“NY”)返回一個可以用作連接的表。

這是一種組織代碼的方式。 知道何時可重複使用以及何時浪費時間只能通過反複試驗和經驗來獲得。

另外,函數在SQL Server中是一個好主意。 它們速度更快,而且可以非常強大。 內聯和直接選擇。 小心不要過度使用。


決定何時使用以下幾點可能有所幫助 -

  1. 存儲過程不能返回一個表變量,其中函數可以做到這一點。

  2. 您可以使用存儲過程來更改服務器環境參數,而使用不能使用的功能。

乾杯


當您想要計算並返回一個用於其他SQL語句的值時,編寫用戶定義的函數; 在你想要的時候編寫一個存儲過程,而不是將一組可能複雜的SQL語句分組。 畢竟這是兩種截然不同的用例!


這是一個比存儲過程更喜歡函數的實際原因。 如果您有一個需要另一個存儲過程的結果的存儲過程,則必須使用insert-exec語句。 這意味著您必須創建臨時表並使用exec語句將存儲過程的結果插入到臨時表中。 這很混亂。 與此有關的一個問題是insert-exec不能嵌套

如果你堅持使用調用其他存儲過程的存儲過程,你可能會遇到這個問題。 如果嵌套存儲過程只是簡單地返回一個數據集,那麼它可以用一個表值函數替換,​​並且不會再出現這個錯誤。

這是我們應該保持業務邏輯不在數據庫中的另一個原因


  • 函數可以用在select語句中,而程序不能。

  • 存儲過程同時接受輸入和輸出參數,但函數僅接受輸入參數。

  • 函數無法返回類型為text,ntext,image&timestamps的值,其中的程序可以在此處顯示。

  • 函數可以在create table中用作用戶定義的數據類型,但是程序不能。

***例如:-create table <tablename>(name varchar(10),salary getsal(name))

這裡的getsal是一個用戶定義的函數,它返回一個工資類型,當創建表時,沒有存儲分配給工資類型,並且getsal函數也沒有執行,但是當我們從這個表中獲取一些值時,getal函數get被執行,返回類型作為結果集返回。





sql-function