创建生成级联上级字符的函数

需求：现有表dw,里面字段bm(编码),sj(上级编码),

      新增字段px,要求在新字段中添加字符串，字符串为单位的级联上级及自身，

并从顶向下，从左到右排序，中间以“|”分割。

     如：单位代码为005，上级单位代码为004，

         单位004的上级单位单面是003,单位003的上级代码是002，

         单位002的上级是最上级单位001。

         则取出数据应为："001|002|003|004|005"

实现：

1  首先取得某一单位的级联上级的数据 

   可以通过sql：

select sj  

  from dw T  

  START WITH T.bm=:BM  

  CONNECT BY PRIOR T.sjbm=T.bm ;

取出该单位的所有级联上级的单位编码

2  通过管道函数能列出某个单位的级联单位代码及自身单位代码并返回结果集 

create or replace FUNCTION                                                   upbm

(

  bm_IN IN VARCHAR2 

)  RETURN bm_DATA pipelined   

-----通过单位编码和单位分类取得上级单位编码(包括自己)数据集 

  AS 

  bm_ROW yly_row_type;

  bm_TAB bm_DATA;

  BEGIN 

  FOR MYROW IN (

  select sjbm  

  from dw T  

  START WITH T.bm=bm_IN   

  CONNECT BY PRIOR T.sj=T.bm 

  UNION ALL 

  SELECT bm 

  FROM dw 

  WHERE bm=bm_IN 

  ORDER BY 1

  ) LOOP 

  bm_ROW := YLY_ROW_TYPE(MYROW.sj);

  PIPE ROW (bm_ROW);

  END LOOP; 

  RETURN;

  END upbm; 

 这时通过select * from table(getupperdeptwitchself(:bm)),取得数据集。

001

002

003

004

005

3  通过函数把取得的数据集转换为一行 

   可以通过wm_concat函数，该函数把输入的结果集转换为1行并以","分割，需要转换为"|"

create or replace FUNCTION                                    upbm_px

(

  bm_IN IN VARCHAR2 

) RETURN VARCHAR2 AS 

 px VARCHAR2(400);

BEGIN 

  SELECT replace(wm_concat(bm),',','|') INTO px from table(upbm(bm_IN)); 

  RETURN px;

END upbm_px;

这时通过函数即可得到一行数据：

    select GET_UPPERDEPT_PX(bm) from dual;

得到数据： "001|002|003|004|005"

这时已基本完成需求。

接下来制作触发器，使表在插入时自动生成sjbm_xp数据。

4 创建触发器

create or replace TRIGGER "dw_SJ_TRG" 

  before insert on dw 

  for each row  

declare

begin  

--插入数据时生成px字段

  :NEW.px :=upbm_px(:NEW.bm); 

end;

但是在插入时报错：ORA-04091：表dw发生了变化 触发器/函数不能读表 

问题原因：oracle执行DML语句时需要显示进行提交操作。当我们进行插入时会触发触发器

         执行对触发器作用表和扩展表，但这时触发器和插入表在同一事物中，插入语句

没有提交时无法对触发器表进行额外操作。 

解决方法：

        把触发器改为显示提交

create or replace TRIGGER "dw_SJ_TRG" 

  before insert on dw 

  for each row  

declare

pragma autonomous_transaction;

begin  

--插入数据时生成px字段

  :NEW.px :=GET_UPPERDEPT_PX(:NEW.bm); 

  commit;

end;

5  全表更新

  把表dw的所有行的px字段补全 

UPDATE dw SET px=GET_UPPERDEPT_PX(bm)；

这时报错:ORA-04091：表dw发生了变化 触发器/函数不能读表

错误原因与刚刚类似：

要更新的表是dw,取得结果集的函数upbm依靠dw进行循环计算，

    而更新之后循环的来源已经产生变化，ORACLE不允许这样，可能会产生无限循环。

解决办法： 创建新表数据dw_BAK与dw表一致，把upbm函数中的

  来源表从dw改为dw_BAK.

        执行语句 UPDATE dw SET px=GET_UPPERDEPT_PX(bm)；

提交后把函数upbm的来源改回dw.