Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Stored Procedures and Functions in MySQL with PhpMyAdmin

See this article how to use Stored Procedures and Functions in MySQL using PhpMyAdmin tool, with some real scenarios.


In this article we will see how to create and use procedures and functions in MySQL with the help of PhpMyAdmin tool. Before we start creating these"stored"routines we need to understand what are Procedures and Functions in MySql and why we chose to use PhpMyAdmin.

Procedures and Functions are routines stored in the database running a defined set of sequential steps. The concept is analogous to creating a method in a programming language where you can call this method several times without the need to rewrite code.

Let's think about the real need for such resources in a Database Management System, in particular in MySQL.

Imagine the following situation: For some reason a particular record can be inserted incorrectly in the database, by user typo that is operating a system X. You do not have access to the system source code, and is not treating this error in your "source" as well as not preventing it from occurring, but you have total control over the database. Knowing that this error occurs frequently and you need to perform a cleaning procedure at least once a week, the easier it will be you to create a routine that performs this cleanup.

Let's look at the "wrong" solution in Listing 1.

Listing 1. Wrong solution.
UPDATE table_x SET record_1 = (record_1 + 3) WHERE record_2 > 10;
DELETE FROM table_x WHERE record_1 < 5;
UPDATE table_y SET record_1 = (SELECT MAX(record_1) FROM table_x);
Listing 1 shows a generic and wrong solution to any problem. Suppose it is the wrong registry problem that we present just above. Every time we needed to clean up, the database administrator (DBA) must perform all of these commands (imagine if he forget any of these, the result can be catastrophic). In the moment it looks easy, but remember that this is just an example and in real life such corrections may require 100, 200 or more lines depending on the complexity of such correction.

Thinking in terms of routines, we could do as in the code ofListing 2.

Listing 2. Using generic routine to solution Listing 1.
routine_1 {
UPDATE table_x SET record_1 = (record_1 + 3) WHERE record_2 > 10;
DELETE FROM table_x WHERE record_1 < 5;
UPDATE table_y SET record_1 = (SELECT MAX(record_1) FROM table_x);
}

--Executing routine_1
CALL routine_1;  
It is obvious that Listing 2 is only an example without many technical details and syntax of the language, but it's worthy to show that we "encapsulated" all the code in Listing 1 in our routine_1 shown in Listing 2. Now DBA needs only to draw "routine_1" whenever you need to realize that such cleaning, or better, as everything is ready, another person, other than DBA, can also run the routine_1 making sure that everything will be done as expected.

Returning to our main subject, what is the real difference between Procedures and Functions? Procedures can only be called using the reserved word "CALL" and do not return anything, while functions can be called within a SELECT, for example, and can return a value. We'll see in the following sections more details.

Stored Procedures

First let's see a very simple procedure and explain it in details and only then seeing more complex examples. Track Listing 3.

Listing 3. Simple Example of Procedure
#Creating Procedure
DELIMITER $
CREATE PROCEDURE proc()
BEGIN
SELECT "hello from proc";
END
$

#Calling the Procedure
CALL proc();

#Result
hello from proc  
The keyword "DELIMITER" defines a delimiter character that is responsible for telling where starts and where ends our role. In our case we define the "$$" as the delimiter, but could be another character of your choice.

Then we have the signature of our function to "CREATE PROCEDURE proc()" which, in our case, has no parameters and is quite simple. The delimiters of the "body" of our routine are the "BEGIN" and "END" and within these put what our routine should do. In our case, it only sends a "hello from proc" message.

In the second block we have to call the procedure with "CALL proc()", thereby making the DBMS execute, the function we created earlier resulting in "hello from proc" message.

Functions

Accompany a function example in Listing 4.

Listing 4. Simple Example of a Function
#Creating the FUNCTION
DELIMITER $
CREATE FUNCTION func() RETURNS CHAR(100)
BEGIN
RETURN "hello from func";
END
$

#Calling func()
SELECT func(); 
Just as in Listing 3, we still use the DELIMITER with the same goal, but now we changed the PROCEDURE keyword to FUNCTION and added "RETURNS CHAR (100)," that is, our function should return a CHAR with a maximum of 100 characters.

