PL/SQL
本节主要介绍Oracle PL/SQL的迁移语法。迁移语法决定了关键字/功能的迁移方式。
PL/SQL是SQL和编程语言过程特性的集合。
SQL命令
GaussDB(DWS)暂不支持set define off/on、spool off,经过DSC工具转换后,在目标数据库中将相关命令注释掉。
Oracle语法 |
迁移后语法 |
---|---|
set define off spool ORACLE.log create table product ( product_id VARCHAR2(20), product_name VARCHAR2(50) ); spool off |
/*set define off;*/ /*spool ORACLE.log*/ CREATE TABLE product ( product_id VARCHAR2(20), product_name VARCHAR2(50) ); /*spool off*/ |
具体内容详见以下节点:
EDITIONABLE
GaussDB(DWS)不支持EDITIONABLE关键字,因此需要在目标数据库中删除。
输入:EDITIONABLE
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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); |
输出
1 2 3 4 |
/*~~PACKAGE_SEND_MESSAGE~~*/ CREATE SCHEMA PACKAGE_SEND_MESSAGE ; |
END
不支持END指定标签。因此,迁移期间将删除标签名称。
输入:END,使用过程名
1 2 3 4 5 |
CREATE OR REPLACE PROCEDURE sp_ins_emp … … ... END sp_ins_emp; |
输出
1 2 3 4 5 |
CREATE OR REPLACE PROCEDURE sp_ins_emp … … ... END; |
输入:END,使用函数名
1 2 3 4 5 6 |
CREATE FUNCTION fn_get_bal … … ... END get_bal; / |
输出
1 2 3 4 5 6 |
CREATE FUNCTION fn_get_bal … … ... END; / |
EXCEPTION处理
GaussDB(DWS)不支持EXCEPTION处理。要将脚本迁移,必须将exceptionHandler参数设置为True。
对于DSC此参数必须设置为默认值False。
![](https://support.huaweicloud.com/tg-dws/figure/zh-cn_image_0000001706224673.png)
![](https://support.huaweicloud.com/tg-dws/figure/zh-cn_image_0000001658025306.png)
LONG
数据类型LONG迁移为TEXT。
输入:LONG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 9 10 11 12 13 14 |
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; / |
RESULT_CACHE
当调用具有结果缓存的函数时,Oracle执行该函数,将结果添加到结果缓存中,然后返回该函数。
当重复该函数调用时,Oracle将从缓存中获取结果,而不必重新执行该函数。
某些场景下,这种缓存行为可带来显著的性能提升。
目标数据库不支持该关键字。该关键字会从目标文件中移除。
![](https://support.huaweicloud.com/tg-dws/figure/zh-cn_image_0000001657865998.png)
![](https://support.huaweicloud.com/tg-dws/figure/zh-cn_image_0000001706105437.png)
替换变量
替换变量是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" **************************************************************************
输入:PARALLEL_ENABLE
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE OR REPLACE FUNCTION F_REPLACE_COMMA (IS_STR IN VARCHAR2) RETURN VARCHAR2 parallel_enable IS BEGIN IF IS_STR IS NULL THEN RETURN NULL; ELSE RETURN REPLACE(REPLACE(IS_STR, CHR(13) || CHR(10), ''), ',', ', '); END IF; END F_REPLACE_COMMA; / |
输出
1 2 3 4 5 6 7 8 9 10 11 |
CREATE OR REPLACE FUNCTION F_REPLACE_COMMA (IS_STR IN VARCHAR2) RETURN VARCHAR2 IS BEGIN IF IS_STR IS NULL THEN RETURN NULL; ELSE RETURN REPLACE(REPLACE(IS_STR, CHR(13) || CHR(10), ''), ',', ', '); END IF; END; / |
PARALLEL子句
PARALLEL必须加注释。
输入
1 2 3 4 5 6 |
CREATE TABLE PRODUCT ( prod_id INTEGER NOT NULL PRIMARY KEY , prod_code VARCHAR(5) , prod_name VARCHAR(100) , unit_price NUMERIC(6,2) NOT NULL ) PARALLEL 8; |
输出
1 2 3 4 5 6 |
CREATE TABLE PRODUCT ( prod_id INTEGER NOT NULL PRIMARY KEY , prod_code VARCHAR(5) , prod_name VARCHAR(100) , unit_price NUMERIC(6,2) NOT NULL ) /* PARALLEL 8 */; |
TRUNCATE TABLE
Oracle中的TRUNCATE TABLE语句用于从表中删除所有记录,与DELETE语句功能相同,但不含WHERE子句。执行截断操作后,表将成为空表。DSC仅可迁移含有静态表名称的TRUNCATE TABLE语句,不支持迁移含有动态表名称的TRUNCATE TABLE语句。
![](https://support.huaweicloud.com/tg-dws/public_sys-resources/note_3.0-zh-cn.png)
该工具不支持迁移含有动态表名称的TRUNCATE TABLE语句。
例如:l_table :='truncate table ' || itable_name
在此示例中,itable_name表示动态表名称,不受DSC支持。不支持的语句将被原样复制到已迁移的脚本中。
输入:TRUNCATE TABLE,使用Execute Immediate
1 2 3 4 5 6 |
CREATE OR REPLACE PROCEDURE schema1.proc1 AS BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE QUERY_TABLE'; End proc1; / |
输出
1 2 3 4 5 |
CREATE OR REPLACE PROCEDURE schema1.proc1 AS BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE schema1.QUERY_TABLE' ; end ; / |
输入:在过程中使用TRUNCATE TABLE
![](https://support.huaweicloud.com/tg-dws/public_sys-resources/note_3.0-zh-cn.png)
DSC不会为动态PL/SQL语句添加模式名称。
1 2 3 4 5 6 7 8 |
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 ; / |
输出
1 2 3 4 5 6 7 8 9 |
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 ; / |
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语句将被逐字复制。
![](https://support.huaweicloud.com/tg-dws/public_sys-resources/note_3.0-zh-cn.png)
该工具不支持迁移命令子句含有变量的ALTER SESSION语句。
例如:EXECUTE IMMEDIATE ' alter session ' || command_val || 'parallel ' || type_value.
示例中,command_val是变量,不受DSC支持。不支持的语句将被逐字复制到已迁移的脚本中。
输入:ALTER SESSION
1 2 3 4 5 |
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'; |
输出
1 2 3 4 5 |
/*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 9 10 11 12 13 |
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 9 10 11 12 |
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 ; / |
AUTONOMOUS
输入:AUTONOMOUS
1 2 3 |
CREATE OR REPLACE EDITIONABLE PACKAGE BODY "Pack1"."DEMO_PROC" is PROCEDURE log(proc_name IN VARCHAR2, info IN VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; |
输出
1 2 |
CREATE OR REPLACE PROCEDURE DEMO_PROC.log ( proc_name IN VARCHAR2 ,info IN VARCHAR2 ) IS /*PRAGMA AUTONOMOUS_TRANSACTION;*/ |
例如:pkg_etl.clear_temp_tables()
输入
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
CREATE OR REPLACE PACKAGE BODY IC_STAGE.pkg_etl AS PROCEDURE clear_temp_tables IS BEGIN NULL; END clear_temp_tables; END pkg_etl; / CREATE OR REPLACE PACKAGE BODY IC_STAGE.PKG_REVN_ARPU AS PROCEDURE AGGR_X_AGG00_REVN_DEALER (p_date PLS_INTEGER, p_days PLS_INTEGER) AS v_start_date PLS_INTEGER; v_curr_date PLS_INTEGER; BEGIN v_start_date := TO_CHAR (TO_DATE (p_date, 'yyyymmdd') - (p_days - 1), 'yyyymmdd'); v_curr_date := p_date; WHILE (v_curr_date >= v_start_date) LOOP pkg_etl.clear_temp_tables; pkg_dw.bind_variable ('v_curr_date', v_curr_date); v_curr_date := TO_CHAR (TO_DATE (v_curr_date, 'yyyymmdd') - 1, 'yyyymmdd'); END LOOP; END; END PKG_REVN_ARPU; / |
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE OR REPLACE PROCEDURE IC_STAGE.pkg_etl#clear_temp_tables PACKAGE IS BEGIN NULL ; END ; / CREATE OR REPLACE PROCEDURE IC_STAGE.PKG_REVN_ARPU#AGGR_X_AGG00_REVN_DEALER ( p_date INTEGER , p_days INTEGER ) PACKAGE AS v_start_date INTEGER; v_curr_date INTEGER; BEGIN v_start_date := TO_CHAR( TO_DATE( p_date ,'yyyymmdd' ) - ( p_days - 1 ), 'yyyymmdd' ) ; v_curr_date := p_date ; WHILE ( v_curr_date >= v_start_date ) LOOP pkg_etl#clear_temp_tables ( ) ; pkg_dw.bind_variable ( 'v_curr_date' ,v_curr_date ) ; v_curr_date := TO_CHAR( TO_DATE( v_curr_date ,'yyyymmdd' ) - 1,'yyyymmdd' ) ; END LOOP ; END ; / |
调用不包含参数的函数名
EXCEPTION语句不支持有参数的函数名调用没有参数的函数名称,例如,SAD.SAD_CALC_ITEM_PKG_TEST_OB#error_msg ( ),但此函数error_msg没有定义参数,如下所示:
1 2 3 4 5 6 7 8 |
CREATE OR REPLACE FUNCTION SAD.SAD_CALC_ITEM_PKG_TEST_OB#func_name RETURN VARCHAR2 IS MIG_PV_VAL_DUMMY_G_FUNC_NAME VARCHAR2 ( 30 ) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( current_schema ( ) --- BEGIN --- RETURN l_func_name ; END ; |
脚本: SAD_CALC_ITEM_PKG_TEST_OB.SQL, SAD_CALC_ITEM_PRI_TEST_OB.SQL
输入:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
CREATE OR REPLACE PACKAGE BODY "SAD"."SAD_CALC_ITEM_PKG_TEST_OB" IS PROCEDURE back_sad_cost_line_t(pi_contract_number IN VARCHAR2, pi_quotation_id IN NUMBER, pi_product_code IN VARCHAR2, pi_process_batch_number IN NUMBER, po_error_msg OUT VARCHAR2) IS BEGIN --- LOOP INSERT INTO sad_cost_line_bak (processing_batch_number, contract_number, product_code, quotation_id, item_code, refresh_date, split_date, error_msg, created_by, creation_date, last_updated_by, last_update_date) VALUES (pi_process_batch_number, cur_1.contract_number, cur_1.product_code, cur_1.quotation_id, cur_1.item_code, cur_1.refresh_date, cur_1.split_date, cur_1.error_msg, cur_1.created_by, cur_1.creation_date, cur_1.last_updated_by, cur_1.last_update_date); END LOOP; --- WHEN OTHERS THEN po_error_msg := 'Others Exception raise in ' || func_name || ',' || SQLERRM; END back_sad_cost_line_t; END SAD_CALC_ITEM_PKG_TEST_OB; |
输出:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
CREATE OR REPLACE PROCEDURE SAD.SAD_CALC_ITEM_PKG_TEST_OB#back_sad_cost_line_t ( pi_contract_number IN VARCHAR2 ,pi_quotation_id IN NUMBER ,pi_product_code IN VARCHAR2 ,pi_process_batch_number IN NUMBER ,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 ( ) ,'SAD_CALC_ITEM_PKG_TEST_OB' ,'g_func_name' ) ::VARCHAR2 ( 30 ) ; ex_data_error EXCEPTION ; ex_prog_error EXCEPTION ; BEGIN --- LOOP INSERT INTO sad_cost_line_bak ( processing_batch_number ,contract_number ,product_code ,quotation_id ,item_code ,refresh_date ,split_date ,SAD.SAD_CALC_ITEM_PKG_TEST_OB#error_msg ( ) ,created_by ,creation_date ,last_updated_by ,last_update_date ) VALUES ( pi_process_batch_number ,cur_1.contract_number ,cur_1.product_code ,cur_1.quotation_id ,cur_1.item_code ,cur_1.refresh_date ,cur_1.split_date ,cur_1.error_msg ,cur_1.created_by ,cur_1.creation_date ,cur_1.last_updated_by ,cur_1.last_update_date ) ; END LOOP ; --- WHEN OTHERS THEN po_error_msg := 'Others Exception raise in ' || SAD.SAD_CALC_ITEM_PKG_TEST_OB#func_name ( ) || ',' || SQLERRM ; END ; |
输入:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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 VARCHAR2(100) ; BEGIN l_func_name := g_pkg_name || '.' || g_func_name ; RETURN l_func_name ; END ; PROCEDURE data_change_logs ( pi_table_name IN VARCHAR2 , pi_table_key_columns IN VARCHAR2 , po_error_msg OUT VARCHAR2 ) IS BEGIN g_func_name := 'insert_fnd_data_change_logs_t'; INSERT INTO fnd_data_change_logs_t ( logid, table_name, table_key_columns ) VALUES ( fnd_data_change_logs_t_s.NEXTVAL , pi_table_name, pi_table_key_columns ); EXCEPTION WHEN OTHERS THEN po_error_msg := 'Others Exception raise in ' || func_name || ',' || SQLERRM; END data_change_logs; END bas_dml_lookup_pkg; / |
输出:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
CREATE OR REPLACE FUNCTION SAD.bas_dml_lookup_pkg#func_name RETURN VARCHAR2 IS MIG_PV_VAL_DUMMY_G_PKG_NAME VARCHAR2(30) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( 'SAD', 'BAS_DML_LOOKUP_PKG', 'G_PKG_NAME' )::VARCHAR2(30) ; MIG_PV_VAL_DUMMY_G_FUNC_NAME VARCHAR2(100) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( 'SAD', 'BAS_DML_LOOKUP_PKG', 'G_FUNC_NAME' )::VARCHAR2(100) ; l_func_name VARCHAR2(100) ; BEGIN l_func_name := MIG_PV_VAL_DUMMY_G_PKG_NAME || '.' || MIG_PV_VAL_DUMMY_G_FUNC_NAME ; RETURN l_func_name ; END ; / CREATE OR REPLACE PROCEDURE SAD.bas_dml_lookup_pkg#data_change_logs ( pi_table_name IN VARCHAR2 , pi_table_key_columns IN VARCHAR2 , po_error_msg OUT VARCHAR2 ) IS MIG_PV_VAL_DUMMY_G_FUNC_NAME VARCHAR2(30) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( 'SAD' ,'BAS_DML_LOOKUP_PKG' ,'G_FUNC_NAME' )::VARCHAR2(30) ; BEGIN MIG_PV_VAL_DUMMY_G_FUNC_NAME := 'insert_fnd_data_change_logs_t' ; INSERT INTO fnd_data_change_logs_t ( logid,table_name,table_key_columns ) VALUES ( NEXTVAL ( 'fnd_data_change_logs_t_s' ) , pi_table_name, pi_table_key_columns ) ; MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( 'SAD', 'BAS_DML_LOOKUP_PKG', 'G_FUNC_NAME', MIG_PV_VAL_DUMMY_G_FUNC_NAME ) ; EXCEPTION WHEN OTHERS THEN po_error_msg := 'Others Exception raise in ' || SAD.bas_dml_lookup_pkg#func_name( ) || ',' || SQLERRM ; MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( 'SAD', 'BAS_DML_LOOKUP_PKG', 'G_FUNC_NAME', MIG_PV_VAL_DUMMY_G_FUNC_NAME ) ; END ; / |