Oracle9iから、副問い合わせに名前を付けて先頭に書く構文が追加されたらしい。[2007-10-15]
FROM句に副問い合わせを書くよりも、このwith句を使う方が分かり易くなるのでとても便利。
SELECT 〜 FROM ( SELECT文 ) vw_a ; |
→ |
with vw_a as ( SELECT文 ) vw_aを使ったSQL文 ; |
with vw_a as ( SELECT文 ), vw_b as ( SELECT文 ) vw_aとvw_bを使ったSQL文 ; |
||
なお、定義したビューを使わないでいるとエラーになる。[2007-10-16]
末尾のSQLを削ってテストしたりするから、別にエラーにしないで無視してくれりゃいーのに。せいぜい警告程度で…(嘆)
SQL> with a as (select 123 from dual)
2 select 456 from dual;
with a as (select 123 from dual)
*
行1でエラーが発生しました。:
ORA-32035: WITH句に参照されないクエリー名が定義されました
SQL> with a as (select 123 from dual)
2 select * from emp;
select * from emp
*
行2でエラーが発生しました。:
ORA-32035: WITH句に参照されないクエリー名が定義されました
SQLの形によって、エラーの示される場所が違うのがやっかい…。
with句のクエリー名の後ろに、そのクエリーのカラム名を定義することが出来る。[2020-05-12]
| カラム名定義なしの例 | カラム名定義ありの例 |
|---|---|
with w1 as ( |
with w1(c1, c2, c3) as ( |
with句で、そのSQL文でしか使用しないPL/SQLの関数（function）・プロシージャー（procedure）を定義することが出来る。[2020-05-12]
with
function f1(arg number) return number is
begin
if arg is null then
return 0;
elsif arg < 0 then
return -1;
end if;
return arg;
end;
select f1(9), f1(null), f1(-9) from dual;
/
※SQL*Plusから実行しようとしたら、何故か最後に「/」が必要だった。
↓実行結果
F1(9) F1(NULL) F1(-9)
---------- ---------- ----------
9 0 -1
with句で指定したクエリー内で、再帰的にそのクエリーを呼び出すことが出来る。[2020-05-12]
これにより、木構造の（親子関係を持った）データに対し、再帰的に子孫を辿る（探索する）ことが出来る。
（PostgreSQLでは、再帰withを使う場合は「with recursive」と書く必要があるが、Oracleでは不要（普通にwithのみでよい））
create table test(
id varchar2(4) not null,
parent_id varchar2(4),
name varchar2(20),
primary key(id)
);
-- ID PARENT, NAME
insert into test values('root', null, 'root');
insert into test values('c1', 'root', 'child1');
insert into test values('g11', 'c1', 'grandchild11');
insert into test values('g12', 'c1', 'grandchild12');
insert into test values('c2', 'root', 'child2');
insert into test values('g21', 'c2', 'grandchild21');
with
tree(id, name, deep_level)
as (
select id, name, 0
from test
where parent_id is null
union all
select t.id, t.name, r.deep_level+1
from test t, tree r
where t.parent_id = r.id
)
select * from tree;
再帰withでは、union allで2つのselect文をつなぐ。（union
all以外でも2つクエリーを結合する演算なら使えるようだが、実際に使う事はまず無いと思われる）
1つ目のselect文は、初期データ（木構造の親）を取得するためのもの。
2つ目のselect文は、子のデータを取得するためのもの。（再帰的に実行される）
↓実行結果
ID NAME DEEP_LEVEL ------------ ------------------------------------------------------------ ---------- root root 0 c1 child1 1 c2 child2 1 g11 grandchild11 2 g12 grandchild12 2 g21 grandchild21 2
再帰withでは、search句によって深さ優先探索か幅優先探索かを指定することが出来る。
また、search句ではsetによって「探索された順番を保持するカラム」を定義できる。
with
tree(id, name, deep_level)
as (
select id, name, 0
from test
where parent_id is null
union all
select t.id, t.name, r.deep_level+1
from test t, tree r
where t.parent_id = r.id
)
search depth first by id set order1
select * from tree
order by order1;
↓実行結果
ID NAME DEEP_LEVEL ORDER1 ------------ ------------------------------------------------------------ ---------- ---------- root root 0 1 c1 child1 1 2 g11 grandchild11 2 3 g12 grandchild12 2 4 c2 child2 1 5 g21 grandchild21 2 6
〜
search breadth first by id set order1
〜
↓実行結果
ID NAME DEEP_LEVEL ORDER1 ------------ ------------------------------------------------------------ ---------- ---------- root root 0 1 c1 child1 1 2 c2 child2 1 3 g11 grandchild11 2 4 g12 grandchild12 2 5 g21 grandchild21 2 6
（Oracle9.2.0.1.0）with句で定義したビューをFULL OUTER JOINするとバグる。[2007-10-16]
自分が遭遇したバグの現象としては「ORA-03113」と「ORA-00604とORA-00904の組み合わせ」。
with * 行1でエラーが発生しました。: ORA-03113: 通信チャネルでend-of-fileが検出されました
ORA-00604: 再帰SQLレベル1でエラーが発生しました。 ORA-00904: "from$_subquery$_007"."COL_NAME_0_0": 無効な識別子です。
後者は単純なSQLとしては再現できなかった（再現させる気が起きなかった）が。
withにdistinct等を使って絞り込むなどしたビューを5つくらい定義しており、途中で2つのビューをFULL OUTER JOINしたときに発生。
前者は以下のような単純なSQL文で再現。
with a as ( select 10 as key,'ABC' as data from dual union select 20 as key,'ABC' as data from dual ), b as ( select 20 as key,'DEF' as data from dual union select 30 as key,'DEF' as data from dual ) select a.*, b.* from a full outer join b on a.key=b.key ;
full joinでなく、inner joinやleft join／right joinのときは問題が起きない。
また、以下のように通常の副問い合わせの形式だと正しく結果が表示される。
SQL> select a.*, b.* 2 from 3 ( 4 select 10 as key,'ABC' as data from dual 5 union 6 select 20 as key,'ABC' as data from dual 7 ) a full outer join 8 ( 9 select 20 as key,'DEF' as data from dual 10 union 11 select 30 as key,'DEF' as data from dual 12 ) b 13 on a.key=b.key 14 ; KEY DAT KEY DAT ---------- --- ---------- --- 20 ABC 20 DEF 10 ABC 30 DEF
「ORA-03113: 通信チャネルでend-of-fileが検出されました」が起きるのは、DBMSのプロセスが落ちた為らしい。[2007-10-29]
どうやらSQLの解釈に失敗してDBMS自体が落ちてコアダンプしており、そのせいでsqlplusとの接続が切れてこのエラーになったんだろう。
それが分かったからと言って 解決に結びつくわけじゃないが…