Nvl SQL の説明。 SELECT コマンドの分岐ステートメント

入れ子関数については先ほど少し触れましたが、ここではもう少し詳しく見ていきます。 NULL 値を操作するための関数や、クエリでの分岐操作の実装に役立つ関数についても検討します。

入れ子関数

ネストされた関数は、ある関数の戻り値を別の関数への入力パラメーターとして使用します。 関数は常に 1 つの値のみを返します。 したがって、関数呼び出しの結果を別の関数呼び出しのパラメーターとして使用する場合、その結果を値リテラルとして扱うことができます。 文字列関数は、任意のネスト レベルにネストできます。 1 つの関数呼び出しは次のようになります

Function1(パラメータ1, パラメータ2, …) = 結果

関数パラメータを別の関数の呼び出しに置き換えると、次のような式が生じる可能性があります。

F1(param1.1, F2(param2.1, param2.2, F3(param3.1)), param1.3)

ネストされた関数は、その結果が他の関数の入力値として使用される前に最初に評価されます。 関数は、最も深いネスト レベルから最上位まで、左から右に評価されます。 前の式は次のように実行されます

1. 関数 F3(param1) が評価され、戻り値が関数 2 の 3 番目のパラメーターとして使用されます。これを param2.3 と呼びます。
2. 次に、関数 F2(param1, param2.2, param2.3) が評価され、戻り値が関数 F1 - param1.2 の 2 番目のパラメーターとして使用されます。
3. 最後に、関数 F1(param1, param2, param1.3) が評価され、結果が呼び出し側プログラムに返されます。

したがって、F3 関数は 3 番目の入れ子レベルにあります。

リクエストを検討してみましょう

デュアルから next_day(last_day(sysdate)-7, ‘tue’) を選択します。

1. このクエリには、下位レベルから上位レベルに向かって、SYSDATE、LAST_DAY、NEXT_DAY の 3 つの関数があります。 リクエストは次のように実行されます
2. ネストされた SYSDATE 関数が実行されます。 現在のシステム時刻を返します。 現在の日付が 2009 年 10 月 28 日だとします。
3. 次に、第 2 レベル関数 LAST_DAY の結果が計算されます。 LAST_DATE('28-OCT-2009') は、2009 年 10 月の最終日、つまり 2009 年 10 月 31 日を返します。
4. 次に、この日付から 7 日が減算され、10 月 24 日になります。
5. 最後に、関数 NEXT_DAY('24-OCT-2009', 'tue') が評価され、クエリは 10 月の最後の火曜日 (この例では 27-OCT-2009) を返します。

多くのネストされた関数呼び出しを使用する複雑な式を理解し、構築することは非常に困難ですが、これには時間と練習が必要です。 このような式を複数の部分に分割し、個別にテストできます。 DUAL テーブルは、クエリや関数呼び出しの結果をテストするのに非常に役立ちます。 小さなコンポーネントをテストおよびデバッグし、それらを 1 つの大きな目的の式に結合できます。

分岐機能

IF-THEN-ELSE とも呼ばれる分岐関数は、状況に応じて実行パスを決定するために使用されます。 分岐関数は、条件評価の結果に基づいて異なる結果を返します。 このような関数のグループには、NULL 値を操作する関数 (NVL、NVL2、NULLIF、COALESCE) が含まれます。 DECODE関数やCASE式に代表される共通関数も。 DECODE 関数は Oracle 関数ですが、CASE 式は ANSI SQL 標準に存在します。

NVL機能

NVL 関数は、任意のデータ型の列または式の値を NULL に対してテストします。 値が NULL の場合は、代替の非 NULL デフォルト値を返し、それ以外の場合は元の値を返します。

NVL 関数には 2 つの必須パラメーターがあり、構文は NVL(original, ifnull) です。original はチェックする元の値、ifnull は元の値が NULL の場合に関数によって返される結果です。 ifnull のデータ型と元のパラメータには互換性がある必要があります。 つまり、データ型が同じであるか、値をある型から別の型に暗黙的に変換できる必要があります。 NVL 関数は、元のパラメータのデータ型と同じデータ型の値を返します。 3 つのクエリを考えてみましょう

クエリ 1: デュアルから nvl(1234) を選択します。

クエリ 2: デュアルから nvl(null, 1234) を選択します。

クエリ 3: select nvl(substr(‘abc’, 4), ‘部分文字列が存在しません’) from Dual;

NVL 関数には 2 つのパラメータが必要なため、リクエスト 1 ではエラー ORA-00909: 引数の数が無効です。 NULL 値がチェックされ、NULL であるため、クエリ 2 は 1234 を返します。 クエリ 3 では、ネストされた SUBSTR 関数を使用して、3 文字長の文字列から 4 番目の文字を抽出しようとして NULL を返し、NVL 関数は文字列「No sbustring contains」を返します。

