一、问题描述
现在需要生成如下图所示的序列
函数的具体要求:
(1)传入一个终值endNumber,使用number类型存放
(2)序列中的每个数需要以何种方式重复出现(如:时间、金额或者只是单次重复出现) 重复模式titleArr,使用plsql中的自定义type来存放(这里其实是当作数组来用)
(3)返回结果是一列按给定规则出现的字符串构成的结果集,使用plsql中的自定义type来存放(用到pl/sql中的管道函数pipe row)
二、准备工作
在写这个通用的序列生成函数之前,我们先来看下,如果就生成上图所示的序列该如何用sql写
如果只是简单的序列1,2,3,4,5,...,11,可以这样写
select level from dual connect by level <= 11;
那如果是生成序列1,1,2,2,3,3,4,4,...,11,11,该怎么写呢?
如果结合高中学过的数列来思考,我们发现前一个数列的通项为
而现在这个数列的通项容易推导,得到
那这个sql可以这样写,
select 0.5 * (level + 0.5 * (1 - power(-1, level))) as l
from dual
connect by level <= 22;
那这样写其实不太好,如果后面需要生成1,1,1,2,2,2,3,3,3,...,11,11,11这样的序列,那又要根据通项公式再把语句改掉,而且后面的通项公式会更复杂。另外,如何与列标题(按“时间、金额”的模式重复,这种重复模式后面还要提取出来,作为参数传入)结合起来也是个问题
能不能结合sql本身的一些特性来写呢?工作中碰到一位大神,使用left join和union all,把这个问题解决了。非常感谢这位大神!通过这条语句,直接写出了开头图中的结果
select '第' || s.l || '期' || t.s as columnTitle
from (select level as l from dual connect by level <= 11) s
left join (select '时间' as s
from dual
union all
select '金额' as s
from dual) t
on 1 = 1
那这个序列(1,1,2,2,3,3,...,11,11)的生成就很简单了
select s.l as columnTitle
from (select level as l from dual connect by level <= 11) s
left join (select '' as s
from dual
union all
select '' as s
from dual) t
on 1 = 1
三、开始实现功能
1.结合上面的sql,首先可以理出思路:
(1)需要传入的参数:connect by level之后的数字11,提取为输入参数endNumber;
在这之后紧接着的连接语句作为需要在函数中动态拼接的那部分sql,其中“时间、金额”是需要作为输入参数titleArr
(2)需要的返回参数:一列数据 经过pipe row处理过的
可以知道,将sql完整拼出来以后,需要根据sql打开游标,然后遍历游标中的每个数据,做pipe row处理后,最终作为返回结果
--创建自定义type
create or replace type "T_RET_TABLE" as table of varchar2(1000);
--创建函数
create or replace function getColumnList(endNum in number,titleArr in t_ret_table)
return t_ret_table pipelined
as v_sql varchar2(2000);--存放完整拼接出来的sql
columnTitle varchar2(100);--存放游标抽取出来的单个数据
columnTitle_sql varchar2(200);--存放重复模式的动态sql
columnNumber number;--存放通过根据重复模式得到的需要重复的次数
data_cur sys_refcursor;--存放通过执行sql得到的游标
begin
columnNumber:=titleArr.count;
--数组中至少有一个元素时,进行遍历操作
if columnNumber>=1 then
for i in 1..titleArr.count loop
--数组遍历到最后一个元素时,不需要以union all结尾
if i=titleArr.count then
columnTitle_sql:=columnTitle_sql|| 'select '''||titleArr(i)||''' as s from dual';
else
columnTitle_sql:=columnTitle_sql||'select '''||titleArr(i)||''' as s from dual union all ';
end if;
end loop;
end if;
--dbms_output.put_line(columnTitle_sql);
--将前面拼好的sql与已有的sql结合,得到完整的sql
v_sql:='select s.l||'',''||t.s as columnTitle '||
'from (select level as l from dual connect by level <= '||endNum||') s '||
'left join ('||columnTitle_sql||') t on 1 = 1';
--dbms_output.put_line(v_sql);
--根据sql语句打开游标,取出游标中的数据存放到返回类型t_ret_table中
open data_cur for v_sql;
loop
fetch data_cur into columnTitle;
exit when data_cur%notfound;
pipe row(columnTitle);
--dbms_output.put_line(columnTitle);
end loop;
--取完数据后,关闭前面打开的游标
close data_cur;
return;
end getColumnList;
2.调用函数:
select * from table(getColumnList(11, t_ret_table('时间', '金额')));
运行以后,发现结果如下所示:
为什么这样处理呢?
可以看到在getColumnList函数的第22行,我在拼接值的时候加了个逗号(就是个分隔符)在中间。因为不确定这些数字后面可能会接什么样的量词(如:期、次、个、批等等,当然也可以为空),所以用了一个分隔符来占个位置。
(1)生成开头那张图片的结果,就可以写成
select '第' || replace(column_value, ',', '期') as columnTitle
from table(getColumnList(11, t_ret_table('时间', '金额')));
(2)如果仅仅生成数字序列,
1,2,3,4,5,...,11
select replace(column_value, ',', '') as columnTitle
from table(getColumnList(11, t_ret_table('')))
1,1,1,2,2,2,3,3,3,4,4,4,...,11,11,11
select replace(column_value, ',', '') as columnValue
from table(getColumnList(11, t_ret_table('', '', '')))
(3)如果说11这个数也是需要从其他表中查询得到呢?(具体逻辑就不写了,假定是select 11 from dual这样查到的)也就是说函数参数本身也是不确定的
刚开始我是这样写:
select '第' || replace(column_value, ',', '期') as columnTitle
from table(getColumnList(select 11 from dual, t_ret_table('时间', '金额')));
但是,发现报错“ORA-00936: 缺失表达式”,这种方式不可行。因为在oracle的查询语句中,没有在函数中的参数位置直接放一条sql语句的写法。
结合sql本身的语法,我想到了在table里调用getColumnList函数时,使用查询的方式,将11传入endNumber的位置(也可以写更复杂的逻辑)
select '第' || replace(column_value, ',', '期') as columnTitle
from table (select getColumnList(t.maxPeriod, t_ret_table('时间', '金额'))
from (select 11 as maxPeriod from dual) t);
当然,如果觉得以上的函数封装方式还不够好的话,欢迎提出意见或建议(如果是解决方案的话更好),我们一起交流探讨
四、总结
在解决这个问题时,查找了一些相关的资料。以下是我认为这个问题在解决时的一些关键点:
1.如何给oracle自定义函数中的一个变量中放入多个参数:使用定义的type对象,构造出类似数组形式的参数
2.如何根据给定的sql打开游标:
https://blog.csdn.net/jgmydsai/article/details/42740935
https://bbs.csdn.net/topics/360153607
3.Oracle函数中如何返回结果集:
https://www.linuxidc.com/Linux/2015-04/115912.htm