Oracle Learning (I.)

1. Create an object:

Create or replace type sys_col_id as Object (ID number (38))

2. Define an array

Create or replace type Sys_tbl_ids as table of sys_col_id

3, calculate the length of the delimiter, usage: splitter_count (' 1,2,3,4 ', ', '), return the length of the delimiter

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. Returns an array based on the string passed in

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. The stored procedure returns a cursor

Procedure Matchinginvoice (V_invoiceids varchar2, v_matchingids out sys_refcursor) as Ids_ta    ble 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;    --Put the incoming ID into the array 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
--wrap the array as a temporary table Join table (V_sys_tbl_ids) c on c.id = A.invoiceid Group by A.invoiceid, A.totalamount) loop--accumulate v_totaltaxamount First: = 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 Learning (I.)