MySQL Stored Procedures: Part 1

What are Stored Procedures

MySQL 5.0 finally introduces functionality for Stored Procedures. So what exactly are stored procedures? That is the kind of question that gets database professionals who use other DBMS's raising their eyebrows. Stored procedures have been integral to Oracle, PostgreSQL, DB-2, MS-SQL server and others for years, and it has long been a sore point that MySQL has not had them. But there is no snobbery here - if you are a MySQL newbie, or have been using MySQL for years and want to find out what all the fuss is about, read on. If it is your eyebrows that are raised, and you just want to know how MySQL implements them, you will be relieved to know MySQL stored procedures are very similar to the DB2 implementation, as both are based on the SQL:2003 standard.
A stored procedure is simply a procedure that is stored on the database server. MySQL developers have to date unthinkingly written and stored their procedures on the application (or web) server, mainly because there hasn't been an option. That has been limiting. Some have claimed that there are two schools of thought - one claiming that logic should be in the application, the other saying it should reside in the database. However, most professionals would not bind themselves to one or other viewpoint at all times. As always, there are times when doing either makes sense. Unfortunately, some of the staunchest adherents of the in the application school are only there because until now they have had no choice, and it is what they are used to doing. So why would we want to place logic on the database server?

Why use stored procedures?

• They will run in all environments, and there is no need to recreate the logic. Since they are on the database server, it makes no difference what application environment is used - the stored procedure remains consistent. If your setup involves different clients, different programming languages - the logic remains in one place. Web developers typically make less use of this feature, since the web server and database server are usually closely linked. However, in complex client-server setups, this is a big advantage. The clients are automatically always in sync with the procedure logic as soon as its been updated.
• They can reduce network traffic. Complex, repetitive tasks may require getting results, applying some logic to them, and using this to get more results. If this only has to be done on the database server, there is no need to send result sets and new queries back and forth from application server to database server. Network traffic is a common bottleneck causing performance issues, and stored procedures can help reduce this. More often though, it is the database server itself that is the bottleneck, so this may not be much of an advantage.

A simple example

A stored procedure is simply some SQL statements. Almost any valid SQL can go inside a stored procedure, with a few exceptions, which we will look at, at a later date. Let's set up a basic stored procedure first. This one will simply say 'Hello' in the Xhosa language - Molo.

mysql> CREATE PROCEDURE molo() SELECT 'Molo';

Query OK, 0 rows affected (0.00 sec)

It is as simple as that. And to call it:

mysql> CALL molo()\G

*************************** 1. row ***************************

Molo: Molo

1 row in set (0.00 sec)

Hardly useful, but the basics are there. CREATE PROCEDURE sp_name() will define the procedure, and CALL sp_name() will call the procedure.

Parameters

The real benefit of a stored procedure is of course when you can pass values to it, as well as receive values back. The concept of parameters should be familiar to anyone who has had experience with any procedural programming experience.
There are three types of parameter:

• IN: The default. This parameter is passed to the procedure, and can change inside the procedure, but remains unchanged outside.
• OUT: No value is supplied to the procedure (it is assumed to be NULL), but it can be modified inside the procedure, and is available outside the procedure.
• INOUT: The characteristics of both IN and OUT parameters. A value can be passed to the procedure, modified there as well as passed back again.

Mastery of stored procedures does require knowledge of session variables. Most of you probably know how to use session variables already, but if not, the concept is simple. You can assign a value to a variable, and retrieve it later. Here is an example, setting the variable x to the Xhosa word for hello to a group of people.

mysql> SET @x='Molweni';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G

*************************** 1. row ***************************

@x: Molweni

1 row in set (0.00 sec)

An IN example

Here is an example of a stored procedure demonstrating the use of an IN parameter. Since IN is the default, there is no need to specify the parameter as such.

mysql> CREATE PROCEDURE sp_in(p VARCHAR(10)) SET @x = P;

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_in('Molo');

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G

*************************** 1. row ***************************

@x: Molo

1 row in set (0.00 sec)

The session variable @x is set inside of the procedure, based upon the parameter P, which is passed to the procedure, and remains unchanged.

An OUT example

mysql> SET @x='Molweni';

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE sp_out(OUT p VARCHAR(10)) SET P='molo';

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_out(@x);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G

*************************** 1. row ***************************

@x: molo

1 row in set (0.00 sec)

We reset @x just to make sure the final result is not a legacy of the previous procedure. This time, the parameter P is changed inside of the procedure, while the session variable is passed to the procedure, ready to receive the result.

An INOUT example

mysql> CREATE PROCEDURE sp_inout(INOUT P INT) SET @x=P*2;

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_inout(2);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G

*************************** 1. row ***************************

@x: 4

1 row in set (0.00 sec)

Here, a parameter is passed to the procedure, used in the calculation, and the results are made available to the session variable @x.

Getting information about existing stored procedures

It is clearly necessary to be able to get more information about any stored procedures later, such as a list of procedures available, and the definitions. There are MySQL-specific ways to do this, and the syntax should be familiar to experienced MySQL users. SHOW PROCEDURE STATUS returns a list of stored procedures, and some metadata about them, while SHOW CREATE PROCEDURE returns the definition of a particular procedure.

mysql> SHOW PROCEDURE STATUS\G

************* 1. row ************

           Db: test

         Name: molo

         Type: PROCEDURE

      Definer: ian@localhost

     Modified: 2005-07-29 19:20:27

      Created: 2005-07-29 19:20:27

Security_type: DEFINER

      Comment:

************* 2. row ************

           Db: test

         Name: sp_in

         Type: PROCEDURE

      Definer: ian@localhost

     Modified: 2005-08-02 11:58:34

      Created: 2005-08-02 11:58:34

Security_type: DEFINER

      Comment:

************* 3. row ************

           Db: test

         Name: sp_inout

         Type: PROCEDURE

      Definer: ian@localhost

     Modified: 2005-08-02 12:16:18

      Created: 2005-08-02 12:16:18

Security_type: DEFINER

      Comment:

************* 4. row ************

           Db: test

         Name: sp_out

         Type: PROCEDURE

      Definer: ian@localhost

     Modified: 2005-08-02 12:01:56

      Created: 2005-08-02 12:01:56

Security_type: DEFINER

      Comment:

4 rows in set (0.00 sec)

It will become clear what all these fields mean as we progress through the rest of this tutorial series.

mysql> SHOW CREATE PROCEDURE molo\G

*************************** 1. row ***************************

       Procedure: molo

        sql_mode:

Create Procedure: CREATE PROCEDURE 'test'.'molo'()

SELECT 'Molo'

1 row in set (0.00 sec)

There is also an ANSI-standard way of doing it, which will be more familiar to other users.

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G

*************************** 1. row ***************************

     SPECIFIC_NAME: molo

   ROUTINE_CATALOG: NULL

    ROUTINE_SCHEMA: test

      ROUTINE_NAME: molo

      ROUTINE_TYPE: PROCEDURE

    DTD_IDENTIFIER:

      ROUTINE_BODY: SQL

ROUTINE_DEFINITION: SELECT 'Molo'

     EXTERNAL_NAME: NULL

 EXTERNAL_LANGUAGE: NULL

   PARAMETER_STYLE:

  IS_DETERMINISTIC: NO

   SQL_DATA_ACCESS: CONTAINS_SQL

          SQL_PATH: NULL

     SECURITY_TYPE: DEFINER

           CREATED: 2005-07-29 19:20:27

      LAST_ALTERED: 2005-07-29 19:20:27

          SQL_MODE:

   ROUTINE_COMMENT:

           DEFINER: ian@localhost

*************************** 2. row ***************************

     SPECIFIC_NAME: sp_in

   ROUTINE_CATALOG: NULL

    ROUTINE_SCHEMA: test

      ROUTINE_NAME: sp_in

      ROUTINE_TYPE: PROCEDURE

    DTD_IDENTIFIER:

      ROUTINE_BODY: SQL

ROUTINE_DEFINITION: SET @x = P

     EXTERNAL_NAME: NULL

 EXTERNAL_LANGUAGE: NULL

   PARAMETER_STYLE:

  IS_DETERMINISTIC: NO

   SQL_DATA_ACCESS: CONTAINS_SQL

          SQL_PATH: NULL

     SECURITY_TYPE: DEFINER

           CREATED: 2005-08-02 11:58:34

      LAST_ALTERED: 2005-08-02 11:58:34

          SQL_MODE:

   ROUTINE_COMMENT:

           DEFINER: ian@localhost

*************************** 3. row ***************************

     SPECIFIC_NAME: sp_inout

   ROUTINE_CATALOG: NULL

    ROUTINE_SCHEMA: test

      ROUTINE_NAME: sp_inout

      ROUTINE_TYPE: PROCEDURE

    DTD_IDENTIFIER:

      ROUTINE_BODY: SQL

ROUTINE_DEFINITION: SET @x=P*2

     EXTERNAL_NAME: NULL

 EXTERNAL_LANGUAGE: NULL

   PARAMETER_STYLE:

  IS_DETERMINISTIC: NO

   SQL_DATA_ACCESS: CONTAINS_SQL

          SQL_PATH: NULL

     SECURITY_TYPE: DEFINER

           CREATED: 2005-08-02 12:16:18

      LAST_ALTERED: 2005-08-02 12:16:18

          SQL_MODE:

   ROUTINE_COMMENT:

           DEFINER: ian@localhost

