数字求和问题字符串分组相加

数字8546把各个位数相加

create or replace function f_convertion(num in number) return number is --此处暂时考虑num_in为整数
num_in number(18,0);
num_out number(18,0) default 0;
begin
num_in := num;
dbms_output.put_line(num_in);

if length(num_in) <= 1 then
        return num_in;
end if;

for i in 1..length(num_in) loop
        num_out := num_out + floor(num_in / power(10,length(num_in)-1));
        num_in := substr(num_in, - length(num_in) + 1);
end loop;

return num_out;
end f_convertion;
/

select f_convertion(123456) from dual;

 

一般位数总是有限的，可以这样，
SQL> SELECT NVL(SUBSTRB('8546',-1,1),0)+
             NVL(SUBSTRB('8546',-2,1),0)+
             NVL(SUBSTRB('8546',-3,1),0)+
             NVL(SUBSTRB('8546',-4,1),0)+
             NVL(SUBSTRB('8546',-5,1),0)+
             NVL(SUBSTRB('8546',-6,1),0)+
             NVL(SUBSTRB('8546',-7,1),0)+
             NVL(SUBSTRB('8546',-8,1),0) 各个位数相加
       FROM DUAL；


各个位数相加
------------
          23

 

稍微改进一下：

create or replace function f_convertion(num_in in number) return number is --此处暂时考虑num_in为整数

num_out number(18,0) default 0;

begin

if length(num_in) <= 1 then
        return num_in;
end if;

for i in 1..length(num_in) loop
        num_out := num_out + substr(num_in,i,1);
end loop;

return num_out;

end f_convertion;
/


select f_convertion(123456) from dual;

F_CONVERTION(123456)
--------------------
                  21

 

SQL> SELECT SUM(NM) TOTAL
  2    FROM (SELECT SUBSTR(&innum, ROWNUM, 1) NM
  3            FROM DUAL CONNECT BY ROWNUM <= LENGTH(&innum))
  4  /

     TOTAL
----------
        10

SQL> set verify on
SQL>
SQL> SELECT SUM(NM) TOTAL
  2    FROM (SELECT SUBSTR(&innum, ROWNUM, 1) NM
  3            FROM DUAL CONNECT BY ROWNUM <= LENGTH(&innum))
  4  /
&innum: 1234
&innum: 1234

     TOTAL
----------
        10

SQL> /
&innum: 5678
&innum: 5678

     TOTAL
----------
        26

 

 

SQL> SELECT SUM(NM) TOTAL
  2    FROM (SELECT SUBSTR(&innum, ROWNUM, 1) NM
  3            FROM DUAL CONNECT BY ROWNUM <= LENGTH(&innum))
  4  /

     TOTAL
----------
        10

SQL> set verify on
SQL>
SQL> SELECT SUM(NM) TOTAL
  2    FROM (SELECT SUBSTR(&innum, ROWNUM, 1) NM
  3            FROM DUAL CONNECT BY ROWNUM <= LENGTH(&innum))
  4  /
&innum: 1234
&innum: 1234

     TOTAL
----------
        10

SQL> /
&innum: 5678
&innum: 5678

     TOTAL
----------
        26

 

字符串分组相加方法之总结


--创建测试表、添加测试数据

create table test(id varchar2(10),mc varchar2(50));
insert into test values('1','11111');
insert into test values('1','22222');
insert into test values('2','11111');
insert into test values('2','22222');
insert into test values('3','11111');
insert into test values('3','22222');
insert into test values('3','33333');
commit;

--方法一：


set serveroutput on size 1000000
declare
union_mc varchar2(200);
begin
for cur_a in(select distinct id from test) loop
for cur_b in(select mc from test where id=cur_a.id) loop
union_mc:=union_mc||cur_b.mc;
end loop;
dbms_output.put_line(cur_a.id||chr(9)||union_mc);
union_mc := '';
end loop;
end;
/

--方法二：

CREATE OR REPLACE function link(v_id varchar2)
return varchar2
is
union_mc varchar2(200);
begin
for cur in (select mc from test where id=v_id) loop
union_mc := union_mc||cur.mc;
end loop;
union_mc := rtrim(union_mc,1);
return union_mc;
end;
/

select id,link(id) from test group by id;

--方法三：

