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.


51 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

Brave Technologies said...

Excellent post. Thanks for sharing.
erp in chennai | erp software providers in chennai

Evelin Harriet said...

Very helpful post.Thanks for your information! Keep sharing.
erp software in chennai | erp providers in chennai

oracle fusion procurement online training said...

Thank you for posting this blog I, in fact, cherished it and located up some precise blogs about oracle........go to our net internet page related to oracle fusion procurement all modules education
Thanks for sharing this form of useful information,
Oracle Fusion procurement Training
Oracle Fusion Cloud procurement Training

Anonymous said...

This is a great inspiring article.I am pretty much pleased with your good work. You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post fashion handbags uk

janu3vj said...

Thanks for the awesome share
We at Colan Infotech Private Limited best web design company in chennai,is Situated in US and India,
will provide you best service in qa testing services
Design Services and Colan Infotech has a group of exceedingly dedicated, inventive and creative experts with an energy for delivering exciting ,
helpful and stylish Web and Mobile Applications, We are one of the best software testing services company
and of course we stepped in bangalore too we are best qa and testing services
can provide quality assurance and testing services,
we are the best among the software testing company india

Anonymous said...

This is a good post. This post give truly quality information. I’m definitely going to look into it. Really very useful tips are provided here. thank you so much. Keep up the good works. Low OBL dofollow Blog Comments

rupiya dani said...

I have good experience to read your blog, Your posting wonderful post... Web Designing Training Institute in Chennai | Web Designing Training Institute in Velachery.

italian designers brands said...

Very nice post. Full of information and to the point.

Anonymous said...

Thanks for a very interesting blog. What else may I get that kind of info written in such a perfect approach? I’ve a undertaking that I am simply now operating on, and I have been at the look out for such info High Trust Flow Dofollow Blog Comments

Krishna Veni said...


Nice and good article.. it is very useful for me to learn and understand easily.. thanks for sharing your valuable information and time.. please keep updating.

Java Training in chennai

Java Training institute in chennai

venkatesh raghunath said...

Good and nice information, thanks for sharing your views and ideas.. keep rocks and updating

Software Testing Training in chennai |
Android Training in chennai

unknown said...

The Spring Framework is a lightweight framework for developing Java enterprise applications. It provides high performing, easily testable and reusable code. Spring handles the infrastructure as the underlying framework so that you can focus on your application.Spring is modular in design, thereby making creation, handling and linking of individual components so much easier.
spring mvc validation example

unknown said...

Java is high level programming language .Due to its stability and scalability, you can find Java on mobiles, desktops, large scale applications etc. Java is also gaining big in the field of Internet of Things (IoT).
Java training center in chennai

mallika said...

Informative post. keep posting regularly...Java Training Institute in Chennai | Java Training Institute in Velachery.

PLC SCADA Training in Chennai said...

Thanks for posting and keep updating

PLC Training in Chennai | PLC Training Institute in Chennai | PLC Training Center in Chennai | PLC SCADA Training in Chennai | Inplant Training in Chennai

sukere said...

Really an amazing post..! By reading your blog post i gained more information.
Social Media Marketing

Deshma Teja said...

This information is impressive..I am inspired with your post writing style & how continuously you describe this topic.
No.1 Big Data Project Center in Chennai | No.1 Cloud Computing Project Center in Chennai | No.1 Dot Net Project Center in Chennai

PLC SCADA Training in Chennai said...

PLC Training in Chennai | PLC Training Institute in Chennai | PLC Training Center in Chennai | PLC SCADA Training in Chennai | Inplant Training in Chennai | PLC Training in Kerala

Embedded System Training in Chennai said...

Best Embedded System Training in Chennai | Embedded Course in Chennai | Embedded Training in Chennai | Best Embedded Training in Chennai | Embedded Systems Course in Chennai | Matlab Training in Chennai | LabVIEW Training in Chennai | VLSI Training in Chennai

PLC Training Institute in Chennai said...

PLC Training in Chennai | PLC Training in Chennai | Embedded System Training in Chennai | VLSI Training in Chennai | Inplant Training in Chennai

PLC Training in Chennai said...

Inplant Training for in Chennai | Inplant Training for in Chennai For ECE | Inplant Training in Chennai For EEE | Inplant Training in Chennai For EIE | Inplant Training in Chennai For IT Students | Inplant Training in Chennai For CSE Students | Inplant Training in Chennai For Mechanical Engineering Students | Inplant Training in Chennai For CIVIL Enginnering Students | Inplant Training For MECHATRONICS

PLC Training in Aluva said...


PLC Training in Kerala | PLC Training Institute in Kerala | AutoCAD Training Institute in Kerala | Inplant Training in Kerala

ALINAAMEL said...

Thanks For Your valuable posting, it was very informative
Webdesign Lüdenscheid

subash said...
This comment has been removed by the author.
Fuzzy International said...

The best thing is that your blog really informative thanks for your great information!
cashew nuts suppliers and exporters in dubai
A4 paper suppliers and exporters in dubai
onion suppliers and exporters in dubai
potato suppliers and exporters in dubai
spices&grains suppliers and exporters in dubai

annika romy said...

Informative and impressive. Keep Updating
office 2016 professional plus deutsch download

ALINAAMEL said...

Interesting post! This is really helpful for me. I like it! Thanks for sharing!

suchmaschinenmarketing lüdenscheid

ALINAAMEL said...


thanks for the useful post
Webdesign Lüdenscheid