*************************** 4. row ***************************

     SPECIFIC_NAME: sp_out

   ROUTINE_CATALOG: NULL

    ROUTINE_SCHEMA: test

      ROUTINE_NAME: sp_out

      ROUTINE_TYPE: PROCEDURE

    DTD_IDENTIFIER:

      ROUTINE_BODY: SQL

ROUTINE_DEFINITION: SET P='molo'

     EXTERNAL_NAME: NULL

 EXTERNAL_LANGUAGE: NULL

   PARAMETER_STYLE:

  IS_DETERMINISTIC: NO

   SQL_DATA_ACCESS: CONTAINS_SQL

          SQL_PATH: NULL

     SECURITY_TYPE: DEFINER

           CREATED: 2005-08-02 12:01:56

      LAST_ALTERED: 2005-08-02 12:01:56

          SQL_MODE:

   ROUTINE_COMMENT:

           DEFINER: ian@localhost

4 rows in set (0.01 sec)

Let's introduce some more complex examples. First, we will create a sample table.

mysql> CREATE table sp1 (id INT, txt VARCHAR(10), PRIMARY KEY(id));

Query OK, 0 rows affected (0.11 sec)

Delimiters, and multi-statement procedures

Stored procedures of course are not that useful if they are just one statement. The effects of all the procedures we have looked at so far could have had been duplicated much more easily with a single SQL statement. Useful procedures are much longer than that. Those of you who are on the ball may be thinking of a complication. How can we differentiate between multiple statements inside the procedure, and the end of the procedure? We have to create a different delimiter to end the CREATE PROCEDURE statement. Here is how:

mysql> DELIMITER | 

Note that there is no semicolon after the '|' symbol, which we will use as the delimiter for our purposes. You have to choose a delimiter that does not appear in your procedure, and it can be more than one character.

mysql> CREATE PROCEDURE sp_ins (P VARCHAR(10))

    -> BEGIN

    ->  SET @x=CHAR_LENGTH(P);

    ->  SET @y = HEX(P);

    ->  INSERT INTO sp1(id,txt) VALUES(@x,@y);

    -> END|

Query OK, 0 rows affected (0.05 sec)

mysql> CALL sp_ins('ABC');

    -> |

Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER ;

mysql> SELECT * FROM sp1\G

*************************** 1. row ***************************

 id: 3

txt: 414243

1 row in set (0.00 sec)

Note what happened when we tried to call the procedure. Because MySQL was still using the | symbol as a delimiter, and not the semicolon, the statement did not run after the semicolon. We first needed to close it with the piping symbol. Afterwards, we reset the delimiter back to normal, and test that the records were correctly added to the sp1 table.

Procedure variables

Stored procedures do not only make use of the standard SQL statements. You can also DECLARE variables that exist only inside the procedure, as well as assign values to them with the SET statement without using the '@' symbol, required for session variables. Here is an example.

mysql> DELIMITER |

mysql> CREATE PROCEDURE sp_declare (P INT)

    -> BEGIN

    ->  DECLARE x INT;

    ->  DECLARE y INT DEFAULT 10;

    ->  SET x = P*y;

    ->  INSERT INTO sp1(id,txt) VALUES(x,HEX('DEF'));

    -> END|

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL sp_declare(4);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM sp1\G

*************************** 1. row ***************************

 id: 3

txt: 414243

*************************** 2. row ***************************

 id: 40

txt: 444546

2 rows in set (0.00 sec)

Variables that are declared without a default, such as x above, are set to NULL.

Populating variables from a pre-existing table

Now that you have seen how to INSERT records into a table from within a procedure, you may be wondering how we get values out of an existing table. Here is a simple example.

mysql> DELIMITER |

mysql> CREATE PROCEDURE sp_select ()

    -> BEGIN

    ->  DECLARE x INT;

    ->  DECLARE y VARCHAR(10);

    ->  SELECT id,txt INTO x,y FROM sp1 LIMIT 1;

    ->  SELECT x,y;

    -> END|

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL sp_select()\G

*************************** 1. row ***************************

x: 3

y: 414243

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

For now, we just returned the first record, to introduce the syntax. In next month's tutorial, we will look at iterations, which allow us to loop through multiple records.

MySQL Stored Procedures: Part 2

Characteristics

Last month we created skeleton stored procedures, to introduce the concepts. Let's start off this month by looking at all of the characteristics that you can define when creating a stored procedure. Here is a sample CREATE PROCEDURE statement making use of all of the clauses.

CREATE PROCEDURE sp_full() 

LANGUAGE SQL 

NOT DETERMINISTIC 

MODIFIES SQL DATA 

SQL SECURITY DEFINER 

COMMENT 'Returns a random number' 

SELECT RAND()

