3. Los procedimientos almacenados y funciones
Los procedimientos almacenados y funciones permiten ejecutar instrucciones SQL almacenadas en la base de datos.
Una función devuelve un valor de manera sistemática, lo que no es obligatorio en el caso de los procedimientos.
Una función se utiliza básicamente en una consulta SQL, mientras que un procedimiento es un programa autónomo que se puede ejecutar con PHP.
Por tanto una función no puede tener:
- transacción, ya que la consulta utiliza la transacción.
- actualización (INSERT, UPDATE, DELETE...).
- gestión de errores.
- llamada al procedimiento.
- orden SQL de modificación de la estructura de la base de datos.
No se puede crear una tabla temporal.
Estas funcionalidades aparecen con MySQL 5 y permiten disponer de consultas SQL que se vuelven a utilizar y que además son más seguras.
Si tiene una consulta SQL que se ejecuta en 10 páginas PHP distintas, puede ser interesante tener en su lugar un procedimiento almacenado que contenga esta consulta y que sea este procedimiento el que se ejecute 10 veces. Si quiere modificar su consulta SQL, solo la tiene que cambiar una vez en el procedimiento se haya almacenado.
PHPMyAdmin no permite crear un procedimiento almacenado o una función con sus menús. Por lo tanto, escriba el código SQL que permite crearlo.
Por ejemplo, para crear un procedimiento almacenado sin argumento llamado creacion_persona:
CREATE PROCEDURE creacion_persona()
BEGIN
INSERT INTO Persona (Nombre, Apellidos, Edad) VALUES (’Martín’,
’Alonso’,64);
END//
Las palabras clave CREATE PROCEDURE significan que va a crear un procedimiento. A continuación escriba su nombre seguido de la palabra clave BEGIN y las instrucciones SQL seguidas de la palabra clave END. Atención, añada el delimitador // al final del procedimiento almacenado.
Copie esta consulta en la pestaña SQL de PHPMyAdmin, cambie el delimitador a // en la zona Delimitador, a la izquierda de Continuar, y haga clic en Continuar.
Haga clic en la pestaña Estructura y en el enlace Rutinas:
Se muestra su procedimiento almacenado y varias herramientas le permiten modificarlo, ejecutarlo, exportarlo o eliminarlo.
Cuando hace clic en Editar, se abre una nueva ventana.
En esta ventana puede cambiar su nombre, tipo (procedimiento o función), definición o creador.
Un procedimiento o función se considera ”determinista” si siempre devuelve el mismo resultado para los mismos parámetros de entrada.
El Tipo de seguridad permite elegir en DEFINER o INVOKER. En el primer caso, el procedimiento se almacena y ejecuta con los permisos del usuario que lo ha creado. Esto puede plantear un problema si exporta su procedimiento almacenado a una base de datos en la que no existe el usuario. En este caso es mejor seleccionar INVOKER que ejecuta el procedimiento almacenado con los permisos del usuario que llama a la función.
Respecto al Acceso de datos SQL, CONTAINS SQL, es necesario que la rutina contenga consultas SQL. READS SQL DATA permite indicar que la rutina no realiza ningún acceso en modo escritura, sino solo en modo lectura. MODIFIES SQL DATA permite indicar que la rutina no tiene consultas SQL. Estos parámetros se usan para mejorar el rendimiento.
Para ejecutar este procedimiento almacenado:
CALL creacion_persona();
Copie esta consulta en la pestaña SQL de PHPMyAdmin y haga clic en Continuar:
Seleccione la tabla Persona y haga clic en Examinar:
El procedimiento almacenado ha insertado Martín Alonso.
Puede pasar los argumentos al procedimiento almacenado. Estos argumentos pueden ser de tipo:
- IN: variable de entrada del procedimiento almacenado utilizado dentro de esta.
- OUT: variable de salida del procedimiento almacenado. Esta variable se actualiza al final del procedimiento almacenado y se devuelve a PHP.
- INOUT: variable de entrada-salida del procedimiento almacenado.
Para hacer este procedimiento más versátil, va a tomar como argumentos los apellidos, el nombre y la edad de la persona que va a insertar.
Modifique el procedimiento almacenado de esta manera:
DROP PROCEDIMIENTO creacion_persona //
CREATE PROCEDIMIENTO creacion_persona
(IN apellidos VARCHAR(20), IN Nombre VARCHAR(20), IN Edad INT)
BEGIN
INSERT INTO Persona (Nombre, Apellidos, Edad) VALUES
(nombre,apellidos,edad);
END
//
DROP PROCEDURE significa «eliminar el procedimiento», ya que debe eliminarse antes de volver a crearlo.
Copie este código en la pestaña SQL de PHPMyAdmin, cambie el delimitador a // en la zona Delimitador, a la izquierda del botón Continuar y pulse en el botón Continuar. Responda Sí, si recibe un mensaje para confirmar la eliminación del procedimiento almacenado.
Para ejecutar este procedimiento almacenado:
CALL creation_persona(’Emilio’,’Martin’,15);
Copie esta consulta en la pestaña SQL de PHPMyAdmin y haga clic en Continuar.
Seleccione la tabla Persona y haga clic en Examinar:
He aquí un ejemplo de procedimiento almacenado que devuelve el valor al cubo de un argumento:
CREATE PROCEDIMIENTO cubo(IN valor_entrada INT, OUT valor_salida BIGINT)
BEGIN
SELECT valor_entrada*valor_entrada*valor_entrada INTO valor_salida;
END//
La instrucción SELECT... INTO variable almacena el resultado de select en la variable.
Para ejecutar este procedimiento almacenado:
CALL cubo(5,@RESULTADO);
SELECT @RESULTADO;
El valor de vuelta se ubica en la variable @RESULTADO. El nombre de esta variable tiene que estar obligatoriamente precedido por el símbolo @.
SELECT @RESULTADO permite mostrar el valor de la variable @RESULTADO.
Copie esta consulta en la pestaña SQL de PHPMyAdmin y haga clic en Continuar:
Los procedimientos almacenados se utilizan cuando se quiere ejecutar consultas SQL. En el ejemplo anterior, es más lógico crear una función:
CREATE FUNCTION funcion_cubo (valor_entrada INT) RETURNS BIGINT
RETURN valor_entrada*valor_entrada*valor_entrada;
Las palabras clave CREATE FUNCTION significan que va a crear una función. A continuación escriba su nombre, seguido de los argumentos de entrada. Debe seleccionar el tipo de valor devuelto después de la palabra clave RETURNS. Para terminar, escriba el valor devuelto después de la palabra clave RETURN.
Copie esta consulta en la pestaña SQL de PHPMyAdmin y haga clic en Continuar.
Para probar esta función:
SELECT funcion_cubo(5);
Copie esta consulta en la pestaña SQL de PHPMyAdmin y haga clic en Continuar.
Puede modificar, ejecutar, exportar o eliminar la función con ayuda de la misma pestaña que se utiliza en los procedimientos almacenados.
Existe un lenguaje que permite crear condiciones, bucles, variables y otros objetos en el procedimiento almacenado, pero no es el objetivo de este capítulo.
4. Otros objetos de MySQL
a. Las tablas
Una vez que ha creado la tabla Persona en PHPMyAdmin, habrá observado que este código muestra:
CREATE TABLE ’_prueba’.’Persona’ (
’Id_person’ INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
’Apellidos’ VARCHAR(20) NOT NULL,
’Nombre’ VARCHAR(20) NOT NULL,
’Edad’ INT NOT NULL
) ENGINE = MYISAM;
La palabra clave CREATE se utiliza para crear la tabla. Si quiere eliminarla, utilice la palabra clave DROP:
Para eliminar la tabla Persona:
DROP TABLE ’_prueba’.’Persona’;
Para modificar una tabla, puede utilizar la palabra clave ALTER, pero la sintaxis es mucho más compleja, ya que depende de lo que quiera hacer con los campos. Puede modificar, añadir o eliminar un campo, cambiar su tipo...
Tiene más información en el siguiente enlace: http://dev.mysql.com/doc/refman/5.0/es/alter-table.html
Las palabras clave siempre son CREATE, DROP y ALTER, sea para crear, eliminar, modificar una base, una tabla, un índice o cualquier otro objeto de la base de datos.
b. Los índices
Los índices se utilizan para mejorar el rendimiento de una tabla. Cuando crea un índice en el campo de tipo identificador, la base de datos devuelve rápidamente el identificador entre todos los demás. Resulta imprescindible si tiene muchos registros, ya que la operación puede ser 100 veces más rápida.
Un índice se utiliza para:
- Encontrar rápidamente datos desde la cláusula WHERE.
- Leer registros en tablas con ayuda de las uniones.
- Ordenar o añadir datos.
Hemos visto en PHPMyAdmin cómo se crea un índice, pero también se puede crear en SQL. Su sintaxis es igual que en los otros objetos SQL, es decir, debe utilizar la palabra clave CREATE:
CREATE INDICE nombre_indice ON nombre_tabla (nombre_campo);
Este código crea un índice en el campo nombre_campo de la tabla nombre_tabla.
Hay varias opciones para que sea única, para crear un índice en varios campos al mismo tiempo, etc.
MyISAM representa el motor de almacenamiento de MySQL, es decir, la manera en que MySQL va a almacenar estos datos. Hay dos motores de almacenamiento:
- MyISAM: permite los índices en los campos de tipo fulltext y es muy rápido en las consultas de tipo SELECT o INSERT.
- InnoDB: soporta las transacciones y las claves extranjeras.
Una transacción permite asegurar que una secuencia de instrucción SQL se ha realizado correctamente. Si surge un problema tras una consulta, la transacción anula todas las demás consultas.
Tiene más información en el siguiente enlace: http://dev.mysql.com/doc/refman/5.0/es/create-index.html
c. Las vistas
Una vista es una consulta SQL almacenada en el servidor que contiene una consulta de tipo SELECT y que se utiliza como una tabla.
Por ejemplo, para crear una vista llamada vista_ejemplo que muestra las cuatro primeras letras del nombre y los apellidos de las personas menores de 35 años y los apellidos y el nombre de las personas mayores de 35 años:
CREATE VIEW vista_ejemplo AS
SELECT SUBSTR(Nombre,1,4) as Raiz_nombre, Apellidos FROM Persona
WHERE Edad < 35
UNION
Select Nombre,Apellidos as Raiz_nombre, Apellidos FROM Persona
WHERE Edad > 35;
En PHPMyAdmin, tiene la nueva tabla vista_ejemplo. Haga clic en ella para mostrar sus datos:
La sentencia SELECT contiene una clausula UNION. El conjunto final es las personas con edad <35 años y >35 años.
Puede utilizar esta vista como cualquier otra tabla.
Puede utilizar esta vista como cualquier otra tabla.
Espero haber ayudado en algo. Hasta la próxima oportunidad!
Twittear
No hay comentarios:
Publicar un comentario