Stored Routines


Stored routines is the umbrella term for the combination of stored procedures and stored functions. Stored routines allow you to save a set sequence of code in the MySQL server, and then call that sequence as needed. As a rule of thumb, stored functions are used to determine and return a simple value; stored procedures are used to perform a sequence of steps and likely do not return any values (but not always).

Stored routines can offer several benefits over plain old SQL (remember that a stored routine is just an encapsulated invocation of SQL):

Security. By placing code within a stored routine, you make that code less accessible, but the functionality remains the same.

Speed. With the bulk of the code stored in the database, less information has to be transferred to the server.

Convenience. If the same process is needed by 2 to 200 applications or interfaces, having it exist in only one location makes for easier maintenance.

Whether you experience all of these benefits or not depends upon your server setup, the stored routines themselves, and other factors. Stored routines aren't always the solution, but they often can be.

Stored routines make use of the proc table, found in the mysql database. This should have been installed when MySQL 5.0 was installed. If you upgraded your version of MySQL, you'll likely need to update the grant tables (see Chapter 1, "Installing MySQL") to create proc.

Because stored routines are like a mini-programming language unto themselves, a fair amount of basic syntax must be covered first. Then I'll show how to implement procedures and functions. And then you'll see even more syntax!

Tips

  • The implementation of stored routines differs from one database application to the next. All of the information provided here is MySQL-specific.

  • Not all MySQL client programs support the creation and invocation of stored routines. You can create them within the mysql client, but third-party applications may not have this capability yet.


Permissions Issues

In order to create or execute stored routines, you'll need to log in as a MySQL user with the appropriate permissions. There are three relevant privileges.

The first is CREATE ROUTINE, which allows the user to, yes, make a stored routine. The second is ALTER ROUTINE. This lets a user both modify and/or drop a stored routine. Finally, there is EXECUTE, meaning a user can invoke a saved stored routine. Note that any user that has CREATE ROUTINE privileges automatically has EXECUTE privileges as well.

There are two reasons I'm highlighting these permissions issues. First, if you want to run through these steps and create or execute a stored routine, you must connect to MySQL as a user that has been granted said permissions. Second, as a security measure, one MySQL user can be allowed to create, alter, and drop stored routines, while others can be given permission to just execute them.


Basic stored routine syntax

To get the ball rolling, I'll introduce the very basic syntax for creating stored functions:

CREATE FUNCTION name (arguments) RETURN type code


This is the syntax for creating stored procedures:

CREATE PROCEDURE name (arguments) code


I'll cover both in more detail later, but for now I want to focus on the name, arguments, and code sections, which are common to both.

For the routine's name, you should not use an existing keyword, SQL term, or function name. As with most things you name in MySQL, you should stick to alphanumeric characters and the underscore.

The arguments section is used to pass values to the routine. (In the case of a stored procedure, you can also return values through the arguments.) When a stored routine takes an argument, it's called exactly as you call MySQL functions:

SELECT ROUND(3.2)


The listed arguments are named and given types that correspond to the available data types in MySQL. Note that if your routine has no arguments, the parentheses are still required but are left empty.

The code section of this syntax is the most important. As your routines will normally contain multiple lines, you'll want to create a block by using BEGIN and END:

CREATE FUNCTION name (arguments) RETURNS type BEGIN   statement1;   statement2; END


Within the code block, each statement ends with a semicolon. This can cause a problem: when you go to add this stored function using the mysql client, it will think that the semicolon indicates the end of a command to be executed immediately (Figure 11.1). To prevent this, you should, within the mysql client, establish a new delimiter before you define the stored routine:

Figure 11.1. The semicolons used to mark the end of a statement within a stored routine will cause problems in the mysql client (which uses the semicolon as the end of a command).


DELIMITER $$ CREATE FUNCTION name (arguments) RETURNS type BEGIN   statement1;   statement2; END $$


The delimiter can be anything that will not appear in your code block. It could be \\ or ^:^--|. After you've defined the routine, reset the delimiter:

DELIMITER ;


Tips

  • Stored procedures can call other stored routines as part of their process.

  • All stored routines are associated with a specific database (as of MySQL 5.0.1). This has the added benefit of not needing to select the database (USE databasename) when invoking them. This also means that you cannot have a stored routine select a database.

  • Because stored routines are linked with databases, if you drop the database, you'll also drop any associated stored routine. However, because the routines are stored in the mysql .proc table, they won't be backed up when you back up a database unless you take special precautions.


