logic not meeting requirement- PL-SQL

Posted by Madhavi under Others on 8/24/2010 | Points: 10 | Views : 1457 | Status : [Member] | Replies : 1
Hi

i was stuck up in writing the logic in PL-SQL with cursors. i am writing the rough code below


Procedure xyz

select * from example

for I in c2 loop
....
....
cnt:=cnt+1

if count=30 then
logic to implement
.....
.....

endif


endloop
end xyz

I want to implement some logic to implement for all records of select statement but 30 at a time .

how to implement :
if it is <30, how to implement the logic in "if condition".
Also, if count is some 60, for frist 40 records the logic will implment but for rest 20 records, the condition (count=40) will not satisfy and will not go into logic

Kindly let me know if u need any more information

Thanks
madhavi




Responses

Posted by: Ambily.raj on: 8/31/2010 [Member] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down

You can use the Row_Number() and CTE query to achieve the same.

Following sample shows that the update statement will update the ProductName of the first 4 records in the order of productID.

with productBackUp

as
(
select ROW_NUMBER() over(order by productid) as "ProdCount",* from prodBack
)
update productBackUp
set productName=productName+'##'
where ProdCount<5


Thanks
Ambily

Thanks
Ambily K K
http://ambilykk.com/

Madhavi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response