Prodecimientos MySQL

Los procedimientos almacenados y funciones son nuevas funcionalidades de la versión de MySQL 5.0. Un procedimiento almacenado es un conjunto de comandos SQL que pueden almacenarse en el servidor. Una vez que se hace, los clientes no necesitan relanzar los comandos individuales pero pueden en su lugar referirse al procedimiento almacenado.

Algunas situaciones en que los procedimientos almacenados pueden ser particularmente útiles:

Cuando múltiples aplicaciones cliente se escriben en distintos lenguajes o funcionan en distintas plataformas, pero necesitan realizar la misma operación en la base de datos.
Cuando la seguridad es muy importante. Los bancos, por ejemplo, usan procedimientos almacenados para todas las oparaciones comunes. Esto proporciona un entorno seguro y consistente, y los procedimientos pueden asegurar que cada operación se loguea apropiadamente. En tal entorno, las aplicaciones y los usuarios no obtendrían ningún acceso directo a las tablas de la base de datos, sólo pueden ejectuar algunos procedimientos almacenados.
Los procedimientos almacenados pueden mejorar el rendimiento ya que se necesita enviar menos información entre el servidor y el cliente. El intercambio que hay es que aumenta la carga del servidor de la base de datos ya que la mayoría del trabajo se realiza en la parte del servidor y no en el cliente. Considere esto si muchas máquinas cliente (como servidores Web) se sirven a sólo uno o pocos servidores de bases de datos.
Los procedimientos almacenados le permiten tener bibliotecas o funciones en el servidor de base de datos. Esta característica es compartida por los lenguajes de programación modernos que permiten este diseño interno, por ejemplo, usando clases. Usando estas características del lenguaje de programación cliente es beneficioso para el programador incluso fuera del entorno de la base de datos.

Comandos Create Procedure y Create Function
Estos comandos crean una rutina almacenada. Desde MySQL 5.0.3, para crear una rutina,
es necesario tener el permiso CREATE ROUTINE, y los permisos ALTER ROUTINE y EXECUTE se asignan automáticamente a su creador.


Por defecto, la rutina se asocia con la base de datos actual. Para asociar la rutina explícitamente con una base de datos, especifique el nombre como db_name.sp_name al crearlo.


Si el nombre de rutina es el mismo que el nombre de una función de SQL, necesita usar un espacio entre el nombre y el siguiente paréntesis al definir la rutina, o hay un error de sintaxis. Esto también es cierto cuando invoca la rutina posteriormente.


La cláusula RETURNS puede especificarse sólo con FUNCTION, donde es obligatorio. Se usa para indicar el tipo de retorno de la función, y el cuerpo de la función debe contener un comando RETURN value.



La lista de parámetros entre paréntesis debe estar siempre presente. Si no hay parámetros, se debe usar una lista de parámetros vacía () . Cada parámetro es un parámetro IN por defecto. Para especificar otro tipo de parámetro, use la palabra clave OUT o INOUT antes del nombre del parámetro. Especificando IN, OUT, o INOUT sólo es valido para una PROCEDURE.


Un procedimiento o función se considera “determinista” si siempre produce el mismo resultado para los mismos parámetros de entrada, y “no determinista” en cualquier otro caso. Si no se da ni DETERMINISTIC ni NOT DETERMINISTIC por defecto es NOT DETERMINISTIC.


Varias características proporcionan información sobre la naturaleza de los datos usados por la rutina. CONTAINS SQL indica que la rutina no contiene comandos que leen o escriben datos. NO SQL indica que la rutina no contiene comandos SQL. READS SQL DATA indica que la rutina contiene comandos que leen datos, pero no comandos que escriben datos. MODIFIES SQL DATA indica que la rutina contiene comandos que pueden escribir datos. CONTAINS SQL es el valor por defecto si no se dan explícitamente ninguna de estas características.

MySQL almacena la variable de sistema sql_mode que está en efecto cuando se crea la rutina, y siempre ejecuta la rutina con esta inicialización.

La cláusula COMMENT es una extensión de MySQL, y puede usarse para describir el procedimiento almacenado. Esta información se muestra con los comandos SHOW CREATE PROCEDURE y SHOW CREATE FUNCTION.

MySQL permite a las rutinas que contengan comandos DDL (tales como CREATE y DROP) y comandos de transacción SQL (como COMMIT). Esto no lo requiere el estándar, y por lo tanto, es específico de la implementación.


Los procedimientos almacenados no pueden usar LOAD DATA INFILE.

Los comandos que retornan un conjunto de resultados no pueden usarse desde una función almacenada. Esto incluye comandos SELECT que no usan INTO para tratar valores de columnas en variables, comandos SHOW y otros comandos como EXPLAIN. Para comandos que pueden determinarse al definir la función para que retornen un conjunto de resultados, aparece un mensaje de error Not allowed to return a result set from a function (ER_SP_NO_RETSET_IN_FUNC). Para comandos que puede determinarse sólo en tiempo de ejecución si retornan un conjunto de resultados, aparece el error PROCEDURE %s can't return a result set in the given context (ER_SP_BADSELECT).

Ejemplo.

DROP PROCEDURE IF EXISTS getPostsByCategory;
 
DELIMITER $$
 
CREATE PROCEDURE getPostsByCategory(
    IN _category VARCHAR(100),
    IN _language VARCHAR(2),
    IN _page INT, 
    IN _max INT)
BEGIN
    DECLARE _start INT DEFAULT 0;
    DECLARE _limit1 INT DEFAULT 0;
    DECLARE _limit2 INT DEFAULT 0;
 
    SET _limit1 = 0;
    SET _limit2 = _max;
 
    IF _page > 0 THEN
      SET _limit1 = _page * _max - _max;
    END IF;
 
    SELECT COUNT(1) AS total 
      FROM (
        SELECT blog_posts.id FROM blog_posts 
          LEFT JOIN blog_re_categories2posts ON (blog_re_categories2posts.postId = blog_posts.id) 
          LEFT JOIN blog_categories ON (blog_categories.id = blog_re_categories2posts.categoryId) 
          WHERE blog_categories.slug = _category 
          GROUP BY blog_posts.id
      ) AS Result;
 
    SELECT  title, blog_posts.slug, excerpt, content, author, mainImage, createdAt, day, month, year, blog_posts.language, GROUP_CONCAT(blog_categories.category SEPARATOR ', ') AS categories
      FROM blog_posts 
        LEFT JOIN blog_re_categories2posts ON (blog_re_categories2posts.postId = blog_posts.id)
        LEFT JOIN blog_categories ON (blog_categories.id = blog_re_categories2posts.categoryId)
        WHERE blog_categories.slug = _category  
          AND blog_posts.language = _language 
          AND blog_categories.language = _language
          AND blog_posts.situation = 'Published'
      GROUP BY blog_posts.id
      ORDER BY blog_posts.id DESC
      LIMIT _limit1, _limit2;
END $$
DELIMITER ; 

Uso del CASE.

Las sentencias CASE pueden utilizarse para entrar condicionalmente en una lógica determinada en función del estado de una condición que se cumple. Existen dos tipos de sentencias CASE:
Sentencia case simple: utilizada para entrar en una lógica determinada en función de un valor literal.
Sentencia case buscada: utilizada para entrar en una lógica determinada en función del valor de una expresión.
La cláusula WHEN de la sentencia CASE define el valor que, si se cumple, determina el flujo de
control.



Dicho esto las demás funciones que vienen dentro del case, dictarán que harán con la base de datos, como insertar, actualizar o borrar algun dato de la misma, como esto ya lo vimos no habrá p