MySQL Tutorial/Procedure Function/Function Data Dictionary

Select information from the mysql.proc table and get all the underlying data.

mysql>
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.00 sec)
mysql>
mysql>
mysql>
mysql> SELECT * FROM mysql.proc WHERE name = "myFunction"\G
*************************** 1. row ***************************
              db: test
            name: myFunction
            type: FUNCTION
   specific_name: myFunction
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: NO
   security_type: DEFINER
      param_list: item_sum DECIMAL(10,2)
         returns: decimal(10,1)
            body: BEGIN
RETURN ROUND(item_sum,1);
END
         definer: root@localhost
         created: 2007-07-23 18:58:39
        modified: 2007-07-23 18:58:39
        sql_mode:
         comment:
1 row in set (0.00 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

To see all of the functions across all databases, use the SHOW FUNCTION STATUS command

\G switch from the mysql client utility to display the results in rows, rather than in columns.

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.02 sec)
mysql> DELIMITER ;
mysql>
mysql> select myFunction(123.12);
+--------------------+
| myFunction(123.12) |
+--------------------+
|              123.1 |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
           Db: test
         Name: delivery_day_shipping
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2007-07-23 17:17:21
      Created: 2007-07-23 17:17:21
Security_type: DEFINER
      Comment:
*************************** 2. row ***************************
           Db: test
         Name: myFunction
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2007-07-23 18:58:39
      Created: 2007-07-23 18:58:39
Security_type: DEFINER
      Comment:
2 rows in set (0.01 sec)
mysql>
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

Use the SHOW CREATE FUNCTION command

mysql>
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.02 sec)
mysql> DELIMITER ;
mysql>
mysql> select myFunction(123.12);
+--------------------+
| myFunction(123.12) |
+--------------------+
|              123.1 |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> SHOW CREATE FUNCTION test.myFunction\G
*************************** 1. row ***************************
       Function: myFunction
       sql_mode:
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `myFunction`(item_sum DECIMAL(10,2)) RETURNS decimal(10,1)
BEGIN
RETURN ROUND(item_sum,1);
END
1 row in set (0.00 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>