我們將Oracle數(shù)據(jù)同步到sqlserver時(shí)，是先得在sqlserver端建表的。

復(fù)雜的字段我們不同步，就只考慮下面四種數(shù)據(jù)類(lèi)型。

Oracle到SQLServer做的映射：

int -> int

number -> decimal(18,6)

number(p,s) -> decimal(p,s)

date -> datetime

varchar2(n) -> nvarchar(n)

下面是從Oracle端執(zhí)行的plsql腳本。

/*

簡(jiǎn)介：從oracle拉出在mssql建表的腳本。這是用PLSQL語(yǔ)言寫(xiě)成的，在Oracle中執(zhí)行的腳本。如下是取出BOM屬主下的非臨時(shí)表。

作者：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;

總結(jié)

以上是生活随笔為你收集整理的c sqlserver建表csdn_Oracle拉出在sqlserver建表的语句的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。