Difference between %ROWTYPE and %TYPE Variable
Simple / Single Variable :- VAR_1 VARCHAR2(100);
TABLE_NAME%ROWTYPE :- VAR_1 XX_EMP%ROWTYPE
CURSOR_NAME%ROWTYPE :- (Multi_table ) :- CURSOR_NAME%ROWTYPE
TABLE_NAME.COLUMN_NAME%TYPE :- VAR_1 XX_EMP.SAL%TYPE
|
EMPNO |
ENAME |
JOB |
MGR |
HIREDATE |
SAL |
COMM |
DEPTNO |
|
7369 |
SMITH |
CLERK |
7902 |
17-Dec-80 |
800 |
|
20 |
|
7499 |
ALLEN |
SALESMAN |
7698 |
20-Feb-81 |
1600 |
300 |
30 |
|
7521 |
WARD |
SALESMAN |
7698 |
22-Feb-81 |
1250 |
500 |
30 |
|
7566 |
JONES |
MANAGER |
7839 |
02-Apr-81 |
2975 |
|
20 |
|
7654 |
MARTIN |
SALESMAN |
7698 |
28-Sep-81 |
1250 |
1400 |
30 |
|
7698 |
BLAKE |
MANAGER |
7839 |
01-May-81 |
2850 |
|
30 |
|
7788 |
SCOTT |
ANALYST |
7566 |
09-Dec-82 |
3000 |
|
20 |
|
7844 |
TURNER |
SALESMAN |
7698 |
08-Sep-81 |
1500 |
0 |
30 |
|
7876 |
ADAMS |
CLERK |
7788 |
12-Jan-83 |
1100 |
|
20 |
|
7900 |
JAMES |
CLERK |
7698 |
03-Dec-81 |
950 |
|
30 |
|
7902 |
FORD |
ANALYST |
7566 |
03-Dec-81 |
3000 |
|
20 |
|
7782 |
CLARK |
MANAGER |
7839 |
09-Jun-81 |
2450 |
|
10 |
|
7839 |
KING |
PRESIDENT |
|
17-Nov-81 |
5000 |
|
10 |
|
7934 |
MILLER |
CLERK |
7782 |
23-Jan-82 |
1300 |
|
10 |
|
9001 |
Manoj |
CLERK |
|
08-Nov-20 |
1000 |
450 |
40 |
--====================================================
Use of Normal Variable
--====================================================
DECLARE
V_NAME VARCHAR2(100);
BEGIN
select
ENAME INTO
V_NAME
from XX_EMP
WHERE
EMPNO = '7369';
dbms_output.put_line ('Employee
Name : '||
V_NAME);
END;
--====================================================
Use of %ROWTYPE
--====================================================
DECLARE
V_ENAME VARCHAR2(100);
V_JOB VARCHAR2(100);
V_MGR VARCHAR2(100);
V_HIREDATE VARCHAR2(100);
V_SAL NUMBER;
V_DEPTNO VARCHAR2(100);
BEGIN
select ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO
INTO V_ENAME, V_JOB, V_MGR, V_HIREDATE, V_SAL, V_DEPTNO
from XX_EMP
WHERE
EMPNO = '7369';
dbms_output.put_line ('Employee
Name : '||
V_ENAME);
dbms_output.put_line ('Job
Name : '||
V_JOB);
dbms_output.put_line ('Manager
Code : '||
V_MGR);
dbms_output.put_line ('DOJ :
'||
V_HIREDATE);
dbms_output.put_line ('Department
ID : '||
V_DEPTNO);
END;
--===================================================
DECLARE
V_EMP XX_EMP%ROWTYPE;
BEGIN
--select ENAME,
JOB, MGR, HIREDATE, SAL, DEPTNO
select *
INTO
V_EMP
from XX_EMP
WHERE
EMPNO = '7369';
dbms_output.put_line ('Employee
Name : '||V_EMP.ENAME);
dbms_output.put_line ('Job
Name : '||
V_EMP.JOB);
dbms_output.put_line ('Manager
Code : '||
V_EMP.MGR);
dbms_output.put_line ('DOJ :
'||
V_EMP.HIREDATE);
dbms_output.put_line ('Department
ID : '||
V_EMP.DEPTNO);
END;
--====================================================
HOW TO USE %ROWTYPE for
Multuitable
--====================================================
DECLARE
CURSOR C1 IS
select A.*, B.DNAME
from XX_EMP A, XX_DEPT B
WHERE A.DEPTNO
= B.DEPTNO
AND
EMPNO = '7369';
V_EMP C1%ROWTYPE;
BEGIN
OPEN C1;
FETCH C1 INTO
V_EMP;
dbms_output.put_line ('Employee
Name : '||V_EMP.ENAME);
dbms_output.put_line ('Job
Name : '||
V_EMP.JOB);
dbms_output.put_line ('Manager
Code : '||
V_EMP.MGR);
dbms_output.put_line ('DOJ :
'||
V_EMP.HIREDATE);
dbms_output.put_line ('Department
ID : '||
V_EMP.DEPTNO);
dbms_output.put_line ('Department
NAME : '||
V_EMP.DNAME);
CLOSE C1;
END;
--====================================================
Use of %TYPE
--====================================================
DECLARE
--V_ENAME
VARCHAR2(100);
--V_JOB
VARCHAR2(100);
--V_MGR
VARCHAR2(100);
--V_HIREDATE VARCHAR2(100);
--V_SAL NUMBER;
--V_DEPTNO
VARCHAR2(100);
V_ENAME XX_EMP.ENAME%TYPE;
V_JOB XX_EMP.JOB%TYPE;
V_MGR XX_EMP.MGR%TYPE;
V_HIREDATE XX_EMP.HIREDATE%TYPE;
V_SAL XX_EMP.SAL%TYPE;
V_DEPTNO XX_EMP.DEPTNO%TYPE;
BEGIN
select ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO
INTO V_ENAME, V_JOB, V_MGR, V_HIREDATE, V_SAL, V_DEPTNO
from XX_EMP
WHERE
EMPNO = '7369';
dbms_output.put_line ('Employee
Name : '||
V_ENAME);
dbms_output.put_line ('Job
Name : '||
V_JOB);
dbms_output.put_line ('Manager
Code : '||
V_MGR);
dbms_output.put_line ('DOJ :
'||
V_HIREDATE);
dbms_output.put_line ('Department
ID : '||
V_DEPTNO);
END;
Comments
Post a Comment