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
NOTFOUNDis 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:
- Cursor Looping: In a cursor loop, %
NOTFOUNDcan 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. - 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.
- Error Handling: %NOTFOUND can be used to handle errors or ignore the execution of some unwanted code blocks by adding Oracle SQL
NOTFOUNDcondition.
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
