複雑な関数を作成する Ms SQL の例。 SQL ストアドプロシージャ: 作成と使用

SQL ストアドプロシージャは、次の形式で保存できる実行可能なプログラムモジュールです。さまざまなオブジェクト。つまり、SQL ステートメントを含むオブジェクトです。これらのストアドプロシージャはクライアントで実行できます。アプリケーションプログラム良いパフォーマンスを得るために。さらに、そのようなオブジェクトは他のスクリプトや他のセクションから呼び出されることがよくあります。

導入

多くの人は、これらがさまざまなプロシージャ (MS SQL を除く) に似ていると信じています。おそらくこれは真実でしょう。これらは同様のパラメータを持ち、同様の値を生成できます。さらに場合によっては接触することもあります。たとえば、これらは DDL および DML データベース、およびユーザー関数 (コード名 UDF) と結合されます。

実際には、SQL ストアドプロシージャには広い範囲同様のプロセスとは一線を画す利点があります。セキュリティ、プログラミングの柔軟性、生産性 - これらすべてがデータベースを使用するユーザーをますます惹きつけています。この手順の人気のピークは 2005 年から 2010 年で、そのときマイクロソフトのプログラムが「 SQLサーバーマネジメントスタジオ」。そのおかげで、データベースの操作がはるかに簡単、より実用的、便利になりました。年々、これはプログラマーの間で人気を集めてきました。現在では、データベースと「通信」するユーザーにとっては Excel と同等の非常に馴染みのあるプログラムです。

プロシージャが呼び出されると、プロシージャは何もせずにサーバー自体によって即座に処理されます。不要なプロセスそしてユーザー介入。この後、削除、実行、または変更を実行できます。これらすべては DDL オペレーターの責任であり、DDL オペレーターだけが実行します。最も複雑なアクションオブジェクトを処理するため。さらに、これらすべては非常に迅速に行われ、サーバーは実際にはロードされません。この速度とパフォーマンスにより、大量の情報をユーザーからサーバーに、またはその逆に非常に迅速に転送できます。

情報を操作するためにこのテクノロジーを実装するには、いくつかのプログラミング言語があります。これらには、たとえば、Oracle の PL/SQL、InterBase および Firebird システムの PSQL、さらに従来の Microsoft Transact-SQL が含まれます。これらはすべてストアドプロシージャの作成と実行用に設計されており、大規模なデータベースプロセッサが独自のアルゴリズムを使用できるようになります。これは、そのような情報を管理する者が第三者による不正アクセスからすべてのオブジェクトを保護し、それに応じて特定のデータの作成、変更、または削除を保護できるようにするためにも必要です。

生産性

これらのデータベースオブジェクトは、さまざまな方法でプログラムできます。これにより、ユーザーは最も適した方法の種類を選択でき、労力と時間を節約できます。さらに、手順は自動的に処理されるため、サーバーとユーザー間の通信に費やす膨大な時間が回避されます。また、モジュールはいつでも再プログラムして希望の方向に変更することができます。 SQL ストアドプロシージャの起動速度は特に注目に値します。このプロセスは、同様の他のプロセスよりも高速に実行されるため、便利で汎用的です。

安全性

このタイプの情報処理は、次のことを保証するという点で同様のプロセスとは異なります。安全性の向上。これは、他のユーザーによるプロシージャへのアクセスを完全に排除できるという事実によって保証されます。これにより、管理者は情報の傍受を心配することなく、独立して操作を実行できるようになります。不正アクセスデータベースに。

データ転送

SQL ストアドプロシージャとクライアントアプリケーションの間の関係は、パラメータと戻り値の使用です。後者はストアドプロシージャにデータを渡す必要はありませんが、この情報は (主にユーザーの要求に応じて) SQL 用に処理されます。ストアドプロシージャは作業を完了すると、SQL ストアドプロシージャを呼び出すか返すために使用できるさまざまなメソッドを使用して、ストアドプロシージャを呼び出したアプリケーションにデータパケットを (ただし、オプションで再度) 送り返します。次に例を示します。

出力タイプパラメータを使用してデータを転送します。

return 演算子を使用してデータを渡す。

select 演算子を使用してデータを渡します。

では、このプロセスが内部からどのように見えるかを見てみましょう。

1. SQL で EXEC ストアドプロシージャを作成する

MS SQL (Managment Studio) でプロシージャを作成できます。プロシージャが作成されると、データベースのプログラマブルノードにリストされ、作成プロシージャがオペレータによって実行されます。 SQL ストアドプロシージャを実行するには、オブジェクト自体の名前を含む EXEC プロセスを使用します。

プロシージャを作成すると、最初にその名前が表示され、次にそれに割り当てられた 1 つ以上のパラメータが表示されます。パラメータはオプションである場合があります。パラメータ、つまりプロシージャの本体を記述した後、いくつかの必要な操作を実行する必要があります。

重要なのは、ボディにはローカル変数を配置でき、これらの変数はプロシージャに関してもローカルであるということです。つまり、体内でしか見ることができないのです。マイクロソフトの手順 SQLサーバー。この場合のストアドプロシージャはローカルとみなされます。

したがって、プロシージャを作成するには、プロシージャの名前と、プロシージャの本体として少なくとも 1 つのパラメータが必要です。この場合の優れたオプションは、分類子にスキーマ名を使用してプロシージャを作成して実行することです。

プロシージャの本体は、テーブルの作成、テーブルの 1 つ以上の行の挿入、データベースのタイプと性質の確立など、あらゆる種類のものにすることができます。ただし、プロシージャ本体は、その中での特定の操作の実行を制限します。重要な制限の一部を以下に示します。

本体では他のストアドプロシージャを作成しないでください。

身体はその物体に対して誤った印象を与えてはなりません。

身体が引き金を作ってはなりません。

2. プロシージャ本体に変数を設定する

