Oracle拉出在sqlserver建表的语句
我们将oracle数据同步到sqlserver时,是先得在sqlserver端建表的。
复杂的字段我们不同步,就只考虑下面四种数据类型。
oracle到sqlserver做的映射: int -> int number -> decimal(18,6) number(p,s) -> decimal(p,s) date -> datetime varchar2(n) -> nvarchar(n)下面是从oracle端执行的plsql脚本。
/*
简介:从oracle拉出在建表的脚本。这是用plsql语言写成的,在oracle中执行的脚本。如下是取出bom属主下的非临时表。
作者:dba_白老大
最后更新日期:20140515
*/
/*
oracle到sqlserver做的映射:
int -> int
number -> decimal(18,6)
number(p,s) -> decimal(p,s)
date -> datetime
varchar2(n) -> nvarchar(n)
*/
declare
v_column_name varchar2(30);
v_data_type varchar2(106);
v_data_length number;
v_data_precision number;
v_data_scale number;
v_cnt int;
begin
for i in (
select 'ggmgr' as owner, 't6' as table_name from dual union
select 'ggmgr' as owner, 't2' as table_name from dual union
select 'ggmgr' as owner, 't6' as table_name from dual
)
loop
dbms_output.put_line('create table '||'erp'||'.'||'dbo.'||i.table_name||'(');
select count(*)
into v_cnt
from dba_tab_columns
where table_name = i.table_name
and owner = i.owner;
for b in 1 .. v_cnt loop
select column_name,
data_type,
data_length,
data_precision,
data_scale
into v_column_name,
v_data_type,
v_data_length,
v_data_precision,
v_data_scale
from dba_tab_columns t
where table_name = i.table_name
and owner = i.owner
and column_id = b;
--int
if v_data_type = 'number' and v_data_precision is null and b != v_cnt then
dbms_output.put_line(v_column_name||' decimal'||',');
end if;
if v_data_type = 'number' and v_data_precision is null and b = v_cnt then
dbms_output.put_line(v_column_name||' decimal');
end if;
--number
if (v_data_type = 'number') and (v_data_precision is not null) and (b != v_cnt) then
dbms_output.put_line(v_column_name||' decimal'||'('||v_data_precision||','||v_data_scale||'),');
end if;
if (v_data_type = 'number') and (v_data_precision is not null) and (b = v_cnt) then
dbms_output.put_line(v_column_name||' decimal'||'('||v_data_precision||','||v_data_scale||')');
end if;
--varchar2
if (v_data_type = 'varchar2') and (v_data_length is not null) and (b != v_cnt) then
dbms_output.put_line(v_column_name||' nvarchar'||'('||v_data_length||'),');
end if;
if (v_data_type = 'varchar2') and (v_data_length is not null) and (b = v_cnt) then
dbms_output.put_line(v_column_name||' nvarchar'||'('||v_data_length||')');
end if;
--date
if (v_data_type = 'date') and (b != v_cnt) then
dbms_output.put_line(v_column_name||' datetime,');
end if;
if (v_data_type = 'date') and (b = v_cnt) then
dbms_output.put_line(v_column_name||' datetime');
end if;
--不属于int,number,date,varchar2
if (v_column_name != 'number') and (v_column_name != 'date') and (v_column_name != 'varchar2') then
null;
end if;
end loop;
dbms_output.put_line(');'||chr(10));
end loop;
end;
上一篇: 移动端熊掌号icon全部消失