カーソル宣言の位置が問題だ...

T-SQLのカーソル宣言は，PL/SQLと異なり，プログラム中の宣言する位置が動作に影響を与えます。
PL/SQL実装になれた方ですと，T-SQLで以下のようなプログラムを記述する可能性があります。

0001 -- 変数宣言0002 DECLARE @DEL_FLG_ACTIVE INT; -- 未削除フラグ値0003 DECLARE @DEL_FLG_DELETED INT; -- 未削除フラグ値0004 0005 DECLARE @CUSTOMER_ID       DECIMAL(12, 0),0006         @CUSTOMER__NAME    VARCHAR(50),0007         @ADDRESS_ID        DECIMAL(12, 0),0008         @CONTACT_ID        DECIMAL(12, 0),0009         @DEL_FLG           DECIMAL(1, 0),0010         @CREATED_BY        VARCHAR(10),0011         @CREATION_DATE     DATETIME,0012         @LAST_UPDATE_BY    VARCHAR(10),0013         @LAST_UPDATE_DATE  DATETIME0014 0015 DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY 0016 FOR0017     SELECT CUSTOMER_ID,0018            CUSTOMER__NAME,0019            ADDRESS_ID,0020            CONTACT_ID,0021            DEL_FLG,0022            CREATED_BY,0023            CREATION_DATE,0024            LAST_UPDATE_BY,0025            LAST_UPDATE_DATE0026     FROM   BLOGDB.BLOG.M_CUSTOMERS0027     WHERE  DEL_FLG = @DEL_FLG_ACTIVE0028            AND CREATED_BY > DATEADD(DAY, -1, GETDATE())0029 ;0030 0031 -- 初期設定0032 SET @DEL_FLG_ACTIVE = 0;0033 SET @DEL_FLG_DELETED = 1;0034 0035 OPEN my_cursor

しかし，このプログラムは期待通りには，動作しません。なぜでしょうか。なぜなら，カーソル宣言時には，変数@DEL_FLG_ACTIVEに値が代入されていないからです。T-SQLではカーソル内で使用される変数値も含めて，カーソル宣言時にその内容が取得されます。そのため，カーソルのOPEN時に変数が再評価されると言うことがありません。

以上のようなT-SQLのカーソル特性は，カーソル処理のネストを1ストアド内で記述する場合にPL/SQLと大きく異なるコーディングを求めます。論理コードで示すと，以下のように親カーソルループ内に，子カーソルの宣言と子カーソルの破棄を記述する必要が生じます。

DECLARE 親カーソル
OPEN 親カーソル
WHILE 親カーソルループ BEGIN

    DECLARE 子カーソル（当然親カーソルの値が利用される）
    OPEN 子カーソルオープン
    WHILE 子カーソルループ BEGIN
        何らかの処理
    END 子カーソルループ
    CLOSE 子カーソル
    DEALLOCATE 子カーソル

END 親カーソルループ
CLOSE 親カーソル
DEALLOCATE 親カーソル

カーソルのネスト構造というのは，ストアド処理の中では，比較的よく現れるものです。ただでさえ，コード量が増えるT-SQLですが，ループ内で子カーソル宣言や破棄を書いていると，自分が何をしているのか，わからなくなるときがあります。T-SQLの限界といえば，それまでですが，何とかして欲しい部分です。

このようなわけで，T-SQLには引数付きのカーソル宣言というものも存在しません。とても便利な機能なので，T-SQLでも実装して欲しいのですけどね。

クラスタ化テーブルと索引構成表(IOT)

SQL ServerとOracleは等価な機能に対して違う名称を付けていることがあります。読み取り一貫性と行のバージョニングもその一例ですが，SQL Serverのクラスタ化テーブルとOracleの索引構成表もそうです。
別の記事で説明していますが，SQL Serverはテーブル定義時に主キーの設定が事実上必須です。そして，SQL Serverは主キーを定義するとディフォルトでクラスタ化テーブルになります。

