Home » SQL & PL/SQL » SQL & PL/SQL » collection and records(basic documents) (oracle 9i)
collection and records(basic documents) [message #429305] Tue, 03 November 2009 03:14 Go to next message
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 #429306 is a reply to message #429305] Tue, 03 November 2009 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference, Chapter 5 PL/SQL Collections and Records
Application Developer's Guide - Object-Relational Features

Regards
Michel

Re: collection and records(basic documents) [message #429309 is a reply to message #429306] Tue, 03 November 2009 03:27 Go to previous messageGo to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
hi michel
thanks for your immediate reply
i want to download this two links
how its possible to download this 2 link
Re: collection and records(basic documents) [message #429313 is a reply to message #429309] Tue, 03 November 2009 03:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you go here you can get all of the 10g boooks as PDF files.
Re: collection and records(basic documents) [message #429322 is a reply to message #429309] Tue, 03 November 2009 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From the links I posted, click on "Home" link and then on "List of books".

Regards
Michel
Re: collection and records(basic documents) [message #429366 is a reply to message #429305] Tue, 03 November 2009 07:09 Go to previous messageGo to next message
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





Re: collection and records(basic documents) [message #429371 is a reply to message #429366] Tue, 03 November 2009 07:30 Go to previous message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And this is quoted from?

Regards
Michel
Previous Topic: How to use Type to work in a procedure
Next Topic: Calculate with sql
Goto Forum:
  


Current Time: Fri Sep 30 15:47:13 CDT 2016

Total time taken to generate the page: 0.19048 seconds