advantages of composite datatypes in oracle

by jagadeesh
(bangalore)

Whhat are the advantages of composite datatypes in oracle?

Comments for advantages of composite datatypes in oracle

Average Rating starstarstarstarstar

Click here to add your own comments

Nov 04, 2016
Rating
starstarstarstarstar
PL/SQL composite data types
by: Anonymous

Before we can answer this we first have to understand what a composite data type is. The easiest way to do that is to start with the Oracle PL/SQL built-in data types as composite data types only apply to PL/SQL.

The built-in data types then are those that are part of SQL:
  • NUMBER/FLOAT/BINARY_FLOAT
  • CHAR/NCHAR
  • VARCHAR2/NVARCHAR2
  • LONG/RAW/LONGRAW
  • BLOB/CLOB/BFILEDATE/TIMESTAMP
(and a few more see Oracle Database SQL Language Reference for the full complement.)

Plus the PL/SQL only ones:
  • BOOLEAN
  • PLS_INTEGER
  • BINARY_INTEGR
  • REF CURSOR

The composite data types are user-defined data types comprising 2 or more of the built-in data types.

The method of defining composite data types in Pl/SQL is via the record structure and PL/SQL collections. For example the following snippet of code defines a record type to hold information about a book:

DECLARE

   TYPE book_typ IS

   RECORD

   (

      isbn          VARCHAR2(20)

      ,title        VARCHAR2(2000)

      ,author       VARCHAR2(2000)

      ,publisher    VARCHAR2(120) 

      ,published_on DATE

   )


Not this just defines the type (the data structure) not a variable. Having defined the type you can define as many variables of that type as needed. For example:

a_book book_typ;
book_2 book_typ;
book_3 book_typ;


Each variable is a data structure built of the 5 elements that we defined in the type declaration and holds information about one book.

You're probably thinking that this is not very practical - what if you need to store or process information about a hundred books? You'd have to declare a hundred variables. And how would you handle an unknown number of books? That's where collections come in.

TYPE books_tab IS TABLE OF book_typ;

This time we've defined a PL/SQL collection type (a PL/SQL table of our book_typ type).

Having defined the collection type we can now define a variable of that type.

books books_tab;

Now we can store information about an infinite number of books (subject to memory constraints).

We can build much more elaborate composite data types using collections and the record structure.

Let's suppose we want to store a list of contacts including the person's first and last name and 3 phone numbers for each person (office, home and mobile/cell). First we need to define a data structure to hold the set of phone numbers, as follows:

TYPE phone_no_set is VARRAY(3) of PLS_INTEGER;


Then we need to combine that into a record type to hold the other information.

TYPE person IS RECORD (
first_name VARCHAR2(30);
last_name VARCHAR2(60);
phone phone_no_set;
)


Now we can declare a PL/SQL collection so that we can store details of more than one person.

TYPE contacts TABLE OF person INDEX BY VARCHAR2(30);


Finally we can declare a variable to hold the information.

my_contacts contacts;


In other words the short answer to the question is that without composite data types our PL/SQL applications would be very limited, which means that their advantage is that they enable us to build complex data types which we can use to model the real world much more effectively than if we just had simple data types.

See our collection of PL/SQL tutorials for more help and for further examples of using collections and records.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.