TSQLストレージプロセスの例

SQL Serverを例にストレージ・プロシージャを説明します.
ストレージ・プロシージャとは
ストレージ・プロシージャとは、データベースが特定のタスクを実現するために、複数の呼び出しを必要とする固定操作文をサーバに格納し、データベース・サーバがサブルーチンを介して呼び出すプログラム・セグメントに記述することです.
ストレージ・プロシージャの利点
1、ストレージプロセスはシステムの運行速度を速め、ストレージプロセスは作成時にコンパイルするだけで、後で実行するたびに再コンパイルする必要はない.
2、ストレージプロセスは複雑なデータベース操作をカプセル化し、操作プロセスを簡略化し、例えば複数のテーブルの更新削除などである.
3、モジュール化のプログラム設計を実現することができ、ストレージプロセスは何度も呼び出すことができ、統一的なデータベースアクセスインターフェースを提供し、アプリケーションのメンテナンス性を改善する.
4、ストレージ・プロシージャはコードのセキュリティを強化することができ、sqlserverでは指定したストレージ・プロシージャに対するユーザーの実行権限を設定することができ、ユーザーがストレージ・プロシージャのオブジェクトを直接操作できないようにする.
5、ストレージプロセスはネットワークトラフィックを低減することができ、ストレージプロセスコードは直接データベースに格納され、クライアントとサーバーの通信過程において、大量のt-sqlコードトラフィックは発生しない.
ストレージ・プロシージャの欠点
1、データベースの移植は不便で、ストレージプロセスはデータベース管理システムに依存し、例えばSQLServerのストレージプロセスにカプセル化されたオペレーティングコードは他のデータベース管理システムに直接移植できない.
2、オブジェクト向けの設計をサポートせず、オブジェクト向けの方式で論理業務をカプセル化することができず、汎用的なサポート可能なサービスの業務論理フレームワークを形成することもできない.
3、コードの可読性が悪く、メンテナンスしにくい
4、クラスタをサポートしない
ストアド・プロシージャの分類
1、システムストレージプロセス
システムストレージ・プロシージャは、データベース・サーバにあり、sp_最初に、呼び出し時にストレージ・プロシージャの前にデータベース制限名を付ける必要はありません.一部のストレージ・プロシージャはシステム管理者のみが使用でき、一部のプロシージャはライセンスによって他のユーザーにも使用できます.
2、カスタムストレージプロセス
このプロセスも最も一般的なプロセスであり、ユーザーがt-sql文を使用して作成し、特定のビジネスニーズを実現するために、ユーザーデータベースで作成したT-SQL文のセットです.ストアド・プロシージャに#を追加すると、一時的なストアド・プロシージャが作成され、ストアド・プロシージャに##を追加すると、グローバルな一時的なストアド・プロシージャが作成されます.
ユーザー定義ストレージ・プロシージャは、2つのクラスに分けられます.
1、T-SQL文保存プロセス.T-SQL文の集合であり、ユーザーが提供したパラメータを受け入れて返すことができます.
2、CLRストレージプロセス.は、アプリケーションのことです.NetFramework共通言語メソッドのストレージプロセス.ユーザが提供するパラメータを受け入れる、返すことができる.NetFrameworkではクラスとしての静的メソッドが実現される.
3、拡張ストレージプロセス
拡張ストレージ・プロシージャは、SQLSERVER環境外で動的リンク・ライブラリ(DLLファイル)を実行することによって実現されます.拡張ストレージ・プロシージャは、SQLServer 2012拡張ストレージ・プロシージャAPIを使用してプログラミングを完了し、拡張ストレージ・プロシージャは、xp_のプレフィックスを使用することができる.を参照してください.拡張ストレージ・プロシージャは、ユーザーにとって通常のストレージ・プロシージャと同様に、同じ方法で実行できます.
ストアド・プロシージャの作成
テーブルの作成

CREATE TABLE [dbo].[Stu](
	[ID] [int] NOT NULL,
	[Name] [varchar](50) NULL,
 CONSTRAINT [PK_Stu] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

パラメータなしのストアド・プロシージャの作成

USE TEST;
GO
CREATE PROCEDURE GetStu
AS
SELECT ID,NAME FROM Stu;
GO

ストレージ・プロシージャの最大容量は128 M
 
パラメータ付きストレージ・プロシージャの作成
 

USE TEST;
GO
CREATE PROCEDURE AddStu
@Id int,
@NAME varchar(50)
AS
INSERT INTO Stu([ID],[Name]) VALUES(@Id,@NAME)
GO

ストレージ・プロシージャの最大パラメータ数は2100です.
 
戻り値のあるストアド・プロシージャの作成

USE TEST;
GO
CREATE PROCEDURE GetStuName
@Id int,
@Name varchar(50) output
AS
SELECT @Name=Name FROM Stu WHERE ID=@Id;
GO

ストアド・プロシージャの呼び出し

USE TEST;
GO
--            
EXEC [dbo].[AddStu] 1,'  '
--        
EXEC [dbo].[GetStu]
--             
declare @name varchar(50)
EXEC GetStuName 1,@Name output
print @Name

ストアド・プロシージャの変更

USE TEST;
GO
ALTER PROCEDURE GetStuName
@Id int,
@Name varchar(50) output
AS
SELECT @Name=Name FROM Stu WHERE ID>@Id;
GO

キーワードALTER PROCEDURE+プロシージャ名
ストアド・プロシージャの削除

USE TEST;
GO
DROP PROCEDURE dbo.GetStuName;

キーワードDROP PROCEDURE+プロシージャ名
 
備考:PROCEDUREはPROCと略すことができます