存储过程问题集合

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

可以使用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 )ASV_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 isselect '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_TMPWHERE 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;