I have the following sample of code:
Declare
CURSOR c_contacts_cursor
IS
SELECT OUTLET_CODE,
OUTLET_NAME,
CONTACT_NAME,
MOBILE_NO,
ALT_NO,
ADDRESS1,
ADDRESS2,
CITY,
POSTCODE,
EMAIL
FROM SYNC_CONTACTS
WHERE (source = 'W' OR SOURCE = 'T') AND SURVIVAL_FLAG = 'Y';
TYPE temp_string IS TABLE OF VARCHAR2 (2000);
V_OUTLET_CODE temp_string;
V_OUTLET_NAME temp_string;
V_CONTACT_NAME temp_string;
V_MOBILE_NO temp_string;
V_ALT_NO temp_string;
V_ADDRESS1 temp_string;
V_ADDRESS2 temp_string;
V_CITY temp_string;
V_POSTCODE temp_string;
V_EMAIL temp_string;
BEGIN
OPEN c_contacts_cursor;
FETCH c_contacts_cursor BULK COLLECT INTO
V_OUTLET_CODE,
V_OUTLET_NAME,
V_CONTACT_NAME,
V_MOBILE_NO,
V_ALT_NO,
V_ADDRESS1,
V_ADDRESS2,
V_CITY,
V_POSTCODE,
V_EMAIL;
CLOSE c_contacts_cursor;
FOR indx IN 1 .. V_OUTLET_CODE.count
LOOP
--xecutable statements on V_OUTLET_CODE(indx) and the like
END LOOP;
End;
I used the structure above extensively in many functions and procedures all related to some application and working fine. However, I started to review my code and I was thinking there might be better way of doing it. One of the methods I found is cursor for loop: http://www.techonthenet.com/oracle/loops/cursor_for.php
and http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/cursor_for_loop_statement.htm#LNPLS1155
I'm trying to understand the pros and cons of each methods. I figured out the following (notating my approach is A, and cursor for loop is B
1) A is faster as it uses BULK COLLECT INTO as opposed to B that is implicitly opens, fetch one row and close for each loop round.
2) A is a bit messy, much more code as opposed to B that is readable, tidy up.
First, I would like to know, if my conclusions are correct or not?
Second, what other differences could be between both?
Third, is there any other approach I'm not aware of? Or what enhancement could be applied to my approach if there is any?