A comprehensive discussion on the compound data types of pl/sql

Pl/sql has two types of composite data structures: records and collections. Records are made up of different fields, and collections are made up of different elements. In this article we will discuss the types of records and collections, how to define and use records and collections.

Pl/sql Records

Records are a composite data structure of pl/sql, scalar data types and other data types are simply predefined at the package level, but composite data types must be defined before they are used, and records are called composite data types because he is composed of a domain, a logical group of data elements. A field can be a scalar data type or other record type, similar to the structure in C language, records can also be viewed as rows of data in a table, and fields are equivalent to columns in a table, and are easily defined and used in tables and virtual tables (views or queries), and each column or field in a row or record can be referenced or individually assigned. You can also record all the fields through a single statement reference. Records may also have parameters in stored procedures or functions.

Create a record

There are two ways of defining in Pl/sql: explicit definition and implicit definition. Once the record is defined, declare or create a record variable that defines the type, and then the variable is used. An implicit declaration is the use of the%type property on a table-based structure or query, and implicit declarations are a more powerful tool because the data variables are created dynamically.

Explicitly define a record

The explicit definition of a record is defined in the declaration section before the record variable is created in the PL/SQL program block. Use the type command to define a record, and then create the variable for that record. The syntax is as follows:

TYPE record_type IS RECORD (field_definition_list);

Field_definition_list is a comma-delimited list.

The syntax for a domain definition is as follows:

field_name data_type_and_size [NOT NULL][{:=|DEFAULT} default_value]

The domain name must be subject to the same naming convention as the table or column naming convention. Let's take a look at the following example:

DELCARE
TYPE stock_quote_rec IS RECORD
(symbol stock.symbol%TYPE
,bid NUMBER(10,4)
,ask NUMBER(10,4)
,volume NUMBER NOT NULL:=0
,exchange VARCHAR2(6) DEFAULT 'NASDAQ'
);
real_time_quote stock_quote_rec;
variable

The%type property when the domain definition is used to refer to the data type and size of the table or view in the database, before which the program does not know the type and size. In the example above, the record field will be defined as the same data type and size as the column symbol, and it is best defined in the variable or field definition with%type when you want to use the data from the database in your code.