Oracle%type and%rowtype__oracle

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:
DECLARE
V_firstname VARCHAR2 (20);
But if the definition of the first_name column changes what happens (for example, the table changes, First_Name's current type becomes 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:
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.

General syntax for declaring record types:

Type is a record (

Field 1 data type [NOT NULL] [{default |: =} expression]

[, Field 2 ...]

)

Instance:
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.
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.
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:
DECLARE
V_roomrecord Rooms%rowtype;
A record is defined, and the fields in the record correspond to the columns in the rooms table.

The difference between 3.%type and%rowtype

%type is used for basic data types,%rowtype for record types and table structures, and so on, and they are all in the same place by applying a type of its modification to a variable to keep the two consistent.

Source: http://www.wangchao.net.cn/bbsdetail_148980.html