我们将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;
版权声明：本文博客原创文章，博客，未经同意，不得转载。
转载于:https://www.cnblogs.com/bhlsheji/p/4658760.html
相关资源：数据结构—成绩单生成器