Oracle学习（一）

1、创建一个对象：

create or replace type sys_col_id as object(id number(38))

2、定义一个数组

create or replace type sys_tbl_ids as table of sys_col_id

 3、算出分隔符的长度，用法:splitter_count('1,2,3,4',','),返回的是分隔符的长度

function splitter_count(str in varchar2, delim in char) return int as
    val pls_integer;
  begin
    val := length(replace(str, delim, delim || ' '));
    return val - length(str);
  end;

 4、根据传入的字符串 返回数组

function tokenize_to_sys_tbl_ids(str varchar2, delim char)
    return sys_tbl_ids as
    target     int;
    i          int;
    this_delim int;
    last_delim int;
    ids_table  sys_tbl_ids := sys_tbl_ids();
  BEGIN
    i          := 1;
    last_delim := 0;
    target     := splitter_count(str, delim);
    while i <= target loop
      this_delim := instr(str, delim, 1, i);
      ids_table.extend();
      ids_table(i) := sys_col_id(to_number(substr(str,
                                                  last_delim + 1,
                                                  this_delim - last_delim - 1)));
      i := i + 1;
      last_delim := this_delim;
    end loop;
  
    ids_table.extend();
    ids_table(i) := sys_col_id(to_number(substr(str, last_delim + 1)));
  
    return ids_table;
  end;

5、存储过程返回游标

procedure MatchingInvoice(v_Invoiceids  varchar2,
                            v_MatchingIds out sys_refcursor) as
    ids_table        sys_tbl_ids := sys_tbl_ids();
    v_rownum         number;
    v_sys_tbl_ids    sys_tbl_ids;
    v_TotalTaxAmount number(19, 6);
    v_Totalamount    number(19, 6);
  begin
    v_rownum         := 1;
    v_TotalTaxAmount := 0;
    v_Totalamount    := 0;
    --把传入的Id放入数组中
    v_sys_tbl_ids := pack_cmn.tokenize_to_sys_tbl_ids(v_Invoiceids, ',');
    for record_invoice in (Select a.invoiceid,
                                  sum(b.taxamount) taxamount,
                                  a.totalamount / 1.17 * 0.17 newtaxamount
                             From fi_invoice a
                             Join fi_invoicedetail b
                               On a.invoiceid = b.invoiceid
                              --把数组封装成临时表
                             Join table(v_sys_tbl_ids) c
                               On c.id = a.invoiceid
                            group by a.invoiceid, a.totalamount) loop
      --先累加
      v_TotalTaxAmount := v_TotalTaxAmount + record_invoice.taxamount;
      v_Totalamount    := v_Totalamount + record_invoice.newtaxamount;
      ids_table.extend();
      ids_table(v_rownum) := sys_col_id(record_invoice.invoiceid);
      v_rownum := v_rownum + 1;
      if abs(round(v_TotalTaxAmount - v_Totalamount, 2)) >= 0.05 then
        exit;
      end if;
    end loop;
    open v_MatchingIds for
      Select c.id From table(ids_table) c;
  end MatchingInvoice;