T-SQLプログラミングの構成方法

これらのコンポーネントを使用すると、1つのステートメントでは完了できないさまざまな操作を処理できます。ユーザーは、複数のコマンドをグループ化して、次のいずれかの方法を使用できます。

• バッチ：アプリケーションからサーバーにクエリユニットとして送信される1つ以上のコマンドのリスト
• ストアドプロシージャ：ストアドプロシージャは、サーバー上で事前にコンパイルされ、事前定義されたT-SQLコマンドのコレクションです。
• トリガー：トリガーは、ユーザーがテーブルでINSERT、UPDATE、DELETEなどのイベントを処理するときに実行されるストアドプロシージャの一種です。
• スクリプト：ファイルに保存され、SSMSまたはsqlcmdを介した入力に使用される一連のT-SQLステートメント
• 変数：ユーザーがT-SQLステートメントの入力として使用できる変数
• フロー制御：T-SQLで条件付き構造を作成するためのdideefuフローを制御します
• エラー処理：エラーを制御し、エラーが発生したときにユーザーに情報を提供するために使用されるエラー処理メカニズム。

T-SQLバッチ

T-SQ1は、アプリケーションからサーバーにクエリユニットとして送信される1つ以上のステートメントのグループです。 SQL Serverは、バッチを実行プランと呼ばれる単一の実行単位にコンパイルします。実行プランでは、SQLはステートメントを1つずつ実行します。各ステートメントは「;」で終わります。この条件はオプションですが、「;」なしでステートメントを終了するための基礎は非推奨であり、新しいバージョンのSQLServerで削除される可能性があります。したがって、dauasを使用してください。ステートメントを終了することを常にお勧めします。

構文エラーなどのコンパイルエラーが発生した場合、バッチは実行されません。

制約違反やアルゴリズムロジックなどの実行時エラーにより、次のいずれかの状況が発生します。

• ほとんどのランタイムエラーは、バッチ内の現在のコマンドと次のステートメントを停止します
• 制約として指定された実行時エラーは、ステートメントが存在し、ステートメントの残りの部分がまだ実行されている場合にのみ使用されます。

ランタイムエラーが発生する前に実行されたSQLステートメントは影響を受けません。唯一の例外は、バッチがトランザクションであり、ロールバックがあるときにトランザクションでエラーが発生する場合です。

たとえば、バッチに10個のステートメントがあり、6個のステートメントにエラーがあるとすると、バッチ内の残りのステートメントは実行されません。バッチがコンパイルされ、3番目のコマンドが失敗した場合、最初の2つのステートメントが実行されます。

バッチを使用するときに従うべきいくつかのルール：

• CREATE FUNCTION、CREATE DEFAULT、CREATE RULE、CREATE TRIGGER、CREATE PROCEDURE、CREATE VIEW、またはCREATE SCHEMAは、バッチ内の別のステートメントと連結することはできません。 CREATEステートメントはバッチを開始し、バッチ内の残りのすべてのステートメントは、CREATEステートメント定義の一部と見なされます。
• テーブルに変更は加えられず、新しい列は元の状態に戻って参照されました
• バッチの最初のステートメントがexecuteステートメントである場合、excuteコマンドは必要ありません。これは、executeステートメントがバッチの最初のステートメントに存在しない場合にのみ必要です。

バッチ例：

USE AdventureWorks2019; GO GREATE VIEW dbo.vProduct AS SELECT ProductNumber, Name FROM Production.Product; GO SELECT * FROM dbo.vProduct; GO

例2は、トランザクションで複数のBATCHを組み合わせるために使用されます。

BEGIN TRANSACTION GO USE AdventureWorks2019; GO CREATE TABLE Company( Id_Num int IDENTITY(1,1), Company_Name nvarchar(100)) GO INSERT Company (Company_Names) ('Company 1') INSERT Company (Company_Names) ('Company 2') INSERT Company (Company_Names) ('Company 3') GO SELECT Id_Num, Company_Name FROM dbo.Company ORDER BY Company_Name ASC; GO COMMIT; GO

上記のコードでは、複数のバッチが1つのトランザクションに結合されています。 BEGIN TRANSACTIONステートメントとCOMMITステートメントは、トランザクションの開始と終了を定義します。 CREATE TABLE、BEGIN TRANSACTION、SELECT、COMMIT、およびUSEは、単一ステートメントのバッチです。 INSERTステートメントはすべて1つのバッチにパッケージ化されています。

