Parameters

Parameters

A parameter is a placeholder for a variable that is used to pass data to and from a stored procedure. An input parameter is used to pass data value to a stored procedure and an output parameter lets the stored procedure pass a data value back to the caller. A parameter can be defined by specifying the mode as well as the data type and an optional maximum length.

Parameters are used to make a stored procedure flexible. Another reason of using parameters is avoiding direct user inputs in a query string. A user input can result in a runtime error and in worst case a malicious input can potentially harm the system.

In MySQL a parameter can have three modes; IN, OUT and INOUT. If the mode of a parameter is defined as IN, it indicates that the application calling the stored procedure has to pass an argument. The stored procedure can not alter the value of the argument, rather it only works on a copy of the IN parameter and the original value of the parameter is retained after the stored procedure ends. A parameter defined as having OUT mode indicates that the stored procedure will pass an argument back to the caller. The value of an OUT parameter can change in the stored procedure and the new value is passed back in the end. The stored procedure, however, cannot access the initial value of the OUT parameter and its value is NULL when the procedure begins execution. The third mode, INOUT has properties of both the IN and OUT mode. The caller may pass an argument to the stored procedure and the stored procedure can work on it and pass the altered value back to the caller.

Syntax

[IN | OUT | INOUT] parameter_name datatype [(length)]

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/53lesson.sh and wait for the MySQL prompt to start-up.