Home » Learn – MySQL Procedure

Learn – MySQL Procedure

  • by

Learn – MySQL Procedure

A procedure (sometimes known as a stored procedure) is a set of pre-compiled SQL statements that are stored in a database. In the normal computing language, it is a subroutine or a subprogram. A procedure must always have a name, a list of parameters, and SQL statements. Triggers, other procedures, and applications like Java, Python, and PHP can all be used to call the procedures. MySQL version 5 was the first to include it. It is currently supported by nearly all relational database systems.
If we examine the enterprise programme, we must do specified operations on the database on a regular basis, such as database cleanup, payroll processing, and so on.

For each task to be completed, many SQL statements are used. This process may be simplified if we combined these jobs into a single task. In MySQL, we may accomplish this by defining a stored procedure in our database.
When a process calls itself, it is referred to as a recursive stored procedure. Recursive stored procedures are supported by most database systems. However, MySQL does not handle it well.
Features of a Stored Procedure
The performance of the applications is improved by using stored procedures. After creating stored procedures, they are built and saved in the database.
The traffic between the application and the database server is reduced by using stored procedures.

A method is invariably safe. Without giving permissions on the database tables, the database administrator can grant permissions to applications that access stored procedures in the database. Features of a Stored Procedure What is the best way to write a procedure? To create a stored procedure in MySQL, use the syntax below. It can return one or more values via parameters, or it may not return anything at all. A procedure is associated with our current database by default. However, by supplying database name as the name, we can create it in a different database from the present one.

Syntax:

DELIMITER &&
CREATE PROCEDURE procedure_name [[IN | OUT | INOUT] parameter_name datatype [, parameter datatype]) ]
BEGIN
Declaration_section
Executable_section
END &&
DELIMITER ;

 

MySQL procedure parameter types

MySQL procedure parameter has one of three modes:

IN parameterIt is the default mode. It takes a parameter as input, such as an attribute. When we define it, the calling program has to pass an argument to the stored procedure.This parameter’s value is always protected.

OUT parametersIt is used to pass a parameter as output. Its value can be changed inside the stored procedure, and the changed (new) value is passed back to the calling program. It is noted that a procedure cannot access the OUT parameter’s initial value when it starts.

INOUT parametersIt is a combination of IN and OUT parameters. It means the calling program can pass the argument, and the procedure can modify the INOUT parameter, and then passes the new value back to the calling program.

How can we call a stored procedure?

We can use the CALL statement to call a stored procedure. This statement returns the values to its caller through its parameters (IN, OUT, or INOUT). The following syntax is used to call the stored procedure in MySQL:

CALL procedure_name ( parameter(s))

Examples to create a procedure in MySQL with different parameters

mysql > USE database_name;

DELIMITER &&
CREATE PROCEDURE get_merit_student ()
BEGIN
SELECT * FROM student_info WHERE marks > 70;
SELECT COUNT(stud_code) AS Total_Student FROM student_info;
END &&
DELIMITER ;

 

DELIMITER &&
CREATE PROCEDURE get_student (IN var1 INT)
BEGIN
SELECT * FROM student_info LIMIT var1;
SELECT COUNT(stud_code) AS Total_Student FROM student_info;
END &&
DELIMITER ;


DELIMITER &&
CREATE PROCEDURE display_max_mark (OUT highestmark INT)
BEGIN
SELECT MAX(marks) INTO highestmark FROM student_info;
END &&
DELIMITER ;

mysql > CALL display_max_mark(@M);
mysql > SELECT @M;

DELIMITER &&
CREATE PROCEDURE display_marks (INOUT var1 INT)
BEGIN
SELECT marks INTO var1 FROM student_info WHERE stud_id = var1;
END &&
DELIMITER ;

mysql > SET @M = ‘3’;
mysql > CALL display_marks(@M);
mysql > SELECT @M;

How can we show or list stored procedures in MySQL?

SHOW PROCEDURE STATUS [LIKE ‘pattern’ | WHERE search_condition]

This statement displays all stored procedure names, including their characteristics. If we want to display procedures in a particular database, we need to use the WHERE clause.

mysql > SHOW PROCEDURE STATUS WHERE db = ‘mystudentdb’;

DROP PROCEDURE

DROP PROCEDURE [ IF EXISTS ] procedure_name;

mysql > DROP PROCEDURE display_marks;

If we want to make changes, we must drop and re-create the procedure using the DROP PROCEDURE and CREATE PROCEDURE statement.

The below statement is used to change the characteristics of a procedure but not the actual procedure:

ALTER PROCEDURE

ALTER PROCEDURE procedure_name [parameters]
{
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}

mysql > SHOW CREATE PROCEDURE get_merit_student;