DETERMINISTIC function : DETERMINISTIC « Procedure Function « MySQL Tutorial

1. MySQL Tutorial
2. Procedure Function
3. DETERMINISTIC

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE FUNCTION count_strings
    ->       (in_string VARCHAR(256),in_substr VARCHAR(128))
    ->   RETURNS INT
    ->   DETERMINISTIC
    -> BEGIN
    ->   DECLARE l_count INT DEFAULT 0;
    ->   DECLARE l_start INT DEFAULT 1;
    ->   DECLARE l_pos   INT;
    ->
    ->   MainLoop:
    ->   LOOP
    ->     SET l_pos=LOCATE(in_substr,in_string,l_start);
    ->     IF l_pos=0 THEN
    ->        LEAVE MainLoop;
    ->     ELSE
    ->       SET l_count=l_count+1;
    ->       SET l_start=l_pos+1;
    ->     END IF;
    ->
    ->   END LOOP;
    ->   RETURN(l_count);
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> select count_strings('asdf,asdfasdff,asdf,asdfasdf,','asd');
+------------------------------------------------------+
| count_strings('asdf,asdfasdff,asdf,asdfasdf,','asd') |
+------------------------------------------------------+
|                                                    6 |
+------------------------------------------------------+
1 row in set (0.02 sec)

mysql> drop function count_strings;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>