大家好，又见面了，我是你们的朋友全栈君。

oracle迁移到sqlserver时，报错-如下图，

查找原因，发现是因为有些表number类型没有设精度导致的，解决方法如下，修改表结构加上精度

，加上之后就可以了。考虑到有些表有多个字段没有设精度，所以采取以下方式实现。

–split函数

create or replace type type_split as table of varchar2(4000);

/

create or replace function test_split(p_list VARCHAR2, p_sep VARCHAR2) return type_split

PIPELINED IS

l_idx PLS_INTEGER;

v_list VARCHAR2(32767) := p_list;

begin

LOOP

l_idx := instr(v_list, p_sep);

IF l_idx > 0 THEN

PIPE ROW(substr(v_list, 1, l_idx – 1));

v_list := substr(v_list, l_idx + length(p_sep));

ELSE

PIPE ROW(v_list);

EXIT;

END IF;

END LOOP;

RETURN;

end test_split;

/

–修改表加精度的存储过程

create or replace procedure changefield

(tablename in varchar2,

fieldname in varchar2

) as

CURSOR c1

IS

SELECT * FROM table(test_split(fieldname,’,’));

begin

execute immediate ‘create table temp_table as select * from ‘|| tablename;

execute immediate ‘DELETE FROM ‘|| tablename;

FOR x IN c1

LOOP

execute immediate ‘alter table ‘ || tablename || ‘ modify ‘ || x.column_value || ‘ NUMBER(38, 0)’;

END LOOP;

execute immediate ‘INSERT INTO ‘|| tablename||’ SELECT * FROM temp_table’;

execute immediate ‘DROP TABLE temp_table’;

end;

/

–执行例子需要手工改 参数为表名和字段名 字段名可以一个可以多个，多个字段以逗号分隔

exec changefield(‘A25′,’A2517,SORT’);

exec changefield(‘A36′,’A3617’);

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/163898.html原文链接：https://javaforall.cn