Friday, 28 April 2017

VARRAY in Oracle with examples

VARRAY in Oracle with examples, VARRAY in object relational databases, How to create and manipulate VARRAY data in Oracle, Examples for VARRAY



VARRAY (Varrying Array)


  • VARRAY is one of the collection data types in Oracle to store column(s) that contain multiple values. That is, an attribute of type VARRAY is a multi-valued attribute.
    • For example, assume a customer with multiple phone numbers. To store all phone numbers as part of single record, we would use VARRAY.
  • Unlike nested tables, VARRAY has a limit. In other words, we use VARRAY attributes to store limited number of entries. It has a limit like other data types NUMBER, CHAR etc.
  • We cannot access the individual VARRAY elements using array_name[1] like format. But it can be accessed in PL/SQL using array_name[] format.
  • The values that are stored under a VARRAY attribute is stored in-line, that is in the same tablespace as the other data in its row of the same table.
  • VARRAY can be created as either
    • an array of particular data type like NUMBER, CHAR, VARCHAR, etc.
    • or an array of user defined data types.
Example 1 shows the first one and example 2 shows the example of user defined type as varray.

Example 1:
The following statement will create a user defined type Phone_Arr as VARRAY to store/accept at most 20 phone numbers of type NUMBER;
CREATE OR REPLACE TYPE Phone_Arr AS VARRAY(10) OF NUMBER(10);
/

The following relational table includes an attribute Phone of VARRAY type that we declared above;
CREATE TABLE Customer
(
Cust_ID CHAR(10),
Name VARCHAR(30),
Phone Phone_Arr
)

We can insert the records into the Customer table using the type constructor function as follows [a constructor function is created when you create a type automatically];
INSERT INTO Customer VALUES (
‘C1001’, ‘Raghu’, Phone_Arr(9878676500, 9809076750)
);
INSERT INTO Customer VALUES (
‘C1002’, ‘Vels’, Phone_Arr(9978679520)
);

Here, the first insert statement inserts two phone numbers of customer ‘C1001’, while the second inserts one phone number.

Example 2:
Let us first create a type for Address as follows;
CREATE TYPE Address_Typ AS OBJECT
(
Street VARCHAR(20),
City VARCHAR(20),
Pincode NUMBER(6)
);
/

The following statement will create a user defined type Address_Arr as VARRAY to store/accept at most 3 addresses of type Address_Type (as defined above);
CREATE OR REPLACE TYPE Address_Arr AS VARRAY(3) OF Address_Typ;
/

The following relational table includes an attribute Address of VARRAY type that we created above;
CREATE TABLE Customer
(
Cust_ID CHAR(10),
Name VARCHAR(30),
Address Address_Arr
)

We can insert the records into the Customer table using the type constructor function as follows [a constructor function is created when you create a type automatically];
INSERT INTO Customer VALUES (
‘C1001’, ‘Raghu’, Address_Arr(Address_Typ(‘str1’, ‘chennai’, 602345),
Address_Typ(‘Street’, ‘Madurai’, 634589))
);
INSERT INTO Customer VALUES (
‘C1002’, ‘Vels’, Address_Arr(Address_Typ(‘MG st’, ‘Bangalore’, 502345))
);

Here, the first insert statement inserts two addresses of customer ‘C1001’, while the second inserts one address.

****************
 

Go to Collection Types page

Go to Object database systems page








No comments:

Post a Comment

Nlp and machine learning online lectures

Links to Natural Language Processing (NLP) and Data Science online lectures An introduction to Bayes theorem and Hidden Markov model ...