NVL 関数は、数値を扱うときに非常に便利です。 数値の算術演算が NULL を返さないように、NULL 値を 0 に変換するために使用されます。

NVL機能2

NVL2 関数は、NVL よりも多くの機能を提供しますが、NULL 値を処理する役割も果たします。 これは、任意の型の列または式の値を NULL に対してテストします。 値が NULL でない場合は 2 番目のパラメーターが返され、それ以外の場合は 3 番目のパラメーターが返されます。これは NVL 関数とは異なり、この場合は元の値が返されます。

NVL2 関数には 3 つの必須パラメータがあり、構文は NVL2(original, ifnotnull, ifnull) です。original はテストされる値、ifnotnull はオリジナルが NULL でない場合に返される値、ifnull はオリジナルが NULL である場合に返される値です。 ifnotnull パラメーターと ifnull パラメーターのデータ型は互換性がある必要があり、LONG 型にすることはできません。 NVL2 関数によって返されるデータ型は、ifnotnull パラメーターのデータ型と同じです。 いくつかの例を見てみましょう

クエリ 1: デュアルから nvl2(1234, 1, ‘a string’) を選択します。

クエリ 2: デュアルから nvl2(null, 1234, 5678) を選択します。

クエリ 3: デュアルから nvl2(substr(‘abc’, 2), ‘Not bc’, ‘No substring’) を選択します。

リクエスト 1 の ifnotnull パラメータは数値であり、ifnull パラメータは文字列です。 データ型に互換性がないため、エラー「ORA-01722: 無効な番号」が返されます。 クエリ 2 は、元の値が NULL で結果は 5678 になるため、ifnull パラメータを返します。クエリ 3 は、'bc' を返す SUBSTR 関数を使用し、NVL2('bc','Not bc','No substring') を呼び出します。 ifnotnull パラメータ – 「Not bc」。

NULLIF関数

NULLIF関数は2つの値が同一かどうかをチェックします。 それらが同じ場合は NULL が返され、そうでない場合は最初のパラメータが返されます。 NULLIF 関数には 2 つの必須パラメーターがあり、構文は NULLIF(ifunequal, COMPANY_ITEM) です。 この関数は 2 つのパラメータを比較し、それらが同一であれば NULL を返し、それ以外の場合パラメータは ifunequal を返します。 要望を検討してみよう

クエリ 1: デュアルから nullif(1234, 1234) を選択します。

パラメータが同一で​​あるため、クエリ 1 は NULL を返します。 クエリ 2 の文字列は日付に変換されず、文字列として比較されます。 文字列の長さが異なるため、ifunequal パラメータが 2009 年 7 月 24 日に返されます。

図 10-4 では、NULLIF 関数が NVL2 関数内にネストされています。 NULLIF 関数は、ifunequal パラメータの式の一部として SUBSTR 関数と UPPER 関数を使用します。 EMAIL 列はこの式と比較され、名が 4 文字である従業員の名前の最初の文字と姓を組み合わせたものを返します。 これらの値が等しい場合、NULLIF は NULL を返し、それ以外の場合は ifunequal パラメータの値を返します。 これらの値は、NVL2 関数のパラメーターとして使用されます。 NVL2 は、比較された要素が一致したかどうかの説明を返します。

図 10-4 – NULLIF 関数の使用

COALESCE機能

COALESCE 関数は、パラメーター リストから最初の非 NULL 値を返します。 すべてのパラメータが NULL の場合は、NULL が返されます。 COALESCE 関数には 2 つの必須パラメーターと任意の数のオプション パラメーターがあり、構文は COALESCE(expr1, expr2, ..., exprn) です。 expr 1 の値が NULL でない場合、結果は expr1 になります。それ以外の場合、結果は次のようになります。 expr2 (NULL でない場合など) COALESCE は、入れ子になった NVL 関数と同じ意味です。

COALESCE(expr1, expr2) = NVL(expr1, expr2)

COALESCE(expr1, expr2, expr3) = NVL(expr1,NVL(expr2, expr3))

非 NULL 値が見つかった場合に返される値のデータ型は、最初の非 NULL 値のデータ型と同じです。 「ORA-00932: 一貫性のないデータ型」エラーを回避するには、すべての非 NULL パラメータが最初の非 NULL パラメータと互換性がある必要があります。 3つの例を見てみましょう

クエリ 1: デュアルから coalesce(null, null, null, 'a string') を選択します。

クエリ 2: デュアルから coalesce(null, null, null) を選択します。

クエリ 3: select coalesce(substr(‘abc’, 4), ‘Not bc’, ‘No substring’) from Dual;

