明示的な型の変換を行なう、型のキャストを行なう

※ CAST 関数を使用しない場合でも 暗黙的な変換 が行なわれることが多い。暗黙変換が行なわれずにエラーになる関数もある。

syntax

  • CAST ( expr AS type_name )
  • CAST ( expr AS type_name
               [ DEFAULT def_val ON CONVERSION ERROR ] ↵
               [ , format[, nls_param]] ) Oracle 12cr2

return [ any datatype ]

  • CAST ( MULTISET ( query ) AS type_name )

return [ collection ]

parameter

expr任意の組み込み型による式
type_name組み込み型の名称
query副問合せ式
def_val変換エラー時のデフォルト値
formatフォーマット文字列
nls_param各国語サポートパラメータNLS セッションパラメータ依存

return

  • 型をキャストしたデータ、または、TYPE にキャストした副問合せの内容
    Oracle 12c R2 以降の場合、format 書式にて変換出来ない値を def_val で戻すことが可能。

avail

  • SQL および PL/SQL の両方で使用可能。但し、PL/SQL では機能が制限される。

組み込み型の名称

DEFAULT ... ON CONVERSION ERROR で使用可能なデータ型は以下に制限される。

  • 数値型 BINARY_DOUBLE / BINARY_FLOAT / NUMBER
  • 日付型 DATE / TIMESTAMP / TIME WITH TIME ZONE / TIMESTAMP WITH LOCAL TIME ZONE
  • 期間型 INTERVAL DAY TO SECOND / INTERVAL YEAR TO MONTH (※1)

(※1) 期間型には フォーマット文字列、各国語サポートパラメータ は使用できない。

変換エラー時のデフォルト値

フォーマットエラーとならないバインド変数または文字リテラル
カラム名は使用できない(Oracle 12c R2 時点)

フォーマット文字列

各国語サポートパラメータ例

nls_param の種類expr のタイプ設定例
NLS_DATE_LANGUAGE日時文字列式JAPANESE
NLS_CALENDAR'JAPANESE IMPERIAL'(要クォート)
NLS_NUMERIC_CHARACTERS数値、数値文字列式NLS 関連パラメータ 参照
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_DUAL_CURRENCY

CAST 関数の内容(組み込みデータ型の型変換)

任意の組み込み型による 式 expr組み込みデータ型 type_name 型に変換して戻す。
TO_xxx 関数シリーズ とは異なり書式を指定することはできない。(Oracle 12c R1 以前)

CHAR ⇔ VARCHAR2、CHAR ⇔ CLOB、NUMBER ⇔ VARCHAR2 など、知らずに 暗黙変換 による型キャストを利用していることが多い。
良くある失敗:カラムが暗黙変換されると、そのカラムはインデックスを使用できない。

注意

PL/SQL では SQL 関数としての CAST と異なった仕様となる。

SQL> declare
  2     str varchar2(10);
  3  begin
  4     str := CAST(10 AS VARCHAR2);
  5  end;
  6  /
 
PL/SQLプロシージャが正常に完了しました。
 
SQL> declare
  2     str varchar2(10);
  3  begin
  4     str := CAST(10 AS VARCHAR2(10));
  5  end;
  6  /
        str := CAST(10 AS VARCHAR2(10));
                                  *
行4でエラーが発生しました。:
ORA-06550: 行4、列28:

CAST 使用例

CHAR 型の変換

SQL> select translate(CAST('ひらがな' as CHAR(20 byte)), ' ', '_')
  2   from dual;
 
TRANSLATE(CAST('ひらがな'ASCHAR(20BYTE)),'','_')
--------------------------------------------------------------------
ひらがな____________
 
SQL>
SQL> select translate(CAST('ひらがな' as CHAR(20 char)), ' ', '_')
  2   from dual;
 
TRANSLATE(CAST('ひらがな'ASCHAR(20CHAR))
----------------------------------------
ひらがな________________
 
SQL>

VARCHAR2 型で 文字リテラル が キャスト先の定義より長い場合

SQL> select CAST('1234567890' AS VARCHAR(5)) from dual;
 
CAST('1234
----------
12345

NUMBER 型の場合(丸め処理される)

SQL> select CAST(1.534567890 AS NUMBER(5)) from dual;
 
CAST(1.534567890ASNUMBER(5))
----------------------------
                           2

数値のオーバーフロー (Oracle 12c R1 以前)

SQL> select CAST(1234567890 AS NUMBER(5)) from dual;
select CAST(1234567890 AS NUMBER(5)) from dual
            *
行1でエラーが発生しました。:
ORA-01438: この列に許容される指定精度より大きな値です

数値のオーバーフロー対応 (Oracle 12c R2 以降)

SQL> select 
  2   CAST(1234567890 AS NUMBER(5) DEFAULT null ON CONVERSION ERROR) NG_EXPR,
  3   CAST(1234567890 AS NUMBER(5) DEFAULT null ON CONVERSION ERROR, '99999') OK_EXPR1,
  4   CAST(1234567890 AS NUMBER    DEFAULT null ON CONVERSION ERROR, '99999') OK_EXPR2
  5  from dual;
 
   NG_EXPR   OK_EXPR1	OK_EXPR2
---------- ---------- ----------
1234567890 <NULL>     <NULL>

データ型の表記に精度は使わないほうが良い模様…

文字リテラルからタイムゾーン付きタイムスタンプへ

SQL> select CAST('2000 01 01 00 00 00 +9:00' as timestamp with time zone)
  2   from dual;
 
CAST('20000101000000+9:00'ASTIMESTAMPWITHTIMEZONE)
--------------------------------------------------------------------------
00-01-01 00:00:00.000000 +09:00
 


SQL 関数 - CAST 関連

 
アルファベット別 関数一覧 ショートカット
ABCDEFG
HILMNOP
RSTUVWX
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ オラクルサポートセンター