変数をプロシージャ本体に対してローカルに作成すると、変数はプロシージャ本体内にのみ常駐するようになります。ストアドプロシージャ本体の先頭に変数を作成することをお勧めします。ただし、特定のオブジェクトの本体内の任意の場所に変数を設定することもできます。

場合によっては、複数の変数が 1 行に設定されており、各変数パラメーターがカンマで区切られていることに気づくかもしれません。また、変数には @ という接頭辞が付いていることに注意してください。プロシージャの本体では、変数を任意の場所に設定できます。たとえば、@NAME1 変数はプロシージャ本体の終わり近くで宣言される場合があります。宣言された変数に値を割り当てるには、一連の個人データが使用されます。複数の変数が同じ行で宣言されている状況とは異なり、この状況では 1 セットの個人データのみが使用されます。

ユーザーはよく「プロシージャ本体の 1 つのステートメントで複数の値を割り当てるにはどうすればよいですか?」という質問をします。良い。興味深い質問ですが、思っているよりもはるかに簡単です。回答: 「Select Var = value」などのペアを使用します。これらのペアをカンマで区切って使用できます。

さまざまな例で、簡単なストアドプロシージャを作成して実行する様子が示されています。ただし、プロシージャは、それを呼び出すプロセスがそれに近い値を持つようなパラメータを受け入れることができます (ただし、常にではありません)。それらが一致すると、対応するプロセスが体内で始まります。たとえば、呼び出し元から都市と地域を受け入れ、対応する都市と地域に属する著者の数に関するデータを返すプロシージャを作成するとします。このプロシージャは、Pubs などのデータベースの著者テーブルをクエリして、この著者のカウントを実行します。これらのデータベースを取得するために、たとえば、Google は SQL2005 ページから SQL スクリプトをダウンロードします。

前の例では、プロシージャは 2 つのパラメータを取ります。英語条件付きで @State および @City と呼ばれます。データ型はアプリケーションで定義された型と一致します。プロシージャの本体には内部変数 @TotalAuthors があり、この変数は作成者の数を表示するために使用されます。次にクエリ選択セクションがあり、すべてを計算します。最後に、print ステートメントを使用して、計算された値が出力ウィンドウに表示されます。

SQLでストアドプロシージャを実行する方法

手続きには2つの方法があります。最初の方法は、パラメータを渡すことによって、プロシージャ名の後にカンマ区切りのリストがどのように実行されるかを示します。 (前の例のように) 2 つの値があるとします。これらの値は、@State および @City プロシージャパラメーター変数を使用して収集されます。パラメーターを渡すこの方法では、順序が重要です。この方法は、順序引数の受け渡しと呼ばれます。 2 番目の方法では、パラメーターはすでに直接割り当てられており、この場合、順序は重要ではありません。この 2 番目のメソッドは、名前付き引数の受け渡しとして知られています。

この手順は、通常の手順とは若干異なる場合があります。すべては前の例と同じですが、ここでのみパラメータがシフトされます。つまり、@City パラメーターが最初に保存され、@State がデフォルト値の次に保存されます。通常、デフォルトのパラメータは個別に強調表示されます。 SQL ストアドプロシージャは単なるパラメータとして渡されます。この場合、指定されたパラメータ「UT」がデフォルト値「CA」を置き換えます。 2 回目の実行では、@City パラメーターに 1 つの引数値のみが渡され、@State パラメーターはデフォルト値の「CA」を受け取ります。経験豊富なプログラマは、デフォルトではすべての変数をパラメータリストの末尾の方に配置することをお勧めします。そうしないと実行できず、名前付き引数を渡す作業が必要になりますが、これは長くて複雑です。

4. SQL Server ストアドプロシージャ: 戻りメソッド

3つあります重要な方法呼び出されたストアドプロシージャでデータを送信します。それらは以下にリストされています。

ストアドプロシージャの値を返します。

ストアドプロシージャパラメータ出力。

ストアドプロシージャの 1 つを選択します。

4.1 SQLストアドプロシージャからの戻り値

この手法では、プロシージャはローカル変数に値を代入し、それを返します。プロシージャは定数値を直接返すこともできます。で次の例として、著者の合計数を返すプロシージャを作成しました。この手順を前の手順と比較すると、印刷値が逆転していることがわかります。

次に、プロシージャを実行してその戻り値を出力する方法を見てみましょう。プロシージャを実行するには、変数の設定と印刷が必要です。これは、このプロセス全体の後に実行されます。 print ステートメントの代わりに、Select @RetValue や OutputValue などの Select ステートメントを使用できることに注意してください。

4.2 SQL ストアドプロシージャのパラメータ出力

応答値を使用して単一の変数を返すことができます。これは前の例で見たものです。 Output パラメーターを使用すると、プロシージャで 1 つ以上の変数値を呼び出し元に送信できます。出力パラメータは、プロシージャを作成するときにこのキーワード「Output」によって正確に指定されます。パラメータが出力パラメータとして指定された場合、プロシージャオブジェクトはそれに値を割り当てる必要があります。 SQL ストアドプロシージャ (以下にその例を示します) は、この場合、概要情報を返します。

この例では、@TotalAuthors と @TotalNoContract という 2 つの出力名があります。それらはパラメータのリストに示されています。これらの変数は、プロシージャ本体内の値を割り当てます。出力パラメータを使用すると、呼び出し元はプロシージャの本体内に設定された値を確認できます。

また、前のシナリオでは、MS SQL Server ストアドプロシージャが出力パラメータに設定した値を確認するために 2 つの変数が宣言されています。次に、「CA」パラメータの通常の値を指定することによって手順が実行されます。次のパラメータが出力されるため、宣言された変数が渡されます。所定の方法で。変数を渡す場合、出力キーワードもここで設定されることに注意してください。プロシージャが正常に完了すると、出力パラメータによって返された値がメッセージウィンドウに表示されます。

4.3 SQL ストアドプロシージャの 1 つを選択する

