Composite Datatypes   «Prev  Next»
Lesson 1

Working with 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:
  1. Describe the structure of a PL/SQL table
  2. Define and populate a PL/SQL record
  3. 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
subtype INTEGER is NUMBER(38,);