1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
nageswarareddy

Pl sql collections 2012-05-13

Pl sql collections

  1. nageswarareddy
    VARRAYS
    Creating a Varray Type
    1. A varray stores an ordered set of elements.
    2. Each element has an index associated with it.
    3. A varray has a maximum size that you can change dynamically.

    You create a varray type using the SQL DDL CREATE TYPE statement.

    You specify the maximum size and the type of elements stored in the varray when creating the

    The basic Oracle syntax for the CREATE TYPE statement for a VARRAY type definition would be:


    CREATE OR REPLACE TYPE name-of-type IS VARRAY(nn)of type

    Where name-of-type is a valid attribute name, nn is the number of elements (maximum) in the array, and type is the data type of the elements of the array.

    You can change the maximum size of a varray using the ALTER TYPE statement.


    SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
    2 /

    Type created.

    SQL>
    SQL> desc addressVarray;
    addressVarray VARRAY(2) OF VARCHAR2(50)




    Assign value to VARRAY

    SQL> SET ECHO ON
    SQL> SET SERVEROUTPUT ON
    SQL>
    SQL> DECLARE
    2 TYPE emp_array IS VARRAY(100) OF VARCHAR2(30);
    3
    4 emps emp_array;
    5 inx1 PLS_INTEGER;
    6
    7 BEGIN
    8 emps := emp_array ('emp One','emp Two');
    9
    10 FOR inx1 IN 1..2 LOOP
    11 DBMS_OUTPUT.PUT_LINE(emps(inx1));
    12 END LOOP;
    13 END;
    14 /
    emp One
    emp Two

    PL/SQL procedure successfully completed.

    SQL>
    SQL>




    Using VARRAYs

    In the Oracle environment, array subscripts start from 1, and not from 0 (as in C and Java).

    VARRAYs are of fixed length.

    You specify the length of the array when you define it.

    Arrays of elements of the same type use sequential numbers as a subscript.

    VARRAYS can be used both in PL/SQL and SQL.

    You should use VARRAYs when you know the size of your data set and that size is very stable.



    declare
    type VarrayType is varray(size) of ElementType;
    ...
    create or replace type VarrayType is varray(size) of ElementType;

    The size of a VARRAY must be a positive integer and cannot be null.

    You cannot create an array of REF CURSORs.


    Example:



    SQL>
    SQL> declare
    2 type month_va is varray(13) of VARCHAR2(20);
    3 v_month_va month_va;
    4 v_count_nr number;
    5 begin
    6 v_month_va:=month_va('A','B','C','D','E','F','G');
    7 DBMS_OUTPUT.put_line('Length:'||v_month_va.count);
    8
    9 v_month_va.extend;
    10 v_month_va(v_month_va.last):='Null';
    11 DBMS_OUTPUT.put_line('Length:'||v_month_va.count);
    12
    13 for i in v_month_va.first..v_month_va.last
    14 loop
    15 DBMS_OUTPUT.put_line('v_month_va(i): '||v_month_va(i));
    16 end loop;
    17 end;
    18 /
    Length:7
    Length:8
    v_month_va(i): A
    v_month_va(i): B
    v_month_va(i): C
    v_month_va(i): D
    v_month_va(i): E
    v_month_va(i): F
    v_month_va(i): G
    v_month_va(i): Null

    PL/SQL procedure successfully completed.



    Getting Information on Varrays

    You can use the DESCRIBE command to get information on your varray types.

    SQL> CREATE or replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
    2 /

    Type created.

    SQL>
    SQL> DESCRIBE addressVarray;
    addressVarray VARRAY(2) OF VARCHAR2(50)


    CREATE TABLE with a VARRAY

    SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
    2 /

    SQL>
    SQL>
    SQL> CREATE TABLE club (Name VARCHAR2(10),
    2 Address VARCHAR2(20),
    3 City VARCHAR2(20),
    4 Phone VARCHAR2(8),
    5 Members mem_type)
    6 /

    SQL>
    SQL> DESC club;
    Name Null? Type
    -----------
    NAME VARCHAR2(10)
    ADDRESS VARCHAR2(20)
    CITY VARCHAR2(20)
    PHONE VARCHAR2(8)
    MEMBERS MEM_TYPE

    SQL>
    SQL> drop table club;

    Table dropped.

    SQL> drop type mem_type;

    Type dropped.




    Loading a Table with a VARRAY in It: INSERT VALUEs with Constants

    Oracle's VARRAYs behave like classes in object-oriented programming. Classes are instantiated into objects using constructors.

    SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
    2 /

    Type created.

    SQL>
    SQL> CREATE TABLE club (Name VARCHAR2(10),
    2 Address VARCHAR2(20),
    3 City VARCHAR2(20),
    4 Phone VARCHAR2(8),
    5 Members mem_type)
    6 /

    Table created.

    SQL>
    SQL> INSERT INTO club VALUES ('AL','111 First St.','Mobile',
    2 '222-2222', mem_type('Brenda','Richard'));

    1 row created.

    SQL>
    SQL> INSERT INTO club VALUES ('FL','222 Second St.','Orlando',
    2 '333-3333', mem_type('Gen','John','Steph','JJ'));

    1 row created.

    SQL>
    SQL> SELECT *
    2 FROM club;

    NAME ADDRESS CITY PHONE
    ---------- -------------------------------------------------- -------------------- --------
    MEMBERS
    -------------------------------------------------------------------------------------------
    AL 111 First St. Mobile 222-2222
    MEM_TYPE('Brenda', 'Richard')

    FL 222 Second St. Orlando 333-3333
    MEM_TYPE('Gen', 'John', 'Steph', 'JJ')


    SQL>
    SQL>
    SQL>
    SQL> drop table club;

    Table dropped.

    SQL> drop type mem_type;

    Type dropped.
    to be continued.........................
    Oracle Collections
    Purpose

    The goal of this article is to show the principal features about the collections.

    We will see how to declare, initialize and handle collection with SQL and PL/SQL.

    All the examples have been runned on a 10.1.0.2.0 database release.

    Definition

    This is what the documentation says about collections:

    “A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

    PL/SQL offers these collection types:

    • Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)

    • Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

    • Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.


    Although collections can have only one dimension, you can model multi-dimensional arrays by creating collections whose elements are also collections.

    To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters, to move data between client-side applications and stored subprograms.
    To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.”


    Nested tables

    * A nested table is an unordered set of any number of elements, all of the same data type.

    * A nested table has a single column.


    * The type of that column may be a built-in database type or an object type.

    * If the column in a nested table is an object type, the table can also be viewed as a multicolumn table, with a column for each attribute of the object type.


    * You can insert, update, and delete individual elements in a nested table.

    Declaration

    declare
    type [nested table]is table of[element type] ;
    ...

    create or replace type [nested table]is table of[element type] ;



    CREATE OR REPLACE PACKAGE aa_types
    2 IS
    3 TYPE boolean_aat IS TABLE OF BOOLEAN
    4 INDEX BY BINARY_INTEGER;
    5
    6 TYPE date_aat IS TABLE OF DATE
    7 INDEX BY BINARY_INTEGER;
    8
    9 TYPE integer_aat IS TABLE OF INTEGER
    10 INDEX BY BINARY_INTEGER;
    11
    12 TYPE pls_integer_aat IS TABLE OF PLS_INTEGER
    13 INDEX BY BINARY_INTEGER;
    14
    15 TYPE binary_integer_aat IS TABLE OF BINARY_INTEGER
    16 INDEX BY BINARY_INTEGER;
    17
    18 TYPE natural_aat IS TABLE OF NATURAL
    19 INDEX BY BINARY_INTEGER;
    20
    21 TYPE positive_aat IS TABLE OF POSITIVE
    22 INDEX BY BINARY_INTEGER;
    23
    24 TYPE number_aat IS TABLE OF NUMBER
    25 INDEX BY BINARY_INTEGER;
    26
    27 TYPE maxdbvarchar2_aat IS TABLE OF VARCHAR2(4000)
    28 INDEX BY BINARY_INTEGER;
    29
    30 TYPE maxvarchar2_aat IS TABLE OF VARCHAR2(32767)
    31 INDEX BY BINARY_INTEGER;
    32
    33 TYPE identifier_aat IS TABLE OF VARCHAR2(30)
    34 INDEX BY BINARY_INTEGER;
    35
    36 TYPE utl_file_aat IS TABLE OF UTL_FILE.FILE_TYPE
    37 INDEX BY BINARY_INTEGER;
    38
    39 TYPE xml_aat IS TABLE OF XML_TYPE
    40 INDEX BY BINARY_INTEGER;
    41
    42 TYPE clob_aat IS TABLE OF CLOB
    43 INDEX BY BINARY_INTEGER;
    44
    45 TYPE blob_aat IS TABLE OF BLOB
    46 INDEX BY BINARY_INTEGER;
    47 END aa_types;
    48 /

    Warning: Package created with compilation errors.

    SQL>
    SQL> show errors
    Errors for PACKAGE AA_TYPES:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    36/4 PL/SQL: Declaration ignored
    36/34 PLS-00201: identifier 'UTL_FILE' must be declared
    39/4 PL/SQL: Declaration ignored
    39/29 PLS-00201: identifier 'XML_TYPE' must be declared


    Creating a Nested Table Type

    You create a nested table type using the CREATE TYPE statement:

    If you don't specify the maximum size of a nested table, you can insert any number of elements in a nested table.



    SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
    2 street VARCHAR2(15),
    3 city VARCHAR2(15),
    4 state CHAR(2),
    5 zip VARCHAR2(5)
    6 );
    7 /


    SQL>
    SQL> CREATE Or Replace TYPE nested_table_AddressType AS TABLE OF AddressType;
    2 /

    Type created.