UsingVariables
[edit] Using Variables
You may already be aware of user variables within MySQL. A user variable can be used to store information for later use in you session. A variable in broader terms can be viewed as a container for information. In MySQL functions and procedures we can create variables to hold information during our processing.
[edit] Variable Data Types
When we create a variable we need to give it a data type. A data type is the format and type of data we want to store in the variable, this could be a string, numeric or date and time. There are a number of different data types within MySQL and all are avaliable for use in our programs. For the moment we will concentrate on a few basic types but if you wish to see the full set available take a look here.
http://dev.mysql.com/doc/mysql/en/Column_types.html
We won't concern ourselves with all the different types for now so lets get straight in and see how we define a variable, what we can do with it and how we can change the value stored in it.
Lets use our HelloWorld function again to work with a variable. Rather than simply passing out a string we will pass out the contents of a variable.
To create a variable we use the following syntax.
declare var_name[,...] type [DEFAULT value]
The declare tells the compiler we are about to define the variable. We then give it a unique name, at this stage MySQL have yet to specify naming conventions and restrictions for variable names but its a given that you should avoid using reserved words and try to keep them short but descriptive. You can define more than one variable of the same type using one declare statement as we shall see in a moment. We then specify the data type we would like to assign to the variable, as mentioned before this can be any of the standard MySQL data types. Finally we can assign the variable a default value, we will look at this in more detail in a few minutes.
This may be a little bit too much to take in so lets try and define a variable so we can see one in action.
drop function if exists helloworld // create function helloworld() returns varchar(20) begin declare l_hello varchar(20) default ´Hello World 3´; return l_hello; end // select helloworld() // --------------- | helloworld() | --------------- | Hello World 3 | --------------- 1 row in set (0.00 sec)
Download helloworld3.myp
So we have now changed our helloworld function to use a variable. We added the declare keyword to tell the compiler that we were using a variable, named it l_hello and gave it a default value of ´Hello World 3´. We then replaced the string we had used previously with the variable. Now when we run the function we can see that the output has changed.
We mentioned that we could declare more than one variable on a single line, lets now try and declare more than on variable and also see how we can set a value for them.
drop function if exists helloworld // create function helloworld() returns varchar(20) begin declare l_hello, l_world, l_string varchar(20); set l_hello = ´Hello´; set l_world = ´World´; set l_string = concat(l_hello,´ ´,l_world,´ 4´); return l_string; end // select helloworld() // --------------- | helloworld() | --------------- | Hello World 4 | --------------- 1 row in set (0.00 sec)
Download helloworld4.myp
We are now starting to see our function look a little more substantial. We have introduced a few new concepts here which are quite important.
Firstly we can see that we have declared 3 variables using one declare statement. We could have used the default statement also but this would have been pointless as all three would have had the same value.
Next we used the set statement to assign a value to each of our variables. In the case of the first two this was a simple case of use a character string. But for the third we used the CONCAT function to join the other 2 strings together. This demostrates the fact that we can use functions within other functions, this goes for the standard functions such as CONCAT and also for ones we define ourselves. However... while trying to prove that we can call our own programs from within other functions the server kept crashing. Up to this point we had been running the source using version 5.0.1alpha, This is a good time to press home that release 5 is not a full release as yet and is not stable and should be used as such. There will be things that we can´t do at this early stage. But we installed the latest version 5.0.2alpha and the problem seemed to be fixed.
create function testhelloworld() returns varchar(20) return helloworld() // select testhelloworld(); // ------------------ | testhelloworld() | ------------------ | Hello World 4 | ------------------ 1 row in set (0.00 sec)
All this discussion of calling functions has taken us off the path a little so back to the matter in hand, variables. So far we have declared, set and used a string function for output, but we can use many other type of variables. Here´s a procedure which uses a date type variable and returns the current date.
drop function if exists showdate // create function showdate() returns date begin declare l_date date; set l_date = CURDATE(); return l_date; end // select showdate() ------------ | showdate() | ------------ | 2005-01-23 | ------------ 1 row in set (0.02 sec)
Download showdate.myp
As you can see in the function we call CURDATE which in effect does exactly the same thing as our function but it does demonstrate that we can define and use date data types.
This next one returns a number:
drop function if exists getsalary // create function getsalary() returns numeric begin declare l_salary numeric(13,2); set l_salary = 1000000.00; return l_salary; end //
Download getsalary.myp
These small examples will have shown you what variables are and how to create them. We will leave variables now as we will be using them in future programs and it is there that we will begin to see more clearly how and when we might use them more fully.