/*从Oracle 9i开始，开发者可以创建用户自定义的合计函数，除了PL/SQL外，还可以使用任何Oralce所支持的语言（如C＋＋或者Java）来创建合计函数。 TYPE头定义必须包含ODCIAggregateInitialize、ODCIAggregateIterate、 ODCIAggregateMerge和ODCIAggregateTerminate这四个接口函数。*/

/*Initialize函数对数据组各个需要处理的字段各运行一次。自然的，我需要为每一个值准备一个新的清单，所以需要初始化持久变量list，这里初始化值为null。*/

/*Iterate函数处理返回的行，所以实际上是由它来创建返回的值的清单。先测试list是否为空，如果为空，就把list直接设置为所引入的 value值；如果list变量非空，则给list添加一个逗号后再插入value值，list的最大允许字符数32767。*/

/*Terminate函数在数据组的每个行的感兴趣字段数据被处理后执行。在这个函数中我只需简单的返回清单变量即可。*/

/*Merge函数，用来返回成功标记的。*/

/*创建自己的合计函数扩展了Oracle统计和文本处理能力。*/


create or replace type t_cat as object
(
union_mc VARCHAR2(200),
static function ODCIAggregateInitialize(sctx IN OUT t_cat) return number,
member function ODCIAggregateIterate(self IN OUT t_cat,value IN varchar2) return number,
member function ODCIAggregateTerminate(self IN t_cat,returnValue OUT varchar2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT t_cat,ctx2 IN t_cat) return number
);

create or replace type body t_cat is
static function ODCIAggregateInitialize(sctx IN OUT t_cat )
return number is
begin
sctx := t_cat('');
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT t_cat, value IN varchar2)
return number is
begin
self.union_mc := self.union_mc || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN t_cat, returnValue OUT varchar2, flags IN number) return number is
begin
returnValue := self.union_mc;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT t_cat , ctx2 IN t_cat ) return number is
begin
return ODCIConst.Success;
end;
end;
/

/*如果你的Oracle服务器没有配置成支持并行处理的方式，可以去掉参数PARALLEL_ENABLE*/

create or replace function catstr(v_mc varchar2) return varchar2 PARALLEL_ENABLE AGGREGATE USING t_cat;
/

select id,catstr(mc) from test group by id;

--方法四：
create table test(id varchar2(10),mc varchar2(50));
insert into test values('1','11111');
insert into test values('1','22222');
insert into test values('2','11111');
insert into test values('2','22222');
insert into test values('3','11111');
insert into test values('3','22222');
insert into test values('3','33333');

commit;

select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test;

/*
利用分析函数，构造两列，做为连接的条件：按照id分组，RN-1等于PRIOR RN作为条件连接。
ID         MC                                                   RN_BY_ID         RN
---------- -------------------------------------------------- ---------- ----------
1          11111                                                       1          2
1          22222                                                       2          3
2          11111                                                       1          5
2          22222                                                       2          6
3          11111                                                       1          8
3          22222                                                       2          9
3          33333                                                       3         10
*/

select id,ltrim(max(sys_connect_by_path(mc,';')),';') add_mc from (
select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test
)
start with rn_by_id = 1 connect by rn - 1 = prior rn
group by id
order by id;

/*
另用sys_connect_by_path函数实现字符串的连接，把最左边的分号去掉，即得到我们想要的结果
ID         ADD_MC
---------- --------------------------------------------------------------------------------
1          11111;22222
2          11111;22222
3          11111;22222;33333
*/

select * from test;

 

 

估计类似的写法还有很多，这个和上一个不同在于用的没有带有start with（filter功能）的connect，并借助level和first_value来实现。

SELECT distinct id,ltrim(first_value(mc_add) over (partition by id order BY l DESC),';')
from (
SELECT id,LEVEL l,sys_connect_by_path(mc,';') mc_add from
(
select id||rownum rn,id||rownum-1 rn_small,id,mc from test
)
CONNECT BY PRIOR rn = rn_small
)
;

 

 

变换一下：（考虑id不是数字的情况）

select id,ltrim(max(sys_connect_by_path(mc,';')),';') from(
select id,mc,row_number() over(partition by id order by id) id1,
row_number() over(order by id) + dense_rank() over(order by id) id2
from test
)
start with id1=1 connect by prior id2 = id2 -1
group by id order by id;