文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Oracle语法迁移/ PL/SQL集合（使用自定义类型）
更新时间：2024-10-26 GMT+08:00
查看PDF
分享

PL/SQL集合（使用自定义类型）

本节主要介绍Oracle PL/SQL集合的迁移语法。迁移语法决定了关键字/功能的迁移方式。

自定义类型（UDT）衍生于数据库支持的数据类型。

自定义数据类型基于内置数据类型和其他自定义数据类型，定义应用程序中数据的结构和行为。自定义类型便于用户使用PL/SQL集合。

UDT表

创建该类型的表，以跟踪用户定义类型的结构。表中不存储任何数据。

输入：CREATE TABLE TYPE

1
 
CREATE <OR REPLACE> TYPE <schema.>inst_no_type IS TABLE OF VARCHAR2 (32767);

输出

1
2
 
CREATE TABLE<schema.>mig_inst_no_type           
       ( typ_col VARCHAR2 (32767) );

UDT VArray

输入：CREATE VArray

1
 
CREATE TYPE phone_list_typ_demo AS VARRAY(n) OF VARCHAR2(25);

输出

1
2
 
CREATE TABLE mig_pone_list_typ_demo 
         ( typ_col VARCHAR2 (25) );

声明用户自定义类型

输入：声明用户自定义类型

1
2
3
4
5
6
 
DECLARE
       v_SQL_txt_array            
        inst_no_type <:=    
        inst_no_type()>;
BEGIN
       …

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
DECLARE
/*       v_SQL_txt_array inst_no_type <:= inst_no_type()>; */
BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS 
          v_SQL_txt_array; 
              CREATE LOCAL TEMPORARY TABLE 
               v_SQL_txt_array 
                   ON COMMIT PRESERVE ROWS 
                   AS SELECT *, CAST(NULL AS INT) AS 
                    typ_idx_col                                                                     
                       FROM mig_inst_no_type 
                      WHERE FALSE'; 
       …

UDT Count

输入：UDT，在FOR LOOP中使用COUNT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
BEGIN
    ...
   FOR i IN 1..v_jobnum_list.COUNT 
   LOOP
      SELECT COUNT(*) INTO v_abc
         FROM ...
        WHERE ...
          AND nvl(t.batch_num, 
           c_batchnum_null_num) = 
           v_jobnum_list(i);
      ...
   END LOOP;
   ...

输出

BEGIN
    ...
    FOR i IN 1..(SELECT COUNT(*) from v_jobnum_list) 
    LOOP
      SELECT COUNT(*) INTO v_abc
         FROM ...
        WHERE ...
          AND nvl(t.batch_num, c_batchnum_null_num) = 
          (SELECT typ_col FROM v_jobnum_list
                WHERE typ_idx_col = i);
      ...
   END LOOP;
   ...

UDT记录

记录类型用于创建记录，并且可以在任何PL/SQL块、子程序或包的声明部分中定义。

输入：RECORD类型 
1
2
3
4
5
6
7
8
9
 
Create
     or Replace Procedure test_proc AS TYPE t_log IS RECORD ( col1 int ,col2 emp.ename % type ) ;
     fr_wh_SQL t_log ;
BEGIN
     fr_wh_SQL.col1 := 101 ;
     fr_wh_SQL.col2 := 'abcd' ;
DBMS_OUTPUT.PUT_LINE ( fr_wh_SQL.col1 || ',' || fr_wh_SQL.col2 ) ;
END test_proc;
/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
Create
     or Replace Procedure test_proc AS /*TYPE t_log IS RECORD ( col1 int,col2 emp.ename%type );*/
     fr_wh_SQL RECORD ;
     MIG_t_log_col1 int ;
MIG_t_log_col2 emp.ename % type ;
BEGIN
select
          MIG_t_log_col1 as col1 ,MIG_t_log_col2 as col2 INTO FR_WH_SQL ;
          fr_wh_SQL.col1 := 101 ;
     fr_wh_SQL.col2 := 'abcd' ;
DBMS_OUTPUT.PUT_LINE ( fr_wh_SQL.col1 || ',' || fr_wh_SQL.col2 ) ;
END ;
/

增强用户自定义类型

DSC支持在特定数据类型和任何表字段中增强Oracle中使用的TABLE的PL/SQL类型。

