SAK 図書館
SAK 図書館
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 編資料
Copyright (C) 1999-2018 Y.SAK