Oracle常用操作

一. 函数使用

1.1 listagg聚合函数

语法:

listagg(col_XXX,YYY) WITHIN GROUP( ORDER BY ZZZ)。
参数解释:
①. col_XXX: 需要聚合的字段
②. YYY: 字段间分割符, 如’’, ‘,’ ,’#’
③. ZZZ: 必填字段, 表示根据zzz字段来进行排序, 以保证col_XXX 能够按照业务指定的顺序进行字符串拼装.
可以配合 GROUP BY 进行聚合查询，也可以配合 PARTITION BY 进行聚合查询。

综合样例清单:

实现以下功能:

• 1.从虚表dual中构建一张存放在内存中的指标表, 该表有5个字段, 分别为业务指标名, 指标1~指标4;
• 2.设置表头;
• 3.从内存表 table_test_1 中读取所有的内容行, 作为html table元素的内容.
• 4.使用listagg 聚合所有的内容字段(table_content, 分割字符设置为空字符)

WITH table_test_1 AS (
SELECT
		'指标名_1' AS idx_name,
		11 AS col_1,
		11.1 AS col_2,
		11 AS col_3,
		11.2 AS col_4
FROM
		dual
UNION ALL
SELECT
		'指标名_2' AS idx_name,
		22 AS col_1,
		22.2 AS col_2,
		22 AS col_3,
		22.3 AS col_4
FROM
		dual
UNION ALL
SELECT
		'指标名_3' AS idx_name,
		33 AS col_1,
		33.3 AS col_2,
		33 AS col_3,
		33.4 AS col_4
FROM
		dual
UNION ALL
SELECT
		'指标名_4' AS idx_name,
		44 AS col_1,
		44.4 AS col_2,
		444 AS col_3,
		44.5 AS col_4
FROM
		dual
) --select * from table_test_1;    --测试如上内存中的sql拼装是否正确. 测试完后请删除该select语句.
SELECT
	listagg (
		table_test_2.table_content,        --希望聚合的字段
		''                                 --聚合字段间的连接字符
	) WITHIN GROUP (ORDER BY sort_no) AS assembly_string --装配完后的字符串
FROM
	(
		SELECT
			'<table border="solid" cellspacing="0" cellpadding="10" frame="solid" rules="solid">' AS table_content,
			1 AS sort_no
		FROM
			dual
		UNION ALL
			SELECT
				'<tr align="center" valign="center" bgcolor="red"><th>Proj</th><th>time</th><th>indicator_1</th><th>indicator_2</th><th>indicator_3</th><th>indicator_4</th></tr>' AS table_content,
				2 AS sort_no
			FROM
				dual
			UNION ALL
				SELECT
					'<tr><td align="left">' || table_test_1.idx_name || '</td><td  align="center">' || TO_CHAR (SYSDATE, 'yyyy-MM-dd') || '</td><td align="right">' || ROUND (table_test_1.col_1, 0) || '</td><td  align="right">' || ROUND (table_test_1.col_2, 0) || '</td><td  align="right">' || ROUND (table_test_1.col_3, 0) || '</td><td  align="right">' || ROUND (table_test_1.col_4, 0) || '</td></tr>' AS table_content,
					3 AS sort_no
				FROM
					table_test_1
				UNION ALL
					SELECT
						'</table>' AS table_content,
						4 AS sort_no
					FROM
						dual
	) table_test_2;

查询结果如下:

<table border="solid" cellspacing="0" cellpadding="10" frame="solid" rules="solid"><tr align="center" valign="center" bgcolor="red"><th>Proj</th><th>time</th><th>indicator_1</th><th>indicator_2</th><th>indicator_3</th><th>indicator_4</th></tr><tr><td align="left">指标_1</td><td  align="center">2020-04-27</td><td align="right">11</td><td  align="right">11</td><td  align="right">11</td><td  align="right">11</td></tr><tr><td align="left">指标_2</td><td  align="center">2020-04-27</td><td align="right">22</td><td  align="right">22</td><td  align="right">22</td><td  align="right">22</td></tr><tr><td align="left">指标_3</td><td  align="center">2020-04-27</td><td align="right">33</td><td  align="right">33</td><td  align="right">33</td><td  align="right">33</td></tr><tr><td align="left">指标_4</td><td  align="center">2020-04-27</td><td align="right">44</td><td  align="right">44</td><td  align="right">444</td><td  align="right">45</td></tr></table>

1.2 with as构建临时表

with as 也叫做子查询部分，首先定义一个sql片段，该sql片段会被整个sql语句所用到，为了让sql语句的可读性更高些，作为提供数据的部分，也常常用在union [all]等集合操作中。
with as就类似于一个视图或临时表，可以用来存储一部分的sql语句作为别名，不同的是with as 属于一次性的，而且必须要和其他sql一起使用才可以！
其最大的好处就是适当的提高代码可读性，而且如果with子句在后面要多次使用到，这可以大大的简化SQL；更重要的是：一次分析，多次使用，这也是为什么会提供性能的地方，达到了“少读”的目标。

语法:

WITH t1 AS (
SELECT *FROM student
),
t2 AS (
SELECT *FROM course
) SELECT * FROM t1, t2

!!! Notice:

• 1. 这里必须要整体作为一条sql查询，即with as语句后不能加分号，不然会报错。
• 2. with子句必须在引用的select语句之前定义,同级with关键字只能使用一次,多个只能用逗号分割；最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来.
• 3. 如果定义了with子句，但其后没有跟select查询，则会报错！select 语句中有无使用WITH后的表名是无所谓的, 只要有select语句就不会报错.
• 4. 前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句！
     ***** 样例代码 ****：
     with t1 as (select * from student_course),
     t2 as (select t1.student_id from student)
     select * from t2