sql-info.de

CREATE FUNCTION examples

Basic MySQL CREATE FUNCTION statement

A very basic CREATE FUNCTION example which will produced the famed 'Hello World' output:

DELIMITER $$
CREATE FUNCTION hello_world()
  RETURNS TEXT
LANGUAGE SQL BEGIN RETURN 'Hello World'; END; $$ DELIMITER ;

Execute this function as follows:

mysql> SELECT hello_world();
+---------------+
| hello_world() |
+---------------+
| Hello World   |
+---------------+
1 row in set (0.00 sec)

MySQL function with a parameter

Customize your 'Hello World' output with input from a parameter:

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(addressee TEXT)
  RETURNS TEXT
LANGUAGE SQL -- This element is optional and will be omitted from subsequent examples BEGIN RETURN CONCAT('Hello ', addressee); END; $$ DELIMITER ;

Execute as follows:

mysql> SELECT hello_world('Earth');
+----------------------+
| hello_world('Earth') |
+----------------------+
| Hello Earth          |
+----------------------+
1 row in set (0.00 sec)

MySQL function with a local variable

Use a local variable to perform calculations inside your function:

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(addressee TEXT)
  RETURNS TEXT
BEGIN
  DECLARE strlen INT;
  SET strlen = LENGTH(addressee);
  RETURN CONCAT('Hello ', addressee, ' - your parameter has ', strlen, ' characters');
END;
$$
DELIMITER ;

Output:

mysql> SELECT hello_world('Earth');
+-----------------------------------------------+
| hello_world('Earth')                          |
+-----------------------------------------------+
| Hello Earth - your parameter has 5 characters |
+-----------------------------------------------+
1 row in set (0.00 sec)

MySQL function with a loop

DROP FUNCTION IF EXISTS looptest;
DELIMITER $$
CREATE FUNCTION looptest()
  RETURNS INT READS SQL DATA
BEGIN
  DECLARE v_total INT;

  SET v_total = 0;

  count_loop: LOOP
    SET v_total = v_total + 1;

    IF v_total = 10 THEN
      LEAVE count_loop;
    END IF;
    
  END LOOP;

  RETURN v_total;
END;
$$
DELIMITER ;

MySQL function with a loop and cursor

It is possible - if somewhat inelegant - to iterate through a CURSOR using a loop:

DROP FUNCTION IF EXISTS looptest;
DELIMITER $$
CREATE FUNCTION looptest()
  RETURNS INT READS SQL DATA
BEGIN
  DECLARE v_total INT;
  DECLARE v_counter INT;
  DECLARE done INT DEFAULT FALSE;
  DECLARE csr CURSOR FOR 
    SELECT counter FROM items;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET v_total = 0;
  OPEN csr;
  read_loop: LOOP
    FETCH csr INTO v_counter;

    IF done THEN
      LEAVE read_loop;
    END IF;

    SET v_total = v_total + v_counter;
  END LOOP;
  CLOSE csr;

  RETURN v_total;
END;
$$

DELIMITER ;

Comments
How to give out parameter in mysql
Posted by: Sachin Tak | 2013-10-21 05:34