PL/SQL RECORDS

PL/SQL RECORDS

PL/SQL RECORDS ARE SIMILAR TO STRUTCURES IN C PROGRAMMING. A STRUCTURE IS AN

OBJECT WHERE WE CAN STORE MORE THAN ONE DATATYPE IN A SINGLE VARIABLE.

SYNTAX:-

DECLARE

TYPE <TYPE NAME> IS RECORD ( <FIELD NAME> <DATA TYPE> [NOT NULL],......);

<VARIABLE NAME> <TYPE NAME>;

BEGIN

----

END;

DECLARE

TYPE EMP_TYPE IS RECORD (EMPNO NUMBER(4),ENAME VARCHAR2(10),JOB VARCHAR2(10));

VEMP EMP_TYPE;

BEGIN

SELECT EMPNO,ENAME,JOB INTO VEMP FROM EMP WHERE EMPNO=7788;

DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO||VEMP.ENAME||VEMP.JOB);

END;

-----------------------------------------------------

WE CAN DECLARE ANY NUMBER OF VARIABLES WITH RECORD TYPE THAT WE DEFINED

DECLARE

TYPE EMP_TYPE IS RECORD (EMPNO NUMBER(4),ENAME VARCHAR2(10),JOB VARCHAR2(10));

VEMP EMP_TYPE;

VEMP1 EMP_TYPE;

BEGIN

SELECT EMPNO,ENAME,JOB INTO VEMP FROM EMP WHERE EMPNO=7788;

SELECT EMPNO,ENAME,JOB INTO VEMP1 FROM EMP WHERE EMPNO=7902;

DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO||VEMP.ENAME||VEMP.JOB);

DBMS_OUTPUT.PUT_LINE(VEMP1.EMPNO||VEMP1.ENAME||VEMP1.JOB);

END;

/

-----------------------------------------------------

WE CAN DIRECTLY ASSIGN ONE TYPE TO ANOTHER.

DECLARE

TYPE EMP_TYPE IS RECORD (EMPNO NUMBER(4),ENAME VARCHAR2(10),JOB VARCHAR2(10));

VEMP EMP_TYPE;

VEMP1 EMP_TYPE;

BEGIN

SELECT EMPNO,ENAME,JOB INTO VEMP FROM EMP WHERE EMPNO=7788;

VEMP1:=VEMP;

DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO||VEMP.ENAME||VEMP.JOB);

DBMS_OUTPUT.PUT_LINE(VEMP1.EMPNO||VEMP1.ENAME||VEMP1.JOB);

END;

DECLARE

TYPE EMP_TYPE IS RECORD (EMPNO NUMBER(4),ENAME VARCHAR2(10),JOB VARCHAR2(10));

VEMP EMP_TYPE;

VEMPNO EMP.EMPNO%TYPE;

BEGIN

VEMPNO:='&EMPNO';

SELECT EMPNO,ENAME,JOB INTO VEMP FROM EMP WHERE EMPNO=VEMPNO;

DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO||VEMP.ENAME||VEMP.JOB);

END;

PL/SQL TABLE

----------------------

A PL/SQL TABLE IS SIMILAR TO AN ARRAY IN C, WE CAN STORE MULTIPLE VALUES IN A

VARIABLE THAT IS DECLARED AS A PL/SQL TABLE.

SYNTAX:-

DECLARE

TYPE <TYPE NAME> IS TABLE OF <DATA TYPE> INDEX BY BINARY_INTEGER;

<VARIABLE NAME> <TYPE NAME>;

BEGIN

---

---

END;

-----------------------------------------------------

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

DBMS_OUTPUT.PUT_LINE(VENAME(1000));

END;

-----------------------------------------------------

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

DBMS_OUTPUT.PUT_LINE(VENAME(1)||VENAME(0));

END;

-----------------------------------------------------

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

DBMS_OUTPUT.PUT_LINE(VENAME.COUNT);

END;

-----------------------------------------------------