LANGUAGE SQL simply means that the language used to write the procedure is SQL. MySQL cannot yet use procedures in any other languages, but it probably will in the future. It is, unsurprisingly, the default.
NOT DETERMINISTIC implies that the procedure may produce different results, given the same inputs. The alternative is DETERMINISTIC, if the procedure always gives the same results given the same input. The default is NOT DETERMINISTIC. Procedures that are NOT DETERMINISTIC have implications for binary logging and replication (if it performs updates, it means that slave data could be different to master data, and for this reason you can't define a NOT DETERMINISTIC procedure if binary logging is enabled). Besides binary logging, MySQL as yet makes no use of this information (potentially the optimizer could use it to improve performance).
MODIFIES SQL DATA indicates that data could be modified by the procedure. Alternatives are CONTAINS SQL (which would be SQL that does not read or write data), NO SQL or READS SQL DATA. The default is CONTAINS SQL.
SQL SECURITY DEFINER indicates that MySQL should check the privileges of the user that defined the procedure when executing the procedure. The alternative is SQL SECURITY INVOKER, which causes MySQL to use the privileges of the user calling the procedure. The default is SQL SECURITY DEFINER.
COMMENT is self-explanatory, and can be used to describe the procedure. It is a non-standard MySQL extension.

SQL statements unique to Stored Procedures

A procedure may need to use conditions or loops, and standard SQL is not sufficient for these purposes. For that reason, the SQL standard includes a number of statements that only occur inside stored procedures. We already looked at the DECLARE statement last month. Let's take a look now at conditions and loops. The concepts should all be familiar to anyone with programming experience.

Conditions: IF THEN ELSE

MySQL implements conditions in the stock standard, tried and tested way.

IF condition 

THEN statement/s 

ELSE statement/s

END IF

The logic is simple. If the condition is met, then a set of statements are carried out. Else, another set of statements are carried out.
Here is an example:

mysql> CREATE PROCEDURE sp_condition(IN var1 INT) 

BEGIN 

 IF (var1 > 10)  

  THEN SELECT 'greater'; 

  ELSE SELECT 'less than or equal'; 

 END IF; 

END|

Query OK, 0 rows affected (0.06 sec)

mysql> CALL sp_condition(5)\G

*************************** 1. row ***************************

less than or equal: less than or equal

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_condition(15)\G

*************************** 1. row ***************************

greater: greater

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

I presume you can follow the logic easily enough. NULL values throw a curveball into the mix - an argument against their usage is that they undermine standard boolean logic. Nevertheless, you may come across instances where they are used. Let's see what happens if we pass a NULL to the procedure we have just created:

mysql> CALL sp_condition(NULL)\G

*************************** 1. row ***************************

less than or equal: less than or equal

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

As you probably expected, NULL > 10 evaluates to false, so the ELSE statement is run. Let's switch things around:

mysql> CREATE PROCEDURE sp_condition2(IN var1 INT) 

BEGIN 

 IF (var1 <= 10) 

  THEN SELECT 'less than or equal'; 

  ELSE SELECT 'greater';

 END IF; 

END|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_condition2(NULL)\G

*************************** 1. row ***************************

greater: greater

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Using what should in boolean logic be an identical test, we get a differing result. NULL <= 10 also evaluates to false. NULL's are beyond such dualistic thinking. This should be enough of a warning to be cautious of NULL's!

Conditions: CASE

The other construct used for condition is CASE.

CASE variable 

WHEN condition1 statement/s 

WHEN condition2 statement/s

ELSE statement/s

END CASE

This construct is used when the same variable is being tested against multiple conditions. Instead of long nested IF statements, using a CASE statement allows the procedure code to be more compact and easily readable. Here is an example:

mysql> CREATE PROCEDURE sp_case(IN var1 INT) 

BEGIN 

 CASE var1 

  WHEN 1 THEN SELECT 'One'; 

  WHEN 2 THEN SELECT 'Two'; 

  ELSE SELECT 'Something else'; 

 END CASE; 

END|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_case(1)\G

*************************** 1. row ***************************

One: One

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_case(2)\G

*************************** 1. row ***************************

Two: Two

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_case(3)\G

*************************** 1. row ***************************

Something else: Something else

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Loops: WHILE

Loops are a vital component of procedures - they allow the same portion of code to be repeated a number of time. WHILE loops, the first type of loop we are going to look at, continuously repeat a block while a particular condition is true.

WHILE condition DO

statement/s

END WHILE

Here is an example. Be careful when entering it though, and with all loops! Always create your procedures on a test server first. All novice (and some not so novice) programmers at one time or another create an infinite loop. If you had forgotten to increment var1 in the procedure, it would always stay as whatever you had passed to the procedure. If this were anything less than 20, the loop would continue indefinitely.

mysql> CREATE PROCEDURE sp_while(IN var1 INT)

BEGIN 

 WHILE (var1 < 20) DO 

  SELECT var1; 

  SET var1=var1+1; 

 END WHILE;

END|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_while(18)\G

*************************** 1. row ***************************

var1: 18

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 19

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_while(22)\G

Query OK, 0 rows affected (0.00 sec)

Note that when we called the procedure, passing 22, no statements were executed, as the condition failed immediately.

REPEAT UNTIL

The other commonly used loop is the REPEAT UNTIL construct.

REPEAT 

statement/s

UNTIL condition

END REPEAT

The statements are repeatedly performed until the condition is met. A difference to the WHILE loop is that the condition is only tested after the statements have been performed, so there is always at least one instance of the statements being run. Here is an example:

mysql> CREATE PROCEDURE sp_repeat(IN VAR1 INT)

BEGIN 

 REPEAT 

  SELECT var1; 

  SET var1=var1+1; 

  UNTIL var1>5 

 END REPEAT; 

END|

Query OK, 0 rows affected (0.09 sec)

mysql> CALL sp_repeat(3)\G

*************************** 1. row ***************************

var1: 3

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 4

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 5

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_repeat(8)\G

*************************** 1. row ***************************

var1: 8

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Note, that even when we call the procedure with 8, which means that the condition evaluates to true (and exits the loop) the first time it is encountered, the SELECT statement is still executed once, as the condition is only tested at the end.

LABELS, LEAVES and LOOPS

Labels are simply text strings that are used to mark part of the procedure. They can simply play the role of comments, or be part of the logic, as we will see below in the LOOP construct. Here is an example of LABELS used to comment the start of a procedure. The label is begin1. This may not seem useful here, but complex statements can be deeply nested, and in that case, clear labels will make the logic easier to follow. They also play a vital logical role, as we will see later.

mysql> CREATE PROCEDURE sp_label() 

 begin1: BEGIN 

END|

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE sp_label2() 

 begin1: BEGIN 

 END begin1|

Query OK, 0 rows affected (0.00 sec)

In the second example, the end is also labeled. If an end label exists, it must match a start label of the same name. You can use LABELS before BEGIN, WHILE, REPEAT and LOOP (introduced below) statements, as well as the accompanying END statements, and also as targets for ITERATE statements (also introduced below).
A third kind of loop is the LOOP construct. This one does not test against a condition at either the start or the end of the loop. It continues looping until explicitly exited with a LEAVE statement, making it easy to get stuck in an infinite loop (as I did when creating this example). The LEAVE statement exits a block (which can include the procedure itself). Since there can be many nested constructs, it must also be accompanied by a label name to determine which block to leave.

label LOOP

 statement/s

 LEAVE label

 statement/s

END LOOP

Here is an example:

mysql> CREATE PROCEDURE sp_loop(IN var1 INT)

BEGIN

 loop1: LOOP

  IF (var1 > 5) THEN

   LEAVE loop1;

  END IF;

  SET var1=var1+1;

  SELECT var1;

 END LOOP;

END |

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_loop(2)\G

*************************** 1. row ***************************

var1: 3

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 4

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 5

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 6

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The LABEL name we used above is loop1, and the LEAVE statement explicitly left loop1. We did not make use of an end label.

ITERATE statements

Iterates can only appear within LOOP, REPEAT, and WHILE statements. They are followed by a LABEL name, and effectively direct control back to that LABEL. So, if they appear half-way through a loop, and they direct control back to the top of that loop, the rest of the loop will not be carried out (bear this logic in mind when testing, as it again opens the possibilities for infinite loops).
In this next example, we use a WHILE loop, and ITERATE back to the beginning of the loop if var1 is still less than 3.

mysql> CREATE PROCEDURE sp_while_iterate(IN var1 INT)

    -> BEGIN

    ->  while_label: WHILE (var1 < 5) DO

    ->   SELECT CONCAT('starting the loop: var1 is: ',var1);

    ->   SET var1=var1+1;

    ->   IF (var1<3) THEN

    ->    ITERATE while_label;

    ->   END IF;

    ->   SELECT CONCAT('ending the loop: var1 is: ',var1);

    ->  END WHILE;

    -> END|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_while_iterate(1)\G

*************************** 1. row ***************************

CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 1

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 2

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 3

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 3

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 4

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 4

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 5

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The first time through the loop, only starting the loop is displayed, as var1 is at 2 when the time comes to evaluate the IF condition, and the condition evaluates to true. The ITERATE sends control back to the start of the loop again. The second time through, var1 starts at 2, but is then incremented to 3, and the IF condition is false. The full loop is executed.

MySQL Stored Procedures: Part 3

Handlers and error handling

With stored procedures allowing the DBMS to grapple with concepts that beforehand were only dealt with in the murkier programming world, there is a clear need for a more elegant way of handling errors and exceptions. Enter the handler. There are two types of handler supported by MySQL - EXIT handlers that immediately exit the current BEGIN/END block, and CONTINUE handlers that allow processing to continue after the handler actions have been performed (the UNDO handler that may be familiar to users of other DBMS' is not yet supported). Below is an example. Remember that we are still using the | character as a delimiter, as outlined in part 1 of the series.

mysql>

CREATE procedure sp3()

 BEGIN

  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';

  DECLARE EXIT HANDLER FOR 'unknown column'

   SELECT 'error error whoop whoop';

  SELECT aha;

  SELECT 'continuing';

 END;|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp3()\G

*************************** 1. row ***************************

error error whoop whoop: error error whoop whoop

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

So, what happened here? We declared a condition, called 'unknown column'. It is a condition that occurs when SQLSTATE 42S22 is reached, which is when there is an unknown column. You can find a full list of error codes and messages on the MySQL site. Next, we declare an exit handler for the 'unknown column' condition, declared above. The handler simply displays the message error error whoop whoop. The actual body of the procedure consists of two statements, SELECT aha, which is designed to trigger SQLSTATE 42S22, and SELECT 'continuing', which is never actually executed as, being an exit handler, the procedure is immediately exited when the condition is met. So, when we call sp3(), the SELECT statement triggers the condition, and the message is displayed. Let's change this to use a CONTINUE handler, and see the difference.

mysql>

CREATE procedure sp4()

 BEGIN

  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';

  DECLARE CONTINUE HANDLER FOR 'unknown column'

   SELECT 'error error whoop whoop';

  SELECT aha;

  SELECT 'continuing';

 END;

mysql> CALL sp4()\G

*************************** 1. row ***************************

error error whoop whoop: error error whoop whoop

1 row in set (0.00 sec)

*************************** 1. row ***************************

continuing: continuing

1 row in set (0.06 sec)

As expected, the procedure continues executing after the error, and this time the SELECT 'continuing' statement is run.
Here is another procedure. What do you think it will do? If we want to display the error error and still handling messages as part of the handler, after reaching the aha statement, and then continue with the continuing statement, will this achieve that?

CREATE procedure sp5()

 BEGIN

  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';

  DECLARE CONTINUE HANDLER FOR 'unknown column'

   SELECT 'error error whoop whoop';

   SELECT 'still handling the error';

  SELECT aha;

  SELECT 'continuing';

 END;

mysql> CALL sp5()\G

*************************** 1. row ***************************

still handling the error: still handling the error

1 row in set (0.00 sec)

*************************** 1. row ***************************

error error whoop whoop: error error whoop whoop

1 row in set (0.00 sec)

*************************** 1. row ***************************

continuing: continuing

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The answer is clearly no. I hope that you were eagle-eyed enough to spot the misleading indentation. The SELECT 'still handling the error'; is actually part of the main procedure body, and not part of the error handler. Since we have no BEGIN or END statements as part of the handler, it consists of the one statement only. Here is what will achieve what we actually intended.

mysql>

CREATE procedure sp6()

 BEGIN

  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';

  DECLARE CONTINUE HANDLER FOR 'unknown column'

   BEGIN

    SELECT 'error error whoop whoop';

    SELECT 'still handling the error';

   END;

  SELECT aha;

  SELECT 'continuing';

 END;|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp6()\G

*************************** 1. row ***************************

error error whoop whoop: error error whoop whoop

1 row in set (0.00 sec)

*************************** 1. row ***************************

still handling the error: still handling the error

1 row in set (0.00 sec)

*************************** 1. row ***************************

continuing: continuing

1 row in set (0.00 sec)

Just to round off our examples, here is an example of a procedure where the error handler is not called, and none of the handler statements are executed.

mysql>

CREATE procedure sp7()

 BEGIN

  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';

  DECLARE CONTINUE HANDLER FOR 'unknown column'

   BEGIN

    SELECT 'error error whoop whoop';

    SELECT 'still handling the error';

   END;

  SELECT 'continuing';

 END;|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp7()\G

*************************** 1. row ***************************

continuing: continuing

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Variations and uses

For the examples above, we declared a condition to detect an SQLSTATE error. There are other ways. Firstly, handlers can be declared for specific error codes directly - you don't always need to go via an intermediate condition, although doing so is more useful in that the condition name can (and should) be descriptive, so there is no need to refer to a list of error codes at a later stage. The error code can also either be the SQLSTATE error number, as above, or the MySQL error code, as well as one of the more generic SQLWARNING, for all errors with an SQLSTATE beginning with 01, NOT FOUND for all errors with an SQLSTATE beginning with 02, or SQLEXCEPTION for all others. Below is a procedure that acts in a similar manner to our earlier examples. This time we use the MySQL Error code 1054, which is almost equivalent to SQLSTATE 42S22, and we also skip the condition:

mysql> 

CREATE procedure sp8()

 BEGIN

  DECLARE EXIT HANDLER FOR 1054

   BEGIN

    SELECT 'error error whoop whoop';

    SELECT 'still handling the error';

   END;

  SELECT aha;

 END;|

mysql> CALL sp8()\G

*************************** 1. row ***************************

error error whoop whoop: error error whoop whoop

1 row in set (0.00 sec)

*************************** 1. row ***************************

still handling the error: still handling the error

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

That's enough examples for now - hopefully you are starting to consider some practical uses for this. The handlers could ROLLBACK statements, or log to an error table. Moreover, the statements could be as complex as required, incorporating all the loops and conditions we looked at in the previous article.

Cursors

Cursors are another of the frequently demanded features now supported by MySQL 5. For those familiar with other DBMS implementations, MySQL 5 does not yet support them completely. In fact, it has some quite severe restrictions. MySQL 5's cursors are asensitive (so you should not update the table while using a cursor, otherwise you will get unpredictable results), read-only (you cannot update using the cursor position) and non-scrolling (you can only move forward to the next record, not back and forth).

In general, cursors are used to access a resultset that can be retrieved one or more rows at a time. They are also used for positioning a pointer at a specific row, and can allow updates to rows based upon this current position; although as mentioned, MySQL does not support this. The term cursor is short for CUrrent Set Of Records. In our examples, we will use the same table, sp1, we used in part 1 of this series. When we left off, the table contained the following records:

mysql> SELECT * FROM sp1\G

*************************** 1. row ***************************

 id: 3

txt: 414243

*************************** 2. row ***************************

 id: 40

txt: 444546

2 rows in set (0.00 sec)

Here is a sample procedure containing a cursor.

mysql>

CREATE PROCEDURE sp9 (OUT rx INT, OUT ry INT)

 BEGIN

  DECLARE x,y INT;

  DECLARE sp1_cursor CURSOR FOR SELECT id,txt FROM sp1;

  OPEN sp1_cursor;

   FETCH sp1_cursor INTO x,y;

  CLOSE sp1_cursor;

  SET rx = x;

  SET ry = y;

 END |

mysql> CALL sp9(@x,@y)\G

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x,@y\G

*************************** 1. row ***************************

@x: 3

@y: 414243

1 row in set (0.01 sec)

Here we declared variables (x and y and a cursor (sp1_cursor). The order of declarations is important - they must be in a particular order - variables and conditions, then cursors, then handlers.
Next is a new statement - OPEN sp1_cursor, which activates the cursor we declared earlier. The FETCH statement is the one that does all the magic, returning the next row of the actual resultset. Results must be placed somewhere, and the two variables x and y are the recipients of the two columns returned by the SELECT id,txt FROM sp1 query that makes up the cursor. The cursor is then closed (although MySQL will free the resources when it reaches the end of the compound statement block for you if you do not explicitly do so), and the results assigned to the two OUT variables. By calling the procedure, and then querying the two session variables that receive the results, we can see that they have been populated with the first row from the sp1 table, as expected.
However, returning just one row is not particularly useful. We need to loop through the entire resultset and return the results. We can achieve this using a simple REPEAT UNTIL loop, which we were introduced to in part 2.

mysql>

CREATE PROCEDURE sp10 (OUT rx INT, OUT ry INT)

 BEGIN

  DECLARE x,y,z INT;

  DECLARE sp1_cursor CURSOR FOR SELECT id,txt FROM sp1;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET z = 1;

  OPEN sp1_cursor;

  REPEAT

   FETCH sp1_cursor INTO x,y;

   UNTIL (z=1)

  END REPEAT;

  CLOSE sp1_cursor;

  SET rx = x;

  SET ry = y;

 END |

mysql> CALL sp10(@x,@y)|

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x,@y\G

*************************** 1. row ***************************

@x: 40

@y: 444546

1 row in set (0.00 sec)

The handler is declared for the NOT FOUND condition. The handler sets the variable z to 1, and z=1 is the condition the REPEAT UNTIL loop tests for. As mentioned earlier, the NOT FOUND condition includes all errors with an SQLSTATE beginning with 02, one of which is the No data to FETCH error. This will apply when we have retrieved all the rows in the table. As expected, upon calling the procedure and querying the session variables, this time we see the last row of the sp1table, as in the procedure each row's data is assigned to rx and ry, overwriting the previous contents.

 

MySQL Stored Functions

What's a Stored Function

If procedural programming is new to you, you may be wondering what the difference is between a Stored Procedure and a Stored Function. Not too much really. A function always returns a result, and can be called inside an SQL statement just like ordinary SQL functions. A function parameter is the equivalent of the IN procedure parameter, as functions use the RETURN keyword to determine what is passed back. Stored functions also have slightly more limitations in what SQL statements they can run than stored procedures.

A Stored Function example

Here is an example of a stored function:

mysql> DELIMITER |

mysql>

 CREATE FUNCTION WEIGHTED_AVERAGE (n1 INT, n2 INT, n3 INT, n4 INT)

  RETURNS INT

   DETERMINISTIC

    BEGIN

     DECLARE avg INT;

     SET avg = (n1+n2+n3*2+n4*4)/8;

     RETURN avg;

    END|

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT WEIGHTED_AVERAGE(70,65,65,60)\G

*************************** 1. row ***************************

WEIGHTED_AVERAGE(70,65,65,60): 63

1 row in set (0.00 sec)

As mentioned in the first stored procedures tutorial, we declare the "|" symbol as a delimiter, so that our function body can use ordinary ";" characters. This function returns a weighted average, as could be used to determine an overall result for a subject. The third test score is weighted twice as heavily as the first and second scores, while the fourth score counts four times as much. We also make use of the DECLARE (declaring a variable) and DETERMINISTIC (telling MySQL that, given the same input, the function will always return the same result) statements, as discussed in earlier tutorials.

Accessing tables in stored functions

Stored functions in early versions of MySQL 5.0 (< 5.0.10) could not reference tables except in a very limited capacity. That limited their usefulness to a large degree. Newer versions can now do so, but still cannot make use of statements that return a result set. So, no SELECT queries returning result sets from a table. However, you can get around this by using SELECT INTO. For the next example, we create a table allowing us to store 4 marks, and a name. Then we will define a new WEIGHTED_AVERAGE function to make use of the dynamic data from the table.

mysql> CREATE TABLE sfdata(mark1 INT,mark2 INT,mark3 INT,mark4 INT,name VARCHAR(50))

mysql> INSERT INTO sfdata VALUES(70,65,65,60,'Mark')|

mysql> INSERT INTO sfdata VALUES(95,94,75,50,'Pavlov')|

mysql>

 CREATE FUNCTION WEIGHTED_AVERAGE2 (v1 VARCHAR(50))

  RETURNS INT

  DETERMINISTIC

   BEGIN

    DECLARE i1,i2,i3,i4,avg INT;

    SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 FROM sfdata WHERE name=v1;

    SET avg = (i1+i2+i3*2+i4*4)/8; 

    RETURN avg;

   END|

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT WEIGHTED_AVERAGE2('Pavlov') AS Pavlov, WEIGHTED_AVERAGE2('Mark') AS Mark\G

*************************** 1. row ***************************

Pavlov: 67

  Mark: 63

1 row in set (0.00 sec)

By SELECTING the contents of the mark1 to mark4 rows INTO the variables we have just declared, there is no need to return a result set, and we can happily use the results inside of the function.
All the usual behaviors and conditions apply inside the function. Here is what happens if one of the records is missing a field.

mysql> INSERT INTO sfdata VALUES(90,NULL,70,60,'Isabelle')|

Query OK, 1 row affected (0.18 sec)

mysql> SELECT WEIGHTED_AVERAGE2('Isabelle') AS Isabelle\G

*************************** 1. row ***************************

Isabelle: NULL

1 row in set (0.16 sec)

As expected, the NULL (and NULLs are always a bad idea to use) contaminates the entire result, and MySQL, not knowing what else to do, can do nothing other than return a NULL.
Here is the full syntax for stored functions:

CREATE FUNCTION sf_name ([parameter1 [...]])

    RETURNS type

    [

     LANGUAGE SQL

     | [NOT] DETERMINISTIC

     | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

     | SQL SECURITY { DEFINER | INVOKER }

     | COMMENT 'string'

    ] 

    SQL statements

Manipulating tables

With the early restrictions on accessing tables inside a function lifted, you can use a function to make changes to a table as well. The next two examples are not ideal use of functions (in their current format they would more ideally be stored procedures), as we are not interested in the result being returned, and only want to manipulate the data, but they show you some of the potential power of functions. A function is best used when you want to return a result. Building upon these examples, you can create your own where complex INSERTs, SELECTs and UPDATEs are performed, with a single result being returned at the end of it all. First, we INSERT a record into the sfdata table.

mysql>

 CREATE FUNCTION WEIGHTED_AVERAGE3 (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50))

  RETURNS INT

  DETERMINISTIC

   BEGIN

    DECLARE i1,i2,i3,i4,avg INT;

    INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1);

    RETURN 1;

   END|

Query OK, 0 rows affected (0.08 sec)

mysql> SELECT WEIGHTED_AVERAGE3(50,60,60,50,'Thoko')\G

*************************** 1. row ***************************

WEIGHTED_AVERAGE3(50,60,60,50,'Thoko'): 1

1 row in set (0.00 sec)

mysql> SELECT * FROM sfdata\G

*************************** 1. row ***************************

mark1: 70

mark2: 65

mark3: 65

mark4: 60

 name: Mark

*************************** 2. row ***************************

mark1: 95

mark2: 94

mark3: 75

mark4: 50

 name: Pavlov

*************************** 3. row ***************************

mark1: 90

mark2: NULL

mark3: 70

mark4: 60

 name: Isabelle

*************************** 4. row ***************************

mark1: 50

mark2: 60

mark3: 60

mark4: 50

 name: Thoko

4 rows in set (0.01 sec)

Similarly, the next example UPDATEs a record based upon the parameters passed to it:

mysql>  

 CREATE FUNCTION WEIGHTED_AVERAGE_UPDATE (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50))

  RETURNS INT

  DETERMINISTIC

   BEGIN

    DECLARE i1,i2,i3,i4,avg INT;

    UPDATE sfdata SET mark1=n1,mark2=n2,mark3=n3,mark4=n4 WHERE name=v1;

    RETURN 1;

   END|

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT WEIGHTED_AVERAGE_UPDATE(60,60,60,50,'Thoko')\G

