文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Oracle语法迁移/ PL/SQL包/ 包变量
更新时间：2024-06-29 GMT+08:00
查看PDF
分享

包变量

Oracle支持包变量，允许变量保留包中所有的函数/过程。DSC通过自定义函数实现GaussDB(DWS)支持包变量。

前提条件：
  • 创建并使用MIG_ORA_EXT模式。
  • 复制自定义脚本文件的内容，并在要执行迁移的所有目标数据库中执行此脚本。详情请参见迁移流程。

如果模式和包名称之间存在空格，或包规范或包体（二者之一）含有引号，则输出可能与预期不符。

输入：包变量 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
CREATE
     OR REPLACE PACKAGE scott.pkg_adm_util IS un_stand_value long := '`' ;
     defaultdate date := sysdate ;
g_pkgname CONSTANT VARCHAR2 ( 255 ) DEFAULT 'pkg_adm_util' ;
procedure p1 ;
END pkg_adm_util ;
/

CREATE
     OR REPLACE PACKAGE BODY scott.pkg_adm_util AS defaulttime timestamp := systimestamp ;
     PROCEDURE P1 AS BEGIN
          scott.pkg_adm_util.un_stand_value := 'A' ;
          pkg_adm_util.un_stand_value := 'B' ;
     un_stand_value := 'C' ;
