カスタムSQL構文機能の例

はじめに

約1年半前に、OCP Advanced PL / SQL Developerの試験に合格した後、作業の仕様がわずかに変わり、Oracleの標準的な本番使用後、コンピューター言語のニーズに合わせてOracleに基づく2レベルのクライアントサーバーシステムのアーキテクチャを開発していました。 次は、システムの開発とそれに基づいたハイテクタスクを解決する段階でした。非標準の問題やその他の特定の問題を解決する際に、階層クエリを使用する必要がありました。 詳細を深めた結果、基地の特定の「沈下」が生じました。これは、試験の準備に使用した資料を再び見る時が来たことを意味します。



以下は、SQLクエリの非標準的な使用例です。 そのような例は、通常、要約を見ながら思い浮かび、チェックされ、他の専門家と議論され、忘れられます。 今回はそれらのいくつかをsqlファイルに保存したかったのですが、後に各リクエストのコメントの必要性が明らかになりました。 そこで、このメモが登場しました。

演算子による順序の詳細についてのいくつかの言葉

このリクエストを実行した結果、エラーが発生すると思いますか？

-- №1 select * from dual order by 1+2||dummy
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

回答：エラーはありません。 仕様による順序は次のように定義されます。

ORDER BY {col（s）|式| numeric_pos} [ASC | DESC] [NULLS FIRST | LAST];

Where式-数値、文字列、日時式などのソートキー式。 式は、スカラーサブクエリやケース式などの行値式にすることもできます。

詳細については、たとえばこちらからダウンロードしてください。oracle.com/ javadb / 10.6.2.1 / ref / rrefsqlj13658.html



したがって、二重テーブルを式「3X」でソートしましたが、これは無意味ですが、たとえば、式をsubstr関数とともにソート用の式として使用できます。 式を使用する能力は重要です。



知っているように、デュアルテーブルには、次のクエリがエラーを返すかどうかに関係なく、1つの列が含まれます。

 -- №2 select * from dual order by 2
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そしてこれ？

 -- №3 select * from dual order by 2+0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最初のケースでは、列の位置表示を扱っています-つまり それぞれ存在しない列2を参照すると、エラーが発生します。



2番目のケースでは、式を処理しています。 これは列番号ではなく、クエリ番号1の「3X」行によるソートと同様に、番号2によるソートです。 したがって、エラーは発生しません。



次の例を見てください。 このクエリの結果としてどのような行順序を取得しますか？ 間違いはありますか？

 -- №4 select 3, dummy from dual union all select 2, dummy from dual order by "3"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ユニオンはすべて、ユニオンセットの行をソートしません（ユニオンとは異なります）。 order byなしで、リクエストで指定された順序で行を取得します（すべての結合は元の行の順序の保持を保証します）。 さて、順番が変わりました。主な質問は、この場合の「3」とは何ですか？ 「単一引用符ではなく」二重引用符を使用しているため、列のエイリアスは3です。ご存じのように、セットの操作では類似のデータ型を使用する必要があります。最初の列の名前を明示的に指定しなかったため、列名は最初のクエリから取得されます、その後、デフォルトで式の名前、つまり「3」を受け取りました。このようなエイリアスの使用は、たとえばリクエストNo. 5に示されています（主なことはアプリのケースを忘れないことです）。



デフォルトのソートは常にasc、つまりです。 クエリ結果＃4は、最初の列の昇順で並べ替えられます。 結果：行「2、X」、次に「3、X」。

 -- №5 select substr(dummy,1,1) from dual order by "SUBSTR(DUMMY,1,1)"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

セットでクエリNo. 3から実験を繰り返しましょう。 クエリの結果はどうなりますか？

 -- №6 select 3, dummy from dual union all select 2, dummy from dual order by 2+0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリの結果は次のように見えるかもしれません：行「3、X」、次に「2、X」。 これの前提条件は次のとおりです。この順序はすべてを結合することを保証し、式2 + 0はレコードのソートに影響しません（クエリNo. 7に示すように）。 したがって、1つの「しかし」ではない場合：セットを使用した操作では、最初のクエリの名前または列番号を含む複合クエリの最後でのみorder byを使用でき、式は許可されません。

