更新时间:2024-06-29 GMT+08:00
分享

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

变量赋值

END

EXCEPTION处理

子事务处理

STRING

LONG

RESULT_CACHE

包含空格的关系运算符

替换变量

PARALLEL_ENABLE

TRUNCATE TABLE

ALTER SESSION

AUTONOMOUS

过程调用

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
;

变量赋值

图1 输入:PL/SQL
图2 输出:PL/SQL

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。

图3 输入:EXCEPTION处理
图4 输出:EXCEPTION处理

子事务处理

不支持子事务(即PL/SQL中的提交和回滚语句)。使用此参数的默认值True。

图5 输入:子事务处理
图6 输出:子事务处理

STRING

GaussDB(DWS)不支持Oracle PL/SQL数据类型STRING。使用VARCHAR来处理该数据类型。

图7 输入:STRING
图8 输出:STRING

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将从缓存中获取结果,而不必重新执行该函数。

某些场景下,这种缓存行为可带来显著的性能提升。

目标数据库不支持该关键字。该关键字会从目标文件中移除。

图9 输入:RESULT_CACHE
图10 输出:RESULT_CACHE

包含空格的关系运算符

GaussDB(DWS)不支持含有空格的关系运算符(<=、>=、!=)。DSC会删除运算符之间的空格。

图11 输入:关系运算符
图12 输出:关系运算符

替换变量

替换变量是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

在Oracle中,通过PARALLEL_ENABLE启用并发执行,从而实现负载分区。

输入: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语句。

该工具不支持迁移含有动态表名称的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

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语句将被逐字复制。

该工具不支持迁移命令子句含有变量的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 ;
/

相关文档