DBMS_OUTPUT.PUT_LINE ( pkg_adm_util.defaultdate ) ;
DBMS_OUTPUT.PUT_LINE ( defaulttime ) ;
DBMS_OUTPUT.PUT_LINE ( scott.pkg_adm_util.un_stand_value ) ;
DBMS_OUTPUT.PUT_LINE ( pkg_adm_util.un_stand_value ) ;
DBMS_OUTPUT.PUT_LINE ( un_stand_value ) ;
END ;
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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
SCHEMA pkg_adm_util
; 
BEGIN
INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES
( SCHEMA_NAME ,PACKAGE_NAME ,SPEC_OR_BODY ,VARIABLE_NAME
,VARIABLE_TYPE ,CONSTANT_
I ,DEFAULT_VALUE ,EXPRESSION_I )
VALUES
( UPPER( 'scott' ) ,UPPER( 'pkg_adm_util' ) ,'S' ,UPPER(
'un_stand_value' ) ,UPPE
R( 'TEXT' ) ,false ,'`' ,false ) ;
INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES
( SCHEMA_NAME ,PACKAGE_NAME ,SPEC_OR_BODY ,VARIABLE_NAME
,VARIABLE_TYPE ,CONSTANT_
I ,DEFAULT_VALUE ,EXPRESSION_I )
VALUES
( UPPER( 'scott' ) ,UPPER( 'pkg_adm_util' ) ,'S' ,UPPER(
'defaultdate' ) ,UPPER( '
date' ) ,false ,$q$sysdate$q$ ,true ) ;
INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES
( SCHEMA_NAME ,PACKAGE_NAME ,SPEC_OR_BODY ,VARIABLE_NAME
,VARIABLE_TYPE ,CONSTANT_
I ,DEFAULT_VALUE ,EXPRESSION_I )
VALUES
( UPPER( 'scott' ) ,UPPER( 'pkg_adm_util' ) ,'S' ,UPPER(
'g_pkgname' ) ,UPPER( 'VA
RCHAR2 ( 255 )' ) ,true ,'pkg_adm_util' ,false ) ;
END ;
/
BEGIN
INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES
( SCHEMA_NAME ,PACKAGE_NAME ,SPEC_OR_BODY ,VARIABLE_NAME
,VARIABLE_TYPE ,CONSTANT_
I ,DEFAULT_VALUE ,EXPRESSION_I )
VALUES
( UPPER( 'scott' ) ,UPPER( 'pkg_adm_util' ) ,'B' ,UPPER(
'defaulttime' ) ,UPPER( '
timestamp' ) ,false ,$q$CURRENT_TIMESTAMP$q$ ,true ) ;
END ;
/
CREATE
OR REPLACE PROCEDURE pkg_adm_util.P1 AS
BEGIN
MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( current_schema ( )
,'pkg_adm_util' ,'un_stand_value' ,( 'A' ) ::TEXT ) ;
MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( current_schema ( )
,'pkg_adm_util' ,'un_stand_value' ,( 'B' ) ::TEXT ) ;
MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( current_schema ( )
,'pkg_adm_util' ,'un_stand_value' ,( 'C' ) ::TEXT ) ;

DBMS_OUTPUT.PUT_LINE ( MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE
( 'scott' ,'pkg_adm_util' ,'defaultdate' ) :: date ) ;
DBMS_OUTPUT.PUT_LINE ( MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE(
'scott' ,'pkg_adm_util' ,'defaulttime' ) :: timestamp ) ;
DBMS_OUTPUT.PUT_LINE ( MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE(
'scott' ,'pkg_adm_util' ,'un_stand_value' ) :: TEXT ) ;
DBMS_OUTPUT.PUT_LINE ( MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE(
'scott' ,'pkg_adm_util' ,'un_stand_value' ) :: TEXT ) ;
DBMS_OUTPUT.PUT_LINE ( MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE(
'scott' ,'pkg_adm_util' ,'un_stand_value' ) :: TEXT ) ; 
END ;
/

如果pkgSchemaNaming设置为true，

  • Oracle支持多个模式的包变量。如果不同的模式具有相同的包名和变量名，例如：
    • schema1.mypackage.myvariable
    • schema2.mypackage.myvariable

    则在迁移之后，模式名称将不会用于区分这两个包变量。由于模式名称被忽略，[any_schema] .mypackage.myvariable的最后一个数据类型声明或操作将覆盖schema1.mypackage.myvariable和schema2.mypackage.myvariable的类型和值。

输入：使用CONSTANT关键字在一个包中声明的默认值的包变量，并在另一个包中使用

在包规范中声明的全局变量可以在相同的包和其他包中被访问。

 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
 
PACKAGE "SAD"."BAS_SUBTYPE_PKG" : (Declaring global variable)
-------------------------------------------------
g_header_waiting_split_status CONSTANT VARCHAR2(20) := 'Waiting_Distribute';

PACKAGE SAD.sad_lookup_stage_pkg: (Used global variable)
--------------------------------------------------
PROCEDURE calc_product_price(pi_contract_no   IN VARCHAR2 DEFAULT NULL,
                               pi_stage_id      IN NUMBER DEFAULT NULL,
                               pi_calc_category IN VARCHAR2 DEFAULT 'all',
                               pi_op_code       IN NUMBER,
                               po_error_msg     OUT VARCHAR2) 
 IS

 CURSOR cur_contract IS
      SELECT DISTINCT sdh.contract_number, sdh.stage_id
        FROM sad_distribution_headers_t sdh
       WHERE sdh.status = bas_subtype_pkg.g_header_waiting_split_status
         AND sdh.contract_number = nvl(pi_contract_no, sdh.contract_number)
         AND sdh.stage_id = nvl(pi_stage_id, sdh.stage_id);

 v_ras_flag VARCHAR2 ( 1 ) ;
BEGIN
..
...
END calc_product_price;
/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
PROCEDURE calc_product_price(pi_contract_no   IN VARCHAR2 DEFAULT NULL,
                               pi_stage_id      IN NUMBER DEFAULT NULL,
                               pi_calc_category IN VARCHAR2 DEFAULT 'all',
                               pi_op_code       IN NUMBER,
                               po_error_msg     OUT VARCHAR2) 
 IS

 MIG_PV_VAL_DUMMY_G_HEADER_WAITING_SPLIT_STATUS VARCHAR2 ( 20 ) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( 'SAD' ,'bas_subtype_pkg' ,'g_header_waiting_split_status' ) ::VARCHAR2 ( 20 ) ;  

 CURSOR cur_contract IS
      SELECT DISTINCT sdh.contract_number, sdh.stage_id
        FROM sad_distribution_headers_t sdh
       WHERE sdh.status = MIG_PV_VAL_DUMMY_G_HEADER_WAITING_SPLIT_STATUS
         AND sdh.contract_number = nvl(pi_contract_no, sdh.contract_number)
         AND sdh.stage_id = nvl(pi_stage_id, sdh.stage_id);

 v_ras_flag VARCHAR2 ( 1 ) ;

BEGIN
..
...
END;
/

包变量需要在CURSOR声明之前声明。

输入：EXCEPTION的变量

包变量是一种全局变量，可以通过声明一次在整个包中使用。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
CREATE OR REPLACE PACKAGE BODY SAD.sad_lookup_stage_pkg IS

  ex_prog_error EXCEPTION;

PROCEDURE assert_null ( pi_value IN VARCHAR2 ) 
IS 
BEGIN
    IF pi_value IS NOT NULL THEN
            RAISE ex_prog_error ;

    END IF ;

END assert_null;

END SAD.sad_lookup_stage_pkg
/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE
     OR REPLACE PROCEDURE SAD.sad_lookup_stage_pkg#assert_null 
 ( pi_value IN VARCHAR2 ) 
PACKAGE
IS 
  ex_prog_error EXCEPTION;
BEGIN
    IF pi_value IS NOT NULL THEN
            RAISE ex_prog_error ;

    END IF ;

END ;
/

GaussDB没有软件包功能，因此包变量需要使用它的过程或函数中声明。

输入：若pkgSchemaNaming设置为false

包变量是一种全局变量，可以通过声明一次在整个包中使用。 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
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;
END SAD.bas_lookup_misc_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
35
36
37
38
39
40
 
INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
          PACKAGE_NAME
          ,SPEC_OR_BODY
          ,VARIABLE_NAME
          ,VARIABLE_TYPE
          ,CONSTANT_I
          ,DEFAULT_VALUE
          ,RUNTIME_EXEC_I
     )
     VALUES ( UPPER( 'bas_lookup_misc_pkg' )
     ,'B'
     ,UPPER( 'g_func_name' )
     ,UPPER( 'VARCHAR2(30)' )
     ,FALSE
     ,NULL
     ,FALSE ) ;

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

如果配置参数pkgSchemaNaming设置为false，则包变量迁移在某些地方会出错（例如，GET获取默认值和SET分配最终值不会被添加）。但是，内核团队不建议使用此设置。请咨询内核团队。

输入：数据类型声明为包变量的表列%TYPE

如果数据类型被声明为变量的表列%TYPE，在表创建级别定义的数据类型被视为相应的列。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
CREATE OR REPLACE PACKAGE BODY SAD.bas_lookup_misc_pkg IS

  v_emp_name emp.ename%TYPE;

PROCEDURE save_emp_dtls ( v_empno IN VARCHAR2 ) 
IS 
BEGIN

    IF v_emp_name IS NULL THEN
       v_emp_name := 'test';
    END IF ;

END save_emp_dtls;

END bas_lookup_misc_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
 
BEGIN

     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
          PACKAGE_NAME
          ,SPEC_OR_BODY
          ,VARIABLE_NAME
          ,VARIABLE_TYPE
          ,CONSTANT_I
          ,DEFAULT_VALUE
          ,RUNTIME_EXEC_I
     )
     VALUES ( UPPER( 'bas_lookup_misc_pkg' )
     ,'B'
     ,UPPER( 'v_emp_name' )
     ,UPPER( 'VARCHAR2(30)' )
     ,FALSE
     ,NULL
     ,FALSE ) ;

END ;
/
--*********************************************************
CREATE
     OR REPLACE PROCEDURE SAD.bas_lookup_misc_pkg#save_emp_dtls ( v_empno IN VARCHAR2 ) 
PACKAGE
IS 
  MIG_PV_VAL_DUMMY_EMP_NAME VARCHAR2 ( 30 ) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( 'SAD' ,'bas_lookup_misc_pkg' ,'v_emp_name' ) ::VARCHAR2 ( 30 ) ;
BEGIN
    IF MIG_PV_VAL_DUMMY_EMP_NAME IS NULL THEN
       MIG_PV_VAL_DUMMY_EMP_NAME := 'test';
    END IF ;

END ;
/

使用数据类型作为表列％TYPE迁移包变量时，需要从表中获取实际数据类型，并且在声明变量时使用该数据类型，而不是使用％TYPE。

输入：若配置参数pkgSchemaNaming设置为false

如果一起指定PACKAGE名称和SCHEMA名称，则需要在GET()上使用SCHEMA名称来获取默认值，并使用SET()来指定最终值。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
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;
END SAD.bas_lookup_misc_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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
BEGIN
     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
          PACKAGE_NAME
          ,SPEC_OR_BODY
          ,VARIABLE_NAME
          ,VARIABLE_TYPE
          ,CONSTANT_I
          ,DEFAULT_VALUE
          ,RUNTIME_EXEC_I
     )
     VALUES ( UPPER( 'bas_lookup_misc_pkg' )
     ,'B'
     ,UPPER( 'g_pkg_name' )
     ,UPPER( 'VARCHAR2(30)' )
     ,TRUE
     ,'bas_lookup_misc_pkg'
     ,FALSE ) ;

     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
          PACKAGE_NAME
          ,SPEC_OR_BODY
          ,VARIABLE_NAME
          ,VARIABLE_TYPE
          ,CONSTANT_I
          ,DEFAULT_VALUE
          ,RUNTIME_EXEC_I
     )
     VALUES ( UPPER( 'bas_lookup_misc_pkg' )
     ,'B'
     ,UPPER( 'g_func_name' )
     ,UPPER( 'VARCHAR2(30)' )
     ,FALSE
     ,NULL
     ,FALSE ) ;

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

输入：若配置参数pkgSchemaNaming设置为false

若配置参数pkgSchemaNaming设置为false

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
CREATE OR REPLACE PACKAGE BODY 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;
END SAD.bas_lookup_misc_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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
BEGIN
     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
          PACKAGE_NAME
          ,SPEC_OR_BODY
          ,VARIABLE_NAME
          ,VARIABLE_TYPE
          ,CONSTANT_I
          ,DEFAULT_VALUE
          ,RUNTIME_EXEC_I
     )
     VALUES ( UPPER( 'bas_lookup_misc_pkg' )
     ,'B'
     ,UPPER( 'g_pkg_name' )
     ,UPPER( 'VARCHAR2(30)' )
     ,TRUE
     ,'bas_lookup_misc_pkg'
     ,FALSE ) ;

     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
          PACKAGE_NAME
          ,SPEC_OR_BODY
          ,VARIABLE_NAME
          ,VARIABLE_TYPE
          ,CONSTANT_I
          ,DEFAULT_VALUE
          ,RUNTIME_EXEC_I
     )
     VALUES ( UPPER( 'bas_lookup_misc_pkg' )
     ,'B'
     ,UPPER( 'g_func_name' )
     ,UPPER( 'VARCHAR2(30)' )
     ,FALSE
     ,NULL
     ,FALSE ) ;

END ;
/
--********************************************************************
CREATE
     OR REPLACE FUNCTION 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 ( CURRENT_SCHEMA() ,'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 ( CURRENT_SCHEMA() ,'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 ( CURRENT_SCHEMA(),'bas_lookup_misc_pkg','g_pkg_name',MIG_PV_VAL_DUMMY_G_PKG_NAME ) ;
 MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( CURRENT_SCHEMA(),'bas_lookup_misc_pkg','g_func_name',MIG_PV_VAL_DUMMY_G_FUNC_NAME ) ;

    RETURN l_func_name ;


END ;
/

输入： 若pkgSchemaNaming设置为false，使用包变量

全局变量在包转换期间未正确转换，并在编译期间报错。如果配置参数pkgSchemaNaming设置为false，则某些位置不会进行包变量迁移。但是，内核团队不建议使用此设置，详情请咨询内核团队。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
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 ;

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
 
BEGIN
     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
            USER_NAME, PACKAGE_NAME, SPEC_OR_BODY
          , VARIABLE_NAME, VARIABLE_TYPE
          , CONSTANT_I, DEFAULT_VALUE, RUNTIME_EXEC_I
     )
     VALUES ( 'SAD', UPPER( 'bas_dml_lookup_pkg' ), 'B'
            , UPPER( 'g_pkg_name' ), UPPER( 'VARCHAR2 ( 30 )' )
            , TRUE, 'bas_dml_ic_price_rule_pkg', FALSE ) ;

     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
            USER_NAME, PACKAGE_NAME, SPEC_OR_BODY
          , VARIABLE_NAME, VARIABLE_TYPE
          , CONSTANT_I, DEFAULT_VALUE, RUNTIME_EXEC_I
     )
     VALUES ( 'SAD', UPPER( 'bas_dml_lookup_pkg' ), 'B'
            , UPPER( 'g_func_name' ), UPPER( 'VARCHAR2(100)' )
            , FALSE, NULL, FALSE ) ;

