Oracle-PLSQL Composite data type

• Composite variables are also called composite variables. Composite variables include multiple internal components, and each component can store values separately,
Therefore, a composite variable can store multiple values.
• The composite variable type is not an existing data type in the database. Therefore, before declaring a composite variable type, you must first create a composite type,

The composite type can be used multiple times after being created to define multiple composite variables.

Composite data types include:

-Record

-Table

-Nested table

-Array

A record is a logical unit composed of a set of related data items. Each data item has its own name and data type.
 
A table is a collection of data that can be referenced and processed as a whole.
 
A table consists of columns and keywords, and rows can be accessed in groups through keywords.
 
Once defined, record and table can be reused.
 

PL/SQL records

• Record is a set of related data items stored in multiple fields. Each field has its own name and data type.
• Regard the set of fields as a whole logical unit
• It is mainly used to retrieve the queried row data from the table.

Record features: 

• Each record can contain many fields.
• You can assign initial values to records and use not null to limit records.
• A field without an initial value is defined as null • The reserved word default can also be used when defining a field
• The record type can be defined in the Declaration part of any block, subroutine, or package, and user-defined records can be declared
• You can declare and reference nested records. A record can be a component of other records.

Syntax:

Type type_name is record <br/> (field_declaration [, field_declaration]…);

Field_declaration:

Field_name {field_type | variable % Type <br/> | table. column % type | table % rowtype} <br/> [[not null] {: = | default} expr]
Type emp_record_type is record <br/> (empno EMP. empno % type, <br/> ename EMP. ename % type, <br/> job varchar2 (9); <br/> emp_record emp_record_type; <br/>
You can use point numbers to reference fields in a record.

Record_name.field_name

% Rowtype
Attribute

• Declared variables correspond to the set of columns in database tables or views
• Add the database table name before % rowtype
• For the names and Data Types of fields in the record, refer to the columns in the table or view.

Declare <br/> emp_record EMP % rowtype; <br/> .... <Br/> emp_record.ename = 'ljs'; <br/> emp_record.sal = 3000; <br/> select * into emp_record <br/> from EMP <br/> where ......;
Advantages of % rowtype:

• You do not need to know the number and type of columns in the database.
• During running, the number and type of columns in the database may change.
• This attribute can be used in select statements to effectively retrieve rows in a table.

Create PL/SQL
Table

Type type_name is table of <br/> {column_type | variable % Type <br/> | table. column % Type} [not null] <br/> [index by binary_integer]; <br/> identifier type_name;
Declare <br/> type ename_table_type is table of EMP. ename % Type <br/> index by binary_integer; <br/> type hiredate_table_type is table of date <br/> index by binary_integer; <br/> ename_table ename_table_type; <br/> hiredate_table hiredate_table_type; <br/> begin <br/> ename_table (1): = 'cameron '; <br/> hiredate_table (8): = sysdate + 7; <br/> If ename_table.exists (1) Then <br/> insert... <br/>... <br/> end;

Table method: