T-SQL ストアドプロシージャ・関数向け処理

T-SQL(Transact-SQL)はMicrosoft SQL Serverで使われるSQLのバリエーションの1つです。

当ページではストアドプロシージャ、および関数を組むときに用いることが多い処理についてまとめています。

オブジェクトの操作・制御

ストアドプロシージャ・関数向け処理

その他

ストアドプロシージャの作成

ストアドプロシージャを作成します。引数を利用するかどうかで若干書き方が変わります。

文法(引数無し)

CREATE PROCEDURE ストアドプロシージャ名

AS

※処理

文法(引数有り)

CREATE PROCEDURE ストアドプロシージャ名 (

@引数1 データ型,

@引数2 データ型,

:

:

:

@引数n データ型

)

AS

※処理

ところで、引数有りの書き方をしたとき引数を指定しないとエラーになります。そんなときは引数にデフォルト値を設定しておくとエラーになりません。

文法(引数有り・デフォルト値付き)

CREATE PROCEDURE ストアドプロシージャ名 (

@引数1 データ型 = デフォルト値,

@引数2 データ型 = デフォルト値,

:

:

:

@引数n データ型 = デフォルト値

)

AS

※処理

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

ストアドプロシージャを更新します。引数を利用するかどうかで若干書き方が変わります。

文法(引数無し)

ALTER PROCEDURE ストアドプロシージャ名

AS

※処理

文法(引数有り)

ALTER PROCEDURE ストアドプロシージャ名 (

@引数1 データ型,

@引数2 データ型,

:

:

:

@引数n データ型

)

AS

※処理

ところで、引数有りの書き方をしたとき引数を指定しないとエラーになります。そんなときは引数にデフォルト値を設定しておくとエラーになりません。

文法(引数有り・デフォルト値付き)

ALTER PROCEDURE ストアドプロシージャ名 (

@引数1 データ型 = デフォルト値,

@引数2 データ型 = デフォルト値,

:

:

:

@引数n データ型 = デフォルト値

)

AS

※処理

ストアドプロシージャの削除

ストアドプロシージャを削除します。

文法

DROP PROCEDURE プロシージャ名

例文

DROP PROCEDURE USP_1

ストアドプロシージャの実行

ストアドプロシージャを実行します。引数が必要なストアドプロシージャに引数を与えない場合、エラーになりますので注意です。EXECUTEはEXECと省略することができます。

文法

EXECUTE ストアドプロシージャ名 引数1,引数2, ... ,引数n

例文1

EXECUTE USP_1

例文2

EXECUTE USP_1 1,'Table_1'

例文2(省略版)

EXEC USP_1 1,'Table_1'

スカラ―変数

スカラ―変数、つまり普通の変数(という呼び方も変ですが)の取り扱いをまとめています。T-SQLでは変数の頭に@を付ける必要があります。また、宣言あるいは初期化していない変数に値を代入することはできません。

宣言

スカラ―変数の宣言方法は以下の通りです。

文法

DECLARE

@変数1 データ型,

@変数2 データ型,

:

:

:

@変数n データ型

例文

DECLARE

@var1 int,

@var2 varvhar(50)

もちろん初期化もできます。

例文

DECLARE

@var1 int = 1,

@var2 varvhar(50) = 'Hello world.'

代入

宣言した変数に代入します。

文法

SET

@変数1 = データ,

@変数2 = データ,

:

:

:

@変数n = データ

例文

SET

@var1 = 1,

@var2 = 'Hello world.'

ただし、ややこしいようですが、他の処理と組み合わせるときはSET文を使いません。

例文

--COUNT関数と組み合わせて、レコード件数を代入

DECLARE

@record_count int

SELECT

@record_count = COUNT(*)

FROM

Table_1

参照

変数を利用します。宣言しただけで初期化や何のデータも代入していない場合はNULLとなっています。

例文

DECLARE

@var int = 100

SELECT

*

FROM

Table_1

WHERE

no = @var

テーブル変数

テーブル変数はテーブルのように機能する変数です。使用感覚としては普通のテーブルを取り扱う場合と殆ど同じでしょう。オプションもテーブルで使えるものが全部使えます。テーブル変数と似た機能を持つものに一時テーブルというものがあります。両者の使い分けですが基本的にはテーブル変数を利用したほうがパフォーマンスの観点から良いようです。逆に、大規模なデータを扱うときなどは一時テーブルのほうが良いようです。