セット演算子UNION、INTERSECT、MINUS、またはUNION ALLを含む複合クエリの場合、ORDER BY句は明示的な式ではなく位置またはエイリアスを指定する必要があります。 また、ORDER BY句は、最後のコンポーネントクエリでのみ表示できます。 ORDER BY句は、複合クエリ全体によって返されるすべての行を順序付けます。 download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries005.htm#i2053998



したがって、クエリ番号6はエラーを返します。

 -- №7 select * from (select 3, dummy from dual union all select 2, dummy from dual) order by 2+0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

使用しない

確かに、さまざまなコースを経験したほとんどの人は、not in演算子を避ける必要があることを覚えており、inまたはexists演算子を使用して同様の機能を取得できます。 否定的な態度をとらない理由は、ヌル値を伴う彼の作品の詳細にあります。



次のクエリの結果を決定します。

 -- №1 select * from dual where 1 not in (select 2 from dual union select null from dual)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

まず、次のクエリを検討してください。

 -- №2 select * from dual where 2 in (select 2 from dual union select null from dual)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリ番号2に異常はありません。サブクエリは、値が「2」と「null」の2行のセットを返します。trueの条件は、クエリ番号2全体が1行を返します-標準動作です。



次に、inとnotの動作の違いを考えます。ここでは、null算術の知識が必要です。

1）nullの算術演算はnullを返します

2）nullのブール演算子は次のように機能します。

• null AND false = false
• false AND null = false
• null AND true = null
• trueおよびnull = null
• nullまたはtrue = true
• trueまたはnull = true
• nullまたはfalse = null
• falseまたはnull = null

これまでのところ、すべてが論理的です。 次に、クエリ＃2でINがどのように機能するかを検討します。

2 IN（2、null）=>（2 = 2）OR（2 = null）=> true OR null => true

クエリ番号1でのNOT INの動作を考えます。

1 NOT IN（2、null）=>（1！= 2）AND（1！= Null）=> true AND null => null



したがって、少なくとも1つのオペランドがnullの場合、クエリ＃1のwhere句はnullに変換されるため、クエリ＃1全体は次のクエリとほぼ同等になります。

 -- №3 select * from dual where null=null
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

明らかに、クエリNo. 3はそれぞれ単一の行を返しません。クエリNo. 1も単一の行を返しません。

暗黙的な型変換

明示的および暗黙的な型変換のトピックは非常に広範囲であるため、全体としてカバーしようとせずに、1つの例を検討したいと思います。 今日10.09.11 10:00:00とOracleがDD.MM.RR形式がデフォルトで認識されるように構成されているとしましょう。どのクエリが単一行を返しますか？

 -- №1 select * from dual where sysdate>'10.09.11'; -- №2 select * from dual where sysdate||''='10.09.11';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

答えは両方です。 なぜこれが一般的に可能ですか？ 答えは、暗黙的な型変換のメカニズムにあります。



クエリ番号1を検討します。この場合、日付を文字列と比較します。この場合、文字列形式がデフォルトの日付形式のいずれかに一致する場合、Oracleは文字列を日付に変換しようとします

nls_session_parametersから*を選択します）。 文字列形式がデフォルトの日付形式に対応していない場合、エラーが発生します。 この場合、フォーマットは対応しており、sysdate = 09/10/11 10:00:00であるため、文字列'10 .09.11 'は09/10/11 00:00:00の日付に変換され、クエリNo. 1は1行を返します。



クエリ番号2を検討します：日付が文字列と連結されている場合、そのような操作の結果は文字列になり、日付が暗黙的に文字列に変換されると、データはDD.MM.RR形式に切り捨てられます。 2つの行「10 .09.11」と「10 .09.11」を比較します。 その結果、where句が満たされ、クエリは単一の行を返します。

おわりに

このレビューは、Oracle機能の範囲を提供するふりをするものではなく、楽しみのために作成されたものであり、SQL構文機能の非標準使用のいくつかのケースを示すことを目的としています。