Oracle 构造虚表方法

数据库
Oracle

一、简述

有时候做一些相关报表的程序，免不了拼接业务SQL，因此涉及到表与表外连接（左连接与右连接），根据业务需求需要补全表连接资料。补全表连接资料可以有两个办法，通过程序遍历结果集做资料补全或者通过拼接SQL构造Table与其他表做左右外连接。当然这两个办法各有区别：业务复杂度高的还是建议通过程序作处理，假若是通过拼接SQL的办法，就会造成业务SQL变复杂；业务复杂度低的采用拼接SQL的办法能够快速构造出业务数据，这也未必是个坏办法。下面就总结下通过 Oracle 构造虚表的办法。

二、dual+union表构造

毫无疑问，通过dual表构造虚表也是最简单暴力的办法，例如：
构造单列：

1
2
3
4
5
6
7
SELECT 'AA' FIELD1 FROM dual
UNION ALL
SELECT 'BB' FIELD1 FROM dual
UNION ALL
SELECT 'CC' FIELD1 FROM dual
UNION ALL
SELECT 'DD' FIELD1 FROM dual;

构造多列：

1
2
3
4
5
6
7
SELECT 'AA' FIELD1, 'AA' FIELD2 FROM dual
UNION ALL
SELECT 'BB' FIELD1, 'BB' FIELD2 FROM dual
UNION ALL
SELECT 'CC' FIELD1, 'CC' FIELD2 FROM dual
UNION ALL
SELECT 'DD' FIELD1, 'DD' FIELD2 FROM dual;

上述办法特点是简单，但是一旦拼接的行数过多的话，SQL会变的冗长，也不直观。

三、REGEXP_SUBSTR函数

1
2
SELECT REGEXP_SUBSTR('AA,BB,CC,DD', '[^,]+', 1, LEVEL) FIELD1 FROM dual
CONNECT BY REGEXP_SUBSTR('AA,BB,CC,DD', '[^,]+', 1, LEVEL) IS NOT NULL;

通过 REGEXP_SUBSTR 分割字符串，达到构造虚表的目的，特点是写法简洁，不过需要了解 REGEXP_SUBSTR 和 CONNECT BY 用法。

REGEXP_SUBSTR 用法：

1
REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ]]])

  1. source_char: 字符串
  2. pattern: 正则表达式
  3. position: 从 source_char 指定坐标开始分割字符串，1为起始坐标
  4. occurrence: 满足正则表达式的字符串第N次出现
  5. match_parameter: 正则表达式匹配的参数有 ‘c’, ‘i’, ‘n’, ‘m’, ‘x’

CONNECT BY 用法：
未完待续

四、参考资料

Oracle的REGEXP_SUBSTR函数简单用法

评论