华为云用户手册

创建包模式 包声明迁移为创建以包名命名的模式。设置pkgSchemaNaming=false后，可以进行迁移。 输入：为包创建模式名 123456789 CREATE OR REPLACE EDITIONABLE PACKAGE "PACK_DEMO"."PACKAGE_GET_NOVA_INFO" AS TYPE novalistcur is REF CURSOR; PROCEDURE getNovaInfo ( i_appEnShortName IN VARCHAR2, o_flag OUT VARCHAR2, o_errormsg OUT VARCHAR2, o_novalist OUT novalistcur ); 输出 1234 /*~~PACKAGE_GET_NOVA_INFO~~*/CREATE SCHEMA PACKAGE_GET_NOVA_INFO; 父主题： PL/SQL包

TRUNCATE TABLE Oracle中的TRUNCATE TABLE语句用于从表中删除所有记录，与DELETE语句功能相同，但不含WHERE子句。执行截断操作后，表将成为空表。DSC仅可迁移含有静态表名称的TRUNCATE TABLE语句，不支持迁移含有动态表名称的TRUNCATE TABLE语句。 该工具不支持迁移含有动态表名称的TRUNCATE TABLE语句。 例如：l_table :='truncate table ' || itable_name 在此示例中，itable_name表示动态表名称，不受DSC支持。不支持的语句将被原样复制到已迁移的脚本中。 输入：TRUNCATE TABLE，使用Execute Immediate 123456 CREATE OR REPLACE PROCEDURE schema1.proc1 ASBEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE QUERY_TABLE';End proc1;/ 输出 12345 CREATE OR REPLACE PROCEDURE schema1.proc1 AS BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE schema1.QUERY_TABLE' ; end ;/ 输入：在过程中使用TRUNCATE TABLE DSC不会为动态PL/SQL语句添加模式名称。 12345678 CREATE OR REPLACE PROCEDURE schemName.sp_dd_table ( itable_name VARCHAR2 ) IS l_table VARCHAR2 ( 255 ) ; BEGIN l_table :='truncate table ' || itable_name ; ---- dbms_utility.exec_ddl_statement(l_table);dbms_output.put_line ( itable_name || ' ' || 'Truncated' ) ;END sp_dd_table ;/ 输出 123456789 CREATE OR REPLACE PROCEDURE schemName.sp_dd_table ( itable_name VARCHAR2 ) IS l_table VARCHAR2 ( 255 ) ; BEGIN l_table :='truncate table ' || itable_name ;/* dbms_utility.exec_ddl_statement(l_table); */dbms_output.put_line ( itable_name || ' ' || 'Truncated' ) ;end ;/

AUTONOMOUS 输入：AUTONOMOUS 123 CREATE OR REPLACE EDITIONABLE PACKAGE BODY "Pack1"."DEMO_PROC" is PROCEDURE log(proc_name IN VARCHAR2, info IN VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; 输出 12 CREATE OR REPLACE PROCEDURE DEMO_PROC.log ( proc_name IN VARCHAR2 ,info IN VARCHAR2 ) IS /*PRAGMA AUTONOMOUS_TRANSACTION;*/

ALTER SESSION Oracle中的ALTER SESSION语句用于设置或修改数据库连接的参数和行为。该语句将持续有效，除非数据库连接断开。DSC可迁移如下形式的ALTER SESSION语句： 含有ADVISE、ENABLE、DISABLE、CLOSE和FORCE的ALTER SESSION语句将被迁移为注释脚本。 含有SET CLAUSE参数（例如：NLS_DATE_FORMAT和NLS_DATE_LANGUAGE等）的ALTER SESSION语句将被逐字复制。 该工具不支持迁移命令子句含有变量的ALTER SESSION语句。 例如：EXECUTE IMMEDIATE ' alter session ' || command_val || 'parallel ' || type_value. 示例中，command_val是变量，不受DSC支持。不支持的语句将被逐字复制到已迁移的脚本中。 输入：ALTER SESSION 12345 ALTER SESSION ENABLE PARALLEL DDL;ALTER SESSION ADVISE COMMIT;ALTER SESSION CLOSE DATABASE LINK local;ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';ALTER SESSION SET current_schema = 'isfc'; 输出 12345 /*ALTER SESSION ENABLE PARALLEL DDL;*//*ALTER SESSION ADVISE COMMIT;*//*ALTER SESSION CLOSE DATABASE LINK local;*/ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';ALTER SESSION SET current_schema = 'isfc'; 输入：ALTER SESSION 1 2 3 4 5 6 7 8 910111213 CREATE OR REPLACE PROCEDURE PUBLIC .TEST_CALL is command_val varchar2 ( 1000 ) ; type_value number ; BEGIN command_val := 'enable parallel ddl' ; dbms_output.put_line ( mike ) ;-- execute immediate 'ALTER SESSION DISABLE GUARD' ; execute immediate 'ALTER SESSION ADVISE ROLLBACK' ;EXECUTE IMMEDIATE ' alter session ' || command_val || 'parallel ' || type_value ;END TEST_CALL;/ 输出 1 2 3 4 5 6 7 8 9101112 CREATE OR REPLACE PROCEDURE PUBLIC.TEST_CALL is command_val varchar2 ( 1000 ) ; type_value number ; BEGIN command_val := 'enable parallel ddl' ;dbms_output.put_line ( mike ) ;/* execute immediate 'ALTER SESSION DISABLE GUARD' ; */ execute immediate '/*ALTER SESSION ADVISE ROLLBACK*/' ;EXECUTE IMMEDIATE 'alter session ' || command_val || 'parallel ' || type_value ; END ; /

LONG 数据类型LONG迁移为TEXT。 输入：LONG 1 2 3 4 5 6 7 8 91011121314 CREATE OR REPLACE FUNCTION fn_proj_det ( i_proj_cd INT ) RETURN LONG IS v_proj_det LONG; BEGIN SELECT proj_det INTO v_proj_det FROM project WHERE proj_cd = i_proj_cd; RETURN v_proj_det; END; / 输出 1 2 3 4 5 6 7 8 91011121314 CREATE OR REPLACE FUNCTION fn_proj_det ( i_proj_cd INT ) RETURN TEXT IS v_proj_det TEXT; BEGIN SELECT proj_det INTO v_proj_det FROM project WHERE proj_cd = i_proj_cd; RETURN v_proj_det; END; /

END 不支持END指定标签。因此，迁移期间将删除标签名称。 输入：END，使用过程名 12345 CREATE OR REPLACE PROCEDURE sp_ins_emp … … ...END sp_ins_emp; 输出 12345 CREATE OR REPLACE PROCEDURE sp_ins_emp … … ...END; 输入：END，使用函数名 123456 CREATE FUNCTION fn_get_bal … … ...END get_bal;/ 输出 123456 CREATE FUNCTION fn_get_bal … … ...END;/

替换变量 替换变量是Oracle SQL * Plus工具的一个特性。 当在一个语句中使用一个替换变量时，SQL * Plus会请求一个输入值并重写该语句以将其包含在内。 重写的语句被传递到Oracle数据库。 当输入的Oracle脚本包含任何替换变量时，DSC将显示以下消息。消息记录在控制台和日志文件中。 **************************************************************************USER ATTENTION!!! Variable: &bbid should be substituted in the file : "/home/testmigration/V100R002C60/MigrationTool/Input/proc_frss_jczbsc.SQL" Variable: &wdbs should be substituted in the file : "/home/testmigration/V100R002C60/MigrationTool/Input/proc_frss_jczbsc.SQL" Variable: &batch_no should be substituted in the file : "/home/testmigration/V100R002C60/MigrationTool/Input/proc_frss_jczbsc.SQL"**************************************************************************

EDITIONABLE GaussDB (DWS)不支持EDITIONABLE关键字，因此需要在目标数据库中删除。 输入：EDITIONABLE 1 2 3 4 5 6 7 8 91011121314 CREATE OR REPLACE EDITIONABLE PACKAGE "PACK1"."PACKAGE_SEND_MESSAGE" AS TYPE filelist IS REF CURSOR; PROCEDURE get_message_info (in_userid IN VARCHAR2, in_branchid IN VARCHAR2, in_appverid IN VARCHAR2, in_app_list_flag IN VARCHAR2, in_filetype IN VARCHAR2, in_filestate IN VARCHAR2, o_retcode OUT VARCHAR2, o_errormsg OUT VARCHAR2, o_seq OUT VARCHAR2, o_totalnum OUT NUMBER, o_filelist OUT filelist); 输出 1234 /*~~PACKAGE_SEND_MESSAGE~~*/CREATE SCHEMA PACKAGE_SEND_MESSAGE;

MERGE MERGE是一种ANSI标准的SQL语法运算符，用于从一个或多个源中选择行来更新或插入表或视图。用户可指定更新或插入目标表或视图的条件。 DSC使用多种方法将MERGE迁移到GaussDB(DWS)兼容的SQL中。 配置参数mergeImplementation： 默认设置为WITH。设为此值时，目标查询将转换成公用表表达式。 图11 输入：MERGE（1） 图12 输出：MERGE（2） 也可设置为SPLIT。设为此值时，MERGE语句将被分解为多个INSERT和UPDATE语句。 图13 输入：MERGE（3） 图14 输出：MERGE（4）

TYPE命名约定 用户定义的类型允许定义数据类型，以模拟应用程序中数据的结构和行为。 输入 CREATE TYPE t_line AS ( product_line VARCHAR2 ( 30 ) ,product_amount NUMBER ) ;; 输出 CREATE TYPE sad_dml_product_pkg.t_line AS ( product_line VARCHAR2 ( 30 ) ,product_amount NUMBER ) ; 输入 CREATE TYPE t_line AS ( product_line VARCHAR2 ( 30 ) ,product_amount NUMBER ) ;

EXTEND GaussDB(DWS)支持EXTEND关键字。 输入： EXTEND FUNCTION FUNC_EXTEND ( in_str IN VARCHAR2) RETURN ARRYTYPE AS v_count2 INTEGER; v_strlist arrytype; v_node VARCHAR2 (2000); BEGIN v_count2 := 0; v_strlist := arrytype (); FOR v_i IN 1 .. LENGTH (in_str) LOOP IF v_node IS NULL THEN v_node := ''; END IF; IF (v_count2 = 0) OR (v_count2 IS NULL) THEN EXIT; ELSE v_strlist.EXTEND (); v_strlist (v_i) := v_node; v_node := ''; END IF; END LOOP; RETURN v_strlist; END; / 输出 FUNCTION FUNC_EXTEND ( in_str IN VARCHAR2 )RETURN ARRYTYPE AS v_count2 INTEGER ;v_strlist arrytype ;v_node VARCHAR2 ( 2000 ) ;BEGIN v_count2 := 0 ; v_strlist := arrytype ( ) ; FOR v_i IN 1.. LENGTH( in_str ) LOOP IF v_node IS NULL THEN v_node := '' ; END IF ; IF ( v_count2 = 0 ) OR( v_count2 IS NULL ) THEN EXIT ; ELSE v_strlist.EXTEND ( 1 ) ; v_strlist ( v_i ) := v_node ; v_node := '' ; END IF ; END LOOP ; RETURN v_strlist ; END ; /

SUBTYPE SUBTYPE语句中，PL/SQL允许您定义自己的子类型或预定义数据类型的别名，有时称为抽象数据类型。 输入 CREATE OR REPLACE PACKAGE "SAD"."BAS_SUBTYPE_PKG" ISSUBTYPE CURRENCY IS BAS_PRICE_LIST_T.CURRENCY%TYPE;END bas_subtype_pkg;/CREATE OR REPLACE PACKAGE BODY "SAD"."BAS_SUBTYPE_PKG" ISBEGIN NULL;END bas_subtype_pkg;/--********************************************************************CREATE OR REPLACE PACKAGE BODY SAD.bas_lookup_misc_pkg IS FUNCTION get_currency(pi_price_type IN NUMBER) RETURN VARCHAR2 IS v_currency bas_subtype_pkg.currency; BEGIN g_func_name := 'get_currency'; FOR rec_currency IN (SELECT currency FROM sad_price_type_v WHERE price_type_code = pi_price_type) LOOP v_currency := rec_currency.currency; END LOOP; RETURN v_currency; END get_currency; END SAD.bas_lookup_misc_pkg; /

PL/SQL包 本节主要介绍Oracle PL/SQL包（详情请参见包）和REF CURSOR（详情请参见REF CURSOR）的迁移语法。迁移语法决定了关键字/功能的迁移方式。 本节包括以下内容： 包、包变量、包拆分、REF CURSOR、VARRAY、创建包模式、授予执行权限、包名列表、数据类型，各节点的具体内容详见包~数据类型章节。 包 包变量 包拆分 REF CURSOR 创建包模式 父主题： Oracle语法迁移

DBMS_LOB.CREATETEMPORARY DBMS_LOB.CREATETEMPORARY函数在用户默认的临时表空间中创建一个临时LOB及其对应索引。DBMS_LOB.FREETEMPORARY用于删除临时LOB及其索引。 输入：DBMS_LOB.CREATETEMPORARY和DBMS_LOB.FREETEMPORARY 12345678 DECLARE v_clob clob;BEGIN DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION); v_clob := TO_CLOB('abcddedf'); DBMS_OUTPUT.PUT_LINE(v_clob); DBMS_LOB.FREETEMPORARY(v_clob);end;/ 输出 123456789 DECLARE v_clob clob;BEGIN -- DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION); v_clob := CAST('abcddedf' AS CLOB); DBMS_OUTPUT.PUT_LINE(CAST(v_clob AS TEXT)); -- DBMS_LOB.FREETEMPORARY(v_clob); NULL;end;/

