Simple MySQL Stored Procedure ( Without Parameters )

Posted in Web Development by

Simple MySQL Stored Procedure Without Parameters and Considering One MySQL Table

For the purpose of this tutorial , I have considered a MySQL table student_scores with very simple structure and some sample data .

— student_scores Table Structure

CREATE TABLE IF NOT EXISTS `student_scores` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`score_1` int(11) NOT NULL DEFAULT ‘-1’,
`score_2` int(11) NOT NULL DEFAULT ‘-1’,
`total_score` int(11) NOT NULL DEFAULT ‘-1’,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

— Data

INSERT INTO `student_scores` (`score_1`, `score_2`) VALUES
(75, 80),
(80, 85),
(90, 80);

— PROCEDURE

I am considering a procedure update_total_scores to calculate the Total Score for each row and to update the corresponding total_score column .

— PROCEDURE DEFINITION

CREATE DEFINER = `test_user`@`localhost`
PROCEDURE `update_total_scores` ( )
COMMENT 'This procedure calculates Total Score and Updates for each row.'
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
BEGIN
UPDATE `student_scores` SET `total_score` = `score_1` + `score_2` ;
END

— ADDING USING PHPMYADMIN

  • Step 1 : When in PHPMyAdmin , Click Routines .
    You probably would see something like this :

Add Mysql Procedure Using PHPMyAdmin

  • Step 2 : Click Add Routine .
    An empty form will be displayed to add in a routine.
  • Step 3 : Select and/or Type In all the necessary fields appropriately .
    It could probably look something like this :

Image : Add MySQL Procedure Using PHPMyAdmin

  • Step 4 : Click Go .
    After the routine is successfully created , you probably would see something like this :

add_mysql_routine_using_phpmyadmin_img3

  • Step 5 : Execute it , Or Call it by its name update_total_scores in your MySQL Query in your Scripts .

— PROCEDURE DEFINITION

DELIMITER $$
CREATE DEFINER = `test_user`@`localhost`
PROCEDURE `update_total_scores` ( )
COMMENT 'This procedure calculates Total Score and Updates for each row.'
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
BEGIN
UPDATE `student_scores` SET `total_score` = `score_1` + `score_2` ;
END $$
DELIMITER ;

— EXPLANATION

DELIMITER $$ : Change the delimiter to $$ ( from the usual ; )
NOT DETERMINISTIC : I have considered the default
CONTAINS SQL : I have considered the default
SQL SECURITY INVOKER : This is to instruct that when executing , this procedure should execute with the privileges of the invoking user/account
DELIMITER ; : Put back the delimiter to ;

Published by : Athyala Mani Kanth
Last Updated : 2013-09-02