MySQL Stored Procedure for Select, Insert, Update, and Delete

Create Table

This tutorial require to two tables.

mysql> CREATE TABLE student
    -> (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(200) NOT NULL,
    -> score DOUBLE NOT NULL,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.43 sec)

mysql> CREATE TABLE student_status
    -> (
    -> ss_id INT NOT NULL AUTO_INCREMENT,
    -> student_id INT NOT NULL,
    -> status enum('pass','fail'),
    -> PRIMARY KEY(ss_id)
    -> );

Insert Records

mysql> INSERT INTO student VALUES(null,'Jone Mark',35),
    -> (null,'Bob Maat',80),
    -> (null,'Paul Chao',85),
    -> (null,'Marry',74),
    -> (null,'Christ',63);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM student;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Jone Mark |    35 | 
|  2 | Bob Maat  |    80 | 
|  3 | Paul Chao |    85 | 
|  4 | Marry     |    74 | 
|  5 | Christ    |    63 | 
+----+-----------+-------+
5 rows in set (0.00 sec)

store procedure to get all student

mysql> DELIMITER $$
mysql> CREATE PROCEDURE GetAllStudents()
    -> BEGIN
    ->      SELECT * FROM student;
    -> END 
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL GetAllStudents();
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Jone Mark |    63 | 
|  2 | Bob Maat  |    80 | 
|  3 | Paul Chao |    85 | 
|  4 | Marry     |    74 | 
|  5 | Christ    |    63 | 
+----+-----------+-------+
5 rows in set (0.00 sec)

store procedure to delete record

mysql> DELIMITER $$
mysql> CREATE PROCEDURE DelStudents(IN studentID INT)
    -> BEGIN
    ->      DELETE FROM student WHERE id= studentID;
    ->      SELECT * FROM student;
    -> END 
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Stored procedure for counting the total student using SELECT .. INTO

mysql> CREATE PROCEDURE COUNT_ALL()
    -> BEGIN
    ->      DECLARE total_student INT DEFAULT 0;
    ->      SELECT COUNT(*) INTO total_student FROM student ;
    ->      SELECT total_student;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL COUNT_ALL();
+---------------+
| total_student |
+---------------+
|             5 | 
+---------------+
1 row in set (0.01 sec)

Using cursor, OPEN, FETCH, CLOSE for stored procedure

mysql> DELIMITER $$
mysql> CREATE PROCEDURE COUNT_ALL2()
    -> BEGIN
    ->      DECLARE total_student INT DEFAULT 0;
    ->      DECLARE total_cursor CURSOR 
    ->             FOR SELECT COUNT(*) FROM student;  
    ->      OPEN total_cursor;
    ->      FETCH total_cursor INTO total_student;
    ->      CLOSE totaL_cursor;
    ->      SELECT total_student;
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CALL COUNT_ALL2();
+---------------+
| total_student |
+---------------+
|             5 | 
+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Return value for store procedure

mysql> DELIMITER $$
mysql> CREATE PROCEDURE COUNT_ALL3(OUT Total INT)
    -> BEGIN 
    ->      DECLARE myTotal CURSOR
    ->          FOR SELECT COUNT(*) FROM student;
    ->      OPEN myTotal;
    ->      FETCH myTotal INTO Total;
    ->      CLOSE myTotal;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL COUNT_ALL3(@total_student);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @total_student;
+----------------+
| @total_student |
+----------------+
| 5              | 
+----------------+
1 row in set (0.00 sec)

mysql> 

How to create stored procedure for both in and out parameters

mysql> CREATE PROCEDURE COUNT_BY_Score(INOUT Total INT)
    ->     BEGIN 
    ->       DECLARE myTotal CURSOR
    ->           FOR SELECT COUNT(*) FROM student GROUP BY score HAVING score = Total;
    ->         OPEN myTotal;
    ->        FETCH myTotal INTO Total;
    ->          CLOSE myTotal;
    ->      END;
    -> $$
mysql>DELIMITER ;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @score=63;
+-----------+
| @score=63 |
+-----------+
|      NULL | 
+-----------+
1 row in set (0.00 sec)

mysql> SET @score=63;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL COUNT_BY_Score(@score);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @score;
+--------+
| @score |
+--------+
| 2      | 
+--------+
1 row in set (0.00 sec)

the stored procedure to check the student grade range

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE checkStudent(IN startScore DOUBLE, IN endScore DOUBLE)
    -> BEGIN
    ->     SELECT id,name, score FROM student WHERE
    ->     score <= endScore AND score >= startScore;
    -> END; 
    -> ;;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL checkStudent(30,40);
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Jone Mark |    35 | 
+----+-----------+-------+
1 row in set (0.00 sec)

update student record using stored procedure

• First update the student score to the new score based on the student ID,
• secondly, query all the students that have the same score

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE updateScore(IN studentID INT, IN newScore DOUBLE)
    ->    BEGIN 
    ->           UPDATE student SET score= newScore 
    ->                WHERE id = StudentID;
    ->           SELECT * FROM student WHERE score = newScore;
    ->    END;
    -> ;;
Query OK, 0 rows affected (0.01 sec)
mysql>DELIMITER ;
mysql> SELECT * FROM student;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Jone Mark |    35 | 
|  2 | Bob Maat  |    80 | 
|  3 | Paul Chao |    85 | 
|  4 | Marry     |    74 | 
|  5 | Christ    |    63 | 
+----+-----------+-------+
5 rows in set (0.00 sec)

mysql> CALL updateScore(1,63);
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Jone Mark |    63 | 
|  5 | Christ    |    63 | 
+----+-----------+-------+
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

create store procedure with Repeat and continue handler for not found

mysql> DELIMITER $$
mysql> CREATE PROCEDURE make_decision(IN score_consider DOUBLE)
    -> BEGIN
    ->           DECLARE student_score   DOUBLE;
    ->           DECLARE student_id      INT ;
    ->           DECLARE ddDone INT;
    ->           DECLARE failed_student CURSOR 
    ->                FOR SELECT id, score FROM student ;
    ->         
    ->           DECLARE CONTINUE HANDLER FOR NOT FOUND SET ddDone = 1;
    ->   
    ->           SET ddDone = 0; 
    ->           OPEN failed_student; 
    ->   
    ->           REPEAT
    ->           FETCH failed_student INTO student_id, student_score;
    ->                 IF student_score <= score_consider THEN
    ->                  INSERT INTO student_status (ss_id, student_id, status)
    ->                    VALUES(null,student_id,'fail');
    ->           ELSE
    ->                  INSERT INTO student_status (ss_id, student_id, status)
    ->                    VALUES(null,student_id,'pass');
    ->                 END IF;
    ->           UNTIL ddDone 
    ->           END REPEAT;
    ->           CLOSE failed_student;
    ->           SELECT * FROM student_status;             
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

mysql> TRUNCATE student_status;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL make_decision(74);
+-------+------------+--------+
| ss_id | student_id | status |
+-------+------------+--------+
|     1 |          1 | fail   | 
|     2 |          2 | pass   | 
|     3 |          3 | pass   | 
|     4 |          4 | fail   | 
|     5 |          5 | fail   | 
|     6 |          5 | fail   | 
+-------+------------+--------+
6 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)