END ;
/

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

输入：(%type)表中的表字段类型定义

包转换期间，模式定义不会被添加到（％type）表中的表字段类型定义中，并且在编译期间会报错。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
CREATE TABLE CTP_BRANCH 
     ( ID            VARCHAR2(10)
  , NAME          VARCHAR2(100)
  , DESCRIPTION   VARCHAR2(500)
  );

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 CTP_BRANCH.NAME%TYPE;

  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 ;

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
 
BEGIN
     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
            USER_NAME, PACKAGE_NAME, SPEC_OR_BODY
          , VARIABLE_NAME, VARIABLE_TYPE
          , CONSTANT_I, DEFAULT_VALUE, RUNTIME_EXEC_I
     )
     VALUES ( 'SAD', UPPER( 'bas_dml_lookup_pkg' ), 'B'
            , UPPER( 'g_pkg_name' ), UPPER( 'VARCHAR2 ( 30 )' )
            , TRUE, 'bas_dml_ic_price_rule_pkg', FALSE ) ;

     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
            USER_NAME, PACKAGE_NAME, SPEC_OR_BODY
          , VARIABLE_NAME, VARIABLE_TYPE
          , CONSTANT_I, DEFAULT_VALUE, RUNTIME_EXEC_I
     )
     VALUES ( 'SAD', UPPER( 'bas_dml_lookup_pkg' ), 'B'
            , UPPER( 'g_func_name' ), UPPER( 'VARCHAR2(100)' )
            , FALSE, NULL, FALSE ) ;

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

EXCEPTION

包变量可以被添加为EXCEPTION，GaussDB(DWS)不支持此功能。

输入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
CREATE OR REPLACE PACKAGE BODY product_pkg IS

  ex_prog_error EXCEPTION;

  PROCEDURE assert_null(pi_value IN VARCHAR2) IS
  BEGIN
    IF pi_value IS NOT NULL
    THEN
      RAISE ex_prog_error;
    END IF;
  EXCEPTION
    WHEN ex_prog_error THEN
      RAISE ex_prog_error;

  END assert_null;
END product_pkg;
/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
CREATE OR REPLACE PROCEDURE product_pkg.Assert_null (pi_value IN VARCHAR2) 
IS 
  ex_prog_error EXCEPTION; 
BEGIN 
    IF pi_value IS NOT NULL THEN 
      RAISE ex_prog_error; 
    END IF; 