DBMS_LOB.SUBSTR DBMS_LOB.SUBSTR通过配置参数MigDbmsLob，用户可以指定迁移此函数还是直接保留。 输入：DBMS_LOB.SUBSTR，MigDbmsLob设为true 如果参数MigDbmsLob设为true，则迁移。相反，如果参数MigDbmsLob设为false，则不迁移。 输入 SELECT dbms_lob.substr('!2d3d4dd!',1,5); 输出 If the config param is true, it should be migrated as below:select substr('!2d3d4dd!',5,1);If false, it should be retained as it is:select dbms_lob.substr('!2d3d4dd!',1,5); 输入 SELECT dbms_lob.substr('!2d3d4dd!',5); 输出 If the config param is true, it should be migrated as below:select substr('!2d3d4dd!',1,5);If false, it should be retained as it is:select dbms_lob.substr('!2d3d4dd!',5);

DBMS_LOB.FREETEMPORARY DBMS_LOB.FREETEMPORARY函数释放默认临时表空间中的临时BLOB或CLOB。在调用FREETEMPORARY之后，释放的LOB定位器标记为无效。 输入：DBMS_LOB.CREATETEMPORARY和DBMS_LOB.FREETEMPORARY 12345678 DECLARE v_clob clob;BEGIN DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION); v_clob := TO_CLOB('abcddedf'); DBMS_OUTPUT.PUT_LINE(v_clob); DBMS_LOB.FREETEMPORARY(v_clob);end;/ 输出 123456789 DECLARE v_clob clob ;BEGIN /*DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);*/ v_clob := cast( 'abcddedf' as CLOB ) ; DBMS_OUTPUT.PUT_LINE ( v_clob ) ; /* DBMS_LOB.FREETEMPORARY(v_clob); */ null ; end ; /

