example of varray in oracle pl sql

declare
Type varray_demo is varray(30) of emp.ename%type;
demovarray varray_demo;
begin
demovarray:=varray_demo('hari','raju','nari','madhu','lalitha');
end;

create type address is varray(30) of varchar2(40);
create type location is varray(30) of varchar2(40);
create type Dname is varray(30) of varchar2(40);

alter table emp add address address;
alter table emp add Loc location;
alter table emp add Dname Dname;

insert into emp values(110,'user1',15000,20,20,address('sargar society','roadno 2','Banjarahills'),location('HYD','Vijag'),Dname('accounts','software'));

select * from emp where 'software' not in (select * from table(emp.dname))
select * from emp where 'software' in (select * from table(emp.dname))

update emp
set address=address('NRP','roadno 2','KPHB')
where deptno=40

drop type address
drop type location
drop type Dname

Varrays:
=========

 The Varray is short for Variable Array. A Varray stores elements of the same type in the order
 in which they are added. The number of elements in a Varray must be known at the time of its declaration.
 In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types
 from other programming languages. Once it is created and populated, each element can be accessed
 by a numeric index.



DECLARE
    TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
    Fiction_genres genres;
BEGIN
    fiction_genres := genres('MYSTERY','SUSPENSE', 'ROMANCE','HORROR');
END;


The following code sample demonstrates how to use a few of these methods.
We are using a Varray in the example, but the methods function similarly on all collection types.
 We mentioned that a Varray differs from Nested Tables and Associative Arrays in that you must supply a size during its declaration.
This example usese the EXTENDS method to demonstrate that it is possible to modify a Varray's size programmatically.

--Add a new genre.
IF adding_new_genre  THEN

     --Is this genre id already in the collection?
     IF NOT fiction_genres.EXISTS(v_genre_id)     THEN
       --**Add** another element to the varray.
       fiction_genres.EXTENDS(1);
       fiction_genres(v_genre_id) := v_genre;
   END IF;
    --Display the total # of elements.
    DBMS_OUTPUT.PUT_LINE('Total # of entries in fiction_genres is :
                         '||fiction_genres.COUNT();

END IF;
...
...
--Remove all entries.
IF deleting_all_genres THEN
     Fiction_genres.DELETE();
END IF;



The advantage that Varrays (and Nested Tables) have over Associative Arrays is their ability to be added to the database.
 For example, you could add the genres type, a Varray, to a DML statement on the library table.

CREATE TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
/
CREATE TABLE book_library (
    library_id    NUMBER,
    name          VARCHAR2(30),
    book_genres   genres);
/


When a new library record is added, we can supply values to our genres type, book_genres, by using its constructor:


--Insert a new collection into the column on our book_library table.
INSERT INTO book_library (library_id, name, book_genres)
  VALUES (book_library_seq.NEXTVAL,'Brand New Library',
          Genres('FICTION','NON-FICTION', 'HISTORY',
                 'BUSINESS AND FINANCE'));


SELECT name, book_genres from book_library;



Ur's
AmarAlam