用wm_concat合并行及merge into更新

已知需求：

kmb结构：
as_code varchar2(21),
as_name varchar2(30)
样例数据：(as_code列的编码是规则的，即3-3-3-3)
as_code     as_name
501         经费支出
501001      商品服务支出
501001001   办公费
501001001001  其他杂项支出

pzb结构：
as_code varchar2(21),
full_name varchar2(200)
样例数据
as_code               as_name
501              
501001
501001001
501001001001

现编写sql实现如下功能：
更新pzb的as_name列，使名称按全称显示即：
as_code       full_name
501           经费支出
501001        经费支出-商品服务支出
501001001     经费支出-商品服务支出-办公费
501001001001  经费支出-商品服务支出-办公费-其他杂项支出

--造表及数据
create table kmb(
as_code varchar2(21) CONSTRAINT kmb_pk PRIMARY KEY,
as_name varchar2(30)) ;

create table pzb(
as_code varchar2(21) CONSTRAINT pzb_fk_kmb REFERENCES kmb(as_code),
full_name varchar2(200));

insert into kmb(as_code, as_name) values('501','经费支出');
insert into kmb(as_code, as_name) values('501001','商品服务支出');
insert into kmb(as_code, as_name) values('501001001','办公费');
insert into kmb(as_code, as_name) values('501001001001','其他杂项支出');
insert into pzb(as_code) select as_code from kmb;
commit;

SQL> select * from kmb;

AS_CODE               AS_NAME
--------------------- ------------------------------
501                           经费支出
501001                    商品服务支出
501001001              办公费
501001001001       其他杂项支出

SQL> select * from pzb;

AS_CODE               FULL_NAME
--------------------- ------------------------------
501                  
501001               
501001001            
501001001001         

--实现：oracle提供了一个函数wm_concat函数可以合并列，利用此函数将表kmb中同一个大类的as_name的不同列按顺序合并成一行，wm_concat默认连接字符为','，可用replace将其转换成'-'以符合需求，这样即可得出最细分类的串。最后按条件取结果集用merge into更新pzb表的full_name字段。脚本如下：
SQL> merge into pzb p
  2  using (
  3  select p.as_code as_code, substr(a.wm, 1, instr(a.wm, '-', 1, length(p.as_code)/3)-1) full_name
  4         from kmb p,
  5   (select as_code, replace(wm, ',','-')||'-' wm from
  6     (select min(as_code) as_code, wm_concat(as_name) wm from kmb where 1=1 group by substr(as_code,1,3)))a
  7          where substr(p.as_code,1,3)=a.as_code)a
  8  on (p.as_code=a.as_code)
  9  when matched then
 10  update set full_name=a.full_name;
 
SQL> commit;
SQL> select * from pzb;

AS_CODE                 FULL_NAME
---------------------     ------------------------------------------
501                              经费支出
501001                       经费支出-商品服务支出
501001001                 经费支出-商品服务支出-办公费
501001001001          经费支出-商品服务支出-办公费-其他杂项支出

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16210564/viewspace-695503/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16210564/viewspace-695503/