Oracle存储过程中利用数组循环调用另一存储过程

朱雀 2022-08-18 11:38 129阅读 0赞

代码
    
    CREATE OR REPLACE PROCEDURE SP_DoReBalance (
          vAccountId in varchar, --当前公司主体账套
          vCheckMonth in varchar,--结账月
          retCnt out number
    ) is
    --DECLARE TYPE emp_array IS TABLE OF VARCHAR2(25)
    --INDEX BY BINARY_INTEGER;
    TYPE emp_array IS TABLE OF VARCHAR2(25)
    INDEX BY BINARY_INTEGER;
    
    arrFieldName emp_array;
    
    r integer := 0;
    /**********************************************
    update大类结算表-重新结账
    author: dingzh@jstrd.com
    date: 2010-02-04
    ***********************************************/
    begin
    
    arrFieldName(1 ) := 'THIS_MONTH_GET';
    arrFieldName(2 ) := 'MOVE_IN';
    arrFieldName(3 ) := 'CITY_MOVE_IN';
    arrFieldName(4 ) := 'MOVE_OUT';
    arrFieldName(5 ) := 'CITY_MOVE_OUT';
    arrFieldName(6 ) := 'PROJECT_TAKEOUT';
    arrFieldName(7 ) := 'PROJECT_GETIN';
    arrFieldName(8 ) := 'BUDGET_TAKEOUT';
    arrFieldName(9 ) := 'BUDGET_GETIN';
    arrFieldName(10) := 'NET_SALE';
    arrFieldName(11) := 'NET_BACK';
    arrFieldName(12) := 'TRANSFER_OUT';
    arrFieldName(13) := 'TRANSFER_IN';
    arrFieldName(14) := 'SCRAP_CABLE';
    arrFieldName(15) := 'SEND_BACK';
    arrFieldName(16) := 'CDMA_TEMP_GETIN';
    arrFieldName(17) := 'TEMP_GETIN';
    arrFieldName(18) := 'CANCELLATION';
    arrFieldName(19) := 'TERMINAL_OUT';
    arrFieldName(20) := 'TERMINAL_IN';
    
    FOR i IN 1..arrFieldName.count LOOP
       --dbms_output.put_line(arrFieldName(i));
       begin
          SP_ReBalance(arrFieldName(i),vAccountId,vCheckMonth,r);
          dbms_output.put_line('[' || arrFieldName(i) || '] - ' || r);
       end;
       retCnt := nvl(retCnt,0) + r;
    END LOOP;
    
    
      ----------------------------------------
    end SP_DoReBalance;