この手法は、呼び出し元のストアドプロシージャに値のセットをデータテーブル (RecordSet) として返すために使用されます。この例では、@AuthID パラメーターを含む SQL ストアドプロシージャは、その @AuthId パラメーターを使用して返されたレコードをフィルター処理することによって、Authors テーブルをクエリします。 Select ステートメントは、ストアドプロシージャの呼び出し元に何を返すかを決定します。ストアドプロシージャが実行されると、AuthId が返されます。ここでのこのプロシージャは、常に 1 つのレコードのみを返すか、まったくレコードを返しません。ただし、ストアドプロシージャには、複数のレコードを返すことに対する制限はありません。複数の合計を提供することにより、計算変数を含む選択したパラメーターを使用してデータが返される例を見ることは珍しくありません。

ついに

ストアドプロシージャは非常に深刻ですソフトウェアモジュール、クライアントアプリケーションのおかげで、返信または送信、および必要な変数の設定が行われます。ストアドプロシージャ自体がサーバー上で実行されるため、サーバーとクライアントアプリケーションの間での大量のデータ交換 (一部の計算用) を回避できます。これにより、SQL サーバーの負荷が軽減され、当然、サーバーの所有者にとってもメリットが得られます。サブタイプの 1 つは T SQL ストアドプロシージャですが、優れたデータベースを作成する人にはこれらの学習が必要です。ストアドプロシージャを学習する際に役立つニュアンスが多数、さらには膨大な数ありますが、専門的にも含めてプログラミングに参加しようと考えている人にとっては、これがさらに必要となります。

CREATE FUNCTION - 関数を作成します

構文

説明

CREATE FUNCTION コマンドは新しい関数を定義します。 CREATE OR REPLACE FUNCTION は、新しい関数を作成するか、既存の関数の定義を置き換えます。関数を定義するには、適切な言語に対する USAGE 権限が必要です。

スキーマ名が指定されている場合、関数は指定されたスキーマ内に作成され、それ以外の場合は現在のスキーマ内に作成されます。新しい関数の名前は、このスキーマ内の同じ引数型を持つ既存の関数の名前とは異なる必要があります。ただし、異なる型の引数を持つ関数は同じ名前を持つことができます (これは 過負荷).

現在の定義を置き換えるには既存の機能、CREATE OR REPLACE FUNCTION コマンドを使用します。ただし、関数の名前や引数を変更することはできないことに注意してください (これを行おうとすると、実際には新しい独立した関数が作成されます)。さらに、CREATE OR REPLACE FUNCTION では、既存の関数の結果の型を変更することはできません。これを行うには、関数を削除して、再度作成する必要があります。 (これは、関数に出力パラメーター (OUT) がある場合、関数を削除することによってのみ OUT パラメーターのタイプを変更できることを意味します。)

CREATE OR REPLACE FUNCTION コマンドで既存の関数を置き換えても、その関数の所有権と権限は変更されません。他のすべての関数プロパティは、明示的に設定された値、またはコマンドによってデフォルトで設定された値を受け取ります。関数を置き換えるには、その関数の所有者 (または所有ロールのメンバー) である必要があります。

関数を削除してから再作成すると、新機能古いものとは別の存在になります。古い関数を参照する既存のルール、ビュー、トリガーなども削除する必要があります。したがって、関数を参照するオブジェクトを保持しながら関数の定義を変更するには、 CREATE OR REPLACE FUNCTION を使用する必要があります。さらに、多くの追加のプロパティ既存の関数は ALTER FUNCTION を使用して変更できます。

それを作成したユーザーが関数の所有者になります。

関数を作成するには、その引数の型と戻り値の型に対する USAGE 権限が必要です。

オプション

名前

作成する関数の名前 (スキーマで補足される可能性があります)。 引数モード

引数モード: IN (入力)、OUT (出力)、INOUT (入力および出力)、または VARIADIC (変数)。デフォルトは IN です。単一の VARIADIC 引数の後には、OUT 引数のみを続けることができます。さらに、OUT 引数と INOUT 引数を RETURNS TABLE 句と一緒に使用することはできません。 引数名

引数名。一部の言語 (SQL や PL/pgSQL など) では、この名前を関数の本体で使用できます。他の言語の場合、この名前は単に関数自体の追加説明として機能します。ただし、読みやすさを向上させるために、関数を呼び出すときに引数名を指定できます (セクション 4.3 を参照)。出力引数の名前は、結果の型の列の名前を指定するため、とにかく重要です。 (出力引数の名前を省略した場合、システムはデフォルトの名前を選択します。) 引数の種類

引数がある場合、関数の引数のデータ型 (スキーマで拡張される可能性があります)。引数のタイプは、ベース、複合、またはドメインにすることも、テーブル列参照にすることもできます。

実装言語によっては、次のように指定することもできる場合があります。「疑似タイプ」たとえば、cstring。疑似型は、引数の実際の型が不完全に定義されているか、セットの外に存在することを示します。レギュラータイプ SQL。

列型への参照は次のように記述されます。 テーブル名.列名％タイプ。この仕様により、テーブル定義の変更に依存しない関数を作成できるため、便利な場合があります。 デフォルトの式

パラメーターが明示的に指定されていない場合に、デフォルト値を計算するために使用される式。式の結果は、対応するパラメータの型に変換される必要があります。入力パラメータ (INOUT を含む) のみがデフォルト値を持つことができます。デフォルト値が定義されたパラメータに続くすべての入力パラメータにも、デフォルト値が定義されている必要があります。 結果の種類

戻りデータ型 (スキーマで拡張される可能性があります)。これは、ベース、複合、ドメインタイプ、またはテーブル列タイプへの参照にすることができます。実装言語によっては、これにより「疑似タイプ »、たとえば、cstring。関数が値を返さない場合、結果の型は void になります。

OUT または INOUT パラメータがある場合、RETURNS 句は省略できます。存在する場合、出力パラメータから推論された結果の型と一致している必要があります。複数の出力パラメータがある場合、戻り値の型は RECORD であり、単一の出力パラメータの型です。