In general, both routine in Listing 3 as in Listing 4 have the same goal: show the console any sentence. But in the function we have the advantage of being able to use it within a SELECT, as shown in Listing 4.

Real Case with PhpMyAdmin - Creating our database

For all the examples shown, from now on we will use the PhpMyAdmin tool that can usually be accessed through http://localhost/phpMyAdmin. But nothing prevents you from using another tool or even your own MySQL terminal, feel free.

Our Scenario: To illustrate a real example we'll work with the need to create a database to store the records and notes of the students of a school called ABC. The database will have to provide a Proof of Registration, Students and Notes. Each student can have zero or more notes and a note can only be a test, i.e., the Student "John" has the note "8.0" in the race "0010" which was held in "20/06/2014".

First let's create our model to later start building the necessary scripts for definition and population of the database. Refer to Figure 1.




Figure 1. Bank Modeling

Given the above model, we can create our database. In PhpMyAdmin, as soon as you access it, click the "SQL" tab and you will have a console to enter the commands you need. Create the database as in Listing 5 through this console and do not forget to click run for the command to take effect.

Listing 5. Creating Database
CREATE DATABASE school_abc CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci' 
Once created our database, let's go to the structure definitions. Before entering the commands in Listing 6, make sure that the database "school_abc" is selected. In PhpMyAdmin top you should see something like: "localhost ? school_abc". If it says only "localhost" means that the database has not been selected.

With the database properly selected, reselect the SQL tab, for now execute commands within the school_abc database, only.