DBMS_LOB.INSTR DBMS_LOB.INSTR函数从指定的偏移量开始，返回在LOB中第n次匹配模式的位置。 输入：在SQL中使用DBMS_LOB.INSTR 123 SELECT expr1, …, DBMS_LOB.INSTR(str, septr, 1, 5) FROM tab1 WHERE …; 输出 123 SELECT expr1, …, INSTR(str, septr, 1, 5) FROM tab1 WHERE … 输入：在PL/SQL中使用DBMS_LOB.INSTR 123456 BEGIN … pos := DBMS_LOB.INSTR(str,septr,1, i); ...END;/ 输出 123456 BEGIN … pos := INSTR(str,septr,1, i); ...END;/

数据类型 子类型 包中的自定义类型无法被转换。 SUBTYPE error_msg IS sad_products_t.exception_description%TYPE; SUBTYPE AR_FLAG IS SAD_RA_LINES_TI.AR_FLAG%TYPE; SUBTYPE LOCK_FLAG IS SAD_SHIPMENT_BATCHES_T.LOCK_FLAG%TYPE; bas_subtype_pkg.error_msg 输入： 123456789 CREATE OR REPLACE PACKAGE SAD.bas_subtype_pkg IS SUBTYPE func_name IS sad_products_t.func_name%TYPE;END bas_subtype_pkg;/CREATE OR REPLACE PACKAGE BODY SAD.bas_subtype_pkg ISBEGIN NULL;END bas_subtype_pkg;/ 输出： 1 2 3 4 5 6 7 8 910111213141516 CREATE OR REPLACE PACKAGE BODY SAD.bas_dml_lookup_pkg IS g_pkg_name CONSTANT VARCHAR2(30) := 'bas_dml_ic_price_rule_pkg' ; g_func_name VARCHAR2(100); FUNCTION func_name RETURN VARCHAR2 IS l_func_name bas_subtype_pkg.func_name;; BEGIN l_func_name := g_pkg_name || '.' || g_func_name ; RETURN l_func_name ; END func_name;END bas_dml_lookup_pkg;/ %ROWTYPE 包的过程/函数包含 IN/OUT参数中的%ROWTYPE属性，此功能不被支持。 脚本：BAS_DML_SERVIECE_PKG.SQL, BAS_LOOKUP_MISC_PKG.SQL 输入： 12345678 CREATE OR REPLACE PACKAGE BODY "SAD"."BAS_DML_SERVIECE_PKG" ISPROCEDURE save_split_ou(pi_split_ou IN split_ou%ROWTYPE,po_error_msg OUT VARCHAR2) IS---BEGIN---end save_split_ou;end BAS_DML_SERVIECE_PKG; 输出： 1 2 3 4 5 6 7 8 910111213 CREATEOR REPLACE PROCEDURE SAD.BAS_DML_SERVIECE_PKG#save_split_ou ( pi_split_ou IN split_ou%ROWTYPE,po_error_msg OUT VARCHAR2 ) IS MIG_PV_VAL_DUMMY_G_FUNC_NAME VARCHAR2 ( 30 ) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( current_schema ( ),'BAS_DML_SERVIECE_PKG','g_func_name' ) ::VARCHAR2 ( 30 ) ;ex_data_errorEXCEPTION ;ex_prog_errorEXCEPTION ;---BEGIN---END; 输入 1 2 3 4 5 6 7 8 910111213141516 CREATE OR REPLACE PACKAGE BODY SAD.BAS_DML_SERVIECE_PKG IS PROCEDURE save_split_ou(pi_split_ou IN split_ou%ROWTYPE, po_error_msg OUT VARCHAR2) IS BEGIN UPDATE split_ou so SET so.auto_balance_flag = pi_split_ou.auto_balance_flag, so.balance_start_date = pi_split_ou.balance_start_date, so.balance_source = pi_split_ou.balance_source WHERE so.dept_code = pi_split_ou.dept_code; EXCEPTION WHEN OTHERS THEN po_error_msg := 'Others Exception raise in ' || g_func_name || ',' || SQLERRM; END save_split_ou;END bas_dml_serviece_pkg;/ 输出 1 2 3 4 5 6 7 8 910111213141516171819 CREATE TYPE mig_typ_split_ou AS ...;CREATE OR REPLACE PROCEDURE SAD.BAS_DML_SERVIECE_PKG#save_split_ou ( pi_split_ou IN mig_typ_split_ou ,po_error_msg OUT VARCHAR2 ) PACKAGE ISBEGIN UPDATE split_ou so SET so.auto_balance_flag = pi_split_ou.auto_balance_flag ,so.balance_start_date = pi_split_ou.balance_start_date ,so.balance_source = pi_split_ou.balance_source WHERE so.dept_code = pi_split_ou.dept_code ;EXCEPTION WHEN OTHERS THEN po_error_msg := 'Others Exception raise in ' || g_func_name || ',' || SQLERRM ;END ;/ 父主题： Oracle语法迁移

