This document introduces the subject of procedural programming within MySQL, highlights the main features and functions and gives a quick tutorial on the basics. It will be followed by part 2 which will introduce more advanced features of stored procedures within MySQL.
With the release of MySQL 5 AB has addressed one of the major reasons ‘serious’ database developers may have avoided using MySQL, despite its self confessed title of “The World's Most Popular Open Source Database”.
Stored procedures and functions are executable program units stored within the database server and can be used for a number of important data manipulation tasks that cannot be completed by SQL alone. Its true to say that with a mixture of some external programming and SQL inside MySQL it may be possible to achieve the same results as using stored procedures, but that’s not a reason to dismiss them or even think of them as in some way inferior, in fact the opposite is often true.
One of the great advantages of stored program units within a database is the ability to deal with data at source rather than pulling it down, manipulating it and passing it back. This is particularly useful in situations where a database is accessed by multiple interfaces. For example lets say you had both a Visual Basic application and a web front end accessing a MySQL database, you may have written some code both in VB and PHP to enforce a business rule, what happens when this business rule changes? Both sections of code need to be rewritten which introduces not just two programming jobs but two testing jobs and the possibility of two points of failure or error. Using procedural programming within MySQL we can create one set of business rules callable by both the VB and web front end, in fact callable from any front end we chose to implement in the future. There are certainly those who prefer to do all their processing at the front end but to a database developer that sounds as logical as writing your presentation layer in the database, data processing surely belongs in the database.
So onto MySQL’s implementation of procedural programming, lets first point out its early days and only available in the very latest releases (5.0 and up), when compared with other implementations such as Oracle and MS SQL Server it is very immature but MySQL have taken the decision to get the basic’s right first rather than pack it full of useful but ultimately problematic features. Having said this seasoned database developers will be pleased to see many of the features available in other databases.
So enough of the theory lets get straight into developing a few programs and see what can be done. Unfortunately the installation and set up of MySQL is outside the scope of this document, but we will be using version 5.0.0alpha release if you would like to play along. This can be downloaded free from the MySQL website and is relatively easy to install. Note that stored procedures are only available in version 5 and up so if your using a lower version you won’t be able to try it out.
So lets start. We will create a new database and set up a few tables first so we have some thing to work with.
We will come back to the tables later so now we will turn our attention to creating a simple function.create database pers; use pers; create table emps (emp_no integer, ename varchar(30),dept varchar(5)); create table dept (dept_id varchar(5),description varchar(30)); insert into emps values (1,'Bob','IT'), (2,'Alan','SAL'),(3,’Jane’,’SAL’); insert into dept values (‘SAL’,’Sales’),(‘IT’,’Information Technology’);
When writing procedures and functions we need tell the compiler when the line ends, this is done with a semi colon (;). However MySQL interprets this as a delimiter character when we are using the console so when entering our procedures and functions we need to use a different delimiter.
So lets set that now
Now whenever we would have normally used the ; we use //. So lets enter the source for the function.
Create function HelloWorld() Returns varchar(20) Return ‘Hello World’; Query OK, 0 rows affected (0.00 sec)
Hopefully you should have created the function and received the Query OK message. If you haven’t check what you have typed carefully.
Now lets run the function.
select helloworld() // +--------------+ | helloworld() | +--------------+ | Hello World | +--------------+ 1 row in set (0.00 sec)
So that shows you how simple it is to create a function in MySQL, to call that function and see the results. That’s all there is to it.
Lets now go into some more detail about how a procedural program is built within MySQL.
To create procedures and functions we use the following syntax
CREATE PROCEDURE sp_name ([parameter[,.
[characteristic ...] routine_body
CREATE FUNCTION sp_name ([parameter[,..
[characteristic ...] routine_body
A program unit name will follow the basic naming conventions used with in MySQL, its best to use simple but descriptive names and to avoid anything other than alphanumeric characters and underscores. The routine body is the main body of the code where you write your SQL statements. We will talk about parameters and characteristics later, so at this stage the only thing worth mentioning about the create syntax is the RETURNS keyword. As yet we haven’t talked about the difference between functions and procedures. A function may or not accept a parameter or parameters but must always return 1 and only 1 value to the calling program. A procedure again can accept none or more parameters but can also return none or more values. As a general rule of thumb functions are used to make simple changes to individual values, procedures are used to do more complex processing. So back to the RETURNS keyword, RETURNS must be used when writing a function to specify what datatype the procedure will return as being a function it will return a value.
The Routine Body
The routine body is where we place our SQL statements that will actually perform the calculations and manipulation of data. In our first simple function we had a single line of code, which in itself was fun but pretty useless. When adding more than one line of code we need to enclose our statements within a BEGIN and END. Even with a single line function its still possible to use BEGIN and END and in fact it should be positively encouraged. So lets do that now with our simple one line function. Firstly we need to drop the function.
Drop function HelloWorld CREATE FUCNTION HelloWorld() RETURNS VARCHAR2 BEGIN RETURN ‘Hello World’; END
It may at this point be worth creating a file to store your function so that we don’t have to keep dropping it and then recreating it by hand. Create a file like so
use pers DROP FUNCTION IF EXISTS HelloWorld CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20) RETURN 'Hello World 2';
I have saved mine in a folder called source in my MySQL folder. So now all I have to do to run the function is call the following
source c:/mysql/source/helloworld.sql select HelloWorld() //
A word of warning, use / rather than \ as certain folder names can cause MySQL to think your calling various MySQL commands.
Those who are familiar with other programming languages will be equally familiar with the term variable. A variable is in essence a named and reserved area of memory, which can be referenced from within your program. We can create variables using any datatype supported in MySQL.
To create a variable we using the following syntax
DECLARE variable_name variable_type;
Where the variable_name is the unique name we wish to assign and variable type is the datatype of the variable we wish to create.
So to create a variable called output_text containing a VARCHAR of 20 characters we use the following syntax
DECLARE output_text VARCHAR(20);
Now within our program we can set and reference that variable like so.
CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20) BEGIN DECLARE outtext VARCHAR(20); SET outtext = 'Hello World'; RETURN outtext; END
The SET keyword simple allows us to assign a value to the variable. However what we can do is give the variable a default value so on creation it is populated.
Lets try that out using out HelloWorld function.
CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20) bullet just won't do. Instead, you feel the need to use animage for each bullet. In the past, the only way to achieve this sortof effect was to fake it. Now all you need is alist-style-image declaration.
Yes, that's really all there is to it. One simpleurl value, and you're putting images in forbullets, as you can see in Figure 7-81. BEGIN DECLARE output_text VARCHAR(20) DEFAULT 'HelloWorld'; RETURN output_text; END
We can also declare more than one variable of the same type at the same time.DECLARE output_text, name, department VARCHAR;
Parameters are another common programming tool. Using parameters we can pass data into and in the case of procedures out of our program units. Lets extend our HelloWorld function to accept a parameter add this to our ‘hello world’ variable and return the result.CREATE FUNCTION HelloWorld(p_inparam VARCHAR(20)) RETURNS VARCHAR(20) BEGIN DECLARE output_text VARCHAR(20); SET output_text = p_inparam; RETURN output_text; END
This time when we run the function we need to supply the parameter like soselect HelloWorld(' Hello World') //
Because we know functions only ever return one value its easy for us to run them using the select command within MySQL, however procedures do not need to return a value or can in fact return more than one. Lets see how to create and run a procedure, accept the values it returns and how we can display those values.
Creating a procedure is just as simple as creating a function. Here we are going to create a procedure which will return a simple string.use pers DROP PROCEDURE IF EXISTS HelloWorld CREATE procedure HelloWorld(out p_text VARCHAR(30)) BEGIN set p_text = 'Hello World'; END
So now we need to run the procedure. Because MySQL doesn’t know if the procedure will return a value we need to call it in a different way. We can pass values in and out of the procedure using user variables (@). The syntax to run the procedure is as follows.CALL HelloWorld(@out)
Then to see the result.Select @out // +-------------+ | @out | +-------------+ | Hello World | +-------------+ 1 row in set (0.00 sec)
So as you can see, it’s easy to call both functions and procedures with MySQL.
So far our function or procedures are not offering us a great deal so lets use another feature of stored procedures called select into. Rather than hard coding the variable value or passing in as a parameter as we have done so far we can select the value from a table.
Note: Currently functions are restricted so that they cannot access tables. This also applies to some set and select statements. In the MySQL documentation it says this restriction will be lifted soon.
So lets try and use the select into functionality in a procedure. We will use the database tables we have set up to do this. A useful procedure would be if we could request a department description (we could do this easily using a straight SQL statement but that would bring a quick end to this document).CREATE procedure GetDept(out p_dept_description VARCHAR(30)) BEGIN SELECT description INTO p_dept_description FROM DEPT LIMIT 1; END
Now lets call the procedure and see what the result was.Select @dept // +-------+ | @dept | +-------+ | Sales | +-------+ 1 row in set (0.00 sec)
In this simple example we are simply returning the first department, what would be much more useful would be the ability to pass in a department id and get the corresponding description. So lets do that now.CREATE procedure GetDept(out p_dept_description VARCHAR(30), in p_dept_id VARCHAR(5)) BEGIN SELECT description INTO p_dept_description FROM DEPT WHERE dept_id = p_dept_id; END call GetDept(@out,'IT') // mysql> select @out // +---------------------------+ | @out | +---------------------------+ | Information Technology | +---------------------------+ 1 row in set (0.00 sec)
We’re now starting to see the power of stored procedures within MySQL and how they might be more useful than standard SQL statements. It’s true that we could have achieved the same thing using an SQL select statement, but using stored procedures allows us to protect the database and increase the level of security. Rather than giving direct access to the tables we can place procedures in between to do a number of tasks and maintain the data we store in tables.
This concludes the first part of this introductory look at stored procedures within MySQL, but there is still more to learn and in part 2 we will look at how we can expand on the functionality we have seen so far.
Use DOM to directly manipulate the information stored in the document (which DOM turns into a tree of nodes). This document object is created by the DOM XML parser after it reads in the XML document. This option leads to messy and hard-to-understand code. Also, this works better for document-type data rather than just computer generated data (like data structures and objects used in your code). Create your own Java object model that imports information from the XML document by using either SAX or DOM. This kind of object model only uses SAX or DOM to initialize itself with the information contained in the XML document(s). Once the parsing and initialization of your object model is completed, DOM or SAX isn't used anymore. You can use your own object model to accessed or modify your information without using SAX or DOM anymore. So you manipulate your information using your own objects, and rely on the SAX or DOM APIs to import the information from your ApplicationML file into memory (as a bunch of Java objects). You can think of this object model as an in-memory instance of the information that came was "serialized" in your XML document(s). Changes made to this object model are made persistent automatically, you have to deal with persistence issues (ie, write code to save your object model to a persistence layer as XML). Create your own Java object model (adapter) that uses DOM to manipulate the information in your document object tree (that is created by the parser). This is slightly different from the 2nd option, because you are still using the DOM API to manipulate the document information as a tree of nodes, but you are just wrapping an application specific API around the DOM objects, so its easier for you to write the code. So your object model is an adapter on top of DOM (ie, it uses the adapter pattern). This application specific API uses DOM and actually accesses or modifies information by going to the tree of nodes. Changes made to the object model still have to be made persistence (if you want to save any changes). You are in essence creating a thin layer on top of the tree of nodes that the parser creates, where the tree of nodes is accessed or modified eventually depending on what methods you invoke on your object model.
Depending on which of the three options you use to access information using your Java classes, this information must at some point be saved back to a file (probably to the one from which it was read). When the user of your application invokes a File->Save action, the information in the application must be written out to an ApplicationML file. Now this information is stored in memory, either as a (DOM) tree of nodes, or in your own proprietary object model. Also note that most DOM XML parsers can generate XML code from DOM document objects (but its quite trivial to turn a tree of nodes into XML by writing the code to do it yourself). There are 2 basic ways to get this information back into an ApplicationML file:
only way content will inherit this line height is if it is inheritedby an inline element. Most text isn't contained by an inlineelement. Thus, if we pretend that each line is contained by thefictional LINE element, then the model works outvery nicely.
188.8.131.52. Generating a line box
Here arethe SPAN text (which is set to bebolder) will inherit the value of100 and then evaluate to the next-heaviest face,which is the Bold face and which has a numerical weight of700. Figure 5-11 shows us thevisual result of all this.
Figure 5-11. Greater weight will usually confer visual boldness
Let's take this all one step further, and add two more rules,plus some markup, to illustrate how all this works (see Figure 5-12 for the results):
Figure 7-41. The incredible disappearing border
If you'll remember, the terminology used in the previous section was that a border with a style of none does not exist. Those words were picked carefully because they help explain what's going on here. Since the border doesn't exist, it can't have any width, so the width is automatically set to 0 (zero). This may seem completely backward, but it actually makes a great deal of sense. After all, if a drinking glass is empty, you can't really describe it as being half-full of nothing. You can only discuss the depth of a