クエリ 1 は、最初の非 NULL パラメータであるため、4 番目のパラメータである文字列を返します。 すべてのパラメータが NULL であるため、クエリ 2 は NULL を返します。 クエリ 3 は、最初のパラメータを評価して NULL 値を取得し、2 番目のパラメータを返します。これは、最初の非 NULL パラメータであるためです。

すでに NVL 関数に精通している場合、NVL2 関数のパラメーターは混乱する可能性があります。 NVL(original, ifnull) は、値が NULL でない場合はオリジナルを返し、そうでない場合は ifnull を返します。 NVL2(original, ifnotnull, ifnull) は、オリジナルが NULL でない場合は ifnotnull を返し、それ以外の場合は ifnull を返します。 混乱は、NVL 関数の 2 番目のパラメーターが ifnull であるのに対し、NVL2 関数は ifnotnull であるという事実から生じます。 したがって、関数内のパラメーターの位置に依存しないでください。

デコード機能

DECODE 関数は、最初の 2 つのパラメーターが等しいかどうかをテストし、等しい場合は 3 番目の値を返し、等しくない場合は異なる値を返すことにより、if-then-else ロジックを実装します。 DECODE 関数には 3 つの必須パラメーターがあり、構文は DECODE(expr1, comp1, iftrue1, , ) です。 これらのパラメーターは、次の疑似コードの例に示すように使用されます。

expr1=comp1 の場合は iftrue1 を返します

それ以外の場合、expr1=comp2 の場合は iftrue2 を返します

それ以外の場合、exprN=compN の場合は iftrueN を返します

それ以外の場合は NULL|iffalse を返します。

まず、expr1 と comp1 が比較されます。 等しい場合は、iftrue1 が返されます。 expr1 が comp1 と等しくない場合、次に何が起こるかは、パラメータ comp2 および iftrue2 が指定されているかどうかによって異なります。 指定すると、expr1 の値が comp2 と比較されます。 値が等しい場合、iftrue2 が返されます。 そうでない場合は、パラメータ compN、iftrueN、expr1、および compN のペアが比較され、等しい場合は iftrueN が返されます。 どのパラメータ セットでも一致が見つからなかった場合は、このパラメータが指定されている場合は iffalse、または NULL が返されます。

DECODE 関数のすべてのパラメーターは式にすることができます。 戻り値の型は、最初の検証要素 (パラメーター) の型と同じです。 comp 1. 式 expr 1 は暗黙的に comp パラメータのデータ型に変換されます。1. 他のすべての利用可能なコンプパラメータ 1...補完 暗黙的に type comp にも変換されます 1. DECODE は、NULL 値を別の NULL 値と等しいものとして扱います。つまり、 expr1 が NULL、comp3 が NULL、comp2 が NULL でない場合、iftrue3 が返されます。 いくつかの例を見てみましょう

クエリ 1: デュアルからデコード (1234、123、「123 は一致」) を選択します。

クエリ 2: デュアルからデコード (1234、123、「123 は一致」、「一致なし」) を選択します。

クエリ 3: select decode('search', 'comp1', 'true1', 'comp2', 'true2', 'search', 'true3', substr('2search', 2, 6), 'true4', ' false') デュアルから;

クエリ 1 は、値 1234 と 123 を比較します。これらは等しくないため、iftrue1 は無視され、値 iffalse が定義されていないため、NULL が返されます。 リクエスト 2 は、値 iffalse が定義されていることを除き、リクエスト 1 と同じです。 1234 は 123 に等しくないため、iffalse (「一致しません」) を返します。 クエリ 3 は、パラメータ値が検索値と一致するかどうかをチェックします。 パラメータ comp1 と comp2 は「search」と等しくないため、iftrue1 と iftrue2 の結果はスキップされます。 要素 comp3 (パラメータ位置 6) の 3 番目の比較演算で一致が見つかり、「true3」に等しい iftrue3 (パラメータ 7) の値が返されます。 一致が見つかったので、それ以上の計算は実行されません。 つまり、comp4 (パラメータ 8) の値も expr1 と一致しますが、前の比較で一致が見つかったため、この式は計算されません。

CASE式

すべての第 3 世代および第 4 世代のプログラミング言語は case 構造を実装します。 DECODE 関数と同様に、CASE 式を使用すると、if-then-else ロジックを実装できます。 CASE 式を使用するには 2 つのオプションがあります。 単純な CASE 式は、一度比較するソース要素を設定し、必要なテスト条件をすべてリストします。 複雑な (検索された) CASE は、条件ごとに両方のステートメントを評価します。

CASE 式には 3 つの必須パラメータがあります。 式の構文は型によって異なります。 単純な CASE 式の場合は次のようになります