SETOF を指定すると、関数が単一の要素ではなくセットを返すことを示します。

RETURNS TABLE エントリの出力列の名前。これは本質的に、名前付き出力パラメータ (OUT) を宣言する別の方法ですが、RETURNS TABLE には RETURNS SETOF も暗黙的に含まれます。 列の種類

RETURNS TABLE レコードの出力列のデータ型。 言語名

関数が実装されている言語の名前。これは、 sql 、 c 、 external 、または手続き型言語の名前です。ユーザー定義の、たとえば、plpgsql。この名前をアポストロフィで記述するスタイルは時代遅れであると考えられており、大文字と小文字の正確な一致が必要です。変換 (タイプの場合) タイプ名 } [, ... ] }

関数を呼び出すときに適用する必要がある変換のリストを設定します。変換は、SQL 型と言語固有のデータ型の間の変換を実行します。「トランスフォームの作成」を参照してください。組み込みの型変換は通常、手続き型言語の実装でハードコーディングされているため、ここで指定する必要はありません。手続き型言語の実装が型を処理できず、その型に利用できる変換がない場合は、デフォルトの型変換が実行されますが、これは実装に依存します。窓

WINDOW 表示は単純なものではないことを示していますが、 窓関数。現在、これは C で書かれた関数に対してのみ意味を持ちます。後で関数定義を変更しても、WINDOW 属性を変更することはできません。不変
安定した
揮発性

これらの属性は、関数の動作についてクエリオプティマイザーに通知します。一度に指定できる属性は 1 つだけです。属性が指定されていない場合は、デフォルトで VOLATILE が想定されます。

IMMUTABLE (定数) 特性は、関数がデータベースを変更できず、特定の引数値に対して常に同じ結果を返すことを示します。つまり、データベースにアクセスしたり、引数リストで明示的に渡されていない情報を使用したりしません。関数にこの特性がある場合、定数引数を使用した関数の呼び出しは、関数の値に直ちに置き換えられます。

STABLE 特性は、関数がデータベースを変更できず、1 回のテーブルスキャン内で常に同じ結果を返すことを示します。特定の値引数を指定しますが、この結果は次の条件によって異なる場合があります。さまざまな演算子 SQL。これ適切な選択結果がデータベースの内容と構成可能な設定 (現在のタイムゾーンなど) に依存する関数の場合。 (ただし、このオプションは、現在のコマンドによって変更された行を読み取る AFTER トリガーには適していません。) また、 current_timestamp ファミリの関数も、トランザクション内で結果が変わらないため、安定しているとみなされることにも注意してください。

VOLATILE (揮発性) 特性は、関数の結果が 1 回のテーブルスキャン内でも変更される可能性があるため、関数の呼び出しを最適化できないことを示します。この意味で変更可能なデータベース関数は比較的少数です (random()、currval()、timeofday() など)。ただし、副作用のある関数は、その結果が完全に予測可能な場合でも、呼び出しが最適化されないように、揮発性として分類する必要があることに注意してください。そのような関数の例: setval() 。

詳細については、セクション 35.6 を参照してください。漏れ防止

LEAKPROOF（漏れ防止）特性は、その機能が無いことを示します。副作用。結果を返す以外に、引数に関する情報は明らかにされません。たとえば、すべてではなく一部の引数値を含むエラーメッセージを生成する関数や、エラーメッセージに引数の値を出力する関数は気密ではありません。これは、セキュリティバリア (security_barrier を指定) を使用して作成されたビュー、または行セキュリティが有効になっているテーブルに対してシステムがクエリを実行する方法に影響します。制御されていないデータ漏洩を防ぐために、システムはセキュリティポリシーの条件をチェックし、ユーザーがリクエスト自体で指定した漏洩機能を含む条件の前に、セキュリティバリアのある定義を表示します。シール済みとしてマークされている関数と演算子は信頼されているとみなされ、セキュリティポリシーやセキュリティバリアのあるビューの条件が適用される前に実行できます。ただし、引数をとらない関数、またはセキュリティバリアビューまたはテーブルから引数を渡されない関数は、セキュリティ関連の条件が発生する前に実行するためにシールとしてマークする必要はありません。「CREATE VIEW」とセクション 38.5 を参照してください。このプロパティはスーパーユーザーのみが設定できます。 NULL 入力で呼び出される
NULL 入力で NULL を返す
厳しい

CALLED ON NULL INPUT (デフォルト) は、引数に NULL 値がある場合に関数が通常どおり呼び出されることを示します。この場合、NULL 値をチェックし、それに応じて処理するのは関数開発者の責任です。

RETURNS NULL ON NULL INPUT または STRICT を指定すると、引数の 1 つに NULL を受け取った場合、関数は常に NULL を返すことになります。このような関数は NULL 引数を指定して呼び出されることはありませんが、代わりに自動的に NULL 結果を想定します。 [ 外部] セキュリティインボーカー
[外部] セキュリティ定義者

SECURITY INVOKER 特性は、関数を呼び出したユーザーの権限で関数が実行されることを示します。このオプションはデフォルトです。 SECURITY DEFINER オプションは、関数を作成したユーザーの権限で関数が実行されることを指定します。

EXTERNAL キーワードは SQL 標準に準拠することが許可されていますが、SQL とは異なり、この特性は外部関数だけでなくすべての関数に適用されるため、オプションです。平行

PARALLEL UNSAFE の表示は、この関数がパラレルモードで実行できないこと、およびそのような関数がパラレルモードで存在することを意味します。 SQL文その結果、順次実行プランが選択されます。これはデフォルトの機能です。 PARALLEL RESTRICTED は、関数が並列実行できるが、グループの先頭のプロセスでのみ実行できることを意味します。 PARALLEL SAFE は、関数が制限なく並列実行しても安全であることを示します。

