Showing posts with label Type. Show all posts
Showing posts with label Type. Show all posts

May 7, 2013

Oracle Collection - Varrays


Varrays hold a fixed number of elements, although the number of elements can be changed at runtime. Like nested tables, varrays use sequential numbers as the index or key to the elements. You can define equivalent SQL types, allowing varrays to be stored in database tables. Varrays are a good choice when the number of elements is known in advance, and when the elements are likely to be accessed in sequence.


      A VARRAY is an array of varying size. It has an ordered set of data elements, and all the elements are of the same data type. The number of elements in a VARRAY is the "size" of the VARRAY. You must specify a maximum size (but not a minimum size) when you declare the VARRAY type.

         In general, the VARRAY type should be used when the number of items to be stored is small; it is not suitable for large numbers of items or elements. Note that you cannot index or constrain VARRAY values. Varray is available in PL/SQL as well as in SQL

Arrays must be dense (have consecutive subscripts). So, you cannot delete individual elements from an array


  • Use to preserve ordered list
  • Use when working with a fixed set, with a known number of entries
  • Use when you need to store in the database and operate on the Collection as a whole

  •   Varrays in SQL

    CREATE OR REPLACE TYPE varray_type AS VARRAY(SIZE) OF element_type;

     Varrays in PLSQL

    TYPE varray_type IS VARRAY(SIZE) OF element_type (datatype) ;

    varray_name varray_type; (variable type in PL/SQL)

    varray_name := varray_type();(create it using the constructor)


    CREATE TABLE table_name(field1 [VARCHAR2 NUMBER DATE],field2 varray_type);

    Example :
    CREATE TYPE Project AS OBJECT ( project_no NUMBER(2), title  VARCHAR2(35),cost  NUMBER(7,2));

    CREATE TYPE ProjectList AS VARRAY(50) OF Project;

    CREATE TABLE department ( dept_id  NUMBER(2), name  VARCHAR2(15), budget NUMBER(11,2), projects ProjectList);


    BEGIN
       INSERT INTO department
          VALUES(30, 'Accounting', 1205700,
             ProjectList(Project(1, 'Design New Expense Report', 3250),
                         Project(2, 'Outsource Payroll', 12350),
                         Project(3, 'Evaluate Merger Proposal', 2750),
                         Project(4, 'Audit Accounts Payable', 1425)));
    END;

    DECLARE
       new_projects ProjectList :=
          ProjectList(Project(1, 'Issue New Employee Badges', 13500),
                      Project(2, 'Develop New Patrol Plan', 1250),
                      Project(3, 'Inspect Emergency Exits', 1900),
                      Project(4, 'Upgrade Alarm System', 3350),
                      Project(5, 'Analyze Local Crime Stats', 825));
    BEGIN
       UPDATE department
          SET projects = new_projects WHERE dept_id = 60;
    END;

    ---

    May 3, 2013

    Oracle Collection - PLSQL Table

    Collections


        A collection is an ordered group of elements ,of the same type.Collection have only one dimension,we can nodel multi-dimensional arrays by creating collections whose elements are also collections.

    The three type of collections are following

    1) Associative Array
    2) Nested tables
    3) Varrays

    Associative Array
    TYPE array_type IS TABLE OF element_type INDEX BY key_type;
    array_name array_type;
    array_name(index) := value;


    The “Associative Arrays” are also known as “Index-By” tables in PL/SQL.Associative arrays are single-dimensional, unbounded, sparse collections of homogeneous elements. Associative arrays can be sparse, which means not all elements between two elements need to be defined.Similar to hash table in other language
    For instance you can have an element at index -29 and one at index 12 with nothing in between. Homogeneous elements mean every element must be of the same type.
    Associative arrays are useful for small to medium sized lookup tables where the array can be constructed in memory ( only , which is destroyed after the session ends) each time a procedure is called or a package is initialized. There is no fixed limit on their size and their index values are more flexible- associative array keys can be negative and/or nonsequential, and associative arrays can use string values instead of numbers .The Associative Array is only available in PL/SQL


    type my_tab_t is table of number index by pls_integer/ binary_integer;
    type my_tab_t is table of number index by varchar2(4000);
    type my_tab_t is table of tab.value%TYPE index by tab.id%TYPE;
    Example :
    Declare
      cursor c_customer is select cust_name,cust_no,rownum from customers;
      type type_cname is table of customers% rowtype index by binary_integer;
      tab_cname type_cname;
      v_counter number:=0;


    begin

    for r_customer in c_customer loop
       tab_cname(v_counter) := r_customer.cust_name ;
       v_counter:=v_counter+1;
    end loop;


    forall i in tab_cname.first .. tab_cname.last
      dbms_output.put_line(tab_cname(i));
    end;


    PLSQL Table attributes

    DELETE - Delete rows in a table.
    EXISTS - Return TRUE if the specified entry exists in the table.
    COUNT - Returns the number of rows in the table .
    FIRST - Returns the index of the first in the table.
    LAST - Returns the index of last row in the table.
    NEXT - Returns the index of the last row in the table .
    PRIOR - Returns the index of previous row in the table before the specified row.

    Use NOCOPY hint to reduce overhead of passing collections in and out of program units






    -----------

    Jan 9, 2009

    To get diff data 's of similar id in a row

    -----------

    create or replace type string_agg_type as object (
    total varchar2(4000),

    static function
    ODCIAggregateInitialize(sctx IN OUT string_agg_type )
    return number,

    member function
    ODCIAggregateIterate(self IN OUT string_agg_type ,
    value IN varchar2 )
    return number,

    member function
    ODCIAggregateTerminate(self IN string_agg_type,
    returnValue OUT varchar2,
    flags IN number)
    return number,

    member function
    ODCIAggregateMerge(self IN OUT string_agg_type,
    ctx2 IN string_agg_type)
    return number
    );
    /

    create or replace type body string_agg_type
    is

    static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
    return number
    is
    begin
    sctx := string_agg_type( null );
    return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(self IN OUT string_agg_type,
    value IN varchar2 )
    return number
    is
    begin
    self.total := self.total || ',' || value;
    return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self IN string_agg_type,
    returnValue OUT varchar2,
    flags IN number)
    return number
    is
    begin
    returnValue := ltrim(self.total,',');
    return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self IN OUT string_agg_type,
    ctx2 IN string_agg_type)
    return number
    is
    begin
    self.total := self.total || ctx2.total;
    return ODCIConst.Success;
    end;

    end;
    /

    CREATE or replace
    FUNCTION stragg(input varchar2 )
    RETURN varchar2
    PARALLEL_ENABLE AGGREGATE USING string_agg_type;
    /

    select deptno, stragg(ename)
    from scott.emp
    group by deptno
    /

    From 10g onwards No need for user defined function and type , We can use Oracle supplied function

    select deptno, wmsys.wm_concat(ename)
    from scott.emp group by deptno

    In Oracle 10g  wm_concat is an oracle undocumnented function 
     
    From 11g onwards , We can use Oracle supplied listlagg function .

    select distinct listagg(orcl, ',')   within group(order by orcl) over(partition by 1) orcl  from
     ( select 'ORACLE9i' orcl from dual
     union
     select 'ORACLE10g' from dual
    union select 'ORACLE11g' from dual )  c ;

    Instead of wm_concat  in Oracle 10 g

    SELECT DEPARTMENT_ID , RTRIM(XMLAGG(XMLELEMENT(E, FIRST_NAME || ',')).EXTRACT('//text()'), ',') ENAMES FROM EMPLOYEES GROUP BY DEPARTMENT_ID ;

     SELECT SUBSTR(SYS_CONNECT_BY_PATH(username, ';'), 2) csv FROM (SELECT username, ROW_NUMBER() OVER(ORDER BY username) rn, COUNT(*) OVER() cnt FROM all_users where rownum <= 3) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1;