Oracle拉出在sqlserver建表的语句

浏览数：31 / 时间：2015年06月12日

我们将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;