Oracle FUNCTION :将Excel中的SUM坐标全部展开

自己写了一个Oracle FUNCTION, 用来将Excel中的SUM坐标全部展开

调用方式:
select Excel_Sum_to_individual_cols('sum(D1:D3,D5,C5,D12:D15,D6)') from dual;

CREATE OR REPLACE FUNCTION Excel_Sum_to_individual_cols(p_sum_input VARCHAR2) RETURN VARCHAR2 IS
/**
支持:
输入同行跨列如 SUM(AA26:AC26)
输出 (AA26+AB26+AC26)
或者:
输入列同跨行如 SUM(AA21:AA24)
输出 (AA21+AA22+AA23+AA24)
或者:
输入 sum(D1:D3,D5,C5,D12:D15,D6)
输出 (D1+D2+D3+D5+C5+D12+D13+D14+D15+D6)
 Author: CCQ
*/
  PARAM_INPUT VARCHAR2(500);
  rtnStr VARCHAR2(1000); --返回值
  str_1  VARCHAR2(10); -- 如 AA26
  str_2  VARCHAR2(10); -- 如 AC26
  str_1_head  VARCHAR2(10); -- 如AA
  str_1_tail  number; -- 如 26
  str_2_head  VARCHAR2(10); -- 如 AC
  str_2_tail  number; -- 如 26

  gap number;

  new_enchar  VARCHAR2(10);
BEGIN
  --先把sum和括号去掉
  select replace(replace(upper(p_sum_input),'SUM('),')') INTO PARAM_INPUT from dual; --
  
	--根据逗号循环处理
	
  for item in (
			select * from table(split(PARAM_INPUT,',') )
  )loop
      
      --dbms_output.put_line('	item.column_value ------'||	item.column_value);
	    
      if item.column_value like '%：%' or item.column_value like '%:%' then --有中文或英文冒号
			  --根据冒号 得到需要sum的首尾
				if item.column_value like '%：%'  then -- 他居然有中文冒号 兼容一下
					 select substr(item.column_value,0,instr(item.column_value,'：')-1) into str_1 from dual ;
					 select substr(item.column_value,instr(item.column_value,'：')+1) into str_2 from dual ;
			--	elsif item.column_value not like '%：%' and item.column_value not like '%:%' then -- 居然还有sum里只有一个坐标的, 直接返回
					-- RETURN '('||rtnStr||')';
				else
					 select substr(item.column_value,0,instr(item.column_value,':')-1) into str_1 from dual ;
					 select substr(item.column_value,instr(item.column_value,':')+1) into str_2 from dual ;
				end if;

				--拆分行和列
				select regexp_replace(str_1,'[^A-Za-z]') into str_1_head from dual;
				select to_number(regexp_replace(str_1,'[^0-9]','')) into str_1_tail from dual;

				select regexp_replace(str_2,'[^A-Za-z]') into str_2_head from dual;
				select to_number(regexp_replace(str_2,'[^0-9]','')) into str_2_tail from dual;
			 

				rtnStr:= rtnStr ||str_1;

				if str_1_head = str_2_head then -- 列同,跨行 :SUM(AA21:AA24) 这种

					 select str_2_tail-str_1_tail into gap from dual;
					 for i in 1..gap
					 loop
							 rtnStr:= rtnStr ||'+' ||str_1_head||(str_1_tail +i) ;
					 end loop ;


				else -- 同行,跨列:  SUM(AA21:AC21)  这种
					 select (select num from t_numTOEnChar where enchar = str_2_head) - (select num from t_numTOEnChar where enchar = str_1_head) into gap
						from dual;

					for i in 1..gap
						 loop
									-- select enchar||str_1_tail|| '+' from t_numTOEnChar where num = (select num+1 from t_numTOEnChar where enchar = 'AA');
								 select enchar into new_enchar from t_numTOEnChar where num = (select num+i from t_numTOEnChar where enchar = str_1_head);
								 rtnStr:= rtnStr||'+' ||new_enchar||str_1_tail;
							end loop ;

				end if ;
		else -- 没有冒号直接拼上
		     if rtnStr is null then
			      rtnStr := item.column_value||'+';
         elsif instr(rtnStr,'+',-1) <> length(rtnStr) then
			      rtnStr := rtnStr||'+'||item.column_value||'+';
				 else
			      rtnStr := rtnStr||item.column_value||'+';
         end if;
		end if;
	end loop;
	
	--如果最后有+ 去掉
	if instr(rtnStr,'+',-1) = length(rtnStr) then
	  select substr(rtnStr,1,length(rtnStr)-1) into rtnStr from dual;
	end if;

  RETURN '('||rtnStr||')';
END;