Mastering PL/SQL Cursors: A Practical Guide to Handling NO_DATA_FOUND

Here we are common problem in PLSQL. We try find answer for one common question in programing databases. Is the specyfic record exist in other table or not exist? Some examples below show how to menage this problem:

DECLARE
    VAR_SUPP_NM VARCHAR2(100);
    VAR_SUPP_ID  NUMBER := 1;
    WHILE_VAR CHAR := 'Y';
BEGIN
  SELECT SUPP_NM
    INTO VAR_SUPP_NM
    FROM TEST.SUPPLIER
   WHERE SUPP_ID = VAR_SUPP_ID;
 DBMS_OUTPUT.PUT_LINE('DATA FOUND');
exception
  when no_data_found then 
  	-- here some code when data ont exist
  DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND');
END;
/

In line 11 and 14 we have place to insert own code. Other solution we found when we use define explicit cursor:

CREATE FUNCTION f_get_name (ip_emp_id IN NUMBER) RETURN VARCHAR2
AS
CURSOR c IS SELECT ename FROM emp WHERE emp_id = f_get_name.ip_emp_id;
lv_ename emp.ename%TYPE;
BEGIN
  OPEN c;
  FETCH c INTO lv_ename;
  IF (SQL%NOTFOUND) THEN
     RAISE NO_DATA_FOUND;
  ENDIF;
  FETCH c INTO lv_ename;
  IF (SQL%FOUND) THEN
    RAISE TOO_MANY_ROWS;
  ENDIF;
  CLOSE c;
  RETURN lv_ename;
END;
/

CREATE OR REPLACE PROCEDURE refresh_store_feed AS
TYPE prod_array IS TABLE OF store_products%ROWTYPE INDEX BY BINARY_INTEGER;
l_prod prod_array;
CURSOR c IS
  SELECT product
    FROM listed_products@some_remote_site;
BEGIN
  OPEN C;
  LOOP
    FETCH C BULK COLLECT INTO l_prod LIMIT 100;
  FOR i IN 1 .. l_csi.COUNT
    LOOP
     /* ... do some procedural code here that cannot be done in SQL to l_csi(i) ... */
    END LOOP;
  FORALL i IN 1 .. l_csi.COUNT
    INSERT INTO store_products (product) VALUES (l_prod(i));
    EXIT WHEN c%NOTFOUND;
  END LOOP;
CLOSE C;
END;
/

REF Cursors in Brief

Though there are two other sections in this chapter that cover REF cursors more fully (“Static REF Cursors” and “Dynamic REF Cursors”), a brief overview regarding these types of cursors warrants mention in this section due to their structure: they must be explicitly opened, fetched from, and closed. If you are returning a result set to a client, a great way to avoid lots of repetitive code is to use a REF cursor. Essentially, the cursor you open usually depends on what input you receive from the requesting client. Listing 10-4 provides a brief example of a REF cursor scenario in PL/SQL:

DECLARE
prod_cursor sys_refcursor;
BEGIN
  IF ( input_param = 'C' )
  THEN
    OPEN prod_cursor FOR
    SELECT * FROM prod_concepts
      WHERE concept_type = 'COLLATERAL'
        AND concept_dt < TO_DATE( '01-JAN-2003', 'DD-MON-YYYY');
  ELSE
    OPEN prod_cursor FOR
    SELECT * FROM prod_concepts
      WHERE concept_category = 'ADVERTISING';
  END IF;
LOOP
END;
/

BEGIN
    FOR x IN (SELECT * FROM dual) LOOP ... END LOOP;
  END;
DECLARE
  CURSOR c IS SELECT * FROM dual;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO …
    EXIT WHEN c%NOTFOUND;
      …
    END LOOP;
    CLOSE c;
  END;
  BEGIN
    SELECT *INTO ...FROM dual;
    END;
  DECLARE
    CURSOR c IS SELECT * FROM dual;
    l_rec dual%ROWTYPE;
  BEGIN
  OPEN c;
  FETCH c INTO l_rec;
  IF (SQL%NOTFOUND)
  THEN
    RAISE NO_DATA_FOUND;
  END IF;
  FETCH c INTO l_rec;
  IF (SQL%FOUND)CHAPTER 
  THEN
    RAISE TOO_MANY_ROWS;
  END IF;
  CLOSE c;
END;
/