RelationalDBDesign RelationalDBDesign



Composite Datatypes   «Prev  Next»

Using Composite Datatypes within PL/SQL

The correct composite datatypes for the scenarios are:
  1. Need to store 10 product names: Use PL/SQL table
  2. Need to store name , sale_price , current_inventory_count for a single product: Use PL/SQL record
  3. Need to store product_name , but the size of the column in the database is unknown: Use %TYPE
  4. Need to store the details of all the columns within the PRODUCT table: Use %ROWTYPE

PL/SQL Collections and Records

A composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite. You can use scalar components wherever you can use scalar variables. PL/SQL lets you define two kinds of composite data types:
  1. collection and
  2. record.
You can use composite components wherever you can use composite variables of the same type.
If you pass a composite variable as a parameter to a remote subprogram, then you must create a redundant loop-back DATABASE LINK, so that when the remote subprogram compiles, the type checker that verifies the source uses the same definition of the user-defined composite variable type as the invoker uses.
In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable, you either define a collection type and then create a variable of that type or use %TYPE. In a record, the internal components can have different data types, and are called fields. You can access each field of a record variable by its name, with this syntax:
variable_name.field_name. 
To create a record variable, you either define a RECORD type and then create a variable of that type or use 1) %ROWTYPE or 2) %TYPE.