A composite type is one that has components within it A variable of a composite type contains one or more scalar variables. The main composite types available in PL/SQL are:
1) RECORD: A record represents a row in a table or a
row fetched by a cursor. Records have uniquely named fields and can belong to
different data types.
To declare a PL/SQL record, one first declares a table
record type and then declare variables of that type.
For example,
DECLARE
TYPE emp_rec_type IS RECORD
(emp_no NUMBER(5) NOT NULL,
name VARCHAR2(50), dob DATE);
Once declared, one can declare records of that type:
emp_rec emp_rec_type
For example, following shows the declaration and
usage of PL/SQL records:
DECLARE
TYPE emp_rec_type IS RECORD
(emp_no NUMBER(5) NOT NULL,
name VARCHAR2(50), dob DATE);
emp_rec emp_rec_type;
BEGIN
SELECT empno, name, d_dob
INTO emp_rec
FROM employee;
END;
The following rules apply when using PL/SQL records:
i) Records of different types cannot be assigned to each
other.
ii) One cannot assign a list of values to a record by using
the assignment statement.
iii) Records cannot be tested for equality or inequality.
2) TABLE: PL/SQL tables are objects of type TABLE,
which look similar to database tables but are not quite the same. These tables
give you an array-like access to rows of data. PL/SQL tables are declare in the
declaration portion of the block. They contain one column and the primary key,
neither of which can be named. The primary key must be defined with the
BINARY_INTEGER data type.
To declare PL/SQL table, one first declare a table type and
then declare van For that type.
For example,
DECLARE
TYPE emp_table_type IS TABLE OF
VARCHAR2(30)
INDEXED BY BINARY_INTEGER;
Once the table is declared, declare variables of that type.
For example, the following declares variables using the table type defined
earlier:
emp_table emp_table_type
To reference a PL/SQL table, specify a primary key value
using the array like syntax.
For example, to reference the 18th row in the PL/SQL
table created in the previous example:
emp_table(18);
Assign values directly to a row in the PL/SQL table using:
DECLARE
TYPE emp_table_type IS TABLE OF
VARCHAR2(30)
INDEXED BY BINARY_INTEGER;
k BINARY_INTEGER : = 0;
BEGIN
FOR new_emp IN (SELECT erne name
FROM employee WHERE dept = `ADMN');
LOOP
k : = k +1;
emp_table(k) = new_emp.emp_name —
insert row into PL/SQL table
END LOOP;
END:
The following rules apply when using PL/SQL tables:
i) A loop must be used to insert values from a PL/SQL table
into a database column.
ii) A loop must be used to fetch data from a database column
in a PL/SQL code.
iii) One cannot use the DELETE command to delete the contents
of a PL/SQL table One must assign an empty table to the PL/SQL table being
deleted.
3) VARRAY: A VARRAY is a variable length array and is
a data type, which is very similar to an array in. C or Pascal. Syntactically,
a VARRAY is accessed in the same way as a nested or index-by table. However, a
VARRAY is implemented differently. Rather than being a sparse data structure
with no upper bound, elements are inserted into a VARRAY starting at index 1 ,
up to the maximum length declared in the VARRAY type. Storage for a VARRAY is
the same as a C or Pascal array, as opposed to the storage for a nested table,
which is more like a database table.
Syntax:
TYPE type_name IS {VARRAY I VARYING ARRAY) (maximum_size)
OF element_type [NOT NULL];
where, ‘type_name' is the name of the new VARRAY type,
‘maximum_size' is the integer specifying the maximum number of the elements in
the VARRAY, and `element_type' is a PL/SQL scalar, record or object type. The
'element_type' can be specified using %type as well, but cannot be BOOLEAN,
NCHAR, NCLOB, NVARCHAR2, REF CURSOR, TABLE or another VARRAY type.
4) REFERENCE
TYPE: Once
a variable of a scalar or composite type is declared in PL/SQL, the memory
storage for this variable is allocated. The variable names this storage and is
used to refer to it later in the program. However, there is no way to
deallocate the storage and still have the variable available the — memory is not
freed until the variable is no longer in scope. A reference type does not have
this restriction. A reference type in PL/SQL is same as a pointer in C. A
variable that is declared of a reference type can point to different storage
locations over the life of the program. The reference type variables available
in PL/SQL are REF CURSOR and REF object type.
5) LOB
TYPES: The
LOB types are used to store large objects. A large object can be either a
binary or character value upto 4 GB in size. Large objects can contain
unstructured data, which is accessed more efficiently than LONG and LONG RAW
data, with fewer instructions.
