PL/SQL three special data types

In addition to the basic types specified by orecle, PL/SQL provides 3 special data types%type types, record types, and%rowtype types.

"%type Type"

You can use%type to declare a data type that is the same as the specified column name, for example: v_id Emp.id%type is a variable of the same type as the ID field in the EMP table.

There are two advantages to using%type to define a variable: First, you do not have to look at the data type of each column in the table when defining the variable, and second, if the column data types in the table are modified, the variables defined with%type are automatically adjusted.

"Record Type"

Also known as record types, variables that use the record can store a row of data consisting of multiple column values. Use the following methods:

Declare

Type Emp_type is record (---------------------declares the record type Emp_type

V_ID Emp.id%type;

V_name Emp.name%type;

)

Empinfo Emp_type; -------------------declares a variable of type Emp_type empinfo

Begin

Select ID name into Empinfo from EMP where id=1;

/*

Query the EMP table for a record with ID 1 assigned to Empinfo

*/

Dbms_output.put_line (empinfo.v_id| | ' ==========> ' | | Empinfo.v_name);

End

【 】

Variables of type%rowtype combine the advantages of the%type type and the record type, which can be used to store a record based on the structure of the rows in the table that defines the data type. For example:

Declare

Empinfo Emp%rowtype;

Begin

SELECT * into Empinfo from EMP where id=1;

Dbms_output.put_line (empinfo.id| | ' ========> ' | | Emp.name);

End