分析関数 OVER() とWHEREに関して

たまに、SUM() OVER() や　MAX() OVER()　などの分析関数を使いますが
SUM() OVER() 集計値でWHERE句に記述した条件のものって含むのかなとか迷ったりしますが、WHERE句の条件で対象外のデータは集計値に含まれませんね。

具体例としては

SELECT
A
,B
,SUM(C) OVER (PARTITION BY D,E)　SUMROW
FROM TABLE_SAMPLE
WHERE F = 1

の場合ならSUMROWの値はTABLE_SAMPLEのF列が1のD列,E列ごとのC列の集計値となります。

フェッチ・アクロス・コミット （fetch across commit）を回避

カーソルのフェッチ中にトランザクション処理（COMMIT,ROLLBACK）をおこなうとフェッチ・アクロス・コミット （fetch across commit）というのでエラーとなってしまいます。

そのような処理がある場合の回避方法としては、

カーソルで取得したデータを1度配列の変数に代入してから、その配列変数をループさせるとうまく回避できます。

配列の変数もカーソル%TYPEにしてしまえば、ほぼカーソルでループしているようなものですから

ただ、2回ループするはめになってしまうので重い処理（ループ回数が多いなど）では遅くなってしまうかもしれません。

もっと、うまく回避できる方法があるのかもしれませんがすぐに思いつく方法としてはこれくらいかなと思います。

あとは自立型トランザクションってのをうまく使えば回避できる方法があるかもしれません。

GROUPING

オラクルでROLLUPとセットで使うのが便利なGROUPING
です。

これでその行が指定の列でグループ化されたものなのがわかります。

グループ化された列の場合は１
そうでない場合は2を返します。

意外と重宝します

PL/SQLでBREAK処理

PL／SQLでブレイク処理するときに、前レコードを保持して、比べるという方法がメジャーかと思いますが

よくよく考えたらLEADってのを使えば、前レコードと比べる必要がないですね。

忘れがちで、私自身もLEADを使えばよかったと感じることもしばしば。

LEADなひとつ前のレコードとかひとつ先のレコードを取得できるので、どこでブレイクするか明白です

FULL JOIN について

oracleの結合でFULL JOIN というものがあります。

簡単に言うとLEFT JOIN とRIGHT JOINを足したような感じですが、

プロジェクトによっては

結合の記述をJOIN（LEFT JOIN ,RUGHT JOIN）ではなく、(+)を使って結合するというルールになっている場合もあります。

で、FULL JOINの記述をしないで同じことを実現しようと思ったら、冗長なコードになってしまいますね…

いまだに、(+)で結合を標準にしているのはどうかなと思える理由がひとつ増えました。

くれぐれも、新しく規約を作る型は結合はJOINでするようにしてほしいなと思います。

主な理由は

JOINのほうが結合条件と検索条件を見分けやすい。
どのテーブルを結合しているのか見やすい。
FULL JOINが使える。
他のSQL言語でも使える。
といったところかな？

oracleでSQLを実行するソフト

オラクルデータベースでSQL実行して結果を見てみたいというとき

どんなソフトを使ってますか？？

オブジェクトブラウザやosqleditを使っている人が多いのではないでしょうか？？

ただ、状況によっては外部からソフトをインストールできなくて、これらをPCにいれることができない場合もあります。

そんなときは、たいていSQLPlusを使うのですが、

ちょっと待った！！

もしかしたら、SQLPlus WorkSheetってのが入ってるかもしれませんよ。

こっちのが、SQLPlusよりは使い勝手が良いので確認をおすすめします。

案外、オラクルが提供してくれているソフトの使い方とか知らないものです。

仕事で開発するときは、もっと便利なソフトがはいっていたりしますし、

環境は既に構築済みであったりしますから。

かくいう私も、TNSの追加を始めてソフトからしました。

名前はNetなんとかだったけど、忘れちゃいました。

じゃあ、普段はどうしてたかというと、そうです。

oranames.tns（←うる覚えなのでファイル名間違ってるかも）を直接
、テキストで開いて編集してました。

半角と全角

基本的なことですが、SQL文を書いているときに

半角で書かないといけないものを全角で記述してエラーとなってしまう凡ミスがたまにあります。

特に()は全角と半角の区別がしづらい。

コピペしたときなどとか…

なのでSQL文の文法が間違っているのではなくこういった凡ミスもあるので

気をつけましょう。

NULLの並び順

ORDER BY句でNULLのデータを前か後ろのどちらかに並べたい場合

ORDER BY 列名 NULLS FIRST
でNULLを前に

ORDER BY 列名 NULLS LAST
でNULLを後に

できます。

たまに使える

SQL Plusで表示する行の長さを調整

Set lin(esize) 設定したい数値

で設定可能

SELECT文の結果表示が格段に見やすくなります

linesizeはlinと省略可能

SQL Plus で出力結果を表示する

oracle SQLでPL/SQLの出力結果（DBMS_OUTPUT.PUTLINEなど）を表示するには

SQL Plusのコマンド

SET serveroutput On;

とすれば、表示されるようになります。

上記のコマンドがなくともSELECT文の結果は表示されますね。