宣言

テーブル変数を宣言します。

文法

DECLARE @テーブル変数名

TABLE (

カラム1名 データ型 オプション

)

例文

DECLARE @table_var1

TABLE (

no int PRIMARY KEY,

name varchar(50)

)

代入

カラムにデータを挿入します。

文法

INSERT INTO @テーブル変数名

VALUES

(

カラム1データ,

カラム2データ,

:

:

:

カラムnデータ

),

(

カラム1データ,

カラム2データ,

:

:

:

カラムnデータ

)

例文

INSERT INTO @table_var1

VALUES

(

101,

'Almond'

),

(

102,

'Bean'

),

(

103,

'Coffee'

)

参照

テーブル変数を参照します。

例文

-- テーブル変数の参照

SELECT

*

FROM

@table_var1

データの削除

テーブル変数に代入されたレコードを削除します。削除するレコードはWHERE句で指定することができます。FROMは省略することができます。

文法

DELETE FROM @テーブル変数名

WHERE

※条件

例文

DELETE FROM @table_var1

WHERE

no > 10 AND

no < 21

例文(省略版)

DELETE @table_var1

WHERE

no > 10 AND

no < 21

トランザクション

トランザクションを宣言します。TRANSACTIONはTRANと省略することができます。

文法

BEGIN TRANSACTION

文法(省略版)

BEGIN TRAN

トランザクション宣言後は適当な場所でコミットするかロールバックする必要があります。こちらもTRANと省略することができます。

コミット

COMMIT TRANSACTION

ロールバック

ROLLBACK TRANSACTION

カーソル

カーソルとは指定したデータから1レコードずつ抜き出す処理です。便利な処理ですが若干動作が重いという欠点があります。安易に多用せず本当に必要かどうか見極める必要があるでしょう。

カーソルの利用は若干手間が掛かります。カーソルの範囲となるデータを指定し、データを収める変数を宣言し、カーソルを開く。カーソル利用中は1行ずつフェッチして、その度に変数にデータを代入する。カーソル利用後はカーソルを閉じて、解放するという手順を踏まなければエラーになる可能性があります。

文法

--カーソルの値を入れる変数を宣言

DECLARE

※変数


--カーソルの宣言(カーソル名の頭に@は付けません)

DECLARE カーソル名

CURSOR FOR

※SELECT文


--カーソルを開く

OPEN カーソル名


--フェッチしてカーソルをデータの1行目に合わせ、変数にデータを代入する

FETCH NEXT FROM カーソル名

INTO

※変数


--カーソル内にフェッチできるデータ残っているならば繰り返し処理する

WHILE @@FETCH_STATUS = 0

BEGIN

※ここに処理を書く

END


--カーソルを閉じる

CLOSE カーソル名


--カーソルを開放する

DEALLOCATE カーソル名

例文

DECLARE

@no int,

@name varchar(50),

@price int

DECLARE cursor_1

CURSOR FOR

SELECT

no,

name,

price

FROM

Table_1

OPEN cursor_1

FETCH NEXT FROM cursor_1

INTO

@no,

@name,

@price

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO Table_2 (

no,

name,

price

)

SELECT

@no,

@name,

@price

END

CLOSE cursor_1

DEALLOCATE cursor_1

繰り返し処理

T-SQLでの繰り返し処理にはWHILE文を使います。他のプログラミング言語で見かけるFOR文は用意されていません。WHILE文から抜けるときはBREAK文を、処理の途中でWHILE文の条件式に戻りたいときはCONTINUE文を利用します。

文法

WHILE 条件式

BEGIN

※処理

END

例文

--1レコードずつSELECT出力する処理です

DECLARE

@no int

@i int = 0

SELECT

@no = COUNT(*)

FROM

Table_1

WHILE @i < @no

BEGIN

@i += 1

SELECT

*

FROM

Table_1

WHERE

no = @i

END

分岐処理

T-SQLでは分岐処理としてIF文が用意されています。ELSE句は省略することができます。

文法

IF 条件式

※条件式が真のときの処理

ELSE

※条件式が偽のときの処理

例外処理

エラー時に特定の処理を実行させます。

文法

BEGIN TRY

※このブロックでエラー発生時にジャンプ

END TRY

BEGIN CATCH

※エラー時にここにジャンプします

END CATCH

RETURN文

実行すると即座に処理を終了し、戻り値を返します。

文法

RETURN 戻り値

例文

RETURN 0