Oracle-关键字-BINARY_INTEGER(数组)

  TYPE cursor_type IS REF CURSOR;

   -- 数组类型
   TYPE str_array IS TABLE OF VARCHAR (512)
                        INDEX BY BINARY_INTEGER;

 

   --创建月结订单
   PROCEDURE p_bill_monthly (va_bills IN str_array)
   AS
      vn_index       NUMBER := 0;                                      --索引最小值
      vn_index_max   NUMBER := 0;                                      --索引最大值
      vv_bill_id     VARCHAR (32);                                    --当前账单编号
      vr_new_bill    fi_acc_bill%ROWTYPE;                              --第一行数据
      vr_del_bill    fi_acc_bill%ROWTYPE;                              --当前行数据
      vn_amount      NUMBER := 0.0;                                      --总金额
   BEGIN
      --第一个索引
      vn_index := va_bills.FIRST;
      --最后一个索引
      vn_index_max := va_bills.LAST;

      IF va_bills.COUNT = 0
      THEN
         raise_application_error (-20001, '账单编号数组不能为空');
      END IF;

      WHILE vn_index <= vn_index_max
      LOOP
         vv_bill_id := va_bills (vn_index);

         IF vn_index_max = 0
         THEN
            --获取第一行
            SELECT *
              INTO vr_new_bill
              FROM fi_acc_bill
             WHERE bill_id = vv_bill_id;

            IF vr_new_bill.amount IS NULL
            THEN
               raise_application_error (-20001, '数据为空');
            END IF;
         ELSE
            --获取当前要删除行
            SELECT *
              INTO vr_del_bill
              FROM fi_acc_bill
             WHERE bill_id = vv_bill_id;

            --是否要判断方向？

            vn_amount := vn_amount + vr_del_bill.amount;
         --删除当前行
         -- delete from fi_acc_bill where  bill_id=VV_Bill_ID;
         END IF;

         vn_index := vn_index + 1;
      END LOOP;
   --  vn_amount := vn_amount + vr_New_Bill.amount;

   --update fi_acc_bill set amount=vn_amount where  bill_id=vr_New_Bill.bill_id;
   END;