oracle嵌套xml解析

1hdlvixo 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(371)

我有一个excel，当我用应用程序导入excel时，我将其转换为xml字符串，然后将其发送到oracle中的过程。下面的代码是我的程序。

PROCEDURE SP_UPLOAD_KALEMS(P_REFCURSOR OUT SYS_REFCURSOR,P_YEAR IN NUMBER,P_MONTH IN NUMBER,P_KALEMS IN CLOB) IS personelId NUMBER; BEGIN
     SAVEPOINT start_tran;
     DELETE FROM HRANALY.WAGE_ACTUAL WA WHERE WA.A_MONTH=P_MONTH AND WA.A_YEAR=P_YEAR;
       FOR r IN (
            select * FROM XMLTABLE('/ArrayOfBaseUpload/BaseUpload'PASSING xmltype(P_KALEMS) 
            COLUMNS  
                SICIL  NUMBER    PATH './SICIL',  
                SAP_ORG_KOD NUMBER     PATH './SAP_ORG_KOD' ,
                POSITION VARCHAR2(100)     PATH './POSITION',
                IS_INFO VARCHAR2(10) PATH './IS',
                FABRIC VARCHAR2(10) PATH './FABRIC',
                COST_CENTER NUMBER     PATH './COST_CENTER' ,
                PERSONE_TYPE VARCHAR2(10) PATH './PERSONE_TYPE',
                UCRET_TIP VARCHAR2(10)     PATH './UCRET_TIP' ,
                BELGE_KOD VARCHAR2(200) PATH './BELGE_KOD',
                TUTAR NUMBER     PATH './TUTAR' ,
                SGK_GUN NUMBER     PATH './SGK_GUN' ,
                SSK_MATRAH NUMBER     PATH './SSK_MATRAH' ,
                SS_MATRAH NUMBER     PATH './SS_MATRAH' ,
                YASAL_NET NUMBER     PATH './YASAL_NET' ,
                ODEME_TUTARI NUMBER     PATH './ODEME_TUTARI' 
                )  PERSONELS
        )LOOP
            personelId:=HRANALY.SEQ_WAGE_MAIN.nextval;
            INSERT INTO  HRANALY.WAGE_ACTUAL(ID,SICIL,SAP_ORG_KOD, POSITION,IS_INFO,FABRIC,COST_CENTER,PERSONE_TYPE,A_MONTH,A_YEAR,UCRET_TIP,BELGE_KOD,
                                             TUTAR,SGK_GUN,SSK_MATRAH,SS_MATRAH,YASAL_NET,ODEME_TUTARI)
            VALUES(personelId,r.SICIL,r.SAP_ORG_KOD,r.POSITION,r.IS_INFO,r.FABRIC,r.COST_CENTER,r.PERSONE_TYPE,P_MONTH,P_YEAR,R.UCRET_TIP,r.BELGE_KOD,
                                             r.TUTAR,r.SGK_GUN,r.SSK_MATRAH,r.SS_MATRAH,r.YASAL_NET,r.ODEME_TUTARI);
            FOR p IN (
                select * FROM XMLTABLE('/ArrayOfBaseUpload/BaseUpload/DETAILS/DetayUpload'PASSING xmltype(P_KALEMS)              
                COLUMNS CODE varchar2(100) PATH './CODE', 
                SICIL NUMBER PATH './../../SICIL',
                AMOUNT NUMBER PATH './AMOUNT') kalems            
            )
            LOOP
                IF r.SICIL=p.SICIL THEN
                    INSERT INTO HRANALY.WAGE_ACTUAL_DETAIL(ID,REF_WAGE,AMOUNT,KALEM_KOD,A_MONTH,A_YEAR)
                    VALUES(HRANALY.SEQ_WAGE_DETAIL.nextval,personelId,p.AMOUNT,p.CODE,P_MONTH,P_YEAR);
                END IF;           
            END LOOP;
        END LOOP;  
        COMMIT;
        OPEN P_REFCURSOR FOR
        SELECT 'SUCCESS' AS RESULT FROM DUAL;  
     EXCEPTION
     WHEN OTHERS THEN
       ROLLBACK TO start_tran;
        OPEN P_REFCURSOR FOR
        SELECT 'ERROR' AS RESULT FROM DUAL;
       RAISE;
    END ;

