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 :
- 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 :
- Step 4 : Click Go .
After the routine is successfully created , you probably would see something like this :
- 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