Composite Variable or %ROWTYPE Variable or Table Based Record
--====================================================
Simple Variable
1.
Explicit Declaration
Ex:-
V_NAME VARCHAR2(100);
2.
Implicit Declaration
Ex:-
V_NAME XXEMP_TL.ENAME%TYPE;
--====================================================
--Here we can
store only one value. but if we waann to store multiple value in one variable
Composite Variable OR Table
based Record Variable
--====================================================
Synatx:-
Variable_Name Table_Name%RowType
Size of :-
total size of all columns
DataType:- Table type
--====================================================
select * from XXEMP_TL
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
|
|
7782
|
CLARK
|
MANAGER
|
7839
|
09-Jun-81
|
2450
|
10
|
|
7788
|
Scott
|
ANALYST
|
7566
|
09-Dec-82
|
3000
|
20
|
|
7839
|
KING
|
PRESIDENT
|
17-Nov-81
|
5000
|
10
|
||
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
|
|
7934
|
MILLER
|
CLERK
|
7782
|
23-Jan-82
|
1300
|
10
|
|
7999
|
Rani
|
SALESMAN
|
7698
|
23-Sep-88
|
1900
|
10
|
10
|
--========
Example:-1
--========
DECLARE
V_EMPNO VARCHAR(5);
V_ENAME VARCHAR(50);
V_JOB VARCHAR(30);
V_MGR VARCHAR(5);
V_HIREDATE DATE;
V_SAL NUMBER;
V_COMM NUMBER;
V_DEPTNO NUMBER;
BEGIN
SELECT EMPNO,
ENAME , JOB , MGR ,
HIREDATE , SAL , COMM
,
DEPTNO
INTO V_EMPNO,
V_ENAME ,
V_JOB ,
V_MGR ,
V_HIREDATE ,
V_SAL ,
V_COMM ,
V_DEPTNO
FROM XXEMP_TL
WHERE
EMPNO = :V_EMPNO;
dbms_output.put_line ('Employee
Record :- '||
' | Emp
No : '||V_EMPNO||
' | Name : '||V_ENAME||
' | Designation : '||V_JOB||
' | Manager : '||V_MGR||
' | Hire Date : '||V_HIREDATE||
' | Salary : '||V_SAL||
' | Comm : '||V_COMM||
' | Deptartment : '||V_DEPTNO);
EXCEPTION when others THEN
dbms_output.put_line (SQLCODE||' -
ERROR MSG :- '||SQLERRM);
END;
OUTPUT:-
Employee Record :- | Emp No : 7844 | Name : TURNER | Designation : SALESMAN | Manager : 7698 | Hire Date : 08-SEP-81 | Salary : 1500 | Comm : 0
| Deptartment : 30
--========
Example:-2
--========
DECLARE
V_EMP_TABLE XXEMP_TL%ROWTYPE;
BEGIN
SELECT EMPNO,
ENAME , JOB , MGR ,
HIREDATE , SAL , COMM
,
DEPTNO
INTO V_EMP_TABLE.EMPNO,
V_EMP_TABLE.ENAME
,
V_EMP_TABLE.JOB ,
V_EMP_TABLE.MGR ,
V_EMP_TABLE.HIREDATE
,
V_EMP_TABLE.SAL ,
V_EMP_TABLE.COMM ,
V_EMP_TABLE.DEPTNO
FROM XXEMP_TL
WHERE
EMPNO = :V_EMPNO;
dbms_output.put_line ('Employee
Record :- '||
' | Emp
No : '||V_EMP_TABLE.EMPNO||
' | Name : '||V_EMP_TABLE.ENAME||
' | Designation : '||V_EMP_TABLE.JOB||
' | Manager : '||V_EMP_TABLE.MGR||
' | Hire Date : '||V_EMP_TABLE.HIREDATE||
' | Salary : '||V_EMP_TABLE.SAL||
' | Comm : '||V_EMP_TABLE.COMM||
' | Deptartment : '||V_EMP_TABLE.DEPTNO);
EXCEPTION when others THEN
dbms_output.put_line (SQLCODE||' -
ERROR MSG :- '||SQLERRM);
END;
OUTPUT:-
Employee Record :- | Emp No : 7844 | Name : TURNER | Designation : SALESMAN | Manager : 7698 | Hire Date : 08-SEP-81 | Salary : 1500 | Comm : 0
| Deptartment : 30
--====================================================
--Advantages
--====================================================
1. it
decrease the
coding lines
2. it
increase the program performance
--====================================================
--Disadvantages
--====================================================
1. it
Increase the
coding lines for
declaring the
multi variables
2. it
decrease the program performance
3. It
increase the Program timing
4. by using %ROWTYPE we
can not store
perticular column value
Comments
Post a Comment