関数がデータベースの状態を変更したり、サブトランザクションの使用などのトランザクションに変更を加えたり、シーケンスにアクセスしたり、パラメーターを保存しようとしたり (setval の使用など) する場合、関数は同時実行に対して安全でないとマークする必要があります。制限された並列では、一時テーブル、クライアント接続状態、カーソル、プリペアドステートメント、またはシステムが並列同期できないその他のサーバープロセス状態にアクセスする関数をマークする必要があります (たとえば、setseed は、別のプロセスによって変更が行われるため、グループの先頭プロセスによってのみ実行できます)プロセスはマスターに送信されません）。一般に、関数が制限付きまたは安全でないときに安全であるとマークされている場合、または安全でないときに制限付き安全であるとマークされている場合、その関数を並列クエリで呼び出そうとすると、エラーまたは不正な結果が生成される可能性があります。 C 関数は、誤ってマークされた場合、理論的には完全に未定義の動作を示す可能性があります。これは、システムには任意の C コードから保護する方法がないためです。しかし、多くの場合、それらはすべて他の関数と同様に動作します。疑わしい場合は、関数を安全でない (UNSAFE) としてマークする必要があります。これがデフォルトです。 実行コスト

正の数値を指定するおおよその費用関数の実行 (cpu_operator_cost 単位)。関数がセットを返す場合、この数値は 1 行のコストを指定します。コストが指定されていない場合、C および内部関数の場合は 1 単位、その他すべての言語の関数の場合は 100 単位とみなされます。値が大きい場合、スケジューラはこの関数を必要以上に頻繁に呼び出さないようにします。 結果内の行数

スケジューラがこの関数からの出力として予期するおおよその行数を指定する正の数。この指示は、関数がセットを返すように宣言されている場合にのみ有効です。予想されるデフォルト値は 1000 行です。 構成パラメータ
意味

SET 句は、関数が呼び出されるときに、指定されたパラメータ設定は指定された値を受け入れ、関数が完了したときに以前の値を復元する必要があります。 SET FROM CURRENT 句は、関数の開始時に適用される値として、CREATE FUNCTION の実行時に有効な値を格納します。

SET が関数定義に追加される場合、同じパラメータに対して関数内で実行される SET LOCAL コマンドの影響は、関数の本体に限定されます。関数が終了すると、パラメータの前の値も復元されます。ただし、通常の SET コマンド (LOCAL なし) は、前のコマンドと同様に SET 句をオーバーライドします。 SETコマンド LOCAL: このようなコマンドの効果は、トランザクションがロールバックされない限り、関数の完了後も継続します。

パラメータの名前と値の詳細については、SET および第 18 章を参照してください。意味

関数の実装を定義する文字列定数。その意味は言語によって異なります。これは、内部関数の名前、オブジェクトファイルへのパス、SQL コマンド、または手続き型言語の関数のコードである可能性があります。

多くの場合、関数の定義を従来のアポストロフィではなくドルで囲むと便利です (セクション 4.1.2.4 を参照)。ドルを使用しない場合は、関数定義内のすべてのアポストロフィとバックスラッシュを複製してエスケープする必要があります。 オブジェクトファイル, オブジェクト_シンボル

この形式の AS 句は、C コード内の関数名が SQL 内の関数名と異なる場合に、動的にロードされる C 関数に使用されます。ライン オブジェクトファイル動的にロードされるオブジェクトを含むファイルの名前を指定します。 オブジェクト_シンボルリンクされた関数のシンボル、つまり C ソースコード内の関数の名前です。オブジェクトシンボルが省略された場合は、定義されている SQL 関数の名前と一致すると想定されます。 C では、すべての関数名が異なる必要があるため、C で実装されたオーバーロードされた関数には次の名前を付ける必要があります。さまざまな名前(たとえば、C 名に引数の型指定を含めます)。

CREATE FUNCTION の繰り返し呼び出しが同じオブジェクトファイルを参照する場合、そのオブジェクトファイルはセッション内で 1 回だけロードされます。このファイルを再度アップロードおよびダウンロードするには (たとえば、開発中)、新しいセッションを開始します。属性

歴史的には、関数に関する追加情報を渡す方法です。このエントリには次の属性が含まれる場合があります。

厳密です

STRICT または RETURNS NULL ON NULL INPUT と同等。キャッシュ可能です

isCachable プロパティは、非推奨の IMMUTABLE と同等のプロパティです。下位互換性のために引き続きサポートされています。

属性名では大文字と小文字が区別されません。

後ろに追加情報機能開発については、セクション 35.3 を参照してください。

過負荷

PostgreSQL では、 過負荷機能; つまり、1 つの名前を複数の名前に使用できるようになります。さまざまな機能、異なるタイプの入力引数がある場合。この機能を使用するかどうかに関係なく、一部のユーザーが他のユーザーを信頼していないデータベースで関数を呼び出す場合には注意が必要です。セクション10.3を参照してください。

2 つの関数は、名前と型が同じであれば同じとみなされます。入力引数の場合、OUT パラメータは無視されます。したがって、たとえば次のような宣言は競合を引き起こす可能性があります。

CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, out text) ...

持つ機能他の種類引数は作成時には競合しないとみなされますが、引数に提供されるデフォルト値は使用時に競合を引き起こす可能性があります。たとえば、次の定義を考えてみましょう。

CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, int デフォルト 42) ...

foo(10) の呼び出しは、呼び出す関数の選択があいまいなために失敗します。

ノート

関数の引数と戻り値の宣言では、完全な SQL 型宣言構文が許可されます。ただし、括弧内の型修飾子 (たとえば、数値型の精度フィールド) は、CREATE FUNCTION コマンドでは考慮されません。したがって、たとえば、 CREATE FUNCTION foo (varchar(10)) ... は、 CREATE FUNCTION foo (varchar) ... と同じ関数を作成します。

CREATE OR REPLACE FUNCTION を使用して既存の関数を置換する場合、パラメーター名の変更には制限があります。特に、入力パラメータにすでに割り当てられている名前を変更することはできません (ただし、以前に名前が付けられていないパラメータに名前を追加することはできます)。また、関数に複数の出力パラメータがある場合、出力パラメータの名前を変更することはできません。これは、匿名関数の列の名前が変更されるためです。複合型、関数の結果を説明します。これらの制限は、関数への既存の呼び出しが置換後に機能を停止しないようにするのに役立ちます。