*************************** 1. row ***************************

WEIGHTED_AVERAGE_UPDATE(60,60,60,50,'Thoko'): 1

1 row in set (0.00 sec)

mysql> SELECT * FROM sfdata\G

*************************** 1. row ***************************

mark1: 70

mark2: 65

mark3: 65

mark4: 60

 name: Mark

*************************** 2. row ***************************

mark1: 95

mark2: 94

mark3: 75

mark4: 50

 name: Pavlov

*************************** 3. row ***************************

mark1: 90

mark2: NULL

mark3: 70

mark4: 60

 name: Isabelle

*************************** 4. row ***************************

mark1: 60

mark2: 60

mark3: 60

mark4: 50

 name: Thoko

5 rows in set (0.01 sec)

Information about existing stored functions

As with stored procedures, there are various ways to get to the metadata about existing stored functions. There is SHOW CREATE FUNCTION, and SHOW FUNCTION STATUS. The former returns the CREATE statement applied to the supplied function, while the latter returns metadata about all existing functions, as follows.

mysql> SHOW CREATE FUNCTION WEIGHTED_AVERAGE\G

*************************** 1. row ***************************

       Function: WEIGHTED_AVERAGE

       sql_mode:

Create Function: CREATE FUNCTION 'WEIGHTED_AVERAGE'(n1 INT, n2 INT, n3 INT, n4 INT) 

