help with executing a cursor

[minus0]

Hi all,

I am trying to do a simple update using cursors - I get a successful response but the update itself seems to be failing and I am clueless as to whats going wrong. I am including my script below and would appreciate if some one can point me in the right direction.

declare

cursor c1 is select phone_number
from table1 g, table2 c
where g.PHN_SAK = c.PHN_SAK
for update;

myPhn table2.phone_number%type;

begin
open c1;
loop
fetch c1 into myPhn;
exit when c1%notfound;
if length(myPhn) = 8
then
dbms_output.put_line ('AA' || myPhn);
update table2
set phone_number = 'AA' + myPhn
where current of c1;
elsif length(myPhn) = 7
then
dbms_output.put_line ('AAA' || myPhn);
update table2
set phone_number = 'AAA' + myPhn
where current of c1;
end if;
end loop;
close c1;
end;

On execution, I get a "PL/SQL procedure successfully completed." but when I query the table I am trying to update I don't see the desired output. (yes I did commit the changes)

Thanks in advance

 

[sem]

Have you commited your transaction?

Regards, Dima

http://seminihin.narod.ru

 

[amrita418]

A case stmt in the update should do what you need if you all you are after is updating a column in the table. Cursors are expensive, should only be used when necessary.

update   table2 a
set      a.phone_number = (select case when length(b.phone_Number) = 8 
                                       then 'AA' ||  b.phone_number 
                                       when length(b.phone_number) = 7                   
                                       then 'AAA' || b.phone_number 
                                       else b.phone_number end from Table1 b
                           where  b.PHN_SAK = a.PHN_SAK
                                  -- and rownum <= 1)
--where    a.phone_number is null

Also, if you are not sure that the subquery returns max one row you will need to add rownum <= 1 clause.

You can use the additional where clause to update only specific values.

Regards,
AA

 

[minus0]

Yes I did commit my changes @sem

AA - I never used an update statement with a select case - would you please point me to some documentation for the usage of update this way? I tried googling this but ended up with a lot of stuff thats nothing to do with update/select case combination.

Thanks a lot

 

[sem]

Does that query return any rows? Is it key-preserving?

In any case I concur with amrita418: single statement is far more efficient:

update (select phone_number
        from table1 g, table2 c
        where g.PHN_SAK = c.PHN_SAK
        and length(phone_number) in (7,8)) 
set phone_number = phone_number||lpad(phone_number,10,'A')

Regards, Dima

http://seminihin.narod.ru

 

[amrita418]

All we are doing here is setting the value of phone_number depending on the length of the column value. So general documentation on case is what you are looking for. You could do the same with using the decode function.

Something like

decode(length(b.phone_number), 8, 'AA' || b.phone_number,
                               7, 'AAA' || b.phone_number,
                               b.phone_number)

Infact I like sem's idea better than using a case or decode in this particular case.

Regards,
AA