Listing 6. Structure of Creation - DDL
-- -----------------------------------------------------
-- Table student
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS student (
 id  INT NOT NULL ,
 name  VARCHAR(100) NOT NULL ,
 registrationId  VARCHAR(45) NOT NULL ,
 date_birth  DATE NULL ,
 date_registrationId  DATE NOT NULL ,
PRIMARY KEY ( id ) ,
UNIQUE INDEX  registrationId_UNIQUE  ( registrationId  ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table  test
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS test (
 id  INT NOT NULL ,
 testDate  DATE NOT NULL ,
 description  VARCHAR(255) NOT NULL ,
PRIMARY KEY ( id ) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table score
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS score (
 student_id  INT NOT NULL ,
 test_id  INT NOT NULL ,
 score  DECIMAL(15,2) NULL ,
PRIMARY KEY ( student_id ,  test_id ) ,
INDEX  fk_student_has_test_test1  ( test_id  ASC) ,
INDEX  fk_student_has_test_student1  ( student_id  ASC) ,
CONSTRAINT  fk_student_has_test_student1
  FOREIGN KEY ( student_id  )
  REFERENCES student ( id  )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
CONSTRAINT  fk_student_has_test_test1
  FOREIGN KEY ( test_id  )
  REFERENCES test  ( id  )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION)
ENGINE = InnoDB;  
To finish the structure of our database and finally start creating our routines, we need to populate the database with some data (Listing 7), using the same SQL tab with the selected database, and do not forget to click "run" for the command to take effect.

Listing 7. Populating the database
-- INSERINDO STUDENTS
INSERT INTO student (id, name, registrationId, date_birth, date_registrationId) VALUES
(1,'MICHAEL JONH', '123A', STR_TO_DATE('23/08/1993', '%d/%m/%Y'), CURRENT_DATE()),
(2,'WILLIANS JUNIOR', '400B', STR_TO_DATE('10/04/1993', '%d/%m/%Y'), CURRENT_DATE()),
(3,'JOHN BILLBOARD', '420B', STR_TO_DATE('30/07/1993', '%d/%m/%Y'), CURRENT_DATE()),
(4,'JENNY KILLY', '010A', NULL, STR_TO_DATE('25/01/2014', '%d/%m/%Y'))

-- INSERTING TESTS
INSERT INTO test(id, record, description) VALUES
(1, STR_TO_DATE('30/03/2014', '%d/%m/%Y'), 'Test A1'),
(2, STR_TO_DATE('30/04/2014', '%d/%m/%Y'), 'Test B1'),
(3, STR_TO_DATE('30/05/2014', '%d/%m/%Y'), 'Test C1'),
(4, STR_TO_DATE('30/07/2014', '%d/%m/%Y'), 'Test A2'),
(5, STR_TO_DATE('30/08/2014', '%d/%m/%Y'), 'Test B2'),
(6, STR_TO_DATE('30/09/2014', '%d/%m/%Y'), 'Test C2')

INSERT INTO score (student_id, test_id, score) VALUES
#STUDENT 1
(1, 1, 10),
(1, 2, 9.8),
(1, 3, 8),
(1, 4, 10),
(1, 5, 10),
(1, 6, 9),

#STUDENT 2
(2, 1, 7),
(2, 2, 7.5),
(2, 3, 6),
(2, 4, 8),
(2, 5, 8.5),
(2, 6, 9),

#STUDENT 3
(3, 1, 9),
(3, 2, 9),
(3, 3, 9),
(3, 4, 10),
(3, 5, 10),
(3, 6, 9.8),

#STUDENT 4
(4, 1, 3),
(4, 2, 6),
(4, 3, 7),
(4, 4, 8),
(4, 5, 7),
(4, 6, 7)  

Defining our routines

Based on our structure set up above and data that has been populated, we will define functions that will help you to better understand the operation in practice of functions and procedures in MySQL.

The creation of functions and procedures in PhpMyAdmin can be made on the same console we used to populate data in Listing 7, that is, you should be with the database "school_abc" selected and click the SQL tab (just remember the process).

Function to show weight-based note

Suppose the test score of a particular student should be calculated by a factor P (called weight) and only then set the final score of a particular race, so we will create a function that takes as a parameter the note and return the calculated value of the note. Notice theListing 8.

Listing 8. calculate_score()
DELIMITER $
CREATE FUNCTION calc_score(score NUMERIC(15,2)) RETURNS NUMERIC(15,2)
BEGIN
DECLARE weight INT;

#If the student score is greater than 9.5 so your score will have a bigger weight
IF score > 9.5 THEN
SET weight = 2;
ELSE
SET weight = 1;
END IF;

RETURN (score*weight) / 20;
END
We have a script that recalculates the scores of a student based on a weight that can have the value of 1 or 2. We will see in Listing 9 how to use it.

Listing 9. Using calculate_score()
SELECT a.name, p.description, calculate_score(n.score) AS calculated_score, n.score AS original_score
FROM student a INNER JOIN score n ON a.id = n.id
INNER JOIN test p ON n.id = p.id
ORDER BY a.id, calculated_score DESC  
I the above SELECT we have listed all students with their notes and calculated the original notes, orders to be made by id and value of the calculated grade. Let's use the same function to sort by far higher average score, ie, the sum of all the grades calculated. Notice it in Listing 10.

Listing 10.Calculated Averages
SELECT a.name, (SUM(calculate_score(n.score))/6) AS avarage_score
FROM student a INNER JOIN score n ON a.id = n.id
INNER JOIN test p ON n.id = p.id
GROUP BY a.name
ORDER BY avarage_score DESC;  

Procedure to round score of students

We have seen above how to use a function in MySQL and now we will see how to use a procedure. We will create a procedure that will "sweep" all students' grades and then round it up or down as needed.

Our rounding criterion is the following: if the difference between the note and the next integer is less than or equal to 0.2, then we round for this case. Otherwise, the note will remain intact. Note the Listing 11 code.

Listing 11. round_score()
DELIMITER $
CREATE PROCEDURE round_score()
BEGIN
#O DECLARE declarates a variable that will be used during the program
DECLARE current_score NUMERIC(15,2);
DECLARE id_student, id_test INT;

#We create a CURSOR that will store the SELECT result
DECLARE cur CURSOR FOR SELECT score, studentId, testId FROM score;

#We open the CURSOR to use it into the LOOP
OPEN cur;

#We start the LOOP giving a name to it to be referenced always as needed.
myloop:
LOOP
  #We attribute the columns values of the cursor to the variables we created before
  FETCH cur INTO current_score, id_student, id_test;

  #Main logic of our routine.
  #If the next integer has a difference of 2.0 or less from the current score
  # then the current score will be rounded to this same integer.
  IF (CEIL(current_score) - current_score) <= 0.2 THEN
	UPDATE score SET score = CEIL(current_score) WHERE studentId = id_student AND testId = id_test;
  END IF;

END LOOP;

#We close the cursor
CLOSE cur;
END
In the script above we perform all the necessary job without any return, so we chose to use a procedure instead of a function. To use it, simply run the same way as the one shown in Listing 12.

Listing 12. Running the round_score() procedure
CALL round_score();
You can also delete both the procedure and the function using the following commands in Listing 13.

Listing 13. Excluding procedures and functions
#EXCLUDING FUNCTION calculate_score
DROP FUNCTION calculate_score; 

#EXCLUDING PROCEDURE round_score 
DROP PROCEDURE round_score; 
To finish our article, in Listing 14 we list the full script that can be run to create the whole structure, population, functions and procedures shown throughout the article.

Listing 14. Final Script
UPDATE table_x SET record_1 = (record_1 + 3) WHERE record_2 > 10;
DELETE FROM table_x WHERE record_1 < 5;
UPDATE table_y SET record_1 = (SELECT MAX(record_1) FROM table_x);

routine_1 {
UPDATE table_x SET record_1 = (record_1 + 3) WHERE record_2 > 10;
DELETE FROM table_x WHERE record_1 < 5;
UPDATE table_y SET record_1 = (SELECT MAX(record_1) FROM table_x);
}

--Executing routine_1
CALL routine_1;

#Creating Procedure
DELIMITER $
CREATE PROCEDURE proc()
BEGIN
SELECT "hello from proc";
END
$

#Calling the Procedure
CALL proc();

#Result
hello from proc

#Creating the FUNCTION
DELIMITER $
CREATE FUNCTION func() RETURNS CHAR(100)
BEGIN
RETURN "hello from func";
END
$

#Calling func()
SELECT func();

CREATE DATABASE school_abc CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci'

-- -----------------------------------------------------
-- Table student
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS student (
 id  INT NOT NULL ,
 name  VARCHAR(100) NOT NULL ,
 registrationId  VARCHAR(45) NOT NULL ,
 date_birth  DATE NULL ,
 date_registrationId  DATE NOT NULL ,
PRIMARY KEY ( id ) ,
UNIQUE INDEX  registrationId_UNIQUE  ( registrationId  ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table  test
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS test (
 id  INT NOT NULL ,
 testDate  DATE NOT NULL ,
 description  VARCHAR(255) NOT NULL ,
PRIMARY KEY ( id ) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table score
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS score (
 student_id  INT NOT NULL ,
 test_id  INT NOT NULL ,
 score  DECIMAL(15,2) NULL ,
PRIMARY KEY ( student_id ,  test_id ) ,
INDEX  fk_student_has_test_test1  ( test_id  ASC) ,
INDEX  fk_student_has_test_student1  ( student_id  ASC) ,
CONSTRAINT  fk_student_has_test_student1
  FOREIGN KEY ( student_id  )
  REFERENCES student ( id  )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
CONSTRAINT  fk_student_has_test_test1
  FOREIGN KEY ( test_id  )
  REFERENCES test  ( id  )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- INSERINDO STUDENTS
INSERT INTO student (id, name, registrationId, date_birth, date_registrationId) VALUES
(1,'MICHAEL JONH', '123A', STR_TO_DATE('23/08/1993', '%d/%m/%Y'), CURRENT_DATE()),
(2,'WILLIANS JUNIOR', '400B', STR_TO_DATE('10/04/1993', '%d/%m/%Y'), CURRENT_DATE()),
(3,'JOHN BILLBOARD', '420B', STR_TO_DATE('30/07/1993', '%d/%m/%Y'), CURRENT_DATE()),
(4,'JENNY KILLY', '010A', NULL, STR_TO_DATE('25/01/2014', '%d/%m/%Y'))

-- INSERTING TESTS
INSERT INTO test(id, record, description) VALUES
(1, STR_TO_DATE('30/03/2014', '%d/%m/%Y'), 'Test A1'),
(2, STR_TO_DATE('30/04/2014', '%d/%m/%Y'), 'Test B1'),
(3, STR_TO_DATE('30/05/2014', '%d/%m/%Y'), 'Test C1'),
(4, STR_TO_DATE('30/07/2014', '%d/%m/%Y'), 'Test A2'),
(5, STR_TO_DATE('30/08/2014', '%d/%m/%Y'), 'Test B2'),
(6, STR_TO_DATE('30/09/2014', '%d/%m/%Y'), 'Test C2')

INSERT INTO score (student_id, test_id, score) VALUES
#STUDENT 1
(1, 1, 10),
(1, 2, 9.8),
(1, 3, 8),
(1, 4, 10),
(1, 5, 10),
(1, 6, 9),

#STUDENT 2
(2, 1, 7),
(2, 2, 7.5),
(2, 3, 6),
(2, 4, 8),
(2, 5, 8.5),
(2, 6, 9),

#STUDENT 3
(3, 1, 9),
(3, 2, 9),
(3, 3, 9),
(3, 4, 10),
(3, 5, 10),
(3, 6, 9.8),

#STUDENT 4
(4, 1, 3),
(4, 2, 6),
(4, 3, 7),
(4, 4, 8),
(4, 5, 7),
(4, 6, 7) 

DELIMITER $
CREATE FUNCTION calc_score(score NUMERIC(15,2)) RETURNS NUMERIC(15,2)
BEGIN
DECLARE weight INT;

#If the student score is greater than 9.5 so your score will have a bigger weight
IF score > 9.5 THEN
SET weight = 2;
ELSE
SET weight = 1;
END IF;

RETURN (score*weight) / 20;
END

SELECT a.name, p.description, calculate_score(n.score) AS calculated_score, n.score AS original_score
FROM student a INNER JOIN score n ON a.id = n.id
INNER JOIN test p ON n.id = p.id
ORDER BY a.id, calculated_score DESC;

SELECT a.name, (SUM(calculate_score(n.score))/6) AS avarage_score
FROM student a INNER JOIN score n ON a.id = n.id
INNER JOIN test p ON n.id = p.id
GROUP BY a.name
ORDER BY avarage_score DESC;

DELIMITER $
CREATE PROCEDURE round_score()
BEGIN
#O DECLARE declarates a variable that will be used during the program
DECLARE current_score NUMERIC(15,2);
DECLARE id_student, id_test INT;

#We create a CURSOR that will store the SELECT result
DECLARE cur CURSOR FOR SELECT score, studentId, testId FROM score;

#We open the CURSOR to use it into the LOOP
OPEN cur;

#We start the LOOP giving a name to it to be referenced always as needed.
myloop:
LOOP
  #We attribute the columns values of the cursor to the variables we created before
  FETCH cur INTO current_score, id_student, id_test;

  #Main logic of our routine.
  #If the next integer has a difference of 2.0 or less from the current score
  # then the current score will be rounded to this same integer.
  IF (CEIL(current_score) - current_score) <= 0.2 THEN
UPDATE score SET score = CEIL(current_score) WHERE studentId = id_student AND testId = id_test;
  END IF;

END LOOP;

#We close the cursor
CLOSE cur;
END

Conclusion

This article's goal was not only present theories and concepts that can be found in the MySQL manual about functions and procedures. We showed how to apply these in practice with a real scenario, creating the entire database structure to the population of the same.

We use as a tool PhpMyAdmin just to give a better sense for those who are just starting out, but nothing prevents them from being used other tools, for example, MySQL Workbench, which is a powerful tool to perform the database modeling and do reserve engineering model to the script.


Web developer and passioned for web design, SEO and front end technologies.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login