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

包

包是对逻辑上相关的PL/SQL类型、变量、函数和过程进行分组形成的模式对象。在Oracle中，每个包由两部分组成：包规范和包体。包规范可能包含变量，以及在变量中声明的REF CURSOR。包的REF CURSOR会被识别并迁移至引用位置。包体中的函数和过程将迁移到单独的函数和过程中。包体中的类型和变量会迁移到各个函数和过程中。

如果包规范和包体的模式名称不匹配，则DSC将在schematoolError.log文件中记录模式名称不匹配的错误。

图1 PL/SQL包迁移
输入：PL/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
 
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;
 /

输出（包含了输入包体中每个函数和过程各自的函数和过程）

 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
 
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包 
1
2
 
CREATE OR REPLACE VIEW vw_emp_name AS 
          Select pkg_get_empdet.get_sal(emp.empno) as empsal from emp;

输出

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

输入：无参数的函数/过程

以防过程或函数没有任何参数，调用相同的过程或函数时，需要在过程或函数名后添加()。 
 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
 
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; 
/

输出

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

输入：无过程或函数的包体

以防包体没有任何逻辑，如过程和函数，迁移工具需要从同一包中删除所有代码。通常情况下输出为空。

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

输入：SUBTYPE

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
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;
 /

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
"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调度和管理作业列队中的作业。

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

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
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(DWS)的变量声明不支持NULL约束，因此需要注释NULL关键字。

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

输出

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

提示按系统访问特定模式中的对象。

1
2
3
4
5
6
 
CREATE OR REPLACE PACKAGE BODY IC_STAGE.PKG_REVN_ARPU
AS
-----------
-----------
END PKG_REVN_ARPU;
/

输出

1
2
3
4
 
SET  package_name_list = 'PKG_REVN_ARPU' ;
--------------
--------------
reset package_name_list ;

输入：配置参数addPackageNameList为false

提示按系统访问特定模式中的对象。

1
2
3
4
5
6
 
CREATE OR REPLACE PACKAGE BODY IC_STAGE.PKG_REVN_ARPU
AS
-----------
-----------
END PKG_REVN_ARPU;
/

输出

1
 
SET SEARCH_PATH=PKG_REVN_ARPU,PUBLIC;

输入：PACKAGE

提示过程和函数属于包。

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

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
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关键字。

输入：嵌套过程

在过程中创建过程称为嵌套过程。嵌套过程是私有的，属于父过程。

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

输出

 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
 
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迁移不应将类型名称中的包名用作其模式。

输入

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

输出

 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
 
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包

相关文档