RETURNS int(11)

    DETERMINISTIC

BEGIN

   DECLARE avg INT;

   SET avg = (n1+n2+n3*2+n4*4)/8;

   RETURN avg;

  END

1 row in set (0.01 sec)

mysql> SHOW FUNCTION STATUS\G

*************************** 1. row ***************************

           Db: test

         Name: WEIGHTED_AVERAGE

         Type: FUNCTION

      Definer: root@localhost

     Modified: 2005-12-07 13:21:37

      Created: 2005-12-07 13:21:37

Security_type: DEFINER

      Comment:

*************************** 2. row ***************************

           Db: test

         Name: WEIGHTED_AVERAGE2

         Type: FUNCTION

      Definer: root@localhost

     Modified: 2005-12-07 13:41:07

      Created: 2005-12-07 13:41:07

Security_type: DEFINER

      Comment:

*************************** 3. row ***************************

           Db: test

         Name: WEIGHTED_AVERAGE3

         Type: FUNCTION

      Definer: root@localhost

     Modified: 2005-12-07 15:51:16

      Created: 2005-12-07 15:51:16

Security_type: DEFINER

      Comment:

*************************** 4. row ***************************

           Db: test

         Name: WEIGHTED_AVERAGE_UPDATE

         Type: FUNCTION

      Definer: root@localhost

     Modified: 2005-12-07 16:03:26

      Created: 2005-12-07 16:03:26

Security_type: DEFINER

      Comment:

4 rows in set (0.00 sec)

Another way to get the same information is to query the mysql.proc table. As you may know, the mysql database stores all sorts of data about permissions, and you can UPDATE the user or db table to change the MySQL privileges. Since MySQL 5, the mysql.proc table also contains metadata about stored procedures and functions.

mysql> SELECT * FROM mysql.proc\G

*************************** 1. row ***************************

              db: test

            name: WEIGHTED_AVERAGE

            type: FUNCTION

   specific_name: WEIGHTED_AVERAGE

        language: SQL

 sql_data_access: CONTAINS_SQL

is_deterministic: YES

   security_type: DEFINER

      param_list: n1 INT, n2 INT, n3 INT, n4 INT

         returns: int(11)

            body: BEGIN

   DECLARE avg INT;

   SET avg = (n1+n2+n3*2+n4*4)/8;

   RETURN avg;

  END

         definer: root@localhost

         created: 2005-12-07 13:21:37

        modified: 2005-12-07 13:21:37

        sql_mode:

         comment:

*************************** 2. row ***************************

              db: test

            name: WEIGHTED_AVERAGE2

            type: FUNCTION

   specific_name: WEIGHTED_AVERAGE2

        language: SQL

 sql_data_access: CONTAINS_SQL

is_deterministic: YES

   security_type: DEFINER

      param_list: v1 VARCHAR(50)

         returns: int(11)

            body: BEGIN     

                    DECLARE i1,i2,i3,i4,avg INT;    

                    SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 FROM sf1_data WHERE name=v1;    

                    SET avg = (i1+i2+i3*2+i4*4)/8;    

                    RETURN avg;   

                  END

         definer: root@localhost

         created: 2005-12-07 13:41:07

        modified: 2005-12-07 13:41:07

        sql_mode:

         comment:

*************************** 3. row ***************************

              db: test

            name: WEIGHTED_AVERAGE3

            type: FUNCTION

   specific_name: WEIGHTED_AVERAGE3

        language: SQL

 sql_data_access: CONTAINS_SQL

is_deterministic: YES

   security_type: DEFINER

      param_list: n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50)

         returns: int(11)

            body: BEGIN

   DECLARE i1,i2,i3,i4,avg INT;

   INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1);

   RETURN 1;

  END

         definer: root@localhost

         created: 2005-12-07 15:51:16

        modified: 2005-12-07 15:51:16

        sql_mode:

         comment:

*************************** 4. row ***************************

              db: test

            name: WEIGHTED_AVERAGE_UPDATE

            type: FUNCTION

   specific_name: WEIGHTED_AVERAGE_UPDATE

        language: SQL

 sql_data_access: CONTAINS_SQL

is_deterministic: YES

   security_type: DEFINER

      param_list: n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50)

         returns: int(11)

            body: BEGIN

   DECLARE i1,i2,i3,i4,avg INT;

   UPDATE sfdata SET mark1=n1,mark2=n2,mark3=n3,mark4=n4 WHERE name=v1;

   RETURN 1;

  END

         definer: root@localhost

         created: 2005-12-07 16:03:26

        modified: 2005-12-07 16:03:26

        sql_mode:

         comment:

4 rows in set (0.00 sec)

Note that querying the mysql.proc table returns more complete data than either of the first two methods, effectively returning the sum of both of those methods.
However, people coming from other DBMS', familiar with the ANSI standard, may be uncomfortable with these MySQL-specific methods. The standard way is to query the INFORMATION_SCHEMA. It is a highly flexible way of getting what you want, but can be a bit of an overkill, hence MySQL's provision of the more simple SHOW methods. I will leave a more complete explanation of INFORMATION_SCHEMA for another day, as it extends well beyond stored procedures and functions. For now, suffice to say that you can query INFORMATION_SCHEMA.ROUTINES to get similar metadata as the above, as follows:

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G

*************************** 1. row ***************************

     SPECIFIC_NAME: WEIGHTED_AVERAGE

   ROUTINE_CATALOG: NULL

    ROUTINE_SCHEMA: test

      ROUTINE_NAME: WEIGHTED_AVERAGE

      ROUTINE_TYPE: FUNCTION

    DTD_IDENTIFIER: int(11)

      ROUTINE_BODY: SQL