Oracleの場合，Enterprise Managerでは，表作成の最初のページで索引構成表にするかどうかを入力します。


PL/SQL Developerでは，organization句の部分にオブションボタンがあります。


このようにOracleでは，あくまでもオプションとしての扱いとなっています。とはいえ，Oracleのマニュアルを見てみるとその有用性を強調しています。後は，開発者次第，ということになるでしょうか。

JavaとC#，OracleとSQL Server
近年のJavaの機能強化とC#の機能強化はお互いの言語のいいとこ取りをしている感があります。そして，OracleとSQL Serverもお互いの長所をそれなりに取り込んでいるという部分があるのだと思います。奇しくもSunを買収したことによりOracleは，Javaを手に入れ，言語でもMicrosoftと双璧となるソリューションを保持するようになっています。これから，両者の言語，DBの発展はどのように刺激しあっていくのか楽しみです。クラウドをキーワードにしながら。

インデックスは，従属オブジェクト?

インデックスはオブジェクトです。しかし，テーブルが存在しなければインデックスだけがあっても無意味です。ということはインデックスというオブジェクトはテーブルの従属オブジェクトということもできそうです。このことはDDLに影響を与えます。OracleとSQL ServerのCreate IndexのDDLについては，ここでは述べません。面白いのはDROPの違いです。
<title>T-SQL:INDEXのDROP</title> <style type="text/css"> </style>

0001 /****** Object:  Index [IX_SAME]    Script Date: 06/14/2010 00:25:34 ******/
0002 IF EXISTS (
0003        SELECT *
0004        FROM   sys.indexes
0005        WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[TableA]')
0006               AND NAME = N'IX_SAME'
0007    )
0008     DROP INDEX [IX_SAME] ON [dbo].[TableA] WITH (ONLINE = OFF)
0009 GO
0010 

SQL Serverでは，DROP INDEX インデックス名 ON テーブル名 というようにテーブル名も指定しないとDROPできません。
Oracleの場合は，単に，DROP INDEX インデックス名 でDROPすることができます。
この違いは，OracleはINDEXがDB中の単一のオブジェクトと見なされているのに対して，SQL Serverでは，あくまでもテーブルの従属オブジェクトであるということです。そのため，Oracleでは，異なるテーブルのインデックス名として同じ名前を付けることはできません。インデックス自体が独立したオブジェクトと見なされているからです。それに対してSQL Serverでは，このインデックスはエラーとなりません。以下のSQLはTableA, TableBを作成し，両テーブルに，インデックスIX_SAMEを定義します。SQL Serverでは正常終了します。
<title>SQL.sql</title> <style type="text/css"> </style>

0001 /****** Object:  Table [dbo].[TableA]    Script Date: 06/14/2010 00:07:30 ******/
0002 SET ANSI_NULLS ON
0003 GO
0004 
0005 SET QUOTED_IDENTIFIER ON
0006 GO
0007 
0008 CREATE TABLE [dbo].[TableA]
0009 (
0010     [AA]  [int] NULL,
0011     [BB]  [nchar](10) NULL
0012 ) ON [PRIMARY]
0013 
0014 GO
0015 
0016 /****** Object:  Index [IX_SAME]    Script Date: 06/14/2010 00:07:14 ******/
0017 CREATE NONCLUSTERED INDEX [IX_SAME] ON [dbo].[TableA] 
0018 ([AA] ASC)WITH (
0019                    PAD_INDEX = OFF,
0020                    STATISTICS_NORECOMPUTE = OFF,
0021                    SORT_IN_TEMPDB = OFF,
0022                    IGNORE_DUP_KEY = OFF,
0023                    DROP_EXISTING = OFF,
0024                    ONLINE = OFF,
0025                    ALLOW_ROW_LOCKS = ON,
0026                    ALLOW_PAGE_LOCKS = ON
0027                ) ON [PRIMARY]
0028 GO
0029 
0030 USE [AdventureWorks]
0031 GO
0032 
0033 /****** Object:  Table [dbo].[TableB]    Script Date: 06/14/2010 00:10:24 ******/
0034 SET ANSI_NULLS ON
0035 GO
0036 
0037 SET QUOTED_IDENTIFIER ON
0038 GO
0039 
0040 CREATE TABLE [dbo].[TableB]
0041 (
0042     [AA]  [int] NULL,
0043     [BB]  [nchar](10) NULL
0044 ) ON [PRIMARY]
0045 
0046 GO
0047 
0048 /****** Object:  Index [IX_SAME]    Script Date: 06/14/2010 00:10:24 ******/
0049 CREATE NONCLUSTERED INDEX [IX_SAME] ON [dbo].[TableB] 
0050 ([AA] ASC)WITH (
0051                    PAD_INDEX = OFF,
0052                    STATISTICS_NORECOMPUTE = OFF,
0053                    SORT_IN_TEMPDB = OFF,
0054                    IGNORE_DUP_KEY = OFF,
0055                    DROP_EXISTING = OFF,
0056                    ONLINE = OFF,
0057                    ALLOW_ROW_LOCKS = ON,
0058                    ALLOW_PAGE_LOCKS = ON
0059                ) ON [PRIMARY]
0060 GO
0061 