CONNECT BY 输入：CONNECT BY 123456789 SELECT id FROM city_branch start with id=roleBranchId connect by prior id=parent_id;SELECT T.BRANCH_LEVEL, t.ID FROM city_branch c WHERE (c.branch_level = '1' OR T.BRANCH_LEVEL = '2') AND (T.SIGN = '1' OR T.SIGN = '4' OR T.SIGN = '8') AND T.STATUS = '1' START WITH c.ID = I_BRANCH_ID CONNECT BY c.ID = PRIOR c.parent_id ORDER BY c.branch_level DESC ; 输出 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061 WITH RECURSIVE migora_cte AS ( SELECT id ,1 AS LEVEL FROM city_branch WHERE id = roleBranchId UNION ALL SELECT mig_ora_cte_join_alias.id ,mig_ora_cte_tab_alias.LEVEL + 1 AS LEVEL FROM migora_cte mig_ora_cte_tab_alias INNER JOIN city_branch mig_ora_cte_join_alias ON mig_ora_cte_tab_alias.id = mig_ora_cte_join_alias.parent_id) SELECT id FROM migora_cte ORDER BY LEVEL; WITH RECURSIVE migora_cte AS ( SELECT BRANCH_LEVEL ,ID ,SIGN ,STATUS ,parent_id ,1 AS LEVEL FROM city_branch c WHERE c.ID = I_BRANCH_ID UNION ALL SELECT c.BRANCH_LEVEL ,c.ID ,c.SIGN ,c.STATUS ,c.parent_id ,mig_ora_cte_tab_alias.LEVEL + 1 AS LEVEL FROM migora_cte mig_ora_cte_tab_alias INNER JOIN city_branch c ON c.ID = mig_ora_cte_tab_alias.parent_id) SELECT BRANCH_LEVEL ,ID FROM migora_cte c WHERE ( c.branch_level = '1' OR T.BRANCH_LEVEL = '2' ) AND( T.SIGN = '1' OR T.SIGN = '4' OR T.SIGN = '8' ) AND T.STATUS = '1' ORDER BY c.branch_level DESC; 输入：多表CONNECT BY 说明了每个子行与父行的关系。该语法使用CONNECT BY xxx PRIOR子句定义当前行（子行）与前一行（父行）的关系。 1 2 3 4 5 6 7 8 9101112 SELECT DISTINCT a.id menuId, F.name menuName, a.status menuState, a.parent_id menuParentId, '-1' menuPrivilege, a.serialNo menuSerialNo FROM CTP_MENU a, CTP_MENU_NLS F START WITH a.serialno in (1, 2, 3)CONNECT BY a.id = PRIOR a.parent_id AND f.locale = Language AND a.id = f.idORDER BY menuId, menuParentId; 输出 1 2 3 4 5 6 7 8 91011121314151617181920212223242526272829 WITH RECURSIVE migora_cte AS ( SELECT pr.service_product_id , t.enabled_flag , pr.operation_id , pr.enabled_flag , pr.product_code , 1 AS LEVEL FROM asms.cppsv_operation_sort t , asms.cppsv_product_class pr WHERE level_id = 3 AND pr.operation_id = t.operation_id(+) UNION ALL SELECT pr.service_product_id , t.enabled_flag , pr.operation_id , pr.enabled_flag , pr.product_code , mig_ora_cte_tab_alias.LEVEL + 1 AS LEVEL FROM migora_cte mig_ora_cte_tab_alias , asms.cppsv_operation_sort t , asms.cppsv_product_class pr WHERE mig_ora_cte_tab_alias.service_product_id = pr.service_product_father_id AND pr.operation_id = t.operation_id(+) ) SELECT pr.service_product_id FROM migora_cte WHERE nvl( UPPER( enabled_flag ) ,'Y' ) = 'Y' AND nvl( enabled_flag ,'Y' ) = 'Y' AND pr.product_code = rec_product1.service_product_code ORDER BY LEVEL; 父主题： Oracle语法迁移

