HelloWorld
[edit] HelloWorld example
What better place to start a programming than with the ubiquitous HelloWorld. In this introduction we wont worry too much about the why or the how we will just get straight into the code.
Before we get into writing some code we will set up some data we will be using later in the tutorials. We are using a text editor to create scripts which we run using the source command in MySQL. Here is the contents of the script we have used to set up the test data.
drop database if exists pers
g
create database pers
g
use pers
g
create table emps(emp_id int NOT NULL,
emp_name varchar(30),
dept_id int,
salary decimal(5,2),
primary key(emp_id))
g
insert into emps (emp_id,emp_name,dept_id,salary)
values (1,’Roger’,1,2000.00),(2,’John’,2,2500.00),(3,’Alan’,1,2100.00)
g
select * from emps
g
create table dept (dept_id int NOT NULL,
description varchar(30),
primary key(dept_id))
g
insert into dept (dept_id,description)
values (1,’Information Technology’),(2,’Sales’)
g
select * from dept
g
Download setup.myp
If you haven’t used source before its a way of writing an SQL script in file and then running it in MySQL. To use source call it in the following way.
source c:/mysql/source/setup.myp
Of course exchange the path for the one you will be using. One word of warning if your using MySQL on windows, you need to use / rather than \ as MySQL can interpret \ as a call to a special function such as \h which would show the MySQL help.
Finally before we begin we need to do one more thing. When creating stored procedures within MySQL we need to use the ; character to terminate a line. However MySQL interprets this as a delimiter character so we need to tell MySQL to use another delimiter when running other code. We will be using //.
So lets set that now
delimiter //
Lets check this works with a simple SQL statement.
select ’hello’ // +-------+ | hello | +-------+ | hello | +-------+ 1 row in set (0.00 sec)
OK so lets get on to our first program which will be a function. If you have any experience of programming it is likely you will have seen HelloWorld or a program like it before. It won’t actually do a great deal but it will work and it will be easy. So lets give it a go.
create function helloworld() returns varchar(20) return "Hello World"; // Query OK, 0 rows affected (0.00 sec)
Download helloworld1.myp
Hopefully you will see the Query OK message which tells you that the function has been successfully created.
Now we want to call the function so we can see the result. We will talk more about the difference between a function and a procedure in a later section, but for now all we need to mention on the subject is that we call them in different ways. To call a function we do the following.
select helloworld() // +--------------+ | helloworld() | +--------------+ | Hello World | +--------------+ 1 row in set (0.02 sec)
So as you can see calling a function is pretty simple and the results are returned in the same was as a result set from an SQL call.
Well done that’s your first function completed. If you haven’t managed to get it working check the syntax for small spelling errors or the odd character missing.
[edit] HelloWorld explained
Now we have written our first function and been able to get some output lets look at what we actually did. Lets look at it word by word and see what each part means.
create OK so first up its create, which as you can imagine is telling the compiler that we want to create something. We wanted to create a function so we used the function keyword next. This tells MySQL we are going to be creating a function rather than another MySQL object such as a table. We then give that function a name helloworld in our case, after the name we put a set of brackets we won´t worry too much about those just yet except to mention that they are there and in fact need to be there. All pretty simple so far, next up is returns, which might not be so obvious. Returns is used to tell MySQL which sort of data type we will be returning from the function. We are returning a VARCHAR which is a variable length character string. We put the (20) in so MySQL knows that the string will be at most 20 characters.
So far we have simply told SQL we want to create a function called helloworld which will return a varchar of 20 characters in length, that´s all there is to it. We now have 1 of 2 options, our first is to create a single line program or our second option to create a program with more than one line. In our case it was just a single line so all we had to do was write that line. If your creating a function you must return a value, that´s done using the return keyword. We know its going to return a varchar so we just out put a simple string "Hello World".
A few moments ago I mentioned we had 2 options, the second option is to create a function with more than one line, while it is possible to create functions with only a single line they are not particularly useful. If we want to write more than one line we need to put the lines between a begin and an end. We can do this even with a single line function so lets do this now with a new version of helloworld.
Before we do that we need to talk about drop function. Drop function is used to remove the function from the database so that when we load our new version it doesn´t cause an error. We can drop in either of the following two ways.
drop function helloworld \
or
drop function if exists helloworld \
The difference between the two is that the first will produce an error message if the function we are trying to drop doesn´t exist. You can use either but i prefer the second as its a little more tidy. So lets make that change to our helloworld function.
drop function if exists helloworld // create function helloworld() returns varchar(20) begin return "Hello World 2"; end // select helloworld() //
Download helloworld2.myp
You may have spotted that we also changed the returned string to ´Hello World 2´ this was simply to show that we were actually looking at the new function rather than the old one. This time we will have seen this output.
+---------------+ | helloworld() | +---------------+ | Hello World 2 | +---------------+ 1 row in set (0.02 sec)
We can see that the addition of begin and end doesn´t effect the functionality. Lets move on to look at the difference between functions and procedures. the second type of program unit we can create.