CASE検索式

WHEN 比較式 1 THEN iftrue1

)

TRUNC 関数は、小数点以下 m 桁に切り捨てられた数値 n を返します。 m パラメータは指定できません。この場合、n は整数に切り捨てられます。

SELECT TRUNC(100.25678) X1、TRUNC(-100.25678) X2、TRUNC(100.99) X3、

TRUNC(100.25678, 2) X4

デュアルから

関数 SIGN(n)

SIGN 関数は数値の符号を決定します。 n が正の場合、関数は 1 を返します。n が負の場合、関数は -1 を返します。 ゼロに等しい場合は、次のように 0 が返されます。

SELECT SIGN(100.22) X1、SIGN(-100.22) X2、SIGN(0) X3

デュアルから

この関数の興味深い特徴は、0 による除算エラーを引き起こすことなく、0 に等しい m を送信できることです。

関数 POWER(n, m)

POWER 関数は、数値 n の m 乗を行います。 次数は小数または負にすることができ、これによりこの関数の機能が大幅に拡張されます。

セレクトパワー(10, 2) X1、パワー(100, 1/2) X2、

パワー(1000、1/3)×3、パワー(1000、-1/3)×4

デュアルから

場合によっては、この関数を呼び出すときに例外が発生することがあります。 例えば：

セレクトパワー(-100、1/2)×2

デュアルから

この場合、負の数の平方根を計算しようとすると、ORA-01428「引数が範囲外です」エラーが発生します。

関数 SQRT(n)

この関数は数値 n の平方根を返します。 例えば：

SELECT SQRT(100) X

デュアルから

EXP(n) および LN(n) 関数

EXP 関数は e の n 乗を行い、LN 関数は n の自然対数を計算します (n は 0 より大きい必要があります)。 例：

SELECT EXP(2) X1、LN(1) X2、LN(EXP(2)) X3

NVL機能

一般に NVL 関数が最も頻繁に使用されます。 この関数は 2 つのパラメータ NVL(expr1, expr2) を受け取ります。 最初のパラメータ expr1 が NULL でない場合、関数はその値を返します。 最初のパラメータが NULL の場合、関数は代わりに 2 番目のパラメータ expr2 の値を返します。

実際の例を見てみましょう。 EMP テーブルの COMM フィールドには NULL 値を含めることができます。 次のようなクエリを実行するとき:

SELECT EMPNO、ENAME、COMM、NVL(COMM, 0) NVL_COMM

スコットからのEMP

NULL 値はゼロに置き換えられます。 関数を使用して値を生成すると、その値にエイリアスが割り当てられることに注意してください。 クエリの結果は次のようになります。

関数 CEIL(n)

CEIL 関数は、パラメーターとして渡された数値 n 以上の最小の整数を返します。 例えば：

選択 CEIL(100) X1、CEIL(-100) X2、CEIL(100.2) X3、CEIL(-100.2) X4

デュアルから

関数TRUNC(n [,m])

TRUNC 関数は、小数点以下 m 桁に切り捨てられた数値 n を返します。 m パラメータは指定できません。この場合、n は整数に切り捨てられます。

SELECT TRUNC(100.25678) X1、TRUNC(-100.25678) X2、TRUNC(100.99) X3、

TRUNC(100.25678, 2) X4

デュアルから

関数 SIGN(n)

SIGN 関数は数値の符号を決定します。 n が正の場合、関数は 1 を返します。n が負の場合、関数は -1 を返します。 ゼロに等しい場合は、次のように 0 が返されます。

SELECT SIGN(100.22) X1、SIGN(-100.22) X2、SIGN(0) X3

デュアルから

この関数の興味深い特徴は、0 による除算エラーを引き起こすことなく、0 に等しい m を送信できることです。

関数 POWER(n, m)

POWER 関数は、数値 n の m 乗を行います。 次数は小数または負にすることができ、これによりこの関数の機能が大幅に拡張されます。

セレクトパワー(10, 2) X1、パワー(100, 1/2) X2、

パワー(1000、1/3)×3、パワー(1000、-1/3)×4

デュアルから

場合によっては、この関数を呼び出すときに例外が発生することがあります。 例えば：

セレクトパワー(-100、1/2)×2

デュアルから

この場合、負の数の平方根を計算しようとすると、ORA-01428「引数が範囲外です」エラーが発生します。

関数 SQRT(n)

この関数は数値 n の平方根を返します。 例えば：

SELECT SQRT(100) X

デュアルから

EXP(n) および LN(n) 関数

EXP 関数は e の n 乗を行い、LN 関数は n の自然対数を計算します (n は 0 より大きい必要があります)。 例：

SELECT EXP(2) X1、LN(1) X2、LN(EXP(2)) X3