Read Using SQL Snippets ™ before using any of this site's sample code or techniques on your own systems.
|
PL/SQL Collections
Setup
Run the code on this page in SQL*Plus to create the sample tables, data, etc. used by the examples in this section.
Be sure to read
Using SQL Snippets ™
before executing any of these setup steps.
create table t ( val varchar2(5) );
insert into t values ( 'A' );
insert into t values ( 'B' );
insert into t values ( 'C' );
insert into t values ( 'D' );
insert into t values ( 'E' );
insert into t values ( 'F' );
insert into t values ( 'G' );
commit;
create package p as
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
procedure print( p1 in varchar2 ) ;
procedure print( p1 in aa_type ) ;
procedure print( p1 in nt_type ) ;
procedure print( p1 in va_type ) ;
function nvl3( p1 in pls_integer ) return varchar2 ;
function tf( p1 in boolean ) return varchar2 ;
end;
/
show errors
create package body p as
procedure print( p1 varchar2 ) is begin dbms_output.put_line(p1); end;
procedure print( p1 in aa_type ) is
i binary_integer ;
begin
if p1 is null then dbms_output.put_line( 'NULL' ); return; end if;
if p1.count = 0 then dbms_output.put_line( 'NOT NULL and empty' ); end if;
i := p1.first;
while i is not null
loop
dbms_output.put_line( '(' || i || ') ' || p1(i) );
i := p1.next(i) ;
end loop;
print( '.first = ' || nvl3(p1.first) );
print( '.last = ' || nvl3(p1.last) );
print( '.count = ' || nvl3(p1.count) );
print( '.limit = ' || nvl3(p1.limit) );
end;
procedure print( p1 in nt_type ) is
i binary_integer ;
begin
if p1 is null then dbms_output.put_line( 'NULL' ); return; end if;
if p1.count = 0 then dbms_output.put_line( 'NOT NULL and empty' ); end if;
i := p1.first;
while i is not null
loop
dbms_output.put_line( '(' || i || ') ' || p1(i) );
i := p1.next(i) ;
end loop;
print( '.first = ' || nvl3(p1.first) );
print( '.last = ' || nvl3(p1.last) );
print( '.count = ' || nvl3(p1.count) );
print( '.limit = ' || nvl3(p1.limit) );
end;
procedure print( p1 in va_type ) is
begin
if p1 is null then dbms_output.put_line( 'NULL' ); return; end if;
if p1.count = 0 then dbms_output.put_line( 'NOT NULL and empty' ); end if;
for i in nvl(p1.first,0) .. nvl(p1.last,-1) loop
dbms_output.put_line( '(' || i || ') ' || p1(i) );
end loop;
print( '.first = ' || nvl3(p1.first) );
print( '.last = ' || nvl3(p1.last) );
print( '.count = ' || nvl3(p1.count) );
print( '.limit = ' || nvl3(p1.limit) );
end;
function nvl3( p1 in pls_integer ) return varchar2 is
begin return( nvl( to_char(p1), 'NULL' ) ); end;
function tf( p1 in boolean ) return varchar2 is
begin if p1 then return('TRUE'); else return('FALSE'); end if; end;
end;
/
show errors
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-12304.html]SQL Snippets: PL/SQL Collections - Setup[/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-12304.html">SQL Snippets: PL/SQL Collections - Setup</a>
To link to this page in other web sites use the following values.
- Link Text : SQL Snippets: PL/SQL Collections - Setup
- URL (href): http://www.sqlsnippets.com/en/topic-12304.html
|