SQL Snippets ™: Tutorials for Oracle Developers

Custom Search
Home   SQL Features   SQL Techniques   Miscellany   About   Blog   Site Map   Search  
Home
SQL Features
PL/SQL Collections
 
 
Basic Operations
 
 
 
< Previous   Next >

PL/SQL Collections

Basic Operations

This tutorial demonstrates how to perform basic operations with each of the three available PL/SQL collection types. The examples span the entire lifecycle of a collection from instantiation through to deletion.

Test Data

The sample code on this page uses a package called "P", the source code for which is available in the Setup topic for this section. Three different collection type definitions from this package will be used. 

  type aa_type is table of varchar2(5) index by binary_integer ; -- Associative Array

  type nt_type is table of varchar2(5) ;                         -- Nested Table

  type va_type is varray(10) of varchar2(5) ;                    -- Varray
 

Sample Code

Associative Array
Nested Table
Varray
 
declare
 
Declare a collection variable. 
aa_0 p.aa_type ;
 
 
nt_0 p.nt_type ;
 
 
va_0 p.va_type ;
 
Declare, initialize, and load a collection variable. 
aa p.aa_type ;

-- cannot load values in
-- declaration

 
 
nt p.nt_type :=
  p.nt_type( 'a', 'b' )
;


 
 
va p.va_type :=
  p.va_type( 'a', 'b' )
;


 
 
begin
 
Let's inspect the variables to see what they look like at this point (NULL means the variable is not initialized). 
p.print( 'aa_0 is ' );
p.print( aa_0       );
p.print( ' '        );
p.print( 'aa is '   );
p.print( aa         );
 
 
p.print( 'nt_0 is ' );
p.print( nt_0       );
p.print( ' '        );
p.print( 'nt is '   );
p.print( nt         );
 
 
p.print( 'va_0 is ' );
p.print( va_0       );
p.print( ' '        );
p.print( 'va is '   );
p.print( va         );
 
 
aa_0 is
NOT NULL and empty
.first = NULL
.last  = NULL
.count = 0
.limit = NULL

aa is
NOT NULL and empty
.first = NULL
.last  = NULL
.count = 0
.limit = NULL
 
 
nt_0 is
NULL

nt is
(1) a
(2) b
.first = 1
.last  = 2
.count = 2
.limit = NULL
 
 
va_0 is
NULL

va is
(1) a
(2) b
.first = 1
.last  = 2
.count = 2
.limit = 10
 
Initialize a collection after it has been declared. 
-- n/a
 
 
nt_0 := p.nt_type() ;

p.print( nt_0 );
 
 
va_0 := p.va_type();

p.print( va_0 );
 
 
 
 
NOT NULL and empty
.first = NULL
.last  = NULL
.count = 0
.limit = NULL
 
 
NOT NULL and empty
.first = NULL
.last  = NULL
.count = 0
.limit = 10
 
Add individual rows to a collection. 
-- add 1 row at a time

aa(1) := 'a' ;
aa(2) := 'b' ;
aa(3) := 'c' ;



aa(4) := 'd' ;
aa(5) := 'e' ;




aa(6) := 'e' ;
aa(7) := 'e' ;

p.print( aa );
 
 
-- add 1 row

nt.extend ;
nt(3) := 'c' ;

-- add 2 rows

nt.extend(2) ;
nt(4) := 'd' ;
nt(5) := 'e' ;

-- create two copies
-- of row #5

nt.extend(2,5) ;


p.print( nt );
 
 
-- add 1 row

va.extend ;
va(3) := 'c' ;

-- add 2 rows

va.extend(2) ;
va(4) := 'd' ;
va(5) := 'e' ;

-- create two copies
-- of row #5

va.extend(2,5) ;


p.print( va );
 
 
(1) a
(2) b
(3) c
(4) d
(5) e
(6) e
(7) e
.first = 1
.last  = 7
.count = 7
.limit = NULL
 
 
(1) a
(2) b
(3) c
(4) d
(5) e
(6) e
(7) e
.first = 1
.last  = 7
.count = 7
.limit = NULL
 
 
(1) a
(2) b
(3) c
(4) d
(5) e
(6) e
(7) e
.first = 1
.last  = 7
.count = 7
.limit = 10
 
Load a single value from the database into a collection row. 
select val
into   aa(2)
from   t
where  val = 'B' ;

p.print( aa );
 
 
select val
into   nt(2)
from   t
where  val = 'B' ;