EXCEPTION 
  WHEN ex_prog_error THEN 
             RAISE ex_prog_error; 
END; 

/

默认值

function被指定为包变量的默认值。

输入

 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
42
43
44
45
46
47
 
BEGIN
     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
          PACKAGE_NAME
          ,SPEC_OR_BODY
          ,VARIABLE_NAME
          ,VARIABLE_TYPE
          ,CONSTANT_I
          ,DEFAULT_VALUE
          ,RUNTIME_EXEC_I
     )
     VALUES ( UPPER( 'PKG_REVN_ARPU' )
     ,'B'
     ,UPPER( 'imodel' )
     ,UPPER( 'log_table.ds_exec%TYPE' )
     ,FALSE
     ,pkg_etl.proc_set_chain ( 'DAILY ARPU' )
     ,FALSE ) ;

END ;
/
gSQL:PKG_REVN_ARPU_04.SQL:23: ERROR:  function pkg_etl.proc_set_chain(unknown) does not exist
LINE 15:      ,pkg_etl.proc_set_chain ( 'DAILY ARPU' )
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.



CREATE OR REPLACE PACKAGE BODY IC_STAGE.PKG_REVN_ARPU
AS
 imodel   log_table.ds_exec%TYPE := pkg_etl.proc_set_chain ('DAILY ARPU');
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;
   v_imodel   VARCHAR2(100);
   BEGIN
      pkg_etl.proc_start (p_date, 'AGGR_X_AGG00_REVN_DEALER ');

      v_start_date :=
         TO_CHAR (TO_DATE (p_date, 'yyyymmdd') - (p_days - 1), 'yyyymmdd');
      v_curr_date := p_date;
   v_imodel := imodel;

   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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
SET
     package_name_list = 'PKG_REVN_ARPU' ;

BEGIN
     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
          PACKAGE_NAME
          ,SPEC_OR_BODY
          ,VARIABLE_NAME
          ,VARIABLE_TYPE
          ,CONSTANT_I
          ,DEFAULT_VALUE
          ,RUNTIME_EXEC_I
     )
     VALUES ( UPPER( 'PKG_REVN_ARPU' )
     ,'B'
     ,UPPER( 'imodel' )
     ,UPPER( 'log_table.ds_exec%TYPE' )
     ,FALSE
     ,$q$pkg_etl.proc_set_chain ('DAILY ARPU')$q$
     ,TRUE ) ;

END ;
/
CREATE
     OR REPLACE PROCEDURE PKG_REVN_ARPU.AGGR_X_AGG00_REVN_DEALER ( p_date INTEGER
     ,p_days INTEGER ) 
  AS 
  MIG_PV_VAL_DUMMY_IMODEL log_table.ds_exec%TYPE := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( CURRENT_USER,'PKG_REVN_ARPU','imodel' ) ::log_table.ds_exec%TYPE ;
     v_start_date INTEGER ;
     v_curr_date INTEGER ;
     v_imodel VARCHAR2 ( 100 ) ;

BEGIN
     pkg_etl.proc_start ( p_date ,'AGGR_X_AGG00_REVN_DEALER ' ) ;
     v_start_date := TO_CHAR( TO_DATE( p_date ,'yyyymmdd' ) - ( p_days - 1 ),'yyyymmdd' ) ;
     v_curr_date := p_date ;
     v_imodel := MIG_PV_VAL_DUMMY_IMODEL ;
     MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( CURRENT_USER,'PKG_REVN_ARPU','imodel',MIG_PV_VAL_DUMMY_IMODEL ) ;

END ;
/
reset package_name_list ;

PLS_INTEGER

PLS_INTEGER数据类型未转换为用于包变量的INTEGER，但对其他本地变量起作用，因此，包变量PLS_INTEGER应转换为INTEGER 数据类型，例如，varaible1 PLS_INTEGER ==> varaible1 INTEGER。

脚本：SAD_CALC_BPART_PRICE_PKG.SQL, SAD_CALC_ITEM_PKG_TEST_OB.SQL, SAD_CALC_ITEM_PRICE_TEST_OB.SQL, SAD_CALC_ITEM_PRI_TEST_OB.SQL, SAD_CALC_ITEM_TEST_OB.SQL

输入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
CREATE OR REPLACE PACKAGE BODY "SAD"."SAD_CALC_BPART_PRICE_PKG" IS
g_max_number_of_entities PLS_INTEGER := 100;
FUNCTION split_warning(pi_contract_number IN VARCHAR2,
pi_stage_id        IN NUMBER,
pi_quotation_id    IN NUMBER,
pi_cfg_instance_id IN NUMBER) RETURN VARCHAR2 IS
BEGIN
---
l_item_list := items_no_cost(pi_contract_number        => pi_contract_number,
pi_stage_id               => pi_stage_id,
pi_quotation_id           => pi_quotation_id,
pi_cfg_instance_id        => pi_cfg_instance_id,
pi_max_number_of_entities => g_max_number_of_entities,
pi_sep_char               => g_item_sep_char,
po_error_msg              => po_error_msg);
---
END split_warning;
END SAD_CALC_BPART_PRICE_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
35
36
37
38
39
40
41
 