ROUTINE_DEFINITION: BEGIN

   DECLARE avg INT;

   SET avg = (n1+n2+n3*2+n4*4)/8;

   RETURN avg;

  END

     EXTERNAL_NAME: NULL

 EXTERNAL_LANGUAGE: NULL

   PARAMETER_STYLE: SQL

  IS_DETERMINISTIC: YES

   SQL_DATA_ACCESS: CONTAINS SQL

          SQL_PATH: NULL

     SECURITY_TYPE: DEFINER

           CREATED: 2005-12-07 13:21:37

      LAST_ALTERED: 2005-12-07 13:21:37

          SQL_MODE:

   ROUTINE_COMMENT:

           DEFINER: root@localhost

*************************** 2. row ***************************

     SPECIFIC_NAME: WEIGHTED_AVERAGE2

   ROUTINE_CATALOG: NULL

    ROUTINE_SCHEMA: test

      ROUTINE_NAME: WEIGHTED_AVERAGE2

      ROUTINE_TYPE: FUNCTION

    DTD_IDENTIFIER: int(11)

      ROUTINE_BODY: SQL

ROUTINE_DEFINITION: BEGIN

                      DECLARE i1,i2,i3,i4,avg INT;    

                      SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 FROM sf1_data WHERE name=v1;    

                      SET avg = (i1+i2+i3*2+i4*4)/8;    

                      RETURN avg;   

                    END

     EXTERNAL_NAME: NULL

 EXTERNAL_LANGUAGE: NULL

   PARAMETER_STYLE: SQL

  IS_DETERMINISTIC: YES

   SQL_DATA_ACCESS: CONTAINS SQL

          SQL_PATH: NULL

     SECURITY_TYPE: DEFINER

           CREATED: 2005-12-07 13:41:07

      LAST_ALTERED: 2005-12-07 13:41:07

          SQL_MODE:

   ROUTINE_COMMENT:

           DEFINER: root@localhost

*************************** 3. row ***************************

     SPECIFIC_NAME: WEIGHTED_AVERAGE3

   ROUTINE_CATALOG: NULL

    ROUTINE_SCHEMA: test

      ROUTINE_NAME: WEIGHTED_AVERAGE3

      ROUTINE_TYPE: FUNCTION

    DTD_IDENTIFIER: int(11)

      ROUTINE_BODY: SQL

ROUTINE_DEFINITION: BEGIN

   DECLARE i1,i2,i3,i4,avg INT;

   INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1);

   RETURN 1;

  END

     EXTERNAL_NAME: NULL

 EXTERNAL_LANGUAGE: NULL

   PARAMETER_STYLE: SQL

  IS_DETERMINISTIC: YES

   SQL_DATA_ACCESS: CONTAINS SQL

          SQL_PATH: NULL

     SECURITY_TYPE: DEFINER

           CREATED: 2005-12-07 15:51:16

      LAST_ALTERED: 2005-12-07 15:51:16

          SQL_MODE:

   ROUTINE_COMMENT:

           DEFINER: root@localhost

*************************** 4. row ***************************

     SPECIFIC_NAME: WEIGHTED_AVERAGE_UPDATE

   ROUTINE_CATALOG: NULL

    ROUTINE_SCHEMA: test

      ROUTINE_NAME: WEIGHTED_AVERAGE_UPDATE

      ROUTINE_TYPE: FUNCTION

    DTD_IDENTIFIER: int(11)

      ROUTINE_BODY: SQL

ROUTINE_DEFINITION: BEGIN

   DECLARE i1,i2,i3,i4,avg INT;

   UPDATE sfdata SET mark1=n1,mark2=n2,mark3=n3,mark4=n4 WHERE name=v1;

   RETURN 1;

  END

     EXTERNAL_NAME: NULL

 EXTERNAL_LANGUAGE: NULL

   PARAMETER_STYLE: SQL

  IS_DETERMINISTIC: YES

   SQL_DATA_ACCESS: CONTAINS SQL

          SQL_PATH: NULL

     SECURITY_TYPE: DEFINER

           CREATED: 2005-12-07 16:03:26

      LAST_ALTERED: 2005-12-07 16:03:26

          SQL_MODE:

   ROUTINE_COMMENT:

           DEFINER: root@localhost

4 rows in set (0.00 sec)

The MySQL documentation gives a complete overview about the INFORMATION_SCHEMA structure, and some MySQL oddities, if you want to pursue that further.

Conclusion

Stored procedures and stored functions open a whole new world to MySQL developers, and mean that MySQL is starting to attract attention from developers of entirely new types of applications. While the implementation in MySQL 5.0 is still raw (MySQL 5.1 is out in alpha now, and develops things further, as will MySQL 6.0), most of what is needed is already there. Not all of the my applications are running on MySQL 5 yet, but the itch to move everything is starting to get stronger every time I do further development, and have to make do with more unwanted logic in the application. I came across a query recently from one poor developer bemoaning the lack of features in MySQL 3.23. Upgrading legacy systems is not fun, but for those of you lucky enough to start with a clean slate, enjoy the new world of MySQL 5, stored procedures and stored functions!

Introduction to SOAP

SOAP is a simple XML based protocol to let applications exchange information over HTTP.
Or more simply: SOAP is a protocol for accessing a Web Service.

What You Should Already Know

Before you study SOAP you should have a basic understanding of XML and XML Namespaces.

If you want to study these subjects first, please read our XML Tutorial.

What is SOAP?

• SOAP stands for Simple Object Access Protocol
• SOAP is a communication protocol
• SOAP is for communication between applications
• SOAP is a format for sending messages
• SOAP is designed to communicate via Internet
• SOAP is platform independent
• SOAP is language independent
• SOAP is based on XML
• SOAP is simple and extensible
• SOAP allows you to get around firewalls
• SOAP will be developed as a W3C standard

Why SOAP?

It is important for application development to allow Internet communication between programs.
Today's applications communicate using Remote Procedure Calls (RPC) between objects like DCOM and CORBA, but HTTP was not designed for this. RPC represents a compatibility and security problem; firewalls and proxy servers will normally block this kind of traffic.
A better way to communicate between applications is over HTTP, because HTTP is supported by all Internet browsers and servers. SOAP was created to accomplish this.
SOAP provides a way to communicate between applications running on different operating systems, with different technologies and programming languages.

Microsoft and SOAP

SOAP is a key element of Microsoft's .NET architecture for future Internet application development.

SOAP 1.1 was Proposed to W3C

UserLand, Ariba, Commerce One, Compaq, Developmentor, HP, IBM, IONA, Lotus, Microsoft, and SAP proposed to W3C, in May 2000, the SOAP Internet protocol that they hope will revolutionize application development by connecting graphic user interface desktop applications to powerful Internet servers using the standards of the Internet: HTTP and XML.

W3C is Working on SOAP 1.2

The first public Working Draft on SOAP was published from W3C in December 2001. To read more about the SOAP activities at W3C please visit our W3C tutorial.

File downloads in PHP

This is a simple code snippet. It will allow you to force the web-browser that is currently viewing your script to come up with a file-download box (and the a file on your system can be downloaded).

Here is the code:

//so only the data from the headers is sent
ob_start();
//dispable caching
header (”Cache-Control: must-revalidate, pre-check=0, post-check=0″);
header (”Content-Type: application/binary”);
header (”Content-Length: ” . filesize($export_long_name));
header (”Content-Disposition: attachment; filename=yourfile.ext”);
readfile($export_long_name);

A list of of mime types can be found Here

Apache mod_rewrite tips and tricks

What is mod_rewrite?

Mod_rewrite is a rewriting engine (based on regular-expressions) built into the apache webserver and it is used to rewrite urls dynamically. The URL manipulations can depend on various tests, of server variables, environment variables, HTTP headers, or time stamps. Even external database lookups in various formats can be used to achieve highly granular URL matching.

How to install it

Apache by default comes with the mod_rewrite module installed but it is not enabled. So if you have Apache installed on your own server, you will need to enable it.

If you need to install apache on your system, there are many free, easy install packages available:

Xamp - http://www.apachefriends.org/en/xampp.html
apache2triad - http://apache2triad.net/
apachePHPMysql - http://apachephpmysql.narhoz.ru/
EasyWebServer - http://e.w.s.free.fr/index_fr.php
FoxServ - http://sourceforge.net/projects/foxserv/

Setting it up

Once installed, mod_rewrite basically relies on one file for all it’s functionality. It’s called .htaccess. This file should be placed in the root directory of your website.

A simple Redirect

Place the following in a .htaccess file:

RewriteEngine on
RewriteRule ^test\.html$ test2.html

RewriteEngine on should always be placed at the beginning of all your .htaccess files.

Note: If you are using a hosting provider, you may have to place the following line in your file (under rewrite_engine on): RewriteBase /
Script details:

• ^ is used before a URL. If a relative URL is used, it starts in the same directory as the .htaccess file
• $ is used for the end of a string that will be matched.
• \ is used to escape the period, periods need the \ before them if they are not going to be part of the actual rule (in this case, it is part of the filename).

