創(chuàng)建生成級聯(lián)上級字符的函數(shù)

需求：現(xiàn)有表dw,里面字段bm(編碼),sj(上級編碼),

      新增字段px,要求在新字段中添加字符串，字符串為單位的級聯(lián)上級及自身，

并從頂向下，從左到右排序，中間以“|”分割。

     如：單位代碼為005，上級單位代碼為004，

         單位004的上級單位單面是003,單位003的上級代碼是002，

         單位002的上級是最上級單位001。

         則取出數(shù)據(jù)應(yīng)為："001|002|003|004|005"

實現(xiàn)：

1  首先取得某一單位的級聯(lián)上級的數(shù)據(jù) 

   可以通過sql：

select sj  

  from dw T  

  START WITH T.bm=:BM  

  CONNECT BY PRIOR T.sjbm=T.bm ;

取出該單位的所有級聯(lián)上級的單位編碼

2  通過管道函數(shù)能列出某個單位的級聯(lián)單位代碼及自身單位代碼并返回結(jié)果集 

create or replace FUNCTION                                                   upbm

(

  bm_IN IN VARCHAR2 

)  RETURN bm_DATA pipelined   

-----通過單位編碼和單位分類取得上級單位編碼(包括自己)數(shù)據(jù)集 

  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)),取得數(shù)據(jù)集。

001

002

003

004

005

3  通過函數(shù)把取得的數(shù)據(jù)集轉(zhuǎn)換為一行 

   可以通過wm_concat函數(shù)，該函數(shù)把輸入的結(jié)果集轉(zhuǎn)換為1行并以","分割，需要轉(zhuǎn)換為"|"

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;

這時通過函數(shù)即可得到一行數(shù)據(jù)：

    select GET_UPPERDEPT_PX(bm) from dual;

得到數(shù)據(jù)： "001|002|003|004|005"

這時已基本完成需求。

接下來制作觸發(fā)器，使表在插入時自動生成sjbm_xp數(shù)據(jù)。

4 創(chuàng)建觸發(fā)器

create or replace TRIGGER "dw_SJ_TRG" 

  before insert on dw 

  for each row  

declare

begin  

--插入數(shù)據(jù)時生成px字段

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

end;

但是在插入時報錯：ORA-04091：表dw發(fā)生了變化 觸發(fā)器/函數(shù)不能讀表 

問題原因：oracle執(zhí)行DML語句時需要顯示進行提交操作。當我們進行插入時會觸發(fā)觸發(fā)器

         執(zhí)行對觸發(fā)器作用表和擴展表，但這時觸發(fā)器和插入表在同一事物中，插入語句

沒有提交時無法對觸發(fā)器表進行額外操作。 

解決方法：

        把觸發(fā)器改為顯示提交

create or replace TRIGGER "dw_SJ_TRG" 

  before insert on dw 

  for each row  

declare

pragma autonomous_transaction;

begin  

--插入數(shù)據(jù)時生成px字段

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

  commit;

end;

5  全表更新

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

UPDATE dw SET px=GET_UPPERDEPT_PX(bm)；

這時報錯:ORA-04091：表dw發(fā)生了變化 觸發(fā)器/函數(shù)不能讀表

錯誤原因與剛剛類似：

要更新的表是dw,取得結(jié)果集的函數(shù)upbm依靠dw進行循環(huán)計算，

    而更新之后循環(huán)的來源已經(jīng)產(chǎn)生變化，ORACLE不允許這樣，可能會產(chǎn)生無限循環(huán)。

解決辦法： 創(chuàng)建新表數(shù)據(jù)dw_BAK與dw表一致，把upbm函數(shù)中的

  來源表從dw改為dw_BAK.

        執(zhí)行語句 UPDATE dw SET px=GET_UPPERDEPT_PX(bm)；

提交后把函數(shù)upbm的來源改回dw.