Create Stored Procedure In MySQL

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: 
  1. CREATE  
  2. [DEFINER = { user | CURRENT_USER }]  
  3. PROCEDURE sp_name ([proc_parameter[,...]])  
  4. [characteristic ...] routine_body  
Let's look at this step by step.
 
Step 1- Create a Table 
  1. CREATE TABLE `employees` (  
  2. `id` int(11) NOT NULL AUTO_INCREMENT,  
  3. `employeeNumber` int(11) NOT NULL,  
  4. `lastname` varchar(50) NOT NULL,  
  5. PRIMARY KEY (`id`)  
  6. )
Step 2- For insertion create a stored procedure: 
  1. delimiter $$  
  2. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_inser`  
  3. (IN `employeenum` int  
  4. , IN `lastname` varchar(200)  
  5. )  
  6. begin  
  7. insert into employees (employeeNumber,lastname) values(employeenum,lastname);  
  8. END$$  
Step 3- Call stored procedure 
  1. call sp_inser(123,'rajeev')  

Summary 

 
In the next chapter, we will learn how to create Trigger in MYSQL Database. 
Author
Pankaj Pandey
0 5k 5m
Next » Triggers in SQL

©2024 C# Corner.