MySQL - 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 −

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 −

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 −

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 −

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 −

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

$sql = "DELIMITER &&
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
    BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
    END &&
 DELIMITER";
$mysqli->query($sql);

var sqlProcedure =
`CREATE PROCEDURE GetCustomerInfo()
BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE > 25;
END;`
con.query(sql)

String sql  "CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)BEGIN SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge; END";
statement.executeQuery(sql);

stored_procedure = "
CREATE PROCEDURE GetCustomerInfo()
BEGIN
SELECT * FROM CUSTOMERS WHERE AGE > 25;
END "
cursorObj.execute(literal_query)

Example

Following are the programs −

$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();

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
)       

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    //Creating CUSTOMERS 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));"
    con.query(sql);

    sql = "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, 'MP', 4500.00 ),(7, 'Muffy', 24, 'Indore', 10000.00 );"
    con.query(sql);

    //Displaying records of CUSTOMERS table
    sql = "SELECT * FROM CUSTOMERS;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("Records of CUSTOMERS");
      console.log(result);
      console.log("--------------------------");
    });

    //Creating a Procedure
    var sqlProcedure = `
    CREATE PROCEDURE GetCustomerInfo()
    BEGIN
        SELECT * FROM CUSTOMERS WHERE AGE > 25;
    END;
    `
    con.query(sqlProcedure);

    sql = "CALL GetCustomerInfo();"
    con.query(sql, function(err, result){
      console.log("Verification");
      if (err) throw err
      console.log(result)
    });
});    

 
Connected!
--------------------------
Records of CUSTOMERS
[
  {ID: 1, NAME: 'Ramesh', AGE: 32, ADDRESS: 'Ahmedabad', SALARY: '2000.00'},
  {ID: 2,NAME: 'Khilan', AGE: 25, ADDRESS: 'Delhi', SALARY: '1500.00'},
  {ID: 3,NAME: 'kaushik', AGE: 23,ADDRESS: 'Kota', SALARY: '2000.00'},
  {ID: 4, NAME: 'Chaitali', AGE: 25,ADDRESS: 'Mumbai',SALARY: '6500.00'},
  {ID: 5,NAME: 'Hardik',AGE: 27,ADDRESS: 'Bhopal',SALARY: '8500.00'},
  {ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: '4500.00' },
  {ID: 7,NAME: 'Muffy',AGE: 24,ADDRESS: 'Indore',SALARY: '10000.00'}
]
--------------------------
Verification
[
  [
    {ID: 1, NAME: 'Ramesh', AGE: 32, ADDRESS: 'Ahmedabad', SALARY: '2000.00'},
    {ID: 5, NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00'}
  ],
  ResultSetHeader {
    fieldCount: 0,
    affectedRows: 0,
    insertId: 0,
    info: '',
    serverStatus: 34,
    warningStatus: 0,
    changedRows: 0
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class StoredProcedure {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //creating a table 
            String 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))";
            st.execute(sql);
            System.out.println("Table Customers created successfully....!");
            //let's insert some records into it...
            String sql1 = "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 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully....!");
            //print table records
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //not let's create stored procedure
            String sql3 = "CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)BEGIN SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge; END";
            st.execute(sql3);
            System.out.println("Stored procedure created successfully....!");
            //verify by calling the procedure
            String sql4 = "CALL GetCustomerInfo(25)";
            rs = st.executeQuery(sql4);
            System.out.println("Procedure called successfully...!");
            rs = st.executeQuery(sql4);
            System.out.println("Table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}    

Table Customers created successfully....!
Records inserted successfully....!
Table records: 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Stored procedure created successfully....!
Procedure called successfully...!
Table records: 
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00    

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Creating the table 'CUSTOMERS'
create_table_query = '''
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)
);
'''
cursorObj.execute(create_table_query)
print("Table 'CUSTOMERS' is created successfully!")
# Inserting records into 'CUSTOMERS' table
sql = "INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (%s, %s, %s, %s, %s);"
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, 'MP', 4500.00),
    (7, 'Muffy', 24, 'Indore', 10000.00)
]
cursorObj.executemany(sql, values)
# Printing the records from the 'CUSTOMERS' table
select_query = "SELECT * FROM CUSTOMERS;"
cursorObj.execute(select_query)
records = cursorObj.fetchall()
print("\nCustomers table records:")
for record in records:
    print(record)
# Creating a stored procedure
stored_procedure = """
CREATE PROCEDURE GetCustomerInfo()
BEGIN
SELECT * FROM CUSTOMERS WHERE AGE > 25;
END 
"""
cursorObj.execute(stored_procedure)
print("\nStored procedure 'GetCustomerInfo' created successfully!")
# Call the stored procedure
show_functions = "CALL GetCustomerInfo();"
cursorObj.execute(show_functions)
functions = cursorObj.fetchall()
# Print the information about stored functions
print("\nStored Procedure Results:")
for function in functions:
    print(function)
# Closing the cursor and connection
cursorObj.close()
connection.close()    

Table 'CUSTOMERS' is created successfully!

Customers table records:
(1, 'Ramesh', 32, 'Ahmedabad', Decimal('2000.00'))
(2, 'Khilan', 25, 'Delhi', Decimal('1500.00'))
(3, 'kaushik', 23, 'Kota', Decimal('2000.00'))
(4, 'Chaitali', 25, 'Mumbai', Decimal('6500.00'))
(5, 'Hardik', 27, 'Bhopal', Decimal('8500.00'))
(6, 'Komal', 22, 'MP', Decimal('4500.00'))
(7, 'Muffy', 24, 'Indore', Decimal('10000.00'))

Stored procedure 'GetCustomerInfo' created successfully!

Stored Procedure Results:
(1, 'Ramesh', 32, 'Ahmedabad', Decimal('2000.00'))
(5, 'Hardik', 27, 'Bhopal', Decimal('8500.00'))