Introduction
In this chapter, we will learn about how to create a stored procedure in the SQL database. For the stored procedure, we will use the MYSQL Database.
Stored Procedure
A stored procedure in a relational database is a collection of SQL statements that are saved on the database server and executed on the server itself. Any SQL query including Insert, Update, Delete can be a part of a stored procedure. Stored procedures are a secure way to access and manipulate data. Stored procedures also provide high-performance results compare to embedded SQL queries in your code.
Let's see how to create a stored procedure in MySQL database.
Syntax to create a Stored Procedure:
- CREATE
- [DEFINER = { user | CURRENT_USER }]
- PROCEDURE sp_name ([proc_parameter[,...]])
- [characteristic ...] routine_body
Let's look at this step by step.
Step 1- Create a Table
- CREATE TABLE `employees` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `employeeNumber` int(11) NOT NULL,
- `lastname` varchar(50) NOT NULL,
- PRIMARY KEY (`id`)
- )
Step 2- For insertion create a stored procedure:
- delimiter $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_inser`
- (IN `employeenum` int
- , IN `lastname` varchar(200)
- )
- begin
- insert into employees (employeeNumber,lastname) values(employeenum,lastname);
- END$$
Step 3- Call stored procedure
- call sp_inser(123,'rajeev')
Summary
In the next chapter, we will learn how to create Trigger in MYSQL Database.