This script will redirect all access from test.html to test2.html. IE: if a user goes to http://www.yoursite.com/test.html, they will be automatically forwarded to http://www.yoursite.com/test2.html

Other interesting uses

A) Blocking a specific Ip addressing from accessing your website.

RewriteCond %{REMOTE_ADDR} ^(W\.X\.Y\.Z)$
RewriteRule ^/* http://www.yoursite.com/sorry.htm [L]

Replace w.x.y.z with the IP you would like to block and http://www.yoursite.com/sorry.htm with the redirected URL.

B) Block/redirect a site that is linking to you

RewriteCond %{HTTP_REFERER} ^http://www\.blockedsite\.com [NC]
RewriteRule ^/* http://www.yoursite.com/sorry.htm [L]

Replace http://www.blockedsite.com/ with site you do not want linking to you, and http://www.yoursite.com/sorry.htm with the redirected URL.

C) preventing people from linking to your images

RewriteCond %{HTTP_REFERER} !^http://(www\.)?yoursite\.com [NC]
RewriteCond %{HTTP_REFERER} !^$
RewriteCond %{HTTP_REFERER} ^http://.*$
RewriteRule \.(png gif bmp jpe?g)$ /images/stopstealing.png [L]

Replace http://www.blockedsite.com/ your site, and /images/stopstealing.png with an image path of choice.

Full Apache Docs: http://httpd.apache.org/docs/2.0/mod/mod_rewrite.html

Top 5 free content management systems

1) CMS made simple

Features

• Easy User and group management
• Group-based permission system
• Admin panel with multiple language support
• RSS module
• Module API for easy extendability

Front-end Demo here
Admin Demo here
username: admin
password: demo

2) DragonFly CMS

Features

• Built-in photo gallery
• BBcode support
• MMCache and eAccelerator support
• Site-wide cache-based template system
• Debugging system and page query list

Front page demo here
Admin Demo here
Username: admin
Password: Demo123 (Case Sensitive)

3) Bitweaver

Features

• SEO friendly urls
• Content spam protection (automatic no-follows)
• Many formats supported (TikiWiki, HTML, BBCode, Wikipedia (aka MediaWik))
• Hotwords - allow you to specify particular words that can be associated with a URL
• Generate PDF documents from all Content

Front-page demo here
Admin demo here
Username: admin
Password: demo

4) Drupal

Features

• SEO Friendly URLs
• Many community modules
• Role based permission system
• External authentication source support with Jabber, Blogger, and LiveJournal
• Blogger API support

Front-end demo here
Admin demo here
Username: admin
Password: demo

5) Joomla

This is one of the most mature, free (did I mention open source), content management systems available. Also a fork of a CMS called Mambo. How are they different?

Joomla version 1.0 is derived from Mambo 4.5.2.3 but includes many additional bug fixes and security patches. Joomla version 1.5 is an extensive refactoring of the API as is Mambo version 4.6 to its codebase. Both applications continue maintain a similar user inferface (look and feel), similar default component and module sets. Both Joomla 1.5 and Mambo 4.6 will include internationisation support. Joomla will use an easy-to-use ‘ini’ format for their translation files while Mambo uses the ‘gettext’ format. Joomla 1.5 will correctly support the UTF-8 character set. Joomla 1.5 also includes many new features such as additional authentication models (LDAP, Gmail, etc), xml-rpc client-server support. It also natively supports database drivers for MySQL 4.1+ (on PHP 5) and has improved support for MySQL 5 as well as the groundings to support other database engines.

Demo here

Country wise Currency Codes

Country	Capital	Currency Name	Code
Afghanistan	Kabul	Afghanistan Afghani	AFA
Albania	Tiran	Albanian Lek	ALL
Algeria	Algiers	Algerian Dinar	DZD
American Samoa	Pago Pago	US Dollar	USD
Andorra	Andorra	Euro	EUR
Angola	Luanda	Angolan New Kwanza	AON
Anguilla	The Valley	East Caribbean Dollar	XCD
Antarctica	None	East Caribbean Dollar	XCD
Antigua and Barbuda	St. Johns	East Caribbean Dollar	XCD
Argentina	Buenos Aires	Argentine Peso	ARS
Armenia	Yerevan	Armenian Dram	AMD
Aruba	Oranjestad	Aruban Guilder	AWG
Australia	Canberra	Australian Dollar	AUD
Austria	Vienna	Euro	EUR
Azerbaijan	Baku	Azerbaijanian Manat	AZM
Bahamas	Nassau	Bahamian Dollar	BSD
Bahrain	Al-Manamah	Bahraini Dinar	BHD
Bangladesh	Dhaka	Bangladeshi Taka	BDT
Barbados	Bridgetown	Barbados Dollar	BBD
Belarus	Minsk	Belarussian Ruble	BYB
Belgium	Brussels	Euro	EUR
Belize	Belmopan	Belize Dollar	BZD
Benin	Porto-Novo	CFA Franc BCEAO	XOF
Bermuda	Hamilton	Bermudian Dollar	BMD
Bhutan	Thimphu	Bhutan Ngultrum	BTN
Bolivia	La Paz	Boliviano	BOB
Bosnia-Herzegovina	Sarajevo	Marka	BAM
Botswana	Gaborone	Botswana Pula	BWP
Bouvet Island	None	Norwegian Krone	NOK
Brazil	Brasilia	Brazilian Real	BRL
British Indian Ocean Territory	None	US Dollar	USD
Brunei Darussalam	Bandar Seri Begawan	Brunei Dollar	BND
Bulgaria	Sofia	Bulgarian Lev	BGL
Burkina Faso	Ouagadougou	CFA Franc BCEAO	XOF
Burundi	Bujumbura	Burundi Franc	BIF
Cambodia	Phnom Penh	Kampuchean Riel	KHR
Cameroon	Yaounde	CFA Franc BEAC	XAF
Canada	Ottawa	Canadian Dollar	CAD
Cape Verde	Praia	Cape Verde Escudo	CVE
Cayman Islands	Georgetown	Cayman Islands Dollar	KYD
Central African Republic	Bangui	CFA Franc BEAC	XAF
Chad	N'Djamena	CFA Franc BEAC	XAF
Chile	Santiago	Chilean Peso	CLP
China	Beijing	Yuan Renminbi	CNY
Christmas Island	The Settlement	Australian Dollar	AUD
Cocos (Keeling) Islands	West Island	Australian Dollar	AUD
Colombia	Bogota	Colombian Peso	COP
Comoros	Moroni	Comoros Franc	KMF
Congo	Brazzaville	CFA Franc BEAC	XAF
Congo, Dem. Republic	Kinshasa	Francs	CDF
Cook Islands	Avarua	New Zealand Dollar	NZD
Costa Rica	San Jose	Costa Rican Colon	CRC
Croatia	Zagreb	Croatian Kuna	HRK
Cuba	Havana	Cuban Peso	CUP
Cyprus	Nicosia	Cyprus Pound	CYP
Czech Rep.	Prague	Czech Koruna	CZK
Denmark	Copenhagen	Danish Krone	DKK
Djibouti	Djibouti	Djibouti Franc	DJF
Dominica	Roseau	East Caribbean Dollar	XCD
Dominican Republic	Santo Domingo	Dominican Peso	DOP
Ecuador	Quito	Ecuador Sucre	ECS
Egypt	Cairo	Egyptian Pound	EGP
El Salvador	San Salvador	El Salvador Colon	SVC
Equatorial Guinea	Malabo	CFA Franc BEAC	XAF
Eritrea	Asmara	Eritrean Nakfa	ERN
Estonia	Tallinn	Estonian Kroon	EEK
Ethiopia	Addis Ababa	Ethiopian Birr	ETB
European Union	Brussels	Euro	EUR
Falkland Islands	Stanley	Falkland Islands Pound	FKP
Faroe Islands	Torshavn	Danish Krone	DKK
Fiji	Suva	Fiji Dollar	FJD
Finland	Helsinki	Euro	EUR
France	Paris	Euro	EUR
French Guiana	Cayenne	Euro	EUR
French Southern Territories	None	Euro	EUR
Gabon	Libreville	CFA Franc BEAC	XAF
Gambia	Banjul	Gambian Dalasi	GMD
Georgia	Tbilisi	Georgian Lari	GEL
Germany	Berlin	Euro	EUR
Ghana	Accra	Ghanaian Cedi	GHC
Gibraltar	Gibraltar	Gibraltar Pound	GIP
Great Britain	London	Pound Sterling	GBP
Greece	Athens	Euro	EUR
Greenland	Godthab	Danish Krone	DKK
Grenada	St. George's	East Carribean Dollar	XCD
Guadeloupe (French)	Basse-Terre	Euro	EUR
Guam (USA)	Agana	US Dollar	USD
Guatemala	Guatemala City	Guatemalan Quetzal	QTQ
Guernsey	St. Peter Port	Pound Sterling	GBP
Guinea	Conakry	Guinea Franc	GNF
Guinea Bissau	Bissau	Guinea-Bissau Peso	GWP
Guyana	Georgetown	Guyana Dollar	GYD
Haiti	Port-au-Prince	Haitian Gourde	HTG
Heard Island and McDonald Islands	None	Australian Dollar	AUD
Honduras	Tegucigalpa	Honduran Lempira	HNL
Hong Kong	Victoria	Hong Kong Dollar	HKD
Hungary	Budapest	Hungarian Forint	HUF
Iceland	Reykjavik	Iceland Krona	ISK
India	New Delhi	Indian Rupee	INR
Indonesia	Jakarta	Indonesian Rupiah	IDR
Iran	Tehran	Iranian Rial	IRR
Iraq	Baghdad	Iraqi Dinar	IQD
Ireland	Dublin	Euro	EUR
Isle of Man	Douglas	Pound Sterling	GBP
Israel	Jerusalem	Israeli New Shekel	ILS
Italy	Rome	Euro	EUR
Ivory Coast	Abidjan	CFA Franc BCEAO	XOF
Jamaica	Kingston	Jamaican Dollar	JMD
Japan	Tokyo	Japanese Yen	JPY
Jersey	Saint Helier	Pound Sterling	GBP
Jordan	Amman	Jordanian Dinar	JOD
Kazakhstan	Astana	Kazakhstan Tenge	KZT
Kenya	Nairobi	Kenyan Shilling	KES
Kiribati	Tarawa	Australian Dollar	AUD
Korea-North	Pyongyang	North Korean Won	KPW
Korea-South	Seoul	Korean Won	KRW
Kuwait	Kuwait City	Kuwaiti Dinar	KWD
Kyrgyzstan	Bishkek	Som	KGS
Laos	Vientiane	Lao Kip	LAK
Latvia	Riga	Latvian Lats	LVL
Lebanon	Beirut	Lebanese Pound	LBP
Lesotho	Maseru	Lesotho Loti	LSL
Liberia	Monrovia	Liberian Dollar	LRD
Libya	Tripoli	Libyan Dinar	LYD
Liechtenstein	Vaduz	Swiss Franc	CHF
Lithuania	Vilnius	Lithuanian Litas	LTL
Luxembourg	Luxembourg	Euro	EUR
Macau	Macau	Macau Pataca	MOP
Macedonia	Skopje	Denar	MKD
Madagascar	Antananarivo	Malagasy Franc	MGF
Malawi	Lilongwe	Malawi Kwacha	MWK
Malaysia	Kuala Lumpur	Malaysian Ringgit	MYR
Maldives	Male	Maldive Rufiyaa	MVR
Mali	Bamako	CFA Franc BCEAO	XOF
Malta	Valletta	Maltese Lira	MTL
Marshall Islands	Majuro	US Dollar	USD
Martinique (French)	Fort-de-France	Euro	EUR
Mauritania	Nouakchott	Mauritanian Ouguiya	MRO
Mauritius	Port Louis	Mauritius Rupee	MUR
Mayotte	Dzaoudzi	Euro	EUR
Mexico	Mexico City	Mexican Nuevo Peso	MXN
Micronesia	Palikir	US Dollar	USD
Moldova	Kishinev	Moldovan Leu	MDL
Monaco	Monaco	Euro	EUR
Mongolia	Ulan Bator	Mongolian Tugrik	MNT
Montenegro	Podgorica	Euro	EUR
Montserrat	Plymouth	East Caribbean Dollar	XCD
Morocco	Rabat	Moroccan Dirham	MAD
Mozambique	Maputo	Mozambique Metical	MZM
Myanmar	Rangoon	Myanmar Kyat	MMK
Namibia	Windhoek	Namibian Dollar	NAD
Nauru	Yaren	Australian Dollar	AUD
Nepal	Kathmandu	Nepalese Rupee	NPR
Netherlands	Amsterdam	Euro	EUR
Netherlands Antilles	Willemstad	Netherlands Antillean Guilder	ANG
New Caledonia (French)	Noumea	CFP Franc	XPF
New Zealand	Wellington	New Zealand Dollar	NZD
Nicaragua	Managua	Nicaraguan Cordoba Oro	NIC
Niger	Niamey	CFA Franc BCEAO	XOF
Nigeria	Lagos	Nigerian Naira	NGN
Niue	Alofi	New Zealand Dollar	NZD
Norfolk Island	Kingston	Australian Dollar	AUD
Northern Mariana Islands	Saipan	US Dollar	USD
Norway	Oslo	Norwegian Krone	NOK
Oman	Muscat	Omani Rial	OMR
Pakistan	Islamabad	Pakistan Rupee	PKR
Palau	Koror	US Dollar	USD
Panama	Panama City	Panamanian Balboa	PAB
Papua New Guinea	Port Moresby	Papua New Guinea Kina	PGK
Paraguay	Asuncion	Paraguay Guarani	PYG
Peru	Lima	Peruvian Nuevo Sol	PEN
Philippines	Manila	Philippine Peso	PHP
Pitcairn Island	Adamstown	New Zealand Dollar	NZD
Poland	Warsaw	Polish Zloty	PLZ
Polynesia (French)	Papeete	CFP Franc	XPF
Portugal	Lisbon	Euro	EUR
Puerto Rico	San Juan	US Dollar	USD
Qatar	Doha	Qatari Rial	QAR
Reunion (French)	Saint-Denis	Euro	EUR
Romania	Bucharest	Romanian Leu	ROL
Russia	Moscow	Russian Ruble	RUR
Rwanda	Kigali	Rwanda Franc	RWF
Saint Helena	Jamestown	St. Helena Pound	SHP
Saint Kitts & Nevis Anguilla	Basseterre	East Caribbean Dollar	XCD
Saint Lucia	Castries	East Caribbean Dollar	XCD
Saint Pierre and Miquelon	St. Pierre	Euro	EUR
Saint Vincent & Grenadines	Kingstown	East Caribbean Dollar	XCD
Samoa	Apia	Samoan Tala	WST
San Marino	San Marino	Italian Lira	ITL
Sao Tome and Principe	Sao Tome	Dobra	STD
Saudi Arabia	Riyadh	Saudi Riyal	SAR
Senegal	Dakar	CFA Franc BCEAO	XOF
Serbia	Belgrade	Dinar	RSD
Seychelles	Victoria	Seychelles Rupee	SCR
Sierra Leone	Freetown	Sierra Leone Leone	SLL
Singapore	Singapore	Singapore Dollar	SGD
Slovakia	Bratislava	Slovak Koruna	SKK
Slovenia	Ljubljana	Slovenian Tolar	SIT
Solomon Islands	Honiara	Solomon Islands Dollar	SBD
Somalia	Mogadishu	Somali Shilling	SOD
South Africa	Pretoria	South African Rand	ZAR
South Georgia & South Sandwich Islands	None	Pound Sterling	GBP
Spain	Madrid	Euro	EUR
Sri Lanka	Colombo	Sri Lanka Rupee	LKR
Sudan	Khartoum	Sudanese Dinar	SDD
Suriname	Paramaribo	Surinam Guilder	SRG
Svalbard and Jan Mayen Islands	Longyearbyen	Norwegian Krone	NOK
Swaziland	Mbabane	Swaziland Lilangeni	SZL
Sweden	Stockholm	Swedish Krona	SEK
Switzerland	Bern	Swiss Franc	CHF
Syria	Damascus	Syrian Pound	SYP
Taiwan	Taipei	Taiwan Dollar	TWD
Tajikistan	Dushanbe	Tajik Ruble	TJR
Tanzania	Dodoma	Tanzanian Shilling	TZS
Thailand	Bangkok	Thai Baht	THB
Togo	Lome	CFA Franc BCEAO	XOF
Tokelau	None	New Zealand Dollar	NZD
Tonga	Nuku'alofa	Tongan Pa'anga	TOP
Trinidad and Tobago	Port of Spain	Trinidad and Tobago Dollar	TTD
Tunisia	Tunis	Tunisian Dollar	TND
Turkey	Ankara	Turkish Lira	TRL
Turkmenistan	Ashgabat	Manat	TMM
Turks and Caicos Islands	Grand Turk	US Dollar	USD
Tuvalu	Funafuti	Australian Dollar	AUD
U.K.	London	Pound Sterling	GBP
Uganda	Kampala	Uganda Shilling	UGS
Ukraine	Kiev	Ukraine Hryvnia	UAG
United Arab Emirates	Abu Dhabi	Arab Emirates Dirham	AED
Uruguay	Montevideo	Uruguayan Peso	UYP
USA	Washington	US Dollar	USD
USA Minor Outlying Islands	None	US Dollar	USD
Uzbekistan	Tashkent	Uzbekistan Sum	UZS
Vanuatu	Port Vila	Vanuatu Vatu	VUV
Vatican	Vatican City	Euro	EUR
Venezuela	Caracas	Venezuelan Bolivar	VUB
Vietnam	Hanoi	Vietnamese Dong	VND
Virgin Islands (British)	Road Town	US Dollar	USD
Virgin Islands (USA)	Charlotte Amalie	US Dollar	USD
Wallis and Futuna Islands	Mata-Utu	CFP Franc	XPF
Western Sahara	El Aaiun	Moroccan Dirham	MAD
Yemen	San'a	Yemeni Rial	YER
Zambia	Lusaka	Zambian Kwacha	ZMK
Zimbabwe	Harare	Zimbabwe Dollar	ZWD