8-21 sys.odciVarchar2Listとsys.odciNumberList

SQLパズル

sys.odciVarchar2Listとsys.odciNumberListの紹介

こちらを参考にさせていただきました(英語)

SQL

--■■■sys.odciVarchar2List■■■
select * from table(sys.odciVarchar2List('AAA','BBB',null,'DDD'))
 where column_value is not null;

COLUMN_VALUE
------------
AAA
BBB
DDD

--■■■sys.odciNumberList■■■
select RowNum ID, column_value X
  from table(sys.odciNumberList(100,200));

ID　  X
--　---
 1　100
 2　200

 --■■■sys.odciDateList■■■
select * from table(sys.odciDateList(sysdate,date '2008-10-10',null,date '2009-12-31'))
 where column_value is not null;

COLUMN_VALUE
------------
09-01-07
08-10-10
09-12-31

解説

スカラーサブクエリを引数に使ったりとか、
テストデータを作るのに使えそうですね。

US-OTNのスレッド

マニュアル ODCIDateList,ODCINumberList,ODCIVarchar2List(英語)
マニュアル ODCIDateList,ODCINumberList,ODCIVarchar2List

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
COLUMN_VALUE疑似列

>キーワードCOLUMN_VALUEは、
>列または属性名を持たない内部のネストした表のスカラー値に対して
>Oracleデータベースが生成する名前です。



Oracle10gで簡易なUnPivotを行うこともできます。

create table ODCITest(key,column1,column2,column3) as
select 1,22,33,44 from dual union all
select 2,55,66,77 from dual union all
select 3,88,99,11 from dual;

select key,COLUMN_VALUE as newCol
  from ODCITest,
 table(sys.odciNumberList(column1,column2,column3));

key  newCol
---  ------
  1      22
  1      33
  1      44
  2      55
  2      66
  2      77
  3      88
  3      99
  3      11

---------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          | 24504 |  1292K|    86   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                          |          | 24504 |  1292K|    86   (0)| 00:00:02 |
|   2 |   TABLE ACCESS FULL                    | ODCITEST |     3 |   156 |     3   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|          |  8168 | 16336 |    28   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

---