Creating stored procedures

With a little bit of basic syntax in the bag, let's create a simple sample stored procedure. As I mentioned in the introduction to this concept, stored procedures normally execute a certain amount of code and may or many not return any values. For example, say you want a procedure that marks an invoice as paid. That might be defined like so:

CREATE PROCEDURE mark_invoice_paid(id INT) BEGIN   UPDATE invoices SET date_invoice_paid=NOW() WHERE invoice_id=id LIMIT 1; END


This procedure receives one argument, an integer that will be assigned to id. Then the procedure runs an UPDATE query using that value in a WHERE clause.

To invoke this procedure, you use the special CALL command:

CALL procedure_name (arguments) CALL mark_invoice_paid (23)


One last thing to note: when referring to arguments within a stored routine, never quote that argument. Even if the argument is a string that's used in a query, you would not quote it. You'll see this in the example.

To create and call a stored procedure:

1.

Log in to the mysql client as a user permitted to create stored procedures in the accounting database.

2.

Select the accounting database.

USE accounting;


3.

View the current list of login accounts (Figure 11.2).

SELECT client_id, AES_DECRYPT (login_name, 'w1cKet') FROM logins ORDER BY client_id;


The stored procedure you are about to create will add records to this table. To confirm that it worked, it'll help to know what or how many records are currently there.

If you have problems with this query, see Chapter 6, "MySQL Functions," where it is first discussed (along with the encryption functions). This table is also created in that section.

Figure 11.2. The logins table currently contains two records.


4.

Change the delimiter.

DELIMITER $$


Remember that this isn't part of stored procedures per se, but rather something that's necessary within the mysql client to avoid confusion and problems. After this line, statements in mysql are executed only upon entering $$.

5.

Define a stored procedure for adding login records (Figure 11.3).

CREATE PROCEDURE add_login (cid INT, name VARCHAR(20), pass VARCHAR(20)) BEGIN   INSERT INTO logins (client_id, login_name, login_pass) VALUES (cid, AES_ENCRYPT(name, 'w1cKet'), SHA1(pass)); END $$


One great example for using a stored procedure is to populate the logins table in the accounting database. This table is used to assign login accounts to clients. To populate it requires the preceding INSERT query. The problem is that any person or program that runs that query will therefore know what encryption algorithms are used and what the salt value is for AES_ENCRYPT(). By wrapping the query within a stored procedure, this is no longer a security risk.

The procedure takes three IN arguments: the client ID, the login name, and the login password. Notice that name and pass are not quoted in the query (...AES_ENCRYPT('name', 'w1cKet'), SHA1('pass')...), as that would have the effect of using the literals name and pass, not the received values.

Figure 11.3. Creating the first stored procedure, which adds records to the logins table.


6.

Change the delimiter back to the semicolon.

DELIMITER ;


If you don't do this, you'll need to keep ending statements and queries with $$.

7.

Add a new login account by invoking the stored procedure (Figure 11.4).

CALL add_login (9, 'whatever', 'mypass');


This line will have the effect of running the INSERT query in the stored procedure using these values.

Figure 11.4. This invocation of the stored procedure will add one new record to the logins table.


8.

Confirm that the new account has been added (Figure 11.5).

SELECT client_id, AES_DECRYPT (login_name, 'w1cKet') FROM logins ORDER BY client_id;


Figure 11.5. The new record has been successfully added (compare with Figure 11.2).


Tips

  • As stored routines can involve a lot of typing, you may want to create them in a text editor, and then copy and paste them into mysql.

  • You can have a space between your routine name and its opening parenthesis when calling a stored routine. This differs from how you use a MySQL function, where a space would cause an error (unless you take special measures).


Creating stored functions

Stored functions are much like stored procedures but are often simpler. For starters, a stored function only takes IN parameters (this will mean more in time). They differ in that they also must contain a RETURN clause, indicating the type of value returned by the function:

CREATE FUNCTION name (arguments) RETURNS type


Functions return scalar (single) values, like a number or a string. To do so, use

RETURN data


within the function's code body. The type of the data returned must match the type indicated in the function's initial definition line.

You cannot return a list of values from a stored function. Because of this limitation, you cannot use most SELECT statements, any SHOW queries, or any EXPLAIN queries in a stored function. However, because stored functions return scalar values, they can be used in queries like any of the existing MySQL functions. For example, the following stored function reformats a string so that only the first letter is capitalized:

CREATE FUNCTION capitalize (str VARCHAR(30)) RETURNS VARCHAR(30) BEGIN   RETURN CONCAT(UPPER(LEFT(str, 1)), SUBSTR(LOWER(str) FROM 2)); END


This may seem wordy, but it just returns the concatenation of two strings. The first string should be the first character in str, in uppercase. The second string is all of str, from the second character on, in lowercase. Once it is defined, you could use this function like so (Figure 11.6 shows the first result):

SELECT capitalize('washington') SELECT capitalize(contact_first_name) FROM clients


Figure 11.6. A stored function is usable like any MySQL function.


To create and use a stored function:

1.

Log in to the mysql client as a user permitted to create stored functions in the accounting database, if you have not already.

2.

Select the accounting database, if it is not already selected.

USE accounting;


Remember that all stored routines belong to a particular database.

3.

Change the delimiter.

DELIMITER $$


4.

Define a stored function for calculating the number of days between the current date and a given date (Figure 11.7).

CREATE FUNCTION days_old (d DATE) RETURNS INT BEGIN   RETURN DATEDIFF(d, CURDATE()); END $$


I want to create a function that will help me see how overdue some unpaid invoices are. To do so, I need to find out how late a payment is in days. The MySQL DATEDIFF() function returns the number of days between two given dates. In this function, it will be fed the current date as one of its arguments and the date sent to the function as the other. It returns an integer, which is the number of days different between the two dates.

Figure 11.7. This stored function makes use of a MySQL function (two actually, including CURDATE()) and returns an integer result.


5.

Change the delimiter back to the semicolon.

DELIMITER ;


6.

Find every overdue invoice (Figure 11.8).

[View full width]

SELECT invoice_id, days_old(invoice_date) AS days_unpaid FROM invoices WHERE date_invoice_paid IS NULL ORDER BY days_unpaid ASC;


The value returned by DATEDIFF(), and therefore by the stored routine, will be a negative number if the invoice date comes before the current date and a positive number if the opposite is true. In other words, unpaid invoices will have a negative value. This query gets me pretty close to my goal (of identifying unpaid invoices), but as you can see from the figure, it's not perfect. The main problem is that it lists all unpaid invoices, including recent invoices that aren't overdue or those in the future. This will be remedied in time. Regardless, you can see how the stored function was used within a query just like any MySQL function.

Figure 11.8. The stored function is used in a query to see how late each invoice is.


Declaring local variables

Now that you should have the basics of stored routines down, let's expand upon the topic. The examples demonstrated so far are perfectly legitimate, but there's so much more you can do. Stored routines are like small programs. They can even have their own variables. As with any programming language, it'll often be necessary to use variables as temporary storage within a stored routine. To do so, use the DECLARE statement:

DECLARE var_name var_type


The naming rules are pretty much the same as for everything else, but you absolutely want to make sure that your variables have unique identifiers. The types correspond to the MySQL data types:

DECLARE var1 INT DECLARE var2 DECIMAL(5,2) DECLARE var3 VARCHAR(20)


The only restrictions to declaring variables are:

  • The declarations must take place within a BEGIN...END code block.

  • The declarations must take place before any other statements (i.e., declarations must be immediately after the BEGIN).

When you declare a variable, you can also give it a default value:

DECLARE var_name var_type DEFAULT value


If not given a default value, a variable's value will be NULL.

Once you've declared a variable, you can assign it a value using SET:

SET name = value


As an implementation of this concept, I'll rewrite the capitalize function so that it will use variables. The new version will work just the same, but it will be easier to understand.

To use local variables:

1.

Log in to the mysql client as a user permitted to create stored functions in the accounting database, if you have not already.

2.

Select the accounting database, if it is not already selected.

USE accounting;


Actually, you can place this function in any database you want, as its usage won't be particular to accounting.

3.

Change the delimiter.

DELIMITER $$


4.

Begin defining the stored function.

CREATE FUNCTION capitalize (str VARCHAR(30)) RETURNS VARCHAR(30) BEGIN


This part of the function is unchanged from its early incarnation.

5.

Declare two variables.

DECLARE s1 CHAR; DECLARE s2 VARCHAR(29);


