Mega Code Archive

 

Categories / MySQL / Procedure Function

 

Determining the Code of an stored procedures

mysql> mysql> delimiter $$ mysql> mysql> mysql> CREATE FUNCTION shorten(s VARCHAR(255), n INT)     ->  RETURNS VARCHAR(255)     ->  BEGIN     ->     IF ISNULL(s) THEN     ->        RETURN '';     ->     ELSEIF n<15 THEN     ->        RETURN LEFT(s, n);     ->     ELSE IF CHAR_LENGTH(s) <= n THEN     ->            RETURN s;     ->         ELSE     ->            RETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5));     ->         END IF;     ->     END IF;     ->  END$$ Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT shorten("abcdefghijklmnopqrstuvwxyz", 15)$$ +-------------------------------------------+ | shorten("abcdefghijklmnopqrstuvwxyz", 15) | +-------------------------------------------+ | abcde ... vwxyz                           | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> delimiter ; mysql> mysql> SHOW CREATE FUNCTION shorten; +----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--- -------------------+----------------------+--------------------+ | Function | sql_mode | Create Function                                                                                                                                                                                                                                                                                                                                                                     | ch aracter_set_client | collation_connection | Database Collation | +----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--- -------------------+----------------------+--------------------+ | shorten  |          | CREATE DEFINER=`root`@`localhost` FUNCTION `shorten`(s VARCHAR(255), n INT) RETURNS varchar(255) CHARSET latin1 BEGIN     IF ISNULL(s) THEN        RETURN '';     ELSEIF n<15 THEN        RETURN LEFT(s, n);     ELSE IF CHAR_LENGTH(s) <= n THEN            RETURN s;         ELSE            RETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5));         END IF;     END IF;  END | latin1               | latin1_swedish_ci    | latin1_swedish_ci  | +----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--- -------------------+----------------------+--------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> drop function shorten; Query OK, 0 rows affected (0.00 sec) mysql> mysql>