输入：特定数据类型的TABLE的PL/SQL类型 
1
2
3
4
5
 
DECLARE
  type       fr_wh_SQL_info_type is table of VARCHAR(10);
 fr_wh_SQL  fr_wh_SQL_info_type   [:= fr_wh_SQL_info_type()];
BEGIN
      …

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
DECLARE
/*      type   fr_wh_SQL_info_type    is table of varchar(10); */
/*      fr_wh_SQL    fr_wh_SQL_info_type    [:= fr_wh_SQL_info_type()]; */
BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS mig_fr_wh_SQL_info_type; 
          CREATE LOCAL TEMPORARY TABLE mig_fr_wh_SQL_info_type 
                ( typ_col VARCHAR (10) ) 
              ON COMMIT PRESERVE ROWS' ;

      EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS fr_wh_SQL; 
                CREATE LOCAL TEMPORARY TABLE fr_wh_SQL 
                     ON COMMIT PRESERVE ROWS AS 
                     AS SELECT  *, CAST(NULL AS INT) AS typ_idx_col  
                          FROM mig_fr_wh_SQL_info_type 
                         WHERE FALSE';
               …
输入：任意表字段的TABLE的PL/SQL类型 
1
2
3
4
5
 
DECLARE
      type         fr_wh_SQL_info_type    is table of fr_wh_SQL_info.col1%type;
      fr_wh_SQL   fr_wh_SQL_info_type    [:= fr_wh_SQL_info_type()];
BEGIN
…

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
DECLARE
/*      type          fr_wh_SQL_info_type    is table of fr_wh_SQL_info.col1%type; */
/*      fr_wh_SQL   fr_wh_SQL_info_type    [:= fr_wh_SQL_info_type()]; */
BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS mig_fr_wh_SQL_info_type; 
           CREATE LOCAL TEMPORARY TABLE mig_fr_wh_SQL_info_type 
               ON COMMIT PRESERVE ROWS 
               AS SELECT col1 AS typ_col
                     FROM fr_wh_SQL_info 
                    WHERE FALSE' ;

      EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS fr_wh_SQL; 
           CREATE LOCAL TEMPORARY TABLE fr_wh_SQL 
               ON COMMIT PRESERVE ROWS AS 
               AS SELECT  *, CAST(NULL AS INT) AS typ_idx_col  
                    FROM mig_fr_wh_SQL_info_type 
                   WHERE FALSE';
…

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 ;
     /

RECORD

RECORD类型在包规范中声明但是实际作用于包体。

设置以下参数后，用户自定义数据类型将迁移为VARRY：

plSQLCollection=varray

输入：RECORD

CREATE OR REPLACE FUNCTION func1 (i1 INT)
RETURN INT
As
TYPE r_rthpagat_list IS RECORD (--Record information about cross-border RMB business parameters (rthpagat)
rthpagat_REQUESTID RMTS_REMITTANCE_PARAM.REQUESTID%TYPE ,rthpagat_PARAMTNAME RMTS_REMITTANCE_PARAM.PARAMTNAME%TYPE ,rthpagat_PARAMNUM RMTS_REMITTANCE_PARAM.PARAMNUM%TYPE ,rthpagat_PARAMSTAT RMTS_REMITTANCE_PARAM.PARAMSTAT%TYPE ,rthpagat_REQTELLERNO RMTS_REMITTANCE_PARAM.REQTELLERNO%TYPE ,rthpagat_REQUESTTIME RMTS_REMITTANCE_PARAM.REQUESTTIME%TYPE ,rthpagat_HOSTERRNO RMTS_REMITTANCE_PARAM.HOSTERRNO%TYPE ,rthpagat_HOSTERRMSG RMTS_REMITTANCE_PARAM.HOSTERRMSG%TYPE ,rthpagat_GATBANK RMTS_REMITTANCE_PARAM.VALUE1%TYPE ,rthpagat_GATEEBANK RMTS_REMITTANCE_PARAM.VALUE2%TYPE ,rthpagat_TELLER RMTS_REMITTANCE_PARAM.VALUE3%TYPE ,rthpagat_DATE RMTS_REMITTANCE_PARAM.VALUE4%TYPE ,rthpagat_BM_GATBANK RMTS_REMITTANCE_PARAM.VALUE5%TYPE ,rthpagat_BM_GATEEBANK RMTS_REMITTANCE_PARAM.VALUE6%TYPE ,rthpagat_BM_LMTEL RMTS_REMITTANCE_PARAM.VALUE7%TYPE ,rthpagat_BM_LMDAT RMTS_REMITTANCE_PARAM.VALUE8%TYPE ) ;
 
  v1  r_rthpagat_list;
