« JPOUG Tech Talk Night #4 @ IIJ  開催のおしらせ | トップページ | Windows XPサポート終了から2週間ほど経過したのでアクセスログをみてみた... »

2014年4月20日 (日)

シーケンス.NEXTVALが使えないからぐるぐる〜〜〜っとしていいですか? (30歳 エンジニア 男性)

CREATE TABLE 〜 AS SELECT文でシーケンス.NEXTVALって使えないからぐるぐる〜〜〜っとしたループ処理しないといけないですよね〜。

と質問されたのですが、1文で書けますからね!
(SQL文でワンライナーって言いそうになったけど、飲み込んだw)


準備

SCOTT> l
1 CREATE TABLE table_foobar
2 (
3 id NUMBER PRIMARY KEY
4 ,data VARCHAR2(10)
5* )
SCOTT> /

表が作成されました。

SCOTT> l
1 INSERT INTO table_foobar
2 SELECT
3 LEVEL
4 ,'D'||TO_CHAR(LEVEL,'FM099999999')
5 FROM
6 dual
7 CONNECT BY
8* LEVEL <= 100.
SCOTT> /

100行が作成されました。

SCOTT> commit;

コミットが完了しました。

SCOTT> SELECT * FROM table_foobar ORDER BY id;

ID DATA
---------- ----------
1 D000000001
2 D000000002
3 D000000003
4 D000000004
5 D000000005
6 D000000006
7 D000000007
8 D000000008
9 D000000009
10 D000000010

   ...中略...

90 D000000090
91 D000000091
92 D000000092
93 D000000093
94 D000000094
95 D000000095
96 D000000096
97 D000000097
98 D000000098
99 D000000099
100 D000000100

100行が選択されました。


前述のデータを複製しtable_foobar_tmp表を作成するとします。
なお、DATA列はそのままで、ID列は、次のシーケンスを利用してID = 1..100の順にシーケンスから採番しなおしたい。
(以下のシーケンスの定義からすると、 ID=1 は、1000、 ID=2は、1001にしたい。)

SCOTT> CREATE SEQUENCE seq_foobar start with 1000 maxvalue 999999999;

順序が作成されました。

1文で書けますよね!

SCOTT> l
1 CREATE TABLE table_foobar_tmp
2 AS
3 SELECT
4 seq_foobar.NEXTVAL AS id
5 ,t01.data
6 FROM
7 (
8 SELECT
9 id
10 ,data
11 FROM
12 table_foobar
13 ORDER BY
14 id
15* ) t01
SCOTT> /

表が作成されました。

SCOTT> SELECT * FROM table_foobar_tmp ORDER BY ID;

ID DATA
---------- ----------
1000 D000000001
1001 D000000002
1002 D000000003
1003 D000000004
1004 D000000005
1005 D000000006
1006 D000000007
1007 D000000008
1008 D000000009
1009 D000000010
1010 D000000011

   ...中略...

1090 D000000091
1091 D000000092
1092 D000000093
1093 D000000094
1094 D000000095
1095 D000000096
1096 D000000097
1097 D000000098
1098 D000000099
1099 D000000100

100行が選択されました。

はい、できました!


マニュアルには、「NEXTVALへの参照が含まれる単一のSQL文の中では、Oracleは、次の各行につき1回順序を増加させます。」
と記載されているので、質問してきた方は、マニュアルを読んでいないか、マニュアル読んでなくても実際に試していない食わず嫌い状態だったか、
以下のようなシーケンスの制限に遭遇して、できないんだ!と思い込んでしまった。

病は気からという状態だったのでしょうね。:)


SCOTT> l
1 CREATE TABLE table_foobar_tmp_NG
2 AS
3 SELECT
4 seq_foobar.NEXTVAL AS ID
5 ,t01.data
6 FROM
7 table_foobar t01
8 ORDER BY
9* t01.id
SCOTT> /
seq_foobar.NEXTVAL AS ID
*
行4でエラーが発生しました。:
ORA-02287: ここでは順序番号は使用できません。

エラーメッセージを見ても、Action がRemove the sequence numberだけですからね....

SCOTT> !oerr ORA 2287
02287, 00000, "sequence number not allowed here"
// *Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
// here in the statement.
// *Action: Remove the sequence number.

そんな時はマニュアルを..
Oracle® Database SQL言語リファレンス 11gリリース2 (11.2) 順序疑似列

順序値の使用方法には、CREATE TABLE ... AS SELECTで使用できると書いてるのに。なんでだろう?
と,、なるかもしれないですが、よ〜〜〜くマニュアルを読んでくださいよ〜っ。

順序値の制限事項にGROUP BY句やORDER BY句を持つSELECT文では使用できないとも書かれているところにちゅうも〜〜〜〜く!

ORDER BY句を含んでいるのでORA-02287回避のために、サブクエリにして別クエリブロック化、シーケンスを利用しているクエリブロックにはORDER BY句を含まないようにしているところがポイント :)

テストデータ作るときとか、知ってると便利ですよ〜と。

SCOTT> l
1 CREATE TABLE table_foobar_tmp
2 AS
3 SELECT
4 seq_foobar.NEXTVAL AS id
5 ,t01.data
6 FROM
7 (
8 SELECT
9 id
10 ,data
11 FROM
12 table_foobar
13 ORDER BY
14 id
15* ) t01
SCOTT> /

Enjoy SQL!

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/59499943

この記事へのトラックバック一覧です: シーケンス.NEXTVALが使えないからぐるぐる〜〜〜っとしていいですか? (30歳 エンジニア 男性):

コメント

コメントを書く