S-JIS[2006-01-09/2012-07-07] 変更履歴

Oracle特殊SQLメモ

書いておかないと忘れてしまうような、Oracleのちょっと面倒なSQLや動作のメモです。

  文字列の指定方法 " ' & 2007-09-15
  NULLの扱い null 2010-12-29
関数 各種関数・集計関数・分析関数   2010-12-29
DML SELECT文 select 2010-09-01
INSERT文 insert 2010-07-24
UPDATE文 update 2010-07-24
DELETE文 delete 2010-07-24
MERGE文 merge 2010-06-16
WHERE句 where(exists・in・like) 2010-12-29
副問い合わせのビュー名定義 with 名前 as 2007-10-16
排他のタイミング lock(SELECT FOR UPDATE) 2009-01-19
実行計画・統計情報ヒント autotrace on/traceonly explain 2010-10-23
DDL 表領域の作成変更 create tablespace 2008-01-14
ユーザーの作成 create user 2008-04-15
権限の付与・削除 grant/revoke 2007-09-22
テーブルの作成・変更・削除 create table/alter table/drop 2012-07-07
整合性制約の定義・確認 constraint 2010-02-19
インデックスの作成・確認 create index 2010-07-29
シノニムの作成 create synonym 2007-09-22
DBリンクの作成 create database link 2008-10-30
シーケンスの作成 create sequence 2010-12-29
マテリアライズドビューの作成 create materialized view 2009-01-17
パーティション分割 partition 2010-07-20
ユーザー定義型 create type 2009-05-02
Javaプログラムの呼び出し create or replace java source 2005-05-15
リサイクルビン (drop)flashback/purge 2010-02-19
  セッション削除 alter system kill session 2009-03-02
  DBの容量見積もり   2007-10-30

文字列

Oracleの文字列(引用符)の扱いについて。[2006-05-26]

表現 内容
文字列   識別子。英小文字で書くと、大文字に変換されて扱われる。
select empno from emp;
"文字列" ダブルクォーテーション 識別子。ダブルクォーテーションで囲むと大文字に変換されない。ので、小文字やハイフンといった記号も使えるようになる。
select "EMPNO" from "EMP";
'文字列' シングルクォーテーション 文字列。
SQL> select 'string' from dual;

'STRIN
------
string
&文字列
&数値
アンパサンド (デフォルトの)置換変数の使用。[/2006-07-08]  
:文字列 コロン バインド変数の使用。[2007-09-15]  
  エスケープ文字 [2007-06-26] 例えば置換変数の文字そのもの(「&」)を 使いたい場合、エスケープ文字を設定して、それを使用する。
(デフォルトではエスケープはオフ(エスケープは使用できない。デフォルトのエスケープ文字というものは無い))
SQL> set escape '\'
SQL> select '\&str' from dual;

'&ST	
----	 置換変数の文字がそのまま出る
&str	
SQL> set escape off		←エスケープをオフにすると
SQL> select '\&str' from dual;
strに値を入力してください: abc	←置換変数の入力が求められる
旧 1: select '\&str' from dual
新 1: select '\abc' from dual

'\AB
----
\abc
一旦エスケープ文字を指定すると、そのエスケープ文字そのものを使う場合もエスケープする必要がある。
SQL> set escape "/"	←初回はOK
SQL> set escape "/"	←↓二度目はエラー
SP2-0272: escape文字に英数字または空白は使用できません。
SQL> set escape "//"	←以前のエスケープ文字でエスケープ要
SQL> 

NULLとの演算

NULLは何も無いことを表すデータ値なので、扱いが特殊。[2007-09-23]

NULLと四則演算を行うと、結果はNULLになる。[/2008-04-29]
Oracleでは空文字列「''」はNULLとして扱われる。文字列の結合を行う場合は空文字列として扱われ、元の文字列そのままとなる。[2010-01-27]
WHEREで条件判断(比較演算)に使っていた場合、NULLとの演算(「IS NULL」以外)は「偽(false)」となる。[/2010-12-29]

「ORDER BY」のソート項目では、NULLは最大値として扱われる。(ASCでは末尾、DESCでは先頭になる)
「order by 項目 nulls first」という指定をするとNULLが先頭に来るようになる。[2007-09-28]
同様に「nulls last」で末尾に来るようになる。[2008-04-29]

