Re: pl/sql best practices
Date: 1 Feb 2002 00:33:40 -0800
Message-ID: <dee17a9f.0202010033.6439b05_at_posting.google.com>
Martin
"Never use implicit cursors" was a rule drummed into Forms developers
way back in Oracle 7. The reason was that Oracle has to perform a
second fetch to satisfy itself that there was only one record
retrieved by the cursor. So across networks, it was considered that
this was inefficient. The alternative was an explicit cursor:-
DECLARE
CURSOR c1 IS
SELECT SYSDATE
FROM dual;
v_date DATE;
BEGIN
OPEN c1;
FETCH c1 INTO v_date;
IF c1%NOTFOUND THEN
...raise error or re-assign v_date - whatever your logic is.
END IF;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
IF c1%ISOPEN THEN
CLOSE c1;
END IF;
RAISE;
END;
/
I subscribed to this coding standard for over two years. HOWEVER, things have moved on a lot since those days and implicit cursors can be significantly more efficient than the above. I advise you strongly to visit asktom.oracle.com and search for "implicit vs explicit" or something like that. He has some excellent discussions and examples of why implicit cursors are BETTER than explicit. His mantra goes something like:-
"implicit cursors RULE"
"they are easier and quicker to code"
"less code means less interpretation"
"they are more efficient (and this is proven by tkprof reports)".
When you have an update,insert,delete within PL/SQL - you are using implicit cursors. Explicit cursors are great when you want to control the logic using attributes such as %FOUND, %COUNT, %NOTFOUND, but if implicit cursors can be used, I would suggest you use them. I use them WHENEVER I CAN and remember that I NEVER used to code SELECT .. INTO - I used to think it was the devil. So I have been converted.
For PL/SQL, you can do no wrong with a Steven Feuerstein book.
Hope this helps.
Adrian Received on Fri Feb 01 2002 - 09:33:40 CET