FC2ブログ

社内SEの徒然なる日記

SQL(Oracle)のUPDATE文でWITH句を使う!

■ UPDATE

前回(SQL(Oracle)のINSERT文でWITH句を使う!)、INSERT文でWITH句を使えたので、UPDATE文でも使えるのか試してみました。SQLリファレンスによると出来そうな感じなのですが、どうですかね?

環境については、前回のINSERT文の記事と同じです。

まず、普通のUPDATE文です。抽出条件にSELECT文を使用しています。
-- 普通のUPDATE
UPDATE TESTTBL
SET STR1 = '1',NUM1 = 1
WHERE
STR1 =
(
SELECT '1' AS STR1
FROM DUAL
)


■ UPDATEでWITH句

WITH句をねじ込んでみました。どうなるかと思ったのですが、これは問題なく動いてくれました。
-- UPDATEでWITH句
UPDATE TESTTBL
SET STR1 = '1',NUM1 = 1
WHERE
STR1 =
(
WITH A AS
(
SELECT '1' AS STR1
, 1 AS NUM1
FROM DUAL
)
SELECT STR1
FROM A
)


■ 後書き

興味があったので実験はしてみましたが、UPDATEの条件指定でWITH句を使うような複雑なSELECT文を書いたことが無いので、使うケースがあるのか微妙です。

何というか、これが発生するようなら設計レベルで間違えている気がします。

ただ、何でも出来るわけではなさそうで、更新値の設定にWITH句を使ってみたのですが・・・
-- UPDATEのSETにWITH句
UPDATE TESTTBL
SET
(STR1,NUM1)
=
(
WITH A AS
(
SELECT '1' AS STR1
, 1 AS NUM1
FROM DUAL
)
SELECT STR1
,NUM1
FROM A
)
WHERE STR1 = '1'


結果、ORA-01767(UPDATE ... SET 式は副問合せである必要があります) が返ってきました。

WITH句を使わずに副問い合わせに変えたら通るようになったので、ここでWITH句を使うのは無理そうです。

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end ---
スポンサードリンク

PageTop

SQL(Oracle)のINSERT文でWITH句を使う!

■ INSERT〜SELECT

Oracleでデータを登録する時に、SELECT文の抽出結果を登録する方法を良く使うのですが、少し複雑なSQL文(具体的には、副問い合わせを多用するようなもの)を書こうとすると副問い合わせがネストして後で追いにくくなります。

以前からWITH句を使えたらなぁって思っていたのですが、使い方が間違っていただけで普通に使えることに気が付きました。

テーブル「TESTTBL」(文字項目「STR1」、数値項目「NUM1」)という架空のテーブルに抽出結果を登録します。

抽出元はDUAL表を使用しました。

まず、普通のINSERT文。
-- 普通のINSERT〜SELECT
INSERT INTO TESTTBL
(STR1,NUM1)
SELECT '1' AS STR1
, 1 AS NUM1
FROM DUAL


副問い合わせを使用。
-- 副問い合わせを使用したINSERT〜SELECT
INSERT INTO TESTTBL
(STR1,NUM1)
SELECT A.STR1
,A.NUM1
FROM
(
SELECT '1' AS STR1
, 1 AS NUM1
FROM DUAL
) A


■ INSERTでWITH句

そして、これが本題のWITH句を使用したINSERT文です。
-- WITH句を使用したINSERT〜SELECT
INSERT INTO TESTTBL
(STR1,NUM1)
WITH A AS
(
SELECT '1' AS STR1
, 1 AS NUM1
FROM DUAL
)
SELECT STR1
,NUM1
FROM A


・・・WITH句って文の先頭にあるべきって思い込んでいました。

■ 後書き

改めて、OracleのSQLリファレンスを読み返してみたのですが、INSERT文の肝心の部分って「subquery::=」となっていて、SELECTの構文を参照みたいになっていました。

んで、SELECT文の構文にはWITH句が記載されている訳でして。





ちなみに、手元にあったマニュアルがOracle9iという昔々のものだったのですが、その時代から既に使えたようです。

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end ---
スポンサードリンク

PageTop

OracleでDISTINCTを追加したらORA-01791が発生【解決】

■ 重複の除去

データを抽出して一覧を表示をする画面で設計ミスがあったらしく、表示結果が重複表示されるケースが出てきました。

調べてみると原因は分かったのですが、それを直そうと思ったら結構面倒なSQLの書き換えが必要になってしまいます。そこで、DISTINCTを追加して抽出結果から重複を除去することにしました。

ところが、実際にSQL文を実行したところ、ORA-01791(SELECT式が無効です)が発生して異常終了してしまいました。

■ 原因

調べてみると、何でもソート順に指定している項目が抽出項目になっていない場合に発生するそうです。

例えば、下記のような書き方をすると同じエラーが発生します。

SELECT DISTINCT
ITEM1
FROM (
SELECT 'A' AS ITEM1
,'B' AS ITEM2
FROM DUAL
UNION ALL
SELECT 'A' AS ITEM1
,'B' AS ITEM2
FROM DUAL
)
ORDER BY
ITEM1
,ITEM2


ITEM2という項目がORDER BY句にはあるが、SELECT句にはありません。こういう書き方はダメってことらしいです。

■ 対応

手っ取り早い対応は、SELECT句にORDER BY句で指定した項目を追加することでしょうか。上記のサンプルの場合、下記のようにITEM2を追加することでSQLが通るようになりました。

