Introduction to Oracle % Type

% Type attribute
In PL/SQL, you can declare variables and constants as built-in or user-defined data types to reference a column name and inherit its data types and sizes. This dynamic value assignment method is very useful. For example, the data type and size of the columns referenced by variables have changed. If % type is used, you do not have to modify it.CodeOtherwise, you must modify the code.

Type is used to obtain the field type in a table or cursor. After the type or field of the table is modified, no code needs to be modified for the defined variable;

 

V_empno Scott. EMP. empno % type;

V_salary EMP. Salary % type;

 

Delcare

V_a number (5): = 10;

V_ B v_a % Type: = 15;

V_c v_a % type;

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, ifWhat happens when the definition of the first_name column is changed (for example, if the table is changed, the current type of first_name is changed to varchar2 (25 ))? All PL/SQL code that uses 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 useInstead of hard encoding the variable type.

For example:

Declare

V_firstname students. first_name % type;

UseThe % type, v_firstname variable will be of the same type as the first_name column in the students table (it can be understood that the two are bounded ).