Tuesday, February 7, 2012

PLSQL Collections

A collection in PLSQL helps to achieve the Array kind of Programming. Its an ordered group of elements, all of the same type. In a collection an element has a unique subscript that determines its position in the collection.

PLSQL has 3 types of collections
    Index-by tables
    Varrays
    Nested Tables

Declaration: 
Nested tables
TYPE type_name IS TABLE OF element_type ;
e.g.
TYPE Books IS TABLE OF VARCHAR2(60);

Varrays
TYPE type_name IS VARRAY(size_limit) OF element_type;
e.g.
TYPE Books IS VARRAY(100) OF VARCHAR2(60);

Index-by tables
TYPE type_name IS TABLE OF element_type
   INDEX BY BINARY_INTEGER ;
  
TYPE Books IS TABLE OF VARCHAR2(60)
   INDEX BY VARCHAR2(60) ;
  
Varrays size is fixed at the time of declaration, however the size of Nested tables and Index-by tables is dynamic.


Initialization:
Nested Tables
Declare
    TYPE Books IS TABLE OF VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Varrays
Declare
    TYPE Books IS varray(3) OF VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Index by Table
Declare
    TYPE Books IS table OF VARCHAR2(60)
    inde by VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Using collections in PLSQL.
The best way to implement collections is to use them in For Loops or Bulk collects.
FORALL and BULK COLLECTS can be used for implementing collections in better way

BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ...

e.g.
DECLARE
   TYPE empntab IS TABLE OF emp.empno%TYPE;
   TYPE enameTab IS TABLE OF emp.ename%TYPE;
   empnum empntab;  -- no need to initialize
   enames enameTab;
BEGIN
   SELECT empno, ename BULK COLLECT INTO empnum, enames FROM emp;
   ...
END;

FORALL
Its used along with collections to perform SQL operations such as Insert, Updates and Deletes.
We can say its spec ail loop to perform Mass operations, a shortcut to implement FOR Loops

e.g.
DECLARE
   TYPE BOOKS IS VARRAY(10) OF VARCHAR2(60);
   var_book BOOKS := BOOKS('Book1','Book2','Book3','Book4','Book5');
BEGIN
   FORALL i IN 1..7  -- bulk-bind only part of varray
      UPDATE BOOK_TAB SET CODE = 1111+1 WHERE NAME = var_book(i);
END;

Other types
RECORD:
TYPE type_name IS RECORD (colname type1, colname2 type2);

e.g.
DECLARE
   TYPE EMPREC IS RECORD (
      EMPNUM    VARCHAR2(60),
      NAME      VARCHAR2(120));
     
      EMP1 EMPREC;
BEGIN
   ...
END;

Collection Methods:

A variety of methods/Functions exist for collections, these can be used to make the implementation
of collection more effective

    EXISTS(n) - Returns TRUE if the specified element exists.

    COUNT - Returns the number of elements in the collection.

    LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.

    FIRST - Returns the index of the first element in the collection.

    LAST - Returns the index of the last element in the collection.

    PRIOR(n) - Returns the index of the element prior to the specified element.

    NEXT(n) - Returns the index of the next element after the specified element.

    EXTEND - Appends a single NULL element to the collection.

    EXTEND(n) - Appends n NULL elements to the collection.

    EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.

    TRIM - Removes a single element from the end of the collection.

    TRIM(n) - Removes n elements from the end of the collection.

    DELETE - Removes all elements from the collection.

    DELETE(n) - Removes element n from the collection.

    DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.


23 comments:

Corporate training in chennai said...

Hi, this one can work:
select * from your_table where replace(translate(your_column,' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890','x'),'x','') is not null
Above code checks for a ascii and numbers. You can adjust the string with others characters which you like to see (exclude)
Java training in chennai | Android training in chennai

abi said...

Thanks for sharing such a great information..Its really nice and informative.
java training in Chennai

Arjun Rishi said...

Great article. Happy to visit your blog. Thanks for sharing.

web design training in chennai

Balarishi said...

Great article. Thanks for sharing such a useful post.


Android Training in Chennai

hadoop online training in hyderabad said...

Hadoop online training in hyderabad.All the basic and get the full knowledge of hadoop.
hadoop online training in hyderbad

Vigneshkumar Seeenivasan said...

very good post
Selenium Interview Questions / Best Selenium Training Center in Chennai / Best Automation Testing Training in Chennai

Regina Rj said...

Wow! very nice post. Happy to visit your blog. Thanks for sharing.

digital marketing training institute in chennai

Nivedageorge said...

This is very useful post. thanks for sharing.

SEO Training in Vadapalani

Rajapriya R said...

nice and really helpful article to everyone... thanks for sharing

selenium training in chennai | selenium training institute in chennai | Android training in chennai | android training institute in chennai

Anu Sri said...

Good post. Thanks for sharing such a useful post.

website design training in chennai

Jones Sathya said...

Professional Expert level Android Training in chennai, Android App Development
Android Training | Android App Development | Training in chennai

Rajapriya R said...

nice and useful blog to everyone... thanks for sharing

java training in chennai | java training institute in chennai | java j2ee training in chennai | java j2ee training institute in chennai

Geetha Devi said...

Very informative ..i suggest this blog to my friends..Thank you for sharing
Best Android training in chennai |

Android training in chennai |
Best Android Training institute in chennai

Rajapriya R said...

helpful article..... keep rocking

java training in chennai | java training institute in chennai | java j2ee training in chennai | java j2ee training institute in chennai

Gopi Perumal said...

Best training in chennai..SAP Training in Chennai

Jones Sathya said...

Professional Expert level Android Training in chennai, Android App Development
Android Training | Android App Development | Training in chennai

Saradha Devi said...

good one to read
Best Selenium Training Center in chennai | Best Automation Testing Training in Chennai

Jones Sathya said...

Professional Expert level Android Training in chennai, Android App Development
Android Training | Android App Development | Training in chennai

Jones Sathya said...

Thanks for posting this useful content, Good to know about new things here, Let me share this,
AngularJS Training in Chennai | AngularJS Training | Best AngularJS Training Institute in Chennai

Rajapriya R said...

nice and good one

best java training institute in chennai | best java training in chennai | best java training institute in india | core java training

Geetha Devi said...

Interesting post.
Android Training in Chennai | Best Android Training in Chennai | Best Android Training in Chennai with Placement

Training Chennai said...

Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
Regards,
JAVA Training | Multimedia Training in Chennai

Ccna Training said...

great links thanks for sharing.
CCNA Training in Pune | hadoop training in pune | java training in pune | digital marketing training in pune