The function will use two variables, one of type CHAR, the other of type VARCHAR. They've both been given simple but unique names. The first variable will store the single initial capital letter. The second will store the rest of the string. As the string is a maximum of 30 characters long (see the argument definition in Step 4), this variable can be up to 29 characters long (after subtracting the first).

6.

Assign values to the variables.

SET s1 = UPPER(LEFT(str, 1)); SET s2 = SUBSTR(LOWER(str) FROM 2);


The s1 CHAR variable is assigned the value of the first letter in str, capitalized. The s2 variable is assigned the rest of str, in lowercase form.

7.

Complete the function definition and change the delimiter back to the semicolon (Figure 11.9).

  RETURN CONCAT(s1, s2); END $$ DELIMITER ;


The last step in the function is to return the concatenated form of the two variables put together. Again, all of this is exactly what the first version of the function did, but it's less crammed together now.

Figure 11.9. The capitalize function uses two local variables to break up its task into smaller pieces.


8.

Use the function to capitalize any word (Figure 11.10).

SELECT capitalize('shady');


Figure 11.10. Applying the capitalize function to a random word.


9.

Use the function to capitalize every client's contact person's name (Figure 11.11).

SELECT capitalize(contact_first_name) AS fn, capitalize(contact_last_name) AS ln FROM clients;


Figure 11.11. Applying the capitalize function to table columns.


Tips

  • Declared variables exist only within the BEGIN...END block. Once the END is reached, the variable no longer exists.

  • You can also use DECLARE to create conditions and handlers. Both are ways of dictating actions to take should a specific something happen. See the MySQL manual for syntax and usage of these.

  • Stored routines also support standard SQL variables (ones prefaced with @). These were introduced in Chapter 10, "Advanced SQL and MySQL."


Using control structures

Programming languages use both variables and control structuresconditionals and loopsto perform their tasks. You've already seen that stored routines support variables, so you might not be surprised to learn they support control structures as well.

For starters, there are two conditionals: IF and CASE (which is like a switch conditional in other languages). The syntax of the IF is natural:

IF condition THEN statements ELSEIF condition2 THEN statements ELSE statements END IF


You can have as many ELSEIF clauses as you'd like. They are also optional, as is the ELSE. If present, though, the ELSE must come last. If you are only checking to see if something has a particular value, you can use CASE instead of an IF ELSEIF conditional:

CASE thing WHEN value1 THEN statements WHEN value2 THEN statements ELSE statements END CASE


Again, the ELSE is optional, but it acts as the default action if none of the WHEN cases are a match for the thing's value.

There are three kinds of loops you can use in a stored routine. The first is the WHILE:

WHILE condition DO statements END WHILE


Similar to WHILE is REPEAT, except that its condition is checked after executing the loop once (it will always execute its statements at least once):

REPEAT statements UNTIL condition END REPEAT


Finally, there is the generic LOOP:

LOOP statements END LOOP


LOOP has no built-in way to exit the loop. Instead you must use the LEAVE command. It's normally used with a label to identify what exactly to leave:

myloop: LOOP   statements   IF condition   THEN LEAVE myloop   END IF END LOOP myloop


The labelmyloopis optional and acts as a way for you to name a control structure. It can be used to label any control structure, including BEGIN...END. Just type label_name: before the control structure and END structure_type label_name after.

An alternative to LEAVE is ITERATE. It reenters the loop (it terminates the current iteration and begins another one). It works within LOOP, REPEAT, and WHILE.

This is a lot of information, but hopefully this is not the first time you've encountered control structures. Let's implement some of these in two stored routines. Before proceeding, I should point out that I include no semicolons in the preceding commands. But in the mysql client you will need to use semicolons to terminate statements and control structures. You'll see this in the following steps.

Altering and Deleting Routines

If you need to alter a stored procedure, first log in as a user with the proper permissions. Then select the appropriate database. From this point, you can change a stored routine using

ALTER {PROCEDURE | FUNCTION} name [characteristics...]


Note that this only changes the characteristics of the stored routine, the characteristics being something I don't discuss in this chapter (see the manual). If what you really need to do is replace a routine's functionality, then first drop it:

DROP {PROCEDURE | FUNCTION} [IF EXISTS] name


Again, you must be logged in as a user with permission to delete stored routines. The final syntax might be something like

DROP FUNCTION days_old or DROP PROCEDURE IF EXISTS add_login


After you drop the routine, you can create it anew.


To use control structures:

1.

