编写慢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 )可能更快。不过，我只会在上述变化仍然太慢的情况下研究这个问题。

分别用写循环来解决。

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 ;