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;