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;

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。