廠家寫的存盤程序：
create or replace procedure PDataSavedWithRelation_Sp(p_string in Varchar2,p_relation in Varchar2
) as
v_num number;
v_haverelation number;
V_ERRMSG varchar2(80);
V_ERRNO number;
-- v_isinsert number;
v_pkfield varchar2(50);
v_temp varchar2(50);
v_PKValuetemp varchar2(100);
v_pkvalue number;
v_FieldSQL varchar2(2000);
v_ValueSQL varchar2(2000);
v_strarry ty_str_split:=new ty_str_split();
CURSOR ctables IS
SELECT *
FROM TABLE(CAST(fn_split(p_string, ';;') AS
ty_str_split));
CURSOR cRelation is SELECT *
FROM TABLE(CAST(fn_split(p_relation ,';;') AS
ty_str_split));
cursor find_pk_field(var_tablename varchar2) is
select col.column_name
from user_cons_columns col,
user_constraints con
where col.constraint_name=con.constraint_name
and con.constraint_type='P'
and upper(col.table_name)=upper(var_tablename);
r ctables%ROWTYPE;
r1 cRelation%ROWTYPE;
BEGIN
savepoint V_SP_0000;
V_ERRNO:=-2000;
v_FieldSQL:='';
v_temp:='---';
v_ValueSQL:='';
select count(1) into v_num from user_tables where table_name = 'T_TEMP';
if v_num < 1 then
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (
tablename varchar2(50),
fieldname varchar2(50),
fieldvalue varchar2(3000),
DataType varchar2(20)
) ON COMMIT delete ROWS';
end if;
OPEN ctables;
FETCH ctables
INTO r;
LOOP
EXIT WHEN ctables%NOTFOUND;
DBMS_OUTPUT.put_line (r.Column_value);
execute immediate 'insert into T_TEMP values(''' ||replace(r.Column_value,',,',''',''') || ''')';
FETCH ctables
INTO r;
END LOOP;
CLOSE ctables;
select count(1) into v_num from user_tables where table_name = 'T_RELATION';
if v_num < 1 then
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_RELATION (
ptablename varchar2(50),
pfieldname varchar2(50),
ctablename varchar2(50),
cfieldname varchar2(50),
fieldvalue varchar2(3000),
DataType varchar2(20)
) ON COMMIT delete ROWS';
end if;
OPEN cRelation;
FETCH cRelation
INTO r1;
LOOP
EXIT WHEN cRelation%NOTFOUND;
if r1.column_value is not null then
execute immediate 'insert into T_RELATION values(''' || replace(r1.Column_value,',,',''',''') || ''')';
end if;
FETCH cRelation
INTO r1;
END LOOP;
CLOSE cRelation;
for emm in(select tablename from (select min(Rowid) xh,tablename from T_TEMP group by tablename) order by xh) loop
open find_pk_field(emm.tablename);
fetch find_pk_field into v_pkfield;
close find_pk_field;
select fieldvalue into v_PKValuetemp from T_TEMP where tablename=emm.tablename and fieldname=v_pkfield;
if v_PKValuetemp is null or v_PKValuetemp = 'null' then
v_num:=0;
else
v_num:=1;
end if ;
if v_num < 1 then
select GetSequenceValue(emm.tablename) into v_pkvalue from dual;
update T_TEMP set fieldvalue=https://bbs.csdn.net/topics/v_pkvalue where tablename=emm.tablename and upper(fieldname)=upper(v_pkfield) ;
end if;
for fee in(select fieldname,fieldvalue,datatype from T_TEMP where tablename=emm.tablename) loop
if fee.fieldname != v_temp then
v_temp:=fee.fieldname;
if v_num < 1 then
case fee.datatype
when 'V' then
v_FieldSQL:=v_FieldSQL||','||fee.fieldname;
v_ValueSQL:=v_ValueSQL||','''||case when fee.fieldvalue='https://bbs.csdn.net/topics/null' then '' else fee.fieldvalue end||'''';
when 'v' then
v_FieldSQL:=v_FieldSQL||','||fee.fieldname;
v_ValueSQL:=v_ValueSQL||','''||case when fee.fieldvalue='https://bbs.csdn.net/topics/null' then '' else fee.fieldvalue end||'''';
when 'N' then
v_FieldSQL:=v_FieldSQL||','||fee.fieldname;
v_ValueSQL:=v_ValueSQL||','||case when fee.fieldvalue is null then 'null' else fee.fieldvalue end;
when 'n' then
v_FieldSQL:=v_FieldSQL||','||fee.fieldname;
v_ValueSQL:=v_ValueSQL||','||case when fee.fieldvalue is null then 'null' else fee.fieldvalue end;
when 'D' then
v_FieldSQL:=v_FieldSQL||','||fee.fieldname;
v_ValueSQL:=v_ValueSQL||',to_date('''||fee.fieldvalue||''',''yyyy-mm-dd hh24:mi:ss'')';
when 'd' then
v_FieldSQL:=v_FieldSQL||','||fee.fieldname;
v_ValueSQL:=v_ValueSQL||',to_date('''||fee.fieldvalue||''',''yyyy-mm-dd hh24:mi:ss'')';
else
v_FieldSQL:=v_FieldSQL||','||fee.fieldname;
v_ValueSQL:=v_ValueSQL||','||fee.fieldvalue;
end case ;
else
if fee.fieldname = v_pkfield then
v_pkvalue:=fee.fieldvalue;
end if;
case fee.datatype
when 'V' then
v_FieldSQL:=v_FieldSQL||fee.fieldname||'='''||case when fee.fieldvalue='https://bbs.csdn.net/topics/null' then '' else fee.fieldvalue end||''',';
when 'v' then
v_FieldSQL:=v_FieldSQL||fee.fieldname||'='''||case when fee.fieldvalue='https://bbs.csdn.net/topics/null' then '' else fee.fieldvalue end||''',';
when 'N' then
v_FieldSQL:=v_FieldSQL||fee.fieldname||'='||case when fee.fieldvalue is null then 'null' else fee.fieldvalue end||',';
when 'n' then
v_FieldSQL:=v_FieldSQL||fee.fieldname||'='||case when fee.fieldvalue is null then 'null' else fee.fieldvalue end||',';
when 'D' then
v_FieldSQL:=v_FieldSQL||fee.fieldname||'=to_date('''||case when fee.fieldvalue='https://bbs.csdn.net/topics/null' then '' else fee.fieldvalue end||''',''yyyy-mm-dd hh24:mi:ss''),';
when 'd' then
v_FieldSQL:=v_FieldSQL||fee.fieldname||'=to_date('''||case when fee.fieldvalue='https://bbs.csdn.net/topics/null' then '' else fee.fieldvalue end||''',''yyyy-mm-dd hh24:mi:ss''),';
else
v_FieldSQL:=v_FieldSQL||fee.fieldname||'='||fee.fieldvalue||',';
end case ;
end if;
end if;
end loop;
if v_num < 1 then
select count(1) into v_haverelation from T_RELATION where ptablename=emm.tablename and upper(pfieldname)=upper(v_pkfield) ;
if v_haverelation>0 then
update T_RELATION set fieldvalue=https://bbs.csdn.net/topics/v_pkvalue where ptablename=emm.tablename and upper(pfieldname)=upper(v_pkfield) ;
end if;
v_temp:='---';
for gff in(select cfieldname,fieldvalue,datatype from T_RELATION where ctablename=emm.tablename) loop
if gff.cfieldname != v_temp then
v_temp:=gff.cfieldname;
case gff.datatype
when 'V' then
v_FieldSQL:=v_FieldSQL||','||gff.cfieldname;
v_ValueSQL:=v_ValueSQL||','''||case when gff.fieldvalue='https://bbs.csdn.net/topics/null' then '' else gff.fieldvalue end||'''';
when 'v' then
v_FieldSQL:=v_FieldSQL||','||gff.cfieldname;
v_ValueSQL:=v_ValueSQL||','''||case when gff.fieldvalue='https://bbs.csdn.net/topics/null' then '' else gff.fieldvalue end||'''';
when 'N' then
v_FieldSQL:=v_FieldSQL||','||gff.cfieldname;
v_ValueSQL:=v_ValueSQL||','||case when gff.fieldvalue is null then 'null' else gff.fieldvalue end;
when 'n' then
v_FieldSQL:=v_FieldSQL||','||gff.cfieldname;
v_ValueSQL:=v_ValueSQL||','||case when gff.fieldvalue is null then 'null' else gff.fieldvalue end;
when 'D' then
v_FieldSQL:=v_FieldSQL||','||gff.cfieldname;
v_ValueSQL:=v_ValueSQL||',to_date('''||case when gff.fieldvalue='https://bbs.csdn.net/topics/null' then '' else gff.fieldvalue end||''',''yyyy-mm-dd hh24:mi:ss'')';
when 'd' then
v_FieldSQL:=v_FieldSQL||','||gff.cfieldname;
v_ValueSQL:=v_ValueSQL||',to_date('''||case when gff.fieldvalue='https://bbs.csdn.net/topics/null' then '' else gff.fieldvalue end||''',''yyyy-mm-dd hh24:mi:ss'')';
else
v_FieldSQL:=v_FieldSQL||','||gff.cfieldname;
v_ValueSQL:=v_ValueSQL||','||gff.fieldvalue;
end case ;
end if;
end loop ;
v_FieldSQL:=' insert into '||emm.tablename||' ('||ltrim(v_FieldSQL,',')||') values ('||ltrim(v_ValueSQL,',')||')';
else
v_FieldSQL:='update '||emm.tablename||' set '||rtrim(v_FieldSQL,',')|| ' where '||v_pkfield||'='||v_pkvalue ;
select count(1) into v_haverelation from T_RELATION where ptablename=emm.tablename and upper(pfieldname)=upper(v_pkfield) ;
if v_haverelation>0 then
update T_RELATION set fieldvalue=https://bbs.csdn.net/topics/v_pkvalue where ptablename=emm.tablename and upper(pfieldname)=upper(v_pkfield) ;
end if;
end if;
--DBMS_OUTPUT.put_line (v_FieldSQL);
v_strarry.EXTEND;
v_strarry (v_strarry.COUNT) := v_FieldSQL;
v_FieldSQL:='';
v_ValueSQL:='';
v_temp:='---';
v_pkvalue:=0;
end loop;
for emm in (select * from table (CAST (v_strarry AS ty_str_split))) loop
execute immediate emm.column_value;
end loop;
COMMIT;
exception
when others then rollback to savepoint V_SP_0000;
V_ERRMSG := 'STS_ERROR: BatchSaved_Sp error hapened' + sqlerrm;
raise_application_error(V_ERRNO, V_ERRMSG);
end;
編譯能成功，沒有報錯，實際使用中報錯如下：
執行【pdatasavedwithrelation_sp('HR_RESERVEWAGES_TB,,RESERVEWAGES_ID,,10651,,N;;
HR_RESERVEWAGES_TB,,USER_ID,,807117,,N;;
HR_RESERVEWAGES_TB,,RESERVEWAGES_AMOUNT,,240,,N;;
HR_RESERVEWAGES_TB,,RESERVEJT_AMOUNT,,0,,N;;
HR_RESERVEWAGES_TB,,U_VALIDATE,,1,,V;;
HR_RESERVEWAGES_TB,,U_MEMO,,null,,V','')】
出錯：ORA-06502: PL/SQL: 數字或值錯誤 : 字符到數值的轉換錯誤
ORA-06512: 在 "PTHR_QH.PDATASAVEDWITHRELATION_SP", line 223
ORA-01403: 未找到任何資料
ORA-06512: 在 line 1。
請大神指點一二
uj5u.com熱心網友回復：
欄位型別轉換錯誤，欄位型別是字符型，但是存放的是數字格式（部分行不是純數字），在進行轉換的時候有些內容無法轉換。uj5u.com熱心網友回復：
提示資訊挺詳細了，可以考慮單步除錯。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/248685.html
標籤：開發
上一篇：老鐵，就差你了
下一篇：SQL查詢陳述句