BEGIN
---
INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
PACKAGE_NAME
,SPEC_OR_BODY
,VARIABLE_NAME
,VARIABLE_TYPE
,CONSTANT_I
,DEFAULT_VALUE
,RUNTIME_EXEC_I
)
VALUES ( UPPER( 'SAD_CALC_BPART_PRICE_PKG' )
,'B'
,UPPER( 'g_max_number_of_entities' )
,UPPER( 'PLS_INTEGER' )
,FALSE
,100
,FALSE ) ;
---
END;
/
CREATE
OR REPLACE FUNCTION SAD.SAD_CALC_BPART_PRICE_PKG#split_warning ( pi_contract_number IN VARCHAR2
,pi_stage_id IN NUMBER
,pi_quotation_id IN NUMBER
,pi_cfg_instance_id IN NUMBER )
RETURN VARCHAR2 IS
---
MIG_PV_VAL_DUMMY_G_MAX_NUMBER_OF_ENTITIES PLS_INTEGER := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( current_schema ( )
,'SAD_CALC_BPART_PRICE_PKG'
,'g_max_number_of_entities' ) ::PLS_INTEGER ;
---
l_item_list := SAD.SAD_CALC_BPART_PRICE_PKG#items_no_cost ( pi_contract_number => pi_contract_number ,
pi_stage_id => pi_stage_id ,
pi_quotation_id => pi_quotation_id ,
pi_cfg_instance_id => pi_cfg_instance_id ,
pi_max_number_of_entities => MIG_PV_VAL_DUMMY_G_MAX_NUMBER_OF_ENTITIES ,
pi_sep_char => MIG_PV_VAL_DUMMY_G_ITEM_SEP_CHAR ,
po_error_msg => po_error_msg ) ;
---
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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
PLS_INTEGER datatype not converted into INTEGER for package variables but it's working fine for other local variables therefore for package variables also PLS_INTEGER should be converted to INTEGER datatype i.e varaible1 PLS_INTEGER ==> varaible1 INTEGER

SCRIPTS : SAD_CALC_BPART_PRICE_PKG.SQL, SAD_CALC_ITEM_PKG_TEST_OB.SQL, SAD_CALC_ITEM_PRICE_TEST_OB.SQL, SAD_CALC_ITEM_PRI_TEST_OB.SQL, SAD_CALC_ITEM_TEST_OB.SQL

INPUT : 

CREATE OR REPLACE PACKAGE BODY "SAD"."SAD_CALC_BPART_PRICE_PKG" IS

 g_max_number_of_entities PLS_INTEGER := 100;

 FUNCTION split_warning(pi_contract_number IN VARCHAR2,
                         pi_stage_id        IN NUMBER,
                         pi_quotation_id    IN NUMBER,
                         pi_cfg_instance_id IN NUMBER) RETURN VARCHAR2 IS

  BEGIN
  ---

  l_item_list := items_no_cost(pi_contract_number        => pi_contract_number,
                                 pi_stage_id               => pi_stage_id,
                                 pi_quotation_id           => pi_quotation_id,
                                 pi_cfg_instance_id        => pi_cfg_instance_id,
                                 pi_max_number_of_entities => g_max_number_of_entities,
                                 pi_sep_char               => g_item_sep_char,
                                 po_error_msg              => po_error_msg);

  ---

  END split_warning;

END SAD_CALC_BPART_PRICE_PKG;


OUTPUT : 

BEGIN

---
INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES (
          PACKAGE_NAME
          ,SPEC_OR_BODY
          ,VARIABLE_NAME
          ,VARIABLE_TYPE
          ,CONSTANT_I
          ,DEFAULT_VALUE
          ,RUNTIME_EXEC_I
     )
     VALUES ( UPPER( 'SAD_CALC_BPART_PRICE_PKG' )
     ,'B'
     ,UPPER( 'g_max_number_of_entities' )
     ,UPPER( 'PLS_INTEGER' )
     ,FALSE
     ,100
     ,FALSE ) ;
---

END;
/

