更新时间：2022-08-16 GMT+08:00
包
包是对逻辑上相关的PL/SQL类型、变量、函数和过程进行分组形成的模式对象。在Oracle中，每个包由两部分组成：包规范和包体。包规范可能包含变量，以及在变量中声明的REF CURSOR。包的REF CURSOR会被识别并迁移至引用位置。包体中的函数和过程将迁移到单独的函数和过程中。包体中的类型和变量会迁移到各个函数和过程中。
如果包规范和包体的模式名称不匹配，则DSC将在schematoolError.log文件中记录模式名称不匹配的错误。
图1 PL/SQL包迁移
输入：PL/SQL包（包规范和包体）
CREATE or REPLACE PACKAGE BODY pkg_get_empdet
IS
PROCEDURE get_ename(eno in number,ename out varchar2)
IS
BEGIN
SELECT ename || ',' || last_name
INTO ename
FROM emp
WHERE empno = eno;
END get_ename;
FUNCTION get_sal(eno in number) return number
IS
lsalary number;
BEGIN
SELECT salary
INTO lsalary
FROM emp
WHERE empno = eno;
RETURN lsalary;
END get_sal;
END pkg_get_empdet;
/
输出（包含了输入包体中每个函数和过程各自的函数和过程）
CREATE
or REPLACE PROCEDURE
pkg_get_empdet.get_ename ( eno in number ,ename out varchar2 ) IS
BEGIN
SELECT
ename || ',' || last_name INTO ename
FROM
emp
WHERE
empno = eno ;
END ;
/
CREATE
or REPLACE FUNCTION
pkg_get_empdet.get_sal ( eno in number )
return number IS lsalary number ;
BEGIN
SELECT
salary INTO lsalary
FROM
emp
WHERE
empno = eno ;
RETURN lsalary ;
END ;
/
输入：PL/SQL包
CREATE OR replace VIEW vw_emp_name AS
Select pkg_get_empdet.get_sal(emp.empno) as empsal from emp;
输出
CREATE
OR replace VIEW vw_emp_name AS (SELECT
pkg_get_empdet.get_sal (emp.empno) AS empsal
FROM
emp)
;
output:
set
package_name_list = 'func' ;
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 ;
/
reset
package_name_list ;
输入：无参数的函数/过程
以防过程或函数没有任何参数，调用相同的过程或函数时，需要在过程或函数名后添加()。
CREATE OR REPLACE PACKAGE BODY SAD.bas_lookup_misc_pkg IS
g_pkg_name CONSTANT VARCHAR2(30) := 'bas_lookup_misc_pkg';
g_func_name VARCHAR2(30);
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 func_name;
------------------------------------------------------------------------------
PROCEDURE insert_fnd_data_change_logs(pi_table_name IN VARCHAR2,
pi_table_key_columns IN VARCHAR2,
pi_table_key_values IN VARCHAR2,
pi_column_name IN VARCHAR2,
pi_column_change_from_value IN VARCHAR2,
pi_column_change_to_value IN VARCHAR2,
pi_op_code IN NUMBER,
pi_description IN VARCHAR2,
po_error_msg OUT VARCHAR2)
IS
BEGIN
g_func_name := 'insert_fnd_data_change_logs_t';
EXCEPTION
WHEN OTHERS THEN
po_error_msg := 'Others Exception raise in ' || func_name || ',' || SQLERRM;
END insert_fnd_data_change_logs;
END SAD.bas_lookup_misc_pkg;
/
输出
CREATE
OR REPLACE FUNCTION SAD.bas_lookup_misc_pkg#func_name
RETURN VARCHAR2
PACKAGE
IS
l_func_name VARCHAR2 ( 100 ) ;
MIG_PV_VAL_DUMMY_G_PKG_NAME VARCHAR2 ( 30 ) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( 'SAD' ,'bas_lookup_misc_pkg' ,'g_pkg_name' ) ::VARCHAR2 ( 30 ) ;
MIG_PV_VAL_DUMMY_G_FUNC_NAME VARCHAR2 ( 30 ) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( 'SAD' ,'bas_lookup_misc_pkg' ,'g_func_name' ) ::VARCHAR2 ( 30 ) ;
BEGIN
l_func_name := MIG_PV_VAL_DUMMY_G_PKG_NAME || '.' || MIG_PV_VAL_DUMMY_G_FUNC_NAME ;
MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( 'SAD','bas_lookup_misc_pkg','g_pkg_name',MIG_PV_VAL_DUMMY_G_PKG_NAME ) ;
MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( 'SAD','bas_lookup_misc_pkg','g_func_name',MIG_PV_VAL_DUMMY_G_FUNC_NAME ) ;
RETURN l_func_name ;
END ;
-------------------------------------------------------------------------------------------------
CREATE
OR REPLACE PROCEDURE SAD.bas_lookup_misc_pkg#insert_fnd_data_change_logs ( pi_table_name IN VARCHAR2
,pi_table_key_columns IN VARCHAR2
,pi_table_key_values IN VARCHAR2
,pi_column_name IN VARCHAR2
,pi_column_change_from_value IN VARCHAR2
,pi_column_change_to_value IN VARCHAR2
,pi_op_code IN NUMBER
,pi_description IN VARCHAR2
,po_error_msg OUT VARCHAR2 )
PACKAGE
IS
MIG_PV_VAL_DUMMY_G_FUNC_NAME VARCHAR2 ( 30 ) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( 'SAD' ,'bas_lookup_misc_pkg' ,'g_func_name' ) ::VARCHAR2 ( 30 ) ;
BEGIN
MIG_PV_VAL_DUMMY_G_FUNC_NAME := 'insert_fnd_data_change_logs_t' ;
MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( 'SAD','bas_lookup_misc_pkg','g_pkg_name',MIG_PV_VAL_DUMMY_G_PKG_NAME ) ;
MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( 'SAD','bas_lookup_misc_pkg','g_func_name',MIG_PV_VAL_DUMMY_G_FUNC_NAME ) ;
EXCEPTION
WHEN OTHERS THEN
po_error_msg := 'Others Exception raise in ' || SAD.bas_lookup_misc_pkg#func_name() || ',' || SQLERRM ;
END ;
/
输入：无过程或函数的包体
以防包体没有任何逻辑，如过程和函数，迁移工具需要从同一包中删除所有代码。通常情况下输出为空。
CREATE OR REPLACE PACKAGE BODY SAD.bas_subtype_pkg IS BEGIN NULL; END bas_subtype_pkg; /
输入：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;
/
输出
"SAD"."BAS_SUBTYPE_PKG" package will be blank after migration.
--**********************************************************
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不支持SUBTYPE，因此使用SUBTYPE变量时，需要替换为SUBTYPE创建时使用的实际类型。
输入：sys.dbms_job
DBMS_JOB调度和管理作业列队中的作业。
CREATE OR replace PACKAGE BODY "SAD"."EIP_HTM_INTEGRATION_PKG"
IS
PROCEDURE Greate_import_instruction_job
IS
v_jobid NUMBER;
BEGIN
IF
bas_lookup_misc_pkg.Exits_run_job('eip_htm_integration_pkg.import_instruction_job') = 'N' THEN
sys.dbms_job.Submit(job => v_jobid,
what => 'begin
eip_htm_integration_pkg.import_instruction_job;
end;',
next_date => SYSDATE);
COMMIT;
END IF;
---
END greate_import_instruction_job;
END eip_htm_integration_pkg;
输出
CREATE OR replace PROCEDURE
sad.Eip_htm_integration_pkg#greate_import_instruction_job
IS
v_jobid NUMBER;
BEGIN
IF Bas_lookup_misc_pkg#exits_run_job (
'eip_htm_integration_pkg.import_instruction_job') = 'N' THEN
dbms_job.Submit(job => v_jobid,
what => 'begin
eip_htm_integration_pkg.import_instruction_job;
end;',
next_date => SYSDATE);
/* COMMIT; */
NULL;
END IF;
---
END;
调用包时需要删除SYS模式。
输入：过程/函数变量
由于GaussDB的变量声明不支持NULL约束，因此需要注释NULL关键字。
CREATE OR REPLACE PACKAGE BODY SAD.sad_lookup_contract_pkg IS
FUNCTION CONTRACT_DISTRIBUTE_STATUS_S2(PI_CONTRACT_NUMBER IN VARCHAR2)
RETURN VARCHAR2 IS
L_CONTRACT_DISTRIBUTE_STATUS VARCHAR2(10) NULL;
BEGIN
IF CUR_CONTRACT.CONTRACT_STATUS = 0 THEN
L_CONTRACT_DISTRIBUTE_STATUS := 'Cancel';
ELSE
L_CONTRACT_DISTRIBUTE_STATUS := 'Active';
END IF;
RETURN L_CONTRACT_DISTRIBUTE_STATUS;
EXCEPTION
WHEN OTHERS THEN
L_CONTRACT_DISTRIBUTE_STATUS := NULL;
END CONTRACT_DISTRIBUTE_STATUS_S2;
END sad_lookup_contract_pkg;
/
输出
CREATE OR replace FUNCTION sad_lookup_contract_pkg.Contract_distribute_status_s2 ( pi_contract_number IN VARCHAR2 )
RETURN VARCHAR2
IS
l_contract_distribute_statusvarchar2 ( 10 )
/* NULL */
;
BEGIN
IF cur_contract.contract_status = 0 THEN
l_contract_distribute_status := 'Cancel' ;
ELSE
l_contract_distribute_status := 'Active' ;
END IF ;
RETURN l_contract_distribute_status ;
EXCEPTION
WHEN OTHERS THEN
l_contract_distribute_status := NULL ;
END ;/
输入：配置参数addPackageNameList为true
提示按系统访问特定模式中的对象。
CREATE OR REPLACE PACKAGE BODY IC_STAGE.PKG_REVN_ARPU AS ----------- ----------- END PKG_REVN_ARPU; /
输出
SET package_name_list = 'PKG_REVN_ARPU' ; -------------- -------------- reset package_name_list ;
输入：配置参数addPackageNameList为false
提示按系统访问特定模式中的对象。
CREATE OR REPLACE PACKAGE BODY IC_STAGE.PKG_REVN_ARPU AS ----------- ----------- END PKG_REVN_ARPU; /
输出
SET SEARCH_PATH=PKG_REVN_ARPU,PUBLIC;
输入：PACKAGE
提示过程和函数属于包。
CREATE OR REPLACE PACKAGE BODY SAD.sad_lookup_contract_pkg
IS
FUNCTION CONTRACT_DISTRIBUTE_STATUS_S2(PI_CONTRACT_NUMBER IN VARCHAR2)
RETURN VARCHAR2 IS
L_CONTRACT_DISTRIBUTE_STATUS VARCHAR2(10) ;
BEGIN
IF CUR_CONTRACT.CONTRACT_STATUS = 0 THEN
L_CONTRACT_DISTRIBUTE_STATUS := 'Cancel';
ELSE
L_CONTRACT_DISTRIBUTE_STATUS := 'Active';
END IF;
RETURN L_CONTRACT_DISTRIBUTE_STATUS;
EXCEPTION
WHEN OTHERS THEN
L_CONTRACT_DISTRIBUTE_STATUS := NULL;
END CONTRACT_DISTRIBUTE_STATUS_S2;
END sad_lookup_contract_pkg;
/
输出
CREATE OR replace FUNCTION sad_lookup_contract_pkg.Contract_distribute_status_s2 ( pi_contract_number IN VARCHAR2 )
RETURN VARCHAR2
PACKAGE
IS
l_contract_distribute_statusvarchar2 ( 10 ) ;
BEGIN
IF cur_contract.contract_status = 0 THEN
l_contract_distribute_status := 'Cancel' ;
ELSE
l_contract_distribute_status := 'Active' ;
END IF ;
RETURN l_contract_distribute_status ;
EXCEPTION
WHEN OTHERS THEN
l_contract_distribute_status := NULL ;
END ;
/
在IS/AS语句之前创建任何过程和函数时需要输入PACKAGE关键字。
输入：嵌套过程
在过程中创建过程称为嵌套过程。嵌套过程是私有的，属于父过程。
CREATE OR REPLACE PROCEDURE refresh_sw_product_amount(pi_stage_id IN NUMBER)
IS
v_product_amount sad_sw_product_amount_t.product_amount%TYPE;
FUNCTION get_sw_no
RETURN VARCHAR2
IS
v_xh NUMBER;
BEGIN
BEGIN
SELECT nvl(to_number(substrb(MAX(sw_no), 3, 4)), 0)
INTO v_xh
FROM sad.sad_sw_product_amount_t
WHERE pi_stage_id = pi_stage_id;
EXCEPTION WHEN OTHERS THEN
v_xh := 0;
END;
RETURN 'SW' || lpad(to_char(v_xh + 1), 4, '0') || 'Y';
END get_sw_no;
BEGIN
FOR rec_pu IN (SELECT t.*, sh.header_id
FROM asms.ht_stages t, asms.ht, sad.sad_distribution_headers_t sh
WHERE t.hth = ht.hth
AND sh.contract_number = t.hth
AND sh.stage_id = t.stage_id
AND ht.sw_track_flag = 'Y'
AND to_char(t.category_id) IN
(SELECT code
FROM asms.asms_lookup_values
WHERE type_code = 'CATEGORY_ID_EQUIPMENT'
AND enabled_flag = 'Y')
AND nvl(t.status, '-1') <> '0'
AND t.stage_id = pi_stage_id)
LOOP
SELECT nvl(SUM(nvl(product_amount, 0)), 0)
INTO v_product_amount
FROM sad.sad_products_t sp
WHERE sp.header_id = rec_pu.header_id
AND sp.sw_flag = 'Y';
END LOOP;
END refresh_sw_product_amount;
输出
CREATE OR REPLACE FUNCTION get_sw_no(pi_stage_id IN NUMBER)
RETURN VARCHAR2 IS
v_xh NUMBER;
BEGIN
BEGIN
SELECT nvl(to_number(substrb(MAX(sw_no), 3, 4)), 0)
INTO v_xh
FROM sad.sad_sw_product_amount_t
WHERE pi_stage_id = pi_stage_id;
EXCEPTION WHEN OTHERS THEN
v_xh := 0;
END;
RETURN 'SW' || lpad(to_char(v_xh + 1), 4, '0') || 'Y';
END ;
/
--*****************************************************************************
CREATE OR REPLACE PROCEDURE refresh_sw_product_amount(pi_stage_id IN NUMBER)
IS
v_product_amount sad_sw_product_amount_t.product_amount%TYPE;
BEGIN
FOR rec_pu IN (SELECT t.*, sh.header_id
FROM asms.ht_stages t, asms.ht, sad.sad_distribution_headers_t sh
WHERE t.hth = ht.hth
AND sh.contract_number = t.hth
AND sh.stage_id = t.stage_id
AND ht.sw_track_flag = 'Y'
AND to_char(t.category_id) IN
(SELECT code
FROM asms.asms_lookup_values
WHERE type_code = 'CATEGORY_ID_EQUIPMENT'
AND enabled_flag = 'Y')
AND nvl(t.status, '-1') <> '0'
AND t.stage_id = pi_stage_id)
LOOP
SELECT nvl(SUM(nvl(product_amount, 0)), 0)
INTO v_product_amount
FROM sad.sad_products_t sp
WHERE sp.header_id = rec_pu.header_id
AND sp.sw_flag = 'Y';
END LOOP;
END;
/
当实现嵌套过程/函数时，所有过程/函数中的包变量都需要处理。
子过程/函数迁移后，需要迁移父过程/函数。
pkgSchemaNaming为false
如果pkgSchemaNaming设置为false，则PL RECORD迁移不应将类型名称中的包名用作其模式。
输入
CREATE OR REPLACE PACKAGE BODY SAD.sad_dml_product_pkg IS
PROCEDURE save_sad_product_line_amount(pi_stage_id IN NUMBER,
pi_product_line_code IN VARCHAR2,
po_error_msg OUT VARCHAR2) IS
TYPE t_line IS RECORD(
product_line VARCHAR2(30),
product_amount NUMBER);
TYPE tab_line IS TABLE OF t_line INDEX BY BINARY_INTEGER;
rec_line tab_line;
v_product_line_arr VARCHAR2(5000);
v_product_line VARCHAR2(30) ;
v_count INTEGER;
v_start INTEGER;
v_pos INTEGER;
BEGIN
v_count := 0;
v_start := 1;
v_product_line_arr := pi_product_line_code;
LOOP
v_pos := instr(v_product_line_arr, ',', v_start);
IF v_pos <= 0
THEN
EXIT;
END IF;
v_product_line := substr(v_product_line_arr, v_start, v_pos - 1);
v_count := v_count + 1;
rec_line(v_count).product_line := v_product_line;
rec_line(v_count).product_amount := 0;
v_product_line_arr := substr(v_product_line_arr, v_pos + 1, length(v_product_line_arr));
END LOOP;
FOR v_count IN 1 .. rec_line.count
LOOP
UPDATE sad_product_line_amount_t spl
SET spl.product_line_amount = rec_line(v_count).product_amount
WHERE spl.stage_id = pi_stage_id
AND spl.product_line_code = rec_line(v_count).product_line;
IF SQL%NOTFOUND
THEN
INSERT INTO sad_product_line_amount_t
(stage_id, product_line_code, product_line_amount)
VALUES (pi_stage_id, rec_line(v_count).product_line, rec_line(v_count).product_amount);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
po_error_msg := 'Others Exception raise in ' || func_name || ',' || SQLERRM;
END save_sad_product_line_amount;
END sad_dml_product_pkg;
/
输出
CREATE TYPE SAD.sad_dml_product_pkg#t_line AS
( product_line VARCHAR2 ( 30 )
, product_amount NUMBER ) ;
CREATE OR REPLACE PROCEDURE SAD.sad_dml_product_pkg#save_sad_product_line_amount
( pi_stage_id IN NUMBER
, pi_product_line_code IN VARCHAR2
, po_error_msg OUT VARCHAR2 )
PACKAGE
IS
TYPE tab_line IS VARRAY ( 10240 ) OF SAD.sad_dml_product_pkg#t_line ;
rec_line tab_line ;
v_product_line_arr VARCHAR2 ( 5000 ) ;
v_product_line VARCHAR2 ( 30 ) ;
v_count INTEGER ;
v_start INTEGER ;
v_pos INTEGER ;
BEGIN
v_count := 0 ;
v_start := 1 ;
v_product_line_arr := pi_product_line_code ;
LOOP
v_pos := instr( v_product_line_arr ,',' ,v_start ) ;
IF v_pos <= 0 THEN
EXIT ;
END IF ;
v_product_line := SUBSTR( v_product_line_arr ,v_start ,v_pos - 1 ) ;
v_count := v_count + 1 ;
rec_line ( v_count ).product_line := v_product_line ;
rec_line ( v_count ).product_amount := 0 ;
v_product_line_arr := SUBSTR( v_product_line_arr ,v_pos + 1 ,length( v_product_line_arr ) ) ;
END LOOP ;
FOR v_count IN 1.. rec_line.count
LOOP
UPDATE sad_product_line_amount_t spl
SET spl.product_line_amount = rec_line ( v_count ).product_amount
WHERE spl.stage_id = pi_stage_id
AND spl.product_line_code = rec_line ( v_count ).product_line ;
IF SQL%NOTFOUND THEN
INSERT INTO sad_product_line_amount_t
( stage_id, product_line_code, product_line_amount )
VALUES ( pi_stage_id, rec_line ( v_count ).product_line
, rec_line ( v_count ).product_amount ) ;
END IF ;
END LOOP ;
EXCEPTION
WHEN OTHERS THEN
po_error_msg := 'Others Exception raise in ' || func_name || ',' || SQLERRM ;
END ;
/
父主题： PL/SQL包
