MySQL Tutorial/Procedure Function/Function

A function that takes a parameter, performs an operation using an SQL function, and returns the result

In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters:

mysql>
mysql>
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
    ->     RETURN CONCAT("Hello, ",s,"!");
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> SELECT hello("world");
+----------------+
| hello("world") |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop function hello;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

Changing and Removing Functions

MySQL provides an ALTER statement for stored functions.

The ALTER statement can change only the characteristics of a function, not the SQL statements that make up the body.

The ALTER statement has the following syntax:

You can change multiple characteristics within a single ALTER statement.

If a characteristic isn"t specified in the statement, the value is left as it was when the function was created.

The DETERMINISTIC characteristic isn"t allowed in the ALTER statement.

ALTER FUNCTION [database.]<name> <characteristics>;

ALTER FUNCTION test.myFunction
  COMMENT "This is the new comment.";

Create a procedure for "READS SQL DATA"

mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc(in_id int)
    ->        READS SQL DATA
    -> BEGIN
    ->     SELECT first_name,last_name
    ->       FROM employee
    ->      WHERE id=in_id;
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myProc(1);
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Jason      | Martin    |
+------------+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>

Creating Functions

CREATE    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS Any valid MySQL data type
    [characteristic ...] Valid SQL procedure statement

Function Permissions

There are two sets of permissions for stored functions,

1. permissions to create and manage stored functions
2. permissions to use functions.

To create a function in a database, the user must have the CREATE ROUTINE privilege.

To use a function, the caller must have the EXECUTE privilege for the particular database.

To change an existing function, the user must have the ALTER ROUTINE privilege.

Permissions can be granted globally in the mysql.user table.

Permissions can also be granted at the database level in the mysql.db table.

Permissions can be granted for a specific routine in the mysql.procs_priv table.

11. 3. Function 11. 3. 1. <A href="/Tutorial/MySQL/0201__Procedure-Function/CreatingFunctions.htm">Creating Functions</a> 11. 3. 2. <A href="/Tutorial/MySQL/0201__Procedure-Function/AfunctionthattakesaparameterperformsanoperationusinganSQLfunctionandreturnstheresult.htm">A function that takes a parameter, performs an operation using an SQL function, and returns the result</a> 11. 3. 3. <A href="/Tutorial/MySQL/0201__Procedure-Function/Singlestatementfunction.htm">Single statement function</a> 11. 3. 4. <A href="/Tutorial/MySQL/0201__Procedure-Function/Nestedfunctioncall.htm">Nested function call</a> 11. 3. 5. <A href="/Tutorial/MySQL/0201__Procedure-Function/Usingbuildinfunctioninuserdefinedfunction.htm">Using buildin function in user-defined function</a> 11. 3. 6. <A href="/Tutorial/MySQL/0201__Procedure-Function/ChangingandRemovingFunctions.htm">Changing and Removing Functions</a> 11. 3. 7. <A href="/Tutorial/MySQL/0201__Procedure-Function/ToremoveastoredfunctionusetheDROPcommand.htm">To remove a stored function, use the DROP command</a> 11. 3. 8. <A href="/Tutorial/MySQL/0201__Procedure-Function/CreateaprocedureforREADSSQLDATA.htm">Create a procedure for "READS SQL DATA"</a> 11. 3. 9. <A href="/Tutorial/MySQL/0201__Procedure-Function/ALTERPROCEDUREandALTERFUNCTIONSyntax.htm">ALTER PROCEDURE and ALTER FUNCTION Syntax</a> 11. 3. 10. <A href="/Tutorial/MySQL/0201__Procedure-Function/DROPPROCEDUREandDROPFUNCTIONSyntax.htm">DROP PROCEDURE and DROP FUNCTION Syntax</a> 11. 3. 11. Function Permissions

Single statement function

mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION calc_tax (cost DECIMAL(10,2)) RETURNS DECIMAL(10,2)
    -> RETURN cost * .05
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> select calc_tax(123.123);
+-------------------+
| calc_tax(123.123) |
+-------------------+
|              6.16 |
+-------------------+
1 row in set, 2 warnings (0.00 sec)
mysql>
mysql> drop function calc_tax;
Query OK, 0 rows affected (0.00 sec)

To remove a stored function, use the DROP command

The DROP statement removes the function from the mysql.proc table.

DROP FUNCTION [database.]<name>

Using buildin function in user-defined function

mysql>
mysql> DELIMITER //
mysql>
mysql> CREATE FUNCTION myFunction (item_sum DECIMAL(10,2))
    -> RETURNS DECIMAL(10,1)
    -> BEGIN
    ->
    ->
    -> RETURN ROUND(item_sum,1);
    ->
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> select myFunction(123.12);
+--------------------+
| myFunction(123.12) |
+--------------------+
|              123.1 |
+--------------------+
1 row in set (0.02 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)