SAK 図書館
SAK 図書館
SQL 基礎実地編 (その八) 問い合わせ、スキーマリスト、テーブル構造他
Oracle 固有の部分があるかもしれません。SQL-Server、MDB では注意のこと。
■数値項目 Not NULL 時セット関数
・以下の SQL では、次の Data2NotNull() ストアドファンクションを使用し
ている。このまま作成するか、SQL を工夫して実習して下さい。
尚、Data2NotNull() のスキーマは「sak」になっているので、変更などして
作成下さい。
/*
========================================================================
数値項目 Not NULL 時セット関数作成
========================================================================
*/
create or replace function sak.Data2NotNull (dt1 in number, dt2 in number) return number is
dt number;
begin
dt := dt1;
if dt2 > 0 then
dt := dt2;
end if;
return dt;
end;
/
■スキーマリスト、テーブルリスト(タイプ順)
・スキーマに含まれるテーブル一覧などを問い合わせることができる。
この例では、スキーマ「sak」のテーブル、ビュー、ストアドファンクショ
ン、ストアドプロシジャなどのオブジェクトリストが全て小文字で得られる。
(すべてのテーブル、全てのテーブル、登録されているテーブル、表一覧、テーブル名表示)
(すべてのビュー、全てのビュー、登録されているビュー、ビュー一覧)
(すべてのパッケージ、全てのパッケージ、登録されているパッケージ、パッケージ一覧)
(すべてのストアドプロシジャ、全てのストアドプロシジャ)
(登録されているストアドプロシジャ、ストアドプロシジャ一覧)
select
lower(owner) as owner,
lower(object_name) as object_name,
lower(object_type) as object_type,
last_ddl_time
from all_objects
where lower(owner) = 'sak'
order by owner, object_type, object_name
;
■スキーマリスト、テーブルリスト(オブジェクト名順)
・スキーマに含まれるオブジェクトをオブジェクト名順リストする。
select
lower(owner) as owner,
lower(object_name) as object_name,
lower(object_type) as object_type,
last_ddl_time
from all_objects
where lower(owner) = 'sak'
order by owner, object_name, object_type
;
■テーブルの有無
・テーブルの有無を調べるには、次のようにする。
(テーブル名参照)
select
owner,
object_name,
object_type,
from all_objects
where owner = 'SAK' and object_name = '受注M'
;
■ユーザリスト、スキーマリスト
・全スキーマ、つまり、ユーザリストを問い合わせることができる。
この例では、スキーマの一覧が全て小文字で得られる。
select
lower(username) as username,
created
from all_users
order by username
;
■テーブル構造リスト、テーブルレイアウト
・特定のテーブルの定義構造を問い合わせることができる。
この例では、スキーマ「sak」のテーブル「テストm」の定義構造リストが
全て小文字で得られる。(テーブル構造取得、表構造取得)
select
lower(owner) as owner,
lower(table_name) as table_name,
column_name, lower(data_type) as data_type,
sak.data2notnull(data_precision, char_col_decl_length) as char_col_decl_length,
data_scale
from all_tab_columns
where lower(owner) = 'sak' and lower(table_name) = 'テストm'
order by owner, table_name, column_id
;
■項目定義サーチ、テーブル列名参照(含まれる文字検索)
・同じ項目名に対する不揃いな定義をなくするには、項目でテーブル構造を
検索して、既存の項目定義を調べると良い。
この例では、「受注数」と言う文字列が含まれる項目定義の一覧が全て
小文字で得られる。
select
lower(owner) as owner,
lower(table_name) as table_name,
column_name,
lower(data_type) as data_type,
sak.data2notnull(data_precision, char_col_decl_length) as char_col_decl_length,
data_scale
from all_tab_columns
where instr(column_name, '受注数', 1, 1) > 0
order by owner, table_name, column_name, column_id
;
■DUAL
・dual はダミーのテーブルみたいなものです。
関数のテストなどにも使用できます。
select
'aaaa' || '001'
from dual
;
select
123 * 100
from dual
;
select
sqrt(2)
from dual
;
■データディクショナリ
・ディクショナリ一覧を得るには、次のようにする。
select table_name from dictionary where table_name like 'USER_%'
;
select table_name from dictionary where table_name like 'ALL_%'
;
select table_name from dictionary where table_name like 'DBA_%'
;
USER_ALL_TABLES テーブル情報
・動的パフォーマンス表を得るには、次のようにする。
select table_name from dictionary where table_name like 'V$%'
;
V$SESSION 接続ユーザ情報
■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
Copyright (C) 1999-2018 Y.SAK