MySQL Forums
Forum List  »  Stored Procedures

stored procedure executing for infinite time
Posted by: amarendra sahoo
Date: December 09, 2015 03:52AM

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `calculateGraceMarkTwo`()
BEGIN
-- declare VARIABLES as per the below CURSOR
DECLARE v_StuID int(11) DEFAULT 0;
DECLARE v_StuFname VARCHAR (255) DEFAULT "";
DECLARE v_StuLname VARCHAR (255) DEFAULT "";
DECLARE v_StuCID int(11) DEFAULT 0;
DECLARE v_StuMrk int(11) DEFAULT 0;
-- declaration for not found handler
DECLARE v_finished INTEGER DEFAULT 0;
-- declare one variable to capture the full mark and passmark of the SUBJECT
DECLARE v_CFullmark INTEGER DEFAULT 0;
DECLARE v_CPassmark INTEGER DEFAULT 0;
DECLARE v_Grace FLOAT DEFAULT 0.00;
-- declare variable to insert into final table student_mark
DECLARE SName VARCHAR(255) default "";
DECLARE CName VARCHAR(255) DEFAULT "";
DECLARE IMark FLOAT DEFAULT 0.00;
DECLARE TMark FLOAT DEFAULT 0.00;
DECLARE Result VARCHAR(10) default "";
DECLARE Remark VARCHAR(25) default "";

-- Declare Cursor for this
DECLARE student_cursor CURSOR FOR select s.Student_Id, s.Student_Fname, s.Student_Lname,s.Course_Id,s.Mark_Obtained,c.Full_Mark , c.Pass_Mark,c.Grace_Per,c.Course_Name
from student s ,course c
where s.Course_id = c.Course_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished =1;

-- Open the CURSOR

OPEN student_cursor;

-- now start the LOOP

calculate_mark: LOOP

FETCH student_cursor INTO v_StuID,v_StuFname,v_StuLname,v_StuCID,v_StuMrk,v_CFullmark,v_CPassmark,v_Grace,CName;

SET SName = CONCAT(v_StuFname ," ", v_StuLname);
-- SET CName = (select Course_Name FROM course where Course_Id = v_StuCID);
SET IMark = v_StuMrk+(v_CFullmark * (v_Grace/100));

IF IMark >= v_CPassmark THEN
SET Result = 'PASS';
SET Remark = 'Grace Applied';
INSERT INTO student_mark(Stu_Name, Course_Name,Mark_Secured,Result,Remarks) VALUES(SName, CName,IMark,Result,Remark);
ELSE
SET Result = 'FAIL';
SET Remark = 'Grace Applied';
INSERT INTO student_mark(Stu_Name, Course_Name,Mark_Secured,Result,Remarks) VALUES(SName, CName,IMark,Result,Remark);
END IF;

END LOOP ;

CLOSE student_cursor;
END

Options: ReplyQuote


Subject
Views
Written By
Posted
stored procedure executing for infinite time
2360
December 09, 2015 03:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.