FunctionsAndProcedures
[edit] Functions and Procedures
Our first program, HelloWorld was a function. In MySQL we have the option of creating both functions and procedures. This lesson will look at the differences between functions and procedures and looks at when you might use one and not the other. Lets start with functions as we are already familiar with how one is constructed and might work.
[edit] Functions
Functions are programs which when called return a single value, they must always return a value and will always return just one value. They can be relatively simple or a little more complex including multiple lines of code or just a single line. One advantage of functions is that we can call them from within an SQL statement. Take for example the CONCAT function which is already available in MySQL.
select concat(emp_name,´ ´,dept_id) from emps -> // +------------------------------+ | concat(emp_name,´ ´,dept_id) | +------------------------------+ | Roger 1 | | John 2 | | Alan 1 | +------------------------------+ 3 rows in set (0.00 sec)
As you can see here we can use functions within the SQL statement to perform transformations or calculations on data within the database tables. We can also do this with our own functions as we will see in later sections.
There really isn´t a great deal more to say about functions, of course they can do a lot more than just return strings as we have done so far and we will cover more of this in future sections.
[edit] Procedures
Procedures can do a little more than functions, they do not need to return anything but can if needed return many things. Procedures cannot be run from within SQL statements like functions, but must be called. Lets create a simple procedure and see how we call it.
create procedure helloprocedure() select ´Hello Procedure´ ; //
Download helloprocedure1.myp
As you can see creating a procedure is very similar to the creating a function. As a general rule we should be able to do the same things in procedures and functions, there is currently one exception to this which we will discuss in a later section. You can also see that I didn´t include a begin or end, this was simply to show that it was possible. Lets run the procedure and see what we get.
call helloprocedure() // +-----------------+ | Hello Procedure | +-----------------+ | Hello Procedure | +-----------------+ 1 row in set (0.00 sec)
You may also have noticed that we run a procedure differently from a function, this was done using the call command. For a little more practice lets add the begin and end statements. We will be calling drop procedure, in just the same way we used drop function, to remove the procedure before adding the changes.
drop procedure if exists helloprocedure // create procedure helloprocedure() begin select ´Hello Procedure 2´ ; end // call helloprocedure() // +-------------------+ | Hello Procedure 2 | +-------------------+ | Hello Procedure 2 | +-------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
Download helloprocedure2.myp
We will see more differences between functions and procedures as we continue, so we will now carry on and expand on our helloworld and helloprocedure programs.