DBMS > Structural Query Language (SQL) > Use of Procedure in Mysql

Suppose we have the following table with data:

create table course_marks( 

s_id int,
marks int null,
gp decimal(5,2) null,
gl varchar(2) null

);

insert into course_marks (s_id,marks) values

(1, 78),
(2, 84),
(3, 46),
(4, 31),
(5, 64)

;

Create a procedure to claculate GPA from the data entered into the course_marks table. The code is as follows:

CREATE PROCEDURE grade(IN st_id INT, OUT st_gpa decimal(5,2))
BEGIN
   DECLARE s_marks INT DEFAULT 0;
   SELECT marks INTO s_marks FROM course_marks WHERE s_id = st_id;
   IF s_marks >= 80 THEN
     SET st_gpa = 4.0;
   ELSEIF s_marks >=70 AND s_marks < 80 THEN
     SET st_gpa = 3.5;
   ELSEIF s_marks >=60 AND s_marks < 70 THEN
     SET st_gpa = 3.0;
   ELSEIF s_marks >=50 AND s_marks < 60 THEN
     SET st_gpa = 2.5;
   ELSE
    SET st_gpa = 0;
   END IF;
END$$
DELIMITER ;

To run the procedure the following commands have to use:

CALL grade(1,@st_gpa);
SELECT @st_gpa;

 

Prev

Aggregate Functions

Next

Simple Example of MySQL Procedure

Feedback

Submit