-
Contributing Member
Record type and %rowtype
What is the difference between record composit type and %rowtype?
Declare
Type Emptype is record
(
emp_no Emp.empno%type ,
emp_name emp.ename%type,
emp_sal emp.sal%type);
Emprec Emptype;
Begin
Select empno,ename,sal into emprec from emp where empno= 7369;
Dbms_output.put_line('Employee number is '|| emprec.emp_no);
Dbms_output.put_line('Employee name is '||emprec.emp_name);
Dbms_output.put_line('Employee salary is '|| emprec.emp_sal);
end;
/
The same result can be achieved simply using %rowtype.
Declare
Emprec Emp%rowtype;
Begin
Select * into emprec from emp where empno= 7369;
Dbms_output.put_line('Employee number is '|| emprec.empno);
Dbms_output.put_line('Employee name is '||emprec.ename);
Dbms_output.put_line('Employee salary is '|| emprec.sal);
end;
/
Then what is the addition advantage we have using record type?
-
Junior Member
Re: Record type and %rowtype
According to your above code, if you give %rowtype, it will take all the columns and their datatypes from the table which you specified
( ex: in 'emp' if you are having 3 columns means it will take all the 3 columns and their datatypes).%rowtype can be used only when we want
to select all the columns and their datatypes in the specified table(s).
When it comes to record type you can choose few or 'n' number of columns (or all columns,and you can use subquires also)
and we can make it as a record type.
Run the below code's so you can understand the difference,
declare
type rec is record
(v_ename emp.ename%type,
v_empno emp.empno%type,
v_job emp.job%type,
v_sal emp.sal%type,
v_deptno emp.deptno%type,
v_mgr emp.mgr%type );
rec1 rec;
begin select ename,empno,job,sal,deptno,mgr into rec1 from emp
where empno=(select empno from emp where ename='&ename');
dbms_output.put_line('employee name is:->'|| rec1.v_ename || 'no is :- '||rec1.v_empno || ' ' || ' having designatio:-' |
| rec1.v_job || 'with salary' || rec1.v_sal || 'and department no is:- ' || rec1.v_deptno);
if
rec1.v_ename='king' then dbms_output.put_line('for presedent manager ' || rec1.v_mgr || ' is null');
end if;
end;
/
declare
type bin_tab is table of emp%rowtype
index by binary_integer;
bin_tab1 bin_tab;
begin
for i in 1..1 loop
select * into bin_tab1(i) from emp where empno = &d; end loop;
for i in 1..1 loop
dbms_output.put_line(bin_tab1(i). Deptno ||bin_tab1(i).ename||bin_tab1(i).job ||bin_tab1(i).sal ||bin_tab1(i).mgr);
dbms_output.put_line(bin_tab1(1).ename);
end loop;
end;
bye
Last edited by su123; 03-18-2008 at 02:06 AM.
Reason: for beter view
-
Re: Record type and %rowtype
Suppose your table is having 15 columns and you want to select only 5 columns from the table . Can you use %rowtype in that case ?
-
Contributing Member
Re: Record type and %rowtype
Even though %rowtype selects all 15 columns and we can access selected 5 columns only. But Performancewise does it has any effect.
We can restrict columns with %rowtype in the following way also
CURSOR c1 IS
SELECT ename, sal, hiredate, job FROM emp;
emp_rec c1%ROWTYPE;
Can't we use subqueries along with %rowtype?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Reply With Quote