片方になれると，もう片方のSQLがおかしく感じられます。不思議です。

識別子の長さ-30バイトの壁

皆さんは，データベースオブジェクトやカラム名をどのように命名されているでしょうか。きっと，それぞれのプロジェクト毎の開発規約に則って命名されているでしょう。システムの規模が大きくなってくるほど，テーブル名やカラム名も複雑で長いものが出てきやすくなります。というわけで，識別子の長さについてです。

SQL Serverの識別子の長さは128文字です。ですので，テーブル名128文字＋カラム名128文字のSQLも作成しようと思えば可能です。あまり見たくないですし，エイリアスを使えば，もう少し短くなります。とはいえ，これは贅沢な悩みです。何しろOracleは30バイトなのです。特に困るのはテーブル名です。テーブル名からインデックスやビュー，バックアップテーブルを派生させることを考えると25，6文字ぐらいが実際に使用できる文字数です。xxxTransactionsなどという名称にすると，Transactionsだけで12文字を使うのでとても窮屈です。

最近の言語に目を向けてみると，識別子の最大長の定めがありません。ですから，省略形にするよりはキャメル表記を使ってクラス，変数を定義していることが一般的になってきました。そうなるとO/RマッピングでDB項目名とJava，.Netの変数名を調和させるのに苦労してしまいます。

確かに二昔ぐらい前のシステム開発環境は，640*400や640*480程度の14インチCRT上で横80桁ぐらいしか表示できませんでした。ですが，今はターミナルでさえ，もっと広大な画面に表示させることができます。システム上の制約がどんどんなくなってきているのですから，Oracleもこのあたりの拡張をお願いしたいところです。何しろあのExcelでさえ，2007からはワークシートサイズを行，列共に拡大したのですから。

トランザクションとコミット

トランザクション管理はデータベースの基本機能ですので，当然実装されていることは言うまでもありません。しかしその振る舞い方にはデータベース毎の特徴があり，注意が必要です。

BEGIN TRANSACTION
SQL ServerにはBEGIN TRANSACTION文がありますが，PL/SQLには該当するものがありません。これは，両者の違いを端的に表しています。つまり，SQL Serverにおいては，トランザクションで囲んでいない部分のSQL（DML)は，即時コミットされるのに対して，Oracleは常にトランザクションモードで動作していると言うことです。ディフォルトの動作が異なっているということですね。これは，Oracle特有の読み取り一貫性の副次的恩恵とも言えます。ただ，この結果，SQL ServerとOracleではトランザクションの扱い方が異なっています。なお，SQL Serverでもデータベースオプションの設定によりOracleと同等の読み取り一貫性を確保できるようになっています。SQL Serverでは，行のバージョニングと呼んでいます。
因みに，この両者の違いをよく示しているのは，SQLPLUSとSQL Server Management Studioにおける動作の違いです。SQLPLUSでコミットせずにSQLPLUSを終了した場合，DMLはコミットされます。一方，SQL Server Management StudioでBEGIN TRANSACTIONを実行後に行ったDMLをコミットせずに終了した場合，ロールバックされます。SQL Server Management Studioでは，そのために，コミット or ロールバックの確認ダイアログが表示されるようになっています。