T-SQLの変数（変数）

SQL Serverでは、変数を宣言してパラメーターとして使用したり、アプリケーションから渡されたパラメーターとして動的クエリを記述したりできます。 SQL Serverは、変数に値を宣言して割り当てるためのステートメントを提供します。

宣言する

変数は、バッチ本体のDECLAREステートメントを介して初期化されます。これらの変数には、SELECTまたはSETステートメントを使用して値が割り当てられます。宣言時にユーザーが値を指定しなかった場合、変数は値NULLで初期化されます。

構文：

DECLARE {(@local_variable [AS] data_type) } [= value]}

例えば：

USE AdventureWorks2019; GO DECLARE @find varchar(30) = 'Man%'; SELECT p.LastName, p.FirstName, ph.PhoneNumber FROM Person.Person AS p JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID WHERE LastName LIKE @find;

設定

SETステートメントは、DECLAREで初期化されたローカル変数に値を割り当てて、変数の値を決定します。

構文：

SET {@local_variable = {expression}} {@local_variable {+= | -= | *= | /= | %= | &= | ^= | |=}}

オペレーターの説明：

• +=等しいものを追加して割り当てる
• -=減算して等しいを割り当てる
• *=乗算して等しいを割り当てる
• /=等しいものを分割して割り当てる
• ％=余りで割り、等しい値を割り当てます
• ＆=ビット単位のANDで、後で割り当てます
• ^=ビット単位のXORと後で割り当てる
• | =ビットごとのORを取り、後で割り当てます。

例えば：

DECLARE @myvar char(20); SET @myvar = 'Hello world';

選択する

SELECTステートメントは、式を取得するためにDECLAREステートメントで指定されたローカル変数を指定します

構文：

SELECT {@local_variable {= | += | -= | *= | /= | %= | &= | ^= | != } expresssion}

例えば：

USE AdventureWorks2019 GO DECLARE @var1 nvarchar(30); SELECT @var1 = 'company hello'; SELECT @var1 = Name FROM Sales.Store WHERE BusinessEntityID = 10; SELECT @var1 AS 'Company name';

SETステートメントとSELECTステートメントは似ていますが、わずかな違いがあります。

• SETを使用して一度に割り当てることができる変数は1つだけですが、SELECTは同時に複数の変数に値を割り当てることができます。
• SETは、クエリから1つの基本的なデータ型の割り当てのみを割り当てることができます。データが行と列のセットである場合はエラーになります。ただし、SELECTは複数の戻り値を変数に割り当てることができます。

同義語

シノニムは、次の目的に役立つデータベースオブジェクトです。

• これは、リモートサーバーまたはローカルサーバーに存在する可能性のある、ベースオブジェクトとも呼ばれるデータベースオブジェクトごとに異なる名前を提案します。
• これは、ベースオブジェクトの場所と名前に加えられた変更からクライアントアプリケーションを保護する抽象化レイヤーを表します。

たとえば、server1という名前のサーバーにあるAdventureWorks2019のDepartmentテーブルについて考えてみます。 server2という名前のサーバー2からテーブルを参照するには、クライアントは4つの要素名を使用する必要があります。
server1.AdventureWorks2019.HumanResources.Department

テーブルの場所が別のサーバーなどに変更された場合、クライアントアプリケーションはこの変更の影響を受けない必要があります。これらの問題の両方を解決するために、ユーザーはServer1のDepartmentテーブルの同義語DeptEmpTableowrServer2を作成できます。
そこから、クライアントはDeptEmpTableという名前の1つを使用するだけで、 Departmentテーブルを参照できます。

同様に、 Departmentテーブルの場所が変更された場合、ユーザーは同義語DeptEmpTableを編集して、 Departmentテーブルの新しい場所を指すようにする必要があります。
また、ALTER Synonymステートメントがない場合は、シノニムを削除し、古い名前で再初期化する必要がありますが、 Departmentの新しい場所を指す必要があります。

ユーザーがシノニムを作成できるデータベースオブジェクトのリスト：

• 拡張ストアドプロシージャ
• SQLテーブル値関数
• SQLストアドプロシージャ
• テーブル（ユーザー定義）
• レプリケーション-フィルター-手順
• SQLスカラー関数
• SQLインラインテーブル値関数
• 意見

同義語とスキーマ

