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 2020-05-12
排他のタイミング 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目次へ戻る / 技術メモへ戻る
メールの送信先：ひしだま