CREATE
     OR REPLACE FUNCTION SAD.SAD_CALC_BPART_PRICE_PKG#split_warning ( pi_contract_number IN VARCHAR2
     ,pi_stage_id IN NUMBER
     ,pi_quotation_id IN NUMBER
     ,pi_cfg_instance_id IN NUMBER )
     RETURN VARCHAR2 IS

  ---

     MIG_PV_VAL_DUMMY_G_MAX_NUMBER_OF_ENTITIES PLS_INTEGER := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( current_schema ( )
     ,'SAD_CALC_BPART_PRICE_PKG'
     ,'g_max_number_of_entities' ) ::PLS_INTEGER ;

  ---

  l_item_list := SAD.SAD_CALC_BPART_PRICE_PKG#items_no_cost ( pi_contract_number => pi_contract_number ,
                pi_stage_id => pi_stage_id ,
                pi_quotation_id => pi_quotation_id ,
                pi_cfg_instance_id => pi_cfg_instance_id ,
                pi_max_number_of_entities => MIG_PV_VAL_DUMMY_G_MAX_NUMBER_OF_ENTITIES ,
                pi_sep_char => MIG_PV_VAL_DUMMY_G_ITEM_SEP_CHAR ,
                po_error_msg => po_error_msg ) ;
  ---

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
35
36
37
38
 
BEGIN
     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES 
   (  PACKAGE_NAME, SPEC_OR_BODY, VARIABLE_NAME
          , VARIABLE_TYPE, CONSTANT_I, DEFAULT_VALUE
          , RUNTIME_EXEC_I )
     VALUES ( UPPER('SAD_CALC_BPART_PRICE_PKG')
      , 'B', UPPER( 'g_max_number_of_entities' )
      , UPPER( 'INTEGER' ),FALSE,100
      , FALSE ) ;
END ;
/

CREATE OR REPLACE FUNCTION SAD.SAD_CALC_BPART_PRICE_PKG#split_warning 
 ( pi_contract_number IN VARCHAR2
    , pi_stage_id   IN NUMBER )
RETURN VARCHAR2 
PACKAGE 
IS 
 MIG_PV_VAL_DUMMY_G_MAX_NUMBER_OF_ENTITIES INTEGER := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE('SAD', 'SAD_CALC_BPART_PRICE_PKG', 'g_max_number_of_entities') ::INTEGER ;
    po_error_msg sad_products_t.exception_description%TYPE ;

BEGIN
     l_item_list := items_no_cost ( pi_contract_number => pi_contract_number ,pi_stage_id => pi_stage_id 
          , pi_max_number_of_entities => MIG_PV_VAL_DUMMY_G_MAX_NUMBER_OF_ENTITIES 
          , po_error_msg => po_error_msg ) ;
     MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ('SAD' ,'SAD_CALC_BPART_PRICE_PKG' ,'g_max_number_of_entities' ,MIG_PV_VAL_DUMMY_G_MAX_NUMBER_OF_ENTITIES);

     RETURN po_error_msg ;

EXCEPTION
    WHEN OTHERS THEN
        po_error_msg := 'Program Others abnormal, Fail to obtain the warning information.' || SQLERRM ;
        MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( 'SAD' ,'SAD_CALC_BPART_PRICE_PKG' ,'g_max_number_of_entities' ,MIG_PV_VAL_DUMMY_G_MAX_NUMBER_OF_ENTITIES ) ;

        RETURN po_error_msg ;

END ;
/

带有包变量的游标

SAD.sad_calc_product_price_pkg#calc_product_price中声明的游标包含包变量，并且需要被处理。

输入

 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
 
CREATE OR REPLACE PACKAGE SAD.bas_subtype_pkg IS
  g_header_waiting_split_status CONSTANT VARCHAR2(20) := 'Waiting_Distribute'; 
  SUBTYPE error_msg IS sad_products_t.exception_description%TYPE;
END bas_subtype_pkg;
/

CREATE OR REPLACE PACKAGE BODY SAD.sad_calc_product_price_pkg IS
  PROCEDURE calc_product_price(pi_contract_no   IN VARCHAR2 DEFAULT NULL,
                               pi_stage_id      IN NUMBER DEFAULT NULL,
                               po_error_msg     OUT VARCHAR2) IS
    CURSOR cur_contract IS
      SELECT DISTINCT sdh.contract_number, sdh.stage_id
        FROM sad_distribution_headers_t sdh
       WHERE sdh.status = bas_subtype_pkg.g_header_waiting_split_status
         AND sdh.contract_number = nvl(pi_contract_no, sdh.contract_number)
         AND sdh.stage_id = nvl(pi_stage_id, sdh.stage_id);

    lv_error_msg bas_subtype_pkg.error_msg;
  BEGIN
    FOR rec_contract IN cur_contract
    LOOP

        validate_process_status(rec_contract.contract_number,
                                rec_contract.stage_id,
                                lv_error_msg);
    END LOOP;

 po_error_msg := lv_error_msg;
  END calc_product_price;

END sad_calc_product_price_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
35
36
37
38
39
 
BEGIN
     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES 
    ( PACKAGE_NAME,SPEC_OR_BODY,VARIABLE_NAME
    , VARIABLE_TYPE,CONSTANT_I,DEFAULT_VALUE
    , RUNTIME_EXEC_I )
     VALUES ( UPPER('bas_subtype_pkg'), 'S', UPPER('g_header_waiting_split_status')
    , UPPER( 'VARCHAR2(20)' ), TRUE, 'Waiting_Distribute'
    , FALSE ) ;
