Table of Contents

Related Articles

How To Use Oracle SQL NOTFOUND (6 Example %NOTFOUND)

Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp
Share on pinterest
Share on print
Oracle SQL NOTFOUND

Oracle PL/SQL cursors are used to fetch records from a table. Oracle SQL NOTFOUND is used to check whether has any row been fetched. Cursors are the most commonly used way to fetch records from a table. But we don’t know cursor fetched any records. So, we have to loop it but using Oracle SQL NOTFOUND possible to find whether the cursor has some records or not.

All the explicit cursor and cursor variables in these four attributes %FOUND%ISOPEN %NOTFOUND, and %ROWCOUNT. Other that this SQL the cursor has another two attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, use with the FORALL statement.

How  SQL%NOTFOUND Works

  • For the Cursors Oracle NOTFOUND return TRUE OR FALSE as a return value
  • When a cursor fetches at least a single row then NOTFOUND returns value as FALSE.
  • Also, the cursor does not fetch any rows (e.g., the result set doesn’t have any records), and NOTFOUND evaluates to TRUE.

Let’s discuss some basic examples and how we have used SQL%NOTFOUND in different places. %NOTFOUND This is the opposite of %FOUND. This return t TRUE if an INSERT, UPDATE, or DELETE statement affected no rows or a SELECT INTO statement returned no rows. else, it returns FALSE.

NOTFOUND in a Simple Cursor

--
DECLARE
  CURSOR c_emp IS  SELECT emp_name FROM employees where emp_id=’1000’;
  v_emp_name employees. emp_name%TYPE;
BEGIN
  OPEN c_emp;  
  FETCH c_emp INTO v_emp_name;
IF c_emp%NOTFOUND THEN
   DBMS_OUTPUT.PUT_LINE('Not found');
  ELSE
   DBMS_OUTPUT.PUT_LINE( 'Employee Name: ' || v_emp_name ||’found'));
  END IF;

  END LOOP;
  CLOSE c_emp;
END;
--

In this example, I would like to add steps on how this executes

  • Create a cursor first with the required values from the employee table.
  • OPEN the cursor for execution.
  • Fetch records from the Cursor and then assign values to our declared variable.
  • Check whether using Oracle SQL NOTFOUND is there any records in the resultset.
  • If records exist execute the ELSE part otherwise execute the IF part.

Let’s dig into some different examples. Like below oracle SQL NOTFOUND has different usages. Let’s see what are the types of usages.

How NOTFOUND is Used:

  1. Cursor Looping: In a cursor loop, %NOTFOUND can be used in different ways. As we explain in the above example, we can just check cursor can find any record. Also, when able to use loop exit condition to determine if there are no more rows to fetch.
  2. Conditional Processing: %NOTFOUND can be used to check whether the condition is fulfilled according to the query. The query is the condition and after fetching the query we can use Oracle SQL NOTFOUND can find any records.
  3. Error Handling: %NOTFOUND can be used to handle errors or ignore the execution of some unwanted code blocks by adding Oracle SQL NOTFOUND condition.

1. Using %NOTFOUND in a Simple Cursor Loop:

--
DECLARE
  CURSOR c_employee IS
    SELECT employee_id, first_name, last_name FROM employee_table WHERE department_id = 100;
  v_emp c_employee%ROWTYPE;
BEGIN
  OPEN c_employee;
  LOOP
    FETCH c_employee INTO v_emp;
    EXIT WHEN c_employee%NOTFOUND; -- Exit loop when no more rows are fetched
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp.employee_id || ', Name: ' || v_emp.first_name || ' ' || v_emp.last_name);
  END LOOP;
  CLOSE c_employee;
END;
--

In this example, a cursor c_employee is for fetching records from employee_table. when looping through the results set EXIT WHEN c_employee%NOTFOUND use to return from the loop if there are no more records in the loop.

2. %NOTFOUND with a Conditional Statement:

In this example simply fetch records from the cursor according to the query. Then we can write different code blocks according to the whether condition is fulfilled or not. Add your condition to the query. Then cursor fetches records according to the query. If there are matching records exist means the condition is fulfilled else the condition fails.

--
DECLARE
  CURSOR c_employee IS
    SELECT employee_id, first_name, last_name FROM employee_table WHERE department_id = ‘HR’;
  v_emp c_employee%ROWTYPE;
BEGIN
  OPEN c_employee;
  FETCH c_employee INTO v_emp;
  IF c_employee%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employees found for department Human Resource');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp.employee_id || ', Name: ' || v_emp.first_name || ' ' || v_emp.last_name);
  END IF;
  CLOSE c_employee;
END;
--

3. %NOTFOUND with Dynamic SQL

This is an extended example of an earlier one we can have dynamic queries by binding values or concatenation string query. Execution happens as we discussed earlier. But you can have different queries according to the parameter or conditional query. In this example, we just change the parameter, but you can create a different String as a query.

--
DECLARE
  v_dept_id VARCHAR := ‘HR’;
  v_sql VARCHAR2(1000) := 'SELECT * FROM employee_table WHERE department_id = :dept_id';
  CURSOR c_employee IS
    EXECUTE IMMEDIATE v_sql USING v_dept_id;
  v_emp employee_table%ROWTYPE;
BEGIN
  OPEN c_employee;
  FETCH c_employee INTO v_emp;
  IF c_employee%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employees found for department Human Resource' || v_dept_id);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp.employee_id || ', Name: ' || v_emp.first_name || ' ' || v_emp.last_name);
  END IF;
  CLOSE c_employee;
END;
--

Here, a dynamic SQL statement is used to fetch employees from a department and test whether Oracle SQL NOTFOUND any records and execute code block according to the condition.

4. %NOTFOUND in a Cursor FOR Loop

--
BEGIN
  FOR emp_rec IN (SELECT first_name, last_name FROM employee_table WHERE department_id = 'HR') LOOP
    DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;
  
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employees found for department Human Resourse');
  END IF;
END;

--

This example is different because first go through the loop and do the required functionalities then again if you need to check if is there record found on loop then Oracle SQL%NOTFOUND use to test that condition. Earlier use cursor%NOTFOUND but now use SQL%NOTFOUND in condition.

5. NOTFOUND with BULK COLLECT:

In this example collect data into an array first then insert all into the backup table in a single call. But c_ employee%NOTFOUND used to test cursor is empty or not. This example exit loop if data is not available in the cursor.

--
DECLARE
 CURSOR c_ employee IS
 SELECT employee_id, first_name, last_name FROM employee_table WHERE department_id = ‘HR’;;

 TYPE fetch_array IS TABLE OF c_ employee %ROWTYPE;
 a_array fetch_array;
BEGIN
  OPEN c_ employee;
  LOOP
    FETCH c_ employee BULK COLLECT INTO a_array LIMIT 1000;

    FORALL i IN 1..a_array.COUNT
    INSERT INTO employee_bk_table VALUES s_array(i);

    EXIT WHEN c_ employee%NOTFOUND;
  END LOOP;
  CLOSE c_ employee;
  COMMIT;
END;
/
--

Summary

By utilizing Oracle SQL NOTFOUND, you can write logical code blocks by testing the cursor resultset. Without adding exceptions or validation directly check cursor fetches any row or not. The NOTFOUND attribute is commonly used in PL/SQL cursor loops to iterate through result sets and process each row until the end of the result set is reached.

Ref https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/13_elems48.htm

Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp
Share on pinterest
Share on print

Leave a Comment

Your email address will not be published. Required fields are marked *

Related Posts

You May Like
Subscribe to our Newsletter