Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Stored Procedure

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Stored Procedure
The MySQL Stored Procedure
A MySQL stored procedure is a group of pre-compiled SQL statements that can be reused anytime. Stored procedures can be used to perform different database operations such as such as inserting, updating, or deleting data.
Syntax
The basic syntax to create a stored procedure in MySQL is as follows 
DELIMITER //
CREATE PROCEDURE procedure_name([IN|OUT|INOUT] parameter_name parameter_datatype)
BEGIN
    -- SQL statements to be executed
END //
DELIMITER;
Where,
The CREATE PROCEDURE statement is used to create the procedure.
The SQL statements that need to be executed are placed between the BEGIN and END keywords.
Creating a Procedure
We can use the following steps to create a stored procedure in MySQL 
Choose a name for the procedure.
Write the SQL query of the procedure.
Execute the procedure with different parameters.
Example
To understand a stored procedure, let us consider the CUSTOMERS table which contains the personal details of customers including their ID, name, age, address and salary as shown below 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
Now insert values into this table using the INSERT statement as follows 
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
The CUSTOMERS table obtained is as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Now, let us create a procedure named 'GetCustomerInfo' without any parameters to retrieve all the records from CUSTOMERS table where age is greater than 25
DELIMITER //
CREATE PROCEDURE GetCustomerInfo()
BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE > 25;
END //
Verification
To verify the changes, we execute the procedure using the CALL command as shown in the query below 
CALL GetCustomerInfo(); //
The result produced is as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
5
Hardik
27
Bhopal
8500.00
Stored Procedure Parameter Types
Stored procedures can have different types of parameters, which are used to decide the values that will be passed during execution. Following are the different types of stored procedure parameters in SQL 
Input parameters − These parameters are used to pass values from the calling program or user to the stored procedure.
Output parameters − These parameters are used to return values from the stored procedure to the calling program or user.
Input/Output parameters − These parameters allow a stored procedure to accept input values and return output values.
Table-valued parameters − These parameters are used to pass a table variable as a parameter to a stored procedure.
Default parameters − These parameters are used to specify a default value that will be used if no value is passed for the parameter.
Cursor parameters − These parameters are used to pass a cursor to a stored procedure.
Output XML parameters − These parameters are used to return XML data from a stored procedure.
Now, let us take a look at some of the most common types of stored procedure parameters in SQL 
Procedure with IN parameter
The IN parameter is the default parameter and is used to receive the input value from the calling program. The value is passed at the time of procedure execution.
Example
In the following query, we are creating a stored procedure that takes a customer's ID as an input parameter and returns that customer's details.
DELIMITER //
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
END //
Verification
To execute the stored procedure and pass a value for the 'CustomerAge' parameter, we will use the CALL command as shown below 
CALL GetCustomerInfo(23); //
Following is the output of the above code 
ID
NAME
AGE
ADDRESS
SALARY
3
Kaushik
23
Kota
2000.00
Procedure with OUT parameter
The OUT parameter is used to send the output values to the calling program. It is necessary to specify the OUT keyword to an output parameter when creating the procedure. At the time of calling, a variable, prefixed with '@' is used to hold the returned value.
We can then use the SELECT statement on the variable to display the output of the procedure.
Example
In the following query, we are creating a stored procedure that takes customer's ID as an input parameter and returns that customer's SALARY using an output parameter "Cust_Salary".
DELIMITER //
CREATE PROCEDURE GetCustomerSalary(IN CustomerID INT, OUT Cust_Salary DECIMAL(18,2))
BEGIN
SELECT Salary INTO Cust_Salary FROM CUSTOMERS WHERE ID = CustomerID;
END //
DELIMITER ;
Verification
To verify the working of the procedure created, we call it using the CALL command 
CALL GetCustomerSalary(3, @S);
The output value will be stored in the '@S' parameter passed; which is further displayed using the SELECT statement as shown below 
SELECT @S AS SALARY;
We get the output as shown below 
SALARY
2000.00
Procedure with INOUT parameter
The INOUT parameter is the combination of IN and OUT parameter and is used to pass and receive data from the stored procedure in a single parameter.
The INOUT keyword is used to declare an INOUT parameter in a stored procedure.
Example
In the following query, we are using salary as the INOUT parameter to input the salary and hold the output returned by the procedure.
The stored procedure retrieves the current salary of the customer using the IN parameter cust_id. It then increases the salary by 10% and stores the increased salary in the INOUT parameter salary 
DELIMITER //
CREATE PROCEDURE increaseSalary(IN cust_id INT, INOUT salary DECIMAL(18, 2))
BEGIN
SELECT SALARY INTO salary FROM CUSTOMERS WHERE ID = cust_id;
SET salary = salary * 1.1;
UPDATE CUSTOMERS SET SALARY = @salary WHERE ID = cust_id;
END //
DELIMITER ;
Verification
We must set the input value of the INOUT parameter before calling the procedure using the following SET command 
SET @salary = 50000.00;
Now, we check whether the output value is retrieved by calling the procedure using the following statement 
CALL increaseSalary(7, @salary);
To verify the result, we retrieve the final increased salary stored in the INOUT parameter using the following SELECT statement 
SELECT @salary as INCREASED_SALARY;
The output obtained is as follows 
INCREASED_SALARY
55000.00
Make a note that these queries won't make any changes to the original table, as we are storing the output in a variable.
Deleting a Stored Procedure
We can delete a stored procedure using the DROP PROCEDURE statement.
Syntax
Following is the basic syntax to delete a stored procedure in SQL 
DROP PROCEDURE [IF EXISTS] procedure_name;
Example
In the following example, we are deleting a stored procedure named "GetCustomerSalary" 
DROP PROCEDURE IF EXISTS GetCustomerSalary;
Output
After executing the above code, we get the following output 
Query OK, 0 rows affected (0.01 sec)
Advantages of Stored Procedure
Following are the advantages of stored procedures 
Improved Performance − Stored procedures are pre-compiled, so they can be executed faster than typical SQL statements.
Code Reuse − Stored procedures can be called from different client applications allowing for code reusability.
Reduced Network Traffic − Stored procedures are executed on the server returning only the results to the client, thereby reducing network traffic and improving application performance.
Better Security − Stored procedures can be used to enforce security rules and prevent unauthorized access to sensitive data.
Simplified Maintenance − Stored procedures make it easy to maintain code by storing SQL code in a single location.
Drawbacks of Stored Procedure
Following are the disadvantages of stored procedures 
Increased Overhead − Stored procedures consume more server resources than simple SQL statements when used frequently or for complex operations.
Limited Portability − Stored procedures cannot be easily moved from one database management system (DBMS) to another since they are often specific to a particular DBMS.
Debugging Challenges − Stored procedures are hard to debug when multiple layers of code are involved.
Security Risks − Stored procedures pose a security risk if they are used to access sensitive data.
Stored Procedure Using Client Program
We can also perform Stored Procedure Using Client Program.
Syntax
PHPNodeJSJavaPython
To Create Stored Procedure through a PHP program, we need to execute the "Create" statement using the mysqli function query() as follows 
$sql = "DELIMITER &&
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
    BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
    END &&
 DELIMITER";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
