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

*/

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‘);

         --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||‘),‘);

         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||‘)‘);

         --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||‘),‘);

         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||‘)‘);

         --date

         IF (v_data_type = ‘DATE‘) and (b != v_cnt) THEN

           dbms_output.put_line(v_column_name||‘  DATETIME,‘);

         IF (v_data_type = ‘DATE‘) and (b = v_cnt) THEN

           dbms_output.put_line(v_column_name||‘  DATETIME‘);

         --不屬于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;