前一段用SQL实现了一个复杂的功能，但是留下了一个问题一直没有真正的解决，那就是如何同时为表中的多条记录分别实现构造结果集。

利用Oracle的TYPE实现结果集构造。

问题源自：一个复杂问题的求解过程（一）：http://yangtingkun.itpub.net/post/468/456641

如何为表中多条记录分别实现构造结果集（一）：http://yangtingkun.itpub.net/post/468/459848

上一篇已经提到通过DISTINCT的方式对于数据量大的情况是不可取的，而且从本质上讲，这个SQL的逻辑是有问题的。

当然这里可以通过对表中的配置记录依次生成结果集：

SQL> SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
2 FROM (SELECT ROWNUM, T.* FROM T WHERE ID = 1) T
3 CONNECT BY LEVEL <= T.POWER
4 UNION ALL
5 SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
6 FROM (SELECT ROWNUM, T.* FROM T WHERE ID = 2) T
7 CONNECT BY LEVEL <= T.POWER
8 UNION ALL
9 SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
10 FROM (SELECT ROWNUM, T.* FROM T WHERE ID = 3) T
11 CONNECT BY LEVEL <= T.POWER
12 UNION ALL
13 SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
14 FROM (SELECT ROWNUM, T.* FROM T WHERE ID = 4) T
15 CONNECT BY LEVEL <= T.POWER
16 UNION ALL
17 SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
18 FROM (SELECT ROWNUM, T.* FROM T WHERE ID = 5) T
19 CONNECT BY LEVEL <= T.POWER
20 ;

ID VALUE POWER RESULT
---------- ---------- ---------- ----------
1 2 3 0
1 2 3 2
1 2 3 4
2 1 2 0
2 1 2 1
3 4 4 0
3 4 4 4
3 4 4 8
3 4 4 12
4 5 2 0
4 5 2 5
5 4 3 0
5 4 3 4
5 4 3 8

已选择14行。

暂且不论这种方式的效率问题，单就实现而言就有很大的问题，这种方式采用硬编码的方式，一旦表中配置记录增加，就需要手工修改SQL，而且随着数据量的增大，SQL语句的长度会迅速增大，每增加一条记录就需要多UNION ALL一次基础表，这种效率也是无法接受的。

硬编码和DISTINCT方式都无法满足要求，只能选择其他的方法进行替代，如果这里借助一下自定义的TYPE类型，则SQL就会很容易实现：

SQL> CREATE OR REPLACE TYPE T_TYPE IS TABLE OF NUMBER;
2 /

类型已创建。

SQL> WITH T1 AS
2 (
3 SELECT ID, VALUE, POWER,
4 CAST (MULTISET(
5 SELECT VALUE * (LEVEL - 1)
6 FROM DUAL CONNECT BY ROWNUM <= POWER
7 ) AS T_TYPE) RESULT
8 FROM T
9 )
10 SELECT ID, VALUE, POWER, T.COLUMN_VALUE RESULT
11 FROM T1 A, TABLE(SELECT RESULT FROM T1 WHERE T1.ID = A.ID) T
12 ;

ID VALUE POWER RESULT
---------- ---------- ---------- ----------
1 2 3 0
1 2 3 2
1 2 3 4
2 1 2 0
2 1 2 1
3 4 4 0
3 4 4 4
3 4 4 8
3 4 4 12
4 5 2 0
4 5 2 5
5 4 3 0
5 4 3 4
5 4 3 8

已选择14行。

采用这种方式实现，SQL就相当简单了，而且性能也完全可以满足要求：

SQL> INSERT INTO T SELECT 5 + ID, VALUE, POWER FROM T;

已创建5行。

SQL> INSERT INTO T SELECT 10 + ID, VALUE, POWER FROM T;

已创建10行。

SQL> INSERT INTO T SELECT 20 + ID, VALUE, POWER FROM T;

已创建20行。

SQL> SET TIMING ON
SQL> SET AUTOT TRACE
SQL> WITH T1 AS
2 (
3 SELECT ID, VALUE, POWER,
4 CAST (MULTISET(
5 SELECT VALUE * (LEVEL - 1)
6 FROM DUAL CONNECT BY ROWNUM <= POWER
7 ) AS T_TYPE) RESULT
8 FROM T
9 )
10 SELECT ID, VALUE, POWER, T.COLUMN_VALUE RESULT
11 FROM T1 A, TABLE(SELECT RESULT FROM T1 WHERE T1.ID = A.ID) T
12 ;

已选择112行。

