Lesson 1
Composite Datatypes in Oracle PL/SQL
PL/SQL provides two composite datatypes: TABLE and RECORD. This module discusses the two composite datatypes which are TABLE and RECORD.
Module objectives
By the end of this module, you will know how to:
- Describe the structure of a PL/SQL table
- Define and populate a PL/SQL record
-
Use %Type and %RowType attributes to manipulate data
In the next lesson, you will begin to learn the structure of a PL/SQL table.
Characteristics of PL/SQL Collection Types
| Collection Type |
Number of Elements |
Subscript Type |
Dense or Sparse |
Where Created |
Can Be Object Type Attribute |
| Associative array (or index-by table) |
Unbounded |
String or integer |
Either Only in |
PL/SQL block |
No |
| Nested table |
Unbounded |
Integer |
Starts dense, can become sparse |
Either in PL/SQL block or at schema level |
Yes |
| Variable-size array (varray) |
Bounded |
Integer |
Always dense |
Either in PL/SQL block or at schema level |
Yes |
PL/SQL data type categories
In a PL/SQL block, every constant, variable, and parameter has a data type.
PL/SQL provides predefined data types and subtypes and lets you define your own PL/SQL subtypes.
Table below lists the categories of the predefined PL/SQL data types.
Predefined PL/SQL scalar data types
| Data type category |
Description |
| Scalar |
Single values with no internal components |
| Composite |
Internal components that are either scalar or composite |
| Reference |
Pointers to other data items such as REF CURSORs |
Overview of PL/SQL Datatypes
Whenever you declare a variable or a constant, you must assign it a datatype. PL/SQL is, with very few exceptions, a statically typed programming language.
PL/SQL offers a comprehensive set of predefined scalar and composite datatypes, and you can create your own user-defined types (also known
as abstract datatypes). Many of the PL/SQL datatypes are not supported by database columns, such as Boolean and NATURAL, but within PL/SQL code, these datatypes
are quite useful. Virtually all of these predefined datatypes are defined in the PL/SQL STANDARD package. Here, for example, are the statements that define the Boolean datatype and
two of the numeric datatypes:
create or replace package STANDARD is
type BOOLEAN is (FALSE, TRUE);
type NUMBER is NUMBER_BASE;
subtype INTEGER is NUMBER(38,);