What are Stored Procedures in MySQL?
We have seen what MySQL is and how to write simple queries. MySQL also supports writing complex logic in the form of Stored Procedures. Stored procedures in Mysql are prepared SQL codes that we can save in one place and can reuse again and again. We can write multiple stored procedures in MySQL based on our requirements. Stored Procedures are also called SP short.
Following is the syntax for the Stored procedure.
CREATE PROCEDURE ‘procedure_name'(
IN input_value1 data_type,
IN input_value2 data_type,
.
.
.
IN input_value_n data_type,
)
BEGIN
/*
.. stored procedure logic
*
/
END
Now we will create the Stored procedure for the selected schema using MySQL workbench. For the schema, we have a procedures option, right click on that option and then select the ‘Create Stored Procedure’ option as shown below.
A new tab will be open as shown below
We will write a simple procedure that will accept two parameters marks and gender and then return the result from the ‘students’ table based on the conditions as shown below.
CREATE PROCEDURE ‘my_custom_procedure’ (
IN in_marks INTEGER,
IN in_gender VARCHAR(1)
)
BEGIN
SELECT *
FROM
test_schema1.students
WHERE
marks > in_marks
AND gender = in_gender;
END
Once we completed the code, and click on the apply button we will get the confirmation window as follows with the SQL query generated. We can copy that query and run it directly to create a procedure in another system if MySQL workbench is not installed.
Click on the final ‘Apply’ button then we will get the success window click on the Finish, and SP will be created.
Once SP is created we can be able to see it under the Stored Procedures option for that particular schema as follows.
Running Stored Procedure:
To run the SP we can use execute button as shown below.
We will get the pop-up window asking for inputs like below, after adding the values click on execute. We will get the output from the SP with the query conditions added.
Output:
We can also directly run the SP using the call statement below.
call test_schema1.my_custom_procedure(100, ‘F’);
We can change the value in the above query for different inputs and we will get the respective outputs.
Extracting Create Statement.
To extract the create statement for the Stored procedure we want to run in the other system, we can right-click on the procedure name, select ‘Copy to Clipboard’, and then select the ‘Create Statement’ option. Create statement will get copied we can paste this statement in any empty sql file and then save and use that file further.
