Simple MySQL Stored Procedure ( Without Parameters )

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

About these ads