PROMPT命令 PROMPT命令应转换成GaussDB(DWS)支持的\ECHO命令。 Oracle语法 迁移后语法 promptprompt Creating table productprompt ===============================promptcreate table product( product_id VARCHAR2(20), product_name VARCHAR2(50)); \echo\echo Creating table product\echo ===============================\echoCREATE TABLE product( product_id VARCHAR2(20), product_name VARCHAR2(50));

范围、账号和语言 当GaussDB(DWS)关键字用作SELECT列表中任意列的别名且没有使用AS时，需要使用“AS关键字”的格式来定义别名。 输入 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031323334353637383940414243 CREATE OR REPLACE /*FORCE*/ VIEW SAD.FND_TERRITORIES_TL_V ( TERRITORY_CODE ,TERRITORY_SHORT_NAME ,LANGUAGE ,Account ,Range ,LAST_UPDATED_BY ,LAST_UPDATE_DATE ,LAST_UPDATE_ LOG IN ,DESCRIPTION ,SOURCE_LANG ,ISO_NUMERIC_CODE ) AS SELECT t.TERRITORY_CODE ,t.TERRITORY_SHORT_NAME ,t.LANGUAGE ,t.Account ,t.Range ,t.LAST_UPDATED_BY ,t.LAST_UPDATE_DATE ,t.LAST_UPDATE_LOGIN ,t.DESCRIPTION ,t.SOURCE_LANG ,t.ISO_NUMERIC_CODE FROM fnd_territories_tl t UNION ALL SELECT 'SS' TERRITORY_CODE ,'Normal Country' TERRITORY_SHORT_NAME ,NULL LANGUAGE ,NULL Account ,NULL Range ,NULL LAST_UPDATED_BY ,NULL LAST_UPDATE_DATE ,NULL LAST_UPDATE_LOGIN ,NULL DESCRIPTION ,NULL SOURCE_LANG ,NULL ISO_NUMERIC_CODE FROM DUAL ; 输出 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031323334353637383940414243 CREATE OR REPLACE /*FORCE*/ VIEW SAD.FND_TERRITORIES_TL_V ( TERRITORY_CODE ,TERRITORY_SHORT_NAME ,LANGUAGE ,CREATED_BY ,CREATION_DATE ,LAST_UPDATED_BY ,LAST_UPDATE_DATE ,LAST_UPDATE_LOGIN ,DESCRIPTION ,SOURCE_LANG ,ISO_NUMERIC_CODE ) AS SELECT t.TERRITORY_CODE ,t.TERRITORY_SHORT_NAME ,t.LANGUAGE ,t.CREATED_BY ,t.CREATION_DATE ,t.LAST_UPDATED_BY ,t.LAST_UPDATE_DATE ,t.LAST_UPDATE_LOGIN ,t.DESCRIPTION ,t.SOURCE_LANG ,t.ISO_NUMERIC_CODE FROM fnd_territories_tl t UNION ALL SELECT 'SS' TERRITORY_CODE ,'Normal Country' TERRITORY_SHORT_NAME ,NULL AS LANGUAGE ,NULL CREATED_BY ,NULL CREATION_DATE ,NULL LAST_UPDATED_BY ,NULL LAST_UPDATE_DATE ,NULL LAST_UPDATE_LOGIN ,NULL DESCRIPTION ,NULL SOURCE_LANG ,NULL ISO_NUMERIC_CODE FROM DUAL ;

