Home » SQL & PL/SQL » SQL & PL/SQL » Compare and update Stored proc
| Compare and update Stored proc [message #622032] |
Wed, 20 August 2014 10:16  |
 |
deepugun
Messages: 6
Registered: January 2014
|
Junior Member |
|
|
Hello All,
I am trying to come up with a pl\sql script which would allow me to compare a record(based on few conditions) with other records in the same table and if there is a match found , i would have to update the first record with few of the fields from the second matched record and then flad the second matched record. When i run this script it just never comeback i.e. it is constantly in a loop.If possible please help in debugging the issue.
DECLARE
v_Email varchar2(400);
v_Addressline1 varchar2(400);
v_Addressline2 varchar2(400);
v_city varchar2(400);
v_state varchar2(400);
v_postalcode varchar2(400);
V_id number;
v1_Email varchar2(400);
v1_Addressline1 varchar2(400);
v1_Addressline2 varchar2(400);
v1_city varchar2(400);
v1_state varchar2(400);
v1_postalcode varchar2(400);
V1_id number;
v1_Firstname varchar2(400);
v_count number;
upd_secfirstname varchar2(400);
upd_id number;
/* First cursor */
CURSOR get_Lib IS
SELECT gun.EMAIL,gun.Addressline1,gun.Addressline2,gun.city,gun.state,gun.postalcode,gun.id
FROM gun;
/* Second cursor */
CURSOR get_lib_nxt IS
SELECT EMAIL,Addressline1,Addressline2,city,state,postalcode,id,Firstname
FROM gun where ID <> V_id ;
BEGIN
/*Begin
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE Lib_temp
(
Email varchar2(400),
Addressline1 varchar2(400) ,
Addressline2 varchar2(400),
city varchar2(400),
state varchar2(400),
postalcode varchar2(400),
id number,
secondaryFirstname varchar2(400)
)';
end;
select count(*) into v_count from lib_temp;
DBMS_OUTPUT.PUT_LINE(V_Count); */
-- Open first cursor
OPEN get_Lib;
LOOP
FETCH get_Lib INTO v_Email, v_Addressline1,v_Addressline2, v_city, v_state, v_postalcode, v_id;
-- Open second cursor
OPEN get_lib_nxt;
LOOP
FETCH get_lib_nxt INTO v1_Email, v1_Addressline1,v1_Addressline2, v1_city, v1_state, v1_postalcode, v1_id,
v1_firstname;
IF
(NVL(v1_Addressline1,'Unidentified') = NVL(v_Addressline1,'Unidentified') and NVL(v1_Addressline2,'Unidentified') = NVL(v_Addressline2,'Unidentified') and NVL(v1_city,'Unidentified')=NVL(v_city,'Unidentified') and NVL(v1_state,'Unidentified')=NVL(v_state,'Unidentified') and NVL(v1_postalcode,'Unidentified') = NVL(v_postalcode,'Unidentified' )) or NVL(V1_Email,'Unidentified') = NVL(V_email,'Unidentified')
then
Insert into lib_temp VALUES(v1_Email, v1_Addressline1,v1_Addressline2, v1_city, v1_state, v1_postalcode,
v1_id,v1_firstname);
end if;
END LOOP;
CLOSE get_lib_nxt;
select count(*) into v_count from lib_temp;
if v_count = 1 then
select secondaryFirstname,id into upd_secfirstname , upd_id from lib_temp;
update Gun set secondary_contact_firstname = upd_secfirstname where id = v_id ;
delete from gun where id = upd_id;
end if;
if v_count > 1 then
Update
Gun set secondary_contact_lastname = 'More Than 1 record' where id = V_id;
Update
Gun set secondary_contact_lastname = 'More Than 1 record' where id in (select id from lib_temp);
end if;
END LOOP;
CLOSE get_lib;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END ;
Thanks
|
|
|
|
| Re: Compare and update Stored proc [message #622033 is a reply to message #622032] |
Wed, 20 August 2014 10:20  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Firstly, please format your code and use code tags. It is almost impossible to read.
And, please read about WHEN OTHERS. It has had been a never ending topic of discussion in this forum as well as over the web.
Regards,
Lalit
|
|
|
|
|
|
| Re: Compare and update Stored proc [message #622073 is a reply to message #622035] |
Wed, 20 August 2014 14:15 |
 |
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
|
Senior Member |
|
|
I agree with cookiemonster, your LOOP constructs need an exit. What I don't understand is that since you are using named (explicit) cursors, why are not using a Cursor FOR Loop? The CURSOR FOR LOOP will automatically exit after the last record is fetched from the cursor. Your code could be modified as follows:
...
-- Open first cursor
FOR rec1 IN GET_LIB LOOP
-- Open second cursor
FOR rec2 IN GET_LIB_NEXT LOOP
IF ( nvl(rec1.addressline1,'Unidentified') = nvl(rec2.addressline1,'Unidentified')
AND nvl(rec1.addressline2,'Unidentified') = nvl(rec2.addressline2,'Unidentified')
AND nvl(rec1.city,'Unidentified') = nvl(rec2.city,'Unidentified')
AND nvl(rec1.state,'Unidentified') = NVL(rec2.state,'Unidentified')
AND NVL(rec1.postalcode,'Unidentified') = NVL(rec2.postalcode,'Unidentified' ))
OR NVL(rec1.Email,'Unidentified') = NVL(rec2.email,'Unidentified') ) THEN
INSERT INTO lib_temp
VALUES(rec1.Email, rec1.Addressline1,rec1.Addressline2, rec1.city
,rec1.state, rec1.postalcode, rec1.id,rec1.firstname);
END IF;
END LOOP;
select count(*) into v_count from lib_temp;
...rest of your code here...
END LOOP;
See how much easier that is? No extra variables, no extra overhead, and each LOOP automatically exits when the last record is processed. Now, as to your code...you realize that your inner loop has to exit before your outer loop will interate to the next row right? This means for every row in OUTER LOOP, it will compare that one row with ALL of the records in the INNER LOOP. This is not a very good design. A better option would be to write a single SQL statement that will return the differences and then you can simply insert those different records into your LIB_TEMP table.
Craig  ...
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 20:53:32 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
