domingo, 9 de noviembre de 2014

SQL avanzado en MySQL y PHPMyAdmin - 4 de 5



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 , 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.


Espero haber ayudado en algo. Hasta la próxima oportunidad!










No hay comentarios:

Publicar un comentario

       

Etiquetas

Academy (23) Accediendo a datos con ADO .NET (31) Acceso a la red (30) Algoritmo (34) Algoritmos en JAVA (2) Ampliación de clases (2) APRENDA A PROGRAMAR COMO SI ESTUVIERA EN PRIMERO - Autores : IKER AGUINAGA (3) APRENDA A PROGRAMAR COMO SI ESTUVIERA EN PRIMERO - Autores : IKER AGUINAGA (10) Aprendiendo a desarrollar en Windows 8 (5) Aprendiendo UML en 24 Horas (Autor : Joseph Schmuller ) (30) Arquitectura (29) Arquitectura del Computador (3) Arquitectura del Computador - Historia de la informática (1) Asignación de direcciones IP (23) Aspectos fundamentales de bases de datos (5) Auditoría de la dirección (2) Auditoría de Sistemas (3) Auditoría Informática - Un enfoque práctico - Mario G . Piattini y Emilio del Peso (7) Avanzado (23) Base de Datos (67) Básico (23) Bios (29) Business Productivity Online Suite - BPOS (3) Capa de Red (22) Capa de Transporte (16) Capítulo 1 - Documentos HTML5 (6) Capítulo 10. API Web Storage (2) Capítulo 11. API IndexedDB (4) Capítulo 12. API File (1) Capítulo 2. Estilos CSS y modelos de caja (7) Capítulo 3. Propiedades CSS3 (4) Capítulo 4. Javascript (6) Capítulo 5. Video y audio (6) Capítulo 6. Formularios y API Forms (8) Capítulo 7. API Canvas (5) Capítulo 8. API Drag and Drop (2) Capítulo 9. API Geolocation (2) CCNA1 v5.0 (227) CCNA1 v6.0 (23) CCNA2 v5.0 (26) CCNA3 v5.0 (25) CCNA4 v5.0 (23) CD-ROM (3) Chapter 1 How does Xamarin.Forms fit in? (7) Chapter 2 Anatomy of an app (5) Cisco (329) Cloud Computing (3) CNNA v5.0 Routing & Switching (248) CNNA v6.0 Routing & Switching (2) Codigo (2) Computadora (32) Configuración (29) Configuración de un sistema operativo de red (21) Control (29) Creación de tipos de datos y tablas (3) Creación y Administración de bases de datos (3) Creando la Interface de la Aplicación Windows (50) Creating Mobile Apps with Xamarin.Forms (13) Cuenta (29) Curso (32) Curso Aprendiendo a Programar (25) Datos (3) Desarrollando en Windows 8 - AVANZADO (2) Desarrollando en Windows 8 - BÁSICO (3) Desarrollando en Windows 8 - INTERMEDIO (2) Desarrollo (2) Desarrollo .Net (21) Desarrollo avanzado de Windows Store Apps usando C# (1) Desarrollo basado en conceptos de Ingeniería de Software para Visual Studio (2) DESARROLLO DE APLICACIONES WINDOWS CON MICROSOFT .NET (37) DESARROLLO DE APLICACIONES WINDOWS CON MICROSOFT .NET (Autor: Luis Dueñas Huaroto) (29) Desarrollo en Microsoft Visual Studio (44) Desarrollo en Microsoft Visual Studio - AVANZADO (15) Desarrollo en Microsoft Visual Studio - BÁSICO (14) Desarrollo en Microsoft Visual Studio - INTERMEDIO (18) Desarrollo en Windows Phone 8 (13) Diagnostico (4) Diagrama (3) Diagramas de actividades (2) Diagramas de colaboraciones (2) Diagramas de secuencias (2) Digital (2) Diplomado (2) Disco (29) Disco Duro (4) Diseño de aplicaciones de Windows 8 en HTML 5 (7) Dispositivos Electrónicos (11) Doctorado (2) Ejemplos (3) Ejemplos de algoritmos (27) El camino hacia el CSS3 (3) El diseño web flexible (6) El elemento de diseño Canvas (3) El enfoque de los sistemas (3) El flujo de un programa (2) El gran libro de HTML5 - CSS3 y Javascript - Autor: Juan Diego Gauchat (55) El principio de organicidad (7) Electrónica (2) Elementos de un sistema (5) Empresas (2) Entrada y salida (4) Entropía y neguentropía (7) Estrategia (2) Estructura de un programa Java (12) Estructuras de almacenamiento (10) Estructuras de control (6) Estructuras de las tablas en SQL Server (2) Estructuras fundamentales de los datos (2) Ethernet (21) Evolución y Familias de los Microprocesadores (15) Exámen (23) Exploración de la red (23) Extensión de clases (4) Facebook (4) Familia Intel (15) Forefront (8) Función (3) Funciones de una red (12) Funciones de una red informática (1) Fundamentos de C# para absolutos principiantes (17) Fundamentos de programación en Java (50) Generaciones de la computadora (5) Gestión (3) Gestión de riesgos - Auditoría de Sistemas (1) GONZALO MARTÍNEZ (1) Grupos Facebook (1) Harvard (29) Historia de las computadoras (11) HTML5 y CSS3 - Autor: Christophe Aubry (99) HTML5 y CSS3 aplicadal texto (7) HTML5 y CSS3 para los formularios (15) Imágenes (2) Implementación de Windows 7 (11) Información (31) Informática (29) Ingeniería (4) Instalar (29) Inteligencia (2) Inteligencia de Negocios con SQL Server (3) Intermedio (23) Internet (29) Internet Explorer 9 (3) Introducción a ASP.NET 5 (8) Introducción a Java (7) Introducción a jQuery (8) Introducción a la Auditoría de Sistemas (2) Introducción a la teoría general de sistemas (Oscar Johansen Bertoglio) (39) Introducción a Networking (2) Introducción a Window Forms (5) Introducción al acceso a datos con ADO .NET (9) Investigación de Operaciones (12) Java (52) Jump Start de consultas en las bases de datos de Microsoft SQL Server 2012 (8) La definición de un Sistema (6) La evolución del HTML y del CSS (3) La nueva sintaxis HTML5 (12) LA QUINTA DISCIPLINA en la práctica (Autor : Peter Senge) (28) Las animaciones en CSS3 (5) Las transformaciones CSS3 (11) Las transiciones con CSS3 (8) Licenciamiento Microsoft (3) Local Area Network (LAN) - Red de Area Local (2) Lógico (2) Los elementos de la estructura en html5 (9) Los elementos multimedia: audio y vídeo (2) Los estilos de caja en CSS3 (13) Los nuevos selectores de CSS3 (6) Maestría (2) Mantenimiento de Mouse y Teclado (2) Manual de Microsoft SQL Server - Full Transact SQL (68) Manual de soporte técnico para escuelas sobre windows 7 (42) Marco Teorico de Investigación de Operaciones (6) Medios de Almacenamiento (11) Medios de Networking (2) Mejorando la Interface de las Aplicaciones Windows (26) Memoria Tipos y Clases (5) Método (2) Metodología (1) Microsoft (324) Microsoft Lync 2010 (7) Microsoft Silverlight 4.0 (2) Microsoft Virtual Academy (356) Modelo (2) Modelo OSI y TCP-IP (2) Modelos con poco grado de dificultad de Programación Lineal - Investigación de Operaciones (13) Modelos con razonable grado de dificultad de Programación Lineal - Investigación de Operaciones (10) Modelos de desafio de Programación Lineal - Investigación de Operaciones (5) Modelos difíciles de Programación Lineal - Investigación de Operaciones (5) Modelos Fáciles de Programación Lineal - Investigación de Operaciones (13) Modelos lineales con solver (3) Modulo (23) Movimiento (2) Mozilla (29) MS SQL Server (77) MS Virtualization para Profesionales VMware - Gestión (3) MS Virtualization para Profesionales VMware- Plataforma (4) MVA (263) Negocio (2) Nivel Avanzado Desarrollo .Net (6) Nivel Básico Desarrollo .Net (11) Nivel Intermedio Desarrollo .Net (8) Normas técnicas peruanas y su evolución - Auditoría de Sistemas (1) Nube Privada - Avanzado (6) Nube Privada - Básico (6) Nube Privada - Intermedio (6) Office 365 (3) Optimización de Escritorio (10) Optimización de Escritorio - Avanzado (4) Optimización de Escritorio - Básico (3) Optimización de Escritorio - Intermedio (3) ORACLE 10g - ADMINISTRACIÓN Y ANÁLISIS (3) Oracle 10g y el Grid Computing (3) Organización aleatoria y secuencial (1) Partes principales de la Mainboard (12) Perceptron (2) Perfil (2) Periféricos de Entrada / Salida (15) Pesi (2) PHP y MySQL - Manual de aprendizaje para crear un sitio web - Autor : Olivier ROLLET (79) Plan (2) Plataforma (29) PMBOK (24) PMBOK - Guía de los fundamentos para la dirección de proyectos (24) PMBOK - INFLUENCIA DE LA ORGANIZACIÓN Y CICLO DE VIDA DEL PROYECTO (6) PMBOK - Introducción (11) PMBOK - PROCESOS DE LA DIRECCIÓN DE PROYECTOS (5) Prevención - Herramientas e Instrumentos de Medida (9) Principios básicos de enrutamiento y switching (201) Proceso (2) Proceso de auditoría de sistemas informáticos (2) Programación en Android - Auor : Salvador Gómez Oliver (46) Programación paso a paso de C# - Autor : Nacho Cabanes (16) Protocolos y comunicaciones de red (17) Proyecto (2) Qué es un sistema (4) Red de Área Local Inalámbrica (WLAN) (4) Redes (30) Redes inalámbricas - WIRELESS - Conocimiento general (15) Redes neuronales (2) Redes y Comunicaciones (45) Reparación de Fuentes - UPS - Estabilizadores (10) Reparación de Impresoras (9) Reparación de Monitores (16) Router (29) Seguridad en la Nube (3) Seminario (23) Server (24) Sharepoint 2010 - Nivel Básico (6) Sharepoint 2010 - Niveles Avanzados (18) Sharepoint 2010 - Niveles Avanzados - Básico (8) Sharepoint 2010 - Niveles Avanzados - Intermedio (9) Sinergia y recursividad (4) Sistema (33) Sistema de Cableado Estructurado (9) Software (30) SOLUCIÓN GRÁFICA DE MODELOS DE PROGRAMACIÓN LINEALES - INVOPE (8) Soporte a Infraestructura (3) SQL (38) SQL Azure - Introducción (3) Subsistemas de control (4) Tablas (4) Tarjeta Principal del Sistema (10) Tarjetas de Interfaces (7) Tecnología (31) Tecnologías LAN (1) TEORÍA GENERAL DE SISTEMAS (1) Tic (2) Tipo (2) TML5 y CSS3 - Autor: Christophe Aubry (12) Trabajando con el Formulario (7) Un diseño HTML5/CSS3: dConstruct 2011 (3) Un diseño HTML5/CSS3: FlipThru (2) Un diseño HTML5/CSS3: The Cat Template (2) Usando Controles Windows Forms (12) Usando Herramientas de Datos de Visual Studio (6) Ventas (2) Virtualización Hyper - V Nivel Básico (5) Virtualización Hyper - V Nivel Intermedio (5) What’s New in Windows 8.1 Security (4) Window (29) Windows 7 Segunda Fase - AVANZADO (4) Windows 7 Segunda Fase - BÁSICO (6) Windows 7 Segunda Fase - INTERMEDIO (4) Windows 8 - Vista Previa (4) Windows 8.1 To Go (2) Windows Azure (3) Windows Phone 7 (2) Windows Server 2008 R2 (3) Windows Server 2012 - Gestión y Automatización (3) Windows Server 2012 R2 Essentials (7) Windows Server 2012: Almacenamiento (5) Windows Server 2012: Identidad y Acceso (4) Windows Server 2012: Revisión Técnica (7) Xamarin (1)

Páginas vistas en total según Google