主键和唯一键 如果在建表时声明了主键和唯一键两个约束，仅迁移主键。 1 2 3 4 5 6 7 8 910111213141516171819202122 create table SD_WO.WO_DU_TRIGGER_REVENUE_T( TRIGGER_REVENUE_ID NUMBER not null, PROJECT_NUMBER VARCHAR2(40), DU_ID NUMBER, STANDARD_MS_CODE VARCHAR2(100), TRIGGER_STATUS NUMBER, TRIGGER_MSG VARCHAR2(4000), BATCH_NUMBER NUMBER, PRO CES S_STATUS NUMBER, ENABLE_FLAG CHAR(1) default 'Y', CREATED_BY NUMBER, CREATION_DATE DATE, LAST_UPDATE_BY NUMBER, LAST_UPDATE_DATE DATE); alter table SD_WO.WO_DU_TRIGGER_REVENUE_T add constraint WO_DU_TRIGGER_REVENUE_PK primary key (TRIGGER_REVENUE_ID);alter table SD_WO.WO_DU_TRIGGER_REVENUE_T add constraint WO_DU_TRIGGER_REVENUE_N1 unique (DU_ID, STANDARD_MS_CODE); 输出 CREATE TABLE SD_WO.WO_DU_TRIGGER_REVENUE_T ( TRIGGER_REVENUE_ID NUMBER NOT NULL ,PROJECT_NUMBER VARCHAR2 (40) ,DU_ID NUMBER ,STANDARD_MS_CODE VARCHAR2 (100) ,TRIGGER_STATUS NUMBER ,TRIGGER_MSG VARCHAR2 (4000) ,BATCH_NUMBER NUMBER ,PROCESS_STATUS NUMBER ,ENABLE_FLAG CHAR( 1 ) DEFAULT 'Y' ,CREATED_BY NUMBER ,CREATION_DATE DATE ,LAST_UPDATE_BY NUMBER ,LAST_UPDATE_DATE DATE ,CONSTRAINT WO_DU_TRIGGER_REVENUE_PK PRIMARY KEY (TRIGGER_REVENUE_ID) ) ;

TEXT和YEAR 输入：TEXT, YEAR 1 2 3 4 5 6 7 8 9101112131415161718 SELECT NAME, VALUE, DESCRIPTION TEXT, JOINED YEAR, LIMIT FROM EMPLOYEE; SELECT NAME, TEXT, YEAR, VALUE, DESCRIPTION, LIMIT FROM EMPLOYEE_DETAILS; 输出 1 2 3 4 5 6 7 8 9101112131415161718 SELECT "NAME", VALUE, DESCRIPTION AS TEXT, JOINED AS YEAR, "LIMIT" FROM EMPLOYEE; SELECT "NAME", "TEXT", "YEAR", VALUE, DESCRIPTION, "LIMIT" FROM EMPLOYEE_DETAILS;

列 xmax、xmin、left、right、maxvalue为GaussDB(DWS)关键字，这些关键字应全字母大写并加英文双引号（""）。 Oracle语法 迁移后语法 12345678 create table product( xmax VARCHAR2(20), xmin VARCHAR2(50), left VARCHAR2(50), right VARCHAR2(50), maxvalue VARCHAR2(50)); 12345678 CREATE TABLE product1( "XMAX" VARCHAR2(20), "XMIN" VARCHAR2(50), "LEFT" VARCHAR2(50), "RIGHT" VARCHAR2(50), "MAXVALUE" VARCHAR2(50));

间隔分区 对于间隔分区，应该注释分区。 Oracle语法 迁移后语法 123456789 CREATE TABLE product( product_id VARCHAR2(20), product_name VARCHAR2(50), manufacture_month DATE)partition by range (manufacture_month) interval (NUMTODSINTERVAL (1, 'MONTH'))( partition T_PARTITION_2018_11_LESS values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))); 123456789 CREATE TABLE product( product_id VARCHAR2(20), product_name VARCHAR2(50), manufacture_month DATE)/*partition by range (manufacture_month) interval (NUMTODSINTERVAL (1, 'MONTH'))( partition T_PARTITION_2018_11_LESS values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')))*/;

