SQLserver写Oracle,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拉出在mssql建表的脚本。

这是用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;