SQL函數和預存程序的區別

本質上沒區別。只是函數有如：只能返回一個變數的限制。而預存程序可以返回多個。而函數是可以嵌入在sql中使用的,可以在select中調用，而預存程序不行。執行的本質都一樣。   函數限制比較多，比如不能用暫存資料表，只能用表變數.還有一些函數都不可用等等.而預存程序的限制相對就比較少   1. 一般來說，預存程序實現的功能要複雜一點，而函數的實現的功能針對性比較強。   2. 對於預存程序來說可以返回參數，而函數只能傳回值或者表對象。   3. 預存程序一般是作為一個獨立的部分來執行，而函數可以作為查詢語句的一個部分來調用，由於函數可以返回一個表對象，因此它可以在查詢語句中位於FROM關鍵字的後面。   4. 當預存程序和函數被執行的時候，SQL Manager會到procedure cache中去取相應的查詢語句，如果在procedure cache裡沒有相應的查詢語句，SQL Manager就會對預存程序和函數進行編譯。   Procedure cache中儲存的是執行計畫 (execution plan) ，當編譯好之後就執行procedure cache中的execution plan，之後SQL SERVER會根據每個execution plan的實際情況來考慮是否要在cache中儲存這個plan，評判的標準一個是這個execution plan可能被使用的頻率;其次是產生這個plan的代價，也就是編譯的耗時。儲存在cache中的plan在下次執行時就不用再編譯了。   預存程序和使用者自訂函數具體的區別   先看定義：   預存程序   預存程序可以使得對資料庫的管理、以及顯示關於資料庫及其使用者資訊的工作容易得多。預存程序是 SQL 陳述式和可選控制流程語句的先行編譯集合，以一個名稱儲存並作為一個單元處理。預存程序儲存在資料庫內，可由應用程式通過一個調用執行，而且允許使用者聲明變數、有條件執行以及其它強大的編程功能。   預存程序可包含程式流、邏輯以及對資料庫的查詢。它們可以接受參數、輸出參數、返回單個或多個結果集以及傳回值。   可以出於任何使用 SQL 陳述式的目的來使用預存程序，它具有以下優點：   可以在單個預存程序中執行一系列 SQL 陳述式。   可以從自己的預存程序內引用其它預存程序，這可以簡化一系列複雜語句。   預存程序在建立時即在伺服器上進行編譯，所以執行起來比單個 SQL 陳述式快。   使用者定義函數   函數是由一個或多個 Transact-SQL 陳述式組成的子程式，可用於封裝代碼以便重新使用。Microsoft? SQL Server? 2000 並不將使用者限制在定義為 Transact-SQL 語言一部分的內建函數上，而是允許使用者建立自己的使用者定義函數。   可使用 CREATE FUNCTION 語句建立、使用 ALTER FUNCTION 語句修改、以及使用 DROP FUNCTION 語句除去使用者定義函數。每個完全合法的使用者定義函數名 (database_name.owner_name.function_name) 必須唯一。   必須被授予 CREATE FUNCTION 許可權才能建立、修改或除去使用者定義函數。不是所有者的使用者在 Transact-SQL 陳述式中使用某個函數之前，必須先給此使用者授予該函數的適當許可權。若要建立或更改在 CHECK 條件約束、DEFAULT 子句或計算資料行定義中引用使用者定義函數的表，還必須具有函數的 REFERENCES 許可權。   在函數中，區別處理導致刪除語句並且繼續在諸如觸發器或預存程序等模式中的下一語句的 Transact-SQL 錯誤。在函數中，上述錯誤會導致停止執行函數。接下來該操作導致停止喚醒調用該函數的語句。   使用者定義函數的類型   SQL Server 2000 支援三種使用者定義函數：   純量涵式   內嵌資料表值函式   多語句資料表值函式   使用者定義函數採用零個或更多的輸入參數並返回標量值或表。函數最多可以有 1024 個輸入參數。當函數的參數有預設值時，調用該函數時必須指定預設 DEFAULT 關鍵字才能擷取預設值。該行為不同於在預存程序中含有預設值的參數，而在這些預存程序中省略該函數也意味著省略預設值。使用者定義函數不支援輸出參數。   純量涵式返回在 RETURNS 子句中定義的類型的單個資料值。可以使用所有純量資料型別，包括 bigint 和 sql_variant。不支援 timestamp 資料類型、使用者定義資料類型和非標量類型(如 table 或 cursor)。在 BEGIN...END 塊中定義的函數主體包含返回該值的 Transact-SQL 陳述式系列。傳回型別可以是除 text、ntext、image、cursor 和 timestamp 之外的任何資料類型。   資料表值函式返回 table。對於內嵌資料表值函式，沒有函數主體;表是單個 SELECT 語句的結果集。對於多語句資料表值函式，在 BEGIN...END 塊中定義的函數主體包含 TRANSACT-SQL 陳述式，這些語句可產生行並將行插入將返回的表中。有關內嵌資料表值函式的更多資訊，請參見內嵌使用者定義函數。有關資料表值函式的更多資訊，請參見返回 table 資料類型的使用者定義函數。   BEGIN...END 塊中的語句不能有任何副作用。函數副作用是指對具有函數外範圍(例如資料庫表的修改)的資源狀態的任何永久性更改。函數中的語句唯一能做的更改是對函數上的局部對象(如局部遊標或局部變數)的更改。不能在函數中執行的操作包括：對資料庫表的修改，對不在函數上的局部遊標進行操作，寄送電子郵件，嘗試修改目錄，以及產生返回至使用者的結果集。   函數中的有效語句類型包括：   DECLARE 語句，該語句可用於定義函數局部的資料變數和遊標。   為函數局部對象賦值，如使用 SET 給標量和表局部變數賦值。   遊標操作，該操作引用在函數中聲明、開啟、關閉和釋放的局部遊標。不允許使用 FETCH 語句將資料返回到用戶端。僅允許使用 FETCH 語句通過 INTO 子句給局部變數賦值。   控制流程語句。   SELECT 語句，該語句包含帶有運算式的挑選清單，其中的運算式將值賦予函數的局部變數。   INSERT、UPDATE 和 DELETE 語句，這些語句修改函數的局部 table 變數。   EXECUTE 語句，該語句調用擴充預存程序。   在查詢中指定的函數的實際執行次數在最佳化器產生的執行計畫間可能不同。樣本為 WHERE 子句中的子查詢喚醒調用的函數。子查詢及其函數執行的次數會因最佳化器選擇的訪問路徑而異。   使用者定義函數中不允許使用會對每個調用返回不同資料的內建函數。使用者定義函數中不允許使用以下內建函數：    @@CONNECTIONS @@PACK_SENT GETDATE   @@CPU_BUSY @@PACKET_ERRORS GetUTCDate   @@IDLE @@TIMETICKS NEWID   @@IO_BUSY @@TOTAL_ERRORS RAND   @@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR   @@PACK_RECEIVED @@TOTAL_WRITE     架構綁定函數   CREATE FUNCTION 支援 SCHEMABINDING 子句，後者可將函數綁定到它引用的任何對象(如表、視圖和其它使用者定義函數)的架構。嘗試對架構綁定函數所引用的任何對象執行 ALTER 或 DROP 都將失敗。   必須滿足以下條件才能在 CREATE FUNCTION 中指定 SCHEMABINDING：   該函數所引用的所有視圖和使用者定義函數必須是綁定到架構的。   該函數所引用的所有對象必須與函數位於同一資料庫中。必須使用由一部分或兩部分構成的名稱來引用對象。   必須具有對該函數中引用的所有對象(表、視圖和使用者定義函數)的 REFERENCES 許可權。   可使用 ALTER FUNCTION 刪除架構綁定。ALTER FUNCTION 語句將通過不帶 WITH SCHEMABINDING 指定函數來重新定義函數。   調用使用者定義函數   當調用標量使用者定義函數時，必須提供至少由兩部分組成的名稱：   SELECT *, MyUser.MyScalarFunction()FROM MyTable可以使用一個部分構成的名稱調用資料表值函式：   SELECT *FROM MyTableFunction()然而，當調用返回表的 SQL Server 內建函數時，必須將首碼 :: 添加至函數名：   SELECT * FROM ::fn_helpcollations()可在 Transact-SQL 陳述式中所允許的函數返回的相同資料類型運算式所在的任何位置引用純量涵式，包括計算資料行和 CHECK 條件約束定義。例如，下面的語句建立一個返回 decimal 的簡單函數：   CREATE FUNCTION CubicVolume-- Input dimensions in centimeters (@CubeLength decimal(4,1), @CubeWidth decimal(4,1), @CubeHeight decimal(4,1) )RETURNS decimal(12,3) -- Cubic Centimeters.ASBEGIN RETURN ( @CubeLength * @CubeWidth * @CubeHeight )END然後可以在允許整型運算式的任何地方(如表的計算資料行中)使用該函數：   CREATE TABLE Bricks ( BrickPartNmbr int PRIMARY KEY, BrickColor nchar(20), BrickHeight decimal(4,1), BrickLength decimal(4,1), BrickWidth decimal(4,1), BrickVolume AS ( dbo.CubicVolume(BrickHeight, BrickLength, BrickWidth) ) )dbo.CubicVolume 是返回標量值的使用者定義函數的一個樣本。RETURNS 子句定義由該函數返回的值的純量資料型別。BEGIN...END 塊包含一個或多個執行該函數的 Transact-SQL 陳述式。該函數中的每個 RETURN 語句都必須具有一個參數，可返回具有在 RETURNS 子句中指定的資料類型(或可隱性轉換為 RETURNS 中指定類型的資料類型)的資料值。RETURN 參數的值是該函數返回的值