//let's create table
$sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID))";
if($mysqli->query($sql)){
    printf("Customers table created successfully...!\n");
}
//lets insert some records
$sql = "INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 )";
if($mysqli->query($sql)){
    printf("First record inserted successfully....!\n");
}
$sql = "INSERT INTO CUSTOMERS VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 )";
if($mysqli->query($sql)){
    printf("Second record inserted successfully....!\n");
}
$sql = "INSERT INTO CUSTOMERS VALUES (3, 'kaushik', 23, 'Kota', 2000.00 )";
if($mysqli->query($sql)){
    printf("Third record inserted successfully....!\n");
}
$sql = "INSERT INTO CUSTOMERS VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 )";
if($mysqli->query($sql)){
    printf("Fourth record inserted successfully....!\n");
}
//display table records
$sql = "SELECT * FROM CUSTOMERS";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        printf("ID: %d, NAME: %s, AGE %d, ADDRESS %s, SALARY %f", 
            $row['ID'],
            $row['NAME'], 
            $row['AGE'], 
            $row['ADDRESS'], 
            $row['SALARY']);
            printf("\n");
    }
}
//let's create procedure
$sql = "DELIMITER &&
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
    BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
    END &&
 DELIMITER";
if($sql){
    printf("Stored procedure created successfully...!\n");
}
//now lets verify
$sql = "CALL GetCustomerInfo(25)";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        print_r($row);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as shown below 
Customers table created successfully...!
First record inserted successfully....!
Second record inserted successfully....!
Third record inserted successfully....!
Fourth record inserted successfully....!
Table records:
ID: 1, NAME: Ramesh, AGE 32, ADDRESS Ahmedabad, SALARY 2000.000000
ID: 2, NAME: Khilan, AGE 25, ADDRESS Delhi, SALARY 1500.000000
ID: 3, NAME: kaushik, AGE 23, ADDRESS Kota, SALARY 2000.000000
ID: 4, NAME: Chaitali, AGE 25, ADDRESS Mumbai, SALARY 6500.000000
Stored procedure created successfully...!
Table records:
Array
(
    [0] => 2
    [ID] => 2
    [1] => Khilan
    [NAME] => Khilan
    [2] => 25
    [AGE] => 25
    [3] => Delhi
    [ADDRESS] => Delhi
    [4] => 1500.00
    [SALARY] => 1500.00
)
Array
(
    [0] => 4
    [ID] => 4
    [1] => Chaitali
    [NAME] => Chaitali
    [2] => 25
    [AGE] => 25
    [3] => Mumbai
    [ADDRESS] => Mumbai
    [4] => 6500.00
    [SALARY] => 6500.00
)
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Stored Procedure

The MySQL Stored Procedure

Syntax

Creating a Procedure

Example

Verification

Stored Procedure Parameter Types

Procedure with IN parameter

Example

Verification

Procedure with OUT parameter

Example

Verification

Procedure with INOUT parameter

Example

Verification

Deleting a Stored Procedure

Syntax

Example

Output

Advantages of Stored Procedure

Drawbacks of Stored Procedure

Stored Procedure Using Client Program

Syntax

Example

Output