PL/SQL Best Practices [1] [Cursor Management]- COUNT(*)

Avoid COUNT(*) Unless Count is Required
Consider the following requirement:
Get the ID for the company which matches specified name.
If no match, display error.
If more than one match, show list.
If single match, return ID.
How do you handle this situation? The most obvious and common solution is to use COUNT to figure out how many matches you have for the name, and then execute logic from there. But are you really answering the "right question?"
When you do a COUNT, you find out how many matches there are. If you don't really care how many, but just that there is more than one match, COUNT is overkill. For example:

 1 BEGIN
 2    SELECT COUNT(*) INTO the_count
 3      FROM company
 4     WHERE name LIKE :cllr.cname;
 5    IF the_count = 1
 6    THEN
 7       SELECT company_id INTO the_id
 8         FROM company
 9        WHERE name = :cllr.cname;
10       return_value (the_id);
11    ELSIF the_count > 1
12    THEN
13       show_list;
14    ELSIF the_count = 0
15    THEN
16       display ('No matches found.');
17    END IF;  
18 END;

Notice a second SELECT has to be executed to retrieve the single ID. In this case, we don’t have to worry about NO_DATA_FOUND, since COUNT returns 0 if no rows found.

A better solution is to use two fetches instead:

 1 DECLARE
 2    CURSOR comp_cur IS ;
 3    comp_rec comp_cur%ROWTYPE;
 4 BEGIN
 5    OPEN comp_cur;
 6    FETCH comp_cur INTO comp_rec;
 7    IF comp_cur%NOTFOUND
 8    THEN
 9       display ('No match found.');
10    ELSE
11       FETCH comp_cur INTO comp_rec;
12       IF comp_cur%FOUND
13       THEN
14          show_list;
15       ELSE
16          :employee.company_id :=
17             comp_rec.company_id;
18          :employee.company_nm :=
19             comp_rec.company_nm;
20       END IF;
21    END IF;  
22    CLOSE comp_cur; END;
23 
24 

Advantages of Dual Fetch Approach
Absolute minimum of CPU and memory employed to meet requirement.
Avoids duplication of SQL statement.
You would have to maintain the WHERE clause in both as there were database changes.
Be sure to read your requirements carefully and decide on the most sensible approach.
What seems at first glance to be the most sensible solution may turn out to have major drawbacks.

What about an “Efficient” COUNT(*)?

1 SELECT COUNT(*) INTO my_count
2   FROM emp
3  WHERE empno = emp_in
4    AND ROWNUM < 2;

In the above implicit cursor, ROWNUM is used to make sure that only one row is retrieved. This query will not ever return a value greater than one, and it will return 0 if no matches are found. A good fit?

There are several drawbacks to this approach:

The SQL layer still performs two fetches.
You are “tricking” PL/SQL. You don’t really want to do a COUNT, you just want to avoid writing the exception handlers for a standard non-COUNT query.
Clever code is less readable and maintainable. Use the most straightforward approach.