SELECT DISTINCT
ITEM1
,ITEM2
FROM (
SELECT 'A' AS ITEM1
,'B' AS ITEM2
FROM DUAL
UNION ALL
SELECT 'A' AS ITEM1
,'B' AS ITEM2
FROM DUAL
)
ORDER BY
ITEM1
,ITEM2


後は、逆にORDER BYからITEM2を消すという方法もあるかと思います。そのソート条件、本当に必要なのって業務的な確認は必要ですけどね。

なお、DISTINCTを消してGROUP BY句で重複を除去とかやってもダメで、ORA-00979(GROUP BYの式ではありません。)が発生します。

■ 後書き

テーブルを結合した結果、抽出結果が重複するというのは良くある話です。その対応方法も色々とあるのですが、私が行ったDISTINCTで重複を除去するというのは邪道かと思っています。

ただ、今回の場合は抽出結果を画面上に表示するだけで、そこから何か(更新処理とか)に連携するというものでもなかったので、別に良いかなと。そもそも、ソート順に指定する項目が抽出結果にないっていう書き方の時点で、ねぇ。

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end ---
スポンサードリンク

PageTop

Oracleのロックの確認とセッションの切断をしてみた【成功】

■ 処理が終わらない

ある日、ユーザーから伝票の更新処理がいつまでたっても終わらないと連絡が来ました。Webシステムと言う関係上、たまにサーバとの切断が切れて同じような現象になったりするのですが、伝票画面を開きなおしても状況が変わらないそうです。

結局、DB(Oracle)でロックが掛ったままになっていたことが原因のようなので、強制的にセッションを切断することにしました。

■ ロックの確認

まず、ロックされているセッションの一覧を表示します。ロックは、V$LOCKED_OBJECTから探せるようです。後でセッションを切断するのに必要な情報(セッション識別子とシリアル番号)は、V$SESSIONから取得します。

-- Oracleのロックを取得するSQL
SELECT
TO_CHAR(S.LOGON_TIME,'YYYY/MM/DD HH24:MI:SS') AS ログイン日時
,L.ORACLE_USERNAME AS Oracleユーザー名
,S.SID AS セッション識別子
,S.SERIAL# AS シリアル番号
FROM
V$LOCKED_OBJECT L
,V$SESSION S
WHERE
L.SESSION_ID = S.SID
ORDER BY
1
,3


もう少し工夫すれば対象のオブジェクトの絞り込みも出来るのですが、私が管理しているシステムは、日中に長時間かかる処理が存在しません。なので、ログイン日時が分れば無駄にロックされているものが分かります。

結果は、こんな感じ。
Oracleのロックの解除

この時の時間は10時頃。DBサーバーは毎日再起動しています。その条件下で見ると、7時のロックが残っているのはちょっと変です。どうやら犯人が見つかったようです。

■ ロックの解除

私はOracle(というかシステム全般)を毎日再起動するようにしています。メーカーなどは、最近は起動しっぱなしでも問題無いと言いますが、私に言わせれば嘘も大概にしろと言いたいです。

実際に、こういう問題が発生するし、そうなっても再起動しておけば対応出来るエンジニアがいなかったとしても、翌日にはロックが解除(DBが再起動されるので、自動的にそうなる)されて問題が解消されます。

なので、放置しても良かったのですが、この日は時間(と心と体に余裕)があったので、手動でロックを解除しました。

-- セッションを切断する
ALTER SYSTEM KILL SESSION
'272, 50867' -- セッション識別子, シリアル番号
IMMEDIATE


ロックの解除は、ALTER SYSTEM KILL SESSION を使用します。上記のように、V$SESSIONから取得したセッション識別子とシリアル番号を指定して実行すれば、それでOKです。

■ 後書き

Oracleのバージョンは、Oracle12cです。2017年頃のサーバーリプレースに合わせて当時の最新バージョンにしたのですが、その辺りからロックが解除されない事象が稀に発生するようになりました。

プログラムは変えていないので、Oracle側の何らかのバグのような気がします。ただ、調査を始めると手間暇かかるし、パッチを当てても、それで新たな問題が発生する気もするので、このまま塩漬けにしようかと。

感覚的な話ですが、どうもOracle12cはバグが多い気がします。

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end ---
スポンサードリンク

PageTop

年に一度の年次処理(そして異常終了)

■ メール

私が管理しているシステムは、夜間の処理で異常があるとメールが送信されます。はい、本日も深夜にメールが送信されました。

・・・眠い。

システムが稼働してから既に15年以上になり、随時メンテナンスを続けているので滅多なことでは異常終了もしなくなってきたのですが、それでも出る時は出ますね。

■ 年次処理

勤め先の決算は3月末です。なので4月の上旬に年に一度の処理が夜間に実行されるのですが、見事にそれが異常終了しました。困ったものです。

結局出社して対応したのですが、コロナが社会問題になっている昨今の情勢なら、遠隔操作で自宅から対応できるようにした方が良いかもしれません。

・・・眠い。

■ 後書き

うん、改めて見直すと文章に中身がない上にボロボロです。いや、障害対応自体は思ったほどの時間は掛からなかったのですが、興奮したのか寝付けなかったのです。

ちなみに、異常終了したのは年に一度だけ作成されていた帳票の出力処理です。違和感を感じる文章かと思いますが、これはシステム上は出力していたのですが、誰も使っていなかったという良くあるアレです。

今までは放置していたのですが、睡眠を邪魔されてムカついたので処理そのものを削除してやりました。これで来年からは安心です。

最新の記事:http://harikofu.blog.fc2.com/
投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end — スポンサードリンク

PageTop