※上記の広告は60日以上更新のないWIKIに表示されています。更新することで広告が下部へ移動します。


文字列定数で改行を指定したい

  • sqlplus で SQLを手動で実行する場合、文字列定数に空行を含ませるとそれ以前の入力が無効になる
SQL> update ttt set item2='asd
  2  asd
  3
SQL>
  • 対策その1
    • 改行コードを関数で指定する
      • 下記は LF を指定する例。
      • CRLF の場合は chr(13)||chr(10) のように指定する
INSERT INTO table_name (item_name) values ('あいうえお'||chr(10)||'かきくけ');
  • 対策その2
    • PL/SQLの書式で入力する
      • この場合、改行コードは sqlplus を実行している環境に依存する
SQL> begin
  2  update ttt set item2='ささ
  3
  4  すすす' where item1=12345;
  5  end;
  6  /

PL/SQLプロシージャが正常に完了しました。

自分が登録した何かを調べたい

  • "USER_OBJECTS" を見るとよい
  • "USER_SOURCE" には処理のソースがある

項目を16進ダンプ

SELECT RAWTOHEX(項目名) FROM テーブル名

並び順を逆順にすると NULL が先頭にくるのはなんとかならんか?

  • NULL 項目に値は無いが、並び順の項目に指定すると無限大と同じ振る舞いをする
  • この場合、強制的に後ろに並べる方法がある
SELECT ITEM01 FROM TABLE01 OEDER BY ITEM01 DESC NULLS LAST
  • 参考
    • ASC - 昇順(省略時)
    • DESC - 降順
    • NULLS LAST - 昇順のときのデフォルト
    • NULLS FIRST - 降順のときのデフォルト

テーブル参照あれこれ

したいこと → とあるテーブルを別のテーブル名でアクセス

簡単なのはテーブルを物理的にコピーする方法

CREATE TABLE 新テーブル名 AS SELECT * FROM 元テーブル名;
(参考1)構造だけコピーしたい場合は次のようにする
CREATE TABLE 新テーブル名 AS SELECT * FROM 元テーブル名 WHERE 2<1;
(参考2)テーブルのリネームは次のように(SQL*Plusの場合)
RENAME 旧テーブル名 TO 新テーブル名;
別名をつけるという方法もある
CREATE SYNONYM シノニム名 FOR 参照先テーブル名;
この場合は、元のテーブルが更新されたも同じものを見ているので問題なし

別のOracleユーザのテーブルをアクセスしたい場合は、権限を与える

GRANT SELECT ON テーブル名 TO 権限を与えるユーザ名;
アクセスは次のように
SELECT * FROM テーブルを持つユーザ名.テーブル名;
権限の取り消しは次のように
REVOKE SELECT FROM ON テーブル名 FROM 権限を与えたユーザ名;

別のサーバをアクセスする場合はDBリンクを作成する

作成方法は省略。アクセスは次のように
SELECT * FROM テーブル名@DBリンク名;

スナップショットを取る方法

もあるが、元テーブルが更新された場合は内容が古いまま
スナップショットは普通のテーブルと同じように扱えるが、更新はできない

Oracle でダブルクォート使用上の注意

Oracle 内では、テーブル名やフィールド名などを小文字で指定しても内部的には大文字に解釈される。
  • 例えばこんなテーブルを作って
CREATE  TABLE  TEST_DQ02 (
  "受注No"  char  (9),  
  "受注日"  date
);
  • このアクセスはOK
SELECT  "受注No"  FROM  TEST_DQ02;
  • このアクセスはNG
SELECT  受注No  FROM  TEST_DQ02;
  • これが、こんなテーブルなら
CREATE  TABLE  TEST_DQ02 (
  "受注NO"  char  (9),  
  "受注日"  date
);
  • このアクセスもOK (「No」が「NO」と解釈されるため)
SELECT  受注No  FROM  TEST_DQ02;
  • この現象は、名称がダブルクオート付きで定義されているのが問題ではない
    • たぶん内部的にはそうなってるんだろうけど…
  • ダブルクォート付きで指定することにより、大文字への変換が行われないから
  • 逆に、ダブルクオートを付けないと、大文字に変換されてしまい不一致を起こすためエラーとなる
  • 更にいうと、大文字で定義してあれば、ダブルクオート無しの小文字指定でもいける、ってこと
  • 参考 - http://homepage3.nifty.com/yamada_ken1/starthp/subpage09.html

SQLで "&" が特別扱いされてしまう

SELECT COUNT(*) FROM table_name WHERE item_name LIKE 'aa&bb';
  • 上記のような場合、"&bb" が置き換え文字として扱われてしまう。
  • これを回避する場合、事前に "set define off" を実行するとよい。
  • ただし、sqlplus 以外では使えないので注意!

sqlplus で CSV形式出力

SET ECHO OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET LINESIZE 5000
SET TRIMSPOOL ON

SPOOL TE_SEARCH.csv

