How to use%type and%rowtype in Oracle pl/sql

1. Use of%type
In many cases, the PL/SQL variable can be used to store data in a database table. In this case, the variable should have the same type as the table column. For example, the first_name column of the students table is of type VARCHAR2 (20), and we can declare a variable as follows:

The code is as follows	Copy Code
DECLARE V_firstname VARCHAR2 (20);

But what happens if the definition of the first_name column changes (for example, the table changes, first_name now has a type of VARCHAR2 (25))? That will cause all Pl/sql code that uses this column to be modified. If you have a lot of pl/sql code, this process can be time-consuming and error-prone.
At this point, you can use the "%TYPE" property instead of typing the variable type hard.
For example:

The code is as follows	Copy Code
DECLARE V_firstname Students.first_name%type;

By using the%type,v_firstname variable, the first_name column of the students table is the same type (which can be understood to set both states).
This type is determined each time an anonymous block or named Block runs the block of statements and compiles stored objects (procedures, functions, packages, object classes, and triggers).
Using%type is a very good programming style because it makes pl/sql more flexible and more adaptable to updating database definitions.
2. Use of%rowtype
2.1 Pl/sql Records
The Pl/sql record type is similar to the structure in C language, is a composite type, and is user-defined.
Records provide a mechanism for handling variables that are independent but also related to a whole unit. Please see:

The code is as follows	Copy Code
DECLARE V_studentid number (5); V_firstname VARCHAR2 (20); V_lastname VARCHAR2 (20);

These 3 variables are logically interrelated because they point to different fields in the students table. If you declare a record type for these variables, the relationship between them is obvious and can be handled as a unit.
　　

The code is as follows	Copy Code
DECLARE /*define a record type to hold common student informationi*/ TYPE T_studentrecord is record ( StudentID Number (5), FirstName VARCHAR2 (20), LastName VARCHAR2 (20); /*declare A variable of this type.*/ V_studentinfo T_studentrecord;

2.2 Record Assignment
You can assign a value to a record with a SELECT statement, which retrieves the data from the database and stores the data in the record. Note that the fields in the record should match the fields in the query results list.

The code is as follows	Copy Code
SELECT Studentid,firstname,lastname Into V_studentinfo from students where studentid=32;

2.3 Using%rowtype
It is common to declare a record as having a database row of the same type in Pl/sql. The Pl/sql provides the%rowtype operator, making such operations more convenient.
For example:

The code is as follows	Copy Code
DECLARE V_roomrecord Rooms%rowtype;

A record is defined, and the fields in the record correspond to the columns in the rooms table