Home

cursor%NOTFOUND should be used instead of NOT cursor%FOUND

Description

    The PL/SQL code rule "cursor%NOTFOUND should be used instead of NOT cursor%FOUND" states that when checking if a cursor has returned any rows, the cursor%NOTFOUND attribute should be used instead of the NOT cursor%FOUND attribute. This is because the NOT cursor%FOUND attribute can return an unexpected result if the cursor has returned no rows. Using the cursor%NOTFOUND attribute will ensure that the correct result is returned.

Key Benefits

  • Reduced complexity: Using the "cursor%NOTFOUND" rule instead of "NOT cursor%FOUND" reduces the complexity of the code.
  • Improved performance: Using "cursor%NOTFOUND" instead of "NOT cursor%FOUND" improves the performance of the code.
  • Increased readability: Using the "cursor%NOTFOUND" rule instead of "NOT cursor%FOUND" increases the readability of the code.

 

Non-compliant Code Example

DECLARE
CURSOR cur_dept (departmentId INTEGER) RETURN departments%ROWTYPE IS  -- Define cur_dept,
    SELECT * FROM departments              -- repeating return type
    WHERE department_id = departmentId;	
	
BEGIN
  OPEN cur_dept;
  FETCH cur_dept INTO temp1;
  IF NOT cur_dept%FOUND THEN  --Non compliant code (Cursor is using "NOT cursor%FOUND" instead of "cursor%NOTFOUND" )
    DBMS_OUTPUT.PUT_LINE('Not Found !!');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Department Found: ' || temp1);
  END IF;
  CLOSE cur_dept;
END;

Compliant Code Example

DECLARE
CURSOR cur_dept (departmentId INTEGER) RETURN departments%ROWTYPE IS  -- Define cur_dept,
    SELECT * FROM departments              -- repeating return type
    WHERE department_id = departmentId;
	
BEGIN
  OPEN cur_dept;
  FETCH cur_dept INTO temp1;
  IF cur_dept%NOTFOUND THEN  --Compliant code (Cursor is using "cursor%NOTFOUND" )
    DBMS_OUTPUT.PUT_LINE('Not Found !!');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Department Found: ' || temp1);
  END IF;
  CLOSE cur_dept;
END;
Visual Expert 2024
 VEPLSQLRULE115