BEGIN TRANSACTIONのネスト
SQL ServerにはBEGIN TRANSACTION文があるため，これが連続して実行された場合，コミット動作はどうなるのか，という問題が発生します。BEGIN TRANSACTIONが存在しないOracleでは，発生し得ない問題です。答えは，一番外側のBEGIN TRANSACTIONに対応するコミットがトランザクションをコミットすることになります。以下のようなSQLがあったとします。
<title>SQL Serverのトランザクション</title> <style type="text/css"> </style>

0001     BEGIN TRANSACTION
0002         UPDATE TableA ...
0003 
0004         BEGIN TRANSACTION
0005             UPDATE TableB ...
0006 
0007         COMMIT;
0008 
0009     COMMIT;

上の例では，1行目と4行目にBEGIN TRANSACTIONがあります。それぞれに対応するCOMMITは7行目と9行目です。この場合，コミットが有効になるのは，9行目のコミットを実行した時点となります。またもし，9行目がROLLBACK TRANSACTIONであったとすれば，TableA，TableB双方のUPDATEがロールバックされます。

トランザクションをネストするなどというバグはあり得ない，と思いますか。ところが，そうはいかないのです。このトランザクションのネストは，ストアドプロシージャ越しや，ADO.Netとストアドプロシージャの間でも発生します。ですので，以下のようなことが起こりえます。
(1)T-SQLのストアドuspXxxA, uspXxxBをそれそれの内部でトランザクション管理をする処理として作成。
(2)その後，この2つのストアドを連続して実行するプログラムを.Netで作成。この際SqlConnectionとSqlTransactionでトランザクション管理を実施
これで，ストアドで定義していたトランザクション管理は無効になってしまいます。ですので，T-SQLストアドをADO.Net経由で使用する場合には注意が必要です。

ADO.Netのトランザクション管理
ここまでで，T-SQLのBEGIN TRANSACTIONはネストしてもエラーとはならないと述べましたが，ADO.Net上のトランザクション管理はネストできません。つまり，以下のようなコードを記述するとエラーが発生します。 <title>ADO.Net</title> <style type="text/css"> </style>

0001         Dim tranSSFirst As SqlTransaction
0002         Dim tranSSSecond As SqlTransaction
0003 
0004         tranSSFirst = conSS.BeginTransaction
0005         tranSSSecond = conSS.BeginTransaction
0006 
0007         Try
0008             ' DMLの実行
0009 
0010             tranSSSecond.Commit()
0011             tranSSFirst.Commit()
0012         Catch ex As Exception
0013             MessageBox.Show(ex.Message.ToString())
0014             tranSSSecond.Rollback()
0015             tranSSFirst.Rollback()
0016         End Try

同一DBに対してこのような2重トランザクションを行うことはあり得ませんが，try catch構文によるRollback処理の実装漏れがあると，結果として同様の状態になります。この場合，エラーが発生するのは，本来のバグであるRollback漏れのコード部分ではなく，次回のBeginTransaction実行時となります。そのため，エラーの発生原因の特定に手間取る場合が多いようです。

ADO.Net経由でのOracle接続とトランザクション管理
Oracle Data Provider for .NETもSystem.Data.Common.DbTransactionから派生しています。ですので，SQL ServerをADO.Net経由で操作するのと同じことが発生します。OCIダイレクト接続の場合には，不必要だったBEGIN TRANSACTIONが，ADO.Netを使用した場合には必要になるということです。