欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle拉出在sqlserver建表的语句

程序员文章站 2024-04-02 21:48:46
我们将oracle数据同步到sqlserver时,是先得在sqlserver端建表的。 复杂的字段我们不同步,就只考虑下面四种数据类型。 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;