Oracle sqlplus tips

Oracle

SQLPlusの覚え書き

出力時の1ページの行数を変更する
"set pages"を使用する。

例）set pages 300



長い文字列データを表示できない
"set long"を使用する。最大で1000文字表示したい場合は次の通り。

例）set long 1000



sqlplusで長い文字列データを入力できない
sqlplusで入力できるコマンド行の長さが2500文字を超えるとエラーになる。文字列結合演算子"||"を使用する。


unixコマンドの実行
先頭に"!"をつけてコマンドを打つ。"!"だけだと、一時的にsqlplusを抜ける。コマンドラインでexitを打つと再びsqlplusに戻る。

例) !ps



Oracle SQLの覚え書き

重複するレコードを削除する
総ての列で同じデータを持つレコードが複数ある場合、ユニークなROWIDを利用して重複レコードを削除する。

例）SELECTでROWIDを調べてから、該当するROWIDのデータを削除する。

SELECT ROWID FROM usertable WHERE col1 = 1;

ROWID
------------------
AAANGiAAEAAAC93AAA

DELETE FROM usertable WHERE ROWID = 'AAANGiAAEAAAC93AAA';



CASE式 (ある条件下での出力値の加工)
主にSELECT文中で使う。IF...THEN...ELSEの構文に対応する。



例）codeが10ならvalの値をcol3として返し、codeが20なら(100-val)をcol3として返す。

SELECT
    col1,
    col2,
    CASE code
        WHEN 10 THEN val
        WHEN 20 THEN 100 - val
    END AS col3
FROM datatable;



MERGE (insertとupdateの使い分け)
KEY(またはあるデータ)があればupdate、なければinsert処理を行うようなときに使用する。

MERGE INTO 表名A
USING 表名B  
ON ( 条件 )
WHEN MATCHED THEN 
    UPDATE SET 列名1 = 値1 ...
WHEN NOT MATCHED THEN 
    INSERT (列名1,...) VALUES ( 値1,... )

例）codeが10のデータがあればupdateし、なければinsert処理を行う。

MERGE INTO datatable t1 
USING (SELECT 10 AS code FROM DUAL) t2
ON (t1.code = t2.code )
WHEN MATCHED THEN
    UPDATE SET name = 'code10' WHERE code = 10
WHEN NOT MATCHED THEN
    INSERT (code,name) VALUES (10,'code10');



ダミー表 (dual)
システム情報を取得したり、組み込み関数による演算のみを行う際に使用する。

例）

SQL> SELECT SUBSTR('moshimoshikameyokamesannyo',5,10) FROM DUAL;

SUBSTR('MO
----------
imoshikame

SQL> SELECT sysdate FROM DUAL;

SYSDATE
---------
18-AUG-06



ソート時のNULL値の扱い
ソートを行う場合、NULL値はデフォルトでは最大値として扱われる。このためASCではNULLの項目が最後、DESCではNULLの項目が最初に表示される。このNULLの評価を逆にしたい(最小値として取り扱う)場合は、ASCではNULLS FIRST、DESCではNULLS LASTを併用する。

例）NULL値の項目を最後に表示する

SELECT * FROM datatable ORDER BY val DESC NULLS LAST;



辞書順ソートNLSSORT()で、大文字、小文字、半角、全角を同値とする
Oracle 10g から"NLS_SORT=JAPANESE_M_CI"と"CI(Case Ignore)"キーワードが追加されている。"CI"を追加すると大文字、小文字、全角、半角、ひらがな、カタカナにおいて、同じコードポイントを戻す。

以下は等価。

NLSSORT('a','NLS_SORT=JAPANESE_M_CI')
NLSSORT('A','NLS_SORT=JAPANESE_M_CI')
NLSSORT('Ａ','NLS_SORT=JAPANESE_M_CI')

以下は等価。

NLSSORT('ｱ','NLS_SORT=JAPANESE_M_CI')
NLSSORT('ア','NLS_SORT=JAPANESE_M_CI')
NLSSORT('あ','NLS_SORT=JAPANESE_M_CI')



LIKE検索の条件の文字列に"%"や"_"を指定する方法
検索文字の"%"や"_"の前にエスケープ文字を付け、ESCAPEの後にそのエスケープ文字を指定する。

例）WHERE a LIKE '%CONT\%_' ESCAPE '\'