Parameters make the stored procedure more useful and flexible. In MySQL, we have the following three kinds of modes − Show
IN modeIt is the default mode. When we define an IN parameter in a stored procedure, the calling program has to pass an argument to the stored procedure. The value of an IN parameter is protected which means that even the value of the IN parameter is changed inside the stored procedure; its original value is retained after the stored procedure ends. OUT modeThe value of an OUT parameter can be changed inside the stored procedure and its new value is passed back to the calling program. It is to be noticed that the stored procedure cannot access the initial value of the OUT parameter when it starts. INOUT modeAn INOUT parameter is the combination of IN and OUT parameters which means that the calling program may pass the argument, and the stored procedure can modify the INOUT parameter and pass the new value back to the calling program. Syntax for defining a parameterFollowing is the syntax of defining a parameter in the stored procedure − MODE parameter_name parameter_type(parameter_size) Here, MODE can be IN, OUT or INOUT which depends upon the purpose of the parameter in the stored purpose.
Updated on 22-Jun-2020 05:29:17
Summary: in this tutorial, you will learn how to create stored procedures with parameters, including Introduction to MySQL stored procedure parametersOften, stored procedures have parameters. The parameters make the stored procedure more useful and reusable. A
parameter in a stored procedure has one of three modes: IN parameters
In addition, the value of an OUT parametersThe value of an Notice that the stored procedure cannot access the initial value of the INOUT parametersAn Defining a parameterHere is the basic syntax of defining a parameter in stored procedures:
In this syntax,
MySQL stored procedure parameter examplesLet’s take some examples of using stored procedure parameters. The IN parameter exampleThe following example creates a stored procedure that finds all offices that locate in a country specified by the input parameter
In this example, the
Suppose that you want to find offices locating in the USA, you need to pass an argument (
To
find offices in
Because the
Here’s the error:
The OUT parameter exampleThe following stored procedure returns the number of orders by order status.
The stored procedure
To find the number of orders that already shipped, you call
To get the number of orders that are in-process, you call
the stored procedure
The INOUT parameter exampleThe following example demonstrates how to use an
In this example, the stored procedure These statements illustrate how to call the
Here is the output: In this tutorial, you have learned how create stored procedures with parameters including
Was this tutorial helpful? What is the default mode of parameter?IN mode. It is the default mode. When we define an IN parameter in a stored procedure, the calling program has to pass an argument to the stored procedure.
What are 3 modes of parameters?PL/SQL procedure parameters can have one of three possible modes: IN, OUT, or IN OUT.
What is parameter in MySQL?In general, a parameter is a placeholder for a variable that contains some value of some type when executing a general-purpose query, or arguments and return values when a stored procedure is executed. Parameter is represented by MySql.
What is default parameter in stored procedure?The default is an input parameter. To specify an output parameter, the OUTPUT keyword must be specified in the definition of the parameter in the CREATE PROCEDURE statement. The procedure returns the current value of the output parameter to the calling program when the procedure exits.
|