Oracleでやりたいアレコレをまとめる

SQL, Programming, DB13 November 2020

Oracleのデータベースに関するやりたいあれやこれやをまとめておきます。

sqlplusを使ってログインする

オラクルへの接続にはsqlplusコマンドを使用する。使い方は以下の通り。

# ユーザー指定
$ sqlplus {user_name}

# ユーザー+パスワード指定
$ sqlplus {user_name}/{password}

インデックスを作成/削除する

  • 作成
CREATE INDEX ${INDEX_NAME} ON ${TABLE_NAME} ( ${COLUMN_LIST} );

${COLUMN_LIST}はカンマ区切りで複数指定ができます。

  • 削除
DROP INDEX ${INDEX_NAME}

テーブルのスキーマを確認する

SELECT
    OWNER,
    TABLE_NAME
FROM
    ALL_TABLES
WHERE
    TABLE_NAME = UPPER(${TABLE_NAME});

ちなみにスキーマとは

スキーマはユーザー作成時に自動で作られる。名前もユーザー名と同じで作成される。

ユーザーと密接に繋がっているため、ユーザーを削除すると同時にそのユーザー作成時に作られたスキーマも削除される！（要注意）

  • スキーマを指定しない場合、ログインユーザーのスキーマを示す

  • 他のユーザーのスキーマにアクセスするには「スキーマ.テーブル名」

結果の表示の全体幅を確認/変更する

  • 確認
show linesize
  • 変更
set linesize ${LINE_SIZE}

一時テーブル（TMPテーブル）を作成する

Oracleで提供される一時テーブルの機能を使用すると、テーブル作成時に「grobal temporary」を指定することで

以下の通りセッションやトランザクションの終了時に中のデータを削除（初期化）することができます。

  • トランザクション終了時に初期化
create global temporary table ${TABLE_NAME}(
  ${COLUMN_NAME_1} ${COLUMN_TYPE_1},
  ${COLUMN_NAME_2} ${COLUMN_TYPE_2}
) on commit delete rows;
  • セッション終了時に初期化
create global temporary table ${TABLE_NAME}(
  ${COLUMN_NAME_1} ${COLUMN_TYPE_1},
  ${COLUMN_NAME_2} ${COLUMN_TYPE_2}
) on commit preserve rows;

違いは「on commit delete」か「on commit rows」か

Limit句みたいなことがしたい

Limit句がないので、その代わりにROWNUMを使用します。 以下は100件の例。

select * from hoge where rounum <= 100;

ソート後の上位何件とかはこのままでは取得できないので、副問い合わせを組み合わせるかrownumer関数を使用する

nullを他の値に変換する

  • NVL関数を使用する
NVL(a, b)

aがnullでなければa、nullならbを返却する 参考

別テーブルのSELECT句の結果でUPDATEしたい

参考

コーディング規則

SQLは大文字と小文字の区別をしないので、規則を決めて統一すると見やすい 分析SQLのコーディングスタイル

DUALについて

ダミーの表のこと。デフォルトでSYSユーザーのスキーマに作られる。 関数のみ実行したいなどの時に有効。 参考

独自関数を定義する

Oracleで既存で定義される関数とは別に、独自で定義することができる

create [or replace] function ${FUNCTION_NAME} (引数 IN データ型[, ...])
    return 戻り値の型
  is
    宣言部
  begin
    処理部
  end
;

参考

commitとrollback

  • commit トランザクション内で行ったDML(INSERT・UPDATE・DELETE)を確定する。 オラクルの設定ではデフォルトでDMLはauto commitされないので、明示的にcommitする必要がある。 DDLについてはauto commit

  • rollback トランザクション内で行った変更を全て取り消すことができる。

参考

統計情報

オラクルに限った話では無いが、一般的なRDBには統計情報というものが存在している。 これは、いわゆるメタデータ的なもので例えば以下のようなものがある。

  • レコード数
  • カラムごとの値のばらつき

この情報があるおかげで、 オラクルでは実行計画を作成する際にどちらのテーブルに対して結合を行う方が効率が良いかなど判断することができる

fetch first N rows only

最初のN行を取得してくる。 where rownum <= Nではorder byした後の取得を行うことが面倒だが、fetch first N rows onlyを使えば楽

tags: SQL, Programming, DB