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.
[the_ad id=”89″]
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’);
[the_ad id=”94″]
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.