%type,%rowtype in Oracle

%type,%rowtype in Oracle
1. Using%type
In many cases, a 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 what happens if the definition of the first_name column changes (for example, if the table changes, the current type of first_name changes to 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 kind of processing can be time-consuming and error-prone.
At this point, you can use the "%TYPE" property instead of hard-coding the variable type.
For example:
DECLARE
V_firstname Students.first_name%type;
Using the%type,v_firstname variable will be the same type as the first_name column of the students table (which can be understood as setting up the two states).
This type is determined every time an anonymous block or a named block runs the statement block and compiles the stored object (procedure, function, package, object class, and trigger).
Using%type is a very good programming style because it makes PL/SQL more flexible and more adaptable to database-defined updates.
For example:
Declare
V_ID Hr.jobs.job_id%type;
V_title Hr.jobs.job_title%type;
Begin
Select Job_id,job_title into V_id,v_title
From Hr.jobs
where job_id = ' &aa ';
Dbms_output.put_line (' Serial number ' | | V_ID);
Dbms_output.put_line (' Name ' | | V_title);
End
Execute, we enter the value of the AA variable: AD_VP
The output is:
Serial AD_VP
Name Administration Vice President
2 Using%rowtype
It is common practice to declare a record in PL/SQL as a database row of the same type. PL/SQL provides the%rowtype operator, which makes the operation more convenient.
For example:
DECLARE
V_studentrecord Students%rowtype;
A record is defined, and the fields in the record correspond to the columns in the students table.
For example:
Declare
V_jobs Hr.jobs%rowtype;
Begin
SELECT * Into V_jobs
From Hr.jobs
where job_id = ' &aa ';
Dbms_output.put_line (' Serial number ' | | V_JOBS.JOB_ID);
Dbms_output.put_line (' Name ' | | V_jobs.job_title);
End
Execute, we enter the value of the AA variable: AD_VP
The output is:
Serial AD_VP
Name Administration Vice President
Tshfang
Source: Mud Embryo article writing http://www.nipei.com/original address: http://www.nipei.com/article/2384

%type,%rowtype in Oracle