Database Interaction with PL/SQL, part 1 - %ROWTYPE attribute
(Page 4 of 5 )
What if we want more and more columns of values to be retrieved and pushed into variables? Consider a table of fifteen columns. Is it necessary to declare all fifteen variables to retrieve those values? This is where %ROWTYPE comes in. It can be used to declare a variable to store an entire row from a specified table. Let us go through a simple example again.
Declare
v_empRec emp%rowtype;
v_empno emp.empno%type := &empno;
begin
select * into v_emprec
from emp
where empno=v_empno;
dbms_output.put_line('Name: '||v_emprec.ename);
dbms_output.put_line('Salary: '||v_emprec.sal);
dbms_output.put_line('Job: '||v_emprec.job);
end;
In the above example, we declared a variable "v_empRec," which can hold an entire row from the table "emp" (which is what "emp%rowtype" means). Within the body, we fetch an entire row (all of the columns) of information (using "*" in the "select" statement) and place it in "v_emprec." To fetch the value of each and every column, we specify the column name with the "%rowtype" variable separated with a dot (watch the dbms_output statements).
Note that it is not compulsory to use all of the columns in our program. This concept of "%rowtype" is not suitable for retrieving information based on joins, but indeed it helps a lot by excluding unnecessary variable declarations. Let us conclude this section with one more example.
Declare
v_empRec emp%rowtype;
v_deptRec dept%rowtype;
v_empno emp.empno%type := &empno;
begin
select * into v_emprec
from emp where empno=v_empno;
Select * into v_deptRec
from dept where deptno=v_empRec.deptno;
dbms_output.put_line('Name: '||v_emprec.ename);
dbms_output.put_line('Salary: '||v_emprec.sal);
dbms_output.put_line('Job: '||v_emprec.job);
dbms_output.put_line('Department: '||v_deptrec.dname);
dbms_output.put_line('Location: '||v_deptrec.loc);
end;
Since this is not very complicated, I leave it to the readers to understand the above program.
Next: Using Other DML Commands >>
More Oracle Articles
More By Jagadish Chatarji