END ;
/

CREATE OR REPLACE PROCEDURE SAD.sad_calc_product_price_pkg#calc_product_price 
 ( pi_contract_no IN VARCHAR2 DEFAULT NULL
    , pi_stage_id IN NUMBER DEFAULT NULL
    , po_error_msg OUT VARCHAR2 ) 
PACKAGE 
IS 
 MIG_PV_VAL_DUMMY_G_HEADER_WAITING_SPLIT_STATUS VARCHAR2 ( 20 ) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( 'SAD' ,'bas_subtype_pkg'
    ,'g_header_waiting_split_status' ) ::VARCHAR2 ( 20 ) ;

 CURSOR cur_contract IS
 SELECT DISTINCT sdh.contract_number, sdh.stage_id
      FROM sad_distribution_headers_t sdh
     WHERE sdh.status = MIG_PV_VAL_DUMMY_G_HEADER_WAITING_SPLIT_STATUS
       AND sdh.contract_number = nvl( pi_contract_no ,sdh.contract_number )
       AND sdh.stage_id = nvl( pi_stage_id ,sdh.stage_id ) ;

    lv_error_msg sad_products_t.exception_description%TYPE ;
BEGIN
     FOR rec_contract IN cur_contract 
  LOOP
          validate_process_status ( rec_contract.contract_number ,rec_contract.stage_id ,lv_error_msg ) ;

     END LOOP ;
     po_error_msg := lv_error_msg ;
     MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( 'SAD' ,'bas_subtype_pkg' ,'g_header_waiting_split_status' ,MIG_PV_VAL_DUMMY_G_HEADER_WAITING_SPLIT_STATUS ) ;

END ;
/

RETURN后的SET VARIABLE函数

SET VARIABLE函数应在过程和函数中的RETURN语句前被调用。

输入

 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
 
CREATE OR REPLACE PACKAGE BODY SAD.bas_dml_lookup_pkg IS 
  g_pkg_name CONSTANT VARCHAR2(30) := 'bas_dml_lookup_pkg' ;
  g_func_name VARCHAR2(100);

  FUNCTION func_name
  RETURN VARCHAR2 
  IS 
    l_func_name VARCHAR2(100) ;
  BEGIN
  g_func_name := 'func_name';
     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 := 'data_change_logs';

 IF pi_table_name IS NULL
 THEN
  RETURN;
 END IF;

    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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
BEGIN
     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES 
  ( PACKAGE_NAME,SPEC_OR_BODY,VARIABLE_NAME
  , VARIABLE_TYPE,CONSTANT_I,DEFAULT_VALUE
  , RUNTIME_EXEC_I )
     VALUES ( UPPER('bas_dml_lookup_pkg'), 'B', UPPER('g_pkg_name')
    , UPPER( 'VARCHAR2(30)' ), TRUE, 'bas_dml_lookup_pkg'
    , FALSE ) ;

     INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES 
  ( PACKAGE_NAME,SPEC_OR_BODY,VARIABLE_NAME
  , VARIABLE_TYPE,CONSTANT_I,DEFAULT_VALUE
  , RUNTIME_EXEC_I )
  VALUES ( UPPER('bas_dml_lookup_pkg'), 'B', UPPER('g_func_name')
   , UPPER( 'VARCHAR2(100)' ), FALSE, NULL, FALSE ) ;

END ;
/
CREATE OR REPLACE FUNCTION SAD.bas_dml_lookup_pkg#func_name
RETURN VARCHAR2 
PACKAGE 
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
     MIG_PV_VAL_DUMMY_G_FUNC_NAME := 'func_name' ;
     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_dml_lookup_pkg' ,'g_func_name' ,MIG_PV_VAL_DUMMY_G_FUNC_NAME ) ;
     MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( 'SAD' ,'bas_dml_lookup_pkg' ,'g_pkg_name' ,MIG_PV_VAL_DUMMY_G_PKG_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 ) 
PACKAGE 
IS 
 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 ) ;
BEGIN
     MIG_PV_VAL_DUMMY_G_FUNC_NAME := 'data_change_logs' ;

     IF pi_table_name IS NULL THEN
        MIG_ORA_EXT.MIG_FN_SET_PKG_VARIABLE ( 'SAD' ,'bas_dml_lookup_pkg' ,'g_func_name' ,MIG_PV_VAL_DUMMY_G_FUNC_NAME ) ;
  RETURN ;
     END IF ;

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

空包

无需迁移空包体。

输入

1
2
3
4
5
 
CREATE OR REPLACE PACKAGE BODY SAD.bas_subtype_pkg IS
BEGIN
  NULL;
END bas_subtype_pkg;
/

输出文件为空。

父主题： PL/SQL包