p.print( nt );
 
 
select val
into   va(2)
from   t
where  val = 'B' ;

p.print( va );
 
 
(1) a
(2) B
(3) c
(4) d
(5) e
(6) e
(7) e
.first = 1
.last  = 7
.count = 7
.limit = NULL
 
 
(1) a
(2) B
(3) c
(4) d
(5) e
(6) e
(7) e
.first = 1
.last  = 7
.count = 7
.limit = NULL
 
 
(1) a
(2) B
(3) c
(4) d
(5) e
(6) e
(7) e
.first = 1
.last  = 7
.count = 7
.limit = 10
 
Initialize a collection and load it with multiple database values (pre-existing contents will be lost). 
select val
bulk collect into aa
from  t ;

p.print( aa );
 
 
select val
bulk collect into nt
from  t ;

p.print( nt );
 
 
select val
bulk collect into va
from  t ;

p.print( va );
 
 
(1) A
(2) B
(3) C
(4) D
(5) E
(6) F
(7) G
.first = 1
.last  = 7
.count = 7
.limit = NULL
 
 
(1) A
(2) B
(3) C
(4) D
(5) E
(6) F
(7) G
.first = 1
.last  = 7
.count = 7
.limit = NULL
 
 
(1) A
(2) B
(3) C
(4) D
(5) E
(6) F
(7) G
.first = 1
.last  = 7
.count = 7
.limit = 10
 
Test a row's existence by subscript. 
p.print
( 'aa.exists(3) is '||
  p.tf( aa.exists(3) )
);

p.print
( 'aa.exists(9) is '||
  p.tf( aa.exists(9) )
);
 
 
p.print
( 'nt.exists(3) is '||
  p.tf( nt.exists(3) )
);

p.print
( 'nt.exists(9) is '||
  p.tf( nt.exists(9) )
);
 
 
p.print
( 'va.exists(3) is '||
  p.tf( va.exists(3) )
);

p.print
( 'va.exists(9) is '||
  p.tf( va.exists(9) )
);
 
 
aa.exists(3) is TRUE
aa.exists(9) is FALSE
 
 
nt.exists(3) is TRUE
nt.exists(9) is FALSE
 
 
va.exists(3) is TRUE
va.exists(9) is FALSE
 
Test a row's existence by content. 
-- use a loop (see below)
 
 
p.print
( '''C'' member of nt is '||
  p.tf( 'C' member of nt )
);

p.print
( '''X'' member of nt is '||
  p.tf( 'X' member of nt )
);

 
 
-- use a loop (see below)
 
 
 
 
'C' member of nt is TRUE
'X' member of nt is FALSE
 
 
 
Compare two collections for equality. 
-- cannot use "=" with
-- two associative arrays
 
 
nt_0 := nt ;

if nt_0 = nt then
  p.print( 'equal' );
else
  p.print( 'not equal' );
end if;
 
 
-- cannot use "=" with
-- two varrays
 
 
 
 
equal
 
 
 
Update a collection row. 
aa(1) := 'a' ;
aa(3) := 'c' ;

p.print( aa );
 
 
nt(1) := 'a' ;
nt(3) := 'c' ;

p.print( nt );
 
 
va(1) := 'a' ;
va(3) := 'c' ;

p.print( va );
 
 
(1) a
(2) B
(3) c
(4) D
(5) E
(6) F
(7) G
.first = 1
.last  = 7
.count = 7
.limit = NULL
 
 
(1) a
(2) B
(3) c
(4) D
(5) E
(6) F
(7) G
.first = 1
.last  = 7
.count = 7
.limit = NULL
 
 
(1) a
(2) B
(3) c
(4) D
(5) E
(6) F
(7) G
.first = 1
.last  = 7
.count = 7
.limit = 10
 
Remove rows from the middle of a collection. 
aa.delete(2);
aa.delete(3,4);

p.print( aa );
 
 
nt.delete(2);
nt.delete(3,4);

p.print( nt );
 
 
-- not possible


p.print( va );
 
 
(1) a
(5) E
(6) F
(7) G
.first = 4
.last  = 7
.count = 4
.limit = NULL
 
 
(1) a
(5) E
(6) F
(7) G
.first = 4
.last  = 7
.count = 4
.limit = NULL
 
 
(1) a
(2) B
(3) c
(4) D
(5) E
(6) F
(7) G
.first = 1
.last  = 7
.count = 7
.limit = 10
 
Loop through all rows in the collection.
declare
  i binary_integer ;
