Home » SQL & PL/SQL » SQL & PL/SQL » value expand to extra 2 digit (merged 3)
icon9.gif  value expand to extra 2 digit (merged 3) [message #410574] Mon, 29 June 2009 01:33 Go to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi to all guru's...here i got doubt about the code that i've made..its simply output the result but not that i've expected

I hav create a proc which will change a given number to extra 1 number. Example like this :

12345 --> 23456
67890 --> 78901

but turn out the number change to 2 extra number like this :

12345 --> 34567
67890 --> 89012

Here i attach my code for u all to review. please tell me if there's anything wrong in my code that i could change to solve it.

Thank you very much

Create Or Replace Procedure Update_Scramble_tbl_num
As
Cursor c1 Is
Select *
From tbl_num;


rec c1%rowtype;

Begin


For rec in c1
Loop

Update tbl_num
Set num_row = translate(num_row,'0123456789','1234567890');


End Loop;

End Update_Scramble_tbl_num;
Re: value expand to extra 2 digit [message #410579 is a reply to message #410574] Mon, 29 June 2009 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
please tell me if there's anything wrong in my code that i could change to solve it.

Does it give you the expected result?
Do you realize that your update statement update EVERY row at each loop?
Why do you use a PL/SQL block and a single statement?

Regards
Michel
Re: value expand to extra 2 digit (merged 3) [message #410593 is a reply to message #410574] Mon, 29 June 2009 03:19 Go to previous messageGo to next message
ds285269
Messages: 10
Registered: June 2009
Location: Mumbai
Junior Member
By using FOR UPDATE.....WHERE CURRENT OF, we can solve the problem.
Re: value expand to extra 2 digit (merged 3) [message #410594 is a reply to message #410593] Mon, 29 June 2009 03:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes, you could use 'Where current of' to make this procedure more complex, or you could make it much more simple, and faster as well:
Create Or Replace Procedure Update_Scramble_tbl_num As
Begin
  Update tbl_num
  Set num_row = translate(num_row,'0123456789','1234567890');
End Update_Scramble_tbl_num;


Now, which seems better?
Re: value expand to extra 2 digit (merged 3) [message #410595 is a reply to message #410594] Mon, 29 June 2009 03:42 Go to previous messageGo to next message
ds285269
Messages: 10
Registered: June 2009
Location: Mumbai
Junior Member
This seems better compare to where current of...
Re: value expand to extra 2 digit (merged 3) [message #410612 is a reply to message #410595] Mon, 29 June 2009 04:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, but why not simply
  Update tbl_num
  Set num_row = translate(num_row,'0123456789','1234567890');
Is PL/SQL a *must* or ...?
Re: value expand to extra 2 digit (merged 3) [message #410616 is a reply to message #410612] Mon, 29 June 2009 04:56 Go to previous messageGo to next message
ds285269
Messages: 10
Registered: June 2009
Location: Mumbai
Junior Member
It's depending on requirement....
Re: value expand to extra 2 digit (merged 3) [message #410620 is a reply to message #410616] Mon, 29 June 2009 05:06 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
It's depending on requirement....
What is that?
Did you read OP's mind?

By
Vamsi
Previous Topic: Analytic function ranking
Next Topic: Export CSV to Chinese characters
Goto Forum:
  


Current Time: Fri Dec 09 00:06:34 CST 2016

Total time taken to generate the page: 0.10932 seconds