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

Popular posts from this blog

E-Text Report In Fusion | Types of E-Text reports

Supplier API's

How to pass default Parameter in cursor