MySQL Tutorial/Procedure Function/Function Data Dictionary

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

   <source lang="sql">

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></source>

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.

   <source lang="sql">

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></source>

Use the SHOW CREATE FUNCTION command

   <source lang="sql">

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></source>