begin
  i := aa.first ;
  while i is not null
  loop
    p.print
      ( i ||'. '|| aa(i) );
    i := aa.next(i) ;
  end loop;
end;
 
 
declare
  i binary_integer ;
begin
  i := nt.first ;
  while i is not null
  loop
    p.print
      ( i ||'. '|| nt(i) );
    i := nt.next(i) ;
  end loop;
end;
 
 
for i in
  nvl(va.first,0) ..
  nvl(va.last,-1)
loop
  p.print
    ( i ||'. '|| va(i) );
end loop;
 
 
1. a
5. E
6. F
7. G
 
 
1. a
5. E
6. F
7. G
 
 
1. a
2. B
3. c
4. D
5. E
6. F
7. G
 
Remove row(s) from the end of a collection. 
aa.delete(7);
aa.delete(5,6);

p.print( aa );
 
 
nt.trim;
nt.trim(2);

p.print( nt );
 
 
va.trim;
va.trim(2);

p.print( va );
 
 
(4) D
.first = 4
.last  = 4
.count = 1
.limit = NULL
 
 
(4) D
.first = 4
.last  = 4
.count = 1
.limit = NULL
 
 
(1) a
(2) B
(3) c
(4) D
.first = 1
.last  = 4
.count = 4
.limit = 10
 
Reuse rows left vacant by earlier delete operations (rows 2,3,4) and trim operations (rows 5,6,7). 





aa(2) := 'B' ;
aa(3) := 'C' ;
aa(4) := 'D' ;







aa(5) := 'E' ;
aa(6) := 'F' ;
aa(7) := 'G' ;

p.print( aa );
 
 
-- note we do not need to
-- call ".extend" for rows
-- 2,3,4 which were
-- removed with ".delete"

nt(2) := 'B' ;
nt(3) := 'C' ;
nt(4) := 'D' ;

-- we do need to call
-- ".extend" for rows 5,6,7
-- which were removed with
-- ".trim"

nt.extend(3) ;
nt(5) := 'E' ;
nt(6) := 'F' ;
nt(7) := 'G' ;

p.print( nt );
 
 









-- we need to call
-- ".extend" first
-- since 5,6,7 were
-- removed with ".trim"

va.extend(3) ;
va(5) := 'E' ;
va(6) := 'F' ;
va(7) := 'G' ;

p.print( va );
 
 
(1) A
(2) B
(3) C
(4) D
(5) E
(6) F
(7) G
.first = 1
.last  = 7
.count = 7
.limit = NULL
 
 
(1) A
(2) B
(3) C
(4) D
(5) E
(6) F
(7) G
.first = 1
.last  = 7
.count = 7
.limit = NULL
 
 
(1) a
(2) B
(3) c
(4) D
(5) E
(6) F
(7) G
.first = 1
.last  = 7
.count = 7
.limit = 10
 
Delete all rows in the collection (frees memory too).
aa.delete ;

p.print( aa );
 
 
nt.delete ;

p.print( nt );
 
 
va.delete ;

p.print( va );
 
 
NOT NULL and empty
.first = NULL
.last  = NULL
.count = 0
.limit = NULL
 
 
NOT NULL and empty
.first = NULL
.last  = NULL
.count = 0
.limit = NULL
 
 
NOT NULL and empty
.first = NULL
.last  = NULL
.count = 0
.limit = 10
 
Set a collection to NULL, i.e. uninitialized state.
-- not possible
 
 
-- "nt := null" will not
-- work; use a  null
-- variable instead

declare
  nt_null p.nt_type ;
begin
  nt := nt_null ;
end;

p.print( nt );
 
 
-- "va := null" will not
-- work; use a  null
-- variable instead

declare
  va_null p.va_type ;
begin
  va := va_null ;
end;

p.print( va );
 
 
 
 
NULL
 
 
NULL
 
 
end;
/
 
<  Choosing the Right Collection Type    Gotchas  >



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12310.html]SQL Snippets: PL/SQL Collections - Basic Operations[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12310.html">SQL Snippets: PL/SQL Collections - Basic Operations</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: PL/SQL Collections - Basic Operations
  • URL (href): http://www.sqlsnippets.com/en/topic-12310.html

Revision Notes

Date Category Note
2007-05-06 Revision Added MEMBER OF example.

Terms and Conditions of Use    Disclaimer    Privacy Policy

This page was first published on May 31, 2007.

© Copyright Joseph Fuda 2006-2018. All rights reserved.

The views expressed on this site are my own and do not necessarily reflect the views of Oracle.