文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle语法迁移> PL/SQL> 过程调用

过程调用

分享
更新时间: 2019/08/09 GMT+08:00

无参数过程

无参数过程

Need to put () after procedure name while calling the same.

需要在调用的相同的过程名称后加上() 。

例如:pkg_etl.clear_temp_tables()

输入

CREATE OR REPLACE PACKAGE BODY IC_STAGE.pkg_etl
AS
 PROCEDURE clear_temp_tables
 IS
 BEGIN
  NULL;
 END clear_temp_tables;
END pkg_etl;
/
CREATE OR REPLACE PACKAGE BODY IC_STAGE.PKG_REVN_ARPU
AS
   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;
   BEGIN
      v_start_date := TO_CHAR (TO_DATE (p_date, 'yyyymmdd') - (p_days - 1), 'yyyymmdd');
      v_curr_date := p_date;

      WHILE (v_curr_date >= v_start_date)
      LOOP
         pkg_etl.clear_temp_tables;
         pkg_dw.bind_variable ('v_curr_date', v_curr_date);

   v_curr_date := TO_CHAR (TO_DATE (v_curr_date, 'yyyymmdd') - 1, 'yyyymmdd');
      END LOOP;

   END;
END PKG_REVN_ARPU;
/  

输出

CREATE OR REPLACE PROCEDURE IC_STAGE.pkg_etl#clear_temp_tables PACKAGE IS
BEGIN
    NULL ;
END ;
/

CREATE OR REPLACE PROCEDURE IC_STAGE.PKG_REVN_ARPU#AGGR_X_AGG00_REVN_DEALER 
 ( p_date INTEGER
    , p_days INTEGER ) 
PACKAGE 
AS 
 v_start_date  INTEGER;
    v_curr_date  INTEGER;
BEGIN
     v_start_date := TO_CHAR( TO_DATE( p_date ,'yyyymmdd' ) - ( p_days - 1 ), 'yyyymmdd' ) ;
     v_curr_date := p_date ;

     WHILE ( v_curr_date >= v_start_date ) 
  LOOP
          pkg_etl#clear_temp_tables ( ) ;
          pkg_dw.bind_variable ( 'v_curr_date' ,v_curr_date ) ;
          v_curr_date := TO_CHAR( TO_DATE( v_curr_date ,'yyyymmdd' ) - 1,'yyyymmdd' ) ;
     END LOOP ;
END ;
/

没有参数的函数名被调用

EXCEPTION语句不支持有参数的函数名调用没有参数的函数名称,例如,SAD.SAD_CALC_ITEM_PKG_TEST_OB#error_msg ( ),但此函数error_msg的定义没有参数,如下所示:

CREATE

OR REPLACE FUNCTION SAD.SAD_CALC_ITEM_PKG_TEST_OB#func_name

RETURN VARCHAR2 IS MIG_PV_VAL_DUMMY_G_FUNC_NAME VARCHAR2 ( 30 ) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( current_schema ( )

---

BEGIN

---

RETURN l_func_name ;

END ;

SCRIPTS : SAD_CALC_ITEM_PKG_TEST_OB.sql, SAD_CALC_ITEM_PRI_TEST_OB.sql

INPUT :

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

PROCEDURE back_sad_cost_line_t(pi_contract_number IN VARCHAR2,

pi_quotation_id IN NUMBER,

pi_product_code IN VARCHAR2,

pi_process_batch_number IN NUMBER,

po_error_msg OUT VARCHAR2) IS

BEGIN

---

LOOP

INSERT INTO sad_cost_line_bak

(processing_batch_number,

contract_number,

product_code,

quotation_id,

item_code,

refresh_date,

split_date,

error_msg,

created_by,

creation_date,

last_updated_by,

last_update_date)

VALUES

(pi_process_batch_number,

cur_1.contract_number,

cur_1.product_code,

cur_1.quotation_id,

cur_1.item_code,

cur_1.refresh_date,

cur_1.split_date,

cur_1.error_msg,

cur_1.created_by,

cur_1.creation_date,

cur_1.last_updated_by,

cur_1.last_update_date);

END LOOP;

---

WHEN OTHERS THEN

po_error_msg := 'Others Exception raise in ' || func_name || ',' || SQLERRM;

END back_sad_cost_line_t;

END SAD_CALC_ITEM_PKG_TEST_OB;

OUTPUT :

CREATE

OR REPLACE PROCEDURE SAD.SAD_CALC_ITEM_PKG_TEST_OB#back_sad_cost_line_t ( pi_contract_number IN VARCHAR2

,pi_quotation_id IN NUMBER

,pi_product_code IN VARCHAR2

,pi_process_batch_number IN NUMBER

,po_error_msg OUT VARCHAR2 ) IS MIG_PV_VAL_DUMMY_G_FUNC_NAME VARCHAR2 ( 30 ) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( current_schema ( )

,'SAD_CALC_ITEM_PKG_TEST_OB'

,'g_func_name' ) ::VARCHAR2 ( 30 ) ;

ex_data_error

EXCEPTION ;

ex_prog_error

EXCEPTION ;

BEGIN

---

LOOP

INSERT INTO sad_cost_line_bak (

processing_batch_number

,contract_number

,product_code

,quotation_id

,item_code

,refresh_date

,split_date

,SAD.SAD_CALC_ITEM_PKG_TEST_OB#error_msg ( )

,created_by

,creation_date

,last_updated_by

,last_update_date

)

VALUES ( pi_process_batch_number ,cur_1.contract_number ,cur_1.product_code ,cur_1.quotation_id ,cur_1.item_code ,cur_1.refresh_date ,cur_1.split_date ,cur_1.error_msg ,cur_1.created_by ,cur_1.creation_date ,cur_1.last_updated_by ,cur_1.last_update_date ) ;

END LOOP ;

---

WHEN OTHERS THEN

po_error_msg := 'Others Exception raise in ' || SAD.SAD_CALC_ITEM_PKG_TEST_OB#func_name ( ) || ',' || SQLERRM ;

END ;

输入

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 ;

  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 := 'insert_fnd_data_change_logs_t';

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

输出

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 ;
/
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 ) 
IS 
 MIG_PV_VAL_DUMMY_G_FUNC_NAME VARCHAR2(30) := MIG_ORA_EXT.MIG_FN_GET_PKG_VARIABLE ( 'SAD' ,'BAS_DML_LOOKUP_PKG' ,'G_FUNC_NAME' )::VARCHAR2(30) ;
BEGIN
     MIG_PV_VAL_DUMMY_G_FUNC_NAME := 'insert_fnd_data_change_logs_t' ;

     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 ;
/
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区