更新时间：2022-12-08 GMT+08:00
PL/SQL集合（使用自定义类型）
本节主要介绍Oracle PL/SQL集合的迁移语法。迁移语法决定了关键字/功能的迁移方式。
自定义类型（UDT）衍生于数据库支持的数据类型。
自定义数据类型基于内置数据类型和其他自定义数据类型，定义应用程序中数据的结构和行为。自定义类型便于用户使用PL/SQL集合。
UDT表
创建该类型的表，以跟踪用户定义类型的结构。表中不存储任何数据。
输入：CREATE TABLE TYPE
CREATE <OR REPLACE> TYPE <schema.>inst_no_type IS TABLE OF VARCHAR2 (32767);
输出
CREATE TABLE<schema.>mig_inst_no_type
( typ_col VARCHAR2 (32767) );
UDT VArray
输入：CREATE VArray
CREATE TYPE phone_list_typ_demo AS VARRAY(n) OF VARCHAR2(25);
输出
CREATE TABLE mig_pone_list_typ_demo
( typ_col VARCHAR2 (25) );
声明用户自定义类型
输入：声明用户自定义类型
DECLARE
v_SQL_txt_array
inst_no_type <:=
inst_no_type()>;
BEGIN
…
输出
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
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类型
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;
/
输出
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类型
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
…
输出
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类型
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
…
输出
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
Gauss支持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 ;
/
由于高斯不支持SUBTYPE，因此使用SUBTYPE变量时，需要将其替换成创建SUBTYPE时使用的实际类型。
父主题： Oracle语法迁移
