Sql-certificate-conversion-functions
変換関数を使用する
SQLユーティリティ関数に加えて、Oracle組み込み関数ライブラリには型変換関数が含まれています。 クエリが特定のデータ型の入力を予期しているが、異なるデータ型で入力を受け取るシナリオがあります。 そのような場合、Oracleは予期しない値を互換性のあるデータ型に暗黙的に変換しようとします。互換性のあるデータ型は代わりに使用でき、アプリケーションの継続性は損なわれません。 型変換は、Oracleによって暗黙的に行われるか、プログラマによって明示的に行われます。
暗黙的なデータ型変換は、内部型キャストに対するOracleのサポートを示すマトリックスに基づいて機能します。 これらのルールに加えて、Oracleでは、明示的な変換および書式設定のための問合せで使用できる型変換関数を提供しています。 実際のところ、ソフトウェアインテリジェンスに頼るのではなく、明示的な変換を実行することをお勧めします。 暗黙的な変換はうまく機能しますが、内部的に型キャストするのが困難な入力が不適切である可能性を排除するために。
暗黙的なデータ型変換
VARCHAR2またはCHAR値は、Oracleによって暗黙的にNUMBERまたはDATE型の値に変換できます。 同様に、NUMBERまたはDATAタイプの値は、Oracleサーバーによって自動的に文字データに変換できます。 暗黙の相互変換は、文字がそれぞれ有効な数値または日付タイプの値を表す場合にのみ発生することに注意してください。
たとえば、以下のSELECTクエリを調べます。 Oracleは内部的に15000と '15000’を同じものとして扱うため、両方のクエリで同じ結果が得られます。
クエリ-1
SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;クエリ2
SELECT employee_id,first_name,salary
FROM employees
WHERE salary > '15000';明示的なデータ型変換
SQL変換関数は、列値、リテラル、または式を型キャストできる単一行関数です。 TO_CHAR、TO_NUMBER、およびTO_DATEは、データ型の相互変更を実行する3つの関数です。
TO_CHAR関数
TO_CHAR関数は、数値または日付の入力をフォーマットモデル（オプション）で文字型に型キャストするために使用されます。
構文
TO_CHAR(number1, [format], [nls_parameter])数値から文字への変換では、nlsパラメーターを使用して、小数点文字、グループ区切り文字、現地通貨モデル、または国際通貨モデルを指定できます。 これはオプションの仕様です。使用できない場合、セッションレベルのnls設定が使用されます。 日付から文字への変換では、nlsパラメーターを使用して、必要に応じて日と月の名前を指定できます。
TO_CHAR関数を使用して文字型に変換した後、日付を複数の形式でフォーマットできます。 TO_CHAR関数は、Oracle 11gが特定の形式で日付を表示するために使用されます。 形式モデルでは大文字と小文字が区別されるため、一重引用符で囲む必要があります。
以下のSELECTクエリを検討してください。 クエリは、TO_CHAR関数を使用して、EMPLOYEESテーブルのHIRE_DATEおよびSALARY列をフォーマットします。
SELECT first_name,
TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
TO_CHAR (salary, '$99999.99') Salary
FROM employees
WHERE rownum < 5;
FIRST_NAME HIRE_DATE SALARY
-------------------- ------------------ ----------
Steven JUNE 17, 2003 $24000.00
Neena SEPTEMBER 21, 2005 $17000.00
Lex JANUARY 13, 2001 $17000.00
Alexander JANUARY 03, 2006 $9000.00最初のTO_CHARは、雇用日を日付形式MONTH DD、YYYYに変換するために使用されます。 月が綴られ、スペースが埋め込まれ、その後に2桁の日、4桁の年が続きます。 月の名前を大文字と小文字を混ぜて表示する（つまり、「12月」）場合は、この引数をフォーマット引数（ 'Month DD、YYYY'）で使用します。
図10-39の2番目のTO_CHAR関数は、通貨記号と2つの小数点位置を表示するようにSALARYをフォーマットするために使用されます。
オラクルは、包括的な形式モデルのセットを提供しています。 次の表は、TO_CHARを使用して日付および数値を文字として型キャストするために使用できる形式モデルのリストを示しています。
| Format Model | Description |
|---|---|
| ,(comma) | It returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions:A comma element cannot begin a number format model. A comma cannot appear to the right of a decimal character or period in a number format model. |
| .(period) | Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one period in a number format model |
| $ | Returns value with a leading dollar sign |
| 0 | Returns leading zeros. Returns trailing zeros. |
| 9 | Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number. |
| B | Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of "0"s in the format model). |
| C | Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter). |
| D | Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.). Restriction: You can specify only one decimal character in a number format model. |
| EEE | Returns a value using in scientific notation. |
| FM | Returns a value with no leading or trailing blanks. |
| G | Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model. Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model |
| L | Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter). |
| MI | Returns negative value with a trailing minus sign (-). Returns positive value with a trailing blank. Restriction: The MI format element can appear only in the last position of a number format model. |
| PR | Returns negative value in . It can appear only in the end of a number format model. |
| RN,rm | Returns a value as Roman numerals in uppercase. Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999. |
| S | Returns negative value with a leading or trailing minus sign (-). Returns positive value with a leading or trailing plus sign (+). Restriction: The S format element can appear only in the first or last position of a number format model. |
| TM | "Text minimum". Returns (in decimal output) the smallest number of characters possible. This element is case-insensitive. |
| U | Returns in the specified position the "Euro" (or other) dual currency symbol (the current value of the NLS_DUAL_CURRENCY parameter). |
| V | Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9’s after the "V". |
| X | Returns the hexadecimal value of the specified number of digits. |
TO_NUMBER関数
TO_NUMBER関数は、文字値を数値データ型に変換します。 変換される文字列に数字以外の文字が含まれている場合、関数はエラーを返します。
構文
TO_NUMBER (string1, [format], [nls_parameter])次の表は、TO_NUMBERを使用して文字値を数値として型キャストするために使用できる形式モデルのリストを示しています。
| Format Model | Description |
|---|---|
| CC | Century |
| SCC | Century BC prefixed with - |
| YYYY | Year with 4 numbers |
| SYYY | Year BC prefixed with - |
| IYYY | ISO Year with 4 numbers |
| YY | Year with 2 numbers |
| RR | Year with 2 numbers with Y2k compatibility |
| YEAR | Year in characters |
| SYEAR | Year in characters, BC prefixed with - |
| BC | BC/AD Indicator |
| Q | Quarter in numbers (1,2,3,4) |
| MM | Month of year 01, 02…12 |
| MONTH | Month in characters (i.e. January) |
| MON | JAN, FEB |
| WW | Week number (i.e. 1) |
| W | Week number of the month (i.e. 5) |
| IW | Week number of the year in ISO standard. |
| DDD | Day of year in numbers (i.e. 365) |
| DD | Day of the month in numbers (i.e. 28) |
| D | Day of week in numbers(i.e. 7) |
| DAY | Day of the week in characters (i.e. Monday) |
| FMDAY | Day of the week in characters (i.e. Monday) |
| DY | Day of the week in short character description (i.e. SUN) |
| J | Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle) |
| HH,H12 | Hour number of the day (1-12) |
| HH24 | Hour number of the day with 24Hours notation (0-23) |
| AM, PM | AM or PM |
| MI, SS | Number of minutes and seconds (i.e. 59) , |
| SSSSS | Number of seconds this day. |
| DS | Short date format. Depends on NLS-settings. Use only with timestamp. |
| DL | Long date format. Depends on NLS-settings. Use only with timestamp. |
| E | Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official, Thai Buddha. |
| EE | The full era name |
| FF | The fractional seconds. Use with timestamp. |
| FF1..FF9 | The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds. |
| FM | Fill Mode: suppresses blanks in output from conversion |
| FX | Format Exact: requires exact pattern matching between data and format model. |
| IYY OR IY OR I | The last 3,2,1 digits of the ISO standard year. Output only |
| RM | The Roman numeral representation of the month (I .. XII) |
| RR | The last 2 digits of the year. |
| RRRR | The last 2 digits of the year when used for output. Accepts fout-digit years when used for input. |
| SP | Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten" |
| SPTH | Spelled and ordinal format; 1 results in first. |
| TH | Converts a number to it’s ordinal format. For example 1 becoms 1st. |
| TS | Short time format. Depends on NLS-settings. Use only with timestamp. |
| TZD | Abbreviated time zone name. ie PST. |
| TZH,TZM | Time zone hour/minute displacement. |
| TZR | Time zone region |
| X | Local radix character. In America this is a period (.) |
以下のSELECTクエリは、文字入力として数字を受け入れ、形式指定子に従って数字を出力します。
SELECT TO_NUMBER('121.23', '9G999D99')
FROM DUAL
TO_NUMBER('121.23','9G999D99')
------------------------------
121.23
SELECT TO_NUMBER('1210.73', '9999.99')
FROM DUAL;
TO_NUMBER('1210.73','9999.99')
------------------------------
1210.73TO_DATE関数
この関数は文字値を入力として受け取り、同じ値にフォーマットされた日付を返します。 TO_DATE関数を使用すると、ユーザーは任意の形式で日付を入力でき、そのエントリをOracle 11gで使用されるデフォルトの形式に変換できます。
構文：
TO_DATE( string1, [ format_mask ], [ nls_language ] )format_mask引数は、データの外観を正確に表す一連の要素で構成され、単一引用符で入力する必要があります。
| Format Model | Description |
|---|---|
| YEAR | Year, spelled out |
| YYYY | 4-digit year |
| YYY,YY,Y | Last 3, 2, or 1 digit(s) of year. |
| IYY,IY,I | Last 3, 2, or 1 digit(s) of ISO year. |
| IYYY | 4-digit year based on the ISO standard |
| RRRR | Accepts a 2-digit year and returns a 4-digit year. |
| Q | Quarter of year (1, 2, 3, 4; JAN-MAR = 1). |
| MM | Month (01-12; JAN = 01). |
| MON | Abbreviated name of month. |
| MONTH | Name of month, padded with blanks to length of 9 characters. |
| RM | Roman numeral month (I-XII; JAN = I). |
| WW | Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
| W | Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
| IW | Week of year (1-52 or 1-53) based on the ISO standard. |
| D | Day of week (1-7). |
| DAY | Name of day. |
| DD | Day of month (1-31). |
| DDD | Day of year (1-366). |
| DY | Abbreviated name of day. |
| J | Julian day; the number of days since January 1, 4712 BC. |
| HH12 | Hour of day (1-12). |
| HH24 | Hour of day (0-23). |
| MI,SS | Minute (0-59). |
| SSSSS | Seconds past midnight (0-86399). |
| FF | Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'. |
| AM,PM | Meridian indicator |
| AD,BC | AD, BC indicator |
| TZD | Daylight savings information. For example, 'PST' |
| TZH,TZM,TZR | Time zone hour/minute/region. |
次の例では、文字列を日付に変換します。
SELECT TO_DATE('January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL;
TO_DATE('
---------
15-JAN-89一般的な機能
データベース内のNULL値を処理するには、一般関数を使用します。 一般的なNULL処理関数の目的は、NULL値を代替値に置き換えることです。 以下にこれらの機能を簡単に説明します。
NVL
NVL関数は、NULL値を代替値に置き換えます。
構文：
NVL( Arg1, replace_with )構文では、両方のパラメーターが必須です。 NVL関数は、すべての種類のデータ型で機能することに注意してください。 また、元の文字列と置換のデータ型は互換性のある状態である必要があります。 Oracleと同じまたは暗黙的に変換可能。
arg1が文字値の場合、oracleは置換文字列をarg1と互換性のあるデータ型に変換してから比較し、expr1の文字セットでVARCHAR2を返します。 arg1が数値の場合、Oracleは数値の優先順位が最も高い引数を決定し、他の引数をそのデータ型に暗黙的に変換し、そのデータ型を返します。
従業員がまだどのジョブにも割り当てられていない場合、以下のSELECTステートメントには「n/a」が表示されます。 JOB_IDはNULLです。 それ以外の場合は、実際のJOB_ID値が表示されます。
SELECT first_name, NVL(JOB_ID, 'n/a')
FROM employees;NVL2
NVLの機能強化として、OracleはNULL列値だけでなくNOT NULL列の値も置換する機能を導入しました。 NVL2関数を使用して、NULL値と非NULL値を代替値で置き換えることができます。
構文：
NVL2( string1, value_if_NOT_null, value_if_null )従業員のJOB_CODEがNULLの場合、以下のSELECTステートメントは「Bench」を表示します。 JOB CODEがnull以外の明確な値の場合、定数値「Job Assigned」が表示されます。
SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;NULLIF
NULLIF関数は、2つの引数expr1とexpr2を比較します。 expr1とexpr2が等しい場合、NULLを返します。それ以外の場合、expr1を返します。 他のNULL処理関数とは異なり、最初の引数はNULLにはできません。
構文：
NULLIF (expr1, expr2)最初の引数はNULLと評価される式にすることができますが、リテラルNULLにすることはできません。 両方のパラメーターは、関数を実行するために必須です。
以下のクエリは、両方の入力値12が等しいため、NULLを返します。
SELECT NULLIF (12, 12)
FROM DUAL;同様に、以下のクエリは両方の文字列が等しくないため「SUN」を返します。
SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;コーレス
NVLのより一般的な形式であるCOALESCE関数は、引数リストの最初の非ヌル式を返します。 最小2つの必須パラメーターを取りますが、最大引数には制限がありません。
構文：
COALESCE (expr1, expr2, ... expr_n )以下のSELECTクエリを検討してください。 従業員の住所フィールドに入力された最初の非ヌル値を選択します。
SELECT COALESCE (address1, address2, address3) Address
FROM employees;興味深いことに、COALESCE関数の機能はIF..ELSIF..ENDIFコンストラクトに似ています。 上記のクエリは次のように書き換えることができます-
IF address1 is not null THEN
result := address1;
ELSIF address2 is not null THEN
result := address2;
ELSIF address3 is not null THEN
result := address3;
ELSE
result := null;
END IF;条件関数
Oracleには、SQL文でも条件を課すための条件関数DECODEおよびCASEが用意されています。
DECODE関数
この関数は、IF..THEN..ELSE条件付き手続き文とSQLで同等です。 DECODEは、すべてのデータ型の値/列/式で機能します。
構文：
DECODE (expression, search, result [, search, result]... [, default])DECODE関数は、式を各検索値と順番に比較します。 式と検索引数が等しい場合、対応する結果を返します。 一致しない場合、定義されている場合はデフォルト値が返され、定義されていない場合はNULLが返されます。 型の互換性が一致しない場合、oracleは暗黙的に潜在的な変換を実行して結果を返します。
実際問題として、OracleはDECODE関数を使用している間、2つのNULLは同等であると見なします。
SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL')
FROM DUAL;
DECOD
-----
EQUALexpressionがnullの場合、Oracleは、nullである最初の検索の結果を返します。 DECODE関数のコンポーネントの最大数は255です。
SELECT first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
FROM employees;CASE式
CASE式はDECODEと同じ概念で機能しますが、構文と使用法が異なります。
構文：
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
ENDOracle検索は、左から始まり、真の条件が見つかるまで右に移動し、それに関連する結果式を返します。 条件に該当するものが見つからず、ELSE句が存在する場合、Oracleはelseで定義された結果を返します。 それ以外の場合、Oracleはnullを返します。
CASE式の引数の最大数は255です。 単純なCASE式の初期式およびオプションのELSE式を含め、すべての式はこの制限にカウントされます。 それぞれいつ… THENペアは2つの引数としてカウントされます。 この制限を超えないようにするために、return_expr自体がCASE式になるようにCASE式をネストすることができます。
SELECT first_name, CASE WHEN salary < 200 THEN 'GRADE 1'
WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2'
ELSE 'GRADE 3'
END CASE
FROM employees;
ENAM CASE
---- -------
JOHN GRADE 2
EDWIN GRADE 3
KING GRADE 1