我的问题是，我在excell中有大约2000行和大约40列（可以多多少少）。其中15个是静态列，保存在 HRANALY.WAGE_ACTUAL 其他表是动态的，并插入到 HRANALY.WAGE_ACTUAL_DETAIL . SICIL 是唯一的，例如用户的标识码。对于一个sicil，可以将多个详细信息插入到 HRANALY.WAGE_ACTUAL_DETAIL 我的代码插入所有变量花费了太多时间。我想更快地优化这个代码。我怎样才能加速呢。
我的xml像

<ArrayOfBaseUpload>
    <BaseUpload>
        <SICIL>1</SICIL>
        <SAP_ORG_KOD>500</SAP_ORG_KOD>
        <POSITION>Operator</POSITION>
        <IS>TR - Dikim </IS>
        <FABRIC>IZ01</FABRIC>
        <COST_CENTER>100</COST_CENTER>
        <PERSONE_TYPE>T2</PERSONE_TYPE>
        <UCRET_TIP>Brüt</UCRET_TIP>
        <BELGE_KOD>1</BELGE_KOD>
        <TUTAR>10.00</TUTAR>
        <SGK_GUN>30</SGK_GUN>
        <SSK_MATRAH>100</SSK_MATRAH>
        <SS_MATRAH>100</SS_MATRAH>
        <YASAL_NET>100</YASAL_NET>
        <ODEME_TUTARI>100</ODEME_TUTARI>
        <DETAILS>
            <DetayUpload><CODE>TEMEL_UCRET</CODE><AMOUNT>100</AMOUNT></DetayUpload>//here there can ve 40 data like that
        </DETAILS>
    </BaseUpload>
</ArrayOfBaseUpload>

提前谢谢

sql stored-procedures oracle oracle11g

来源：https://stackoverflow.com/questions/62328162/oracle-nested-xml-parsing

2条答案

按热度按时间

qrjkbowd1#

编写慢sql的最好方法是 insert/update/delete 在一次改变一行的循环中。
如果您想要快速sql，可以使用一条语句来更改所有行。将回路替换为两个 insert-select 声明应该加速：

insert into hranaly.wage_actual(
  id,sicil,sap_org_kod, position,is_info,fabric,cost_center,persone_type,a_month,a_year,ucret_tip,belge_kod,
  tutar,sgk_gun,ssk_matrah,ss_matrah,yasal_net,odeme_tutari
)
select hranaly.seq_wage_main.nextval, sicil,sap_org_kod,
       position,is_info,fabric,cost_center,persone_type,
       p_month,p_year,ucret_tip,belge_kod,tutar,sgk_gun,
       ssk_matrah,ss_matrah,yasal_net,odeme_tutari
from   xmltable('/ArrayOfBaseUpload/BaseUpload' passing xmltype( p_kalems ) 
columns  
  sicil  number    path './SICIL',  
  sap_org_kod number     path './SAP_ORG_KOD' ,
  position varchar2(100)     path './POSITION',
  is_info varchar2(10) path './IS',
  fabric varchar2(10) path './FABRIC',
  cost_center number     path './COST_CENTER' ,
  persone_type varchar2(10) path './PERSONE_TYPE',
  ucret_tip varchar2(10)     path './UCRET_TIP' ,
  belge_kod varchar2(200) path './BELGE_KOD',
  tutar number     path './TUTAR' ,
  sgk_gun number     path './SGK_GUN' ,
  ssk_matrah number     path './SSK_MATRAH' ,
  ss_matrah number     path './SS_MATRAH' ,
  yasal_net number     path './YASAL_NET' ,
  odeme_tutari number     path './ODEME_TUTARI' 
);
insert into hranaly.wage_actual_detail(id,ref_wage,amount,kalem_kod,a_month,a_year)
  select hranaly.seq_wage_detail.nextval,wa.id,
         p.amount,p.code,p_month,p_year
  from   hranaly.wage_actual wa 
  join   xmltable('/ArrayOfBaseUpload/BaseUpload/DETAILS/DetayUpload' passing xmltype ( p_kalems )              
      columns 
        code varchar2(100) path './CODE', 
        sicil number path './../../SICIL',
        amount number path './AMOUNT'
    ) kalems
  on     wa.a_month= p_month 
  and    wa.a_year=p_year 
  and    wa.sicil=kalems.sicil;

您甚至可以将其制作成一个多表插入( insert all )可能更快。不过，我只会在上述变化仍然太慢的情况下研究这个问题。

展开查看全部

