Consider a STUDENT table with the following records to understand how a stored procedure in MySQL is created and executed.
stored procedure example
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.

0

Share your thoughts !!

Close Menu

Never Miss an article !

Get the new post delivered straight into your inbox, enter your email and hit the button

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

codippa will use the information you provide on this form to be in touch with you and to provide updates and marketing.