sql notes

Topics: SQL, Relational model, Database Pages: 12 (1516 words) Published: December 12, 2013
Table of Contents

Introduction 1

Target audience 1

Collections 1

Use of Collections1


Difference between PL/SQL tables and Varrays2

Varray Manipulation2

Creation of Varray2

PL/SQL Tables4

Transferring Database Information to PL/SQL Tables5

Nested Table Collections8


This document is prepared to give a detailed briefing on how collections could be implemented in Oracle database programming through PL/SQL. This has been illustrated with examples as well. This should prove handy for beginners as well as experienced hands in Oracle programming.

Target audience

The document assumes that the audience has basic knowledge in PL/SQL.


ollection is an ordered group of elements. Each element has a unique subscript that determines its position in the collection.

Use of Collections

You can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.


Oracle Collections can have only one dimension and must be indexed (subscript) by integers.

Oracle Collections can store instances of an object type and, conversely, can be attributes of an object type. Also, collections can be passed as parameters.

PL/SQL offers three collections as below:

Varrays (short for variable-size arrays).

PL/SQL tables (used to be known as index-by tables)

Nested tables (which extend the functionality of index-by tables).

Difference between PL/SQL tables and Varrays

Varray is set of values of same datatype whereas tables can store values of different datatypes

PL/SQL tables has no upper limit where as arrays has.

PL/SQL tables are not stored in database where as varrays are stored in database.

We can use negative index for PL/SQL tables. In varrays negative index is not allowed.

In PL/SQL tables data need not be stored in consecutive rows. Varrays are dense and retain their order while storing in database.

You cannot perform DML operations on PL/SQL table. DML operations can be performed on Varrays.

Varray Manipulation

Creation of Varray

Syntax of VARRAY database object creation is:


For the varray examples below, let us create a varray and a table to hold the varray object as below:

CREATE TYPE my_varray_type AS VARRAY (10) OF NUMBER;

--Create a table with a column to hold VARRAY data

CREATE TABLE my_table (my_column my_varray_type);

Example 1

The following examples shows how a VARRAY could be initialized and stored into database table.

local_varray my_varray_type := my_varray_type ();
local_varray.EXTEND (10);

INSERT INTO my_table
VALUES (local_varray);

Now the content of my_column could be verified as below:

SQL> select * from my_table;

----------------------------------------------------------------------- MY_VARRAY_TYPE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

Example 2

Following example shows how varray could be assigned values explicitly.

local_varray my_varray_type := my_varray_type ();
local_varray.EXTEND (10);
local_varray (1) := 1;
local_varray (2) := 2;
local_varray (3) := 3;
local_varray (4) := 4;
local_varray (5) := 5;
local_varray (6) := 6;
local_varray (7) := 7;
local_varray (8) := 8;
local_varray (9) := 9;

Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • SQL vs. Oracle Essay
  • Pl/Sql Programs Research Paper
  • Sql Introduction to Database Essay
  • SQL-an analysis Essay
  • Essay about Sql Server
  • Sql Research Paper
  • Essay about Sql Injection
  • Database Sql Essay

Become a StudyMode Member

Sign Up - It's Free