sql-server ファンクション 使い方 - 関数とSQL Serverのストアドプロシージャ





8 Answers

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.    |                                        |
+---------------------------------+----------------------------------------+
sqlserver ストアドファンクション 入門

私はかなり長い間関数とストアドプロシージャを学習してきましたが、なぜ、いつ、私は関数やストアドプロシージャを使うべきか分かりません。 彼らは私と同じように見える、おそらく私はそれについてちょっと初心者です。

何人かが私に理由を教えてくれますか?




他のSQL文で使用する値を計算して返すときは、ユーザ定義関数を記述します。 SQLステートメントの複雑なセットをグループ化することが必要な場合には、ストアード・プロシージャーを作成してください。 結局のところ、これは2つのかなり異なるユースケースです!




基本的な違い

関数は値を返す必要がありますが、ストアドプロシージャではオプションです(プロシージャはゼロまたはn値を返すことができます)。

プロシージャは入出力パラメータを持つことができますが、関数は入力パラメータのみを持つことができます。

関数は1つの入力パラメータを取りますが、必須ですが、ストアドプロシージャはo〜n個の入力パラメータを取ることができます。

プロシージャから関数を呼び出すことはできますが、関数からプロシージャを呼び出すことはできません。

事前差額

プロシージャはSELECT文とDML(INSERT / UPDATE / DELETE)文を許可しますが、FunctionはSELECT文のみを許可します。

プロシージャはSELECT文では利用できませんが、関数はSELECT文に埋め込むことができます。

ストアドプロシージャは、WHERE / HAVING / SELECTセクションの任意の場所にあるSQL文では使用できませんが、Functionは使用できます。

テーブルを返す関数は別の行セットとして扱うことができます。 これは他のテーブルとのJOINで使用できます。

インライン関数は、パラメータをとるビューとして使用できますが、JOINや他の行セット操作で使用できます。

例外はプロシージャ内のtry-catchブロックで処理できますが、try-catchブロックは関数内で使用できません。

プロシージャのトランザクション管理に行くことができますが、我々は機能に入ることはできません。

source




ストアドプロシージャ はスクリプトとして使用されます。 彼らはあなたのために一連のコマンドを実行し、それらは特定の時間に実行するようにスケジュールすることができます。

関数 はメソッドとして使用されます。 あなたは何かを渡し、結果を返します。 小さくて速いものでなければならない。




ストアドプロシージャ:

  • SQL Serverの小型プログラムのようなものです。
  • select文のように単純なものでも、データベースの複数のテーブルからデータを追加、削除、更新、および/または読み込む長いスクリプトと同じくらい複雑なものでもかまいません。
  • (ループとカーソルを実装することができます。どちらも、結果を小さくしたり、行単位でデータを操作したりできます。
  • EXECまたはEXECUTEステートメントを使用して呼び出す必要があります。
  • テーブル変数を返しますが、 OUTパラメータは使用できません。
  • トランザクションをサポートします。

関数:

  • データベースへのレコードの更新、削除、または追加には使用できません。
  • 単純に単一の値またはテーブルの値を返します。
  • レコードの選択にのみ使用できます。 ただし、次のような標準SQL内から非常に簡単に呼び出すことができます。

    SELECT dbo.functionname('Parameter1')
    

    または

    SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
    
  • 単純な再利用可能な選択操作のために、関数はコードを単純化することができます。 あなたの関数でJOIN節を使用することにちょうど注意してください。 ファンクションにJOIN句があり、複数の結果を戻す別のSELECT文からコールすると、そのファンクション・コールは、結果セットに戻された各行に対してこれらの表を一緒にJOINます。 だから、いくつかのロジックを単純化するのに役立つかもしれませんが、それらが適切に使われなければ、パフォーマンスのボトルネックになることもあります。

  • OUTパラメータを使用して値を返します。
  • トランザクションはサポートされていません。



SQL Serverの機能は、カーソルのように、最後の武器として使用されることを意図しています。 彼らはパフォーマンス上の問題を抱えているため、テーブル値関数の使用はできるだけ避けるべきです。 パフォーマンスについて言えば、中規模のハードウェア上のサーバーにホストされている1,000,000を超えるレコードを持つテーブルについて言及しています。 それ以外の場合は、関数によって引き起こされるパフォーマンスのヒットを心配する必要はありません。

  1. 結果セットを外部コード(ADO.Netなど)に返す関数を使用しないでください。
  2. 可能な限りビュー/ストアドプロシージャの組み合わせを使用します。 DTA(Database Tuning Adviser)が(インデックス付きのビューや統計情報のように)あなたに与える提案を使用して、将来の成長パフォーマンスの問題から回復することができます。

詳細はhttp://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.htmlを参照してください。




  • Functionがストアド・プロシージャ用ではない値を返すことは必須です。
  • SelectステートメントはUDFでのみ受け入れられ、DMLステートメントは必須ではありません。
  • ストアド・プロシージャは、DML文と同様に文も受け入れます。
  • UDFは入力のみを許可し、出力は許可しません。
  • ストアドプロシージャは、入力と出力の両方を可能にします。
  • キャッチ・ブロックはUDFでは使用できませんが、ストアード・プロシージャーで使用できます。
  • トランザクションはUDFではなく、ストアード・プロシージャーで許可されています。
  • テンポラリ・テーブルではなく、UDFではテーブル変数のみを使用できます。
  • ストアドプロシージャは、テーブル変数と一時テーブルの両方を許可します。
  • UDFでは、ストアード・プロシージャーが関数から呼び出されるのを許可しませんが、ストアード・プロシージャーは関数の呼び出しを許可します。
  • UDFはjoin句で使用され、ストアドプロシージャはjoin句では使用できません。
  • ストアドプロシージャは常にゼロへの戻りを許可します。 逆に、UDFは、あらかじめ決められたポイントまで戻らなければならない値を持っています。



  • プロシージャではできないselect文では、関数を使用できます。

  • ストアドプロシージャは入力パラメータと出力パラメータの両方を使用しますが、関数は入力パラメータのみをとります。

  • 関数は、text、ntext、image&timestampsの型の値を、プロシージャと同じように返すことはできません。

  • 関数はcreate tableのユーザー定義データ型として使用できますが、プロシージャでは使用できません。

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

ここでgetsalは給与タイプを返すユーザ定義関数で、テーブルが作成されても給与タイプには記憶容量は割り当てられていません。また、getsal関数も実行されませんが、このテーブルからいくつかの値を取得すると、戻り値の型が結果セットとして返されます。




Related