PassingParameters
So far we have been getting output from functions using return and from procedures using a select statement. But we can also pass information into our programs and in the case of procedures out also. So far our programs have simply been a way to return static information, we know what we are getting back because its the same each time. To pass information in and out we use parameters. In the case of functions we can only pass in information so lets try that first. We can use yet another version of helloworld, but this time pass in a character string.
drop function if exists helloworld // create function helloworld(param1 varchar(100)) returns varchar(100) begin return CONCAT(´Hello World and ´,param1) ; end // select helloworld(´Alan´) // +----------------------+ | helloworld(´Alan´) | +----------------------+ | Hello World and Alan | +----------------------+ 1 row in set (0.00 sec)
Download helloworld5.myp
Lets explain what we did here, you can see that the function itself is very similar to ones we have written in the past. The first difference is that in the brackets following helloworld we have included the word param1 and a varchar assignment. param1 is the name of our parameter, if we were creating a procedure we would have included a keyword before this to show which type of parameter it is but as functions can only accept IN parameters this is not included. The parameter we will be accepting will be a character string so we assign it to a varchar of 100 characters in length. All we then need to do is reference the parameter within the function which we do in the return value. It is possible to change the value of the parameter in the function, its unlikely you would need to do so but it is possible.
The next change is the way in which we call the function. Previously we simply used helloworld() but we now need to pass the value into the function, this is done simply by placing the value we wish to pass in between the brackets, as we will see in a moment if you have more than one parameter you should pass them in using the same order as they are defined in the function. In some languages it´s possible to pass parameters either by position or by explicitly naming them, the MySQL documentation doesn´t say if passing in by naming them is possible, we did try to do this but couldn´t seem to do so, for now simply pass them in by position.
[edit] More than One
We can pass in as many parameters as we need to and as mentioned above we simply do this by adding more between the brackets like so.
drop function if exists helloworld // create function helloworld(param1 varchar(100),param2 varchar(100)) returns varchar(100) begin return CONCAT(param1,´ ´,param2) ; end // select helloworld(´Hello´,´World´) // +-----------------------------+ | helloworld(´Hello´,´World´) | +-----------------------------+ | Hello World | +-----------------------------+ 1 row in set (0.01 sec)
Download helloworld6.myp
[edit] Passing Parameters with Procedures
We can pass values both in and out of procedures. This is done in a similar way to functions except that we need to use the IN or OUT keywords to tell the compiler what to do with the parameters. So far we have used a select statement in a procedure to produce out put, lets now use an out parameter in the procedure to get the value.
drop procedure if exists helloprocedure // create procedure helloprocedure(OUT param1 VARCHAR(100)) begin set param1 = ´Hello World´; end // call helloprocedure(@a) // Query OK, 0 rows affected (0.00 sec)
What happened there? We create the procedure called it but didn´t get any output. First thing to mention is that when we call the procedure we used @a as the parameter. Using @a created a user variable, this is where the value is now stored. To access it we need to use a select statement to see the value.
select @a // +-------------+ | @a | +-------------+ | Hello World | +-------------+ 1 row in set (0.00 sec)
So there we have the value. With all the excitment of losing our output we skipped over a couple of points. Firstly you can see the OUT keyword needed in a procedure to tell the compiler what type of parameter it is. Secondly you can see all we needed to do in the procedure was use the SET statement to assign a value to param1.
[edit] IN, OUT Shake It All About
As with functions we can also accept parameters into a procedure. We said earlier that with procedures that you need to specify their type, this isn´t strictly true. This is because a parameter in a procedure is give an IN type by default therefore it is possible to not specify the type if it´s going to be an IN type, however its is always recommended to be as descriptive as possible when coding. We also said that there are only two types of parameters, this is also untrue as with procedures you can define a parameter that is both an IN and OUT parameter. Lets have a look at using a parameter without assigning a type and one that is an INOUT type.
drop procedure if exists helloprocedure // create procedure helloprocedure(param1 VARCHAR(100),INOUT param2 VARCHAR(100)) begin set param2 = concat(param1,´ ´,param2); end //
Download helloprocedure4.myp
So we have created the procedure, now we need to call it. Unlike the function we will be returning a value from the parameter therefore we can´t simply use a literal string value. We need to create a user variable, again unlike previous examples we need to create this first as we need to prepopulate it. This is done using the SET statement in the same way we assign values to variables. Lets do that now and then call the procedure.
set @a = ´World´ // call helloprocedure(´Hello´,@a) // Query OK, 0 rows affected (0.00 sec) select @a // +-------------+ | @a | +-------------+ | Hello World | +-------------+ 1 row in set (0.00 sec)
We created the user variable @a and set its value, this was then passed into the procedure along with another parameter, these were combined and placed into our second parameter, this was then passed out of the procedure and we were able to see its value using a select statement.
We looked previously at calling procedures and functions from within other functions and procedures. Anything we can do from the command line when calling program units we can also do when calling them from within other program units, lets test it out. First we will create a function which accepts a string parameter and returns that string reversed, then call this from within a procedure and return the result using an out parameter.
drop function if exists reverseit // create function reverseit(param1 varchar(100)) returns varchar(100) begin return reverse(param1); end // drop procedure if exists callreversefunc // create procedure callreversefunc(INOUT param1 VARCHAR(100)) begin set param1 = reverseit(param1); end // set @a = ´Hello World´ // call callreversefunc(@a) // +-------------+ | @a | +-------------+ | dlroW olleH | +-------------+ 1 row in set (0.00 sec)
Download parametertest.myp
One thing to note, in some languages the compiler will not allow you to create functions or procedures if any programs you call within them are not present, the MySQL compiler doesn´t seem to have this restriction at present, but the script was written so that the function being called by our main procedure was created first.
We have seen in this section how to create parameters, pass values into programs using them and also pass values out. We will use parameters in later programs to allow us to make our programs more dynamic.