SELECT '項目名1'||','||'項目名2'||','||'項目名3' FROM DUAL;
SELECT FILED1||','||FILED2||','||FIELD3 FROM TABLE1;

SPOOL OFF

カレントユーザのテーブル一覧を表示

SELECT * FROM TAB;

表の項目一覧を表示

DESC 表名;
または
DESCRIBE 表名;
※セミコロンは省略できる

インデックスの一覧を表示

SELECT * FROM USER_IND_COLUMNS;
  • 指定したテーブルのインデックス項目を表示するのはこんな感じ
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = '表名';

カレントユーザのユーザ名を表示

SELECT USERNAME FROM USER_USERS;

カレントインスタンスの文字コードを表示

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';

Oracle の制限

属性 ver7.x ver8.x ver9.x
VARCHAR2 2000byte 4000byte 4000byte
LONG 2G - 1byte 2G - 1byte 2G - 1byte

組込関数

CHARTOROWID

CHARTOROWID(char)
  • CHAR型データまたはVARCHAR2データ型の値をROWID型に変換。

CONVERT

CONVERT(char, dest_char_set [, source_char_set])
  • あるキャラクタ・セットの文字列を別のキャラクタ・セットの文字列に変換。

HEXTORAW

HEXTORAW(char)
  • 16進数を含むcharをRAW値に変換します。

RAWTOHEX

RAWTOHEX(raw)
  • rawを16進で表した文字の値に変換します。

ROWIDTOCHAR

ROWIDTOCHAR(rowid)
  • rowidをVARCHAR2型に変換。結果は常に18文字。

TO_CHAR(日付関数)

TO_CHAR(date [, fmt, [, 'nlsparams'] ])
  • dateをfmt書式のVARCHAR2型のデータに変換。

TO_CHAR(ラベル変換)

TO_CHAR(label [, fmt])
  • MLSLABEL型のデータlabelをfmt書式のVARCHAR2型のデータに変換。

TO_CHAR(数値変換)

TO_CHAR(n [, fmt, [, 'nlsparams'] ])
  • NUMBER型のデータnをfmt書式のVARCHAR2型のデータに変換。

TO_DATE

TO_DATE(char [, fmt, [, 'nlsparams'] ])
  • CHARまたはVARCHAR2型のデータcharをDATE型のデータに変換。

TO_LABEL

TO_LABEL(char [, fmt])
  • CHAR型またはVARCHAR2型のデータcharをMLSLABEL型のデータに変換。

TO_MULTI_BYTE

TO_MULTI_BYTE(char)
  • シングルバイト文字のすべてを対応するマルチバイト文字に変換してcharに戻す。
  • マルチバイト文字がないシングルバイト文字はシングルバイト文字のまま。

TO_SINGLE_BYTE

TO_SINGLE_BYTE(char)
  • マルチバイト文字のすべてを対応するシングルバイト文字に変換してcharに戻す。
  • シングルバイト文字がないマルチバイト文字はマルチバイト文字のまま。

TO_NUMBER

TO_NUMBER(char [, fmt, [, 'nlsparams'] ])
  • パラメータfmtによって指定した書式のラベルを含むCHAR型またはVARCHAR2型のデータcharをNUMBER型のデータに変換。

GREATEST

GREATEST(expr [, expr] ...)
  • リストされたexpr内の最大値を戻します。
  • 2番目以降のすべてのexprは比較の前に最初のexprのデータ型に暗黙に変換されます。

LEAST

LEAST(expr [, expr] ...)
  • リストされたexpr内の最小値を戻します。
  • 2番目以降のすべてのexprは比較の前に最初のexprのデータ型に暗黙に変換されます。

NVL

NVL(expr1 , expr2)
  • expr1がNULLの時にはexpr2を戻し、expr1がNULLでない場合はexpr1を戻す。

USER

USER
  • 現在のユーザーIDをVARCHAR2型のデータにして戻す。

DECODE

DECODE(expr, case1, result1 [, case2, result2] ... , default)
  • IF文と同じような機能を提供する関数。例えば
DECODE( SEX , 1 , '男' , 2, '女', '不明' )
という記述は
IF SEX = 1 THEN
  RETURN '男';
ELSIF SEX = 2 THEN
  RETURN '女';
ELSE
  RETURN '不明';
END IF;
という記述と同じような効果があります。
さらに、面白い使い方として
SQL > SELECT SUM( DECODE( SEX , 1 , 1 , 0 ) )  "男性の人数" 
  2 > , SUM( DECODE( SEX , 2 , 1 , 0 ) )  "女性の人数" 
  3 > FROM PERSONAL ; 
これで、ひとつの項目内の種類別のカウントができます。
あとこのような
SQL > SELECT DECODE( SIGN( A - B ) , 1, 'Aの方が大きい' , -1, 'Bの方が大きい' , '同じ大きさ' ) FROM DUAL;
大小比較結果を得ることもできます。

参考資料