之前，我还真没想过用rownum来提高性能。至于用游标循环，倒是很简单。
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:
BEGIN
SELECT COUNT(*) INTO the_count
FROM company
WHERE name LIKE :cllr.cname;
IF the_count = 1
THEN
SELECT company_id INTO the_id
FROM company
WHERE name = :cllr.cname;
return_value (the_id);
ELSIF the_count > 1
THEN
show_list;
ELSIF the_count = 0
THEN
display (’No matches found.’);
END IF;
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:
DECLARE
CURSOR comp_cur IS …;
comp_rec comp_cur%ROWTYPE;
BEGIN
OPEN comp_cur;
FETCH comp_cur INTO comp_rec;
IF comp_cur%NOTFOUND
THEN
display (’No match found.’);
ELSE
FETCH comp_cur INTO comp_rec;
IF comp_cur%FOUND
THEN
show_list;
ELSE
:employee.company_id :=
comp_rec.company_id;
:employee.company_nm :=
comp_rec.company_nm;
END IF;
END IF;
CLOSE comp_cur; END;
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(*)?
SELECT COUNT(*) INTO my_count
FROM emp
WHERE empno = emp_in
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?
文章 (RSS)