引言
我们都知道Oracle的Pivot的In()里是不能跟SQL查询语句的,必须是已知的字符串结果,然而我项目中遇到的需求这个In 并不是静态的,而是根据我传递的条件来筛选,一开始想的是解析XML,可惜转为XML之后,有相同的重复的节点,解析太麻烦,于是想通过动态sql,先把SQl查询结果再另一个函数里查询出来,再赋值给一个中间变量,作为参数传递到动态SQL里。
Pivot相关
在 Oracle 数据库 11g 推出之前,您需要针对每个值通过 decode 函数进行以上操作,并将每个不同的值编写为一个单独的列。但是,该方法一点也不直观。 庆幸的是,您现在可以使用一种很棒的新特性 PIVOT 通过一种新的操作符以交叉表格式显示任何查询,该操作符相应地称为 pivot。下面是查询的编写方式:
select * from (
select times_purchased, state_code
from customers t
)
pivot
(
count(state_code)
for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased
执行结果
输出如下:
. TIMES_PURCHASED 'NY' 'CT' 'NJ' 'FL' 'MO'
--------------- ---------- ---------- ---------- ---------- ----------
0 16601 90 0 0 0
1 33048 165 0 0 0
2 33151 179 0 0 0
3 32978 173 0 0 0
4 33109 173 0 1 0
这表明了 pivot 操作符的威力。state_codes 作为标题行而不是列显示。下面是传统的表格化格式的图示:
动态SQL实现SQL查询子集行转列
CREATE OR REPLACE PROCEDURE p_Getgatherreprot37102(p_Provinceno IN VARCHAR2,
p_Cityno IN VARCHAR2,
p_Countryno IN VARCHAR2,
Ref_Cursor OUT SYS_REFCURSOR --返回的结果集,游标
) AS
v_Getstrulvlsql VARCHAR(4000) := '';
v_Getsql VARCHAR(4000) := '';
v_columnNames Varchar2(32767) :='';
/*获取烟叶等级结构的信息并行转列*/
BEGIN
--获取查询并拼接成一定格式的字符串 作为参数传递到动态SQL里
SELECT f_Concat_Name(p_Provinceno,p_Cityno,p_Countryno) INTO v_Getstrulvlsql FROM dual t;
v_Getsql := '--Open ref_cursor for
SELECT *
FROM (SELECT DISTINCT Cl.Levelno, Cl.Levelqualifiedratio
FROM Pdm_Ylzj_Crutoqualinspe c
LEFT JOIN Pdm_Ylzj_Crutosendinspe Sj
ON c.Pdm_Ylzj_Crutosendinspeid = Sj.Pdm_Ylzj_Crutosendinspeid
LEFT JOIN (SELECT X1.Pdm_Ylzj_Crutosendinspeid,
MAX(X1.Pdm_Ylzj_Crutoreporinspeid) AS Pdm_Ylzj_Crutoreporinspeid
FROM Pdm_Ylzj_Crutosendinsped X1
INNER JOIN Pdm_Ylzj_Crutoreporinspe X2
ON X1.Pdm_Ylzj_Crutoreporinspeid = X2.Pdm_Ylzj_Crutoreporinspeid
WHERE X2.State = 2
GROUP BY X1.Pdm_Ylzj_Crutosendinspeid) f
ON Sj.Pdm_Ylzj_Crutosendinspeid = f.Pdm_Ylzj_Crutosendinspeid
INNER JOIN Pdm_Ylzj_Crutolevelstruc Cl';
v_Getsql:=v_Getsql||' ON Cl.Pdm_Ylzj_Crutoqualinspeid = c.Pdm_Ylzj_Crutosendinspeid';
v_Getsql:=v_Getsql||' LEFT JOIN Pdm_Ylzj_Recrutoqualinspe Fj
ON Fj.Inspectionno = c.Inspectionno AND Nvl(Fj.Isdeleted, ''0'') = ''0''
WHERE Nvl(c.Isdeleted, 0) = 0 AND f.Pdm_Ylzj_Crutosendinspeid IS NULL AND
Fj.Pdm_Ylzj_Recrutoqualinspeid IS NULL AND Cl.Leveltype = ''1'' OR
Cl.Leveltype = ''2''
AND c.Provincesno = ''HB'' AND c.Cityno = ''ES'' AND c.Countyno = ''ES'')
Pivot(SUM(Levelqualifiedratio)';
v_Getsql:= v_Getsql || ' FOR Levelno IN('||v_Getstrulvlsql||'));';
execute Immediate v_Getsql Into Ref_Cursor;
END;