存储过程问题集合

当使用字符串长度超过限制

可以使用CLOB类型来存储字符串。

使用方式：
dbms_lob.createtemporary(ANLN2STR,true);--创建临时区
dbms_lob.append(ANLN1STR,v_temp_str);--往ANLN1STR追加字符串
to_char(dbms_lob.SUBSTR(ANLN1STR,32767,1);--CLOB转字符串，这里有个坑，dbms_lob.SUBSTR和SUBSTR函数后两个参数是相反意义的，详细用法百度。

dbms_lob.freetemporary(ANLN1STR);--释放临时区

CREATE OR REPLACE PROCEDURE CPM_SP_IPRO_TEST(  V_SCENARIO IN varchar2,
                                          V_PERIODO  IN varchar2,
                                          V_USERUPD  IN varchar2 )
AS
V_PROVENIENZA varchar2(20);
V_PROSPETTO   varchar2(20);
ICOUNT  Number;
UCOUNT  Number;
ANLN1STR  CLOB;
ANLN2STR  CLOB;
BUKRSSTR  CLOB;
v_temp_str VARCHAR2(32767);
cursor cursor_1 is
select '2900' BUKRS,ANLN1,ANLN2,TXT50,PDEBIT,DEBIT,PCREDIT,CREDIT,END_BALANCE
  ......
); --声明游标初始化为ods查询结果
BEGIN

 V_PROVENIENZA :=  'MAP_SP_IPRO';
 V_PROSPETTO  :=  'ZG_GL0001_IPT01';
 ICOUNT := 0;
 UCOUNT := 0;
 v_temp_str := '';
 dbms_lob.createtemporary(ANLN1STR,true);
 dbms_lob.createtemporary(ANLN2STR,true);
 dbms_lob.createtemporary(BUKRSSTR,true);
 FOR cu IN cursor_1 LOOP
   --记录抽取数据有的资产号，次资产号，公司
    IF UCOUNT = 0 THEN
     v_temp_str := trim(cu.ANLN1);
     dbms_lob.append(ANLN1STR,v_temp_str);
     v_temp_str := cu.ANLN2;
     dbms_lob.append(ANLN2STR,v_temp_str);
     v_temp_str := cu.BUKRS;
     dbms_lob.append(BUKRSSTR,v_temp_str);
    ELSE 
     v_temp_str := ','||cu.ANLN1;
     dbms_lob.append(ANLN1STR,v_temp_str);
     v_temp_str := ','||cu.ANLN2;
     dbms_lob.append(ANLN2STR,v_temp_str);
     v_temp_str := ','||cu.BUKRS;
     dbms_lob.append(BUKRSSTR,v_temp_str);
    END IF;

   --更新FORM_DATI中存在于抽取数据的记录
    UPDATE FORM_DATI_TMP
     SET IMPORTO_2  = cu.PDEBIT,
         IMPORTO_3   = cu.DEBIT,
         IMPORTO_67 = cu.PCREDIT,
         IMPORTO_4  = cu.CREDIT,
         IMPORTO_5 = cu.END_BALANCE
   WHERE COD_PROSPETTO = V_PROSPETTO
     AND COD_SCENARIO  = V_SCENARIO
     AND COD_PERIODO   = V_PERIODO
     AND COD_AZIENDA   = cu.BUKRS
     AND PROVENIENZA   = V_PROVENIENZA
     AND TESTO_6       = cu.ANLN1
     AND TESTO_7       = cu.ANLN2
     AND COD_CATEGORIA = '$AMOUNT';
  UCOUNT := UCOUNT+1;
  --插入现存在而原先不存在的记录
  IF sql%rowcount <= 0 OR sql%rowcount IS NULL THEN
   UCOUNT := UCOUNT-1;
   INSERT /* +APPEND*/  INTO FORM_DATI_TMP(
                OID_FORM_DATI ,
                COD_PROSPETTO,
                COD_SCENARIO,
                COD_PERIODO,
                COD_AZIENDA,
                TESTO_6,
                TESTO_7,
                TESTO_1,
                COD_CATEGORIA,
                IMPORTO_2,
                IMPORTO_3,
                IMPORTO_67,
                IMPORTO_4,
                IMPORTO_5,
                DATEUPD,
                USERUPD,
                PROVENIENZA
                )
         VALUES(
         NEWID(),
         V_PROSPETTO,
         V_SCENARIO,
         V_PERIODO,
         cu.BUKRS,
         cu.ANLN1,
         cu.ANLN2,
         cu.TXT50,
         '$AMOUNT',
         cu.PDEBIT,
         cu.DEBIT,
         cu.PCREDIT,
         cu.CREDIT,
         cu.END_BALANCE,
         SYSDATE,
         V_USERUPD,
         V_PROVENIENZA
         );
     ICOUNT :=  ICOUNT +1;
    END IF;
 END LOOP;
 dbms_output.put_line('插入FORM_DATI_TMP条目数：'||to_char(ICOUNT)||'条');
 dbms_output.put_line('更新FORM_DATI_TMP条目数：'||to_char(UCOUNT)||'条');
 --删除不存在与抽取数据（资产号 +次资产号+公司）的数据
 DELETE from FORM_DATI_TMP
	WHERE COD_SCENARIO = V_SCENARIO
	  AND COD_PERIODO = V_PERIODO
	  AND PROVENIENZA = V_PROVENIENZA
	  AND COD_PROSPETTO = V_PROSPETTO
	  AND TESTO_6  NOT IN (
                             to_char(dbms_lob.SUBSTR(ANLN1STR,32767,1))||
                             to_char(dbms_lob.SUBSTR(ANLN1STR,32767,32767))||
                             to_char(dbms_lob.SUBSTR(ANLN1STR,32767,32767+32767))
                              
                         ) AND ...;
 dbms_output.put_line('从FORM_DATI_TMP删除条目数：'||to_char(sql%rowcount)||'条');
 dbms_lob.freetemporary(ANLN1STR);--释放lob
 dbms_lob.freetemporary(ANLN2STR);--释放lob
 dbms_lob.freetemporary(BUKRSSTR);--释放lob
 COMMIT;

EXCEPTION
  WHEN OTHERS
  THEN rollback;
  dbms_output.put_line('捕获到异常,完成回滚:'||'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;