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);
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;