COUNT:- WILL COUNT THE TOTAL NO OF ELEMENTS WITHIN THE ARRAY

DELETE: - IT WILL DELETE THE ROWS IN A PL/SQL TABLE

<VARIABLE NAME>.DELETE

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

DBMS_OUTPUT.PUT_LINE(VENAME.COUNT);

VENAME.DELETE; -- THIS WILL DELETE ALL THE ROWS FROM THE PL/SQL TABLE

DBMS_OUTPUT.PUT_LINE(VENAME.COUNT);

END;

-----------------------------------------------------

TO DELETE A PARTICULAR ROW FROM THE PL/SQL TABLE

<VARIABLE NAME>.DELETE(INDEX)

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

DBMS_OUTPUT.PUT_LINE(VENAME.COUNT);

VENAME.DELETE(0);

VENAME.DELETE(1000);

DBMS_OUTPUT.PUT_LINE(VENAME.COUNT);

END;

-----------------------------------------------------

EXISTS:- WILL CHECK IF THE SPECIFIED PL/SQL TABLE ROW IS EXISTING OR NOT

<VARIABLE NAME>.EXISTS(INDEX)

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

DBMS_OUTPUT.PUT_LINE(VENAME.COUNT);

VENAME.DELETE(0);

VENAME.DELETE(1000);

IF VENAME.EXISTS(1) THEN

DBMS_OUTPUT.PUT_LINE(VENAME(1));

END IF;

DBMS_OUTPUT.PUT_LINE(VENAME.COUNT);

END;

-----------------------------------------------------

FIRST:- WILL RETURN THE FIRST ELEMENT OF THE ARRAY

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

DBMS_OUTPUT.PUT_LINE(VENAME.FIRST);

END;

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

VENAME.DELETE(0);

DBMS_OUTPUT.PUT_LINE(VENAME.FIRST);

END;

-----------------------------------------------------

LAST:- WILL RETURN THE LAST ELEMENT OF THE ARRAY

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

DBMS_OUTPUT.PUT_LINE(VENAME.LAST);

END;

-----------------------------------------------------

LOOP FOR PL/SQL TABLE

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

I NUMBER;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

I:= VENAME.FIRST;

LOOP

EXIT WHEN I>VENAME.LAST;

DBMS_OUTPUT.PUT_LINE(VENAME(I));

I:=VENAME.NEXT(I);

END LOOP;

END;

-----------------------------------------------------

NEXT :- IT WILL RETURN THE NEXT INDEX AFTER THE SPECIFIED INDEX

<VARIABLE NAME>.NEXT(<INDEX VALUE>);

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

DBMS_OUTPUT.PUT_LINE(VENAME.NEXT(1));

END;

-----------------------------------------------------

PRIOR :- IT WILL RETURN THE PREVIOUS INDEX BEFORE THE SPECIFIED INDEX

<VARIABLE NAME>.PRIOR(<INDEX VALUE>);

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

DBMS_OUTPUT.PUT_LINE(VENAME.PRIOR(1));

END;

-----------------------------------------------------

PRINTING ALL THE ARRAY ELEMENTS USING A LOOP AND NEXT

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

I NUMBER;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

I:= VENAME.FIRST;

LOOP

DBMS_OUTPUT.PUT_LINE(VENAME(I));

EXIT WHEN I=VENAME.LAST;

I:=VENAME.NEXT(I);

END LOOP;

END;

-----------------------------------------------------

PRINTING THE ARRAY ELEMENTS IN REVERSE USING PRIOR

DECLARE

TYPE EMP_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

VENAME EMP_TYPE;

I NUMBER;

BEGIN

VENAME(1):='SMITH';

VENAME(2):='ALLEN';

VENAME(0):='JAMES';

VENAME(1000):='KING';

I:= VENAME.LAST;

LOOP

DBMS_OUTPUT.PUT_LINE(VENAME(I));

EXIT WHEN I=VENAME.FIRST;

I:=VENAME.PRIOR(I);

END LOOP;

END;