Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Oracle check if value exists in associative array

Oracle check if value exists in associative array:

Use EXISTS collection method to check if any value exists in associative array or not.

--Oracle check if value exists in associative array--

SET SERVEROUTPUT ON;
DECLARE
  TYPE T_NUM IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  V_NUM T_NUM;
BEGIN
  V_NUM(1) := 1;
  V_NUM(2) := 3;
  V_NUM(3) := 5;
  IF V_NUM.EXISTS(1) THEN
    DBMS_OUTPUT.PUT_LINE('Element 1 exists.');
  END IF;
  IF V_NUM.EXISTS(4) THEN
    DBMS_OUTPUT.PUT_LINE('Element 4 exists.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Element 4 does not exists.');
  END IF;
  IF V_NUM.EXISTS(2) = TRUE THEN
    DBMS_OUTPUT.PUT_LINE('Element 2 exists.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Element 2 does not exists.');
  END IF;
END;
/
Oracle check if value exists in associative array
Oracle check if value exists in associative array: Output

Use PLSQL block to check if any value exists in the associative array or not

--Oracle check if value exists in associative array: Example

DECLARE
  TYPE T_FNAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_FNAME T_FNAME;
  TYPE T_LNAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_LNAME T_LNAME;
BEGIN
  SELECT FIRST_NAME,
         LAST_NAME
  BULK COLLECT INTO V_FNAME,
                    V_LNAME
  FROM EMPLOYEES
  WHERE LAST_NAME LIKE '%King%';
  --
  FOR I IN 1..V_LNAME.COUNT
  LOOP
    IF V_LNAME(I) = 'King' THEN
      DBMS_OUTPUT.PUT_LINE('King exists in the Employee list -> '||V_FNAME(I)||' '||V_LNAME(I));
    END IF;
  END LOOP;
END;
/
Oracle check if value exists in associative array: Example

RELATED TOPICS:

Leave a Comment

Your email address will not be published.