Anchored Datatype (% Type)
Anchored data types are those data type which you assign to a variable based on a database object. They are called anchored data type because unlike the variable data type it is not dependent on that of any underlying object.
Syntax of Anchored Datatype.
Where variable name is user defined name given to a variable and type attribute can be anything such as previously declared PL/SQL variable or column of a table. And at the end %type is the direct reference to the underlying database object.
Examples of Anchored Datatype.
For the demonstration I have created a table by the name of Students which has two columns
Stu_id with data type Number and data width 2 and First_name with data type varchar2 and data width 8.
I have also inserted two rows into this table.
How to Declare a variable with Anchored Datatype
Next I will write an anonymous block where I will declare a variable with anchored data type and then initialize that variable by fetching value from this table.
So let’s do it.
SET SERVEROUTPUT ON;
Here In the declaration section I have declared a variable by the name of v_fname
with identical data type as the column First Name
of table Students
. This means that the data type of variable v_fname will be the varchar2 with data width 8. This is the data type and data width of the column first name of our table students.
So let’s add execution section to this anonymous PL/SQL block and initialize this variable v_fname by fetching data from the table students.
SET SERVEROUTPUT ON;
SELECT first_name INTO v_fname FROM students WHERE stu_id =1;
Here in this execution block I have a Select… Into statement
using which I am fetching first name of the student whose stu_id is 1 and storing it into our variable v_fname.
That’s how we declare a variable with anchored data type.
Hope you this was helpful. Kindly please share this with your friends and help me reach out to more people. Thanks and have a great day!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
NO SQL Script used in Video and in this article