Log in to the mysql client as a user permitted to create stored functions in the accounting database, if you have not already.

2.

Select the accounting database, if it is not already selected.

USE accounting;


3.

Change the delimiter.

DELIMITER $$


4.

Begin defining the stored function.

CREATE FUNCTION is_overdue (d DATE) RETURNS BOOLEAN BEGIN


This new function is going to fix the problem with the days_old function. It takes one argument, a date, and returns a Boolean value (TRUE or FALSE).

5.

Declare and assign a value to a variable.

DECLARE num INT; SET num = DATEDIFF(d, CURDATE());


The num variable will store the difference as a number of days between the invoice date (or any date provided to this stored function) and the current date.

6.

Complete the function (Figure 11.12).

  IF num < -45 THEN RETURN TRUE;   ELSE RETURN FALSE;   END IF; END $$


This is a simple conditional that checks if num is less than 45, which I'm using as the number of days when an invoice is considered overdue. Remember that DATEDIFF() returns a negative number if the first date comes before the second. So an invoice two months old would have a return value of 60 (approximately).

If num is less than 45, the function returns trUE (saying that the invoice is overdue). Otherwise, the function returns FALSE. Notice that I use semicolons to conclude my statements (RETURN TRUE and RETURN FALSE) as well as the control structure (END IF).

Figure 11.12. The is_overdue() function uses an IF ELSE conditional to return either trUE or FALSE, depending upon the value of num.


7.

Change the delimiter back to the semicolon and use this new function (Figure 11.13).

DELIMITER ; SELECT invoice_id, invoice_amount, days_old(invoice_date) AS days FROM invoices WHERE date_invoice_paid IS NULL AND is_overdue(invoice_date);


As the function returns a Boolean value, it can be used in a WHERE conditional.

Figure 11.13. A more sophisticated stored function is used to whittle the list of unpaid invoices down to just the two overdue ones. Compare this with the results in Figure 11.8.


8.

Select the test database.

USE test;


For this next example I'll create a dummy table in the test database. This does assume that you have a test database and that you are connected as a user that has permission to create stored routines in it.

9.

Create a new table (Figure 11.14).

CREATE TABLE random_integers (   num INT UNSIGNED NOT NULL );


The table has only one column of type integer. It will be used to store some random integers (the table is well titled). The table will be populated by this next stored procedure.

Figure 11.14. A new, simple table is created for demonstration purposes.


10.

Change the delimiter and begin defining a stored procedure.

DELIMITER $$ CREATE PROCEDURE populate (many INT) BEGIN   DECLARE i INT DEFAULT 1;


The function takes one argument, an integer indicating how many rows to create. Then the procedure declares a variable of type INT, with a default value of 1.

11.

Create a loop.

WHILE i <= many DO


The loop's conditional is true as long as i is less than or equal to many. In other words, the loop will execute from i to many times.

12.

Complete the loop.

  INSERT INTO random_integers VALUES (FLOOR(1 + RAND() * 100));   SET i = i + 1; END WHILE;


The loop contains two statements. The first is the INSERT query that will populate the random_integers table. To do so, it generates a random value between 1 and 100. Because the RAND() function returns a number between 0 and 1, this formula1 + RAND() * 100must be used to create integers. This is taken from the MySQL manual page for the RAND() function, so see that explanation if you find this confusing.

The second statement in the loop increases the value of i by 1. If you forget to do this, the loop will be infinite (which is bad, trust me).

13.

Complete the procedure and change the delimiter back to the semicolon (Figure 11.15).

END $$ DELIMITER ;


Figure 11.15. This stored procedure uses a WHILE loop to execute an INSERT query a certain number of times.


14.

Invoke the new procedure (Figure 11.16).

CALL populate (10); CALL populate (5);


Figure 11.16. Calling the stored procedure twice. Notice that the reported number of affected rows may not be accurate when calling stored procedures.


15.

View the contents of the table (Figure 11.17).

SELECT * FROM random_integers;


Figure 11.17. The query shows how the table was populated using a loop within a stored procedure.


Tip

  • LEAVE can also be used to exit a stored routine (exit a BEGIN...END block). Therefore, it can be used to terminate a stored routine, if need be.





MySQL Visual QuickStart Guide Serie  .Covers My SQL 4 and 5
MySQL, Second Edition
ISBN: 0321375734
EAN: 2147483647
Year: 2006
Pages: 162
Authors: Larry Ullman

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net