当使用字符串长度超过限制
可以使用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;