Cómo añadir un procedimiento (o función) almacenado a un servidor de base de datos MySQL Server y cómo usarlo en consultas SQL. Como ejemplo añadiremos un procedimiento almacenado (stored procedure) que codificará el valor pasado como parámetro de HTML a texto plano, es decir, si el texto pasado como parámetro contiene algún carácter de escape de HTML lo convertirá a su carácter normal.

Requisitos para crear procedimiento almacenado stored procedure en MySQL Server

Servidor con MySQL Server

En primer lugar necesitaremos disponer de acceso a un servidor con MySQL Server, bien sea en Linux o en Windows. A continuación mostramos algunos tutoriales para instalar MySQL Server tanto en Linux como en Windows:

Herramienta software de administración de MySQL Server

Para el acceso al servidor de MySQL Server, para la administración y poder crear un procedimiento almacenado tendremos varias posibilidades:

  • Usar la herramienta de la línea de comandos y crear el procedimiento de forma manual con su SQL asociado. Esto puede resultar bastante arduo y engorroso, sobre todo si el procedimiento almacenado tiene muchas líneas de código.
  • Usar una herramienta software en modo gráfico: esto es lo habitual y podemos usar herramientas gratuitas oficiales del propio MySQL, como MySQL Administrator o MySQL Workbench. En este tutorial mostraremos cómo crear un procedimiento o función MySQL usando tanto MySQL Administrator como MySQL Workbench.

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Administrator

A continuación mostraremos cómo añadir un procedimiento o función a un servidor MySQL Server usando el software MySQL Administrator. Para ello deberemos disponer de este software (es oficial de MySQL y gratuito). Iniciaremos sesión en MySQL Administrator contra el servidor MySQL Server que consideremos, accederemos a la pestaña «Stored procedures» y, para crear un nuevo, pulsaremos en «Create Stored Proc»:

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Administrator

Introduciremos un nombre para el procedimiento almacenado o función, en nuestro caso, HTML_UnEncode y pulsaremos si va a ser un procedimiento en «Create PROCEDURE», si va a ser una función en «Create FUNCTION». En nuestro caso será una función pues devolverá un valor de cadena. Aunque pulsando cualquiera de los dos botones en el código del procedimiento/función se puede especificar si es un procedimiento (PROCEDURE) o una función (FUNCTION):

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Administrator

Si hemos elegido «Create PROCEDURE» nos habrá generado el código:

CREATE PROCEDURE ‘nombreBD’.’nombre_procedimiento’ ()
BEGIN
# código del procedimiento
END

Si hemos elegido «Create FUNCTION» nos habrá generado el código:

CREATE FUNCTION nombre_BD.nombre_funcion () RETURNS INT
BEGIN
# código de la función
END

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Administrator

Cambiaremos el código generado automáticamente por el que queramos que realice nuestro procedimiento/función. En nuestro caso añadiremos una función a la que se le pasará como parámetro un valor de texto (string / varchar) y devolverá otro valor de texto, lo que hará el código de la función será convertir los caracteres HTML en caracteres normales. Una vez que hayamos introducido el código para el procedimiento/función pulsaremos en «Execute SQL» que comprobará la sintaxis y ejecutará el SQL de creación del procedimiento/función:

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Administrator

Nota: en el código del procedimiento/función se le puede indicar qué usuarios y desde qué IP podrán ejecutarlo, por ejemplo:

  • CREATE DEFINER=’root’@’%’ indica que dicho procedimiento podrá ser ejecutado por el usuario root y desde cualquier IP (%).

Si todo es correcto el procedimiento/función quedará guardada en el servidor MySQL Server y podrá ser usado por cualquier aplicación que lo requiera:

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Administrator

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Workbench