ユーザーがシノニムを作成したいと考えており、ユーザーが所有していないデフォルトのスキーマを持っているとします。この場合、彼らは実際に所有しているスキーマで同義語名を識別できる可能性があります。たとえば、ユーザーはスキーマリソースを所有していますが、マテリアルはデフォルトのスキーマです。ユーザーがシノニムを作成する場合は、シノニムの前にスキーマリソースを付ける必要があります。

シノニムに権限を割り当てる

シノニムに権限を付与できるのは、db_ownerまたはdb_ddladminロールのメンバーまたはシノニム所有者のみです。ユーザーは、シノニムのすべてまたは一部の権限を拒否、付与、または取り消すことができます。

権限のリスト：

• 消去
• 入れる
• 所有権を得る
• 定義を表示
• コントロール
• 実行する
• 選択する
• アップデート

同義語の操作

ユーザーは、SELSERver2019の同義語またはSSMSのT-SQLを使用します。オブジェクトエクスプローラーを使用してSSMSでシノニムを作成するには、次の手順を実行します。

T-SQLを使用してシノニムを作成するには、次の構文を使用します。

CREATE SYNONYM [schema_name_1.] synonym_name FOR <object> <object> ::= { server_name.[database_name].[schema_name_2].|database_name.[schema_name_2].|schema_name_2.] object_name}

例えば：

USE AdventureWorks2019; GO CREATE SYNONYM MyAddressType FOR AdventureWorks2019.Person.AddressType; GO

プログラムフローステートメント

T-SQLでサポートされているさまざまな種類のプログラムフローステートメントは次のとおりです。

T-SQLの制御フロー言語

制御フローは、SQLステートメント、コマンドのブロック、ユーザー定義関数、およびストアドプロシージャのフローを示します。

デフォルトでは、T-SQLステートメントは順番に実行されます。プログラムフローを使用すると、特定の相互に関連する状況でステートメントを実行でき、プログラミング言語と同様の構造を使用してコード間で生成されます。

Control-Of-Flow言語キーワードのリスト：

• 戻る
• 投げる
• 試して…キャッチ
• を待つ
• その間
• 開始…終了
• 壊す
• TIẾPTỤC
• GOTOラベル
• IF … ELSE

開始…終了

構文：

BEGIN { sql_statement | statement_block } END

例えば

USE AdventureWorks2019; GO BEGIN TRANSACTION; GO IF @@TRANCOUNT = 0 BEGIN SELECT FristName, MiddleName FROM Person.Person WHERE LastName = 'Andy'; ROLLBACK TRANSACTION; PRINT N'Rolling back the transaction two times would cause an error.'; END; ROLLBACK TRANSACTION; PRINT N'Rolled back the transcaction'; GO

IF … ELSE

TRansact-SQLはIFキーワードに依存しており、条件が満たされ、trueを返した場合にのみ条件が実行されます。 ELSEキーワードは、IF条件が満たされない場合に実行され、FALSEを返すT-SQlステートメントではオプションです。

構文：

IF Boolean_expression {sql_statement | statement_block} [ELSE sql_statement | statement_block } ]

例えば：

USE AdventureWorks2019; GO DECLARE @ListPrice money; SET @ListPrice = (SELECT MAX(p.ListPrice) FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] = 'Mountain Bikes'); PRINT @ListPrice IF @ListPrice < 3000 PRINT 'All the products in this category can be purchased for an amount less than 3000' ELSE PRINT 'The prices for some products in this category exceed 3000'

その間

WHILEステートメントは、ブロック実行を繰り返すための条件を定義します。指定された条件がtrueを返すまで、ステートメントが繰り返し実行されます。 WHILEループの実行ステートメントは、BREAKまたはCONTINUE。キーワードを使用して制御できます。

構文：

WHILE Boolean_expression {sql_statement | statement_block | BREAK | CONTINUE}

例えば：

DECLARE @flag int SET @flag = 10 WHILE (@flag <= 95) BEGIN IF @flag % 2 = 0 PRINT @flag SET @flag = @flag+ 1 CONTINUE; END; GO

場合

構文：

 SELECT column_list, CASE column_name WHEN value1 then display1 WHEN value2 then display2 ... ELSE display END [AS alias] FROM relative_table [WHERE condition];

例えば

 SELECT studentid, studentname, GENDER = CASE gender WHEN 1 then 'Male' WHEN 0 then 'Female' ELSE 'Unknow' END FROM Student;