User Defined Record Type and Its declaration

Case	Table-1	Table-2	Total Table Columns	Used Columns	Simple Variables Dynamic Variable	Table Based Record %ROWTYPE	User Defined Record
1	XXEMP_TL		50	5	Ok	-	-
2	XXEMP_TL		50	50	-	OK	-
3	XXEMP_TL		50	40	-	-	OK
4	XXEMP_TL	XXDEPT_TL	50/40	4/5	-	-	OK

--====================================================

User Defined Record 

--====================================================

Synatx:-

DECLARE

TYPE <TYPE_NAME> IS RECORD (  Col1 DATATYPE(SIZE),

                                                   Col2 DATATYPE(SIZE),

                                                   Col3 DATATYPE(SIZE),

                                                   Col4 DATATYPE(SIZE)

                                                  );

VARIAVLE_NAME  <TYPE_NAME>;

BEGIN

EXCEPTION

END;

--====================================================

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

V_EMP_TABLE  XXEMP_TL%ROWTYPE;

EMPNO,    ENAME,    JOB,    MGR,    HIREDATE,    SAL,    COMM,    DEPTNO

EMPNO,    ENAME,    DEPTNO

select A.EMPNO, A.ENAME, A.DEPTNO

FROM XXEMP_TL A

WHERE EMPNO = :V_EMPNO;

--========================

Example:-1 :- Based on Single Table

--========================

DECLARE

TYPE XX_EMP_INFO IS RECORD (

                                    EMPNO  XXEMP_TL.EMPNO%TYPE  ,

                                    ENAME XXEMP_TL.ENAME%TYPE ,

                                    DEPTNO XXEMP_TL.DEPTNO%TYPE                             

                                  );

V_EMP_TABLE  XX_EMP_INFO;

   

BEGIN

SELECT  A.EMPNO, A.ENAME, A.DEPTNO

INTO    V_EMP_TABLE.EMPNO, V_EMP_TABLE.ENAME, V_EMP_TABLE.DEPTNO 

FROM XXEMP_TL A

WHERE EMPNO = :V_EMPNO;

   

dbms_output.put_line ('Employee Record :- '||

                                      '  | Emp No : '||V_EMP_TABLE.EMPNO||

                                      '  | Name : '||V_EMP_TABLE.ENAME||

                                      '  | Deptartment : '||V_EMP_TABLE.DEPTNO

                                      );

EXCEPTION when others THEN

    dbms_output.put_line (SQLCODE||' - ERROR MSG :- '||SQLERRM);

END;

--=======================

Example:-2 Based on Multiple Tables

--=======================

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

select * from XXDEPT_TL

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

SELECT  A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

FROM XXEMP_TL A, XXDEPT_TL B

WHERE A.DEPTNO = B.DEPTNO

AND EMPNO = :V_EMPNO;

--=================================

DECLARE

TYPE XX_EMP_TYPE IS RECORD (

                                    EMPNO  XXEMP_TL.EMPNO%TYPE  ,

                                    ENAME XXEMP_TL.ENAME%TYPE ,

                                    DEPTNO XXDEPT_TL.DEPTNO%TYPE , 

                                    DNAME XXDEPT_TL.DNAME%TYPE

                                  );

V_EMP_TABLE  XX_EMP_TYPE;

   

BEGIN

SELECT  A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

INTO    V_EMP_TABLE.EMPNO, V_EMP_TABLE.ENAME, V_EMP_TABLE.DEPTNO, V_EMP_TABLE.DNAME 

FROM XXEMP_TL A, XXDEPT_TL B

WHERE A.DEPTNO = B.DEPTNO

AND EMPNO = :V_EMPNO;

   

dbms_output.put_line ('Employee Record :- '||

                                      '  | Emp No : '||V_EMP_TABLE.EMPNO||

                                      '  | Name : '||V_EMP_TABLE.ENAME||

                                      '  | Deptartment : '||V_EMP_TABLE.DEPTNO||

                                      '  | Dept name : '||V_EMP_TABLE.DNAME);

EXCEPTION when others THEN

    dbms_output.put_line (SQLCODE||' - ERROR MSG :- '||SQLERRM);

END;

--====================================================

--Advantages

--====================================================

1. it support multi table columns ( because it is defined by user)

2. It saves memory :- which is used by our variables

3. it also reduce the no of variable declaration which we declare.

4. it is totally user defined :- record Structure with user defined columns only

--====================================================

--Disadvantages

--====================================================

1. We have to write more lines of code

2. that decrease our program performance

3. some limitation in each variables all things are depends on our requirement