関数が VARIADIC 引数を使用して STRICT として宣言されている場合、厳密性の評価ではすべての値がチェックされます。変数配列 一般的に NULLではありません。この配列に NULL 要素が含まれている場合、関数が呼び出されます。

例

以下に簡単な導入例をいくつか示します。後ろに追加情報例についてはセクション 35.3 を参照してください。

CREATE FUNCTION add(integer, integer) RETURNS integer AS "select $1 + $2;" LANGUAGE SQL IMMUTABLE は NULL 入力で NULL を返します。

PL/pgSQL で、名前付き引数を使用して整数を 1 ずつインクリメントする関数:

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1; 終わり; $$ 言語 plpgsql;

複数の出力パラメータを持つレコードを返す関数:

CREATE FUNCTION dup(in int, out f1 int, out f2 text) AS $$ SELECT $1, CAST($1 AS text) || " はテキストです" $$ LANGUAGE SQL; SELECT * FROM dup(42);

複合型を明示的に宣言することで、同じことをより詳細に実行できます。

CREATE TYPE dup_result AS (f1 int, f2 text); CREATE FUNCTION dup(int) RETURNS dup_result AS $$ SELECT $1, CAST($1 AS text) || " はテキストです" $$ LANGUAGE SQL; SELECT * FROM dup(42);

複数の列を返す別の方法は、TABLE 関数を使用することです。

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text) AS $$ SELECT $1, CAST($1 AS text) || " はテキストです" $$ LANGUAGE SQL; SELECT * FROM dup(42);

ただし、TABLE の例は、関数が実際に 1 つを返すのではなく、前の例とは異なります。 キット記録。

安全なSECURITY DEFINER機能の開発

なぜならセキュリティ機能 DEFINER は、それを作成したユーザーの権限で実行されるため、他の目的に使用できないように注意する必要があります。セキュリティ上の理由から、search_path では、信頼できないユーザーが書き込み可能なスキーマを除外する必要があります。これにより、悪意のあるユーザーが、関数で使用されるオブジェクトをマスクする独自のオブジェクト (テーブル、関数、ステートメントなど) を作成することが防止されます。この点で、一時テーブルスキーマを除外することが特に重要です。一時テーブルスキーマはデフォルトで最初に表示され、デフォルトで全員が書き込みアクセス権を持ちます。一時的なスキームを検索リストの最後に置くことで、適切な保護を実現できます。これを行うには、pg_temp を実行する必要があります最後のエントリ search_path 内。安全な使用次の関数は次のことを示しています。

CREATE FUNCTION check_password(uname TEXT, pass TEXT) RETURNS BOOLEAN AS $$ DECLARE 渡された BOOLEAN; BEGIN SELECT (pwd = $2) INTO が FROM pwds WHERE ユーザー名 = $1 に渡されました。 RETURN が成功しました。終わり; $$ LANGUAGE plpgsql セキュリティ定義 -- 最初に信頼できるスキーマに安全な検索パスを設定し、次に「pg_temp」を設定します。 SET 検索パス = 管理者、pg_temp;

この関数は admin.pwds テーブルにアクセスする必要がありますが、 SET 句がない場合、または admin のみを含む SET 句がある場合、一時テーブル pwds を作成することで騙される可能性があります。

PostgreSQL 8.3 より前には SET 句がなかったため、古い関数には、search_path 変数を保存、変更、復元するための非常に複雑なロジックが含まれる可能性があります。現在の SET サービスでは、これがはるかに簡単になります。

また、作成された関数のデフォルトの実行権限は PUBLIC であることに注意してください (詳細については GRANT を参照)。ただし、多くの場合、定義者のコンテキストで動作する関数へのアクセスを一部のユーザーのみに制限する必要があります。これを行うには、取り消す必要があります標準的な権利 PUBLIC を選択し、個別に実行権限を付与します。新しい機能がまったく使用できないウィンドウが作成されるのを避けるには、ウィンドウを作成し、1 つのトランザクションで権限を割り当てます。たとえば、次のようになります。

始める; CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... セキュリティ定義者; 関数 check_password(uname TEXT, pass TEXT) を PUBLIC からすべて取り消します。関数 check_password(uname TEXT, pass TEXT) の実行を管理者に許可します。専念;

互換性

CREATE FUNCTION コマンドは、SQL:1999 以降の標準で定義されています。 PostgreSQL に実装されているバージョンは標準化されたバージョンに近いですが、完全には準拠していません。特に、属性は、異なる実装言語と同様に移植性がありません。

他のDBMSとの互換性のため 引数モード後に書くことができます 引数名またはその前ですが、最初のオプションのみが標準を満たします。

パラメータのデフォルト値を定義する場合、SQL 標準では DEFAULT キーワードを使用した構文のみがサポートされます。 = 構文は T-SQL と Firebird で使用されます。

注意！この作品「17.1.」セクションの翻訳に基づいて構築されています。ストアドルーチンと付与テーブル」、MySQL 5.0.19 ソフトウェアの説明、「リファレンスマニュアル」。 MySQL 5.0 から 5.0.19 について説明します。ドキュメントの作成日: 2006-01-23 (リビジョン:995)」
「まずすべて読んでから、例を試してください。」

ストアドプロシージャは、コンパイルしてサーバーに保存できる SQL コマンドのセットです。このようにして、クライアントは、頻繁に使用されるクエリを保存する代わりに、対応するストアドプロシージャを参照できます。これにより、よりよい性能、なぜならこのリクエスト解析は 1 回だけで済み、サーバーとクライアント間のトラフィックが軽減されます。サーバー上に関数のライブラリを作成することによって、概念的レベルを上げることもできます。