A continuación mostraremos cómo añadir un procedimiento o función a un servidor MySQL Server usando el software MySQL Workbench. Para ello deberemos disponer de este software (es oficial de MySQL y gratuito). Iniciaremos sesión en MySQL Workbench contra el servidor MySQL Server que consideremos, pulsaremos en el botón «Create a new function…» o bien «Create a new stored procedure», según queramos generar un procedimiento (no devolverá nada) o una función (devolverá un valor):

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Workbench

El asistente nos habrá generado un pequeño código para crear una función vacía:

  • En el caso de una función (function):

CREATE FUNCTION new_function ()
RETURNS INTEGER
BEGIN
RETURN 1;
END

  • En el caso de un procedimiento almacenado (stored procedure):

CREATE PROCEDURE new_procedure ()
BEGIN
END

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Workbench

Introduciremos el código que queramos para la función o el procedimiento y pulsaremos en «Apply»:

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Workbench

El asistente para crear un nuevo procedimiento/función de MySQL Workbench nos mostrará el script SQL que se ejecutará, pulsaremos en «Apply»:

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Workbench

Nos mostrará el resultado de la creación del procedimiento/función, si todo es correcto nos mostrará «SQL script was successfully applied to the database», pulsaremos «Finish»:

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Workbench

En la parte izquierda, en Functions o en Stored Procedures podremos consultar las funciones y procedimientos almacenados de nuestro servidor MySQL Server:

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Workbench

Pulsando con el botón derecho del ratón sobre el procedimiento o función que queramos podremos editarlo o eliminarlo:

Añadir procedimiento almacenado stored procedure en servidor MySQL Server con MySQL Workbench

Cómo usar un procedimiento o función de MySQL Server en cualquier aplicación

Una vez que hayamos creado el procedimiento o función como hemos indicado anteriormente, ya podremos usarlo en cualquier aplicación que conecte con MySQL Server. Por ejemplo, en nuestro caso, para usar la función HTML_UnEncode en una consulta SQL en el lenguaje de programación Delphi haremos lo siguiente:

procedure TformMonitorizacion.llenarGridAlarmas(Posicion: Integer);
begin
  mdMonitorizacion.tcEvento.Close;
  mdMonitorizacion.tcEvento.SQL.Clear;
  mdMonitorizacion.tcEvento.SQL.Add('select e.*, a.nombre Agente, a.ultimo_contacto, ');
  mdMonitorizacion.tcEvento.SQL.Add('g.nombre Grupo, HTML_UnEncode(e.evento) evento_deco');
  mdMonitorizacion.tcEvento.SQL.Add('from tevento e');
  mdMonitorizacion.tcEvento.SQL.Add('inner join tagente a on e.id_agente = a.id_agente');
  mdMonitorizacion.tcEvento.SQL.Add('inner join tgrupo g on e.id_grupo = g.id_grupo');
  mdMonitorizacion.tcEvento.SQL.Add('order by e.id_evento desc');
  mdMonitorizacion.tcEvento.Open;
  mdMonitorizacion.tcEvento.RecNo := Posicion;
end;

Como vemos en el código de ejemplo, la función HTML_UnEncode creada se utiliza como una función más de MySQL Server y se añada en la sentencia SQL, en este caso le pasamos como parámetro el valor de un campo de texto y nos devuelve dicho valor pero reemplazando los caracteres unicode HTML por su correspondencia en caracteres normales.

Stored Procedure de ejemplo para convertir códigos HTML a caracteres normales

A continuación mostramos el contenido del procedimiento almacenado de ejemplo que hemos usado en este tutorial. En este caso hemos creado una función a la que se le pasa como parámetro un valor de texto y devuelve otro valor de texto, reemplazando los caracteres de escape propios de HTML en texto normal:

CREATE DEFINER=CURRENT_USER FUNCTION HTML_UnEncode(x VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
BEGIN

DECLARE TextString VARCHAR(255) ;
SET TextString = x ;

#quotation mark
IF INSTR( x , '"' )
THEN SET TextString = REPLACE(TextString, '"','"') ;
END IF ;

#apostrophe
IF INSTR( x , ''' )
THEN SET TextString = REPLACE(TextString, ''','"') ;
END IF ;

#ampersand
IF INSTR( x , '&' )
THEN SET TextString = REPLACE(TextString, '&','&') ;
END IF ;

#less-than
IF INSTR( x , '<' )
THEN SET TextString = REPLACE(TextString, '<','<') ; END IF ; #greater-than IF INSTR( x , '>' ) THEN SET TextString = REPLACE(TextString, '>','>') ;
END IF ;

#non-breaking space
IF INSTR( x , ' ' )
THEN SET TextString = REPLACE(TextString, ' ',' ') ;
END IF ;

#inverted exclamation mark
IF INSTR( x , '¡' )
THEN SET TextString = REPLACE(TextString, '¡','¡') ;
END IF ;

#cent
IF INSTR( x , '¢' )
THEN SET TextString = REPLACE(TextString, '¢','¢') ;
END IF ;

#pound
IF INSTR( x , '£' )
THEN SET TextString = REPLACE(TextString, '£','£') ;
END IF ;

#currency
IF INSTR( x , '¤' )
THEN SET TextString = REPLACE(TextString, '¤','¤') ;
END IF ;

#yen
IF INSTR( x , '¥' )
THEN SET TextString = REPLACE(TextString, '¥','¥') ;
END IF ;

#broken vertical bar
IF INSTR( x , '¦' )
THEN SET TextString = REPLACE(TextString, '¦','¦') ;
END IF ;

#section
IF INSTR( x , '§' )
THEN SET TextString = REPLACE(TextString, '§','§') ;
END IF ;

#spacing diaeresis
IF INSTR( x , '¨' )
THEN SET TextString = REPLACE(TextString, '¨','¨') ;
END IF ;

#copyright
IF INSTR( x , '©' )
THEN SET TextString = REPLACE(TextString, '©','©') ;
END IF ;

#feminine ordinal indicator
IF INSTR( x , 'ª' )
THEN SET TextString = REPLACE(TextString, 'ª','ª') ;
END IF ;

#angle quotation mark (left)
IF INSTR( x , '«' )
THEN SET TextString = REPLACE(TextString, '«','«') ;
END IF ;

#negation
IF INSTR( x , '¬' )
THEN SET TextString = REPLACE(TextString, '¬','¬') ;
END IF ;

#soft hyphen
IF INSTR( x , '­' )
THEN SET TextString = REPLACE(TextString, '­','­') ;
END IF ;

#registered trademark
IF INSTR( x , '®' )
THEN SET TextString = REPLACE(TextString, '®','®') ;
END IF ;

#spacing macron
IF INSTR( x , '¯' )
THEN SET TextString = REPLACE(TextString, '¯','¯') ;
END IF ;

#degree
IF INSTR( x , '°' )
THEN SET TextString = REPLACE(TextString, '°','°') ;
END IF ;

#plus-or-minus 
IF INSTR( x , '±' )
THEN SET TextString = REPLACE(TextString, '±','±') ;
END IF ;

#superscript 2
IF INSTR( x , '²' )
THEN SET TextString = REPLACE(TextString, '²','²') ;
END IF ;

#superscript 3
IF INSTR( x , '³' )
THEN SET TextString = REPLACE(TextString, '³','³') ;
END IF ;

#spacing acute
IF INSTR( x , '´' )
THEN SET TextString = REPLACE(TextString, '´','´') ;
END IF ;

#micro
IF INSTR( x , 'µ' )
THEN SET TextString = REPLACE(TextString, 'µ','µ') ;
END IF ;

#paragraph
IF INSTR( x , '¶' )
THEN SET TextString = REPLACE(TextString, '¶','¶') ;
END IF ;

#middle dot
IF INSTR( x , '·' )
THEN SET TextString = REPLACE(TextString, '·','·') ;
END IF ;

#spacing cedilla
IF INSTR( x , '¸' )
THEN SET TextString = REPLACE(TextString, '¸','¸') ;
END IF ;

#superscript 1
IF INSTR( x , '¹' )
THEN SET TextString = REPLACE(TextString, '¹','¹') ;
END IF ;

#masculine ordinal indicator
IF INSTR( x , 'º' )
THEN SET TextString = REPLACE(TextString, 'º','º') ;
END IF ;

#angle quotation mark (right)
IF INSTR( x , '»' )
THEN SET TextString = REPLACE(TextString, '»','»') ;
END IF ;

#fraction 1/4
IF INSTR( x , '¼' )
THEN SET TextString = REPLACE(TextString, '¼','¼') ;
END IF ;

#fraction 1/2
IF INSTR( x , '½' )
THEN SET TextString = REPLACE(TextString, '½','½') ;
END IF ;

#fraction 3/4
IF INSTR( x , '¾' )
THEN SET TextString = REPLACE(TextString, '¾','¾') ;
END IF ;

#inverted question mark
IF INSTR( x , '¿' )
THEN SET TextString = REPLACE(TextString, '¿','¿') ;
END IF ;

#multiplication
IF INSTR( x , '×' )
THEN SET TextString = REPLACE(TextString, '×','×') ;
END IF ;

#division
IF INSTR( x , '÷' )
THEN SET TextString = REPLACE(TextString, '÷','÷') ;
END IF ;

#capital a, grave accent
IF INSTR( x , 'À' )
THEN SET TextString = REPLACE(TextString, 'À','À') ;
END IF ;

#capital a, acute accent
IF INSTR( x , 'Á' )
THEN SET TextString = REPLACE(TextString, 'Á','Á') ;
END IF ;

#capital a, circumflex accent
IF INSTR( x , 'Â' )
THEN SET TextString = REPLACE(TextString, 'Â','Â') ;
END IF ;

#capital a, tilde
IF INSTR( x , 'Ã' )
THEN SET TextString = REPLACE(TextString, 'Ã','Ã') ;
END IF ;

#capital a, umlaut mark
IF INSTR( x , 'Ä' )
THEN SET TextString = REPLACE(TextString, 'Ä','Ä') ;
END IF ;

#capital a, ring
IF INSTR( x , 'Å' )
THEN SET TextString = REPLACE(TextString, 'Å','Å') ;
END IF ;

#capital ae
IF INSTR( x , 'Æ' )
THEN SET TextString = REPLACE(TextString, 'Æ','Æ') ;
END IF ;

#capital c, cedilla
IF INSTR( x , 'Ç' )
THEN SET TextString = REPLACE(TextString, 'Ç','Ç') ;
END IF ;

#capital e, grave accent
IF INSTR( x , 'È' )
THEN SET TextString = REPLACE(TextString, 'È','È') ;
END IF ;

#capital e, acute accent
IF INSTR( x , 'É' )
THEN SET TextString = REPLACE(TextString, 'É','É') ;
END IF ;

#capital e, circumflex accent
IF INSTR( x , 'Ê' )
THEN SET TextString = REPLACE(TextString, 'Ê','Ê') ;
END IF ;

#capital e, umlaut mark
IF INSTR( x , 'Ë' )
THEN SET TextString = REPLACE(TextString, 'Ë','Ë') ;
END IF ;

#capital i, grave accent
IF INSTR( x , 'Ì' )
THEN SET TextString = REPLACE(TextString, 'Ì','Ì') ;
END IF ;

#capital i, acute accent
IF INSTR( x , 'Í' )
THEN SET TextString = REPLACE(TextString, 'Í','Í') ;
END IF ;

#capital i, circumflex accent
IF INSTR( x , 'Î' )
THEN SET TextString = REPLACE(TextString, 'Î','Î') ;
END IF ;

#capital i, umlaut mark
IF INSTR( x , 'Ï' )
THEN SET TextString = REPLACE(TextString, 'Ï','Ï') ;
END IF ;

#capital eth, Icelandic
IF INSTR( x , 'Ð' )
THEN SET TextString = REPLACE(TextString, 'Ð','Ð') ;
END IF ;

#capital n, tilde
IF INSTR( x , 'Ñ' )
THEN SET TextString = REPLACE(TextString, 'Ñ','Ñ') ;
END IF ;

#capital o, grave accent
IF INSTR( x , 'Ò' )
THEN SET TextString = REPLACE(TextString, 'Ò','Ò') ;
END IF ;

#capital o, acute accent
IF INSTR( x , 'Ó' )
THEN SET TextString = REPLACE(TextString, 'Ó','Ó') ;
END IF ;

#capital o, circumflex accent
IF INSTR( x , 'Ô' )
THEN SET TextString = REPLACE(TextString, 'Ô','Ô') ;
END IF ;

#capital o, tilde
IF INSTR( x , 'Õ' )
THEN SET TextString = REPLACE(TextString, 'Õ','Õ') ;
END IF ;

#capital o, umlaut mark
IF INSTR( x , 'Ö' )
THEN SET TextString = REPLACE(TextString, 'Ö','Ö') ;
END IF ;

#capital o, slash
IF INSTR( x , 'Ø' )
THEN SET TextString = REPLACE(TextString, 'Ø','Ø') ;
END IF ;

#capital u, grave accent
IF INSTR( x , 'Ù' )
THEN SET TextString = REPLACE(TextString, 'Ù','Ù') ;
END IF ;

#capital u, acute accent
IF INSTR( x , 'Ú' )
THEN SET TextString = REPLACE(TextString, 'Ú','Ú') ;
END IF ;

#capital u, circumflex accent
IF INSTR( x , 'Û' )
THEN SET TextString = REPLACE(TextString, 'Û','Û') ;
END IF ;

#capital u, umlaut mark
IF INSTR( x , 'Ü' )
THEN SET TextString = REPLACE(TextString, 'Ü','Ü') ;
END IF ;

#capital y, acute accent
IF INSTR( x , 'Ý' )
THEN SET TextString = REPLACE(TextString, 'Ý','Ý') ;
END IF ;

#capital THORN, Icelandic
IF INSTR( x , 'Þ' )
THEN SET TextString = REPLACE(TextString, 'Þ','Þ') ;
END IF ;

#small sharp s, German
IF INSTR( x , 'ß' )
THEN SET TextString = REPLACE(TextString, 'ß','ß') ;
END IF ;

#small a, grave accent
IF INSTR( x , 'à' )
THEN SET TextString = REPLACE(TextString, 'à','à') ;
END IF ;

#small a, acute accent
IF INSTR( x , 'á' )
THEN SET TextString = REPLACE(TextString, 'á','á') ;
END IF ;

#small a, circumflex accent
IF INSTR( x , 'â' )
THEN SET TextString = REPLACE(TextString, 'â','â') ;
END IF ;

#small a, tilde
IF INSTR( x , 'ã' )
THEN SET TextString = REPLACE(TextString, 'ã','ã') ;
END IF ;

#small a, umlaut mark
IF INSTR( x , 'ä' )
THEN SET TextString = REPLACE(TextString, 'ä','ä') ;
END IF ;

#small a, ring
IF INSTR( x , 'å' )
THEN SET TextString = REPLACE(TextString, 'å','å') ;
END IF ;

#small ae
IF INSTR( x , 'æ' )
THEN SET TextString = REPLACE(TextString, 'æ','æ') ;
END IF ;

#small c, cedilla
IF INSTR( x , 'ç' )
THEN SET TextString = REPLACE(TextString, 'ç','ç') ;
END IF ;

#small e, grave accent
IF INSTR( x , 'è' )
THEN SET TextString = REPLACE(TextString, 'è','è') ;
END IF ;

#small e, acute accent
IF INSTR( x , 'é' )
THEN SET TextString = REPLACE(TextString, 'é','é') ;
END IF ;

#small e, circumflex accent
IF INSTR( x , 'ê' )
THEN SET TextString = REPLACE(TextString, 'ê','ê') ;
END IF ;

#small e, umlaut mark
IF INSTR( x , 'ë' )
THEN SET TextString = REPLACE(TextString, 'ë','ë') ;
END IF ;

#small i, grave accent
IF INSTR( x , 'ì' )
THEN SET TextString = REPLACE(TextString, 'ì','ì') ;
END IF ;

#small i, acute accent
IF INSTR( x , 'í' )
THEN SET TextString = REPLACE(TextString, 'í','í') ;
END IF ;

#small i, circumflex accent
IF INSTR( x , 'î' )
THEN SET TextString = REPLACE(TextString, 'î','î') ;
END IF ;

#small i, umlaut mark
IF INSTR( x , 'ï' )
THEN SET TextString = REPLACE(TextString, 'ï','ï') ;
END IF ;

#small eth, Icelandic
IF INSTR( x , 'ð' )
THEN SET TextString = REPLACE(TextString, 'ð','ð') ;
END IF ;

#small n, tilde
IF INSTR( x , 'ñ' )
THEN SET TextString = REPLACE(TextString, 'ñ','ñ') ;
END IF ;

#small o, grave accent
IF INSTR( x , 'ò' )
THEN SET TextString = REPLACE(TextString, 'ò','ò') ;
END IF ;

#small o, acute accent
IF INSTR( x , 'ó' )
THEN SET TextString = REPLACE(TextString, 'ó','ó') ;
END IF ;

#small o, circumflex accent
IF INSTR( x , 'ô' )
THEN SET TextString = REPLACE(TextString, 'ô','ô') ;
END IF ;

#small o, tilde
IF INSTR( x , 'õ' )
THEN SET TextString = REPLACE(TextString, 'õ','õ') ;
END IF ;

#small o, umlaut mark
IF INSTR( x , 'ö' )
THEN SET TextString = REPLACE(TextString, 'ö','ö') ;
END IF ;

#small o, slash
IF INSTR( x , 'ø' )
THEN SET TextString = REPLACE(TextString, 'ø','ø') ;
END IF ;

#small u, grave accent
IF INSTR( x , 'ù' )
THEN SET TextString = REPLACE(TextString, 'ù','ù') ;
END IF ;

#small u, acute accent
IF INSTR( x , 'ú' )
THEN SET TextString = REPLACE(TextString, 'ú','ú') ;
END IF ;

#small u, circumflex accent
IF INSTR( x , 'û' )
THEN SET TextString = REPLACE(TextString, 'û','û') ;
END IF ;

#small u, umlaut mark
IF INSTR( x , 'ü' )
THEN SET TextString = REPLACE(TextString, 'ü','ü') ;
END IF ;

#small y, acute accent
IF INSTR( x , 'ý' )
THEN SET TextString = REPLACE(TextString, 'ý','ý') ;
END IF ;

#small thorn, Icelandic
IF INSTR( x , 'þ' )
THEN SET TextString = REPLACE(TextString, 'þ','þ') ;
END IF ;

#small y, umlaut mark
IF INSTR( x , 'ÿ' )
THEN SET TextString = REPLACE(TextString, 'ÿ','ÿ') ;
END IF ;

#espacio
IF INSTR( x , ' ' )
THEN SET TextString = REPLACE(TextString, ' ',' ') ;
END IF ;

#parentesis (
IF INSTR( x , '(' )
THEN SET TextString = REPLACE(TextString, '(','(') ;
END IF ;

#parentesis )
IF INSTR( x , ')' )
THEN SET TextString = REPLACE(TextString, ')',')') ;
END IF ;

#comilla simple '
IF INSTR( x , ''' )
THEN SET TextString = REPLACE(TextString, ''',''') ;
END IF ;

RETURN TextString ;

END