Using Exists:
In a collection if a user will like to know the existence of an element, this can be done using the collection method Exists that takes in a value and checks for its existence.
An example using the collection categories as follows:
DECLARE
TYPE VTAB IS TABLE OF VARCHAR2 (10) INDEX BY BINARY_INTEGER;
TAB VTAB;
J BINARY_INTEGER:=0;
CURSOR C1 IS SELECT ENAME FROM EMP;
BEGIN
FOR I IN C1 LOOP
J:=J+1;
TAB(J):=I.ENAME;
END LOOP;
DBMS_OUTPUT.PUT_LINE(TAB.FIRST);
END;
Note, in the given example, it displays the index number 1 and not the value. In sequence to shows the value that statement must be re-written as
Dbms_output.put_line(tab(tab.first));
The following example displays other values,
DECLARE
TYPE VTAB IS TABLE OF VARCHAR2 (10) INDEX BY BINARY_INTEGER;
TAB VTAB;
J BINARY_INTEGER:=0;
CURSOR C1 IS SELECT ENAME FROM EMP;
BEGIN
FOR I IN C1 LOOP
J:=J+1;
TAB(J):=I.ENAME;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE TOTAL NUMBER OF RECORDS ARE '||TAB.COUNT);# show the total records#
DBMS_OUTPUT.PUT_LINE('THE FIRST RECORD IS '||TAB(TAB.FIRST));# show the first record#
DBMS_OUTPUT.PUT_LINE('THE LAST RECORD IS '||TAB(TAB.LAST)); # show the last record#
DBMS_OUTPUT.PUT_LINE('THE SECOND RECORD IS '||TAB(TAB.NEXT(TAB.FIRST))); # show the second record#
DBMS_OUTPUT.PUT_LINE('THE LAST BUT PREVIOUS RECORD IS '||TAB(TAB.PRIOR(TAB.LAST))); # show last previous record#
END;
The displays would be as given below:
The total numbers of records are 14
The first record is KING
The last record is MILLER
The second record is BLAKE
The last but previous record is ADAMS
Here KING is the first record. The second record will be displayed as the first record if the record is deleted.
DECLARE
TYPE VTAB IS TABLE OF VARCHAR2 (10) INDEX BY BINARY_INTEGER; # declaration of variable type#
TAB VTAB;
J BINARY_INTEGER:=0;
CURSOR C1 IS SELECT ENAME FROM EMP;
BEGIN
FOR I IN C1 LOOP
J:=J+1;
TAB(J):=I.ENAME;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE TOTAL NUMBER OF RECORDS ARE '||TAB.COUNT); # to show the total number of records#
DBMS_OUTPUT.PUT_LINE('THE FIRST RECORD IS '||TAB(TAB.FIRST));# to show the first record#
DBMS_OUTPUT.PUT_LINE('THE LAST RECORD IS '||TAB(TAB.LAST)); ));# to show the last record#
DBMS_OUTPUT.PUT_LINE('THE SECOND RECORD IS '||TAB (TAB.NEXT (TAB.FIRST ))) ; ));# to show the second record#
DBMS_OUTPUT.PUT_LINE('THE LAST BUT PREVIOUS RECORD IS'||TAB (TAB .PRIOR (TAB.LAST))); # to show the last previous record#
TAB.DELETE(1); # to delete a record#
DBMS_OUTPUT.PUT_LINE('THE TOTAL NUMBER OF RECORDS ARE '||TAB.COUNT); # to show the total number of records#
DBMS_OUTPUT.PUT_LINE('THE FIRST RECORD IS '||TAB(TAB.FIRST)); # to show the first record#
DBMS_OUTPUT.PUT_LINE('THE LAST RECORD IS '||TAB(TAB.LAST)); # to show the last record#
DBMS_OUTPUT.PUT_LINE('THE SECOND RECORD IS '||TAB(TAB .NEXT(TAB. FIRST))); # to show the second record#
DBMS_OUTPUT.PUT_LINE('THE LAST BUT PREVIOUS RECORD IS '||TAB(TAB .PRIOR (TAB.LAST))); # to show the last previous record#
END;
The output would now be,
The total numbers of record are 14
The first record is KING
The last record is MILLER
The second record is BLAKE
The last but previous record is ADAMS
The total numbers of records are 13
The first record is BLAKE
The last record is MILLER
The second record is CLARK
The last but previous record is ADAMS