「GROUP BY」で使う集計関数(COUNT・MIN・MAXやSUM等)でも、NULLは無視される。
NULLはORDER BYで最大値として扱われているが、MAXでは最大値にならない。(全部がNULLならNULLになっちゃうけど…)

[/2008-04-29] 結果 備考
NULLそのもの SET NULL '*NULL*'
SELECT NULL FROM DUAL;
*NULL*  
NULLとの演算 SELECT 1 + NULL FROM DUAL; *NULL* NULLとの演算結果はNULLになる。
文字列結合 SELECT 'ABC' || '' FROM DUAL;
SELECT 'ABC' || NULL FROM DUAL;
ABC 文字列結合ではNULLは空文字列扱い。[2010-01-27]
SELECT NULL || NULL FROM DUAL; *NULL* 空文字列はNULL扱いされる。
SELECT '' || '' FROM DUAL; *NULL*
SELECT '' FROM DUAL; *NULL*
集計関数 SELECT MAX(NULL) FROM DUAL; *NULL* 対象が全てNULLの場合はNULLになる。
SELECT COUNT(NULL) FROM DUAL; 0 カウントはNULLだと0件(対象なし)になる。
WHERE条件 SELECT 'TRUE' FROM DUAL WHERE NULL = 0; 何も無し NULLとの比較演算は常に偽。
特に「NULL=NULL」も偽になるので注意
NULLかどうかを知りたい場合は「IS NULL
NULLでないことは「IS NOT NULL」を使う。
SELECT 'TRUE' FROM DUAL WHERE NULL <> 0; 何も無し
SELECT 'TRUE' FROM DUAL WHERE NULL = NULL; 何も無し
SELECT 'TRUE' FROM DUAL WHERE NULL <> NULL; 何も無し
SELECT 'TRUE' FROM DUAL WHERE NULL IS NULL; TRUE
SELECT 'TRUE' FROM DUAL WHERE NULL IS NOT NULL; 何も無し
SELECT 'TRUE' FROM DUAL WHERE 'AA' IS NOT NULL; TRUE
空文字列比較 SELECT 'TRUE' FROM DUAL WHERE 'ABC' <> ''; 何も無し 空文字列はNULL扱いされる。
=''」や「<>''」で比較できないことに注意
SELECT 'TRUE' FROM DUAL WHERE '' <> ''; 何も無し
SELECT 'TRUE' FROM DUAL WHERE '' = ''; 何も無し
SELECT 'TRUE' FROM DUAL WHERE '' IS NULL; TRUE
ソート SELECT * FROM (
 SELECT 'VWXYZ'  FROM DUAL UNION
 SELECT NULL     FROM DUAL UNION
 SELECT 'ABCDEF' FROM DUAL
) ORDER BY 1 ASC;
ABCDEF
VWXYZ
*NULL*
「NULLS」の指定をしないと、
NULLは最大値であるかのごとく扱われる。
「NULLS」を指定すると、
昇順・降順に関わらず 指定に従う。
SELECT * FROM (
 SELECT 'VWXYZ'  FROM DUAL UNION
 SELECT NULL     FROM DUAL UNION
 SELECT 'ABCDEF' FROM DUAL
) ORDER BY 1 ASC NULLS FIRST;
*NULL*
ABCDEF
VWXYZ
SELECT * FROM (
 SELECT 'VWXYZ'  FROM DUAL UNION
 SELECT NULL     FROM DUAL UNION
 SELECT 'ABCDEF' FROM DUAL
) ORDER BY 1 DESC NULLS FIRST;
*NULL*
VWXYZ
ABCDEF
SELECT * FROM (
 SELECT 'VWXYZ'  FROM DUAL UNION
 SELECT NULL     FROM DUAL UNION
 SELECT 'ABCDEF' FROM DUAL
) ORDER BY 1 DESC NULLS LAST;
VWXYZ
ABCDEF
*NULL*

参考:


DBの容量見積もり

テーブル等の容量見積もりを計算してくれるページがOTNにあるらしい。[2007-10-30]


参考リンク


Oracle目次へ戻る / 技術メモへ戻る
メールの送信先:ひしだま