トリガーは、特定のイベントが発生したときに実行されるストアドプロシージャです。たとえば、トランザクションテーブルからレコードが削除されるたびに実行されるストアドプロシージャを定義すると、すべてのトランザクションが削除されたときに、対応する顧客が顧客テーブルから自動的に削除されるようになります。

ストアドプログラム (プロシージャと関数) は MySQL 5.0 でサポートされています。ストアドプロシージャは、サーバーに保存できる SQL 式のセットです。これが完了すると、クライアントはリクエストを再送信する必要はなくなり、保存されたプログラムを呼び出すだけで済みます。

これは次のような場合に役立ちます。

多数のクライアントアプリケーションがさまざまな言語または、他のプラットフォームでも動作しますが、同じ操作データベースを使用する必要があります
安全が第一です

ストアドプロシージャと関数 (ルーチン) は、クライアントとサーバー間で送信する必要がある情報が少なくなるため、パフォーマンスが向上します。この選択により、データベースサーバーの負荷は増加しますが、クライアント側のコストは削減されます。多くのクライアントマシン (Web サーバーなど) が 1 つ以上のデータベースによってサービスを提供されている場合は、これを使用します。

ストアドルーチンを使用すると、サーバーデータベースに保存されている関数ライブラリを使用することもできます。この機会を多くの方にご提供します現代語それらを直接呼び出すことを可能にするプログラミング (たとえば、クラスを使用)。

MySQL は、IBM の DB2 ですでに使用されている SQL:2003 のストアドプロシージャ構文に従っています。

言葉から行動まで…

ストアドルーチンを作成、変更、削除するとき、サーバーは mysql.proc テーブルを操作します。

MySQL 5.0.3 以降では、次の権限が必要です。

ルーティンの作成ストアドプロシージャを作成するには

ルーチンを変更する手続きの変更や削除が必要になります。この権限はプロシージャ（関数）の作成者に自動的に割り当てられます。

実行するサブルーチンを実行するには権限が必要です。ただし、プロシージャ（関数）の作成者に自動的に割り当てられます。また、デフォルトでは、DEFINER ルーチンの SQL SECURITY パラメータにより、データベースにアクセスできるユーザーがこのデータベースに関連付けられたルーチンを呼び出すことができます。

ストアドプロシージャと関数の構文

ストアドルーチンはプロシージャまたは関数です。ストアドルーチンは、CREATE PROCEDURE または CREATE FUNCTION ステートメントを使用して作成されます。ストアドルーチンは CALL ステートメントを使用して呼び出され、値を返す変数のみが出力変数として使用されます。この関数は他の関数と同様に呼び出すことができ、スカラー値を返すことができます。ストアドルーチンは他のストアドルーチンを呼び出すことができます。

MySQL 5.0.1 以降、ロードされたプロシージャまたは関数は特定のデータベースに関連付けられます。これにはいくつかの意味があります。

サブルーチンが呼び出されるとき、その目的は USE db_name を呼び出すことです (サブルーチンが完了してデータベースが不要になったときにデータベースを破棄します)。
通常の名前をデータベース名で修飾できます。これは、現在のデータベースにないルーチンを参照するために使用できます。たとえば、テストデータベースに関連付けられたストアドプロシージャ p または関数 f を実行するには、 CALL test.p() または test.f() のようにコマンドインタープリタに指示できます。
データベースが削除されると、それに関連付けられているロードされたルーチンもすべて削除されます。 MySQL 5.0.0 では、ロードされたルーチンはグローバルであり、データベースには関連付けられません。これらは、呼び出し側ステートメントからデフォルトのデータベースを継承します。サブルーチン内で USE db_name を実行すると、サブルーチンを終了した後に元の現在のデータベースが復元されます (たとえば、現在のデータベースが db_11 である場合、db_22 を使用するサブルーチンを呼び出します。サブルーチンを終了した後は、現在の db_11 が残ります)。

MySQL は、ストアドプロシージャ内で (カーソルやローカル変数を使用せずに) 正規の SELECT 式を使用できるようにする拡張機能を完全にサポートしています。クエリから返された結果セットは、単純にクライアントに直接送信されます。複数の SELECT クエリでは複数の結果セットが生成されるため、クライアントは複数の結果セットをサポートするライブラリを使用する必要があります。

プロシージャの作成- ストアドプロシージャを作成します。

関数の作成- ストアド関数を作成します。

構文：

プロシージャの作成プロシージャ名 ([プロシージャ_パラメータ[,...]])
[特徴...] サブルーチン本体

関数の作成関数名 ([関数パラメータ[,...]])
戻り値タイプ
[特徴...] サブルーチン本体

プロシージャ_パラメータ:
[ で | 外 | INOUT] パラメータ名タイプ
関数パラメータ:
パラメータ名の型

タイプ：
任意の MySQL データ型

サブルーチン本体:
正しいSQL式。

実際にすべてを見てみましょう。

まず、次のクエリを使用してストアドプロシージャを作成しましょう。

CREATE PROCEDURE `my_proc`(OUT t INTEGER(11))
決定的ではない
SQL セキュリティ起動者
コメント ""
始める
`my` LIMIT 0,1 から val1+val2 を "t" に選択します。
終わり;

このクエリでの LIMIT 式の使用は、すべてのクライアントが複数行の結果セットを受け入れることができるわけではないという事実を認識して行われています。

その後、それを呼び出しましょう:

my_proc(@a) に呼び出します。
を選択;

内部リクエストと外部リクエストを分離するには、通常とは異なるセパレータが常に使用されます (タスクの場合は、次のコマンドを使用します) デリミタ <строка/символ>)

すべての要件を考慮した別の例を次に示します。

Mysql> 区切り文字 //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> 開始
-> SELECT COUNT(*) INTO param1 FROM t;
->終了;
-> //

mysql>区切り文字;
mysql> CALL simpleproc(@a);
クエリは OK、影響を受ける行は 0 (0.00 秒)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
セット内の 1 行 (0.00 秒)

