データ型変換

SQL文内やPL/SQLプログラム内で、データ型を変換することがあります。データ型の変換にはCAST関数やデータ型に応じたTO_*関数等を利用します。例えば数字を文字列に、文字列を日付に変換する場合には以下のようなSQL文を実行します。

SQL> SELECT TO_CHAR(1234, '9999') FROM DUAL;

TO_CH
-----
 1234

SQL> SELECT TO_DATE('2018/04/02', 'YYYY/MM/DD') FROM DUAL;


TO_DATE(
--------
18-04-02

しかし、常にデータ型の変換が正常に行われるとは限らず、変換できない値を指定された場合にはエラーが発生します。エラー発生時に適切な動作を行うために、PL/SQLプログラムでは例外が発生した場合のEXCEPTIONブロックが必要になります。

下記の例はTO_DATE関数による日付変換がエラーになった場合にErrorを表示されるPL/SQLブロックです。

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  v DATE;
BEGIN
  SELECT TO_DATE('abcd/ef/gh','YYYY/MM/DD') INTO v FROM DUAL;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error');
END;
/

Error

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

VALIDATE_CONVERSION関数

Oracle Database 12c Release 2 の新機能であるVALIDATE_CONVERSION関数を使うと、変換エラーが発生するかどうかを例外を発生させることなく事前にチェックすることができるようになります。
VALIDATE_CONVERSION関数に変換前の値と、変換が必要なデータ型を指定すると、変換可能な場合には0を、変換できない場合には1を返します。

構文

VALIDATE_CONVERSION( 変換前データ AS データ型 ）
VALIDATE_CONVERSION( 変換前データ AS データ型, フォーマット指定 ）
VALIDATE_CONVERSION( 変換前データ AS データ型, フォーマット指定, NLS指定）

実行例
正常に変換できる例（１番目と２番目）とエラーになる例（３番目）です。

SQL> SELECT VALIDATE_CONVERSION(100 AS BINARY_DOUBLE) FROM DUAL;

VALIDATE_CONVERSION(100ASBINARY_DOUBLE)
---------------------------------------
                                      1

SQL> SELECT VALIDATE_CONVERSION('2018/03/31' AS DATE, 'YYYY/MM/DD') FROM DUAL;

VALIDATE_CONVERSION('2018/3/31'ASDATE,'YYYY/MM/DD')
---------------------------------------------------
                                                  1

SQL> SELECT VALIDATE_CONVERSION('abcd/ef/gh' AS DATE, 'YYYY/MM/DD') FROM DUAL;

VALIDATE_CONVERSION('ABCD/EF/GH'ASDATE,'YYYY/MM/DD')
----------------------------------------------------
                                                   0

CAST関数

TO_DATE関数やTO_CHAR型関数と異なり、CAST関数は変換先のデータ型をパラメーターで指定できます。Oracle Database 12c Release 2では、型変換がエラーになった場合に返す値をしていできるようになりました。

構文

CAST( 変換前データ AS データ型 DEFAULT 値 ON CONVERSION ERROR)
CAST( 変換前データ AS データ型 DEFAULT 値 ON CONVERSION ERROR, フォーマット)
CAST( 変換前データ AS データ型 DEFAULT 値 ON CONVERSION ERROR, フォーマット, NLS指定)

実行例1
文字列をBINARY_DOUBLE型に変換し、変換エラーが発生した場合は0.0を返す（変換は正常に行われる）。

SQL> SELECT CAST('123.4567' AS BINARY_DOUBLE DEFAULT 0.0 ON CONVERSION ERROR) FROM DUAL;

CAST('123.4567'ASBINARY_DOUBLEDEFAULT0.0ONCONVERSIONERROR)
----------------------------------------------------------
                                                1.235E+002

実行例2
文字列をDATE型に変換し、変換エラーが発生した場合は当日日付を返す（変換エラーが発生する）。

SQL> SELECT CAST('2018/02/29' AS DATE DEFAULT SYSDATE ON CONVERSION ERROR, 'YYYY/MM/DD') FROM DUAL;

CAST('20
--------
18-04-02