SAK 図書館

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

SQL 基礎実地編 (その三) ビュー作成、結合ビュー作成、ビュー削除

■ビュー作成

  ・ビューの使い方で、データベースの使い心地とプログラムでのハンドリング
    負荷が大きく変わる。
    必要なビューを上手く設計して、プログラムコード負荷が少なくなるように
    する。尚、ビューとは、仮想的な表、つまり、仮想テーブル、論理テーブル
    みたいなものである。

  ・create view の as 句以降には、select 文の様々なパターンが指定できる。
    たいへん強力である。

  ・単純ビューで、特定のデータを抜き出す指定は次のようにする。
    この例では、テストv1 は、キーの先頭が「a」で始まるものだけのテーブル
    と同じにアクセスできるようになる。
    尚、view に order by 句は指定できない。

  create view sak.テストv1 as
  select
    キー,
    データ1,
    データ2,
    データ3
  from sak.テストm
  where キー like 'a%'
  ;

  ・特定の項目を見せたくないときは次のようにする。
    表の分割のようなことにも応用できる。
    この例では、テストv1 には、データ2 と言う項目がなくなる。

  create view sak.テストv1 as
  select
    キー,
    データ1,
    データ3
  from sak.テストm
  ;

  ・項目名を変更する場合は、次のようにする。
    この例では、テストv1 には、データ1 と言う項目名が「あいうえお」と言
    う項目名に変更されている。

  create view sak.テストv1 as
  select
    キー,
    データ1 as あいうえお,
    データ3
  from sak.テストm
  ;

  ・計算項目を作る場合は、次のようにする。
    この例では、データ1 + データ2 の合計が「合計」と言う項目名で追加され
    る。様々な SQL 関数を用いた計算項目が作成できる。

  create view sak.テストv1 as
  select
    キー,
    データ1 + データ2 as 合計,
    データ3
  from sak.テストm
  ;

  ・文字列の結合項目を作る場合は、次のようにする。
    この例では、データ1 と データ2 の結合文字が「結合文字」と言う項目名
    で追加される。SQL 関数を用いたフォーマットなども可能である。
    但し、数値項目の文字結合はできない。

  create view sak.テストv1 as
  select
    キー,
    データ1 || データ2 as 結合文字,
    データ3
  from sak.テストm
  ;

  ・テストm のデータ1 と データ2 は数値なので、実際にこの項目を結合した
    い場合は、次のようにする。
    to_char() は、数値を文字列に変換する。
    逆に to_number() は、文字列を数値に変換する。

  create view sak.テストv1 as
  select
    キー,
    to_char(データ1) || to_char(データ2) as 結合文字,
    データ3
  from sak.テストm
  ;

■結合ビュー作成

  ・単純ビューでの様々な指定を含みながら、複数のテーブルやビューをひとつ
    のビューに結合することができる。
    結合する条件が必要なので、なんでもかんでも結合できるわけではない。
    リレーショナルデータベースのゆえんは、この機能にある。
    次のテーブルがあったする。サンプルm のコード1 は、テストm のキーに
    対応しているものとする。

  create table sak.テストm (
    キー             char    (008)     primary key,
    データ1          number  (009, 0),
    データ2          number  (009, 0),
    データ3          number  (009, 0)
  ) STORAGE(INITIAL 100M NEXT 20M MAXEXTENTS 99);

  create table sak.サンプルm (
    番号             char    (008)     primary key,
    データ1          number  (009, 0),
    データ2          number  (009, 0),
    コード1          char    (008),
    コード2          char    (008)
  ) STORAGE(INITIAL 100M NEXT 20M MAXEXTENTS 99);

  ・サンプルm のコード1 にテストm のキーを結合(ジョイン join) する場合、
    次のようにする。
    サンプルv1 には、サンプルm の項目とコード1 に該当するテストm のデー
    タ1 が「参照項目」と言う名前で参照結合される。
    もし、テストm に存在しないキーの場合は、「参照項目」には NULL がセッ
    トされる。但し、「(+)」を省略すると参照できないレコードはビューに含
    まれなくなる。「(+)」を使用すると外部結合となる。
    結合定義時に同名の項目名が複数のテーブルやビューに存在する場合、どち
    らの項目を示しているかテーブル名やビュー名で修飾しなければならない。
    修飾すると、ビューの項目名も修飾付きになってしまうので、as 句で見や
    すい名前にすると良い。
    Oracle では、(+) ですが、SQL-Server では left join などと指定します。

  create view sak.サンプルv1 as
  select
    番号,
    サンプルm.データ1 as データ1,
    サンプルm.データ2 as データ2,
    コード1,
    テストm.データ1   as 参照項目,
    コード2
  from sak.サンプルm, sak.テストm
  where サンプルm.番号 = テストm.キー (+)
  ;

  ・たくさんのテーブルやビューを結合する場合、修飾が見にくく難解になって
    くる。その場合、次のようにする。
    from 句でテーブルやビューの一時名を指定して、それで修飾する。
    上手く使うと、すっきりした SQL 文にできる。

  create view sak.サンプルv1 as
  select
    番号,
    a.データ1 as データ1,
    a.データ2 as データ2,
    コード1,
    b.データ1 as 参照項目,
    コード2
  from sak.サンプルm a, sak.テストm b
  where a.番号 = b.キー (+)
  ;

  ・サンプルm のコード1 とコード2 が同じくテストm を参照するような結合ビ
    ューは作成できない。
    いろいろ試した見たが、ストアドファンクションで参照すれば良い。
    次のようになる。

  create view sak.サンプルv1 as
  select
    番号,
    データ1,
    データ2,
    コード1,
    sak.GetTestm(コード1) as 参照項目1,
    コード2,
    sak.GetTestm(コード2) as 参照項目2
  from sak.サンプルm
  ;

  ・ストアドファンクション GetTestm のコードは、次のようになる。

  create or replace function sak.GetTestm (cd in varchar2) return varchar2 is
    ret varchar2(200);
  begin
    begin
      select データ1 into ret from sak.テストm where キー = scdk;
    exception
      when OTHERS then
        ret := '';
    end;
    return ret;
  end;
  /

■ビュー削除

  ・ビューを削除しても、実際のテーブルデータが消えることはない。

  drop view sak.サンプルv1;

■シノニム作成

  ・テーブルやビューの別名定義をシノニムと呼ぶ。
    シノニムには、public タイプがある。
    public タイプをうまく利用するとスキーマを意識しないで良い。
    シノニムは、テーブルやビューと同じに select 等で扱える。

  create public synonym pub_test for sak.受注v1;
  create synonym pub_test for sak.受注v1;

■シノニム削除

  ・シノニムを削除しても、実際のテーブルデータが消えることはない。

  drop public synonym pub_test;
  drop synonym pub_test;

■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索



Copyright (C) 1999-2017 Y.SAK