赞(0）回复(0）举报 2021-07-29

ibps3vxo2#

分别用写循环来解决。

PROCEDURE SP_UPLOAD_KALEMS(P_REFCURSOR OUT SYS_REFCURSOR,P_YEAR IN NUMBER,P_MONTH IN NUMBER,P_KALEMS IN CLOB) IS personelId NUMBER; BEGIN
     SAVEPOINT start_tran;
     DELETE FROM HRANALY.WAGE_ACTUAL WA WHERE WA.A_MONTH=P_MONTH AND WA.A_YEAR=P_YEAR;
     DELETE FROM HRANALY.WAGE_ACTUAL_DETAIL WA WHERE WA.A_MONTH=P_MONTH AND WA.A_YEAR=P_YEAR;
       FOR r IN (
            select * FROM XMLTABLE('/ArrayOfBaseUpload/BaseUpload'PASSING xmltype(P_KALEMS) 
            COLUMNS  
                SICIL  NUMBER    PATH './SICIL',  
                SAP_ORG_KOD NUMBER     PATH './SAP_ORG_KOD' ,
                POSITION VARCHAR2(100)     PATH './POSITION',
                IS_INFO VARCHAR2(10) PATH './IS',
                FABRIC VARCHAR2(10) PATH './FABRIC',
                COST_CENTER NUMBER     PATH './COST_CENTER' ,
                PERSONE_TYPE VARCHAR2(10) PATH './PERSONE_TYPE',
                UCRET_TIP VARCHAR2(10)     PATH './UCRET_TIP' ,
                BELGE_KOD VARCHAR2(200) PATH './BELGE_KOD',
                TUTAR NUMBER     PATH './TUTAR' ,
                SGK_GUN NUMBER     PATH './SGK_GUN' ,
                SSK_MATRAH NUMBER     PATH './SSK_MATRAH' ,
                SS_MATRAH NUMBER     PATH './SS_MATRAH' ,
                YASAL_NET NUMBER     PATH './YASAL_NET' ,
                ODEME_TUTARI NUMBER     PATH './ODEME_TUTARI' 
                )  PERSONELS
        )LOOP
            personelId:=HRANALY.SEQ_WAGE_MAIN.nextval;
            INSERT INTO  HRANALY.WAGE_ACTUAL(ID,SICIL,SAP_ORG_KOD, POSITION,IS_INFO,FABRIC,COST_CENTER,PERSONE_TYPE,A_MONTH,A_YEAR,UCRET_TIP,BELGE_KOD,
                                             TUTAR,SGK_GUN,SSK_MATRAH,SS_MATRAH,YASAL_NET,ODEME_TUTARI)
            VALUES(personelId,r.SICIL,r.SAP_ORG_KOD,r.POSITION,r.IS_INFO,r.FABRIC,r.COST_CENTER,r.PERSONE_TYPE,P_MONTH,P_YEAR,R.UCRET_TIP,r.BELGE_KOD,
                                             r.TUTAR,r.SGK_GUN,r.SSK_MATRAH,r.SS_MATRAH,r.YASAL_NET,r.ODEME_TUTARI);
        END LOOP;
        FOR p IN (
            select * FROM XMLTABLE('/ArrayOfBaseUpload/BaseUpload/DETAILS/DetayUpload'PASSING xmltype(P_KALEMS)              
            COLUMNS CODE varchar2(100) PATH './CODE', 
            SICIL NUMBER PATH './../../SICIL',
            AMOUNT NUMBER PATH './AMOUNT') kalems            
        )
        LOOP
            INSERT INTO HRANALY.WAGE_ACTUAL_DETAIL(ID,REF_WAGE,AMOUNT,KALEM_KOD,A_MONTH,A_YEAR)
            SELECT HRANALY.SEQ_WAGE_DETAIL.nextval,WA.ID,p.AMOUNT,p.CODE,P_MONTH,P_YEAR
            FROM HRANALY.WAGE_ACTUAL WA WHERE WA.A_MONTH= P_MONTH AND WA.A_YEAR=P_YEAR AND WA.SICIL=p.SICIL;
        END LOOP;  
        COMMIT;
        OPEN P_REFCURSOR FOR
        SELECT 'SUCCESS' AS RESULT FROM DUAL;  
     EXCEPTION
     WHEN OTHERS THEN
       ROLLBACK TO start_tran;
        OPEN P_REFCURSOR FOR
        SELECT 'ERROR' AS RESULT FROM DUAL;
       RAISE;
    END ;

展开查看全部

赞(0）回复(0）举报 2021-07-29

我来回答

oracle嵌套xml解析

2条答案

相关问题

热门标签

最新问答