分区（二） 在非分区表中，删除表“ALTER TABLE TRUNCATE PARTITION”中的数据。 Oracle语法 迁移后语法 1 2 3 4 5 6 7 8 91011121314151617181920212223242526272829303132 CREATE TABLE product_list( product_id VARCHAR2(20), Product_Name VARCHAR2(50), Year_Manufacture vARCHAR2(10))partition by list (Year_Manufacture)( partition PART_2015 VALUES (2011,2012,2013,2014,2015) pctfree 10 initrans 1 , partition PART_2016 VALUES (2016) pctfree 10 initrans 1 , partition PART_2017 VALUES (2017) pctfree 10 initrans 1 , partition PART_2018 VALUES (2018) pctfree 10 initrans 1 , partition PART_2019 VALUES (2019) pctfree 10 initrans 1 , partition PART_2020 VALUES (2020) pctfree 10 initrans 1 , PARTITION PART_unknown VALUES (DEFAULT) );CREATE OR REPLACE PROCEDURE List_testISV_ID VARCHAR2(10);BEGIN EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_2020; NULL;END;/CREATE OR REPLACE PROCEDURE List_testISV_ID VARCHAR2(10);BEGIN EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID; NULL;END;/ 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627282930313233343536373839404142434445464748 CREATE TABLE product_list( product_id VARCHAR2(20), Product_Name VARCHAR2(50), Year_Manufacture vARCHAR2(10))/*partition by list (Year_Manufacture)( partition PART_2015 VALUES (2011,2012,2013,2014,2015) pctfree 10 initrans 1 , partition PART_2016 VALUES (2016) pctfree 10 initrans 1 , partition PART_2017 VALUES (2017) pctfree 10 initrans 1 , partition PART_2018 VALUES (2018) pctfree 10 initrans 1 , partition PART_2019 VALUES (2019) pctfree 10 initrans 1 , partition PART_2020 VALUES (2020) pctfree 10 initrans 1 , PARTITION PART_unknown VALUES (DEFAULT) )*/;CREATE OR REPLACE PROCEDURE List_testISV_ID VARCHAR2(10);BEGIN EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID; NULL;END;/CREATE OR REPLACE PROCEDURE List_testISV_ID VARCHAR2(10);BEGIN /* EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID; */ IF 'PART_' || V_ID = 'PART_2015' THEN DELETE FROM product_list WHERE Year_Manufacture IN (2011,2012,2013,2014,2015); ELSIF 'PART_' || V_ID = 'PART_2016' THEN DELETE FROM product_list WHERE Year_Manufacture IN (2016); ELSIF 'PART_' || V_ID = 'PART_2017' THEN DELETE FROM product_list WHERE Year_Manufacture IN (2017); ELSIF 'PART_' || V_ID = 'PART_2018' THEN DELETE FROM product_list WHERE Year_Manufacture IN (2018); ELSIF 'PART_' || V_ID = 'PART_2019' THEN DELETE FROM product_list WHERE Year_Manufacture IN (2019); ELSIF 'PART_' || V_ID = 'PART_2020' THEN DELETE FROM product_list WHERE Year_Manufacture IN (2020); ELSE DELETE FROM product_list WHERE Year_Manufacture NOT IN (2011,2012,2013,2014,2015,2016,2017,2018,2019,2020); END IF; NULL;END;/

SEGMENT CREATION GaussDB(DWS)不支持SEGMENT CREATION { IMMEDIATE | DEFERRED }，因此该语句在迁移后被注释掉，需要设置commentStorageParameter=true。 输入：TABLE，使用SEGMENT CREATION 1 2 3 4 5 6 7 8 910111213 CREATE TABLE T1 ( MESSAGE_CODE VARCHAR2(50), MAIL_TITLE VARCHAR2(1000), MAIL_BODY VARCHAR2(1000), MAIL_ADDRESS VARCHAR2(1000), MAIL_ADDRESS_CC VARCHAR2(1000) ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE Test ;

TYPE 将“MDSYS.MBRCOOR DLI ST”替换为“CLOB”。 Oracle语法 迁移后语法 12345 create table product_part( partid VARCHAR2(24), mbrcoords MDSYS.MBRCOORDLIST); 12345 CREATE TABLE product_part( partid VARCHAR2(24), mbrcoords CLOB);

数据类型 删除数据类型中的BYTE关键字。 Oracle语法 迁移后语法 123456 CREATE TABLE TBL_ORACLE ( ID Number, Name VARCHAR2(100 BYTE), ADDRESS VARCHAR2(200 BYTE) ); 123456 CREATE TABLE TBL_ORACLE ( ID NUMBER ,Name VARCHAR2 (100) ,ADDRESS VARCHAR2 (200) ) ;

分区（注释分区） oracle配置参数中“#分区表唯一或主键约束”为“comment_partition”。 Oracle语法 迁移后语法 1 2 3 4 5 6 7 8 910111213141516171819202122232425 CREATE TABLE TBL_ORACLE( ID Number, Name VARCHAR2(100 BYTE), ADDRESS VARCHAR2(200 BYTE) )TABLESPACE space1PCTUSED 40PCTFREE 0INITRANS 1MAXTRANS 255NOLOGGINGPARTITION BY RANGE (ID)( PARTITION PART_2010 VALUES LESS THAN (10) NOLOGGING, PARTITION PART_2011 VALUES LESS THAN (20) NOLOGGING , PARTITION PART_2012 VALUES LESS THAN (MAXVALUE) NOLOGGING)ENABLE ROW MOVEMENT;ALTER TABLE TBL_ORACLE ADD CONSTRAINT SAMPLE_PK PRIMARY KEY (ID); 1 2 3 4 5 6 7 8 910111213141516 CREATE UNLOGGED TABLE TBL_ORACLE ( ID NUMBER ,Name VARCHAR2 (100) ,ADDRESS VARCHAR2 (200) ,CONSTRAINT SAMPLE_PK PRIMARY KEY (ID)) TABLESPACE space1 /*PCTUSED 40*/ PCTFREE 0 INITRANS 1 MAXTRANS 255 /* PARTITION BY RANGE(ID)(PARTITION PART_2010 VALUES LESS THAN(10) , PARTITION PART_2011 VALUES LESS THAN(20) , PARTITION PART_2012 VALUES LESS THAN(MAXVALUE) ) ENABLE ROW MOVEMENT */ ;