Rakuten

  • 楽天ダウンロード

    楽天市場

おすすめ本

天気予報

最近のトラックバック

  • event (夢茶爺&苦茶爺のPC奮戦備忘録)

カウンタ

VC

admax

« 2012年7月 | メイン | 2014年9月 »

2013年2月

2013年2月27日 (水)

SQL-Serverの自動採番(IDENTITY値)の取得・リセット

システムに必要なテーブルで、自動的に番号を振っていくものが必要なときがあります。 たとえば、各種の伝票データの伝票番号の様なものです。

プログラム処理上、データを登録した直後に、自動採番された値を取得し何かに使いたいことがよくあります。
SQL-Serverでは現在の自動採番の値を取得する方法が用意されています。

取敢えず自動採番を行える簡単なテーブルの例を示します。(前回ストアド・ファンクションで使用したテーブルに細工します。)



まず、テストテーブルBに1件データを挿入します。 「ID」は自動採番の設定なので登録する値は設定しません。
■自動採番された値を取得
この直後に、設定された「ID」値を取得するには以下のSELECT文を実行します。
結果として「1」という値が返されます。

■自動採番された値をリセット
デバッグ途中でテーブルのデータを全て削除して、さらに自動採番が「1」からにしたい場合はよくあります。 そのときに以下の命令をクエリアナライザ等で実行します。
自動採番の値を「0」にする命令ですが、実際INSERT実行時には+1された値が「ID」に設定されます。

■関連記事
VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
ADO.NET での DataReader の入れ子エラー
ADO.NET を使用した SQL-Server へのアクセス・クラス
SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
SQL-Serverのプロシージャ生成のバッチ実行
SQL-Serverの関数・プロシージャのVB.NETでの実行


楽天市場

2013年2月14日 (木)

SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない

当然といえばそうなのかもしれませんが、SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない。
TRY・・・CATCHも記述できないようです。関数は戻り値のみを返すもので、テーブル等に変更を与えてはいけないのでしょう。

さて、取敢えずストアド・ファンクションの簡単な例を示します。

前回使用したテストテーブルを利用して、ストアド・ファンクションを作成します。テーブルの構造は以下の感じです。
■スカラ値関数

テストテーブルAを利用した簡単な値を返すストアド・関数です。
与えられた検索用IDでテーブルAの「DATA1」カラムの内容を返す簡単な関数です。 SELECT文での実行は以下のようにします。
======================================
SELECT [dbo].[FuncTest2](1)
======================================

■テーブル値関数その1(インラインテーブル値)

テストテーブルAを利用した簡単な値を返すストアド・関数です。
SELECT文のFROM句で実行しますので、以下のようになります。
======================================
SELECT * FROM [dbo].[FuncTest3](1)
======================================

■テーブル値関数その2(複数の行を返すテーブル値)

テストテーブルAを利用した複数の行を返すストアド・関数です。
指定されたID以降のデータを全て返す様な動作を行います。
RETURNS句で指定されたTABLEにINSERTしていくことで、結果をテーブルの値として返します。
INSERTで追加してやればいろんなことができると思います。上の例ではコメント行になっていますが、 強制的にID「0」を作成することも可能です。
この関数の実行も「テーブル値関数その1」で行った方法と同様です。


■スカラ値関数にUPDATE文を記述
SQL実行結果として以下のエラーメッセージが表示されます。
============================================================
メッセージ 443、レベル 16、状態 15、プロシージャ FuncTest2、行 8
副作用のある演算子または時間に依存する演算子を関数内の 'UPDATE' で使用することはできません。
============================================================

TRY、CATCHを入れてもエラーが表示され使用出来ないようです。
SQL実行結果として以下のエラーメッセージが表示されます。
============================================================
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 6
副作用のある演算子または時間に依存する演算子を関数内の 'BEGIN TRY' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 9
副作用のある演算子または時間に依存する演算子を関数内の 'END TRY' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 10
副作用のある演算子または時間に依存する演算子を関数内の 'BEGIN CATCH' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 11
副作用のある演算子または時間に依存する演算子を関数内の 'END CATCH' で使用することはできません。
============================================================


副作用のある処理をファンクションのなかでは記述できないので、 どうしても処理したいのであれば、ストアド・プロシージャとして作成するしかないようです。
そこで、結果として何かを返したいのであれば、引数に出力属性のものを持つしかありません。

また、プロシージャの中からファンクションを実行することはできますが、ファンクションの中から プロシージャを実行することはできないようです。
ファンクションのコールが入れ子になっていて、深いところでUPDATEなどを行いたい場合、 全てのファンクションをプロシージャにしないといけなくなってきます。
このあたりは、ファンクション、プロシージャの切り分けをしっかりしておく必要がありそうです。

■関連記事
VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
ADO.NET での DataReader の入れ子エラー
ADO.NET を使用した SQL-Server へのアクセス・クラス
SQL-Serverの自動採番(IDENTITY値)の取得・リセット
SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
SQL-Serverのプロシージャ生成のバッチ実行
SQL-Serverの関数・プロシージャのVB.NETでの実行


楽天市場

2013年2月12日 (火)

SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート

SQL-Serverのテーブルの内容を簡単にインポート・エキスポートできる、ユーティリティ(BCP)の忘備録です。

BCPはコマンドラインで使用するEXEファイルで、MSSQLをインストールした時点でユーティリティとして "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"の様なディレクトリに存在するはずです。
パスも通っているはずですから、DOS窓から「BCP」を実行すれば動作するはずです。
(引数無しで実行するとヘルプが表示されます。以下を参照) 各種の引数があって、いろいろな使い方ができますが、私が基本的に使っている方法を記します。
尚、実行はMSSQLがインストールされているサーバ上で実行しています。


■テーブルをテキストファイルに出力する

テスト用のデータベースである「TEST」と、そのDB内のテスト用テーブル「TABLE_A」の内容全てを テキストファイル「table_a.txt」に出力する方法です。
出力結果はテーブルのカラムの区切り記号としてタブ文字が使われます。また、文字コードはShift-JISです。
(尚、実行はCドライブのルートで実行しています。)
最後の引数の「-T」は、Windows認証の指定ですが、SQL Server認証の場合は「-T」の代わりに「-U」「-P」を用います。
================================================================================
-U [ユーザ名] -P [パスワード]
================================================================================


■SQLの結果をテキストファイルに出力する

上記の説明で使用したテーブル「TABLE_A」を対象としたSQL文の結果をテキストファイル「table_b.txt」に出力する方法です。
最初のコマンドの引数と異なるところは、SQL文を指定するところと、処理区分を「queryout」とすることです。 出力結果は、「table_a.txt」とほぼ同じものが出力されます。


■テーブルにテキストファイルをインポートする

上記の説明で使用したテーブル「TABLE_A」にテキストファイル「table_c.txt」の内容をインポートする方法です。
テキストファイルの内容は、インポートされるテーブルの構造と同じである必要があります。
また、キーの重複があればエラーが発生し、全てのインポート処理は破棄されます。

■関連記事
VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
ADO.NET での DataReader の入れ子エラー
ADO.NET を使用した SQL-Server へのアクセス・クラス
SQL-Serverの自動採番(IDENTITY値)の取得・リセット
SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
SQL-Serverのプロシージャ生成のバッチ実行
SQL-Serverの関数・プロシージャのVB.NETでの実行


楽天市場