プロセス全体は次の図で確認できます。

トリガー

MySQL では、バージョン 5.0.2 以降、トリガーのサポートが利用可能になりました。

引き金- テーブルに関連付けられ、特定のイベント (このテーブルに関連付けられたイベント) が発生したときにアクティブ化される名前付きデータベースオブジェクト。

たとえば、次のコードはテーブルと INSERT トリガーを作成します。トリガーは、テーブル列の 1 つに挿入された値を合計します。

Mysql> CREATE TABLE アカウント (acct_num INT、金額 DECIMAL(10,2));
クエリは OK、影響を受ける行は 0 (0.03 秒)
mysql> アカウントに挿入する前に ins_sum トリガーを作成します
-> 各行セット @sum = @sum + NEW.amount;
クエリは OK、影響を受ける行は 0 (0.06 秒)

変数 sum を宣言し、値 1 を割り当てましょう。その後、テーブルに挿入するたびに、 アカウントこの変数の値は、挿入された部分に応じて増加します。

コメント。変数の値が初期化されていない場合、トリガーは機能しません。

トリガーを作成するための構文

作成する

引き金トリガー名トリガー時間トリガーイベント
ON table_name FOR EACH ROWexpression_executed_when_trigger_fires

トリガー名とユーザー名がすぐにわかったら、「トリガー時間」と「イベント」について個別に説明します。

トリガー時間

トリガーアクションが発生する時間を定義します。 BEFORE はトリガーイベントが完了する前にトリガーが実行されることを意味し、AFTER はその後を意味します。たとえば、レコードを挿入する場合 (上記の例を参照)、レコードを実際に挿入する前にトリガーが起動され、合計が計算されます。このオプションは、テーブル内の追加フィールドの事前計算や、別のテーブルへの並行挿入に適しています。

トリガーイベント

ここではすべてが簡単です。どのイベントでトリガーが実行されるかを明確に示します。

挿入: つまり挿入操作または同様の式 (INSERT、LOAD DATA、および REPLACE) の実行中
UPDATE: エンティティ (行) が変更されたとき
DELETE: レコードが削除されたとき (DELETE および/または REPLACE ステートメントを含むクエリ)

1) 標準機能一覧

関数	結果を返す
ビット長()	ビット数
キャスト(役名)	、指定された値に変換されます
Char_Length()	文字の長さ
変換(使用)	、指定に従って変換されます。
現在の日付	現在の日付
現在の時刻（）	指定した時刻からの現在時刻
現在のタイムスタンプ()	指定された時刻からの現在の日時
抜粋（から）	日付からの指定（日、時間など）
より低い（）	、小文字に変換
オクテットの長さ()	のバイト数
位置(インチ)	入る位置
部分文字列(から)	位置から始まり、指定された内容を持つ部分
トリム(先頭\|末尾\|両方から)	、先頭のものは削除されています。終わり \| 両側に
アッパー（）	、大文字に変換
ユーザー	ユーザーIDを定義します

2) MS SQL Server の機能の概要
したがって、SQL Server は、次のグループに分類される多くの機能を提供します。
→ 文字列;
→ 数学;
→ 変換;
→ テキスト型と画像型のデータを操作するには;
→ 日付を扱う場合。
→ システム;
→ Niladic (null-place - パラメータなし)。
そして全行その他の機能。

3) Oracle の機能の概要
→ エラーを処理するため。
→ 数値。
→ 文字列;
→ 変換;
→ ブロードキャスト、日付を操作するため。
→用途はいろいろ。

ストアド関数の宣言

関数の作成 ([ [()], …]) 戻り値 [()] [[ない] 決定的] [SQL を含む|SQL データを読み取る|SQL データを変更する]
始める

戻る
終わり

キーワード
. 決定的関数が同じ入力値を与えられた場合に同じ値を返すかどうかを示します。たとえば、CURRENT_TIME 関数は DETERMINISTIC ではありません。
. SQLを含むは、関数にデータの読み取りと変更を行う SQL ステートメントが含まれていないことを示します。この値はデフォルトで設定されています。
. SQL データを読み取ります関数に SELECT または FETCH ステートメントが含まれていることを示します。
. SQLデータを変更します関数に INSERT、UPDATE、または DELETE ステートメントが含まれていることを示します。

非決定的関数の制限事項

オラクルの場合:
。 CHECK ステートメントで制約をチェックする場合、非決定的関数は使用できません。さらに、制限にはユーザー定義関数の呼び出しを含めることはできません。
。非決定的関数は関数ベースのインデックスでは使用できません。

SQL Server では、次の場合、ユーザー定義関数は決定的であるとみなされます。
。関数はスキーマにバインドされています。この関数は SCHEMABINDING オプションを使用して作成されます。これは、関数によって参照されるオブジェクトを変更または削除できないことを意味します。
。関数の本体から呼び出されるすべての関数 (組み込みまたはユーザー定義) は決定的です。
。関数の本体には、スコープ外のデータベースオブジェクト (テーブル、ビュー、その他の関数など) への参照は含まれません。
。この関数は、拡張ストアドプロシージャ (データベースの状態を変更する可能性がある) を呼び出しません。

保存された機能の削除と変更

関数を削除するには、次の演算子を使用します。

関数を変更するには、次の演算子を使用します。

関数の変更 ([[{入力|出力|入力}] [()],…])
始める

戻る
終わり

SQL Server のストアド関数

関数の作成 ([@ [として] [()] [=] [読み取り専用],…])
戻り値 [()]
[として]
始める

戻る
終わり

例：

B1を使用します。
行く
CREATE FUNCTION Quarter(@Dat DateTime) RETURNS int
始める
@ISQuarter int を宣言します。
IF ((Month(@Dat)>=1) And (Month(@Dat)=4) And (Month(@Dat)=8) And (Month(@Dat)=10) And (Month(@Dat)

複雑な関数を作成する Ms SQL の例。 SQL ストアド プロシージャ: 作成と使用

導入

生産性

安全性