HomeOracle Page 4 - Database Interaction with PL/SQL, part 1
%ROWTYPE attribute - Oracle
This first article in a series focusing on database interactions with Oracle PL/SQL serves as a good introduction to the topic. It mainly focuses on the basics of retrieving information from the Oracle database using the SELECT..INTO statement, as well as two attributes, namely "%TYPE" and "%ROWTYPE." Readers should have some knowledge of RDBMS, Oracle SQL, and some very fundamental grounding in PL/SQL.
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.