Oracle學習（一）

標籤：blog   io   ar   for   div   sp   cti   on   c   

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;

 

Oracle學習（一）