Stored Procedure

Transact-SQL文セット

長所

1.サーバ/クライアントネットワークトラフィックの削減

は、プロセスを実行するためのコールのみがネットワークを介して送信されるため、

である.

プログラムでカプセル化されていない場合、すべてのコードの個別スレッドはネットワークを通過しなければならない.

2.強力なセキュリティ

複数のユーザおよびクライアント・プログラムは、ユーザおよびプログラムがその基本オブジェクトに直接的な権限を持たない場合でも、プロセスを介して基本データベース・オブジェクトに対して操作を実行することができる.

このプロシージャは、実行されるプロセスとアクティビティを制御し、基本データベース・オブジェクトを保護します.単一のオブジェクトレベルで権限を付与する必要がないため、セキュリティ層を簡素化することができる.

プロシージャ・パラメータを使用すると、SQL注入攻撃から保護できます.パラメータ入力は実行コードではなくテキスト値として扱われるため,攻撃者はプログラム内部のTransact−SQL文にコマンドを挿入することでセキュリティを破壊することは困難である.

プログラムは暗号化でき、ソースコードを乱読することができる.

3.コード再利用

4.メンテナンスが容易

クライアント・アプリケーションがプロセスを呼び出し、データベース・オペレーションをデータ・レイヤに保持する場合は、ベース・データベースの変更更新プロセスのみが必要です.アプリケーション・レイヤは独立しており、データベースのレイアウト、リレーションシップ、またはプロセスの変更を理解する必要はありません.

5.パフォーマンスの向上

デフォルトでは、プロセスは最初の実行時にコンパイルされ、後続の実行時に再使用される実行計画(実行)が生成されます.

ストアド・プロシージャのタイプ

Temporary Stored Procedure

2種類のlocal、global
local使用#
global使用##

System Stored Procedure

キー・データベース・エンジン(SQL Serverデータベース・エンジンの通常のメンテナンス用)

exec sp_databases --이런식으로 사용해볼 수 있음

Extended User-Defined Stored Procedure

Cなどのプログラミング言語で外部ルーチンを作成できます

User-Defined Stored Procedure

カスタム・プロシージャは、指定したデータベースまたはリソース・データベース以外のすべてのシステム・データベースで作成できます.

プロシージャの作成

syntax

CREATE [ OR ALTER ] { PROC | PROCEDURE }
[schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

 <procedure_option> ::=
 [ ENCRYPTION ]
 [ RECOMPILE ]
 [ EXECUTE AS Clause ]

in,outとともに使用(重要)

create procedure uspSumProduct @p1 int,
                               @p2 int,
                               @p3 int,
                               @sum int out,
                               @product int out
as
begin
set @sum = @p1 + @p2 + @p3
set @product = @p1 * @p2 * @p3
end
go 
------------
declare @v1 int, @v2 int;

exec uspSumProduct 3,5,7, @v1 out,@v2 out
print @v2
print @v1

Best Practices

Best Practices

Use the SET NOCOUNT ON statement as the first statement in the body of the procedure.

Use schema names when creating or referencing database objects in the procedure.

Avoid wrapping functions around columns specified in the WHERE and JOIN clauses.

Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

Avoid the use of SELECT *. Instead, specify the required column names.

Avoid processing or returning too much data. Narrow the results as early as possible in the procedure code

Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible.

Use the Transact-SQL TRY...CATCH feature for error handling inside a procedure.

Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQL statements in the body of the procedure. This prevents passing NULL to columns that do not allow null values.

Use NULL or NOT NULL for each column in a temporary table.

Use modification statements that convert nulls and include logic that eliminates rows with null values from queries.

Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values.

プロセス本文の最初の文として

SET NOCAUNT ON文を使用します.

ステップ

でデータベース・オブジェクトを作成または参照する場合は、スキーマ名を使用します.

WHEREおよびJOINセクションで指定したカラム周囲検出関数は使用しないでください.

大量のデータ行を返すSELECT文でスカラー関数を使用しないでください.スカラー関数をすべてのローに適用する必要があるため、ローベースの処理と同様に動作し、パフォーマンスが低下します.

SELECT*は使用しないでください.必要なカラム名を指定してください.

データを処理したり、返したりしないでください.できるだけ早くプロセスコードで結果の範囲を縮小してください.

BEGIN/COMIT TRANCEを使用して明示的なトランザクションを行い、可能な限り短縮します.

Transact-SQL TRYを使用...CATCH機能は、プロセス中にエラーを処理します.

プロシージャは、本明細書のCREATE TABLEまたはALTER TABLE Transact-SQL文が参照するすべてのテーブル列でデフォルトのキーワードを使用します.これにより、NULL値が許可されていないカラムにNULLが渡されません.

テンポラリ・テーブルの各カラムにNULLまたはNOT NULLを使用します.

nullを変換し、クエリからNull値行論理を削除する修正文を使用します.

に特別な要求がない限り、Union Olオペレータではなく、Unionまたは手術室オペレータを使用してください.

Limitations and Restrictions

<重要>
A procedure can reference tables that do not yet exist. At creation time, only syntax checking is performed. The procedure is not compiled until it is executed for the first time. Only during compilation are all objects referenced in the procedure resolved.
プログラムは、まだ存在しないテーブルを参照できます.生成時に文法チェックのみが実行されます.このプロシージャは、最初の実行までコンパイルされません.すべてのオブジェクトは、コンパイル中にのみ解析中に参照されます.