We often declare variables in our PL/SQL programs, and most of the time these variables are used to fetch some values from database tables or store some values in the database tables. The variables used for this purpose should have size and structure resembling that of table columns. Now, unless you have a very sharp memory, remembering the structure and size of database colums is really a hard task.
We can solve this problem by making use of Anchored Declarations. PL/SQL offfer two types of anchoring.
i) Scalar Anchoring: In this type of anchoring we use %TYPE attribute to define variable based on some table column or any other PL/SQL variable.
ii) Record Anchoring: We can use %ROWTYPE attribute to define a variable based on a row in the table or on an explicit cursor.
Let's see the use of Anchored Declarations with the help of an example.
Suppose we have a table which has script as shown below.
create table contacts(name varchar2(20), mobile number(10));
insert into contacts values('John', 4563821293);
insert into contacts values('Paul', 7463292330);
In the above script a table contacts has been created and some data is inserted.
Now we will write an anonymous block to understand the use of Anchored declarations.
set serveroutput on;
Declare
v_mobile contacts.mobile%type;
Begin
select mobile into v_mobile from contacts where name='John';
dbms_output.put_line('Mobile no. is '||v_mobile);
end;
In the above block we are trying to fetch the mobile no. of John. Just see how v_mobile variable is defined with the use of anchored declaration. Just by writing
contacts.mobile%type, its datatype is referenced to that of
column mobile in the table. This was an example of
scalar declaration.
In the next example we will see the use of
record anchor declarations.
We shall again write an anonymous block to demonstrate the use of
Record anchored declaration.
Declare
v_rec contacts%rowtype;
Begin
select * into v_rec from contacts where name='John';
dbms_output.put_line('Mobile no. is '||v_rec.mobile);
end;
In the above block we are trying to fetch the whole row in a variable using anchored declarations. Just see, this time we have used %rowtype for fetching the row of a table. To access the individual field we use "variable.columnname", so here we are using v_rec.mobile which is the column name of the table.
Not only this, we can also anchor declare a variable based on another PL/SQL variable. A simple PL/SQL block is given below to demonstrate the usage.
Declare
v_rec contacts%rowtype;
v_rec1 v_rec%type;
Begin
select * into v_rec from contacts where name='John';
v_rec1:=v_rec;
dbms_output.put_line('Mobile no. is '||v_rec1.mobile);
end;
In the above block we are using a variable v_rec1 which is anchored declared based on v_rec.
Advantages of using anchored declarations
Synchronization with Database table columns: Whenever we declare an anchored variable based on row or a column of a table, then the datatype and size of the variable is automatically defined to that of the table column. So, we need not worry about datatype of a variable after anchored declaration.
Normalization: Suppose we have declared a number variable as
v_num number(5). Now we have the need to declare the same type of variable in a lot of procedures, functions and packages. Now let's say, one day the need arises to change the datatype of this variable from
number(5) to
number(8). In this scenario it would be a tedious task to search for this variable in all the functions and procedure, and change its datatype. To solve this problem we can make use of anchor declaration based on a PL/SQL variable as follows.
create or replace package mypack is
v_num number(5);
end;
declare
v_num1 mypack.v_num%type;
begin
null;
end;
In the above code we have defined a variable v_num in the package mypack, and later in our procedures and functions we can anchor declare any variable based on this package variable. This solves the problem discussed above.
Note:
i) Whenever we change the structure of any column in our tables, it is our duty to recompile the functions and procedures, otherwise the functions and packages, using anchor declaration based on table columns, will not work.
ii) Keep in mind, in the case of anchored declarations based on a PL/SQL variable, NOT NULL constraint is automatically transferred to the anchor declared variable. In case of anchored declaration based on table columns, this does not happen.
Its a nice blog. I would suggest writing the PL/SQL blocks separately will make the blog nicer. So, that way one will not have to search for the PL/SQL statements inside the text.
ReplyDeleteThanks