Oracle % type, % rowtype

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

V_firstname varchar2 (20 );

However
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 ))? Then all
The PL/SQL code for using this column must be modified. If you have a lot of PL/SQL code, such processing may be time-consuming and error-prone.

In this case, you can use the "% Type" attribute instead of hard encoding the variable type.

For example:

Declare

V_firstname students. first_name % type;

By using the % Type and v_firstname variables, the Data Type of the first_name column in the students table is the same (it can be understood as setting the two States ).

This type is determined every time an anonymous block or nameblock runs the statement block and compiles stored objects (processes, functions, packages, object classes, and triggers.

Using % type is a good programming style because it makes PL/SQL more flexible and more suitable for updating database definitions.
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 = '& ';
Dbms_output.put_line ('sequence number '| v_id );
Dbms_output.put_line ('name' | v_title );
End;
Run the command. Enter the value of the AA variable: ad_vp.
Output result:
No. ad_vp
Name Administration vice president
2 Use % rowtype
It is common to declare a record as a database row of the same type in PL/SQL. PL/SQL provides the % rowtype operator to facilitate such operations.

For example:

Declare

V_studentrecord students % rowtype;

A record is defined, and the fields in the record correspond to columns in the students table.
For example:
Declare
V_jobs hr.jobs % rowtype;
Begin
Select * into v_jobs
From hr.jobs
Where job_id = '& ';
Dbms_output.put_line ('sequence number '| v_jobs.job_id );
Dbms_output.put_line ('name' | v_jobs.job_title );
End;

Run the command. Enter the value of the AA variable: ad_vp.
Output result:
No. ad_vp
Name Administration vice president