create update query using stored procedure in mysql

Question

I have created stored procedure in oracle. But I want to run this procedure in mysql how can I create for loop in mysql .I do not have any idea about for loop in mysql.

Create table student
  (
    Id number,
    Batch varchar2(2),
    Batch_roll_no number 
  )

Create or replace procedure assign_roll_no
Is
 V_roll_no number; 
Begin
For c1 in (select distinct batch from student order by 1)
Loop
Select nvl(Max(batch_roll_no+1),1)
Into v_roll_no
From student
 where batch = c1.batch;
  For c2 in ( select id from batch where batch_roll_no is null and batch= c1.batch
 Order by 1)
 Loop
Update student
set batch_roll_no=v_roll_no
Where id=c2.id
And batch= c1.batch;
V_roll_no:=v_roll_no + 1;
End loop;
End loop;
End;

rohit_agarwal · Accepted Answer · 2013-11-09 18:21:13Z

1

follow this link. It gives simple examples for your doubt. In case, you don't understand the content of the link. Get back to this thread with your doubts.

Loops in MySQL

As far as the answer is concerned, I hope the following gives Pravin a clear picture of the scenario that you're looking at.

DELIMITER $$ 
CREATE PROCEDURE loop_test() 
BEGIN  
WHILE c2 in (select id from batch where batch_roll_no is null and batch= c1.batch Order by 1)
DO  
Update student  
SET batch_roll_no=v_roll_no  
WHERE id=c2.id AND batch= c1.batch;  
V_roll_no:=v_roll_no + 1;  
END WHILE;  
END$$
DELIMITER ;

edited Nov 9, 2013 at 18:21

answered Oct 4, 2013 at 11:52

rohit_agarwal

1601 gold badge1 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joel Over a year ago

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

rohit_agarwal Over a year ago

Thank you @Joel for the suggestion. I have made necessary changes to the answer.

Joel Over a year ago

And then you get +1 from me because of that :) also, I've edited your answer to format code. In the future, consider using 4 spaces to indent your code, it will indicate S.O. to highlight the code automatically. See the pretty colors?

Collectives™ on Stack Overflow

create update query using stored procedure in mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related