已用时间: 00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 1145965015

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 326K| 12M| 893 (2)| 00:00:11 |
| 1 | NESTED LOOPS | | 326K| 12M| 893 (2)| 00:00:11 |
| 2 | TABLE ACCESS FULL | T | 40 | 1560 | 3 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR SUBQUERY FETCH| | | | | |
|* 4 | TABLE ACCESS FULL | T | 1 | 39 | 3 (0)| 00:00:01 |
| 5 | COUNT | | | | | |
| 6 | CONNECT BY WITHOUT FILTERING | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("ID"=:B1)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
495 consistent gets
0 physical reads
0 redo size
2675 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
40 sorts (memory)
0 sorts (disk)
112 rows processed

SQL> SET AUTOT OFF
SQL> INSERT INTO T SELECT 40 + ID, VALUE, POWER FROM T;

已创建40行。

已用时间: 00: 00: 00.00
SQL> SET AUTOT TRACE
SQL> WITH T1 AS
2 (
3 SELECT ID, VALUE, POWER,
4 CAST (MULTISET(
5 SELECT VALUE * (LEVEL - 1)
6 FROM DUAL CONNECT BY ROWNUM <= POWER
7 ) AS T_TYPE) RESULT
8 FROM T
9 )
10 SELECT ID, VALUE, POWER, T.COLUMN_VALUE RESULT
11 FROM T1 A, TABLE(SELECT RESULT FROM T1 WHERE T1.ID = A.ID) T
12 ;

已选择224行。

已用时间: 00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 1145965015

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 653K| 25M| 1782 (2)| 00:00:22 |
| 1 | NESTED LOOPS | | 653K| 25M| 1782 (2)| 00:00:22 |
| 2 | TABLE ACCESS FULL | T | 80 | 3120 | 3 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR SUBQUERY FETCH| | | | | |
|* 4 | TABLE ACCESS FULL | T | 1 | 39 | 3 (0)| 00:00:01 |
| 5 | COUNT | | | | | |
| 6 | CONNECT BY WITHOUT FILTERING | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("ID"=:B1)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
982 consistent gets
0 physical reads
0 redo size
4756 bytes sent via SQL*Net to client
539 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
80 sorts (memory)
0 sorts (disk)
224 rows processed

可以看到这种SQL写法，在配置表中数据量增长后执行时间变化不大，即使将配置表扩大到1000条记录以上，这种写法仍然可以在1秒钟内完成，可以说这种写法满足性能方面的需求：

SQL> SET AUTOT OFF
SQL> SET TIMING OFF
SQL> INSERT INTO T SELECT 80 + ID, VALUE, POWER FROM T;

已创建80行。

SQL> INSERT INTO T SELECT 160 + ID, VALUE, POWER FROM T;

已创建160行。

SQL> INSERT INTO T SELECT 320 + ID, VALUE, POWER FROM T;

已创建320行。

SQL> INSERT INTO T SELECT 640 + ID, VALUE, POWER FROM T;

已创建640行。

SQL> SET TIMING ON
SQL> SET AUTOT TRACE
SQL> WITH T1 AS
2 (
3 SELECT ID, VALUE, POWER,
4 CAST (MULTISET(
5 SELECT VALUE * (LEVEL - 1)
6 FROM DUAL CONNECT BY ROWNUM <= POWER
7 ) AS T_TYPE) RESULT
8 FROM T
9 )
10 SELECT ID, VALUE, POWER, T.COLUMN_VALUE RESULT
11 FROM T1 A, TABLE(SELECT RESULT FROM T1 WHERE T1.ID = A.ID) T
12 ;

已选择3584行。

已用时间: 00: 00: 00.31

执行计划
----------------------------------------------------------
Plan hash value: 1145965015

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 408M| 28453 (2)| 00:05:42 |
| 1 | NESTED LOOPS | | 10M| 408M| 28453 (2)| 00:05:42 |
| 2 | TABLE ACCESS FULL | T | 1280 | 49920 | 3 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR SUBQUERY FETCH| | | | | |
|* 4 | TABLE ACCESS FULL | T | 13 | 507 | 2 (0)| 00:00:01 |
| 5 | COUNT | | | | | |
| 6 | CONNECT BY WITHOUT FILTERING | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("ID"=:B1)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
16700 consistent gets
0 physical reads
0 redo size
70133 bytes sent via SQL*Net to client
3003 bytes received via SQL*Net from client
240 SQL*Net roundtrips to/from client
1280 sorts (memory)
0 sorts (disk)
3584 rows processed

这种方法唯一的问题是借助了一个自定义对象，利用了对象和SQL的配合实现了这个功能，对于希望完全使用SQL实现的人而言，这个方法并不完美。