Mysql Stored Procedures Column Type Reference

Question

In PL/SQL you can reference the type of a table column for procedure variables and arguments using the TABLE.COLUMN_NAME%type syntax. This makes code maintenance much easier. Is there an equivalent of this in the mysql stored procedure language?

Where might one need this? Consider the following insert statement:

INSERT INTO NL_LIST (NAME, EMAIL)
    SELECT
        NAME,
        normalizeEmail(EMAIL) 
    FROM
        RAW_NL_LIST;

For this i would like to be able to define normalizeEmail in(roughly) the following way:

CREATE FUNCTION normalizeEmail(email RAW_NL_LIST.EMAIL%type)
RETURNS NL_LIST.EMAIL%type
BEGIN
... implementation here
END;

Answer 1

No, you cannot automatically match the type used in the table definition with the type in a stored procedure. You will have to lookup the table definition yourself and enter the correct type.

See: http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html

Here it says:

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type    <<<-- no special magic allowed

....