BEGIN
 
END;
/

输出

CREATE
TYPE rmts_remitparammgmt_rthpagat.r_rthpagat_list AS (/* O_ERRMSG error description */
Rthpagat_REQUESTID
     rthpagat_REQUESTID RMTS_REMITTANCE_PARAM.REQUESTID%TYPE ,rthpagat_PARAMTNAME RMTS_REMITTANCE_PARAM.PARAMTNAME%TYPE ,rthpagat_PARAMNUM RMTS_REMITTANCE_PARAM.PARAMNUM%TYPE ,rthpagat_PARAMSTAT RMTS_REMITTANCE_PARAM.PARAMSTAT%TYPE ,rthpagat_REQTELLERNO RMTS_REMITTANCE_PARAM.REQTELLERNO%TYPE ,rthpagat_REQUESTTIME RMTS_REMITTANCE_PARAM.REQUESTTIME%TYPE ,rthpagat_HOSTERRNO RMTS_REMITTANCE_PARAM.HOSTERRNO%TYPE ,rthpagat_HOSTERRMSG RMTS_REMITTANCE_PARAM.HOSTERRMSG%TYPE ,rthpagat_GATBANK RMTS_REMITTANCE_PARAM.VALUE1%TYPE ,rthpagat_GATEEBANK RMTS_REMITTANCE_PARAM.VALUE2%TYPE ,rthpagat_TELLER RMTS_REMITTANCE_PARAM.VALUE3%TYPE ,rthpagat_DATE RMTS_REMITTANCE_PARAM.VALUE4%TYPE ,rthpagat_BM_GATBANK RMTS_REMITTANCE_PARAM.VALUE5%TYPE ,rthpagat_BM_GATEEBANK RMTS_REMITTANCE_PARAM.VALUE6%TYPE ,rthpagat_BM_LMTEL RMTS_REMITTANCE_PARAM.VALUE7%TYPE ,rthpagat_BM_LMDAT RMTS_REMITTANCE_PARAM.VALUE8%TYPE ) ;
 
CREATE OR REPLACE FUNCTION func1 (i1 INT)
RETURN INT
AS
  v1  r_rthpagat_list;
BEGIN
 
END;
/

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

输出

CREATE
     TYPE SAD.sad_dml_product_pkg#t_line AS ( product_line VARCHAR2 ( 30 )
                                                                                   ,product_amount NUMBER ) ;
  • 对于第一个输出的pkg.t，如果配置文件中的“pkgSchemaNaming”设置为“true”，则PL RECORD迁移应将包名称作为模式名称以及类型名称。
  • 对于第二个输出的pkg #t，假设TYPE属于sad_dml_product_pkg包：

若配置文件中的“pkgSchemaNaming”设置为false，PL RECORD迁移应将模式名称作为模式名称以及包名称+类型名称，以＃分隔类型名称。

SUBTYPE

SUBTYPE语句中，PL/SQL允许您定义自己的子类型或预定义数据类型的别名，有时称为抽象数据类型。

输入

CREATE OR REPLACE PACKAGE "SAD"."BAS_SUBTYPE_PKG" IS
SUBTYPE CURRENCY IS BAS_PRICE_LIST_T.CURRENCY%TYPE;
END bas_subtype_pkg;
/
CREATE OR REPLACE PACKAGE BODY "SAD"."BAS_SUBTYPE_PKG" IS
BEGIN
  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;
 /

输出

 CREATE OR REPLACE FUNCTION SAD.bas_lookup_misc_pk#get_currency(pi_price_type IN NUMBER) RETURN VARCHAR2 IS
    v_currency BAS_PRICE_LIST_T.CURRENCY%TYPE;
  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 ;
 /

由于GaussDB(DWS)不支持SUBTYPE，因此使用SUBTYPE变量时，需要将其替换成创建SUBTYPE时使用的实际类型。

父主题： Oracle语法迁移