第 3 章 「データの取得と修正」～ MCDBA をめざそう!! MCP 70-229 SQL Server 2000 開発編 ～
NEC
Eラーニング事業部
鈴木 智行
2002 年 7 月 1 日
目次
.gif){kind=icon}
1. 前回の復習問題の解答と解説
2. データのインポートとエクスポート
3. 異種データの処理
4. Transact-SQL によるデータの取得および修正
5. カーソルと Transact-SQL による結果セットの管理
6. XML 形式でのデータの拡張
7.復習問題
1. 前回の復習問題の解答と解説
問 1 の解答 ア、オ、カ、キ
（解説） ファイルグループはテーブル作成時に FILEGROUP 句で新規作成できます。またファイル拡張に関しては、単位を省略すれば MB､FILEGROWTH 句自体を省略すれば 10 % が拡張値となります。デフォルトファイルグループは PRIMARY ファイルグループであり、変更したい場合は ALTER DATABASE ステートメントで変更します。
問 2 の解答 エ
表名: 顧客
顧客コード |
顧客名 |
所在地 |
電話番号 |
|---|
（解説） 顧客表では顧客コードを主キー （デフォルト指定） として設定していますから、既にクラスタ化インデックスが作成済です。したがってクラスタ化インデックスはテーブルに１つしか作成できません。そのため、新たにクラスタ化インデックスを作成できませんし、自動的に非クラスタ化インデックスが作成されることもありません。またデータが未入力であるため一意なインデックスを作成することができます。以上を満たすのはエとなります。
問 3 の解答 エ
（解説） レプリケーションは双方のサーバーに重複データを持つため、冗長性があります。したがって、この他の要件でも特に問題がない分散パーティションニングビューが最適でしょう。Server 1 に東京に関するデータ、Server 2 に大阪に関するデータを格納して分散パーティションニングビューを採用し、INSTEAD OF トリガで双方のデータを更新します。
2. データのインポートとエクスポート
SQL Server 2000 ではデータのインポートとエクスポートに BCP、BULK INSERT、DTS を使用できます。
2-1 BCP
BCP はコマンドユーティリティであり、SQL Server 2000 とデータファイルの間で、大量のデータを転送するために使用されます。
（例） 販売データベースの受注テーブルのデータを Orders.txt にエクスポートする
bcp 販売.dbo.受注 out C:\Orders.txt -Ulocaluser –Plocalpass
BCP は上記の例の他にもたくさんのオプションがあるので確認してください。
2.2 BULK INSERT
BULK INSERT は Transact-SQL ステートメントであり、BCP と同等の機能を提供します。しかし BCP と異なり、データファイルから SQL Server へのインポートのみ提供し、SQL Server からデータファイルへのエクスポートは提供しません。
（例） ','をフィールド終端文字、'\n'を行終端文字として Orders.txt ファイルから販売データベースの受注テーブルにデータをインポートする
BULK INSERT 販売.dbo.受注 FROM 'C:\Orders.txt' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
BULK INSERT も BCP 同様、この他にたくさんのオプションがあるので確認してください。
2.3 DTS
DTS（データ変換サービス）では、単一または複数の異種の変換元から単一または複数の異種の変換先へのデータの抽出、変換、および統合を行うための一連のツールが提供されています。転送／変換だけでなく、タスクやワークフローを組み込むことができるため、BCP や BULK INSERT で実現できない処理を構築できます（画面 2-3-1）。また DTS パッケージとして作成すれば、繰り返して利用したり、スケジューリングを行って自動化することもできます。
.gif)
図 2-3-1 ワークフローの例
1対1 の簡単なデータ変換を行う DTS パッケージを作成するには DTS インポートエクスポートウィザードを使用します。多対多の複雑な処理を伴う DTS パッケージを作成するには DTS デザイナを使用します。もちろん簡単なデータ変換にも DTS デザイナは使用できます。また SQL Server 2000 からは DTS パッケージを Visual Basic プログラムで保存できるので、Visual Studio などの開発環境でカスタマイズすることも可能です。
3. 異種データの処理
SQL Server 2000 では分散クエリを使用すると、ローカルやリモートコンピュータに格納されている異種データを処理できます。異種データとしては、OLE DB プロバイダや ODBC ドライバによってアクセスできるリレーショナルまたは非リレーショナルデータをサポートします。
3-1 リンクサーバー
異種データに繰り返しアクセスする場合は、リンクサーバーを設定します。リンクサーバーとは OLE DB データソースへのアクセスに対して定義された仮想サーバーです。ローカルの SQL Serer にあらかじめ情報登録しておくと、簡単な指定で OLE DB データソースにアクセスできます。そのためにはまず、sp_addlinkedserver システムストアドプロシージャでリンクサーバーを定義します。
（例）ACCESS の MDB ファイルを登録する
EXEC sp_addlinkedserver
@server = N'ACCESS商品管理',
@provider = NMicrosoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'D:\商品管理.mdb'
（例）リモートの SQL Server を登録する
EXEC sp_addlinkedserver
N'RemoteSQL',
N'SQL Server'
ユーザーがローカルの SQL Serer にログインして、リンク サーバー上のテーブルにアクセスする分散クエリを実行する場合、ローカルの SQL Serer は、目的のテーブルにアクセスするために、ユーザーの代理でリンク サーバーにログインする必要があります。sp_addlinkedsrvlogin システムストアドプロシージャ を使用して、ローカルの SQL Serer がリンク サーバーへのログインに使用するログイン資格情報を指定します。
(例) ローカル SQL Serer のユーザー localuser をリンクサーバーのユーザーremoteuserにマップする
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'RemoteSQL'
@useself = 'false'
@locallogin = N'localuser'
@rmtuser = N'remoteuser'
@rmtpassword = N'remotepass'
リンクサーバーを使用してクエリを実行する場合、リンクサーバー上のオブジェクトは完全修飾された 4 部構成の名前を使って参照します。
(例) リンクサーバー RemoteSQL の販売データベースの受注テーブルを検索する
Select * from RemoteSQL.販売.dbo.受注
3-2 OPENROWSET 関数
異種データに繰り返しアクセスする予定がない場合は、リンクサーバーを設定しなくてもアクセス可能です。このような場合には OPENROWSET 関数を使用します。
(例) リモートの SQLServer である RemoteSQL の販売データベースの受注テーブルを検索する
Select r.* from OPENROWSET(N'SQL Server', N'RemoteSQL'; N'remoteuser'; N'remotepass',select * from 販売.dbo.受注) As r
3-3 OPENQUERY 関数
OPENQUERY 関数を使用すると、クエリをリンクサーバー上で処理し、結果をローカルの SQL Server に返すことができます。
(例) リンクサーバー RemoteSQL の販売データベースの受注テーブルを検索する
Select * from OPENQUERY(RemoteSQL,'select * from 販売.dbo.受注')
4. Transact-SQL によるデータの取得および修正
Transact-SQL ではデータの取得や修正を行うために、いろいろなステートメントと句をサポートします。ここではその一部を紹介します。
4-1 データのフィルタ
データのフィルタ設定としては DISTINCT キーワード、TOP キーワード、HAVING 句などが挙げられます（表4-1-1）
キーワードおよび句 |
説明 |
|---|---|
DISTINCT |
SELECT ステートメントの結果から重複する行を除去します |
TOP |
SELECT ステートメントの結果セットで返す行を制限します、PERCENT を指定すれば返される行数をパーセントで制限しますまた ORDER BY 句とともに WITH TIES 句を指定すれ TOP 句の最終行と同じ値を持つ行を全て結果セットに追加します |
HAVING |
グルーピングによって集計された結果に対し条件を設定します WHERE 句はグルーピング前のデータに対し条件を設定します |
表4-1-1 代表的なデータのフィルタ設定
4-2 グループ化
行をグループにまとめるためには GROUP BY 句を使用します。GROUP BY 句で指定されている全ての列（導出列を除く）が選択リストに含まれている必要があります。また GROUP BY 句を指定しても並べ替え順序は保証されません。結果を並べ替えるためには ORDER BY 句を指定する必要があります。
(例) 営業成績(製品単価*売上個数)が良い営業マンを上位5名表示する
SELECT TOP 5 社員名,SUM (製品単価*売上個数) as 売上金額 FROM 売上 GROUP BY 社員名 ORDER BY 売上金額 desc
集計関数は GROUP BY 句とよく一緒に使用されます（表4-2-1）。
集計関数 |
説明 |
|---|---|
AVG |
数値式の値の平均 |
COUNT |
式の値の数 |
COUNT(*) |
選択された行の数（NULLを持つ行もカウントする） |
MAX |
式の最大値 |
MIN |
式の最小値 |
SUM |
数値式の値の合計 |
表 4-2-1 集計関数（一部）
結果セットで詳細値と集計値を作成したい場合は ROLL UP 演算子または CUBE 演算子を使用します（表4-2-2）
演算子 |
説明 |
|---|---|
ROLL UP |
選択された列の階層集計を示す結果セットを返します |
CUBE |
選択された列の値の全ての組み合わせの集計を示す結果セットを返します |
表 4-2-2 ROLL UP 演算子と CUBE 演算子
（例）社員 ID ごとの売上金額 (製品単価*売上個数) と総計を表示する
（CUBE 演算子の場合は +製品 ID ごとの売上金額が表示される）
SELECT 社員 ID,製品 ID,SUM(製品単価*売上個数) as 売上金額 FROM 売上 GROUP BY 社員 ID,製品ID WITH ROLLUP --または WITH CUBE ORDER BY 社員 ID,製品ID
4-3 結合とサブクエリ
結合によって複数のテーブルから、各テーブルの行と列を含む結果セットを生成することができます。結合には内部結合、外部結合、クロス結合の3つあります（表4-3-1）。複数のテーブルだけでなく、自分自身と結合することもできこれを自己結合といいます。自己結合する場合には、複数のテーブルとしてみなすために、テーブルの別名が必要です。
種類 |
キーワード |
説明 |
|---|---|---|
内部結合 |
INNER JOIN（デフォルト） |
結合条件に一致する行を結合します |
外部結合 |
LEFT OUTER JOIN |
結合条件に一致する行に加え、結合条件に一致しない行（LEFT OUTER JOIN の場合は FROM 句で最初に名前を指定したテーブルの全ての行を、RIGHT OUTER JOIN の場合は FROM 句で 2 番目に名前を指定したテーブルの全ての行）も結合します。結合条件に一致しない行は NULL が表示されます。 |
クロス結合 |
CROSS JOIN |
全ての行の組み合わせを指定します。結合条件は共通列は必要ありません |
結合はサブクエリでも表現できます。サブクエリとはネストされているSELECTステートメントのことをいいます。
（例）内部結合を使用
SELECT DISTINCT c.顧客名,c.電話番号 FROM 顧客 as c INNER JOIN 受注 as j on c.顧客コード = j.顧客コード where j.受注日付 = '2002/06/28'
（例）サブクエリを使用（上記の結合と同じ結果が得られる）
SELECT c.顧客名,c.電話番号FROM顧客 as c WHERE EXISTS (SELECT * FROM受注 as j WHERE c.顧客コード = j.顧客コード and j.受注日付 = '2002/06/28')
4-4 データの修正
4-4-1 複数行に対する修正と削除
サブクエリを利用すれば、他のテーブルを基にしてデータの削除や修正ができます。
（例）製品 ID ごとの本日の売上を集計する
UPDATE 製品売上集計 SET 本日売上 = ( SELECT SUM(製品単価*売上個数) FROM 売上 WHERE 製品売上集計.製品ID = 売上.製品ID and 売上.受注日付 = '2002/06/28')
4-4-2 複数行の挿入
INSERT ..SELECT ステートメントは SELECT ステートメントの結果セットを挿入できます。
（例）社員IDごとの売上金額(製品単価*売上個数)と総計を社員売上テーブルに追加する。
INSERT 社員売上テーブル SELECT社員ID,製品ID,SUM(製品単価*売上個数) FROM 売上 GROUP BY 社員ID,製品ID WITH ROLLUP ORDER BY社員ID,製品ID
5. カーソルと Transact-SQL による結果セットの管理
Transact-SQL の SELECT ステートメントでは、WHERE 句で指定した条件を満たす全ての行（結果セット）を返します。この結果セットを行単位で扱うメカニズムをカーソルといいます。
5-1 Transact-SQL カーソルの種類
Transact-SQL カーソルには静的カーソル、動的カーソル、キーセットカーソルがあります。選択したカーソルの種類によって、ベーステーブルで、構成要素、順序、値など各データに行われた変更をカーソルの結果セットに反映させるかどうかが決まります（表5-1-1）。
種類 |
構成要素 |
順序 |
値 |
|---|---|---|---|
静的 |
× |
× |
× |
動的 |
○ |
○ |
○ |
キーセット |
× |
× |
○ |
表 5-1-1 Transact-SQL カーソルの種類
上記のカーソルの種類とともに、カーソルの扱い方（更新可能、読取専用、スクロール可能、高速順方向専用）などを定義します。
5-2 同時実行オプション
SQL Server は Transact-SQL カーソルに対して、3 つの同時実行オプションをサポートします（表 5-2-1）。更新の競合を防止しつつ、最大の同時実行性を提供するように設定します。
種類 |
説明 |
|---|---|
READ_ONLY |
行にロックをかけない。 |
OPTIMISTIC |
オプティミスティック同時実行制御を実装する。更新する際に変更前のデータが更新されているかを timestamp 列で比較する。 |
SCROLL LOCKS |
ペシミスティック同時実行制御を実装する。データをカーソルの結果セットに読み取る際にロックをかける。 |
表 5-2-1 Transact-SQL の同時実行オプション
5-3 Transact-SQL カーソルの使用
Transact-SQL カーソルは以下の手順で使用します。
（手順）
カーソルを Transact-SQL ステートメントの結果セットに関連づけ、カーソル内の行が更新可能であるかなどのカーソルの特性を定義します。
Transact-SQL ステートメントを実行して結果セットを生成し、カーソルに読み込みます。
カーソルから1行または行ブロックで取得します（フェッチ）。フェッチを連続して実行して順方向または逆方向に行を取得することをスクロールと言います。
必要に応じて、カーソル内の現在位置の行に対して操作を行います。
カーソルを閉じ、割り当てを解放します。
（例）販売データベース内の各ユーザーテーブルの使用状況を表示する
USE 販売 DECLARE @tablename nvarchar(8000) DECLARE tnames_cursor CURSOR STATIC FORWARD_ONLY --1 FOR SELECT name FROM sysobjects WHERE objectproperty(id,'IsUserTable') = 1 ORDER BY name--2 OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @tablename--3 WHILE (@@FETCH_STATUS = 0) BEGIN EXEC sp_spaceused @tablename--4 FETCH NEXT FROM tnames_cursor INTO @tablename END CLOSE tnames_cursor--5 DEALLOCATE tnames_cursor
6. XML 形式でのデータの拡張
SQL Server 2000 からは SELECT ステートメントの実行結果を XML 形式で返すことができます。
6-1 FOR XML 句
XML形式の結果を直接取得するためには FOR XML 句で 3 つの内のいずれかのモード（表 6-1-1）を使用します。
モード |
説明 |
|---|---|
RAW |
クエリの結果セットに含まれる各行を汎用の識別子 row を持つ XML 要素に変換します。NULL 以外の各列の値は、XML 要素の属性にマップされます。属性名は列名と同じです。 |
AUTO |
クエリ結果をネストされた XML 要素として返します。FROM 句に含まれる各テーブルは、そのうち少なくとも 1 つの列が SELECT 句の一覧に示され、XML 要素として表されます。 |
EXPLICIT |
クエリ結果のXML階層を明示的に指定できます。 |
表 6-1-1 FOR XML 句の 3 つのモード
（例）FOR XML AUTO 句を使用して販売データベースの受注テーブルを検索する
.gif)
図 6-1-2 FOR XML AUTO 句を使用した SQL ステートメントとその結果
6-2 XML ビュー
リレーショナルデータベースのビューと同様に、SQL Server2000 ではリレーショナルデータから XDR スキーマを利用して XML ビューを作成できます。XML ビューは Xpath クエリを使用してアクセスします。Xpath クエリは通常 http 経由でアクセスを行うため、IIS から SQL Server を使用できるように 「IIS Virtual Directory Management for SQL Server」 スナップインを使用して SQL Server の仮想ディレクトリを設定する必要があります（画面6-2-1）。
.gif)
図 6-2-1 SQL Server の IIS 仮想ディレクトリ作成
6-3 OPENXML
OPENXML は XML ドキュメントの解析をして、テーブルやビューと同等の行セットを返す行セットプロバイダです。OPENXML では XML ドキュメントを指し示すハンドルが必要になるので、sp_xml_preparedocument システムストアドプロシージャでハンドルを取得します。処理が完了したら、sp_xml_removedocument システムストアドプロシージャでハンドルを削除します。
（例）XMLドキュメント記述された受注データを検索する
.gif)
6-3-1 OPENXML を使用した SQL ステートメントとその結果
7.復習問題
解答、解説は次回に記述します、復習として考えてみてください。
問1 ACCESS の売上テーブルのデータを定期的に SQL Server の売上テーブルにデータ追加していきたい。どのようにすれば良いですか？最も適切なものを選択肢から選んでください。
ア．Windows 2000 のタスクスケジューラで BCP コマンドユーティリティを指定する
イ．Windows 2000 のタスクスケジューラで BULK INSERT コマンドを指定する
ウ．DTS パッケージを作成し、SQL Server エージェントサービスを使ってジョブ登録する。
エ．スナップショットレプリケーションで定期的にスケジューリングする。
問2 下記の表から売上金額（製品単価＊売上個数の総計）が多い上位 10 ％ の社員の社員 ID と社員名と売上金額を表示したい。適切な SQL 文を選択肢から選んでください。
表名: 売上
| 社員ID | 社員名 | 製品ID | 製品単価 | 売上個数 |
ア．SELECT TOP 10 社員ID,社員名,SUM(製品単価*売上個数) as 売上金額 FROM 売上
GROUP BY 社員ID
ORDER BY売上金額
イ. SELECT TOP 10 社員ID,社員名,SUM(製品単価*売上個数) as 売上金額 FROM 売上
GROUP BY 社員ID
ORDER BY売上金額 DESC
ウ. SELECT TOP 10 PERCENT 社員ID,社員名,SUM(製品単価*売上個数) as 売上金額 FROM 売上
GROUP BY 社員ID
ORDER BY売上金額 DESC
エ. SELECT TOP 10 PERCENT 社員ID,社員名,SUM(製品単価*売上個数) as 売上金額 FROM 売上
GROUP BY 社員ID,社員名
ORDER BY売上金額 DESC
問3 以下の要件で売上テーブルの製品単価を変更したい。適切なSQL文を選択肢から選んでください。
（要件）
製品IDがFで始まるものは製品単価を2倍にする
製品IDがNで始まるものは製品単価をそのままにする
製品IDがFまたはN以外で始まるものはない
ア．DECLARE 単価_cursor CURSOR
DYNAMIC SCROLL_LOCKS
FOR SELECT 製品単価 FROM 売上
WHERE 製品ID LIKE N'F%'
FOR UPDATE
OPEN 単価_cursor
FETCH NEXT FROM 単価_cursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE 売上 SET 製品単価 = 製品単価 * 2
WHERE CURRENT OF 単価_cursor
FETCH NEXT FROM 単価_cursor
END
CLOSE 単価_cursor
DEALLOCATE 単価_cursor
イ．DECLARE 単価_cursor CURSOR
DYNAMIC SCROLL_LOCKS
FOR SELECT 製品単価 FROM 売上
WHERE 製品ID LIKE N'N%'
FOR UPDATE
OPEN 単価_cursor
FETCH NEXT FROM 単価_cursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE 売上 SET 製品単価 = 製品単価 * 2
WHERE CURRENT OF 単価_cursor
FETCH NEXT FROM 単価_cursor
END
CLOSE 単価_cursor
DEALLOCATE 単価_cursor
ウ．UPDATE 売上 SET 製品単価 = 製品単価 * 2
WHERE 製品ID LIKE N'F%'
エ．UPDATE 売上 SET 製品単価 = 製品単価 * 2
WHERE 製品ID IN (SELECT 製品ID FROM 売上
WHERE 製品ID LIKE N'F%')
.jpg)
鈴木 智行 : NEC Eラーニング事業部に所属。 入社以来、インストラクタとして教育業務に従事。汎用機、UNIX を経て、1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。Windows 2000 および SQL Server 2000 での MCSA, MCSE, MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア （データベース） も取得済。最近は MCA の 3 科目 （データベース、OS／ネットワーク、アプリケーション構築） 全てに合格し、C# を勉強中。