[oracle自定义函数]生成复杂序列

一、问题描述

现在需要生成如下图所示的序列

函数的具体要求：

（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

Oracle pipe row