Consider a STUDENT table with the following records to understand how a stored procedure in MySQL is created and executed.
This table contains 5 columns which represent details of a student. It is a very simple structure to understand the usage of stored procedure.
For practicing the examples given in this tutorial, create this table at your end using the below SQL query for creating the table.
CREATE TABLE `STUDENT` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(50) NULL,
`age` INT NULL,
`class` VARCHAR(5) NULL,
`country` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
Use the following SQL queries for inserting data into this table.
INSERT INTO `STUDENT` (`name`, `age`, `class`, `country`) VALUES (‘John Doe’, ’12’, ‘8th’, ‘UK’);
INSERT INTO `STUDENT` (`name`, `age`, `class`, `country`) VALUES (‘Sarita’, ’15’, ’11th’, ‘Thailand’);
INSERT INTO `STUDENT` (`name`, `age`, `class`, `country`) VALUES (‘Robert D\’costa’, ‘8’, ‘7th’, ‘Malaysia’);
INSERT INTO `STUDENT` (`name`, `age`, `class`, `country`) VALUES (‘Gary Thomson’, ’16’, ’11th’, ‘USA’);
Stored procedure create example
Suppose we want to create a stored procedure that fetches all the records from the STUDENT table. Using the syntax of procedure given in the last section, it can be created as below.
DELIMITER %%
CREATE PROCEDURE getstudentdata()
BEGIN
SELECT * FROM STUDENT;
END %%
DELIMITER ;
Above set of statements will create a stored procedure with name getstudentdata. For executing this procedure use CALL statement as
CALL getstudentdata;
This procedure does not accept any parameters. To understand parameters of a stored procedure, go through the next section.