| collection and records(basic documents) [message #429305] |
Tue, 03 November 2009 03:14  |
vivek_rol
Messages: 65
Registered: February 2009
|
Member |
|
|
hi friends
will u please give me practical example of collection and records
that is varry,nested table,index by table,
since i want to use this feature in my project application
please provide basic concepts with practical example,
when should we use these features
please help
regards
vivek
|
|
|
|
|
|
|
|
|
|
|
|
| Re: collection and records(basic documents) [message #429366 is a reply to message #429305] |
Tue, 03 November 2009 07:09  |
srivalar
Messages: 1
Registered: November 2009
|
Junior Member |
|
|
PL/SQL Collections
The composite data types are known as collections and are available in 3 different forms.
1. Nested tables
2. Varrays
3. PL/SQL Associative arrays
1. Nested Tables
Single dimensional arrays which can be declared in the database as a column in another table and in PL/SQL as stand alone.
No upper or lower limits.
Constrained by the memory available.
Nested tables are densely populated when created. Each row has a value. But rows can be logically deleted, so nested tables can be sparsely populated.
Each row of the nested table must be of same type.
Declaration:
Declaration is in two types
Declare a type
TYPE <type_name> IS TABLE OF <existing_type>;
Then declare the variable of that type
<variable_name> <type_name>;
For example, suppose we want to create a table of numbers, the declarations would be
TYPE number_table_type IS TABLE OF NUMBER;
my_tab number_table_type;
(Example is written in Varray section)
2. Varrays:
Is a variable lenght, one dimentional, densely populated arrays (dense in the sense that each cell must exit, although it may be empty)
Each element must be of the same type like nested tables they can be defined in the database or in PL/SQL.
The declaration defines the size of the array which is fixed.
We cannot add new cells to or delete calls from the Varray once we have declared it.
These are designed for storing the small value of attributes.
Declaration:
TYPE <type_name> IS VARRAY <size> OF <existing_type>;
<variable_name> <type_name>;
Considering an example, the declarations would be:-
TYPE phone_no_tab_type IS VARRAY(3) OF NUMBER;
TYPE address_tab_type IS VARRAY(3) OF VARCHAR2(500);
TYPE email_addr_tab_type IS VARRAY(3) OF VARCHAR2(100);
TYPE name_tab_type IS TABLE OF VARCHAR2(100);
addresses address_tab_type;
phone_nos phone_no_tab_type;
names name_tab_type;
email_addresses email_addr_tab_type;
Note that at the moment, each of the types of data is stored separately and therefore has to be worked with separately.To put this all together we need to create another type as a record then create a nested table to hold the records as follows:-
TYPE contact_rec IS RECORD (
name VARCHAR2(100);
addrs address_tab_type;
phone_nums phone_no_tab_type;
email_addrs email_addr_tab_type;
);
TYPE contacts_tab_type IS TABLE OF contact_rec;
contacts contacts_tab_type;
3.PL/SQL Associative arrays:
Associated (ie.,indexed by)with pl/sql integers(BINARY_INTEGER)or character strings VARCHAR2.
Like nested tables associative array are unbounded, single dimensional and each cell must be of the same type (homogeneous).
Unlike nested tables, associative arrays are sparse (each cell does not have to exit).
Declaration:
TYPE <type_name> IS TABLE OF <existing_type> ;
<variable_name> <type_name>;
For example, suppose we want to create a table of numbers, the declarations would then be
TYPE number_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
my_tab number_table_type;
name VARCHAR2(100);
Considering an example:
First we declare the table type:
TYPE phone_no_tab IS TABLE OF VARCHAR2 (20) INDEX BY BINARY_INTEGER;
Next we declare a variable of that type:
phone_nos phone_no_tab;
This creates an array(table) that is available within the PL/SQL session.Inserting and retrieving the datas is same as in Nested Tables.But there is no need to initialize the array or extend it.
SQL> declare
2 TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
3 phone_nos phone_no_tab;
4 begin
5 phone_nos(1) := '+44 (0) 117 942 2508';
6 dbms_output.put_line('phone_no(1) is '||phone_nos(1));
7 end;
8 /
phone_no(1) is +44 (0) 117 942 2508
Considering an example Associative array indexed by varchar2:
SQL> declare
2 TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(30);
3 phone_nos phone_no_tab;
4 begin
5 phone_nos('office') := '+44 (0) 117 942 2508';
6 DBMS_output.put_line('phone_no(office) is '||phone_nos('office'));
7 end;
8 /
phone_no(office) is +44 (0) 117 942 2508
The functions used in collections:
COUNT: This function returns the number of elements (cells) in the collection
DELETE: This procedure with no parameters deletes all the elements in the collection,
as with nested tables, though, we can specify a start and end point to say which element(s)
is (are) to be deleted. After deleting an element or the whole collection, any subsequent attempt
to read that element in the collection generates "ORA-01403: no data found". However you can write
to any element without any errors as this re-creates the element
EXISTS(n) : used to determine if the specified element has been created and not deleted,
returns TRUE if the element exists, FALSE if not. The index variable can be either a number of
type BINARY_INTEGER or a character string of type VARCHAR2
FIRST : returns the subscript of the first element in the PLSQL associative array
LAST : returns the subscript of the last element in the PL/SQL associative array
PRIOR(n): returns the subscript of the previous element in the PL/SQL associative array
or NULL if no more elements exist
NEXT(n) : returns the subscript of the next element in the PLSQL associative array or
NULL if no more elements exist.
TRIM : Not appropriate for associative arrays - generates a compilation error
EXTEND : Not appropriate for associative arrays - generates a compilation error
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
