Base de Datos Oracle 10g - Conceptos Fundamentales de SQL I by Aula cero

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I Volumen I • Guía del Alumno

D17108CS10 Edición 1.0 Junio de 2004 D39504

Autor

Nancy Greenberg

Esta documentación contiene información propiedad de Oracle Corporation; se suministra bajo los términos de un contrato de licencia que contiene restricciones de uso y de revelación y está también protegida por la legislación de derechos de autor. Queda prohibida la ingeniería reversa del software. Si esta documentación se entrega a una agencia del Ministerio de Defensa del Gobierno de EE.UU., se aplicará la siguiente advertencia de “Restricted Rights”:

Colaboradores Técnicos y Revisores Wayne Abbott Christian Bauwens Perry Benson Brian Boxx Zarko Cesljas Dairy Chan Laszlo Czinkoczki Marjolein Dekkers Matthew Gregory Stefan Grenstad Joel Goodman Rosita Hanoman Sushma Jagannath Angelika Krupp Christopher Lawless Marcelo Manzano Isabelle Marchand Malika Marghadi Valli Pataballa Elspeth Payne Ligia Jasmin Robayo Bryan Roberts Helen Robertson Lata Shivaprasad John Soltani Priya Vennapusa Ken Woolfe

Editor Nita K. Brozowski

Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). Este material ni ninguna parte del mismo podrá ser reproducido en cualquier forma o a través de cualquier medio sin el expreso consentimiento por escrito de Oracle Corporation. La reproducción es una violación de la ley de derechos de autor y puede tener consecuencias penales o civiles. Si esta documentación se entrega a una agencia del Gobierno de EE.UU. no perteneciente al Ministerio de Defensa, se aplicará la advertencia de "Restricted Rights" definida en FAR 52.227-14, Rights in Data-General, incluido Alternate III (junio de 1987). La información contenida en este documento está sujeta a cambio sin previo aviso. Si detecta cualquier problema en la documentación, le agradeceremos lo comunique por escrito a Education Products, Oracle Corporation, 500 Oracle Parkway, Redwood Shores, CA 94065. Oracle Corporation no garantiza que este documento esté exento de errores. Toda referencia a Oracle y a los Productos de Oracle constituyen marcas comerciales o marcas comerciales registradas de Oracle Corporation. Todos los demás nombres de compañías y productos mencionados se utilizan a los exclusivos fines de su identificación y pueden ser marcas comerciales de sus respectivos propietarios.

Contenido

Prefacio Introducción Objetivos de la Lección I-2 Objetivos del Curso I-3 Oracle10g I-4 Base de Datos Oracle 10g I-6 Oracle Application Server 10g I-7 Oracle Enterprise Manager 10g Grid Control I-8 Sistemas de Gestión de Bases de Datos Relacionales y Relacionales de Objetos I-9 Plataforma para Internet de Oracle I-10 Ciclo de Vida de Desarrollo del Sistema I-11 Almacenamiento de Datos en Medios Diferentes I-13 Concepto de Base de Datos Relacional I-14 Definición de una Base de Datos Relacional I-15 Modelos de Datos I-16 Modelo de Relación de Entidades I-17 Convención de Modelado de Relación de Entidades I-19 Relación de Varias Tablas I-21 Terminología de Bases de Datos Relacionales I-23 Propiedades de las Bases de Datos Relacionales I-25 Comunicación con un RDBMS mediante SQL I-26 Sistema de Gestión de Bases de Datos Relacionales de Oracle I-27 Sentencias SQL I-28 Tablas Utilizadas en el Curso I-29 Resumen I-30 1

Recuperación de Datos mediante la Sentencia SELECT de SQL Objetivos 1-2 Capacidades de las Sentencias SELECT de SQL 1-3 Sentencia SELECT Básica 1-4 Selección de Todas las Columnas 1-5 Selección de Columnas Específicas 1-6 Escritura de Sentencias SQL 1-7 Valores por Defecto de Cabeceras de Columna 1-8 Expresiones Aritméticas 1-9 Uso de Operadores Aritméticos 1-10 Prioridad de Operadores 1-11 Definición de un Valor Nulo 1-12 Valores Nulos en Expresiones Aritméticas 1-13 Definición de un Alias de Columna 1-14 Uso de Alias de Columnas 1-15 Operador de Concatenación 1-16 Cadenas de Literales 1-17 Uso de Cadenas de Literales 1-18 Operador de Comillas (q) Alternativo 1-19 Filas Duplicadas 1-20 Interacción de SQL e iSQL*Plus 1-21 Sentencias SQL frente a Comandos de iSQL*Plus 1-22 Visión General de iSQL*Plus 1-23

iii

Conexión a iSQL*Plus 1-24 Entorno iSQL*Plus 1-25 Visualización de la Estructura de Tablas 1-26 Interacción con Archivos de Comandos 1-28 Página History de iSQL*Plus 1-32 Definición de Preferencias de iSQL*Plus 1-34 Definición de la Preferencia de Ubicación de Salida 1-35 Resumen 1-36 Práctica 1: Visión General 1-37 2

Restricción y Ordenación de Datos Objetivos 2-2 Limitación de Filas mediante una Selección 2-3 Limitación de las Filas que se Seleccionan 2-4 Uso de la Cláusula WHERE 2-5 Cadenas de Caracteres y Fechas 2-6 Condiciones de Comparación 2-7 Uso de Condiciones de Comparación 2-8 Uso de la Condición BETWEEN 2-9 Uso de la Condición IN 2-10 Uso de la Condición LIKE 2-11 Uso de la Condición NULL 2-13 Condiciones Lógicas 2-14 Uso del Operador AND 2-15 Uso del Operador OR 2-16 Uso del Operador NOT 2-17 Reglas de Prioridad 2-18 Uso de la Cláusula ORDER BY 2-20 Ordenación 2-21 Variables de Sustitución 2-22 Uso de la Variable de Sustitución & 2-24 Valores de Carácter y de Fecha con Variables de Sustitución 2-26 Especificación de Nombres de Columna, Expresiones y Texto 2-27 Uso de la Variable de Sustitución && 2-24 Uso del Comando DEFINE de iSQL*Plus 2-29 Uso del Comando VERIFY 2-30 Resumen 2-31 Práctica 2: Visión General 2-32

Uso de Funciones de una Sola Fila para Personalizar la Salida Objetivos 3-2 Funciones SQL 3-3 Dos Tipos de Funciones SQL 3-4 Funciones de una Sola Fila 3-5 Funciones de Carácter 3-7 Funciones de Manipulación de Mayúsculas/Minúsculas 3-9 Uso de Funciones de Manipulación de Mayúsculas/Minúsculas 3-10 Funciones de Manipulación de Caracteres 3-11 Uso de Funciones de Manipulación de Caracteres 3-12 Funciones Numéricas 3-13

Uso de la Función ROUND 3-14 Uso de la Función TRUNC 3-15 Uso de la Función MOD 3-16 Trabajo con Fechas 3-17 Aritmética con Fechas 3-20 Uso de Operadores Aritméticos con Fechas 3-21 Funciones de Fecha 3-22 Uso de Funciones de Fecha 3-23 Práctica 3: Visión General de la Parte 1 3-25 Funciones de Conversión 3-26 Conversión de Tipos de Datos Implícita 3-27 Conversión de Tipos de Datos Explícita 3-29 Uso de la Función TO_CHAR con Fechas 3-32 Elementos del Modelo de Formato de Fecha 3-33 Uso de la Función TO_CHAR con Fechas 3-37 Uso de la Función TO_CHAR con Números 3-38 Uso de las Funciones TO_NUMBER y TO_DATE 3-41 Formato de Fecha RR 3-43 Ejemplo de Formato de Fecha RR 3-44 Anidamiento de Funciones 3-45 Funciones Generales 3-47 Función NVL 3-48 Uso de la Función NVL 3-49 Uso de la Función NVL2 3-50 Uso de la Función NULLIF 3-51 Uso de la Función COALESCE 3-52 Expresiones Condicionales 3-54 Expresión CASE 3-55 Uso de la Expresión CASE 3-56 Función DECODE 3-57 Uso de la Función DECODE 3-58 Resumen 3-60 Práctica 3: Visión General de la Parte 2 3-61 4

Información sobre Datos Agregados mediante las Funciones de Grupo Objetivos 4-2 ¿Qué son las Funciones de Grupo? 4-3 Tipos de Funciones de Grupo 4-4 Funciones de Grupo: Sintaxis 4-5 Uso de las Funciones AVG y SUM 4-6 Uso de las Funciones MIN y MAX 4-7 Uso de la Función COUNT 4-8 Uso de la Palabra Clave DISTINCT 4-9 Funciones de Grupo y Valores Nulos 4-10 Creación de Grupos de Datos 4-11 Creación de Grupos de Datos: Sintaxis de la Cláusula GROUP BY 4-12 Uso de la Cláusula GROUP BY 4-13 Agrupación por Más de Una Columna 4-15 Uso de la Cláusula GROUP BY en Varias Columnas 4-16 Consultas Ilegales mediante Funciones de Grupo 4-17 Restricción de Resultados de Grupos 4-19

Restricción de Resultados de Grupos con la Cláusula HAVING 4-20 Uso de la Cláusula HAVING 4-21 Anidamiento de Funciones de Grupo 4-23 Resumen 4-24 Práctica 4: Visión General 4-25 5

Visualización de Datos de Varias Tablas Objetivos 5-2 Obtención de Datos de Varias Tablas 5-3 Tipos de Uniones 5-4 Unión de Tablas mediante la Sintaxis SQL:1999 5-5 Creación de Uniones Naturales 5-6 Recuperación de Registros con Uniones Naturales 5-7 Creación de Uniones con la Cláusula USING 5-8 Unión de Nombres de Columnas 5-9 Recuperación de Registros con la Cláusula USING 5-10 Cualificación de Nombres de Columna Ambiguos 5-11 Uso de Alias de Tabla 5-12 Creación de Uniones con la Cláusula ON 5-13 Recuperación de Registros con la Cláusula ON 5-14 Autouniones mediante la Cláusula ON 5-15 Aplicación de Condiciones Adicionales a una Unión 5-17 Creación de Uniones en Tres Sentidos con la Cláusula ON 5-18 Uniones No Igualitarias 5-19 Recuperación de Registros con Uniones No Igualitarias 5-20 Uniones Externas 5-21 Uniones INNER frente a OUTER 5-22 LEFT OUTER JOIN 5-23 RIGHT OUTER JOIN 5-24 FULL OUTER JOIN 5-25 Productos Cartesianos 5-26 Generación de un Producto Cartesiano 5-27 Creación de Uniones Cruzadas 5-28 Resumen 5-29 Práctica 5: Visión General 5-30

Uso de Subconsultas para Resolver Consultas Objetivos 6-2 Uso de Subconsultas para Resolver Problemas 6-3 Sintaxis de Subconsultas 6-4 Uso de Subconsultas 6-5 Instrucciones para el Uso de Subconsultas 6-6 Tipos de Subconsultas 6-7 Subconsultas de una Sola Fila 6-8 Ejecución de Subconsultas de una Sola Fila 6-9 Uso de Funciones de Grupo en una Subconsulta 6-10 La Cláusula HAVING con Subconsultas 6-11 ¿Qué Problema Hay en Esta Sentencia? 6-12 ¿Devolverá Filas Esta Sentencia? 6-13 Subconsultas de Varias Filas 6-14 Uso del Operador ANY en Subconsultas de Varias Filas 6-15

Uso del Operador ALL en Subconsultas de Varias Filas 6-16 Valores Nulos en una Subconsulta 6-17 Resumen 6-19 Práctica 6: Visión General 6-20 7

Uso de Operadores SET Objetivos 7-2 Operadores SET 7-3 Tablas Utilizadas en Esta Lección 7-4 Operador UNION 7-8 Uso del Operador UNION 7-9 Operador UNION ALL 7-11 Uso del Operador UNION ALL 7-12 Operador INTERSECT 7-13 Uso del Operador INTERSECT 7-14 Operador MINUS 7-15 Instrucciones para los Operadores SET 7-17 Oracle Server y los Operadores SET 7-18 Correspondencia de Sentencias 7-19 Correspondencia de la Sentencia: Ejemplo 7-20 Control del Orden de Filas 7-21 Resumen 7-23 Práctica 7: Visión General 7-24

Manipulación de Datos Objetivos 8-2 Lenguaje de Manipulación de Datos 8-3 Adición de una Nueva Fila a una Tabla 8-4 Sintaxis de la Sentencia INSERT 8-5 Inserción de Nuevas Filas 8-6 Inserción de Filas con Valores Nulos 8-7 Inserción de Valores Especiales 8-8 Inserción de Valores de Fecha Específicos 8-9 Creación de un Archivo de Comandos 8-10 Copia de Filas de Otra Tabla 8-11 Cambio de Datos en una Tabla 8-12 Sintaxis de la Sentencia UPDATE 8-13 Actualización de las Filas de una Tabla 8-14 Actualización de Dos Columnas con una Subconsulta 8-15 Actualización de Filas Basándose en Otra Tabla 8-16 Eliminación de una Fila de una Tabla 8-17 Sentencia DELETE 8-18 Supresión de Filas de una Tabla 8-19 Supresión de Filas Basándose en Otra Tabla 8-20 Sentencia TRUNCATE 8-21 Uso de Subconsultas en una Sentencia INSERT 8-22 Transacciones de la Base de Datos 8-24 Ventajas de las Sentencias COMMIT y ROLLBACK 8-26

vii

Control de Transacciones 8-27 Rollback de Cambios hasta un Marcador 8-28 Procesamiento de Transacciones Implícito 8-29 Estado de los Datos antes de COMMIT o ROLLBACK 8-31 Estado de los Datos después de COMMIT 8-32 Validación de Datos 8-33 Estado de los Datos después de ROLLBACK 8-34 Rollback de Nivel de Sentencia 8-36 Consistencia de Lectura 8-37 Implementación de la Consistencia de Lectura 8-38 Resumen 8-39 Práctica 8: Visión General 8-40 9

Uso de Sentencias DDL para Crear y Gestionar Tablas Objetivos 9-2 Objetos de Base de Datos 9-3 Reglas de Nomenclatura 9-4 La Sentencia CREATE TABLE 9-5 Referencia a Tablas de Otro Usuario 9-6 Opción DEFAULT 9-7 Creación de Tablas 9-8 Tipos de Datos 9-9 Tipos de Datos de Fecha/Hora 9-11 Tipo de Datos INTERVAL DAY TO SECOND 9-16 Inclusión de Restricciones 9-17 Instrucciones de Restricciones 9-18 Definición de Restricciones 9-19 Restricción NOT NULL 9-21 Restricción UNIQUE 9-22 Restricción PRIMARY KEY 9-24 Restricción FOREIGN KEY 9-25 Restricción FOREIGN KEY: Palabras Clave 9-27 Restricción CHECK 9-28 CREATE TABLE: Ejemplo 9-29 Violación de Restricciones 9-30 Creación de Tablas mediante una Subconsulta 9-32 Sentencia ALTER TABLE 9-34 Borrado de un Tabla 9-35 Resumen 9-36 Práctica 9: Visión General 9-37

10 Creación de Otros Objetos de Esquema Objetivos 10-2 Objetos de Base de Datos 10-3 ¿Qué Es una Vista? 10-4 Ventajas de las Vistas 10-5 Vistas Simples y Vistas Complejas 10-6 Creación de Vistas 10-7 Recuperación de Datos de una Vista 10-10 Modificación de Vistas 10-11 Creación de Vistas Complejas 10-12

viii

Reglas para Realizar Operaciones DML en Vistas 10-13 Uso de la Cláusula WITH CHECK OPTION 10-16 Denegación de Operaciones DML 10-17 Eliminación de Vistas 10-19 Práctica 10: Visión General de la Parte 1 10-20 Secuencias 10-21 Sentencia CREATE SEQUENCE: Sintaxis 10-23 Creación de Secuencias 10-24 Pseudocolumnas NEXTVAL y CURRVAL 10-25 Uso de Secuencias 10-27 Caché de Valores de Secuencia 10-28 Modificación de Secuencias 10-28 Instrucciones para Modificar una Secuencia 10-30 Índices 10-31 ¿Cómo se Crean los Índices? 10-33 Creación de Índices 10-34 Instrucciones para la Creación de Índices 10-35 Eliminación de Índices 10-36 Sinónimos 10-37 Creación y Eliminación de Sinónimos 10-39 Resumen 10-40 Práctica 10: Visión General de la Parte 2 10-41 11 Gestión de Objetos con Vistas de Diccionario de Datos Objetivos 11-2 El Diccionario de Datos 11-3 Estructura de los Diccionarios de Datos 11-4 Uso de las Vistas de Diccionario 11-6 Vista USER_OBJECTS 11-7 Información de Tabla 11-9 Información de Columna 11-10 Información de Restricción 11-12 Información de Vista 11-15 Información de Secuencia 11-16 Información de Sinónimos 11-18 Adición de Comentarios a Tablas 11-19 Resumen 11-20 Práctica 11: Visión General 11-21

A Soluciones a las Prácticas B Datos y Descripciones de Tablas C Sintaxis de Unión en Oracle D Uso de SQL*Plus Índice Prácticas Adicionales Prácticas Adicionales: Datos y Descripciones de Tablas Prácticas Adicionales: Soluciones

Prefacio

Perfil Antes de Empezar este Curso Antes de comenzar este curso, debe saber utilizar una GUI (Interfaz Gráfica de Usuario). El requisito es que esté familiarizado con los conceptos y las técnicas del procesamiento de datos. Organización de Este Curso Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I es un curso dirigido por un instructor que ofrece clases teóricas y ejercicios prácticos. Las demostraciones en línea y las sesiones prácticas por escrito sirven para reforzar los conceptos y las habilidades que se presentan en él.

Prefacio-3

Publicaciones Relacionadas Publicaciones Oracle Título

Número de Referencia

Oracle® Database Reference 10g Release 1 (10.1)

B10755-01

Oracle® Database SQL Reference 10g Release 1 (10.1) Oracle® Database Concepts 10g Release 1 (10.1)

B10759-01

B10743-01

Oracle® Database Application Developer's Guide - Fundamentals 10g Release 1 (10.1) B10795-01 SQL*Plus® User's Guide and Reference Publicaciones Adicionales •

Boletines de versión de sistema

•

Guías de usuario y de instalación

•

Archivos read.me

•

Artículos de IOUG (International Oracle User’s Group)

•

Oracle Magazine

Prefacio-4

B12170-01

Convenciones Tipográficas A continuación se incluyen dos listas de las convenciones tipográficas que se utilizan específicamente en el texto o en el código.

Convenciones Tipográficas del Texto Convención

Objeto o Término

Ejemplo

Mayúsculas

Comandos, funciones, nombres de columna, nombres de tabla, objetos PL/SQL, esquemas

Utilice el comando SELECT para ver la información almacenada en la columna LAST_NAME de la tabla. EMPLOYEES

Minúsculas, cursiva

Nombres de archivo, donde: role es el nombre del rol que variables de sintaxis, se va a crear. nombres de usuario, contraseñas

Mayúscula inicial Disparadores y nombres de botones

Asigne un disparador When-ValidateItem al bloque ORD. Haga clic en Cancelar.

Cursiva

Comillas

Libros, nombres de cursos y manuales, y palabras o frases a las que se quiere dar énfasis

Si desea más información acerca del tema, consulte Oracle SQL Reference Manual. No guarde los cambios en la base de datos.

Título de módulos de Este tema se trata en la Lección 3, lecciones que se “Trabajo con Objetos”. nombran en el curso

Prefacio-5

Convenciones Tipográficas (continuación) Convenciones Tipográficas del Código Convención

Objeto o Término

Ejemplo

Mayúsculas

Comandos, funciones

SELECT employee_id FROM employees;

Minúsculas, cursiva

Variables de la sintaxis CREATE ROLE role;

Mayúscula inicial

Disparadores de pantallas

Form module: ORD Trigger level: S_ITEM.QUANTITY item Trigger name: When-Validate-Item . . .

Minúsculas

Nombres de columna, nombres de tabla, nombres de archivo, objetos PL/SQL

. . . OG_ACTIVATE_LAYER (OG_GET_LAYER ('prod_pie_layer')) . . . SELECT last_name FROM employees;

Negrita

El texto que debe escribir el usuario

CREATE USER scott IDENTIFIED BY tiger;

Prefacio-6

Introducci贸n

Objetivos de la Lección Al finalizar esta lección, debería estar capacitado para: • Mostrar las funciones de Oracle10g • Analizar los aspectos teóricos y físicos de una base de datos relacional • Describir la implementación de Oracle de RDBMS y ORDBMS • Comprender los objetivos del curso

Objetivos En esta lección, conocerá RDBMS (sistema de gestión de bases de datos relacionales) y ORDBMS (sistema de gestión de bases de datos relacionales de objetos). También se le presentará: • Sentencias SQL específicas de Oracle • iSQL*Plus, que es un entorno utilizado para ejecutar sentencias SQL y por motivos de formato y de información

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-2

Objetivos del Curso Al finalizar este curso, debería estar capacitado para: • Identificar los componentes estructurales principales de la Base de Datos Oracle 10g • Recuperar datos de fila y de columna de las tablas con la sentencia SELECT • • •

•

Crear informes de datos ordenados y restringidos Utilizar funciones SQL para generar y recuperar datos personalizados Ejecutar sentencias DML (lenguaje de manipulación de datos) para actualizar datos en la Base de Datos Oracle 10g Obtener metadatos consultando las vistas de diccionario Copyright © 2004, Oracle. Todos los derechos reservados.

Objetivos del Curso Este curso le ofrece una introducción a la tecnología de Base de Datos Oracle 10g En esta clase, aprenderá los conceptos básicos de bases de datos relacionales y el potente lenguaje de programación SQL. Este curso proporciona los conocimientos de SQL esenciales que le permiten escribir sentencias en una sola tablas o en varias tablas, manipular datos en tablas, crear objetos de base de datos y consultar metadatos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-3

Oracle10g

Un proveedor

Escalabilidad

Fiabilidad

Modelo de desarrollo simple

Gestión unificada

Juegos de conocimientos comunes

Funciones de Oracle10g La versión Oracle10g ofrece una infraestructura exhaustiva de alto rendimiento, que incluye: • Escalabilidad de departamentos a ubicaciones e-business de empresa • Arquitectura sólida, fiable, disponible y segura • Un modelo de desarrollo; sencillas opciones de despliegue • Aproveche el juego de conocimientos actual de una organización en toda la plataforma Oracle (lo que incluye SQL, PL/SQL, Java y XML) • Una interfaz de gestión para todas las aplicaciones • Tecnologías estándar de la industria, sin bloqueo propietario Además de proporcionar las ventajas mencionadas anteriormente, la versión Oracle10g contiene la base de datos para la grid. Grid computing puede reducir drásticamente el costo del cálculo, ampliar la disponibilidad de los recursos de cálculo y ofrecer una productividad y una calidad más altas. La idea básica de grid computing es la noción de cálculo como un servicio, de forma análoga a la red eléctrica o a la red telefónica. Como cliente de la red (grid), no se preocupa de dónde están los datos o de dónde se realiza el cálculo. Lo que desea es que el cálculo se realice y que le entreguen la información cuando la necesite. Desde el punto de vista del servidor, la grid tiene que ver con la virtualización y el provisionamiento. Reúna todos los recursos y provisione estos recursos dinámicamente basándose en las necesidades de su negocio, con lo que conseguirá al mismo tiempo un mejor uso de los recursos. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-4

Oracle10g

Oracle10g Los tres productos de infraestructura de grid de la versión Oracle10g son: • Base de Datos Oracle 10g • Oracle Application Server 10g • Oracle Enterprise Manager 10g Grid Control

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-5

Base de Datos Oracle 10g Datos relacionales de objetos

Documentos

Multimedia

Mensajes

Base de Datos Oracle 10g La Base de Datos Oracle 10g está diseñada para almacenar y gestionar información de empresa. Permite reducir los costos de gestión y proporciona una alta calidad de servicio. La reducción en los requisitos de configuración y de gestión y el ajuste SQL automático han reducido de forma significativa el costo del mantenimiento del entorno. La Base de Datos Oracle 10g forma parte de los productos de infraestructura de grid de la versión Oracle 10g. "Grid computing" está relacionado con ver el cálculo como un servicio. Si es un cliente, no necesita saber dónde residen los datos y qué computadora los almacena. Debería ser capaz de solicitar información o cálculos de sus datos y que se los entreguen. La Base de Datos Oracle 10g gestiona todos sus datos. No se trata simplemente de los datos relacionales de objetos que se pueden esperar que gestione una base de datos de empresa. También pueden ser datos no estructurados como: • Hojas de cálculo • Documentos de Word • Presentaciones de PowerPoint • XML • Tipos de datos multimedia como MP3, gráficos, vídeo, etc. Ni siquiera es necesario que los datos estén en la base de datos. Base de Datos Oracle 10g dispone de servicios a través de los que puede almacenar metadatos sobre información almacenada en los sistemas de archivos. Puede utilizar el servidor de bases de datos para gestionar y servir información dondequiera que se encuentre. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-6

Oracle Application Server 10g Portales

Aplicaciones transaccionales

Análisis de negocio

Integración

Marco de desarrollo de aplicaciones

Servidor de aplicaciones

Oracle Application Server 10g Oracle Application Server 10g proporciona una plataforma completa de infraestructura para desarrollar y desplegar aplicaciones de empresa, integrando muchas funciones, lo que incluye un entorno de tiempo de ejecución de J2EE y de servicios Web, un portal de empresa, un broker de integración de empresa, inteligencia de negocio, almacenamiento en caché Web y servicios de gestión de identidad. Oracle Application Server 10g agrega nuevas funciones de grid computing, basándose en el éxito de Oracle9i Application Server, que cuenta con cientos de clientes que utilizan sus aplicaciones de empresa de producción. Oracle Application Server 10g es el único servidor de aplicaciones que incluye servicios para todas las diferentes aplicaciones de servidores que pueda necesitar utilizar, como: • Portales o direcciones Web • Aplicaciones transaccionales Java • Aplicaciones de inteligencia de negocio También proporciona integración entre usuarios, aplicaciones y datos en toda la organización.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-7

Oracle Enterprise Manager 10g Grid Control • •

Provisionamiento de software Control de nivel de servicio de aplicaciones

Oracle Enterprise Manager 10g Grid Control Oracle Enterprise Manager 10g Grid Control es la consola completa, integrada y central y el marco subyacente que automatiza las tareas administrativas ente juegos de sistemas en un entorno de grids. Con Oracle Grid Control, puede agrupar varios nodos de hardware, bases de datos, servidores de aplicaciones y otros destinos en entidades lógicas simples. Mediante la ejecución de trabajos, el forzado de políticas estándar, el control del rendimiento y la automatización de muchas otras tareas entre un grupo de destinos, en lugar de en muchos sistemas individualmente, Grid Control permite el escalado con una grid en crecimiento. Provisionamiento de Software Con Grid Control, Oracle 10g automatiza la instalación, la configuración y la clonación de Oracle Application Server 10g y Base de datos Oracle 10g entre varios nodos. Oracle Enterprise Manager proporciona un marco común para provisionamiento y gestión de software, lo que permite a los administradores crear, configurar, desplegar y utilizar nuevos servidores con nuevas instancias del servidor de aplicaciones y de la base de datos cuando sea necesario. Control de Nivel de Servicio de Aplicaciones Oracle Grid Control contempla la disponibilidad y el rendimiento de la infraestructura de grids como un todo unificado, tal y como lo experimentaría un usuario, y no como unidades aisladas de almacenamiento, recuadros de procesamiento, bases de datos y servidores de aplicaciones. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-8

Sistemas de Gestión de Bases de Datos Relacionales y Relacionales de Objetos • • • • •

Modelo relacional y modelo relacional de objetos Tipos de datos y objetos definidos por el usuario Totalmente compatible con bases de datos relacionales Soporte de multimedia y objetos grandes Funciones de servidor de base de datos de alta calidad

Acerca de Oracle Server Oracle Server soporta los modelos relacional y relacional de objetos. Oracle Server amplía las capacidades de creación de modelos de datos para soportar una base de datos relacional de objetos que proporciona una programación orientada a objetos, tipos de datos complejos, objetos de negocio complejos y compatibilidad plena con el mundo relacional. Incluye diversas funciones para un rendimiento y una funcionalidad superiores de aplicaciones OLTP (procesamiento de transacciones online), como un mejor uso compartido de estructuras de datos de tiempo de ejecución, mayores cachés de buffer y restricciones diferibles. Las aplicaciones de almacenes de datos se aprovechan de mejoras como la ejecución paralela de operaciones de inserción, actualización y supresión; partición; y optimización de consultas compatible con ejecución paralela. Al operar dentro del marco de NCA (arquitectura de cálculo de red), el modelo Oracle soporta las aplicaciones de cliente/servidor y basadas en Web que se distribuyen y son de varias capas. Para obtener más información sobre los modelos relacional y relacional de objetos, consulte el manual Database Concepts.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-9

Plataforma para Internet de Oracle Clientes

Cualquier Cualquier cliente de correo cliente FTP

Aplicaciones de Internet Lógica de negocios Lógica de presentación y datos y de negocios Bases de datos

Servidores de aplicaciones

Servicios de red

Herramientas de desarrollo

Gestión del sistema

Cualquier explorador

SQL PL/SQL Java

Plataforma para Internet de Oracle Para desarrollar una aplicación de e-commerce, necesita un producto que pueda almacenar los datos, un producto que pueda proporcionar un entorno de tiempo de ejecución para las aplicación que implementen la lógica de negocio y un producto que pueda controlar y diagnosticar la aplicación después de su integración. Los productos Oracle 10g que hemos analizado proporcionan todos los componentes necesarios para desarrollar su aplicación. Oracle ofrece una plataforma para Internet exhaustiva y de alto rendimiento para e-commerce y almacenes de datos. La plataforma para Internet de Oracle incluye todo lo necesario para desarrollar, desplegar y gestionar aplicaciones de Internet, incluidas estas tres partes fundamentales: • Clientes basados en explorador para procesar la presentación • Servidores de aplicaciones para ejecutar la lógica de negocio y servir la lógica de presentación para clientes basados en explorador • Bases de datos para ejecutar la lógica de negocio con uso intensivo de base de datos y servir datos Oracle ofrece una amplia variedad de las herramientas de desarrollo más avanzadas controladas por GUI (interfaz gráfica de usuario) para crear aplicaciones de negocio, así como una gran serie de aplicaciones de software para muchas áreas de negocio e industria. Oracle Developer Suite incluye herramientas para desarrollar formularios e informes y para crear almacenes de datos. Las funciones, los paquetes y los procedimientos almacenados se pueden escribir mediante SQL, PL/SQL o Java. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-10

Ciclo de Vida de Desarrollo del Sistema

Estrategia y análisis Diseño Creación y documentación Transición Producción

Ciclo de Vida de Desarrollo del Sistema Desde el concepto a la producción, puede desarrollar una base de datos mediante el ciclo de vida de desarrollo del sistema, que comprende varias etapas de desarrollo. Este enfoque descendente sistemático del desarrollo de la base de datos transforma los requisitos de información de negocio en una base de datos operativa. Fase de Estrategia y Análisis • Estudie y analice los requisitos de negocio. Entreviste a usuarios y gestores para identificar los requisitos de información. Incorpore la declaración de intenciones de la empresa y de la aplicación, así como las especificaciones de sistema para el futuro. • Cree modelos del sistema. Transfiera la descripción de negocio a una representación gráfica de las necesidades y reglas de información de negocio. Confirme y refine el modelo con analistas y expertos. Fase de Diseño Diseñe la base de datos basándose en el modelo desarrollado en la fase de estrategia y análisis. Fase de Creación y Documentación • Cree el prototipo del sistema. Escriba y ejecute los comandos para crear las tablas y los objetos de soporte para la base de datos. • Desarrolle documentación de usuario, texto de ayuda y manuales operativos para soportar el uso y el funcionamiento del sistema. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-11

Ciclo de Vida de Desarrollo del Sistema

Estrategia y análisis Diseño Creación y documentación Transición Producción

Ciclo de Vida de Desarrollo del Sistema (continuación) Fase de Transición Refine el prototipo. Lleve la aplicación a producción con pruebas de aceptación de usuarios, conversión de datos existentes y operaciones paralelas. Realice las modificaciones que estime oportunas. Fase de Producción Ponga el sistema a disposición de los usuarios. Ponga en marcha el sistema de producción. Controle su rendimiento y mejore y refine el sistema. Nota: Las diversas fases del ciclo de vida de desarrollo del sistema se pueden levar a cabo de forma iterativa. Este curso se centra en la fase de creación del ciclo de vida de desarrollo del sistema.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-12

Almacenamiento de Datos en Medios Diferentes

Hoja de cálculo electrónica

Armario de archivado

Base de datos

Almacenamiento de Información Todas las organizaciones tienen necesidades de información. Las bibliotecas mantienen una lista de miembros, libros, fechas límite y sanciones. Las compañías necesitan guardar información sobre empleados, departamentos y salarios. Estas informaciones reciben el nombre de datos. Las organizaciones pueden almacenar datos en diversos medios y en diferentes formatos, como un documento impreso en un armario de archivado o datos almacenados en hojas de cálculo electrónicas o en bases de datos. Una base de datos es una recopilación de información organizada. Para gestionar bases de datos, necesita un DBMS (sistema de gestión de bases de datos). Un DBMS es un programa que almacena, recupera y modifica los datos de la base de datos a petición. Existen cuatro tipos principales de base de datos: jerárquicas, de red, relacionales y (el más reciente) relacionales de objetos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-13

Concepto de Base de Datos Relacional • • •

El Dr. E. F. Codd propuso el modelo relacional para sistemas de bases de datos en 1970. Es la base de RDBMS (Sistema de gestión de bases de datos relacionales). El modelo relacional consta de: – Recopilación de objetos o relaciones – Juego de operadores para actuar sobre las relaciones – Integridad de datos para precisión y consistencia

Modelo Relacional Los principios del modelo relacional los perfiló por primera el Dr. E. F. Codd en un documento de 1970 titulado “A Relational Model of Data for Large Shared Data Banks” (“Un modelo de datos relacional para grandes bancos de datos compartidos”). En este documento, el Dr. Codd propuso el modelo relacional para los sistemas de bases de datos. Los modelos que se solían utilizar en esos tiempos eran el jerárquico y el de red o incluso simples estructuras de datos de archivos sin formato. Los RDBMS (sistemas de gestión de bases de datos relacionales) se hicieron populares rápidamente, especialmente por lo fáciles de utilizar que resultaban y por la flexibilidad de su estructura. Además, muchos proveedores innovadores, como Oracle, complementaron los RDBMS con una serie de productos de desarrollo de aplicaciones y de usuario, para ofrecer una solución total. Componentes del Modelo Relacional • Recopilaciones de objetos o relaciones que almacenan los datos • Un juego de operadores que pueden actuar en las relaciones para generar otras relaciones • Integridad de datos para precisión y consistencia Para obtener más información, consulte An Introduction to Database Systems, Eighth Edition (Addison-Wesley: 2004), de Chris Date.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-14

Definición de una Base de Datos Relacional Una base de datos relacional es una recopilación de relaciones o tablas bidimensionales. Oracle Server

Nombre de tabla: EMPLOYEES

…

Nombre de tabla: DEPARTMENTS

Definición de una Base de Datos Relacional Una base de datos relacional utiliza relaciones o tablas bidimensionales para almacenar la información. Por ejemplo, puede que necesite almacenar información sobre todos los empleados de la compañía. En una base de datos relacional, creará varias tablas para almacenar diferentes informaciones sobre los empleados, como una tabla de empleados, una de departamentos y una de salarios.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-15

Modelos de Datos

Modelo del sistema en la mente del cliente

Modelo de entidad del modelo del cliente Modelo de tabla del modelo de entidad

Oracle Server

Modelos de Datos Los modelos son una piedra angular del diseño. Los ingenieros crean un modelo de un coche para trabajar en los detalles antes de enviarlo a producción. Del mismo modo, los diseñadores de sistemas desarrollan modelos para explorar ideas y mejorar la comprensión del diseño de las base de datos. Objetivo de los Modelos Los modelos ayudan a comunicar los conceptos que tienen las personas en mente. Se pueden utilizar para: • Comunicar • Clasificar • Describir • Especificar • Investigar • Evolucionar • Analizar • Imitar EL objetivo es generar un modelo que se amolde a muchos de estos usos, resulte comprensible para el usuario final y contenga suficientes detalles para que un desarrollador cree un sistema de base de datos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-16

Modelo de Relación de Entidades •

Cree un diagrama de relaciones de entidades a partir de descripciones o especificaciones de negocio: EMPLOYEE #* número * nombre o cargo

•

DEPARTMENT #* número * nombre compuesto por o ubicación

asignado a

Supuesto – “... Asignar uno o más empleados a un departamento...” – “... Algunos departamentos no tienen aún empleados asignados...”

Modelo de ER En un sistema eficaz, los datos se dividen en categorías o entidades específicas. Un modelo ER (relación de entidades) es una ilustración de varias entidades en un negocio y las relaciones que hay entre ellas. Un modelo ER se deriva de especificaciones o descripciones de negocio y se crea durante la fase de análisis del ciclo de vida de desarrollo del sistema. Los modelos ER separan la información necesaria para un negocio de las actividades que se llevan a cabo en ese negocio. Aunque los negocios pueden cambiar de actividad, el tipo de información tiende a ser constante. Por lo tanto, las estructuras de datos también tienden a ser constantes.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-17

Modelo de ER (continuación) Ventajas del Modelo de ER • Documenta información para la organización en formato claro y preciso • Proporciona una imagen clara del ámbito de los requisitos de información • Proporciona un mapa gráfico comprensible del diseño de la base de datos • Ofrece un marco eficaz para integrar varias aplicaciones Componentes Clave • Entidad: Algo significativo sobre lo que es necesario conocer información. Ejemplos: departamento, empleados y pedidos. • Atributo: Algo que describe o cualifica una entidad. Por ejemplo, para la entidad empleado, los atributos serían número de empleado, nombre, cargo, fecha de contratación, número de departamento, etc. Cada uno de los atributos puede ser necesario u opcional. Este estado se conoce como opcionalidad. • Relación: Asociación específica entre entidades que muestra la opcionalidad y el grado. Ejemplos: empleados y departamentos, y pedidos y artículos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-18

Convenciones del Modelo de Relación de Entidades Atributo

Entidad • • • •

Nombre único, singular Mayúsculas Recuadro editable Sinónimo entre paréntesis EMPLOYEE #* número * nombre o cargo

• • • •

Nombre singular Minúsculas Obligatorio marcado con * Opcional marcado con “o”

DEPARTMENT #* número * nombre compuesto por o ubicación

asignado a

Convenciones del Modelo de ER Entidades Para representar una entidad en un modelo, utilice estas convenciones: • Nombre de entidad único, singular • Nombre de entidad en mayúsculas • Recuadro editable • Nombres de sinónimos opcionales en mayúsculas entre paréntesis: ( ) Atributos Para representar un atributo en un modelo, utilice estas convenciones: • Nombre singular en minúsculas • Asterisco (*) para atributos obligatorios (esto es, los valores que se deben conocer) • Letra “o” para atributos opcionales (esto es, los valores que se pueden conocer) Relaciones

Símbolo

Descripción

Línea con guiones Línea continua Pata de gallo

Elemento opcional que indica que “puede ser” Elemento obligatorio que indica que “debe ser” Elemento de grado que indica “uno o más”

Línea simple

Elemento de grado que indica “uno y sólo uno”

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-19

Convenciones del Modelo de Relación de Entidades Atributo

Entidad • • • •

Nombre único, singular Mayúsculas Recuadro editable Sinónimo entre paréntesis EMPLOYEE #* número * nombre o cargo

• • • •

Nombre singular Minúsculas Obligatorio marcado con * Opcional marcado con “o”

DEPARTMENT #* número * nombre compuesto por o ubicación

asignado a

Convenciones del Modelo de ER (continuación) Relaciones Cada dirección de la relación contiene: • Una etiqueta: por ejemplo, enseñado por o asignado a • Una opcionalidad: ya sea debe ser o puede ser • Un grado: ya sea uno y sólo uno o uno o más Nota: El término cardinalidad es sinónimo de grado. Cada entidad de origen {puede ser | debe ser} nombre de relación {uno y sólo uno | uno o más} entidad de destino. Nota: La convención es leer en el sentido de las agujas del reloj. Identificadores Únicos Un UID (identificador único) es una combinación de atributos o relaciones, o de ambas, que sirve para distinguir incidencias de una entidad. Cada incidencia de entidad debe ser identificable de forma única. • Etiquete cada atributo que forme parte del UID con una almohadilla: # • Etiquete los UID secundarios con una almohadilla entre paréntesis: (#)

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-20

Relación de Varias Tablas • •

Cada fila de datos de una tabla se identifica de manera única mediante una PK (clave primaria). Los datos de varias tablas se pueden relacionar lógicamente mediante FK (claves ajenas).

Nombre de tabla: EMPLOYEES

Nombre de tabla: DEPARTMENTS

… Clave primaria

Relación de Varias Tablas Cada tabla contiene datos que describen exactamente una entidad. Por ejemplo, la tabla EMPLOYEES contiene información sobre los empleados. Las categorías de datos se muestran a lo ancho de la parte superior de cada tabla, y los casos individuales, debajo. Mediante un formato de tabla, resultará fácil visualizar, comprender y utilizar la información. Como los datos sobre diferentes entidades se almacenan en tablas diferentes, puede que necesite combinar dos o más tablas para responder a una pregunta en particular. Por ejemplo, puede que necesite conocer la ubicación del departamento en el que trabaja un empleado. En este supuesto, necesita información de la tabla EMPLOYEES (que contiene datos sobre los empleados) y de la tabla DEPARTMENTS (que contiene datos sobre los departamentos). Con un RDBMS, puede relacionar los datos de una tabla con los de otra mediante las claves ajenas. Una clave ajena es una columna (o un juego de columnas) que hace referencia a una clave primaria en la misma tabla o en otra tabla. Puede utilizar la capacidad para relacionar datos de una tabla con datos de otra para organizar información en unidades separadas y manejables. Los datos de empleado se pueden mantener lógicamente diferenciados de los de departamento almacenándolos en una tabla aparte.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-21

Relación de Varias Tablas (continuación) Instrucciones para Claves Primarias y Claves Ajenas • No se pueden utilizar valores duplicados en una clave primaria. • Las claves primarias, por lo general, no se pueden cambiar. • Las claves ajenas se basan en valores de datos y son punteros puramente lógicos (no físicos). • Un valor de clave ajena debe corresponder exactamente con un valor de clave primaria existente o un valor de clave único, o bien ser nulo. • Una clave ajena debe hacer referencia a una columna de clave primaria o de clave única.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-22

Terminología de Bases de Datos Relacionales 2

4 5 6

Terminología Utilizada en una Base de Datos Relacional Una base de datos relacional puede contener una o varias tablas. Una tabla es la estructura de almacenamiento básica de un RDBMS. Las tablas contienen todos los datos necesarios sobre algo del mundo real, como empleados, facturas o clientes. La diapositiva muestra el contenido de la tabla, o relación, EMPLOYEES. Los números indican: 1. Una sola fila (o tupla) que representa todos los datos necesarios para un empleado en particular. Cada fila de una tabla se debe identificar con una clave primaria, que no permite filas duplicadas. El orden de las filas no es significativo; especifique el orden de filas al recuperar los datos. 2. Una columna, o atributo, que contiene el número de empleado. El número de empleador identifica un empleado único de la tabla EMPLOYEES. En este ejemplo, la columna de número de empleado está designada como clave primaria. Una clave primaria debe contener un valor y el valor debe ser único. 3. Una columna que no es un valor de clave. Una columna representa un tipo de datos en una tabla; en este ejemplo, los datos son los salarios de todos los empleados. El orden de las columnas no es significativo al almacenar datos; especifique el orden de las columnas al recuperar los datos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-23

Terminología Utilizada en una Base de Datos Relacional (continuación) 4. Una columna que contiene el número de departamento, que además es una clave ajena. Una clave ajena es una columna que define cómo se relacionan las tablas entre sí. Una clave ajena hace referencia a una clave primaria o a una clave única de la misma tabla o de otra tabla. En el ejemplo, DEPARTMENT_ID identifica de forma única un departamento de la tabla DEPARTMENTS. 5. Se puede encontrar un campo en la intersección de una fila y una columna. Sólo puede contener un valor. 6. Puede que en el campo no haya ningún valor. Esto se denomina valor nulo. En la tabla EMPLOYEES, sólo los empleados que tengan el rol de representante de ventas tienen un valor en el campo COMMISSION_PCT (comisión).

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-24

Propiedades de Bases de Datos Relacionales Base de datos relacional: • Se puede acceder a ella y se puede modificar ejecutando sentencias de SQL (lenguaje estructurado de consulta) • Contiene una recopilación de tablas sin punteros físicos • Utiliza un juego de operadores

Propiedades de una Base de Datos Relacional En una base de datos relacional, no se especifica la ruta de acceso a las tablas y no es necesario saber cómo se organizan los datos físicamente. Para acceder a la base de datos, ejecute una sentencia SQL (lenguaje estructurado de consulta), que es el lenguaje estándar ANSI (Instituto Nacional Americano de Normalización) para utilizar bases de datos relacionales. El lenguaje contiene un juego grande de operadores para particionar y combinar relaciones. La base de datos se puede modificar mediante las sentencias SQL.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-25

Comunicación con un RDBMS mediante SQL Se introduce la sentencia SQL. SELECT department_name FROM departments;

La sentencia se envía a Oracle Server. Oracle Server

Lenguaje Estructurado de Consulta Mediante SQL, se puede comunicar con Oracle Server. SQL tiene las siguientes ventajas: • Eficaz • Fácil de aprender y utilizar • Completo funcionalmente (Con SQL, se pueden definir, recuperar y manipular datos de las tablas.)

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-26

Sistema de Gestión de Bases de Datos Relacionales de Oracle Oracle server

Tablas de usuario

Diccionario de datos

Sistema de Gestión de Bases de Datos Relacionales de Oracle Oracle proporciona un RDBMS flexible denominado Base de Datos Oracle 10g. Mediante sus funciones, puede almacenar y gestionar datos con todas las ventajas de una estructura relacional más PL/SQL, un motor que le proporciona la capacidad de almacenar y ejecutar unidades de programa. Base de Datos Oracle 10g soporta también Java y XML. Oracle Server ofrece las opciones de recuperar datos basándose en técnicas de optimización. Incluye funciones de seguridad que controlan cómo se accede a una base de dato y cómo se utiliza. Otras funciones son la consistencia y la protección de datos mediante mecanismos de bloqueo. La versión Oracle10g proporciona un enfoque abierto, exhaustivo e integrado de gestión de la información. Oracle Server está formado por una base de datos Oracle y una instancia de Oracle Server. Cada vez que se inicia una base de datos, se asigna un SGA (área global del sistema) y se inician los procesos en segundo plano de Oracle. La SGA es un área de memoria que se utiliza para información de base de datos compartida por usuarios de bases de datos. La combinación de los procesos en segundo plano y los buffers de memoria se denomina instancia Oracle.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-27

Sentencias SQL SELECT INSERT UPDATE DELETE MERGE

DML (Lenguaje de Manipulación de Datos)

CREATE ALTER DROP RENAME TRUNCATE COMMENT

DDL (Lenguaje de Definición de Datos)

COMMIT ROLLBACK SAVEPOINT

Control de transacciones

GRANT REVOKE

DCL (Lenguaje de Control de Datos)

Sentencias SQL Oracle SQL cumple los estándares aceptados por la industria. Oracle Corporation asegura la futura compatibilidad con los estándares en evolución al implicar de forma activa a personal clave en los comités de normalización SQL. Los comités aceptados por la industria son ANSI (Instituto Nacional Americano de Normalización) e ISO (Organización Internacional de Normalización). Tanto ANSI como ISO aceptan SQL como lenguaje estándar para las bases de datos relacionales. Sentencia SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMENT COMMIT ROLLBACK SAVEPOINT GRANT REVOKE

Descripción Recupera datos de la base de datos, introduce nuevas filas, cambia filas existentes y elimina filas no deseadas de las tablas de la base de datos, respectivamente. Conocido colectivamente como DML (lenguaje de manipulación de datos). Configura, cambia y elimina estructuras de datos de tablas. Conocido colectivamente como DDL (lenguaje de definición de datos).

Gestiona los cambios realizados con las sentencias DML. Los cambios en los datos se pueden agrupar en transacciones lógicas. Otorga o quita derechos de acceso a la base de datos Oracle y las estructuras de su interior. Conocido colectivamente como DCL (lenguaje de control de datos). Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-28

Tablas Utilizadas en el Curso EMPLOYEES

DEPARTMENTS

JOB_GRADES

Tablas Utilizadas en el Curso En el curso, se utilizan las siguientes tablas: • Tabla EMPLOYEES: Ofrece detalles de todos los empleados • Tabla DEPARTMENTS: Ofrece detalles de todos los departamentos • Tabla JOB_GRADES: Ofrece detalles de salarios de varios grados Nota: La estructura y los datos de todas las tablas se proporcionan en el Apéndice B.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-29

Resumen • • •

•

Base de Datos Oracle 10g es la base de datos para grid computing. La base de datos se basa en el sistema de gestión de bases de datos relacionales. Las bases de datos relacionales están formadas por relaciones, se gestionan mediante operaciones relacionales y las rigen restricciones de integridad de datos. Con Oracle Server, puede almacenar y gestionar información mediante el lenguaje SQL y el motor PL/SQL.

Resumen Los sistemas de gestión de bases de datos relacionales están formados por objetos o relaciones. Las gestionan operaciones y las rigen restricciones de integridad de datos. Oracle Corporation genera productos y servicios para cubrir las necesidades de los RDBMS. Los productos principales son: • Base de Datos Oracle 10g, con la cual se almacena y se gestiona información mediante SQL • Oracle Application Server 10g, con el que se ejecutan todas las aplicaciones • Oracle Enterprise Manager 10g Grid Control, que se utiliza para gestionar y automatizar tareas administrativas entre juegos de sistemas de un entorno de grids SQL Oracle Server soporta SQL de estándar ANSI y contiene extensiones. SQL es el lenguaje que se utiliza para comunicarse con el servidor para acceder a datos, manipularlos y controlarlos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I I-30

Recuperaci贸n de Datos mediante la Sentencia SELECT de SQL

Objetivos Al finalizar esta lección, debería estar capacitado para: • Enumerar las capacidades de las sentencias SELECT de SQL • Ejecutar una sentencia SELECT básica •

Diferenciar entre sentencias SQL y comandos de iSQL*Plus

Objetivos Para extraer datos de la base de datos, debe utilizar la sentencia SELECT de SQL (Lenguaje Estructurado de Consulta). Puede que tenga que restringir las columnas que se van a mostrar. Esta lección describe todas las sentencias SQL necesarias para realizar estas acciones. Es posible que desee crear sentencias SELECT que se puedan utilizar más de una vez. Esta lección trata también el entorno iSQL*Plus, en el que se pueden ejecutar sentencias SQL.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-2

Capacidades de las Sentencias SELECT de SQL Proyección

Selección

Tabla 1

Tabla 1 Unión

Tabla 1

Capacidades de las Sentencias SELECT de SQL Las sentencias SELECT recuperan información de la base de datos. Con las sentencias SELECT puede utilizar estas capacidades: • Proyección: Seleccione las columnas de una tabla que se han devuelto mediante una consulta. Elija la cantidad de columnas que necesite. • Selección: Seleccione las filas de una tabla que se han devuelto mediante una consulta. Se pueden utilizar varios criterios para restringir las filas que se recuperarán. • Unión: Junte datos almacenados en diferentes tablas especificando el enlace que hay entre ellos. Las uniones SQL se describen más detalladamente en una lección posterior.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-3

Sentencia SELECT Básica

SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;

• •

SELECT identifica las columnas que se van a mostrar FROM identifica la tabla que contiene esas columnas

Sentencia SELECT Básica En su forma más sencilla, una sentencia SELECT debe incluir: • Una cláusula SELECT, que especifica las columnas que se van a mostrar • Una cláusula FROM, que identifica la tabla que contiene las columnas que se muestran en la cláusula SELECT En la sintaxis: SELECT es una lista de una o más columnas * selecciona todas las columnas DISTINCT suprime los duplicados column|expression selecciona la columna o la expresión especificadas alias proporciona a las columnas seleccionadas cabeceras diferentes FROM table especifica la tabla que contiene las columnas Nota: A lo largo de este curso, las expresiones palabra clave, cláusula y sentencia se utilizan como se indica a continuación: • Una palabra clave hace referencia a un elemento SQL individual. Por ejemplo, SELECT y FROM son palabras clave. • Una cláusula es una parte de una sentencia SQL. Por ejemplo, SELECT employee_id, last_name, ... es una cláusula. • Una sentencia es una combinación de dos o más cláusulas. Por ejemplo, SELECT * FROM employees es una sentencia SQL. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-4

Selección de Todas las Columnas

SELECT * FROM departments;

Selección de Todas las Columnas de Todas las Filas Puede mostrar todas las columnas de datos de una tabla si agrega un asterisco (*) después de la palabra clave SELECT. En el ejemplo de la diapositiva, la tabla DEPARTMENTS contiene cuatro columnas: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID y LOCATION_ID. La tabla contiene siete filas, una para cada departamento. También puede mostrar todas las columnas de la tabla enumerando todas las columnas después de la palabra clave SELECT. Por ejemplo, la siguiente sentencia SQL (como el ejemplo de la diapositiva) muestra todas las columnas y todas las filas de la tabla DEPARTMENTS: SELECT FROM

department_id, department_name, manager_id, location_id departments;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-5

Selección de Columnas Específicas

SELECT department_id, location_id FROM departments;

Selección de Columnas Específicas de Todas las Filas Puede utilizar la sentencia SELECT para mostrar columnas específicas de la tabla especificando los nombres de columna, separados por comas. El ejemplo de la diapositiva muestra todos los números de departamento y los números de ubicación de la tabla DEPARTMENTS. En la cláusula SELECT, especifique las columnas que desee, en el orden en que quiere que aparezcan en la salida. Por ejemplo, para que la ubicación se muestre antes del número de departamento de izquierda a derecha, utilice esta sentencia: SELECT location_id, department_id FROM departments;

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-6

Escritura de Sentencias SQL • • • • • •

•

Las sentencias SQL no son sensibles a mayúsculas/minúsculas. Las sentencias SQL pueden estar en una o más líneas. Las palabras clave no se pueden abreviar o dividir entre líneas. Las cláusulas se sitúan normalmente en líneas separadas. Se utilizan sangrados para mejorar la legibilidad. En iSQL*Plus, opcionalmente, las sentencias SQL pueden terminar en punto y coma (;). Los puntos y comas son obligatorios si ejecuta varias sentencias SQL. En SQL*plus, es obligatorio que todas las sentencias SQL terminen en punto y coma (;). Copyright © 2004, Oracle. Todos los derechos reservados.

Escritura de Sentencias SQL Mediante las sencillas reglas que se detallan a continuación, puede crear sentencias válidas que resulten fáciles de leer y de editar: • Las sentencias SQL no son sensibles a mayúsculas/minúsculas. (a menos que se indique que lo sean). • Las sentencias SQL se pueden introducir en una o en varias líneas. • Las palabras clave no se pueden dividir en líneas ni se pueden abreviar. • Las cláusulas se suelen colocar en líneas aparte para facilitar su lectura y su edición. • Se deben utilizar sangrados para facilitar la lectura del código. • Las palabras clave se suelen introducir en mayúsculas; el resto de palabras, como nombres y columnas, se introduce en minúsculas. Ejecución de Sentencias SQL Mediante iSQL*Plus, haga clic en el botón Execute para ejecutar el comando o los comandos de la ventana de edición. Mediante SQL*Plus, termine la sentencia SQL con un punto y coma y pulse la tecla Enter para ejecutar el comando.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-7

Valores por Defecto de Cabeceras de Columnas •

iSQL*Plus: – Alineación de cabecera por defecto: centrada – Visualización de cabecera por defecto: mayúsculas

•

SQL*Plus: – Las cabeceras de las columnas CHARACTER y DATE están alineadas a la izquierda – Las cabeceras de columnas NUMBER están alineadas a la derecha – Visualización de cabecera por defecto: mayúsculas

Valores por Defecto de Cabeceras de Columnas En iSQL*Plus, las cabeceras de columnas se muestran en mayúsculas y centradas. SELECT last_name, hire_date, salary FROM employees;

…

Puede sustituir la visualización de la cabecera de columna por un alias. Los alias de columna se describirán posteriormente en esta lección. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-8

Expresiones Aritméticas Cree expresiones con datos numéricos y de fecha utilizando operadores aritméticos. Operador

Descripción

Suma

Resta

Multiplicación

División

Expresiones Aritméticas Es posible que deba modificar el modo en que se muestran los datos o que desee realizar cálculos o consultar supuestos hipotéticos. Todo esto es posible mediante expresiones aritméticas. Una expresión aritmética puede contener nombres de columna, valores numéricos constantes y los operadores aritméticos. Operadores Aritméticos La diapositiva muestra los operadores aritméticos disponibles en SQL. Puede utilizar operadores aritméticos en cualquier cláusula de una sentencia SQL (excepto en la cláusula FROM). Nota: Con los tipos de datos DATE y TIMESTAMP, puede utilizar únicamente los operadores de suma y de resta.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-9

Uso de Operadores Aritméticos

SELECT last_name, salary, salary + 300 FROM employees;

…

Uso de Operadores Aritméticos El ejemplo de la diapositiva utiliza el operador de suma para calcular una aumento en el salario de 300 dólares para cada empleado. La diapositiva muestra también una columna SALARY+300 en la salida. Observe que la columna calculada resultante SALARY+300 no es una nueva columna de la tabla EMPLOYEES; sólo tiene propósitos de visualización. Por defecto, el nombre de una nueva columna proviene del cálculo que la generó (en este caso, salary+300). Nota: Oracle Server ignora los espacios en blanco que van antes y después del operador aritmético. Prioridad de Operadores Si un operador aritmético contiene más de un operador, la multiplicación y la división se evalúan en primer lugar. Si los operadores de una expresión tienen la misma prioridad, la evaluación se realiza de izquierda a derecha. Puede utilizar paréntesis para forzar a que la expresión que vaya entre paréntesis se evalúe primero. Reglas de Prioridad: • La multiplicación y la división tienen lugar antes que la suma y la resta. • Los operadores con la misma prioridad se calculan de izquierda a derecha. • Los paréntesis se utilizan para omitir la prioridad por defecto o para aclarar la sentencia. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-10

Prioridad de Operadores

SELECT last_name, salary, 12*salary+100 FROM employees;

… SELECT last_name, salary, 12*(salary+100) FROM employees;

…

Prioridad de Operadores (continuación) El primer ejemplo de la diapositiva muestra el apellido, el salario y la compensación anual de los empleados. Calcula la compensación anual multiplicando el salario mensual por 12 y sumando una bonificación única de 100 dólares. Observe que la multiplicación se realiza antes que la suma. Nota: Utilice paréntesis para reforzar el orden estándar de prioridad y para mejorar la claridad. Por ejemplo, la expresión de la diapositiva se puede escribir como (12*salary)+100 sin que cambie el resultado. Uso de Paréntesis Puede sustituir las reglas de prioridad mediante paréntesis para especificar el orden en que desea que se ejecuten los operadores. El segundo ejemplo de la diapositiva muestra el apellido, el salario y la compensación anual de los empleados. Calcula la compensación anual como se indica a continuación: suma una bonificación mensual de 100 dólares al salario mensual y multiplica el subtotal por 12. Debido a los paréntesis, la suma tiene prioridad sobre la multiplicación.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-11

Definición de un Valor Nulo •

Un valor nulo es aquel que no está disponible, no está asignado, es desconocido o no es aplicable. Un valor nulo no es lo mismo que un cero o un espacio en blanco.

•

SELECT last_name, job_id, salary, commission_pct FROM employees;

… …

Valores Nulos Si faltan valores en una fila para una columna en particular, se dice que el valor es nulo o que contiene un valor nulo. Un valor nulo es aquel que no está disponible, no está asignado, es desconocido o no es aplicable. Un valor nulo no es lo mismo que un cero o un espacio. El cero es un número y un espacio es un carácter. Las columnas de cualquier tipo de datos pueden contener valores nulos. Sin embargo, algunas restricciones (NOT NULL y PRIMARY KEY) impiden que se utilicen valores nulos en la columna. En la columna COMMISSION_PCT de la tabla EMPLOYEES, observe que sólo pueden ganar comisiones los directores de ventas o los representantes de ventas. Los demás empleados no están autorizados a ganar comisiones. Un valor nulo representa ese hecho.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-12

Valores Nulos en Expresiones Aritméticas Las expresiones aritméticas que contienen un valor nulo se evalúan como nulas. SELECT last_name, 12*salary*commission_pct FROM employees;

… …

Valores Nulos en Expresiones Aritméticas Si el valor de alguna columna de una expresión aritmética es nulo, el resultado es nulo. Por ejemplo, si intenta realizar una división por cero, recibirá un error. Sin embargo, si divide un número por un valor nulo, el resultado es un valor nulo o desconocido. En el ejemplo de la diapositiva, el empleado King no recibe comisiones. Como la columna COMMISSION_PCT de la expresión aritmética es nula, el resultado es nulo. Para obtener más información, consulte “Basic Elements of SQL” en SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-13

Definición de un Alias de Columna Un alias de columna: • Cambia el nombre de una cabecera de columna • Es útil para los cálculos • Sigue inmediatamente al nombre de columna (puede haber también una palabra clave AS opcional entre el nombre de columna y el alias) • Requiere comillas dobles si contiene espacios o caracteres especiales, o si es sensible a mayúsculas/minúsculas

Alias de Columna Al mostrar el resultado de una consulta, iSQL*Plus utiliza normalmente el nombre de la columna seleccionada como cabecera de columna. Esta cabecera puede no ser descriptiva y, por tanto, puede resultar difícil de entender. Puede cambiar una cabecera de columna mediante un alias. Especifique el alias después de la columna en la lista SELECT utilizando un espacio como separador. Por defecto, las cabeceras de alias aparecen en mayúsculas. Si el alias contiene espacios o caracteres especiales (como # o $) o si es sensible a mayúsculas/minúsculas, ponga el alias entre comillas dobles (" ").

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-14

Uso de Alias de Columna

SELECT last_name AS name, commission_pct comm FROM employees;

… SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees;

Alias de Columna (continuación) El primer ejemplo muestra los apellidos y los porcentajes de comisión de todos los empleados. Observe que la palabra clave opcional AS se ha utilizado antes del nombre de alias de columna. El resultado de la consulta es el mismo independientemente de que se utilice o no la palabra clave AS. Observe también que la sentencia SQL tiene los alias de columna, name y comm, en minúsculas, mientras que el resultado de la consulta muestra las cabeceras de columnas en mayúsculas. Como se mencionó en una diapositiva anterior, las cabeceras de columnas aparecen en mayúsculas por defecto. El segundo ejemplo muestra los apellidos y los salarios anuales de todos los empleados. Como Annual Salary contiene un espacio, se ha puesto entre comillas dobles. Observe que la cabecera de columna de la salida es exactamente igual que el alias de columna.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-15

Operador de Concatenación Un operador de concatenación: • Enlaza columnas o cadenas de caracteres con otras columnas • Se representa mediante dos barras verticales (||) • Crea una columna resultante que es una expresión de carácter SELECT FROM

last_name||job_id AS "Employees" employees;

Operador de Concatenación Puede enlazar columnas a otras columnas, expresiones aritméticas o valores constantes para crear una expresión de carácter mediante el operador de concatenación (||). Las columnas situadas en cualquiera de los lados del operador se combinan para crear una única columna de salida. En el ejemplo, LAST_NAME y JOB_ID están concatenadas y se les ha asignado el alias Employees. Observe que el apellido y el código de puesto del empleado están combinados para crear una única columna de salida. La palabra clave AS antes del nombre de alias facilita la lectura de la cláusula SELECT. Valores Nulos con el Operador de Concatenación Si se concatena un valor nulo con una cadena de caracteres, el resultado es una cadena de caracteres. LAST_NAME || NULL da como resultado LAST_NAME.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-16

Cadenas de Literales •

Un literal es un carácter, un número o una fecha que se ha incluido en la sentencia SELECT.

•

Los valores de literales de caracteres y fecha deben ir entre comillas simples. Las cadenas de literales se incluyen en cada fila devuelta.

•

Cadenas de Literales Un literal es un carácter, un número o una fecha que se ha incluido en la lista SELECT y que no es un nombre de columna ni un alias de columna. Se imprime con cada fila devuelta. Las cadenas de literales de texto sin formato se pueden incluir en el resultado de la consulta y se tratarán igual que una columna en la lista SELECT. Los literales de fecha y de caracteres deben ir entre comillas simples (' '); los literales de números no es necesario que vayan entre comillas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-17

Uso de Cadenas de Literales

SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

…

Cadenas de Literales (continuación) El ejemplo de la diapositiva muestra los apellidos y los códigos de puesto de todos los empleados. La columna tiene la cabecera Employee Details. Observe los espacios entre las comillas simples en la sentencia SELECT. Los espacios mejoran la legibilidad de la salida. En el ejemplo siguiente, el apellido y el salario de cada empleado se concatenan con un literal para dar más significado a las filas devueltas. SELECT last_name ||': 1 Month salary = '||salary Monthly FROM employees;

… Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-18

Operador de Comillas (q) Alternativo

• • •

Especifique su propio delimitador de comillas Puede seleccionar cualquier delimitador Aumentará la legibilidad y la facilidad de uso

SELECT department_name || q'[, it's assigned Manager Id: ]' || manager_id AS "Department and Manager" FROM departments;

…

Operador de Comillas (q) Alternativo Muchas sentencias SQL utilizan literales de caracteres en expresiones o condiciones. Si el literal contiene una comilla simple, puede utilizar el operador de comillas (q) y seleccionar su propio delimitador de comillas. Puede seleccionar cualquier delimitador apropiado, de un solo byte o multibyte, o cualquiera de los siguientes pares de caracteres: [ ], { }, ( ) o < >. En el ejemplo que se muestra, la cadena contiene comillas simples, lo que normalmente se interpreta como delimitador de una cadena de caracteres. Pero mediante el operador q, los corchetes [] se utilizan como delimitador de comillas. La cadena situada entre los delimitadores de corchetes se interpreta como cadena de literales.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-19

Filas Duplicadas La visualización por defecto de las consultas es todas las filas, incluidas las filas duplicadas. SELECT department_id FROM employees;

… SELECT DISTINCT department_id FROM employees;

Filas Duplicadas A menos que indique lo contrario, iSQL*Plus muestra los resultados de una consulta sin eliminar las filas duplicadas. El primer ejemplo de la diapositiva muestra todos los números de departamento de la tabla EMPLOYEES. Observe que los números de departamento están repetidos. Para eliminar filas duplicadas del resultado, incluya la palabra clave DISTINCT de la cláusula SELECT justo después de la palabra clave SELECT. En el segundo ejemplo de la diapositiva, la tabla EMPLOYEES realmente contiene 20 filas, pero sólo hay siete números de departamento únicos en la tabla. Puede especificar varias columnas después del cualificador DISTINCT. El cualificador DISTINCT afecta a todas las columnas seleccionadas y el resultado son todas las combinaciones distintas de las columnas. SELECT FROM

DISTINCT department_id, job_id employees;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-20

Interacción de SQL e iSQL*Plus

Sentencias SQL

Oracle server

Explorador de Internet Comandos de iSQL*Plus

Resultados de la consulta Informe con formato

Cliente

SQL e iSQL*Plus SQL es un lenguaje de comandos para la comunicación con Oracle Server desde cualquier herramienta o aplicación. Oracle SQL contiene muchas extensiones. iSQL*Plus es una herramienta de Oracle que reconoce y envía sentencias SQL a Oracle Server para su ejecución y contiene su propio lenguaje de comandos. Funciones de SQL • Lo pueden utilizar diferentes usuarios, incluidos los que tengan poca o ninguna experiencia en programación • Es un lenguaje no procedural • Es un lenguaje basado en el inglés Funciones de iSQL*Plus • Se accede a él desde un explorador • Acepta sentencias SQL • Proporciona edición online para modificar sentencias SQL • Controla los valores de entorno • Formatea los resultados de las consultas en informes básicos • Accede a bases de datos locales y remotas

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-21

Sentencias SQL frente a Comandos de iSQL*Plus SQL • Un lenguaje • Estándar ANSI • Las palabras clave no se pueden abreviar • Las sentencias manipulan datos y definiciones de tablas en la base de datos

iSQL*Plus • Un entorno • Propietario de Oracle • Las palabras clave se pueden abreviar • Los comandos no permiten la manipulación de valores en la base de datos • Se ejecuta en un explorador • Se carga centralmente; no es necesaria su implementación en cada máquina Comandos de SQL*Plus

Sentencias SQL

SQL e iSQL*Plus (continuación) La tabla siguiente compara SQL e iSQL*Plus: SQL

iSQL*Plus

Es un lenguaje de comunicación con Oracle Server para acceder a datos Se basa en SQL del estándar ANSI (Instituto Nacional Americano de Normalización) Recupera datos; manipula datos y definiciones de tablas en la base de datos No tiene carácter de continuidad

Reconoce sentencias SQL y las envía al servidor Es la interfaz propietaria de Oracle para ejecutar sentencias SQL No permite la manipulación de valores en la base de datos Utiliza un guión (–) como carácter de continuidad si el comando ocupa más de una línea Se puede abreviar Utiliza comandos para formatear datos

No se puede abreviar Utiliza funciones para realizar formateo

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-22

Visión General de iSQL*Plus Después de conectarse a iSQL*Plus, puede: • Describir estructuras de tablas • Introducir, ejecutar y editar sentencias SQL • Guardar o agregar sentencias SQL a archivos • Ejecutar o editar sentencias almacenadas en archivos de comandos guardados

iSQL*Plus iSQL*Plus es un entorno en el que puede: • Ejecutar sentencias SQL para recuperar, modificar, agregar y eliminar datos de la base de datos • Formatear, realizar cálculos, almacenar e imprimir resultados en forma de informes • Crear archivos de comandos para almacenar sentencias SQL para su uso repetido en el futuro Los comandos de iSQL*Plus se pueden dividir en estas categorías principales: Categoría Objetivo Entorno Afecta al comportamiento general de sentencias SQL para la sesión Formato Formatea los resultados de las consultas Manipulación Guarda sentencias en archivos de comandos de texto y ejecuta de archivos sentencias desde archivos de comando de texto Ejecución Envía sentencias SQL desde el explorador a Oracle Server Edición Modifica las sentencias SQL en la ventana Edit Interacción Le permite crear y transferir variables a sentencias SQL, imprimir valores de variables e imprimir mensajes en pantalla Otros Dispone de varios comandos para conectarse a la base de datos, manipular el entorno iSQL*Plus y mostrar definiciones de columna Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-23

Conexión a iSQL*Plus Desde el entorno del explorador:

Conexión a iSQL*Plus Para conectarse desde un entorno de explorador: 1. Inicie el explorador. 2. Introduzca la dirección URL del entorno iSQL*Plus. 3. En la página Login, introduzca valores adecuados en los campos Username, Password y Connect Identifier.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-24

Entorno iSQL*Plus 8

Entorno iSQL*Plus En el explorador, la página iSQL*Plus Workspace tiene varias áreas clave: 1. Cuadro de texto: Área en la que se escriben las sentencias SQL y los comandos de iSQL*Plus 2. Botón Execute: Haga clic en este botón para ejecutar las sentencias y los comandos del cuadro de texto 3. Botón Load Script: Abre un formulario en el que puede identificar una ruta de acceso y un nombre de archivo o una dirección URL que contiene comandos de SQL, PL/SQL o SQL*Plus para cargarlos en el cuadro de texto 4. Botón Save Script: Guarda el contenido del cuadro de texto en un archivo 5. Botón Cancel: Detiene la ejecución del comando del cuadro de texto 6. Botón Clear Screen: Haga clic en este botón para limpiar el texto del cuadro de texto 7. Icono Logout: Haga clic en este icono para dar por terminada la sesión de iSQL*Plus y volver a la página iSQL*Plus Login 8. Icono Preferences: Haga clic en este icono para cambiar la configuración de la interfaz, la configuración del sistema o la contraseña 9. Icono Help: Proporciona acceso a la documentación de ayuda de iSQL*Plus

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-25

Visualización de la Estructura de Tablas Utilice el comando de iSQL*Plus DESCRIBE para mostrar la estructura de una tabla: DESC[RIBE] tablename

Visualización de la Estructura de Tablas En iSQL*Plus, puede mostrar la estructura de una tabla mediante el comando DESCRIBE. El comando muestra los nombres de columna y los tipos de datos e indica si una columna debe contener datos (es decir, si la columna tiene una restricción NOT NULL). En la sintaxis, tablename es el nombre de cualquier tabla, vista o sinónimo existentes a los que pueda acceder el usuario.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-26

Visualización de la Estructura de Tablas

DESCRIBE employees

Visualización de la Estructura de Tablas (continuación) El ejemplo de la diapositiva muestra información sobre la estructura de la tabla DEPARTMENTS. En la visualización resultante, Null? indica que los valores para esta columna pueden ser desconocidos. NOT NULL indica que una columna debe contener datos. Type muestra el tipo de datos para una columna. Los tipos de datos se describen en esta tabla: Tipo de Datos NUMBER(p,s) VARCHAR2(s) DATE CHAR(s)

Descripción Valor numérico con un número máximo p de dígitos, con s números de dígitos a la derecha del separador decimal Valor de carácter de longitud variable de un tamaño máximo s Valor de fecha y hora entre el 1 de enero de 4712 a. C. y el 31 de diciembre de 9999 d. C. Valor de carácter de longitud fija de un tamaño máximo s

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-27

Interacción con Archivos de Comandos

SELECT last_name, hire_date, salary FROM employees;

Interacción con Archivos de Comandos Colocación de Sentencias y Comandos en un Archivo de Comandos de Texto Puede guardar comandos y sentencias desde el cuadro de texto en iSQL*Plus a un archivo de comandos de texto del modo siguiente: 1. Escriba la sentencia SQL en el cuadro de texto en iSQL*Plus. 2. Haga clic en el botón Save Script. Se abrirá el recuadro de diálogo Windows File Save. Identifique el nombre del archivo. La extensión por defecto será .uix. Puede cambiar el tipo de archivo a un archivo de texto o guardarlo como archivo .sql.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-28

Interacción con Archivos de Comandos

Interacción con Archivos de Comandos (continuación) En el ejemplo que se muestra, la sentencia SELECT de SQL escrita en el cuadro de texto se guarda en un archivo denominado emp_data.sql. Puede seleccionar el tipo de archivo, el nombre de archivo y la ubicación de donde desee guardar el archivo de comandos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-29

Interacción con Archivos de Comandos

Interacción con Archivos de Comandos (continuación) Uso de Sentencias y Comandos de un Archivo de Comandos en iSQL*Plus Puede utilizar comandos y sentencias guardados anteriormente de un archivo de comandos en iSQL*Plus del modo siguiente: 1. Haga clic en el botón Load Script. Se abrirá un formulario desde el que puede escribir el nombre del archivo o una dirección URL que contenga los comandos de SQL, PL/SQL o SQL*Plus que desee introducir en el cuadro de texto.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-30

Interacción con Archivos de Comandos

D:\TEMP\emp_data.sql

Interacción con Archivos de Comandos (continuación) 2. Introduzca el nombre y la ruta de acceso del archivo de comandos o la dirección URL. O puede hacer clic en el botón Browse para buscar el nombre y la ubicación del archivo de comandos. 3. Haga clic en el botón Load para llevar el contenido del archivo o de la dirección URL al recuadro de texto.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-31

Página History de iSQL*Plus 3

Ejecución de Sentencias Anteriores La página History de iSQL*Plus le permite ejecutar sentencias previamente ejecutadas en la sesión. La página History muestra las sentencias SQL y los comandos de iSQL*Plus ejecutados más recientemente. Para volver a ejecutar las sentencias: 1. Seleccione las sentencias que desea ejecutar. 2. Haga clic en el botón Load. Nota • Puede controlar el número de sentencias que se muestran en la página History con los valores de Preferences. • Puede suprimir sentencias seleccionadas haciendo clic en el botón Delete.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-32

Página History de iSQL*Plus

Ejecución de Sentencias Anteriores (continuación) 3. Vuelva a la página Workspace. 4. Haga clic en el botón Execute para ejecutar el comando que se ha cargado en el recuadro de texto.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-33

Definición de Preferencias de iSQL*Plus

2 3

Preferencias de iSQL*Plus • Puede definir preferencias para la sesión de iSQL*Plus haciendo clic en el icono Preferences. • Las preferencias se dividen en categorías. Puede definir preferencias para el formato de archivos de comandos, la ejecución de archivos de comandos y la administración de bases de datos, y puede cambiar la contraseña. • Al seleccionar una categoría de preferencias, se muestra un formulario que le permite definir las preferencias para dicha categoría.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-34

Definición de la Preferencia de Ubicación de Salida 2

Cambio de la Ubicación de Salida Puede enviar los resultados generados por una sentencia SQL o un comando de iSQL*Plus a la pantalla (valor por defecto), a un archivo o a otra ventana del explorador. En la página Preferences: 1. Seleccione una opción de Output Location. 2. Haga clic en el botón Apply.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-35

Resumen En esta lección ha aprendido a: • Escribir una sentencia SELECT que: – Devuelva todas las filas y columnas de una tabla – Devuelva columnas especificadas de una tabla – Utilice alias de columna para mostrar más cabeceras de columnas descriptivas

•

Utilice el entorno iSQL*Plus para escribir, guardar y ejecutar sentencias SQL y comandos de iSQL*Plus

SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;

Sentencia SELECT En esta lección, ha aprendido a recuperar datos de una tabla de base de datos con la sentencia SELECT. SELECT FROM

*|{[DISTINCT] column [alias],...} table;

En la sintaxis: SELECT * DISTINCT column|expression alias FROM table iSQL*Plus

es una lista de una o más columnas selecciona todas las columnas suprime los duplicados selecciona la columna o la expresión especificadas proporciona a las columnas seleccionadas cabeceras diferentes especifica la tabla que contiene las columnas

iSQL*Plus es un entorno de ejecución que se puede utilizar para enviar sentencias SQL al servidor de bases de datos para editar y guardar sentencias SQL. Las sentencias se pueden ejecutar desde el prompt SQL o desde un archivo de comandos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-36

Práctica 1: Visión General Esta práctica cubre los temas siguientes: • Selección de todos los datos de diferentes tablas • Descripción de la estructura de tablas • Realización de cálculos aritméticos y especificación de números de columna • Uso de iSQL*Plus

Práctica 1: Visión General Ésta es la primera de varias prácticas para este curso. Las soluciones (si las necesita) se pueden encontrar en el Apéndice A. En las prácticas se pretenden cubrir todos los temas que se presenten en las lecciones correspondientes. Anote esta ubicación para los archivos de práctica: E:\labs\\SQL1\labs Si se le pide que guarde algún archivo de práctica, guárdelos en esta ubicación. Para iniciar iSQL*Plus, inicie el explorador. Debe introducir una dirección URL para acceder a iSQL*Plus. La dirección URL necesita el nombre del host, que le proporcionará el instructor. Introduzca el comando siguiente, para sustituir el nombre del host por el valor que le proporcione el instructor: http://<HOSTNAME:5561>/isqlplus En las prácticas, puede haber ejercicios precedidos de las frases “Si le queda tiempo” o “Si desea afrontar un desafío mayor”. Trabaje en estos ejercicios únicamente si ha terminado todos los demás ejercicios en el tiempo asignado y desea afrontar un desafío mayor para sus habilidades. Realice las prácticas despacio y con precisión. Puede probar a guardar y ejecutar archivos de comando. Si tiene alguna pregunta en cualquier momento, pregúntele al instructor.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-37

Práctica 1 Parte 1 Ponga a prueba sus conocimientos: 1. Inicie una sesión en iSQL*Plus mediante el identificador de usuario y la contraseña que le proporcione el instructor. 2. Los comandos de iSQL*Plus acceden a la base de datos. Verdadero/Falso 3. La siguiente sentencia SELECT se ejecuta correctamente: SELECT last_name, job_id, salary AS Sal FROM employees; Verdadero/Falso 4. La siguiente sentencia SELECT se ejecuta correctamente: SELECT * FROM job_grades; Verdadero/Falso 5. Hay cuatro errores de codificación en esta sentencia. ¿Los puede identificar? SELECT sal x 12 FROM

employee_id, last_name ANNUAL SALARY employees;

Parte 2 Anote esta ubicación para los archivos de práctica: E:\labs\\SQL1\labs Si se le pide que guarde algún archivo de práctica, guárdelos en esta ubicación. Para iniciar iSQL*Plus, inicie el explorador. Debe introducir una dirección URL para acceder a iSQL*Plus. La dirección URL necesita el nombre del host, que le proporcionará el instructor. Introduzca el comando siguiente, para sustituir el nombre del host por el valor que le proporcione el instructor: http://<HOSTNAME:5561>/isqlplus Le han contratado como programador de SQL en Acme Corporation. Su primera tarea consiste en crear unos informes basándose en datos de las tablas de recursos humanos. 6. Su primera tarea consiste en determinar la estructura de la tabla DEPARTMENTS y de su contenido.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-38

Práctica 1 (continuación)

7. Debe determinar la estructura de la tabla EMPLOYEES.

El departamento de recursos humanos desea que cree una consulta para mostrar el apellido, el código de puesto, la fecha de contratación y el número de empleado de cada empleado, con el número de empleado en primer lugar. Proporcione el alias STARTDATE para la columna HIRE_DATE. Guarde la sentencia SQL en un archivo denominado lab_01_07.sql para poder transferir este archivo al departamento de recursos humanos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-39

Práctica 1 (continuación) 8. Pruebe la consulta del archivo lab_01_07.sql para asegurarse de que funciona correctamente.

…

9. El departamento de recursos humanos desea que cree una consulta para visualizar códigos de puesto únicos de la tabla EMPLOYEES.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-40

Práctica 1 (continuación) Parte 3 Si le queda tiempo, realice los siguientes ejercicios: 10. El departamento de recursos humanos desea cabeceras de columnas más descriptivas para su informe sobre los empleados. Copie la sentencia de lab_01_07.sql en el recuadro de texto de iSQL*Plus. Nombre las cabeceras de columna Emp #, Employee, Job y Hire Date, respectivamente. A continuación, vuelva a ejecutar la consulta.

… 11. El departamento de recursos humanos ha solicitado un informe de todos los empleados y de sus identificadores de puesto. Muestre el apellido concatenado con el identificador de puesto (separados por una coma y un espacio) y nombre la columna Employee and Title.

… Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-41

Práctica 1 (continuación) Si desea afrontar un desafío mayor, realice este ejercicio: 12. Para familiarizarse con los datos de la tabla EMPLOYEES, cree una consulta para mostrar todos los datos de esa tabla. Separe la salida de cada columna con una coma. Ponga al título de la columna el nombre THE_OUTPUT.

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 1-42

Restricci贸n y Ordenaci贸n de Datos

Objetivos Al finalizar esta lección, debería estar capacitado para: • Limitar las filas recuperadas por una consulta • Ordenar las filas recuperadas por una consulta • Utilizar la sustitución ampersand en iSQL*Plus para restringir y ordenar la salida en el tiempo de ejecución

Objetivos Al recuperar datos de la base de datos, puede que necesite: • Restringir las filas de los datos que se muestran • Especificar el orden en el que se muestran las filas Esta lección explica las sentencias SQL que se utilizan para realizar estas acciones.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-2

Limitación de Filas mediante una Selección

EMPLOYEES

… “recuperar todos los empleados del departamento 90”

Limitación de Filas mediante una Selección En el ejemplo de la diapositiva, suponga que desea mostrar todos los empleados del departamento 90. Las filas con un valor 90 en la columna DEPARTMENT_ID son las únicas que se devolverán. Este método de restricción es la base de la cláusula WHERE en SQL.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-3

Limitación de las Filas que se Seleccionan •

Restrinja las filas que se devuelven mediante la cláusula WHERE:

SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];

•

La cláusula WHERE sigue a la cláusula FROM.

Limitación de las Filas que se Seleccionan Puede restringir las filas que se devuelven desde la consulta mediante la cláusula WHERE. Una cláusula WHERE contiene una condición que se debe cumplir y sigue directamente a la cláusula FROM. Si la condición es verdadera, se devuelve la fila que cumple la condición. En la sintaxis: WHERE condition

restringe la consulta a las filas que cumplan una condición se compone de nombres de columna, expresiones, constantes y un operador de comparación La cláusula WHERE puede comparar valores en columnas, valores de literales, expresiones aritméticas o funciones. Consta de tres elementos: • Nombre de columna • Condición de comparación • Nombre de columna, constante o lista de valores

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-4

Uso de la Cláusula WHERE

SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;

Uso de la Cláusula WHERE En el ejemplo, la sentencia SELECT recupera el identificador de empleado, el nombre, el identificador de puesto y el número de departamento de todos los empleados que están en el departamento 90.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-5

Cadenas de Caracteres y Fechas • •

•

Las cadenas de caracteres y los valores de fecha van entre comillas simples. Los valores de caracteres son sensibles a mayúsculas/minúsculas y los valores de fecha son sensible al formato. El formato de fecha por defecto es DD-MON-RR.

SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Whalen' ;

Cadenas de Caracteres y Fechas Las cadenas de caracteres de la cláusula WHERE deben ir entre comillas simples (''). Sin embargo, las constantes numéricas no deben ir entre comillas simples. Todas las búsquedas de caracteres son sensibles a mayúsculas/minúsculas. En el ejemplo siguiente, no se devuelve ninguna fila porque la tabla EMPLOYEES almacena los apellidos en caracteres de mayúsculas/minúsculas mezclados. SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'WHALEN';

Las bases de datos Oracle almacenan fechas en un formato numérico interno, que representa el siglo, el año, el mes, el día, las horas, los minutos y los segundos. La visualización de fecha por defecto es DD-MON-RR. Nota: Para obtener detalles sobre el formato RR y sobre cómo cambiar el formato de fecha por defecto, consulte la lección siguiente.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-6

Condiciones de Comparación Operador

Significado

Igual que

Mayor que

Mayor o igual que

Menor que

Menor o igual que

Distinto de

BETWEEN ...AND...

Entre dos valores (ambos inclusive)

IN(set)

Se corresponde con cualquier valor de una lista

Se corresponde con un patrón de caracteres

IS NULL

Condiciones de Comparación Las condiciones de comparación se utilizan en condiciones que comparan una expresión con otro valor u otra expresión. Se utilizan en la cláusula WHERE en el siguiente formato: Sintaxis ... WHERE expr operator value

Ejemplo ... WHERE hire_date = '01-JAN-95' ... WHERE salary >= 6000 ... WHERE last_name = 'Smith'

No se puede utilizar un alias en la cláusula WHERE. Nota: Los símbolos != y ^= también pueden representar la condición no igual que.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-7

Uso de Condiciones de Comparación

SELECT last_name, salary FROM employees WHERE salary <= 3000 ;

Uso de Condiciones de Comparación En el ejemplo, la sentencia SELECT recupera el apellido y el salario de la tabla EMPLOYEES de cualquier empleado cuyo salario es menor o igual que 3.000 dólares. Observe que hay un valor explícito suministrado para la cláusula WHERE. El valor explícito 3000 se compara con el valor de salario de la columna SALARY de la tabla EMPLOYEES.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-8

Uso de la Condición BETWEEN Utilice la condición BETWEEN para mostrar filas basadas en un rango de valores: SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ; Límite inferior Límite superior

Uso de la Condición BETWEEN Puede mostrar filas basándose en un rango de valores mediante la condición de rango BETWEEN. El rango que especifique contiene un límite inferior y uno superior. La sentencia SELECT de la diapositiva devuelve filas de la tabla EMPLOYEES de cualquier empleado cuyo salario esté entre los 2.500 y los 3.500 dólares. Los valores que se especifican con la condición BETWEEN también se incluyen. Debe especificar el límite inferior en primer lugar. También puede utilizar la condición BETWEEN en valores de caracteres: SELECT last_name FROM employees WHERE last_name BETWEEN 'King' AND 'Smith';

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-9

Uso de la Condición IN Utilice la condición IN de miembro para comprobar los valores incluidos en una lista: SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);

Uso de la Condición IN Para probar valores en un juego de valores especificado, utilice la condición IN. La condición IN se conoce también como condición de miembro. El ejemplo de la diapositiva muestra los números de empleado, los apellidos, los salarios y los números de empleado de supervisor para todos los empleados cuyo número de empleado de supervisor sea 100, 101 ó 201. La condición IN se puede utilizar con cualquier tipo de datos. El ejemplo siguiente devuelve una fila de la tabla EMPLOYEES para cualquier empleado cuyo apellido se incluya en la lista de nombres de la cláusula WHERE: SELECT employee_id, manager_id, department_id FROM employees WHERE last_name IN ('Hartstein', 'Vargas');

Si los caracteres o las fechas se utilizan en la lista, deben ir entre comillas simples ('').

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-10

Uso de la Condición LIKE •

•

Utilice la condición LIKE para realizar búsquedas con comodines de valores válidos de cadenas de búsqueda. Las condiciones de búsqueda pueden contener literales de números o caracteres: – % denota cero o muchos caracteres. – _ denota un carácter.

SELECT FROM WHERE

first_name employees first_name LIKE 'S%' ;

Uso de la Condición LIKE Puede que no siempre sepa qué valor buscar exactamente. La condición LIKE permite seleccionar filas que se correspondan con un patrón de caracteres. La operación de correspondencia de patrones de caracteres se conoce como búsqueda con comodines. Se pueden utilizar dos símbolos para crear la cadena de búsqueda.

Símbolo

Descripción

Representa cualquier secuencia de cero o más caracteres

Representa cualquier carácter simple

La sentencia SELECT de la diapositiva devuelve el nombre de empleado de la tabla EMPLOYEES de cualquier empleado cuyo nombre empiece por S. Observe la S en mayúsculas. Los nombres que empiecen por s no se devolverán. La condición LIKE se puede utilizar como método abreviado para algunas comparaciones BETWEEN. El ejemplo siguiente muestra los apellidos y las fechas de contratación de todos los empleados que se hayan incorporado entre enero de 1995 y diciembre de 1995: SELECT last_name, hire_date FROM employees WHERE hire_date LIKE '%95'; Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-11

Uso de la Condición LIKE •

Puede combinar caracteres que se correspondan con un patrón:

SELECT last_name FROM employees WHERE last_name LIKE '_o%' ;

•

Puede utilizar el identificador ESCAPE para buscar los símbolos % y _ reales.

Combinación de Caracteres Comodín Los símbolos % y _ se pueden utilizar en cualquier combinación con caracteres de literales. El ejemplo de la diapositiva muestra los apellidos de todos los empleados cuyos apellidos tengan la letra o como segundo carácter. Opción ESCAPE Cuando necesite una correspondencia exacta de los propios caracteres % y _, utilice la opción ESCAPE. Esta opción especifica qué es el carácter de escape. Si desea buscar las cadenas que contienen ‘SA_’, puede utilizar esta sentencia SQL: SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';

La opción ESCAPE identifica la barra invertida (\) como carácter de escape. En el patrón, el carácter de escape va delante del carácter de subrayado (_). Esto provoca que Oracle Server interprete el carácter de subrayado literalmente. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-12

Uso de las Condiciones NULL Compruebe si hay valores nulos con el operador IS NULL. SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ;

Uso de las Condiciones NULL Las condiciones NULL son la condición IS NULL y la condición IS NOT NULL. La condición IS NULL comprueba si hay valores nulos. Un valor nulo significa que el valor no está disponible, no está asignado, es desconocido o no es aplicable. Por tanto, no puede probar con = porque un valor nulo no puede ser igual ni desigual a ningún valor. El ejemplo de la diapositiva recupera los apellidos y los supervisores de todos los empleados que no tienen supervisor. He aquí otro ejemplo: para mostrar el apellido, el identificador de puesto y la comisión de todos los empleados que no están autorizados a recibir comisión, utilice esta sentencia SQL: SELECT last_name, job_id, commission_pct FROM employees WHERE commission_pct IS NULL;

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-13

Condiciones Lógicas

Operador

Significado

AND

Devuelve TRUE si ambas condiciones componentes son verdaderas

Devuelve TRUE si alguna de las condiciones componentes es verdadera

NOT

Devuelve TRUE si la condición siguiente es falsa

Condiciones Lógicas Una condición lógica combina el resultado de dos condiciones componentes para crear un único resultado basándose en esas condiciones, o invierte el resultado de una sola condición. Se devuelve una fila sólo si el resultado global de la condición es verdadero. En SQL, hay disponibles tres operadores lógicos: • AND • OR • NOT Hasta ahora, todos los ejemplos han especificado únicamente una condición en la cláusula WHERE. Puede utilizar varias condiciones en una cláusula WHERE mediante los operadores AND y OR.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-14

Uso del Operador AND AND requiere que ambas condiciones sean verdaderas: SELECT FROM WHERE AND

employee_id, last_name, job_id, salary employees salary >=10000 job_id LIKE '%MAN%' ;

Uso del Operador AND En el ejemplo, ambas condiciones deben ser verdaderas para que se seleccione algún registro. Por tanto, sólo se seleccionan los empleados que tengan un cargo que contenga la cadena ‘MAN’ y que ganen 10.000 dólares o más. Todas las búsquedas de caracteres son sensibles a mayúsculas/minúsculas. No se devuelve ninguna fila si ‘MAN’ no está en mayúsculas. Las cadenas de caracteres deben ir entre comillas. Tabla de Verdad de AND La tabla siguiente muestra los resultados de la combinación de dos expresiones con AND:

AND TRUE FALSE NULL

TRUE TRUE FALSE NULL

FALSE FALSE FALSE FALSE

NULL NULL FALSE NULL

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-15

Uso del Operador OR OR requiere que una de las condiciones sea verdadera: SELECT FROM WHERE OR

employee_id, last_name, job_id, salary employees salary >= 10000 job_id LIKE '%MAN%' ;

Uso del Operador OR En el ejemplo, una de las condiciones debe ser verdadera para que se seleccione algún registro. Por tanto, se selecciona cualquier empleado que tenga un identificador de puesto que contenga la cadena ‘MAN’ o que gane 10.000 dólares o más. Tabla de Verdad de OR La tabla siguiente muestra los resultados de la combinación de dos expresiones con OR:

OR TRUE FALSE NULL

TRUE TRUE TRUE TRUE

FALSE TRUE FALSE NULL

NULL TRUE NULL NULL

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-16

Uso del Operador NOT

SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;

Uso del Operador NOT El ejemplo de la diapositiva muestra el apellido y el identificador de puesto de todos los empleados cuyo identificador de puesto no es IT_PROG, ST_CLERK o SA_REP. Tabla de Verdad de NOT La tabla siguiente muestra el resultado de aplicar el operador NOT a una condición:

NOT

TRUE FALSE

FALSE TRUE

NULL NULL

Nota: El operador NOT también se puede utilizar con otros operadores SQL, como BETWEEN, LIKE y NULL. ... ... ... ...

WHERE WHERE WHERE WHERE

job_id NOT IN ('AC_ACCOUNT', 'AD_VP') salary NOT BETWEEN 10000 AND 15000 last_name NOT LIKE '%A%' commission_pct IS NOT NULL

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-17

Reglas de Prioridad

Operador Significado 1

Operadores aritméticos

Operador de concatenación

Condiciones de comparación

IS [NOT] NULL, LIKE, [NOT] IN

[NOT] BETWEEN

Distinto de

Condición lógica NOT

Condición lógica AND

Condición lógica OR

Puede utilizar paréntesis para sustituir las reglas de prioridad.

Reglas de Prioridad Las reglas de prioridad determinan el orden en que se evalúan y se calculan las expresiones. La tabla muestra el orden de prioridad por defecto. Puede sustituir este orden por defecto poniendo entre paréntesis las expresiones que desee calcular primero.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-18

Reglas de Prioridad SELECT FROM WHERE OR AND

last_name, job_id, salary employees job_id = 'SA_REP' job_id = 'AD_PRES' salary > 15000;

SELECT FROM WHERE OR AND

last_name, job_id, salary employees (job_id = 'SA_REP' job_id = 'AD_PRES') salary > 15000;

1. Ejemplo de Prioridad y el Operador AND En este ejemplo, hay dos condiciones: • La primera condición es que el identificador de puesto sea AD_PRES y el salario sea mayor de 15.000 dólares. • La segunda condición es que el identificador de puesto sea SA_REP. Por tanto, la sentencia SELECT se interpreta así: “Seleccionar la fila si un empleado es presidente y gana más de 15.000 dólares o si el empleado es representante de ventas.” 2. Ejemplo del Uso de Paréntesis En este ejemplo, hay dos condiciones: • La primera condición es que el identificador de puesto sea AD_PRES o SA_REP. • La segunda condición es que el salario sea mayor de 15.000 dólares. Por tanto, la sentencia SELECT se interpreta así: “Seleccionar la fila si un empleado es presidente o representante de ventas y si el empleado gana más de 15.000 dólares.”

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-19

Uso de la Cláusula ORDER BY •

Para ordenar las filas recuperadas, utilice la cláusula ORDER BY: – ASC: orden ascendente, por defecto – DESC: orden descendente

•

La cláusula ORDER BY se coloca al final de la sentencia SELECT:

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;

Uso de la Cláusula ORDER BY El orden de las filas que se devuelven en el resultado de una consulta no está definido. Se puede utilizar la cláusula ORDER BY para ordenar las filas. Si utiliza la cláusula ORDER BY, debe ser la última cláusula de la sentencia SQL. Puede especificar una expresión, un alias o una posición de columna como condición de ordenación. Sintaxis SELECT FROM [WHERE [ORDER BY

expr table condition(s)] {column, expr, numeric_position} [ASC|DESC]];

En la sintaxis: ORDER BY especifica el orden en el que se muestran las filas recuperadas ASC ordena las filas en orden ascendente (ordenación por defecto) DESC ordena las filas en orden descendente Si no se utiliza la cláusula ORDER BY, el orden no se define y Oracle Server puede no recuperar las filas en el mismo orden para la misma consulta dos veces. Utilice la cláusula ORDER BY para mostrar las filas en un orden específico.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-20

Ordenación

•

Ordenación en orden descendente:

SELECT last_name, job_id, department_id, hire_date FROM employees 1 ORDER BY hire_date DESC;

•

Ordenación por alias de columna:

SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal ;

•

Ordenación por varias columnas:

SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;

Ordenación de Datos por Defecto El orden por defecto es ascendente: • Los valores por defecto se muestran con los valores más bajos en primer lugar (por ejemplo, de 1 a 999). • Los valores se muestran con el valor más antiguo en primero lugar (por ejemplo, 01ENE-92 antes que 01-ENE-95). • Los valores de caracteres se muestran por orden alfabético (por ejemplo, A al principio y Z al final). • Los valores nulos se muestran al final en las secuencias ascendentes y al principio en las descendentes. • Puede ordenar por una columna que no esté en la lista SELECT. Ejemplos 1. Para invertir el orden en que se muestran las filas, especifique la palabra clave DESC después del nombre de columna en la cláusula ORDER BY. El ejemplo de la diapositiva ordena el resultado por el empleado contratado más recientemente. 2. Puede utilizar un alias de columna en la cláusula ORDER BY. El ejemplo de la diapositiva ordena los datos por salario anual. 3. Puede ordenar resultados de consultas por más de una columna. El límite de ordenación es el número de columnas de la tabla en cuestión. En la cláusula ORDER BY, especifique las columnas y separe los nombres de columna mediante comas. Si desea invertir el orden de una columna, especifique DESC después de su nombre. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-21

Variables de Sustitución

... salary = ? … ... department_id = ? … ... last_name = ? ... Deseo consultar diferentes valores.

Variables de Sustitución Los ejemplos hasta ahora han sido codificados. En una aplicación finalizada, el usuario dispararía el informe y el informe se ejecutaría sin pedir al usuario que realice ninguna acción. El rango de datos lo predeterminaría le cláusula fija WHERE del archivo de comandos iSQL*Plus. Con iSQL*Plus, puede crear informes que pidan a los usuarios que suministren sus propios valores para restringir el rango de datos devueltos por variables de sustitución. Puede embeber variables de sustitución en un archivo de comandos o en una única sentencia SQL. Una variable se puede considerar un contenedor en el que los valores se almacenan temporalmente. Al ejecutarse la sentencia, el valor se sustituye.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-22

Variables de Sustitución •

Utilice las variables de sustitución de iSQL*Plus para: – Almacenar valores temporalmente con las sustituciones de ampersand simple (&) y ampersand doble (&&)

•

Utilice variables de sustitución para complementar: – Condiciones WHERE – Cláusulas ORDER BY – Expresiones de columna – Nombres de tablas – Sentencias SELECT completas

Variables de Sustitución (continuación) En iSQL*Plus, puede utilizar las variables de sustitución ampersand simple (&) para almacenar valores temporalmente. Puede predefinir variables mediante el comando DEFINE de iSQL*Plus. DEFINE crea y asigna un valor a una variable. Ejemplos de Rangos Restringidos de Datos • Información de cifras sólo para el trimestre actual o el rango de fechas especificado • Información sobre datos relevantes únicamente para el usuario que solicita el informe • Visualización de personal únicamente de un departamento dado Otros Efectos Interactivos Los efectos interactivos no se restringen a la interacción directa del usuario con la cláusula WHERE. Se pueden utilizar los mismos principios para alcanzar otros objetivos como, por ejemplo: • Obtener valores de entrada de un archivo y no de una persona • Transferir valores de una sentencia SQL a otra iSQL*Plus no soporta las comprobaciones de validación (excepto el tipo de datos) en la entrada de usuario.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-23

Uso de la Variable de Sustitución & Utilice una variable con un ampersand (&) como prefijo para pedir un valor al usuario: SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ;

Variable de Sustitución Ampersand Simple Al ejecutar un informe, los usuarios a menudo desean restringir los datos que se devuelven dinámicamente. iSQL*Plus suministra esta flexibilidad con variables de usuario. Utilice un ampersand (&) para identificar cada variable de la sentencia SQL. No es necesario que defina el valor de cada variable.

Notación

Descripción

&user_variable

Indica una variable en una sentencia SQL; si la variable no existe, iSQL*Plus pide al usuario un valor (iSQL*Plus descarta una nueva variable cuando la ha utilizado.)

El ejemplo de la diapositiva crea una variable de sustitución iSQL*Plus para un número de empleado. Al ejecutarse una sentencia, iSQL*Plus pide al usuario un número de empleado y muestra entonces el número de empleado, el apellido, el salario y el número de departamento de ese empleado. Con el ampersand simple, se hace la petición al usuario cada vez que se ejecuta el comando, si no existe la variable.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-24

Uso de la Variable de Sustitución &

101

1 2

Variable de Sustitución Ampersand Simple (continuación) Cuando iSQL*Plus detecta que la sentencia SQL contiene un ampersand, se le pide que introduzca un valor para la variable de sustitución especificada en la sentencia SQL. Tras introducir un valor y hacer clic en el botón Continue, se muestran los resultados en el área de salida de la sesión iSQL*Plus.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-25

Valores de Carácter y de Fecha con Variables de Sustitución Utilice comillas simples para los valores de fecha y de carácter: SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = '&job_title' ;

Especificación de Valores de Carácter y de Fecha con Variables de Sustitución En una cláusula WHERE, los valores de fecha y de carácter deben ir entre comillas simples. Se aplica la misma regla a las variables de sustitución. Ponga la variable entre comillas simples dentro de la propia sentencia SQL. La diapositiva muestra una consulta para recuperar los apellidos de empleado, los números de departamento y los salarios anuales de todos los empleados basándose en el valor del cargo de la variable de sustitución de iSQL*Plus.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-26

Especificación de Nombres de Columna, Expresiones y Texto SELECT employee_id, last_name, job_id,&column_name FROM employees WHERE &condition ORDER BY &order_column ;

salary

salary > 15000

last_name

Especificación de Nombres de Columna, Expresiones y Texto No sólo puede utilizar las variables de sustitución en la cláusula WHERE de una sentencia SQL, sino que también las puede utilizar para sustituir nombres de columna, expresiones o texto. Ejemplo El ejemplo de la diapositiva muestra el número de empleado, el apellido, el cargo y cualquier otra columna que especifique el usuario en tiempo de ejecución, de la tabla EMPLOYEES. Para cada variable de sustitución de la sentencia SELECT , se le pide que introduzca un valor y debe hacer clic en Continue para proseguir. Si no introduce un valor de sustitución, obtiene un error al ejecutar la sentencia anterior. Nota: Una variable de sustitución se puede utilizar en cualquier parte de la sentencia SELECT, excepto como primera palabra en el prompt de comandos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-27

Uso de la Variable de Sustitución && Utilice el ampersand doble (&&) si desea volver a utilizar el valor de variable sin pedir al usuario que realice una acción cada vez: SELECT employee_id, last_name, job_id, &&column_name FROM employees ORDER BY &column_name ;

Variable de Sustitución Ampersand Doble Puede utilizar la variable de sustitución ampersand doble (&&) si desea volver a utilizar el valor de variable sin pedir al usuario que realice una acción cada vez. El usuario verá el prompt para el valor sólo una vez. En el ejemplo de la diapositiva, se pide al usuario que proporcione el valor para la variable column_name sólo una vez. El valor que suministra el usuario (department_id) se utiliza tanto para visualización como para la ordenación de los datos. iSQL*Plus almacena el valor que se suministra mediante el comando DEFINE; lo vuelve a utilizar siempre que se haga referencia al nombre de variable. Cuando una variable de usuario está en su lugar, debe utilizar el comando UNDEFINE para suprimirlo de esta forma: UNDEFINE column_name

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-28

Uso del Comando DEFINE de iSQL*Plus • •

Utilice el comando DEFINE de iSQL*Plus para crear y asignar un valor a una variable. Utilice el comando UNDEFINE de iSQL*Plus para eliminar una variable.

DEFINE employee_num = 200 SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ; UNDEFINE employee_num

Uso del Comando DEFINE de iSQL*Plus El ejemplo que se muestra crea una variable de sustitución para un número de empleado mediante el comando DEFINE. En tiempo de ejecución, esto muestra el número de empleado, el apellido, el salario y el número de departamento de ese empleado. Como la variable se crea mediante un comando DEFINE de iSQL*Plus, no se pide al usuario que introduzca un valor para el número de empleado. En vez de eso, se sustituye automáticamente el valor de variable definido en la sentencia SELECT. La variable de sustitución EMPLOYEE_NUM está presente en la sesión hasta que el usuario anule la definición o salga de la sesión iSQL*Plus.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-29

Uso del Comando VERIFY Utilice el comando VERIFY para alternar la visualización de la variable de sustitución, tanto antes como después de que iSQL*Plus reemplace con valores las variables de sustitución: SET VERIFY ON SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num;

old new

3: WHERE 3: WHERE

employee_id = &employee_num employee_id = 200

Uso del Comando VERIFY Para confirmar los cambios en la sentencia SQL, utilice el comando VERIFY de iSQL*Plus. Al definir SET VERIFY ON se fuerza a que iSQL*Plus muestre el texto de un comando antes y después de reemplazar con valores las variables de sustitución. El ejemplo de la diapositiva muestra los valores antiguo y nuevo de la columna EMPLOYEE_ID. Variables de Sistema de iSQL*Plus iSQL*Plus utiliza diversas variables de sistema que controlan el entorno de trabajo. Una de esas variables es VERIFY. Para obtener una lista completa de todas las variables de sistema, puede emitir el comando SHOW ALL.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-30

Resumen En esta lección ha aprendido a: • Utilizar la cláusula WHERE para restringir filas de salida: – Utilizar las condiciones de comparación – Utilizar las condiciones BETWEEN, IN, LIKE y NULL – Aplicar los operadores AND, OR y NOT

•

Utilizar la cláusula ORDER BY para ordenar filas de salida:

SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]] ;

•

Resumen En esta lección, ha aprendido a restringir y ordenar las filas que se devuelven con una sentencia SELECT. También ha aprendido a implementar diversos operadores y condiciones. Mediante las variables de sustitución de iSQL*Plus, puede agregar flexibilidad a las sentencias SQL. Puede consultar a los usuarios en tiempo de ejecución y permitir que especifiquen criterios.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-31

Práctica 2: Visión General Esta práctica cubre los temas siguientes: • Selección de datos y cambio del orden de las filas que se muestran • Restricción de filas mediante la cláusula WHERE • Ordenación de filas mediante la cláusula ORDER BY •

Uso de variables de sustitución para agregar flexibilidad a las sentencias SELECT de SQL

Práctica 2: Visión General En esta práctica, creará más informes, que incluirán sentencias que utilicen las cláusulas WHERE y ORDER BY. Haga que las sentencias SQL sean más reutilizables y genéricas incluyendo la sustitución ampersand.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-32

Práctica 2 El departamento de recursos humanos necesita su ayuda para crear algunas consultas. 1. Por motivos presupuestarios, el departamento de recursos humanos necesita un informe que muestre los apellidos y el salario de los empleados que ganen más de 12.000 dólares. Guarde la sentencia SQL en un archivo denominado lab_02_01.sql. Ejecute la consulta.

2. Cree un informe que muestre el apellido del empleado y el número de departamento del empleado número 176.

3. El departamento de recursos humanos necesita buscar los empleados con salarios altos y bajos. Modifique lab_02_01.sql para mostrar el apellido y el salario de cualquier empleado cuyo salario no está en el rango de 5.000 a 12.000 dólares. Guarde la sentencia SQL en un archivo de texto denominado lab_02_03.sql.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-33

Práctica 2 (continuación) 4. Cree un informe para mostrar el apellido, el identificador de puesto y la fecha de inicio para los empleados con los apellidos Matos y Taylor. Ordene la consulta por orden ascendente por fecha de inicio.

5. Muestre el apellido y el número de departamento de todos los empleados de los departamentos 20 y 50 en orden alfabético ascendente por nombre.

6. Modifique lab_02_03.sql para enumerar el apellido y el salario de los empleados que ganan entre 5.000 y 12.000 dólares y están en el departamento 20 ó 50. Etiquete las columnas como Employee y Monthly Salary, respectivamente. Vuelva a guardar lab_02_03.sql como lab_02_06.sql. Ejecute la sentencia en lab_02_06.sql.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-34

Práctica 2 (continuación) 7. El departamento de recursos humanos necesita un informe que muestre el apellido y la fecha de contratación de todos los empleados contratados en 1994.

8. Cree un informe que muestre el apellido y el cargo de todos los empleados que no tengan supervisor.

9. Cree un informe que muestre el apellido, el salario y la comisión de todos los empleados que ganen comisiones. Ordene los datos en orden descendente por salario y comisiones.

10. Los miembros del departamento de recursos humanos desean tener más flexibilidad con las consultas que está creando. Quieren un informe que muestre el apellido y el salario de empleados que ganen más que una cantidad que el usuario especificará tras un prompt. (Puede utilizar la consulta que creó en el ejercicio de la Práctica 1 y modificarla.) Guarde esta consulta en un archivo denominado lab_02_10.sql. Si introduce 12.000 cuando aparezca el prompt, el informe mostrará estos resultados:

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-35

Pr谩ctica 2 (continuaci贸n) 11. El departamento de recursos humanos quiere ejecutar informes basados en un supervisor. Cree una consulta que pida al usuario un identificador de supervisor y genere el identificador de empleado, el apellido, el salario y el departamento de los empleados de ese supervisor. El departamento de recursos humanos quiere poder ordenar el informe por una columna seleccionada. Puede probar los datos con estos valores: manager ID = 103, ordenado por apellido de empleado:

manager ID = 201, ordenado por salario:

manager ID = 124, ordenado por identificador de empleado:

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-36

Práctica 2 (continuación) Si le queda tiempo, realice los siguientes ejercicios: 12. Muestre el apellido de todos los empleados cuya tercera letra sea la a.

13. Muestre el apellido de todos los empleados que tengan tanto una a como una e en su apellido.

Si desea afrontar un desafío mayor, realice estos ejercicios: 14. Muestre el apellido, el puesto de trabajo y el salario de todos los empleados que sean representante de ventas o administrativo y cuyo salario sea distinto de 2.500, 3.500 ó 7.000 dólares.

15. Modifique lab_02_06.sql para mostrar el apellido, el salario y la comisión de todos los empleados cuyo importe de comisión sea del 20 %. Vuelva a guardar lab_02_ 06.sql como lab_02_15.sql. Vuelva a ejecutar la sentencia en lab_02_15.sql.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-37

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 2-38

Uso de Funciones de Una Sola Fila para Personalizar la Salida

Objetivos Al finalizar esta lección, debería estar capacitado para: • Describir varios tipos de funciones que hay disponibles en SQL • Utilizar funciones de carácter, numéricas y de fecha en sentencias SELECT •

Describir el uso de las funciones de conversión

Objetivos Las funciones hacen que el bloque de consulta sea más potente y se utilizan para manipular valores de datos. Ésta es la primera de dos lecciones que examinan las funciones. Se centra en las funciones de una sola fila de carácter, numéricas y de fecha, así como en las que convierten datos de tipo a otro (por ejemplo, conversión de datos de carácter a datos numéricos).

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-2

Funciones SQL

Entrada

Salida Función La función realiza una acción

arg 1 arg 2

Valor resultante

arg n

Funciones SQL Las funciones son una característica muy potente de SQL. Se pueden utilizar para: • Realizar cálculos en datos • Modificar elementos de datos individuales • Manipular la salida para grupos de filas • Formatear fechas y números para su visualización • Convertir tipos de datos de columnas Las funciones SQL a veces toman argumentos y siempre devuelven un valor. Nota: En su mayor parte, las funciones que se describen en esta lección son específicas de la versión Oracle de SQL.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-3

Dos Tipos de Funciones SQL

Funciones

Funciones de una sola fila

Funciones de varias filas

Devuelven un resultado por fila

Devuelven un resultado por juego de filas

Funciones SQL (continuación) Hay dos tipos de funciones: • Funciones de una sola fila • Funciones de varias filas Funciones de una Sola Fila Estas funciones operan sólo en filas únicas y devuelven un resultado por fila. Hay diferentes tipos de funciones de una sola fila. Esta lección trata las siguientes: • De carácter • Numéricas • De fecha • De conversión • Generales Funciones de Varias Filas Las funciones pueden manipular grupos de filas para dar un resultado por grupo de filas. Estas funciones se conocen también como funciones de grupo (y se describen en una lección posterior). Nota: Para obtener más información y una lista completa de las funciones disponibles y su sintaxis, consulte Oracle SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-4

Funciones de una Sola Fila Las funciones de una sola fila: • Manipulan elementos de datos • Aceptan argumentos y devuelven un valor • Actúan en cada fila que se devuelve • Devuelven un resultado por fila • Pueden modificar el tipo de datos • Se pueden anidar • Aceptan argumentos que pueden ser una columna o una expresión function_name [(arg1, arg2,...)]

Funciones de una Sola Fila Las funciones de una sola fila se utilizan para manipular elementos de datos. Aceptan uno o más argumentos y devuelven un valor para cada fila que devuelve la consulta. Un argumento puede ser uno de los siguientes: • Constante proporcionada por el usuario • Valor de variable • Nombre de columna • Expresión Las características de las funciones de una sola fila son: • Actúan en cada fila que se devuelve en la consulta • Devuelven un resultado por fila • Posiblemente devuelven un valor de datos de un tipo diferente a aquel al que se hace referencia • Posiblemente esperan uno o más argumentos • Se pueden utilizar en cláusulas SELECT, WHERE y ORDER BY; se pueden anidar En la sintaxis: function_name es el nombre de la función arg1, arg2 es cualquier argumento que vaya a utilizar la función. Se puede representar con un nombre de columna o una expresión.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-5

Funciones de una Sola Fila

De carácter

Generales

Funciones de una sola fila

De conversión

Numéricas

De fecha

Funciones de una Sola Fila (continuación) Esta lección trata estas funciones de una sola fila: • Funciones de carácter: Αceptan la entrada de caracteres y pueden devolver valores de carácter y numéricos • Funciones numéricas: Aceptan la entrada de números y devuelven valores numéricos • Funciones de fecha: Operan en valores del tipo de datos DATE (todas las funciones de fecha devuelven un valor del tipo de datos DATE excepto la función MONTHS_BETWEEN, que devuelve un número.) • Funciones de conversión: Convierten un valor de un tipo de datos en otro. • Funciones Generales: - NVL - NVL2 - NULLIF - COALESCE - CASE - DECODE

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-6

Funciones de Carácter

Funciones de carácter

Funciones de manipulación de mayúsculas/minúsculas LOWER UPPER INITCAP

Funciones de manipulación de caracteres CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE

Funciones de Carácter Las funciones de carácter de una sola fila aceptan datos de caracteres como entrada y pueden devolver valores de carácter y numéricos. Las funciones de carácter se pueden dividir en: • Funciones de manipulación de mayúsculas/minúsculas • Funciones de manipulación de caracteres Función

Objetivo

LOWER(column|expression)

Convierte los valores de carácter alfabéticos a minúsculas

UPPER(column|expression)

Convierte los valores de carácter alfabéticos a mayúsculas

INITCAP(column|expression)

Convierte los valores de carácter alfabéticos a mayúsculas para la primera letra de cada palabra; el resto en minúsculas

CONCAT(column1|expression1, Concatena el primer valor de carácter con el segundo; column2|expression2) equivalente al operador de concatenación (||) SUBSTR(column|expression, m[,n])

Devuelve los caracteres especificados del calor de carácter empezando por la posición de carácter m, con n caracteres de longitud (Si m es negativo, el recuento se inicia desde el final del valor de carácter. Si se omite n, se devuelven todos los caracteres hasta el final de la cadena.)

Nota: Las funciones analizadas en esta lección únicamente suponen una parte de las disponibles. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-7

Funciones de Carácter (continuación) Función

Objetivo

LENGTH(column|expression)

Devuelve el número de caracteres de la expresión

INSTR(column|expression, ’string’, [,m], [n] )

Devuelve la posición numérica de una cadena especificada. Opcionalmente, puede proporcionar una posición m para iniciar la búsqueda y la incidencia n de la cadena. m y n tienen por defecto el valor 1, lo que significa iniciar la búsqueda al principio de la búsqueda e informar de la primera incidencia. Rellena el valor de carácter justificado a la derecha hasta un ancho total de n posiciones de carácter. Rellena el valor de carácter justificado a la izquierda hasta un ancho total de n posiciones de carácter. Le permite recortar caracteres de cabecera o finales (o ambos) de una cadena de caracteres. Si trim_character o trim_source son literales de carácter, los debe poner entre comillas simples. Esto está disponible en Oracle8i y versiones posteriores. Busca en una expresión de texto una cadena de caracteres y, de encontrarla, la sustituye por una cadena de sustitución especificada

LPAD(column|expression, n, 'string') RPAD(column|expression, n, 'string') TRIM(leading|trailing|both, trim_character FROM trim_source)

REPLACE(text, search_string, replacement_string)

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-8

Funciones de Manipulación de Mayúsculas/Minúsculas Estas funciones convierten en mayúsculas y minúsculas las cadenas de caracteres: Función LOWER('SQL Course')

Resultado sql course

UPPER('SQL Course')

SQL COURSE

INITCAP('SQL Course')

Sql Course

Funciones de Manipulación de Mayúsculas/Minúsculas LOWER, UPPER e INITCAP son las tres funciones de conversión de mayúsculas/minúsculas. • LOWER: Convierte cadenas de caracteres con mayúsculas/minúsculas mezcladas o mayúsculas a minúsculas • UPPER: Convierte cadenas de caracteres con mayúsculas/minúsculas mezcladas o minúsculas a mayúsculas • INITCAP: Convierte la primera letra de cada palabra a mayúsculas y el resto de las letras a minúsculas SELECT 'The job id for '||UPPER(last_name)||' is ' ||LOWER(job_id) AS "EMPLOYEE DETAILS" FROM employees;

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-9

Uso de Funciones de Manipulación de Mayúsculas/Minúsculas Muestre el número de empleado, nombre y número de departamento del empleado Higgins: SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'higgins'; no rows selected SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'higgins';

Uso de Funciones de Manipulación de Mayúsculas/Minúsculas La diapositiva muestra el número de empleado, nombre y número de departamento del empleado Higgins. La cláusula WHERE de la primera sentencia SQL especifica el nombre de empleado como higgins. Como todos los datos de la tabla EMPLOYEES se almacenan con las mayúsculas y minúsculas adecuadas, el nombre higgins no encuentra correspondencia en la tabla y no se selecciona ninguna fila. La cláusula WHERE de la segunda sentencia SQL especifica que el nombre de empleado de la tabla EMPLOYEES se compare con higgins, con lo que convierte la columna LAST_NAME a minúsculas por motivos de comparación. Como ambos nombres están ahora en minúsculas, se encuentra una correspondencia y se selecciona una fila. La cláusula WHERE se puede reescribir del modo siguiente para crear el mismo resultado: ...WHERE last_name = 'Higgins'

El nombre de la salida aparece como se almacenó en la base de datos. Para mostrar el nombre sólo con la primera letra en mayúsculas, utilice la función UPPER en la sentencia SELECT. SELECT employee_id, UPPER(last_name), department_id FROM employees WHERE INITCAP(last_name) = 'Higgins';

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-10

Funciones de Manipulación de Caracteres Estas funciones manipulan cadenas de caracteres: Función CONCAT('Hello', 'World')

Resultado HelloWorld

SUBSTR('HelloWorld',1,5)

Hello

LENGTH('HelloWorld')

INSTR('HelloWorld', 'W')

LPAD(salary,10,'*')

*****24000

RPAD(salary, 10, '*')

24000*****

REPLACE ('JACK and JUE','J','BL')

BLACK and BLUE

TRIM('H' FROM 'HelloWorld')

elloWorld

Funciones de Manipulación de Caracteres CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD y TRIM son las funciones de manipulación de caracteres que se tratan en esta lección. • CONCAT: Une valores (el uso de parámetros con CONCAT se limita a dos.) • SUBSTR: Extrae una cadena de una longitud determinada • LENGTH: Muestra la longitud de una cadena como valor numérico • INSTR: Busca la posición numérica de un carácter especificado • LPAD: Rellena el valor de carácter justificado a la derecha • RPAD: Rellena el valor de carácter justificado a la izquierda • TRIM: Recorta caracteres iniciales o finales (o ambos) de una cadena de caracteres (Si trim_character o trim_source es un literal de caracteres, debe ponerlo entre comillas simples.) Nota: Puede utilizar funciones como UPPER y LOWER con la sustitución ampersand. Por ejemplo, utilice UPPER('&job_title') para que el usuario no tenga que introducir el cargo en un modo específico de mayúsculas/minúsculas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-11

Uso de Funciones de Manipulación de Caracteres 1 SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(job_id, 4) = 'REP';

2 3

Uso de Funciones de Manipulación de Caracteres El ejemplo de la diapositiva muestra los nombres y los apellidos de los empleados unidos, la longitud del apellido de empleado y la posición numérica de la letra a en el apellido del empleado para todos los empleados que incluyan la cadena REP en la cuarta posición del identificador de puesto. Ejemplo Modifique la sentencia SQL de la diapositiva para que muestre los datos para los empleados cuyos apellidos terminen en n. SELECT LENGTH FROM WHERE

employee_id, CONCAT(first_name, last_name) NAME, (last_name), INSTR(last_name, 'a') "Contains 'a'?" employees SUBSTR(last_name, -1, 1) = 'n';

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-12

Funciones Numéricas • • •

ROUND: Redondea el valor al decimal especificado TRUNC: Trunca el valor al decimal especificado MOD: Devuelve el resto de la división

Función

Resultado

ROUND(45.926, 2)

45.93

TRUNC(45.926, 2)

45.92

MOD(1600, 300)

100

Funciones Numéricas Las funciones numéricas aceptan la entrada de números y devuelven valores numéricos. Esta sección describe algunas de las funciones numéricas.

Función ROUND(column|expression, n)

TRUNC(column|expression, n)

MOD(m,n)

Objetivo Redondea la columna, la expresión o el valor a n posiciones decimales o, si se omite n, a ninguna (Si n es negativo, se redondean los números a la izquierda del separador decimal.).) Trunca la columna, la expresión o el valor a n posiciones decimales o, si se omite n, opta por el valor por defecto de cero Devuelve el resto de m dividido por n

Nota: Esta función contiene sólo algunas de las funciones numéricas disponibles. Para obtener más información, consulte “Number Functions” en Oracle SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-13

Uso de la Función ROUND 1

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;

DUAL es una tabla ficticia que puede utilizar para ver los resultados de funciones y cálculos.

Función ROUND La función ROUND redondea la columna, la expresión o el valor a n posiciones decimales. Si el segundo argumento es 0 o falta, el valor se redondea a cero posiciones decimales. Si el segundo argumento es 2, el valor se redondea a dos posiciones decimales. A la inversa, si el segundo argumento es –2, el valor se redondea a dos posiciones decimales a la izquierda (redondeando a la unidad más cercana a 10). La función ROUND también se puede utilizar con funciones de fecha. Podrá ver ejemplos más adelante en esta misma lección. Tabla DUAL La tabla DUAL es propiedad del usuario SYS y pueden acceder a ella todos los usuarios. Contiene una columna, DUMMY, y una fila con el valor X. La tabla DUAL resulta útil si desea devolver un valor sólo una vez (por ejemplo, el valor de una constante, una pseudocolumna o una expresión que no se deriva de una tabla con datos de usuario). La tabla DUAL se utiliza generalmente para la integridad de sintaxis de la cláusula SELECT, ya que las sentencias SELECT y FROM son obligatorias y varios cálculos no necesitan seleccionar de tablas reales.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-14

Uso de la Función TRUNC 1

SELECT ROUND(45.923,2), ROUND(45.923), ROUND(45.923,-1) FROM DUAL;

Función TRUNC La función TRUNC trunca la columna, la expresión o el valor a n posiciones decimales. La función TRUNC trabaja con argumentos parecidos a los de la función ROUND. Si el segundo argumento es 0 o falta, el valor se trunca a cero posiciones decimales. Si el segundo argumento es 2, el valor se trunca a dos posiciones decimales. A la inversa, si el segundo argumento es –2, el valor se trunca a dos posiciones decimales a la izquierda. Si el segundo argumento es –1, el valor se trunca a una posición decimal a la izquierda. Como la función ROUND, la función TRUNC también se puede utilizar con funciones de fecha.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-15

Uso de la Función MOD Para todos los empleados con cargo de representante de ventas, calcule el resto del salario una vez dividido por 5.000. SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP';

Función MOD La función MOD busca el resto del primer argumento dividido por el segundo argumento. El ejemplo de la diapositiva calcula el resto del salario después de dividirlo por 5.000 para todos los empleados cuyo identificador de puesto es SA_REP. Nota: La función MOD se utiliza a menudo para determinar si un valor es par o impar.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-16

Trabajo con Fechas • •

La base de datos Oracle almacena fechas en un formato numérico interno: siglo, año, mes, día, horas, minutos y segundos. El formato de visualización de fecha por defecto es DD-MON-RR. – Le permite especificar fechas del siglo XXI en el siglo XX especificando sólo los dos últimos dígitos del año – Le permite almacenar fechas del siglo XX en el siglo XXI de la misma manera

SELECT last_name, hire_date FROM employees WHERE hire_date < '01-FEB-88';

Formato de Fecha de Oracle La base de datos Oracle almacena las fechas en un formato numérico interno, que representa el siglo, el año, el mes, el día, las horas, los minutos y los segundos. El formato de visualización y de entrada por defecto para cualquier fecha es DD-MON-RR. Las fechas válidas de Oracle van del 1 de enero de 4712 a.C. al 31 de diciembre de 9999 d.C. En el ejemplo de la diapositiva, la salida de la columna HIRE_DATE se muestra en el formato por defecto DD-MON-RR. Sin embargo, las fechas no se almacenan en la base de datos en este formato. Se almacenan todos los componentes de fecha y hora. Así pues, aunque una fecha HIRE_DATE como 17-JUN-87 se muestra como día, mes y año, también hay información de hora y de siglo asociada a la fecha. Los datos completos podrían ser 17 de junio de 1987, 5:10:43 p.m.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-17

Formato de Fecha de Oracle (continuación) Esta fecha se almacena internamente así: SIGLO AÑO MES DÍA HORA MINUTO SEGUNDO 19 87 06 17 17 10 43 Siglos y el Año 2000 Al insertar un registro con una columna de fecha en una tabla, la información de siglo se selecciona en la función SYSDATE. Sin embargo, cuando la columna de fecha se muestra en la pantalla, el componente de siglo no se muestra (por defecto). El tipo de datos DATE almacena siempre la información de año como número de cuatro dígitos internamente: dos dígitos para el siglo y dos para el año. Por ejemplo, la base de datos Oracle almacena el año como 1987 ó 2004, y no simplemente como 87 ó 04.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-18

Trabajo con Fechas SYSDATE es una función que devuelve: • •

Fecha Hora

Función SYSDATE SYSDATE es una función de fecha que devuelve la fecha y la hora actuales del servidor de bases de datos. Puede utilizar SYSDATE igual que cualquier otro nombre de columna. Por ejemplo, puede mostrar la fecha actual seleccionando SYSDATE en una tabla. Se suele seleccionar SYSDATE en una tabla ficticia denominada DUAL. Ejemplo Muestre la fecha actual mediante la tabla DUAL. SELECT SYSDATE FROM DUAL;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-19

Aritmética de Fechas • • •

Sume o reste un número a una fecha para obtener un valor de fecha resultante. Reste dos fechas para calcular el número de días entre dichas fechas. Sume horas a una fecha dividiendo el número de horas entre 24.

Aritmética de Fechas Como la base de datos almacena fechas como números, puede realizar cálculos mediante operadores aritméticos como la suma o la resta. Puede sumar y restar constantes numéricas además de fechas. Puede utilizar las siguientes operaciones:

Operación

Resultado

Descripción

fecha + número

Fecha

Suma un número de días a una fecha

fecha – número

Fecha

Resta un número de días a una fecha

fecha – fecha

Número de días:

Resta una fecha a otra

fecha + número/24

Fecha

Suma un número de horas a una fecha

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-20

Uso de Operadores Aritméticos con Fechas SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90 ;

Aritmética de Fechas (continuación) El ejemplo de la diapositiva muestra el apellido y el número de semanas de empleo para todos los empleados del departamento 90. Resta la fecha en la que se contrató al empleado de la fecha actual (SYSDATE) y divide el resultado por 7 para calcular el número de semanas que un trabajador lleva empleado. Nota: SYSDATE es una función SQL que devuelve la fecha y la hora actuales. Sus resultados pueden diferir de los del ejemplo. Si se resta una fecha más actual a una más antigua, la diferencia será un número negativo.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-21

Funciones de Fecha

Función MONTHS_BETWEEN

Resultado

ADD_MONTHS

Agrega meses de calendario a una fecha

NEXT_DAY LAST_DAY

Día siguiente a la fecha especificada

ROUND

Redondea la fecha

TRUNC

Trunca la fecha

Número de meses entre dos fechas

Último día del mes

Funciones de Fecha Las funciones de fechas operan en fechas de Oracle. Todas las funciones de fecha devuelven un valor del tipo de datos DATE excepto MONTHS_BETWEEN, que devuelve un valor numérico. • MONTHS_BETWEEN(date1, date2): Busca el número de meses entre date1 y date2. El resultado puede ser positivo o negativo. Si date1 es posterior a date2, el resultado es positivo; si date1 es anterior a date2, el resultado es negativo. La parte no entera del resultado representa una porción del mes. • ADD_MONTHS(date, n): Agrega un número n de meses de calendario a date. El valor de n debe ser un entero y puede ser negativo. • NEXT_DAY(date, 'char'): Busca la fecha del siguiente día de la semana especificado ('char') después de date. El valor de char puede ser un número que represente un día o una cadena de caracteres. • LAST_DAY(date): Busca la fecha del último día del mes que contiene date • ROUND(date[,'fmt']): Devuelve date redondeado a la unidad especificada por el modelo de formato fmt. Si se omite el modelo de formato fmt, date se redondeará al día más cercano. • TRUNC(date[, 'fmt']): Devuelve date con la porción de tiempo del día truncada a la unidad especificada por el modelo de formato fmt. Si se omite el modelo de formato fmt, date se trunca al día más cercano. Esta lista es un subjuego de las funciones de fecha disponibles. Los modelos de formato se describirán posteriormente en esta lección. Ejemplos de modelos de formato son el mes y el año. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-22

Uso de Funciones de Fecha

Función

Resultado

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')

19.6774194

ADD_MONTHS ('11-JAN-94',6)

'11-JUL-94'

NEXT_DAY

('01-SEP-95','FRIDAY')

'08-SEP-95'

LAST_DAY

('01-FEB-95')

'28-FEB-95'

Funciones de Fecha (continuación) Por ejemplo, muestre el número de empleado, la fecha de contratación, el número de meses de empleo, la fecha de revisión semestral, el primer viernes tras la fecha de contratación y el mes de contratación de todos los empleados que lleven contratados menos de 36 meses. SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) TENURE, ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY(hire_date) FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 36;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-23

Uso de Funciones de Fecha Supongamos que SYSDATE = '25-JUL-03': Función ROUND(SYSDATE,'MONTH')

Resultado 01-AUG-03

ROUND(SYSDATE ,'YEAR')

01-JAN-04

TRUNC(SYSDATE ,'MONTH') TRUNC(SYSDATE ,'YEAR')

01-JUL-03 01-JAN-03

Funciones de Fecha (continuación) Las funciones ROUND y TRUNC se pueden utilizar para valores numéricos y de fecha. Cuando se utilizan con fechas, estas funciones redondean o truncan al modelo de formato especificado. Por tanto, puede redondear fechas al año o al mes más cercanos. Ejemplo Compare las fechas de contratación de todos los empleados que empezaron en 1997. Muestre el número de empleado, la fecha de contratación y el mes de inicio mediante las funciones ROUND y TRUNC. SELECT employee_id, hire_date, ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH') FROM employees WHERE hire_date LIKE '%97';

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-24

Práctica 3: Visión General de la Parte 1 Esta práctica cubre los temas siguientes: • Escritura de una consulta que muestre la fecha actual • Creación de consultas que requieren el uno de funciones numéricas, de carácter y de fecha • Realización de cálculos de años y meses de servicio de un empleado

Práctica 3: Visión General de la Parte 1 La práctica de la Parte 1 de esta lección proporciona varios ejercicios que utilizan diferentes funciones disponibles para los tipos de datos de carácter, numérico y de fecha. Para la Parte 1, conteste a las preguntas 1–6 al final de la lección.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-25

Funciones de Conversión

Conversión de tipos de datos

Conversión de tipos de datos implícita

Conversión de tipos de datos explícita

Funciones de Conversión Además de los tipos de datos Oracle, las columnas de tablas de una base de datos Oracle se pueden definir mediante tipos de datos ANSI, DB2 y SQL/DS. Sin embargo Oracle Server convierte internamente esos tipos de datos a tipos de datos Oracle. En algunos casos, Oracle Server utiliza datos de un tipo de datos donde espera datos de un tipo de datos diferente. Cuando sucede esto, Oracle Server puede convertir automáticamente los datos al tipo de datos esperado. Esta conversión de tipos de datos lo puede realizar implícitamente Oracle Server, o explícitamente el usuario. Las conversiones de tipos de datos implícitas funcionan de acuerdo con las reglas que se explican en las dos próximas diapositivas. Las conversiones de tipos de datos explícitas se realizan mediante las funciones de conversión. Las funciones de conversión convierten un valor de un tipo de dato a otro. Generalmente, la forma de los nombres de función utiliza la convención data type TO data type. El primer tipo de datos es el de entrada; el segundo, el de salida. Nota: Aunque está disponible la conversión de tipos de datos implícita, se recomienda que realice una conversión de tipos de datos explícita para asegurar la fiabilidad de las sentencias SQL.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-26

Conversión de Tipos de Datos Implícita Para las asignaciones, Oracle Server puede convertir automáticamente: De VARCHAR2 o CHAR

A NUMBER

VARCHAR2 o CHAR

DATE

NUMBER

VARCHAR2

DATE

VARCHAR2

Conversión de Tipos de Datos Implícita La asignación es correcta si Oracle Server puede convertir el tipo de datos del valor utilizado en la asignación al de destino. Por ejemplo, la expresión hire_date > '01-JAN-90' da como resultado la conversión implícita de la cadena '01-JAN-90' a una fecha.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-27

Conversión de Tipos de Datos Implícita Para la evaluación de expresiones, Oracle Server puede convertir automáticamente: De VARCHAR2 o CHAR

A NUMBER

VARCHAR2 o CHAR

DATE

Conversión de Tipos de Datos Implícita (continuación) En general, Oracle Server utiliza la regla para expresiones cuando se necesita una conversión de tipo de datos en lugares no cubiertos por una regla para conversiones de asignación. Por ejemplo, la expresión salary = '20000' da como resultado la conversión implícita de la cadena '20000' al número 20000. Nota: Las conversiones de CHAR a NUMBER sólo son correctas si la cadena de caracteres representa un número válido.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-28

Conversión de Tipos de Datos Explícita TO_NUMBER

NUMBER

TO_DATE

CHARACTER

TO_CHAR

DATE

TO_CHAR

Conversión de Tipos de Datos Explícita SQL proporciona tres funciones para convertir un valor de un tipo de datos a otro.

Función

Objetivo

TO_CHAR(number|date,[ fmt], Convierte un valor numérico o de fecha a una [nlsparams]) cadena de caracteres VARCHAR2 con el modelo de formato fmt Conversión numérica: El parámetro nlsparams especifica los siguientes caracteres, que son devueltos por elementos de formato numérico: • Carácter decimal • Separador de grupos • Símbolo de divisa local • Símbolo de divisa internacional Si se omite nlsparams o cualquier otro parámetro, esta función utiliza los valores de parámetros por defecto para la sesión.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-29

Conversión de Tipos de Datos Explícita TO_NUMBER

NUMBER

TO_DATE

CHARACTER

TO_CHAR

DATE

TO_CHAR

Conversión de Tipos de Datos Explícita (continuación)

Función

Objetivo

TO_CHAR(number|date,[ fmt], [nlsparams])

Conversión de fecha: El parámetro nlsparams especifica el lenguaje en que se devolverán los nombres y las abreviaturas de mes y de día. Si se omite este parámetro, esta función utiliza los lenguajes de fecha por defecto para la sesión.

TO_NUMBER(char,[fmt], [nlsparams])

Convierte una cadena de caracteres que contenga dígitos en un número con el formato especificado por el modelo de formato opcional fmt. El parámetro nlsparams tiene el mismo objetivo en esta función que en la función TO_CHAR de conversión numérica.

TO_DATE(char,[fmt], [nlsparams])

Convierte una cadena de caracteres que representa una fecha en un valor de fecha de acuerdo con el fmt que se haya especificado. Si se omite fmt, el formato es DD-MON-YY. El parámetro nlsparams tiene el mismo objetivo en esta función que en la función TO_CHAR de conversión de fecha.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-30

Conversión de Tipos de Datos Explícita (continuación) Nota: Las lista de funciones mencionadas en esta lección únicamente suponen una parte de las funciones de conversión disponibles. Para obtener más información, consulte “Conversion Functions” en Oracle SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-31

Uso de la Función TO_CHAR con Fechas

TO_CHAR(date, 'format_model')

El modelo de formato: • Debe ir entre comillas simples • Es sensible a mayúsculas/minúsculas • Puede incluir cualquier elemento de formato de fecha válido • Tiene un elemento fm para eliminar espacios en blanco de relleno o suprimir ceros iniciales • Está separado del valor de fecha por una coma

Visualización de una Fecha en un Formato Específico Anteriormente, todos los valores de datos de Oracle se mostraban en formato DD-MON-YY. Puede utilizar la función TO_CHAR para convertir una fecha de este formato por defecto al que especifique. Instrucciones • El modelo de formato debe ir entre comillas simples y es sensible a mayúsculas/minúsculas. • El modelo de formato puede incluir cualquier elemento de formato de fecha. Asegúrese de separar el valor de fecha del modelo de formato con una coma. • Los nombres de días y meses de la salida se rellenan automáticamente con espacios en blanco. • Para eliminar espacios en blanco rellenados o para suprimir ceros iniciales, utilice el elemento fm del modo de relleno. • Puede formatear el campo de caracteres resultante con el comando COLUMN de iSQL*Plus (que se describe en una lección posterior). SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired FROM employees WHERE last_name = 'Higgins';

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-32

Elementos del Modelo de Formato de Fecha

Elemento YYYY

Resultado

YEAR

Nombre completo de año con letras (en inglés)

MM MONTH

Valor de dos dígitos para el mes

MON

Abreviatura de tres letras del mes

Abreviatura de tres letras del día de la semana

DAY

Nombre completo del día de la semana

Día del mes con números

Año completo con números

Nombre completo del mes

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-33

Ejemplo de Elementos de Formato de Formatos de Fecha Válidos

Elemento

Descripción

SCC o CC

Siglo; Oracle Server agrega - a las fechas a. C.

Años en fechas YYYY o SYYYY

Año; Oracle Server agrega - a las fechas a. C.

YYY o YY o Y

Los últimos tres, dos o uno dígitos del año

Y,YYY

Año con una coma en esa posición

IYYY, IYY, IY, I

Año de cuatro, tres, dos o un dígitos basado en el estándar ISO

SYEAR o YEAR

Nombre completo de año con letras; Oracle Server agrega - a las fechas a. C.

BC o AD

Indica un año a.C. o d.C.

B.C. o A.D.

Indica un año a.C. o d.C., con puntos

Trimestre

Mes: valor de dos dígitos

MONTH

Nombre del mes rellenado con espacios en blanco hasta la longitud de nueve caracteres.

MON

Nombre del mes, abreviatura de tres letras

Mes con números romanos

WW o W

Semana del año o del mes

DDD o DD o D

Día del año, del mes o de la semana

DAY

Nombre del día rellenado con espacios en blanco hasta la longitud de nueve caracteres.

Nombre del día, abreviatura de tres letras

Día juliano; número de días desde el 31 de diciembre de 4713 a.C.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-34

Elementos del Modelo de Formato de Fecha •

Los elementos horarios formatean la parte de hora de la fecha: HH24:MI:SS AM

•

15:45:32 PM

Agregue cadenas de caracteres poniéndolas entre comillas dobles: DD "of" MONTH

•

12 of OCTOBER

Utilice sufijos para escribir el nombre completo de los números: ddspth

fourteenth

Elemento de Formato de Fecha: Formatos de Hora Utilice los formatos que se muestran en las tablas siguientes para mostrar información de hora y literales y para cambiar números en cifras a números en letras. Elemento

Descripción

AM o PM

Indicador de meridiano

A.M. o P.M..

Indicador de meridiano, con puntos

HH o HH12 o HH24

Hora del día, u hora (1–12) u hora (0–23)

Minuto (0–59)

Segundo (0–59)

SSSSS

Segundos desde la medianoche (0–86399)

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-35

Otros Formatos Elemento / . ,

Descripción La puntuación se reproduce en el resultado. Las comillas se reproducen en el resultado.

“de”

Especificación de Sufijos para Influir en la Visualización de Números

Elemento TH

Descripción Número ordinal (por ejemplo, DDTH para 4TH)

Número completo con letras (por ejemplo, DDSP para FOUR)

SPTH o THSP

Número ordinal completo con letras (por ejemplo, DDSPTH para FOURTH)

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-36

Uso de la Función TO_CHAR con Fechas

SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE FROM employees;

…

Uso de la Función TO_CHAR con Fechas La sentencia SQL de la diapositiva muestra los apellidos y las fechas de contratación de todos los empleados. La fecha de contratación aparece como 17 June 1987. Ejemplo Modifique el ejemplo de la diapositiva para mostrar las fechas en un formato que aparezca como “Seventeenth of June 1987 12.00.00 AM”. SELECT last_name, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') HIREDATE FROM employees;

… Observe que el mes sigue el modelo de formato especificado; dicho de otro modo, la primera letra va en mayúsculas y el resto en minúsculas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-37

Uso de la Función TO_CHAR con Números

TO_CHAR(number, 'format_model') ddspth

Éstos son algunos de los elementos de formato que se pueden utilizar con la función TO_CHAR para mostrar un valor numérico como carácter: Elemento 9

Resultado

Muestra ceros

Coloca un signo de dólar flotante

Utiliza el símbolo de divisa local flotante

Imprime un punto decimal

Imprime una coma como indicador de miles

Representa un número

Uso de la Función TO_CHAR con Números Al trabajar con valores numéricos como cadenas de caracteres, debe convertir esos números al tipo de datos de carácter mediante la función TO_CHAR, que traduce un valor del tipo de datos NUMBER al tipo de datos VARCHAR2. Esta técnica es especialmente útil con la concatenación.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-38

Uso de la Función TO_CHAR con Números (continuación) Elementos de Formato Numérico Si está convirtiendo un número al tipo de datos de carácter, puede utilizar estos elementos de formato:

Elemento

Ejemplo

Resultado

Descripción Posición numérica (el número de nueves determina el ancho de la visualización)

999999

1234

Visualización de ceros iniciales

099999

001234

Signo de dólar flotante

$999999

$1234

Símbolo de divisa local flotante

L999999

FF1234

Devuelve el carácter decimal en la posición especificada. El valor por defecto es un punto (.).

99,99

99D99

Punto decimal en la posición especificada

999999,99

1234,00

Devuelve el separador de grupos en la posición especificada. Puede especificar varios separadores de grupo en un modelo de formato umérico.

9,999

9G999

Coma en la posición especificada

999,999

1,234

Signo menos a la derecha (valores negativos)

999999MI

1234-

Números negativos entre corchetes

999999PR

<1234>

EEEE

Notación científica (el formato debe especificar cuatro letras E)

99,999EEEE

1,234E+03

Devuelve la divisa dual "euro" (u otra) en la posición especificada

U9999

€1234

Multiplica por 10 n veces (n = número de nueves tras V)

9999V99

123400

Devuelve el valor negativo o positivo

S9999

-1234 o +1234

Visualiza los valores cero como espacios en blanco, no como 0

B9999,99

1234,00

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-39

Uso de la Función TO_CHAR con Números

SELECT TO_CHAR(salary, '$99,999.00') SALARY FROM employees WHERE last_name = 'Ernst';

Instrucciones • Oracle Server muestra una cadena de signos numéricos (#) en lugar de un número completo cuyos dígitos excedan el número de dígitos que se proporciona en el modelo de formato. • Oracle Server redondea el valor decimal almacenado al número de posiciones decimales que se proporciona en el modelo de formato.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-40

Uso de las Funciones TO_NUMBER y TO_DATE •

Convierta una cadena de caracteres en formato numérico mediante la función TO_NUMBER:

TO_NUMBER(char[, 'format_model'])

•

Convierta una cadena de caracteres en formato de fecha mediante la función TO_DATE:

TO_DATE(char[, 'format_model'])

•

Estas funciones tienen un modificador fx. Este modificador especifica la correspondencia exacta del argumento de carácter y el modelo de formato de fecha de una función TO_DATE. Copyright © 2004, Oracle. Todos los derechos reservados.

Uso de las Funciones TO_NUMBER y TO_DATE En ocasiones, deberá convertir una cadena de caracteres a número o a fecha. Para ello, utilice las funciones TO_NUMBER o TO_DATE. El modelo de formato que elija se basará en los elementos de formato demostrados anteriormente. El modificador fx especifica la correspondencia exacta del argumento de carácter y el modelo de formato de fecha de una función TO_DATE. • La puntuación y el texto entre comillas del argumento de carácter debe corresponder exactamente (excepto en las mayúsculas/minúsculas) con las partes correspondientes del modelo de formato. • El argumento de carácter no puede contener espacios en blanco adicionales. Sin fx, Oracle ignora los espacios en blanco adicionales. • Los datos numéricos del argumento de carácter debe tener el mismo número de dígitos que el elemento correspondiente del modelo de formato. Sin fx, los números del argumento de carácter pueden omitir los ceros iniciales.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-41

Uso de las Funciones TO_NUMBER y TO_DATE (continuaci贸n) Ejemplo Muestre el nombre y las fechas de contrataci贸n de todos los empleados que comenzaron a trabajar el 24 de mayo de 1999. Como se utiliza el modificador fx, se requiere una correspondencia exacta y los espacios tras la palabra May no se reconocen: SELECT last_name, hire_date FROM employees WHERE hire_date = TO_DATE('May 24, 1999', 'fxMonth DD, YYYY');

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-42

Formato de Fecha RR

Año Actual 1995 1995 2001 2001

Fecha Especificada 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95

Formato RR 1995 2017 2017 1995

Formato YY 1995 1917 2017 2095

Si los dos dígitos especificados del año son:

Si los dos dígitos del año actual son:

0–49

50–99

0–49

50–99

La fecha devuelta está en el siglo actual La fecha devuelta está en el siglo posterior al actual

La fecha devuelta está en el siglo anterior al actual La fecha devuelta está en el siglo actual

Elemento de Formato de Fecha RR El formato de fecha RR es parecido al elemento YY, pero lo puede utilizar para especificar siglos diferentes. Utilice el elemento de formato RR en lugar de YY para que el siglo del valor de retorno varíe según el año de dos dígitos especificado y los dos dígitos del año actual. La tabla de la diapositiva resume el comportamiento del elemento RR. Año Actual

Fecha Dada

Interpretación (RR)

Interpretación (YY)

1994

27-OCT-95

1995

1994

27-OCT-17

2017

1917

2001

27-OCT-17

2017

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-43

Ejemplo de Formato de Fecha RR Para encontrar empleados contratados antes de 1990, utilice el formato RR, que produce los mismos resultados si el comando se ejecuta en 1999 o ahora: SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY') FROM employees WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');

Ejemplo de Formato de Fecha RR Para buscar empleados contratados antes de 1990, se puede utilizar el formato RR. Como el año actual es mayor que 1999, el formato RR interpreta la porción del año de la fecha de 1950 a 1999. El comando siguiente, por otro lado, da como resultado que no se seleccione ninguna fila porque el formato YY interpreta la porción de la fecha en el siglo actual (2090). SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-yyyy') FROM employees WHERE TO_DATE(hire_date, 'DD-Mon-yy') < '01-Jan-1990'; no rows selected

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-44

Anidamiento de Funciones • •

Las funciones de una sola fila se pueden anidar hasta cualquier nivel. Las funciones anidadas se evalúan desde el nivel más profundo al menos profundo.

F3(F2(F1(col,arg1),arg2),arg3) Paso 1 = Resultado 1 Paso 2 = Resultado 2 Paso 3 = Resultado 3

Anidamiento de Funciones Las funciones de una sola fila se pueden anidar hasta cualquier profundidad. Las funciones anidadas se evalúan desde el nivel más interno al más externo. A continuación, se ofrecen varios ejemplos de la flexibilidad de estas funciones:

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-45

Anidamiento de Funciones

SELECT last_name, UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US')) FROM employees WHERE department_id = 60;

Anidamiento de Funciones (continuación) El ejemplo de la transferencia muestra los apellidos de los empleados del departamento 60. La evaluación de la sentencia SQL implica tres pasos: 1. La función interna recupera los primeros ocho caracteres del apellido. Result1 = SUBSTR (LAST_NAME, 1, 8) 2. La función externa concatena el resultado con _US. Result2 = CONCAT(Result1, '_US') 3. La función externa convierte los resultados a mayúsculas. Toda la expresión se convierte en la cabecera de la columna porque no se ha proporcionado alias de columna. Ejemplo Muestra la fecha del siguiente viernes seis meses después de la fecha de contratación. La fecha resultante debe aparecer como Friday, August 13th, 1999. Ordene los resultados por fecha de contratación. SELECT

TO_CHAR(NEXT_DAY(ADD_MONTHS (hire_date, 6), 'FRIDAY'), 'fmDay, Month DDth, YYYY') "Next 6 Month Review" FROM employees ORDER BY hire_date; Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-46

Funciones Generales Estas funciones pueden utilizar cualquier tipo de datos y están relacionadas con el uso de valores nulos: • NVL (expr1, expr2) • NVL2 (expr1, expr2, expr3) • NULLIF (expr1, expr2) • COALESCE (expr1, expr2, ..., exprn)

Funciones Generales Estas funciones pueden utilizar cualquier tipo de datos y están relacionados con el uso de valores nulos en la lista de expresiones. Función

Descripción

NVL

Convierte un valor nulo en un valor real

NVL2

Si expr1 no es nulo, NVL2 devuelve expr2. Si expr1 es nulo, NVL2 devuelve expr3. El argumento expr1 puede tener cualquier tipo de datos.

NULLIF

Compara dos expresiones y devuelve un valor nulo si son iguales; devuelve la primera expresión si no son iguales

COALESCE

Devuelve la primera expresión no nula de la lista de expresiones

Nota: Para obtener más información sobre los cientos de funciones disponibles, consulte “Functions” en Oracle SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-47

Función NVL Convierte un valor nulo en un valor real: • Los tipos de datos que se pueden utilizar son fecha, carácter y numérico. • Los tipos de datos deben corresponder: – NVL(commission_pct,0) – NVL(hire_date,'01-JAN-97') – NVL(job_id,'No Job Yet')

Función NVL Para convertir un valor nulo en un valor real, utilice la función NVL. Sintaxis NVL (expr1, expr2)

En la sintaxis: • expr1 es el valor o la expresión de origen que puede contener un valor nulo • expr2 es el valor de destino para convertir el valor nulo Puede utilizar la función NVL para convertir cualquier tipo de datos, pero el valor de retorno es siempre el mismo que el tipo de datos de expr1. Conversiones NVL para Varios Tipos de Datos

Tipo de Datos

Ejemplo de Conversión

NUMBER

NVL(number_column,9)

DATE

NVL(date_column, '01-JAN-95')

CHAR or VARCHAR2

NVL(character_column, 'Unavailable')

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-48

Uso de la Función NVL 1 SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees;

… 1

Uso de la Función NVL Para calcular la compensación anual de todos los empleados, debe multiplicar el salario mensual por 12 y sumar el porcentaje de comisión al resultado. SELECT last_name, salary, commission_pct, (salary*12) + (salary*12*commission_pct) AN_SAL FROM employees;

… … Observe que la compensación anual se calcula únicamente para los empleados que ganen comisión. Si el valor de alguna columna de una expresión es nulo, el resultado es nulo. Para calcular valores para todos los empleados, debe convertir el valor nulo en un número antes de aplicar el operador aritmético. En el ejemplo de la diapositiva, la función NVL se utiliza para convertir valores nulos en cero.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-49

Uso de la Función NVL2 SELECT last_name, salary, commission_pct, 1 NVL2(commission_pct, 2 'SAL+COMM', 'SAL') income FROM employees WHERE department_id IN (50, 80);

Uso de la Función NVL2 La función NVL2 examina la primera expresión. Si la primera expresión no es nula, la función NVL2 devuelve la segunda expresión. Si la primera expresión es nula, se devuelve la tercera expresión. Sintaxis NVL2(expr1, expr2, expr3)

En la sintaxis: • expr1 es el valor o la expresión de origen que puede contener un valor nulo • expr2 es el valor que se devuelve si expr1 no es nulo • expr3 es el valor que se devuelve si expr2 es nulo En el ejemplo que se muestra en la diapositiva, se examina la columna COMMISSION_PCT. Si se detecta un valor, se devuelve la segunda expresión de SAL+COMM. Si la columna COMMISSION_PCT contiene un valor nulo, se devuelve la tercera expresión de SAL. El argumento expr1 puede tener cualquier tipo de datos. Los argumentos expr2 y expr3 pueden tener cualquier tipo de datos excepto LONG. Si los tipos de datos de expr2 y expr3 son diferentes, Oracle Server convierte expr3 al tipo de datos de expr2 antes de compararlos a menos que expr3 sea una constante nula. En el último caso, no es necesaria una conversión del tipo de datos. El tipo de datos del valor de retorno es siempre el mismo que el tipo de datos de expr2, a menos que expr2 sean datos de carácter, en cuyo caso el tipo de datos del valor de retorno es VARCHAR2. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-50

Uso de la Función NULLIF 1 SELECT first_name, LENGTH(first_name) "expr1", 2 last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees;

… 1

Uso de la Función NULLIF La función NULLIF compara dos expresiones. Si son iguales, la función devuelve un valor nulo. Si no son iguales, la función devuelve la primera expresión. No puede especificar el literal NULL para la primera expresión. Sintaxis NULLIF (expr1, expr2)

En la sintaxis: • expr1 es el valor de origen que se compara con expr2 • expr2 es el valor de origen que se compara con expr1 (Si no es igual que expr1, se devuelve expr1.) En el ejemplo que se muestra en la diapositiva, la longitud del nombre de la tabla EMPLOYEES se compara con el apellido de la tabla EMPLOYEES. Si las longitudes del nombre y el apellido son iguales, se devuelve un valor nulo. Si las longitudes del nombre y el apellido no son iguales, se muestra la longitud del nombre. Nota: La función NULLIF es lógicamente equivalente a esta expresión CASE. La expresión CASE se analiza en una página posterior: CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-51

Uso de la Función COALESCE •

La ventaja de la función COALESCE sobre la función NVL es que la primera puede tomar varios valores alternativos. Si la primera expresión no es nula, la función COALESCE devuelve esa expresión; en caso contrario, realiza una fusión (COALESCE) de las expresiones restantes.

•

Uso de la Función COALESCE La función COALESCE devuelve la primera expresión no nula de la lista. Sintaxis COALESCE (expr1, expr2, ... exprn)

En la sintaxis: • expr1 devuelve esta expresión si no es nula • expr2 devuelve esta expresión si la primera expresión es nula y esta expresión no lo es • exprn devuelve esta expresión si las expresiones precedentes son nulas Todas las expresiones deben ser del mismo tipo de datos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-52

Uso de la Función COALESCE

SELECT last_name, COALESCE(manager_id,commission_pct, -1) comm FROM employees ORDER BY commission_pct;

…

Uso de la Función COALESCE (continuación) En el ejemplo de la diapositiva, si el valor MANAGER_ID no es nulo, se muestra. Si el valor MANAGER_ID es nulo, se muestra COMMISSION_PCT. Si los valores MANAGER_ID y COMMISSION_PCT son nulos, se muestra el valor –1.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-53

Expresiones Condicionales • •

Permiten utilizar la lógica IF-THEN-ELSE dentro de una sentencia SQL Puede usar dos métodos: – Expresión CASE – Función DECODE

Expresiones Condicionales Los dos métodos utilizados para implementar procesamiento condicional (lógica IF-THENELSE) en una sentencia SQL son la expresión CASE y la función DECODE. Nota: La expresión CASE cumple con ANSI SQL. La función DECODE es específica de la sintaxis Oracle.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-54

Expresión CASE Facilita las consultas condicionales realizando el trabajo de una sentencia IF-THEN-ELSE: CASE expr WHEN [WHEN WHEN ELSE END

comparison_expr1 THEN return_expr1 comparison_expr2 THEN return_expr2 comparison_exprn THEN return_exprn else_expr]

Expresión CASE Las expresiones CASE le permiten utilizar la lógica IF-THEN-ELSE en sentencias SQL sin llamar a procedimientos. En una expresión CASE simple, Oracle Server busca el primer par WHEN ... THEN en el que expr sea igual a comparison_expr y devuelve return_expr. Si ninguno de los pares WHEN ... THEN cumplen esta condición y si existe una cláusula ELSE, Oracle Server devuelve else_expr. De lo contrario, Oracle Server devuelve un valor nulo. No puede especificar el literal NULL para todas las expresiones return_exprs y else_expr. Todas las expresiones ( expr, comparison_expr y return_expr) deben ser del mismo tipo de datos, que puede ser CHAR, VARCHAR2, NCHAR o NVARCHAR2.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-55

Uso de la Expresión CASE Facilita las consultas condicionales realizando el trabajo de una sentencia IF-THEN-ELSE: SELECT last_name, job_id, salary CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;

… …

Uso de la Expresión CASE En la sentencia SQL de la diapositiva, se descodifica el valor de JOB_ID. Si JOB_ID es IT_PROG, el aumento de salario es del 10 %; si JOB_ID es ST_CLERK, el aumento de salario es del 15 %; si JOB_ID es SA_REP, el aumento de salario es del 20 %. Para el resto de roles de trabajo, no hay aumento de salario. Se puede escribir la misma sentencia con la función DECODE. Esto es un ejemplo de expresión CASE buscada. En una expresión CASE buscada, la búsqueda se produce de izquierda a derecha hasta que se encuentre una incidencia de la condición mostrada y, entonces, devuelve la expresión de retorno. Si no se encuentra ninguna condición que sea verdadera y si existe una cláusula ELSE, se devuelve la expresión de retorno de la cláusula ELSE; de lo contrario, se devuelve NULL. SELECT last_name,salary, (CASE WHEN salary<5000 THEN 'Low' WHEN salary<10000 THEN 'Medium' WHEN salary<20000 THEN 'Good' ELSE 'Excellent' END) qualified_salary FROM employees;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-56

Función DECODE Facilita las consultas condicionales realizando el trabajo de una expresión CASE o de una sentencia IF-THEN-ELSE: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default])

Función DECODE La función DECODE descodifica una expresión de forma parecida a la lógica IF-THEN-ELSE que se utiliza en varios lenguajes. La función DECODE descodifica expression tras compararla con cada valor search. Si la expresión es igual que search, se devuelve result. Si se omite el valor por defecto, se devuelve un valor nulo donde un valor de búsqueda no corresponda a ninguno de los valores del resultado.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-57

Uso de la Función DECODE

SELECT last_name, job_id, salary DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARY FROM employees;

… …

Uso de la Función DECODE En la sentencia SQL de la diapositiva, se prueba el valor de JOB_ID. Si JOB_ID es IT_PROG, el aumento de salario es del 10 %; si JOB_ID es ST_CLERK, el aumento de salario es del 15 %; si JOB_ID es SA_REP, el aumento de salario es del 20 %. Para el resto de roles de trabajo, no hay aumento de salario. Se puede expresar la misma sentencia en pseudocódigo como sentencia IF-THEN-ELSE. IF job_id = IF job_id = IF job_id = ELSE salary

'IT_PROG' 'ST_CLERK' 'SA_REP' = salary

THEN THEN THEN

salary = salary*1.10 salary = salary*1.15 salary = salary*1.20

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-58

Uso de la Función DECODE Muestre la tasa de impuestos aplicable para cada empleado del departamento 80: SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATE FROM employees WHERE department_id = 80;

Uso de la Función DECODE (continuación) La diapositiva muestra otro ejemplo que utiliza la función DECODE. En este ejemplo, determinamos la tasa de impuestos para cada empleado del departamento 80 basándonos en el salario mensual. Las tasas de impuestos son: Rango de Salarios Mensuales Tasa de Impuestos $0.00–1,999.99 00 % $2,000.00–3,999.99 09 % $4,000.00–5,999.99 20 % $6,000.00–7,999.99 30 % $8,000.00–9,999.99 40 % $10,000.00–11,999.99 42 % $12,200.00–13,999.99 44 % $14,000.00 o más 45 %

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-59

Resumen En esta lección ha aprendido a: • Realizar cálculos en datos mediante funciones • Modificar elementos de datos individuales mediante funciones • Manipular la salida para grupos de filas mediante funciones • Modificar formatos de fecha para su visualización mediante funciones • Convertir tipos de datos de columnas mediante funciones • Utilizar funciones NVL • Utilizar la lógica IF-THEN-ELSE Copyright © 2004, Oracle. Todos los derechos reservados.

Resumen Las funciones de una sola fila se pueden anidar hasta cualquier nivel. Las funciones de una sola fila pueden manipular: • Datos de carácter: LOWER, UPPER, INITCAP, CONCAT, SUBSTR, INSTR, LENGTH • Datos numéricos: ROUND, TRUNC, MOD • Datos de fecha: MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC Recuerde que: • Los valores de datos también pueden utilizar operadores aritméticos. • Las funciones de conversión pueden convertir valores de carácter, de fecha y numéricos: TO_CHAR, TO_DATE, TO_NUMBER • Hay varias funciones relacionadas con los valores nulos, como NVL, NVL2, NULLIF y COALESCE. • Se puede aplicar la lógica IF-THEN-ELSE dentro de una sentencia SQL mediante la expresión CASE o la función DECODE. SYSDATE y DUAL SYSDATE es una función de fecha que devuelve la fecha y la hora actuales. Se suele seleccionar SYSDATE en una tabla ficticia denominada DUAL.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-60

Práctica 3: Visión General de la Parte 2 Esta práctica cubre los temas siguientes: • Creación de consultas que requieren el uno de funciones numéricas, de carácter y de fecha • Uso de concatenación con funciones • Escritura de consultas sensibles a mayúsculas/minúsculas para probar la utilidad de las funciones de carácter • Realización de cálculos de años y meses de servicio de un empleado • Determinación de la fecha de revisión para un empleado

Práctica 3: Visión General de la Parte 2 La práctica de la Parte 2 de esta lección proporciona varios ejercicios que utilizan diferentes funciones disponibles para los tipos de datos de carácter, numérico y de fecha. Para la Parte 2, realice los ejercicios 7–14. Recuerde que para las funciones anidadas, los resultados se evalúan desde la función más interna a la más externa.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-61

Práctica 3 Parte 1 1. Escriba una consulta para mostrar la fecha actual. Etiquete la columna como Date.

2. El departamento de recursos humanos necesita mostrar el número de empleado, el apellido, el salario y el salario aumentado en un 15,5 % (expresado como número entero) de cada empleado. Etiquete la columna como New Salary. Guarde la sentencia SQL en un archivo de texto denominado lab_03_02.sql. 3. Ejecute la consulta del archivo lab_03_02.sql.

…

4. Modifique la consulta lab_03_02.sql para agregar una columna que reste el antiguo salario al nuevo salario. Etiquete la columna como Increase. Guarde el contenido en un archivo denominado lab_03_04.sql. Ejecute la consulta revisada.

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-62

Práctica 3 (continuación) 5. Escriba una consulta que muestre el apellido (con la primera letra en mayúsculas y las demás en minúsculas) y la longitud del apellido de todos los empleados cuyo nombre comience por J, A o M. Etiquete cada columna de forma adecuada. Ordene los resultados por los apellidos de los empleados.

Reescriba la consulta para que se pida al usuario que introduzca la primera letra de un apellido. Por ejemplo, si el usuario introduce H cuando se le pida una letra, la salida debería mostrar todos los empleados cuyo apellido comience por la letra H.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-63

Práctica 3 (continuación) 6. El departamento de recursos humanos desea averiguar el tiempo que llevan contratados todos los empleados. Para cada empleado, muestre el apellido y calcule el número de meses entre hoy y la fecha en que se contrató. Etiquete la columna como MONTHS_WORKED. Ordene los resultados por el número de meses empleado. Redondee el número de meses al número entero más cercano. Nota: Los resultados que obtenga diferirán.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-64

Práctica 3 (continuación) Parte 2 7. Cree un informe que cree lo siguiente para cada empleado: <employee last name> earns <salary> monthly but wants <3 times salary>. Etiquete la columna como Dream Salaries.

…

Si le queda tiempo, realice los siguientes ejercicios: 8. Cree una consulta para mostrar el apellido y el salario de todos los empleados. Formatee el salario para que tenga una longitud de 15 caracteres, rellenados a la izquierda con el símbolo $. Etiquete la columna como SALARY.

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-65

Práctica 3 (continuación) 9. Muestre el apellido, la fecha de contratación y la fecha de revisión salarial de cada empleado, que es el primer lunes después de seis meses de servicio. Etiquete la columna como REVIEW. Formatee las fechas para que aparezca en un formato similar a “Monday, the Thirty-First of July, 2000”.

…

10. Muestre el apellido, la fecha de contratación y el día de la semana en que empezó el empleado. Etiquete la columna como DAY. Ordene los resultados por día de la semana, empezando por el lunes.

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-66

Práctica 3 (continuación) Si desea afrontar un desafío mayor, realice estos ejercicios: 11. Cree una consulta que muestre los apellidos y los importes de comisión de los empleados. Si un empleado no gana ninguna comisión, muestre “No Commission”. Etiquete la columna como COMM.

…

12. Cree una consulta que muestre los ocho primeros caracteres de los apellidos de los empleados e indique los importes de sus salarios con asteriscos. Cada asterisco representa mil dólares. Clasifique los datos en orden descendente de salario. Etiquete la columna como EMPLOYEES_AND_THEIR_SALARIES.

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-67

Pr谩ctica 3 (continuaci贸n) 13. Mediante la funci贸n DECODE, escriba una consulta que muestre el grado de todos los empleados bas谩ndose en el valor de la columna JOB_ID, mediante estos datos: Puesto AD_PRES ST_MAN IT_PROG SA_REP ST_CLERK Ninguno de las anteriores

Grado A B C D E 0

14. Vuelva a escribir la sentencia del ejercicio anterior mediante la sintaxis CASE.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 3-68

Informaci贸n sobre Datos Agregados mediante las Funciones de Grupo

Objetivos Al finalizar esta lección, debería estar capacitado para: • Identificar las funciones de grupo disponibles • Describir el uso de las funciones de grupo • Agrupar datos mediante la cláusula GROUP BY •

Incluir o excluir filas agrupadas utilizando la cláusula HAVING

Objetivos Esta lección trata más exhaustivamente las funciones. Se centra en la obtención de información de resumen (como, por ejemplo, medias) para grupos de filas. Analiza cómo agrupar las filas de una tabla en juegos más pequeños y cómo especificar criterios de búsqueda para grupos de filas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-2

¿Qué son las Funciones de Grupo? Las funciones de grupo operan en juegos de filas para dar un resultado por grupo. EMPLOYEES

Salario máximo en la tabla EMPLOYEES

Funciones de Grupo A diferencia de las funciones de una sola fila, las funciones de grupo operan en juegos de filas para dar un resultado por grupo. Estos juegos pueden abarcar toda la tabla o la tabla dividida en grupos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-3

Tipos de Funciones de Grupo • • • • • • •

AVG COUNT MAX MIN STDDEV SUM VARIANCE

Funciones de grupo

Tipos de Funciones de Grupo Cada una de las funciones acepta un argumento. La tabla siguiente identifica las opciones que puede utilizar en la sintaxis: Función AVG([DISTINCT|ALL]n)

Descripción Valor medio de n; se ignoran los valores nulos

COUNT({*|[DISTINCT|ALL]expr}) Número de filas, en las que expr se evalúa como algo no nulo (cuenta todas las filas seleccionadas que utilizan *, incluidas las duplicadas y las que contienen valores nulos) MAX([DISTINCT|ALL]expr) Valor máximo de expr; se ignoran los valores nulos MIN([DISTINCT|ALL]expr)

Valor mínimo de expr; se ignoran los valores nulos

STDDEV([DISTINCT|ALL]x)

Desviación estándar de n; se ignoran los valores nulos

SUM([DISTINCT|ALL]n)

Suma los valores de n; se ignoran los valores nulos

VARIANCE([DISTINCT|ALL]x)

Varianza de n; se ignoran los valores nulos

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-4

Funciones de Grupo: Sintaxis

SELECT FROM [WHERE [GROUP BY [ORDER BY

[column,] group_function(column), ... table condition] column] column];

Instrucciones para Utilizar Funciones de Grupo • DISTINCT hace que la función considere únicamente valores no duplicados; ALL hace que considere todos los valores, incluidos los duplicados. El valor por defecto es ALL y, por tanto, no es necesario especificarlo. • Los tipos de datos para las funciones con un argumento expr pueden ser CHAR, VARCHAR2, NUMBER o DATE. • Todas las funciones de grupo ignoran los valores nulos. Para sustituir con un valor los valores nulos, utilice las funciones NVL, NVL2 o COALESCE.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-5

Uso de las Funciones AVG y SUM Puede utilizar AVG y SUM para datos numéricos. SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';

Uso de las Funciones de Grupo Puede utilizar las funciones AVG, SUM, MIN y MAX en columnas que puedan almacenar datos numéricos. El ejemplo de la diapositiva muestra la media, el más alto, el más bajo y la suma de los salarios mensuales de todos los representantes de ventas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-6

Uso de las Funciones MIN y MAX Puede utilizar MIN y MAX para tipos de datos numéricos, de carácter y de fecha. SELECT MIN(hire_date), MAX(hire_date) FROM employees;

Uso de las Funciones de Grupo (continuación) Puede utilizar las funciones MAX y MIN para tipos de datos numéricos, de carácter y de fecha. El ejemplo de la diapositiva muestra al empleado más nuevo y al más antiguo. El ejemplo siguiente muestra el apellido del empleado primero y el del último de una lista alfabética de todos los empleados. SELECT MIN(last_name), MAX(last_name) FROM employees;

Nota: Las funciones AVG, SUM, VARIANCE y STDDEV se pueden utilizar sólo con tipos de datos numéricos. MAX y MIN no se pueden utilizar con tipos de datos LOB o LONG.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-7

Uso de la Función COUNT COUNT(*) devuelve el número de filas en una tabla:

SELECT COUNT(*) FROM employees WHERE department_id = 50;

COUNT(expr) devuelve el número de filas con valores no nulos para expr:

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

Función COUNT La función COUNT tiene tres formatos: • COUNT(*) • COUNT(expr) • COUNT(DISTINCT expr) COUNT(*) devuelve el número de filas de una tabla que satisfacen el criterio de la sentencia SELECT, incluidas las filas duplicadas y las que contienen valores nulos de cualquiera de las columnas. Si se incluye una cláusula WHERE en la sentencia SELECT, COUNT(*) devuelve el número de filas que satisfacen la condición de la cláusula WHERE. Por el contrario, COUNT(expr) devuelve el número de valores no nulos que hay en la columna identificada con expr. COUNT(DISTINCT expr) devuelve el número de valores únicos no nulos que hay en la columna identificada con expr. Ejemplos 1. El ejemplo de la diapositiva muestra el número de empleados del departamento 50. 2. Este ejemplo muestra el número de empleados del departamento 80 que ganan comisión.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-8

Uso de la Palabra Clave DISTINCT • •

COUNT(DISTINCT expr) devuelve el número de valores no nulos distintos de expr. Para mostrar el número de valores de departamento distintos en la tabla EMPLOYEES:

SELECT COUNT(DISTINCT department_id) FROM employees;

Palabra Clave DISTINCT Utilice la palabra clave DISTINCT para suprimir el recuento de valores duplicados en una columna. El ejemplo de la diapositiva muestra el número de valores de departamento distintos que hay en la tabla EMPLOYEES.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-9

Funciones de Grupo y Valores Nulos Las funciones de grupo ignoran los valores nulos en la columna:

SELECT AVG(commission_pct) FROM employees;

La función NVL fuerza a las funciones de grupo a incluir valores nulos:

SELECT AVG(NVL(commission_pct, 0)) FROM employees;

Funciones de Grupo y Valores Nulos Todas las funciones de grupo ignoran los valores nulos de la columna. La función NVL fuerza a las funciones de grupo a incluir valores nulos. Ejemplos 1. La media se calcula basándose sólo en las filas de la tabla en la que se almacena un valor válido en la columna COMMISSION_PCT. La media se calcula como comisión total que se paga a todos los empleados dividida por el número de empleados que reciben una comisión (cuatro). 2. La media se calcula basándose en todas las filas de la tabla, independientemente de si se almacenan valores válidos en la columna COMMISSION_PCT. La media se calcula como comisión total que se paga a todos los empleados dividida por el número total de empleados de la compañía (20).

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-10

Creación de Grupos de Datos

EMPLOYEES 4400 9500

3500

6400

Salario medio de la tabla EMPLOYEES para cada departamento

10033

Creación de Grupos de Datos Hasta el momento, todas las funciones de grupo han tratado la tabla como un grupo de información de gran tamaño. Hay ocasiones, sin embargo, en que debe dividir la tabla de información en grupos más pequeños. Esto se puede conseguir mediante la cláusula GROUP BY.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-11

Creación de Grupos de Datos: Sintaxis de la Cláusula GROUP BY SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

Puede dividir las filas de una tabla en grupos más pequeños mediante la cláusula GROUP BY.

Cláusula GROUP BY Puede utilizar la cláusula GROUP BY para dividir las filas de una tabla en grupos. Puede utilizar entonces las funciones de grupo para devolver información de resumen para cada grupo. En la sintaxis: group_by_expression especifica columnas cuyos valores determinan la base para agrupar filas Instrucciones • Si incluye una función de grupo en una cláusula SELECT, no puede seleccionar además resultados individuales, a menos que la columna individual aparezca en la cláusula GROUP BY. Recibirá un mensaje de error si no incluye la lista de columnas en la cláusula GROUP BY. • Mediante una cláusula WHERE, puede excluir filas antes de dividirlas en grupos. • Debe incluir las columnas en la cláusula GROUP BY. • No puede utilizar un alias de columna en la cláusula GROUP BY.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-12

Uso de la Cláusula GROUP BY Todas las columnas de la lista SELECT que no sean funciones de grupo se deben incluir en la cláusula GROUP BY. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

Uso de la Cláusula GROUP BY Cuando utilice la cláusula GROUP BY, asegúrese de que todas las columnas de la lista SELECT que no sean funciones de grupo se incluyan en la cláusula GROUP BY. El ejemplo de la diapositiva muestra el número de departamento y el salario medio de cada departamento. La sentencia SELECT, que contiene una cláusula GROUP BY, se evalúa así: • La cláusula SELECT especifica las columnas que se recuperarán, de este modo: - Columna de números de departamento de la tabla EMPLOYEES - Media de todos los salarios del grupo que especificó en la cláusula GROUP BY • La cláusula FROM especifica las tablas a las que debe acceder la base de datos: la tabla EMPLOYEES. • La cláusula WHERE especifica las filas que se recuperarán. Como no hay cláusula WHERE, se recuperarán todas las filas por defecto. • La cláusula GROUP BY especifica cómo se deben agrupar las filas. Las filas se agrupan por número de departamento, por lo que la función AVG que se aplica a la columna de salarios calcula el salario medio de cada departamento.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-13

Uso de la Cláusula GROUP BY La columna GROUP BY no tiene que estar en la lista SELECT. SELECT AVG(salary) FROM employees GROUP BY department_id ;

Uso de la Cláusula GROUP BY (continuación) La columna GROUP BY no tiene que estar en la cláusula SELECT. Por ejemplo, la sentencia SELECT de la diapositiva muestra los salarios medios de cada departamento sin mostrar los números respectivos de departamento. Sin embargo, sin los números de departamento, los resultados no parecen significativos. Puede utilizar la función de grupo en la cláusula ORDER BY. SELECT FROM GROUP BY ORDER BY

department_id, AVG(salary) employees department_id AVG(salary);

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-14

Agrupación por Más de Una Columna EMPLOYEES

Sume los salarios de la tabla EMPLOYEES para cada puesto, agrupados por departamento

…

Grupos Dentro de Grupos En ocasiones, necesita ver los resultados para grupos dentro de grupos. La diapositiva muestra un informe que detalla el salario total que se paga a cada cargo en cada departamento. La tabla EMPLOYEES se agrupa primero por número de departamento y después por cargo dentro de esa agrupación. Por ejemplo, los cuatro administrativos del departamento 50 se agrupan juntos y se crea un único resultado (salario total) para todos los administrativos del grupo.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-15

Uso de la Cláusula GROUP BY en Varias Columnas SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id;

Grupos Dentro de Grupos (continuación) Puede devolver un resumen de resultados para grupos y subgrupos mostrando más de una columna GROUP BY. Puede ordenar por defecto de los resultados según el orden de las columnas en la cláusula GROUP BY. En el ejemplo de la diapositiva, la sentencia SELECT que contiene una cláusula GROUP BY se evalúa así: • La cláusula SELECT especifica la columna que se recuperará: - Número de departamento de la tabla EMPLOYEES - Identificador de trabajo de la tabla EMPLOYEES - Suma de todos los salarios del grupo que especificó en la cláusula GROUP BY • La cláusula FROM especifica las tablas a las que debe acceder la base de datos: la tabla EMPLOYEES. • La cláusula GROUP BY especifica cómo se deben agrupar las filas: - En primer lugar, las filas se agrupan por número de departamento. - En segundo lugar, las filas se agrupan por identificador de trabajo de los grupos de número de departamento. Así pues, la función SUM se aplica a la columna de salarios para todos los identificadores de trabajo de cada grupo de números de departamento.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-16

Consultas Ilegales que Utilizan Funciones de Grupo Cualquier columna o expresión de la lista SELECT que no sea una función de agregación se debe incluir en la cláusula GROUP BY: SELECT department_id, COUNT(last_name) FROM employees; SELECT department_id, COUNT(last_name) * ERROR at line 1: ORA-00937: not a single-group group function

Falta una columna en la cláusula GROUP BY

Consultas Ilegales que Utilizan Funciones de Grupo Siempre que utilice una mezcla de elementos individuales (DEPARTMENT_ID) y funciones de grupo (COUNT) en la misma sentencia SELECT, debe incluir una cláusula GROUP BY que especifique los elementos individuales (en este caso, DEPARTMENT_ID). Si falta la cláusula GROUP BY, aparece el mensaje de error “not a single-group group function” y un asterisco (*) indica la columna con el error. Puede corregir el error de la diapositiva agregando la cláusula GROUP BY: SELECT department_id, count(last_name) FROM employees GROUP BY department_id;

… Cualquier columna o expresión de la lista SELECT que no sea una función de agregación se debe incluir en la cláusula GROUP BY.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-17

Consultas Ilegales que Utilizan Funciones de Grupo • • •

No puede utilizar la cláusula WHERE para restringir grupos. Utilice la cláusula HAVING para restringir grupos. No puede utilizar funciones de grupo en la cláusula WHERE.

SELECT FROM WHERE GROUP BY

department_id, AVG(salary) employees AVG(salary) > 8000 department_id ;

WHERE

AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here

No se puede utilizar la cláusula WHERE para restringir grupos

Consultas Ilegales que Utilizan Funciones de Grupo (continuación) La cláusula WHERE no se puede utilizar para restringir grupos. La sentencia SELECT de la diapositiva da como resultado un error porque utiliza la cláusula WHERE para restringir la visualización de salarios medios de los departamentos que tienen un salario medio superior a 8.000 dólares. Puede corregir el error del ejemplo si utiliza la cláusula HAVING para restringir grupos. SELECT FROM HAVING GROUP BY

department_id, AVG(salary) employees AVG(salary) > 8000 department_id ;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-18

Restricción de Resultados de Grupos EMPLOYEES

Salario máximo por departamento cuando es superior a 10.000 dólares

…

Restricción de Resultados de Grupos De la misma forma que utiliza la cláusula WHERE para restringir las filas que se seleccionarán, utilice la cláusula HAVING para restringir grupos. Para buscar el salario máximo de cada departamento que tenga un salario máximo superior a 10.000 dólares, necesita: 1. Buscar el salario medio de cada departamento agrupando por número de departamento. 2. Restringir los grupos a los departamentos con un salario máximo superior a 10.000 dólares.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-19

Restricción de Resultados de Grupos con la Cláusula HAVING Si se utiliza la cláusula HAVING, Oracle Server restringe los grupos así: 1. Se agrupan las filas. 2. Se aplica la función de grupo. 3. Se muestran los grupos que satisfacen la cláusula HAVING. SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY

column, group_function table condition] group_by_expression] group_condition] column];

Restricción de Resultados de Grupos con la Cláusula HAVING Utilice la cláusula HAVING para especificar los grupos que se deben mostrar, con los que se restringen más los grupos basándose en la información de agregación. En la sintaxis, group_condition restringe los grupos de filas devueltas a los grupos para los que es verdadera la condición especificada. Oracle Server sigue estos pasos si se utiliza la cláusula HAVING: 1. Se agrupan las filas. 2. Se aplica al grupo la función de grupo. 3. Se muestran los grupos que satisfacen los criterios de la cláusula HAVING. La cláusula HAVING puede ir delante de la cláusula GROUP BY, pero se recomienda poner primero la cláusula GROUP BY, porque resulta más lógico. Se forman los grupos y se calculan las funciones de grupo antes de que se aplique la cláusula HAVING a los grupos de la lista SELECT.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-20

Uso de la Cláusula HAVING

SELECT FROM GROUP BY HAVING

department_id, MAX(salary) employees department_id MAX(salary)>10000 ;

Uso de la Cláusula HAVING El ejemplo de la diapositiva muestra números de departamento y salarios máximos de los departamentos con un salario máximo superior a 10.000 dólares. Puede utilizar la cláusula GROUP BY sin utilizar la función de grupo de la lista SELECT. Si restringe filas basándose en el resultado de una función de grupo, debe tener una cláusula GROUP BY además de la cláusula HAVING. El ejemplo siguiente muestra los números de departamento y salarios medios de los departamentos con un salario máximo superior a 10.000 dólares.: SELECT FROM GROUP BY HAVING

department_id, AVG(salary) employees department_id max(salary)>10000;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-21

Uso de la Cláusula HAVING

SELECT FROM WHERE GROUP BY HAVING ORDER BY

job_id, SUM(salary) PAYROLL employees job_id NOT LIKE '%REP%' job_id SUM(salary) > 13000 SUM(salary);

Uso de la Cláusula HAVING (continuación) El ejemplo de la diapositiva muestra el identificador de trabajo y el salario mensual total de cada trabajo que tiene una nómina total superior a 13.000 dólares. El ejemplo excluye a los representantes de venta y ordena la lista por salario mensual total.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-22

Anidamiento de Funciones de Grupo Muestre al salario medio máximo: SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id ;

Anidamiento de Funciones de Grupo Las funciones de grupo se pueden anidar hasta una profundidad de dos. El ejemplo de la diapositiva muestra el salario medio máximo.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-23

Resumen En esta lección ha aprendido a: • Utilizar las funciones de grupo COUNT, MAX, MIN y AVG • Escribir consultas que utilicen la cláusula GROUP BY • Escribir consultas que utilicen la cláusula HAVING SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY

column, group_function table condition] group_by_expression] group_condition] column];

Resumen Hay varias funciones de grupo disponibles en SQL, como éstas: AVG, COUNT, MAX, MIN, SUM, STDDEV y VARIANCE Puede crear subgrupos mediante la cláusula GROUP BY. Los grupos se pueden restringir mediante la cláusula HAVING. Ponga las cláusulas HAVING y GROUP BY después de la cláusula WHERE en una sentencia. El orden de las cláusulas HAVING y GROUP después de la cláusula WHERE no es importante. Ponga la cláusula ORDER BY en último lugar. Oracle Server evalúa las cláusulas por este orden: 1. Si la sentencia contiene una cláusula WHERE, Oracle Server establece las filas candidatas. 2. Oracle Server identifica los grupos especificados en la cláusula GROUP BY. 3. La cláusula HAVING restringe más los grupos resultantes que no cumplen los criterios de grupo de la cláusula HAVING. Nota: Para obtener una lista completa de las funciones de grupo, consulte Oracle SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-24

Práctica 4: Visión General Esta práctica cubre los temas siguientes: • Escritura de consultas que utilicen las funciones de grupo • Agrupación por filas para obtener más de un resultado • Restricción de grupos mediante la cláusula HAVING

Práctica 4: Visión General Al finalizar esta práctica, debería estar familiarizado con el uso de funciones de grupo y la selección de grupos de datos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-25

Práctica 4 Determine la validez de las tres siguientes afirmaciones. Seleccione Verdadero o Falso. 1. Las funciones de grupo trabajan a través de muchas filas para generar un resultado. Verdadero/Falso 2. Las funciones de grupo incluyen valores nulos en los cálculos. Verdadero/Falso 3. La cláusula WHERE restringe las filas antes de incluirlas en un cálculo de grupo. Verdadero/Falso El departamento de recursos humanos necesita estos informes: 4. Busque el salario más alto, el más bajo, la suma y el salario medio de todos los empleados. Etiquete las columnas como Maximum, Minimum, Sum y Average, respectivamente. Redondee los resultados al siguiente número entero. Guarde la sentencia SQL en un archivo de texto denominado lab_04_04.sql.

5. Modifique la consulta en lab_04_04.sql para mostrar el salario mínimo, el máximo, la suma y el salario medio para cada tipo de trabajo. Vuelva a guardar lab_04_04.sql como lab_04_05.sql. Ejecute la sentencia en lab_04_05.sql.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-26

Práctica 4 (continuación) 6. Escriba una consulta para mostrar el número de personas con el mismo puesto.

Generalice la consulta para que se le pida un cargo al usuario del departamento de recursos humanos. Guarde el archivo de comandos en un archivo denominado lab_04_06.sql. 7. Determine el número de supervisores sin listarlos. Etiquete la columna como Number of Managers. Indicación: Utilice la columna MANAGER_ID para determinar el número de supervisores.

8. Busque la diferencia entre el salario más alto y el más bajo. Etiquete la columna como DIFFERENCE.

Si le queda tiempo, realice los siguientes ejercicios: 9. Cree un informe para mostrar el número de supervisor y el salario del empleado peor pagado de ese supervisor. Excluya a los empleados cuyo supervisor no se conozca. Excluya los grupos en los que el salario mínimo sea 6.000 dólares o menos. Clasifique la salida en orden descendente de salario.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-27

Práctica 4 (continuación) Si desea afrontar un desafío mayor, realice estos ejercicios: 10. Cree una consulta que muestre el número total de empleados y, de dicho total, el número de empleados contratados en 1995, 1996, 1997 y 1998. Cree las cabeceras de columna adecuadas.

11. Cree una consulta de matriz para mostrar el trabajo, el salario de dicho puesto basado en el número de departamento y el salario total de ese puesto de los departamentos 20, 50, 80 y 90, y proporcione una cabecera apropiada a cada columna.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-28

Visualizaci贸n de Datos de Varias Tablas

Objetivos Al finalizar esta lección, debería estar capacitado para: • Escribir sentencias SELECT para acceder a datos de más de una tabla mediante las uniones igualitarias y no igualitarias • Unir una tabla a sí misma mediante una autounión • Ver datos que generalmente no cumplen una condición de unión utilizando uniones externas • Generar un producto cartesiano de todas las filas de dos o más tablas

Objetivos Esta lección explica cómo obtener datos de más de una tabla. Una unión se utiliza para ver información de varias tablas. De esta forma, puede unir tablas para ver información de más de una tabla. Nota: Puede encontrar información sobre uniones en “SQL Queries and Subqueries: Joins” en Oracle SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-2

Obtención de Datos de Varias Tablas

DEPARTMENTS

EMPLOYEES

…

Obtención de Datos de Varias Tablas En ocasiones, tiene que utilizar datos de más de una tabla. En el ejemplo de la diapositiva, el informe muestra datos de dos tablas distintas: • Los identificadores de empleado se encuentran en la tabla EMPLOYEES. • Los identificadores de departamento se encuentran en las tablas EMPLOYEES y DEPARTMENTS. • Los nombres de departamento se encuentran en la tabla DEPARTMENTS. Para crear el informe, debe enlazar las tablas EMPLOYEES y DEPARTMENTS y acceder a datos de ambas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-3

Tipos de Uniones Las uniones compatibles con el estándar SQL:1999 son: • Uniones cruzadas • Uniones naturales • Cláusula USING • •

Uniones externas completas (o de dos lados) Condiciones de unión arbitrarias para uniones externas

Tipos de Uniones Para unir tablas, puede utilizar la sintaxis de unión compatible con el estándar SQL:1999. Nota: antes de la versión Oracle9i, la sintaxis de unión era diferente a los estándares ANSI. La sintaxis de unión compatible con SQL:1999 no ofrece ventajas de rendimiento con respecto a la sintaxis de unión propietaria de Oracle que existía en las versiones anteriores. Para obtener información detallada sobre la sintaxis de unión propietaria, consulte el Apéndice C.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-4

Unión de Tablas mediante la Sintaxis SQL:1999 Utilice una unión para consultar datos de más de una tabla: SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]| [CROSS JOIN table2];

Definición de Uniones En la sintaxis: table1.column muestra la tabla y la columna de las que se recuperan los datos NATURAL JOIN une dos tablas basándose en el mismo nombre de columna JOIN table USING column_name realiza una unión igualitaria basándose en el nombre de columna JOIN table ON table1.column_name realiza una unión igualitaria basándose en la condición de la cláusula ON, = table2.column_name LEFT/RIGHT/FULL OUTER se utiliza para realizar uniones externas CROSS JOIN devuelve un producto cartesiano de las dos tablas Para obtener más información, consulte “SELECT” en Oracle SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-5

Creación de Uniones Naturales •

• •

La cláusula NATURAL JOIN se basa en todas las columnas de las dos tablas que tienen el mismo nombre. Selecciona filas de las dos tablas que tienen valores iguales en todas las columnas correspondientes. Si las columnas que tienen los mismos nombres tienen tipos de datos diferentes, se devuelve un error.

Creación de Uniones Naturales Puede unir tablas automáticamente basándose en columnas de las dos tablas que tengan tipos de datos y nombres correspondientes. Hágalo mediante las palabras clave NATURAL JOIN. Nota: la unión sólo se puede realizar en las columnas que tengan los nombres y los tipos de datos iguales en ambas tablas. Si las columnas tienen el mismo nombre pero diferentes tipos de datos, la sintaxis de NATURAL JOIN producirá un error.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-6

Recuperación de Registros con Uniones Naturales

SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ;

Recuperación de Registros con Uniones Naturales En el ejemplo de la diapositiva, la tabla LOCATIONS se une a la tabla DEPARTMENT por la columna LOCATION_ID, que es la única con el nombre igual en ambas tablas. De haber habido más columnas comunes, la unión las habría utilizado todas. Uniones Naturales con una Cláusula WHERE Las restricciones adicionales de una unión natural se implementan mediante la cláusula WHERE. El ejemplo siguiente limita las filas de salida a aquéllas con un identificador de departamento igual a 20 ó 50. SELECT

department_id, department_name, location_id, city FROM departments NATURAL JOIN locations WHERE department_id IN (20, 50);

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-7

Creación de Uniones con la Cláusula USING •

• • •

Si hay varias columnas que tienen los mismos nombres pero los tipos de datos no se corresponden, la cláusula NATURAL JOIN se puede modificar mediante la cláusula USING para especificar las columnas que se deben utilizar para una unión igualitaria. Utilice la cláusula USING para asignar sólo una columna cuando corresponde a más de una columna. No utilice un alias o un nombre de tabla en las columnas a las que se hace referencia. Las cláusulas NATURAL JOIN y USING se excluyen mutuamente. Copyright © 2004, Oracle. Todos los derechos reservados.

Cláusula USING Las uniones naturales pueden utilizar todas las columnas con nombres y tipos de datos correspondientes para unir las tablas. Se puede utilizar la cláusula USING para especificar únicamente las columnas que se deben utilizar para una unión igualitaria. Las columnas a las que se hace referencia en la cláusula USING no deben tener cualificador (nombre de tabla o alias) en ninguna parte de la sentencia SQL. Por ejemplo, esta sentencia es válida: SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE location_id = 1400;

La sentencia siguiente no es válida por que la cláusula WHERE cualifica a LOCATION_ID: SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE d.location_id = 1400; ORA-25154: column part of USING clause cannot have qualifier

Se aplica la misma restricción a las uniones naturales. Por tanto, las columnas con el mismo nombre en ambas tablas se deben utilizar sin cualificadores.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-8

Unión de Nombres de Columna

EMPLOYEES

DEPARTMENTS

…

… Clave ajena

Clave primaria

La cláusula USING para Uniones Igualitarias Para determinar el nombre de departamento de un empleado, compare el valor de la columna DEPARTMENT_ID de la tabla EMPLOYEES con los valores de DEPARTMENT_ID de la tabla DEPARTMENTS. La relación entre las tablas EMPLOYEES y DEPARTMENTS es una unión igualitaria (es decir, los valores de la columna DEPARTMENT_ID de ambas tablas deben ser iguales). Con frecuencia, este tipo de unión implica complementos de clave primaria y ajena. Nota: Las uniones igualitarias se denominan también uniones simples o uniones internas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-9

Recuperación de Registros con la Cláusula USING

SELECT employees.employee_id, employees.last_name, departments.location_id, department_id FROM employees JOIN departments USING (department_id) ;

…

Recuperación de Registros con la Cláusula USING El ejemplo de la diapositiva une la columna DEPARTMENT_ID de las tablas EMPLOYEES y DEPARTMENTS, con lo que muestra la ubicación en la que trabaja un empleado.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-10

Cualificación de Nombres de Columna Ambiguos • • •

•

Utilice prefijos de tabla para cualificar nombres de columna que estén en varias tablas. Utilice prefijos de tabla para mejorar el rendimiento. Utilice alias de columna para distinguir columnas que tengan nombres idénticos pero que residan en tablas diferentes. No utilice alias en columnas que estén identificadas en la cláusula USING y que se muestren en cualquier otra parte de la sentencia SQL.

Cualificación de Nombres de Columna Ambiguos Debe cualificar los nombres de las columnas con el nombre de la tabla para evitar ambigüedades. Sin los prefijos de tabla, la columna DEPARTMENT_ID en la lista SELECT podría ser de la tabla DEPARTMENTS o de la tabla EMPLOYEES. Resulta necesario agregar el prefijo de tabla para ejecutar la consulta: SELECT employees.employee_id, employees.last_name, departments.department_id, departments.location_id FROM employees JOIN departments ON employees.department_id = departments.department_id;

Si no existen nombres de columna comunes en las dos tablas, no es necesario cualificar las columnas. Sin embargo, utilizar el prefijo de tabla mejora el rendimiento, ya que le indica a Oracle Server exactamente dónde puede encontrar las columnas. Nota: Al realizar uniones mediante la cláusula USING, no puede cualificar una columna que se utilice en la propia cláusula USING. Lo que es más, si se utiliza esa columna en cualquier otra parte de la sentencia SQL, no puede aplicarle un alias.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-11

Uso de Alias de Tabla • •

Utilice alias de tabla para simplificar las consultas. Utilice alias de tabla para mejorar el rendimiento.

SELECT e.employee_id, e.last_name, d.location_id, department_id FROM employees e JOIN departments d USING (department_id);

Uso de Alias de Tabla Cualificar nombres de columna con nombres de tabla puede llevar mucho tiempo, especialmente si los nombres de tabla son largos. Puede utilizar alias de tabla en lugar de nombres de tabla. Al igual que los alias de columna cambian el nombre de las columnas, los alias de tabla hacen lo propio con las tablas. Los alias de tabla ayudan a reducir el código SQL, con lo que se consume menos memoria. Observe cómo se identifican en el ejemplo los alias de tabla de la cláusula FROM. El nombre de tabla se especifica entero, seguido de un espacio y, a continuación, el alias de tabla. Se le ha dado a la tabla EMPLOYEES el alias e y a la tabla DEPARTMENTS, el alias d. Instrucciones • Los alias de tabla pueden contener hasta 30 caracteres, pero son mejores los cortos que los largos. • Si se utiliza un alias de tabla para un nombre de tabla concreto en la cláusula FROM, se debe sustituir el nombre de tabla por alias de tabla en toda la sentencia SELECT. • Los alias de tabla deben ser significativos. • Los alias de tabla son válidos únicamente para la sentencia SELECT actual.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-12

Creación de Uniones con la Cláusula ON •

•

• •

La condición de unión para la unión natural es básicamente una unión igualitaria de todas las columnas con el mismo nombre. Utilice la cláusula ON para especificar condiciones arbitrarias o para especificar las columnas que se unirán. La condición de unión se separa de otras condiciones de búsqueda. La cláusula ON facilita la comprensión del código.

Cláusula ON Utilice la cláusula ON para especificar una condición de unión. Esto le permite especificar condiciones de unión aparte de cualquier condición de búsqueda o de filtro en la cláusula WHERE.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-13

Recuperación de Registros con la Cláusula ON

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id);

…

Creación de Uniones con la Cláusula ON En este ejemplo, las columnas DEPARTMENT_ID de las tablas EMPLOYEES y DEPARTMENTS se unen mediante la cláusula ON. Siempre que un identificador de departamento de la tabla EMPLOYEES sea igual a un identificador de departamento de la tabla DEPARTMENTS, se devuelve la fila. También puede utilizar la cláusula ON para unir columnas con nombres diferentes.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-14

Autouniones mediante la Cláusula ON

EMPLOYEES (WORKER)

EMPLOYEES (MANAGER)

…

Unión de una Tabla a Sí Misma A veces, tendrá que unir una tabla a sí misma. Para encontrar el nombre del supervisor de cada empleado, debe unir la tabla EMPLOYEES a sí misma o realizar una autounión. Por ejemplo, para encontrar el nombre del supervisor de Lorentz, debe: • Buscar Lorentz en la tabla EMPLOYEES en la columna LAST_NAME. • Buscar el número de supervisor para Lorentz en la columna MANAGER_ID. El número del supervisor de Lorentz es 103. • Buscar el nombre del supervisor con el valor EMPLOYEE_ID 103 en la columna LAST_NAME. El número de empleado de Hunold es 103, por lo que Hunold es el supervisor de Lorentz. En este proceso, se consulta la tabla dos veces. La primera vez, se consulta la tabla es para buscar Lorentz en la columna LAST_NAME y el valor MANAGER_ID de 103. La segunda vez, se consulta la columna EMPLOYEE_ID para buscar 103 y la columna LAST_NAME para buscar Hunold.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-15

Autouniones mediante la Cláusula ON

SELECT e.last_name emp, m.last_name mgr FROM employees e JOIN employees m ON (e.manager_id = m.employee_id);

…

Unión de una Tabla a Sí Misma (continuación) La cláusula ON también se puede utilizar para unir columnas con nombres diferentes, dentro de la misma tabla o en una tabla diferente. El ejemplo que se muestra en una autounión de la tabla EMPLOYEES, basada en las columnas EMPLOYEE_ID y MANAGER_ID.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-16

Aplicación de Condiciones Adicionales a una Unión SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149 ;

Aplicación de Condiciones Adicionales a una Unión Puede aplicar condiciones adicionales a la unión. El ejemplo realiza una unión de las tablas EMPLOYEES y DEPARTMENTS y, además, muestra sólo a los empleados con el identificador de supervisor 149. Para agregar condiciones adicionales a la cláusula ON, puede agregar cláusulas AND. De forma alternativa, puede utilizar una cláusula WHERE para aplicar condiciones adicionales. SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.manager_id = 149;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-17

Creación de Uniones en Tres Sentidos con la Cláusula ON SELECT FROM JOIN ON JOIN ON

employee_id, city, department_name employees e departments d d.department_id = e.department_id locations l d.location_id = l.location_id;

…

Uniones en Tres Sentidos Una unión en tres sentidos es una unión de tres tablas. En la sintaxis compatible con SQL:1999, las uniones se realizan de izquierda a derecha. De esta forma, la primera unión que se debe realizar es EMPLOYEES JOIN DEPARTMENTS. La primera condición de unión puede hacer referencia a columnas de EMPLOYEES y DEPARTMENTS pero no puede hacer referencia a columnas de LOCATIONS. La segunda condición de unión puede hacer referencia a columnas de las tres tablas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-18

Uniones No Igualitarias

EMPLOYEES

JOB_GRADES

…

Uniones No Igualitarias Una unión no igualitaria es aquélla que contiene algo distinto a un operador de igualdad. La relación entre las tablas EMPLOYEES y JOB_GRADES es un ejemplo de unión no igualitaria. Una relación entre las dos tablas es que la columna SALARY de la tabla EMPLOYEES debe estar entre los valores de las columnas LOWEST_SALARY y HIGHEST_SALARY de la tabla JOB_GRADES. La relación se obtiene mediante un operador distinto al de igualdad (=).

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-19

Recuperación de Registros con Uniones No Igualitarias SELECT e.last_name, e.salary, j.grade_level FROM employees e JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

…

Uniones No Igualitarias (continuación) El ejemplo de la diapositiva crea una unión no igualitaria para evaluar el grado del salario de un empleado. El salario debe estar entre cualquier par de los rangos de salario bajo y alto. Observe que todos los empleados aparecen exactamente una vez al ejecutarse esta consulta. No se repite ningún empleado en la lista. Hay dos motivos: • Ninguna de las filas de la tabla de grados de trabajo contiene grados que se solapen. Es decir, el valor del salario para un empleado sólo puede quedar entre los valores de salario bajo y salario alto de una de las filas de la tabla de grados de salarios. • Los salarios de todos los empleados quedan dentro de los límites que proporciona la tabla de grados de trabajo. Es decir, ningún empleado gana menos del valor más bajo de la columna LOWEST_SAL ni más del valor más alto de la columna HIGHEST_SAL. Nota: Se pueden utilizar otras condiciones (como <= y >=), pero BETWEEN es la más sencilla. Recuerde especificar el valor bajo en primer lugar y el alto al final cuando utilice BETWEEN. Se han especificado los alias de tabla en el ejemplo de la diapositiva por motivos de rendimiento, no por posibles ambigüedades.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-20

Uniones Externas

DEPARTMENTS

EMPLOYEES

Devolución de Registros sin Correspondencias Directas con Uniones Externas Si una fila no satisface una condición de unión, no aparecerá en el resultado de la consulta. Por ejemplo, en la condición de unión igualitaria de las tablas EMPLOYEES y DEPARTMENTS, el identificador de departamento 190 no aparece porque no hay empleados con ese identificador de departamento registrado en la tabla EMPLOYEES. En lugar de ver 20 empleados en el juego de resultados, sólo verá 19. Para devolver el registro de departamento que no tiene empleados, puede utilizar una unión externa.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-21

Uniones INNER frente a OUTER •

•

En SQL:1999, la unión de dos tablas que devuelve sólo filas con correspondencia se denomina unión interna. Una unión entre dos tablas que devuelve los resultados de la unión interna y las filas sin correspondencia de las tablas a la izquierda (o derecha) se denomina unión externa izquierda (o derecha). Una unión entre dos tablas que devuelve los resultados de la unión interna y los resultados de una unión izquierda y derecha es una unión externa completa. Copyright © 2004, Oracle. Todos los derechos reservados.

Uniones INNER frente a OUTER Unir tablas con las cláusulas NATURAL JOIN, USING u ON da como resultado una unión interna. Las filas sin correspondencias no se muestran en la salida. Para devolver las filas sin correspondencias, puede utilizar una unión externa. Una unión externa devuelve todas las filas que satisfacen la condición de unión y devuelve también algunas o todas las filas de una tabla para las que ninguna fila de la otra tabla satisface la condición de unión. Hay tres tipos de uniones externas: • LEFT OUTER • RIGHT OUTER • FULL OUTER

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-22

LEFT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ;

…

Ejemplo de LEFT OUTER JOIN Esta consulta recupera todas las filas de la tabla EMPLOYEES, que es la tabla izquierda aunque no haya correspondencias en la tabla DEPARTMENTS.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-23

RIGHT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ;

…

Ejemplo de RIGHT OUTER JOIN Esta consulta recupera todas las filas de la tabla DEPARTMENTS, que es la tabla derecha aunque no haya correspondencias en la tabla EMPLOYEES.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-24

FULL OUTER JOIN

SELECT e.last_name, d.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ;

…

Ejemplo de FULL OUTER JOIN Esta consulta recupera todas las filas de la tabla EMPLOYEES, aunque no haya correspondencias en la tabla DEPARTMENTS. También recupera todas las filas de la tabla DEPARTMENTS, aunque no haya correspondencias en la tabla EMPLOYEES.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-25

Productos Cartesianos •

Se forma un producto cartesiano cuando: – Se omite una condición de unión – Una condición de unión no es válida – Todas las filas de la primera tabla se unen a todas las filas de la segunda tabla

•

Para evitar un producto cartesiano, incluya siempre una condición de unión válida.

Productos Cartesianos Cuando una condición de unión no es válida o se omite por completo, el resultado es un producto cartesiano, en el que se muestran todas las combinaciones de filas. Todas las filas de la primera tabla se unen a todas las filas de la segunda tabla. Un producto cartesiano tiende a generar gran cantidad de filas, con lo que el resultado no suele ser de utilidad. Debería incluir siempre una condición de unión válida a menos que tenga la necesidad específica de combinar todas las filas de todas las tablas. Los productos cartesianos resultan útiles si necesita generar gran número de filas para simular una cantidad aceptable de datos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-26

Generación de Productos Cartesianos

EMPLOYEES (20 filas)

DEPARTMENTS (8 filas)

…

Producto cartesiano: 20 x 8 = 160 filas

Productos Cartesianos (continuación) Se genera un producto cartesiano si se omite una condición de unión. El ejemplo de la diapositiva muestra el apellido del empleado y el nombre del departamento de las tablas EMPLOYEES y DEPARTMENTS. Como no se ha especificado ninguna condición de unión, todas las filas (20 filas) de la tabla EMPLOYEES se unen a todas las filas (8 filas) de la tabla DEPARTMENTS, con lo que se generan 160 filas en la salida.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-27

Creación de Uniones Cruzadas • •

La cláusula CROSS JOIN genera el producto combinado de dos tablas. También se denomina un producto cartesiano entre las dos tablas.

SELECT last_name, department_name FROM employees CROSS JOIN departments ;

Creación de Uniones Cruzadas El ejemplo de la diapositiva genera un producto cartesiano de las tablas EMPLOYEES y DEPARTMENTS.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-28

Resumen En esta lección, ha aprendido a utilizar uniones para mostrar datos de varias tablas mediante: • Uniones igualitarias • Uniones no igualitarias • Uniones externas • Autouniones • Uniones cruzadas • Uniones naturales • Uniones externas completas (o de dos lados)

Resumen Existen varias formas de unir tablas. Tipos de Uniones • Uniones igualitarias • Uniones no igualitarias • Uniones externas • Autouniones • Uniones cruzadas • Uniones naturales • Uniones externas completas (o de dos lados) Productos Cartesianos Un producto cartesiano da como resultado la visualización de todas las combinaciones de filas. Esto se consigue omitiendo la cláusula WHERE o especificando la cláusula CROSS JOIN. Alias de Tabla • Los alias de tabla aceleran el acceso a la base de datos. • Los alias de tabla ayudan a reducir el código SQL y así conservar memoria.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-29

Práctica 5: Visión General Esta práctica cubre los temas siguientes: • Unión de tablas mediante una unión igualitaria • Realización de uniones externas y autouniones • Agregación de condiciones

Práctica 5: Visión General En esta práctica se pretende que reciba experiencia práctica en la extracción de datos de más de una tabla mediante uniones compatibles con SQL:1999.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-30

Práctica 5 1. Escriba una consulta para que el departamento de recursos humanos genere las direcciones de todos los departamentos. Utilice las tablas LOCATIONS y COUNTRIES. Muestre en la salida el identificador de ubicación, el domicilio de calle, la ciudad, el estado o la provincia y el país. Utilice una unión natural para generar los resultados.

2. El departamento de recursos humanos necesita un informe de todos los empleados. Escriba una consulta para mostrar el apellido, el número de departamento y el nombre de departamento de todos los empleados.

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-31

Práctica 5 (continuación) 3. El departamento de recursos humanos necesita un informe de los empleados de Toronto. Muestre el apellido, el puesto, el número de departamento y el nombre de departamento de todos los empleados que trabajan en Toronto.

4. Cree un informe que muestre el apellido del empleado y el número de empleado junto al apellido y número de supervisor del supervisor del empleado. Etiquete las columnas como Employee, Emp#, Manager y Mgr#, respectivamente. Guarde la sentencia SQL en un archivo de texto denominado lab_05_04.sql.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-32

Práctica 5 (continuación) 5. Modifique lab_05_04.sql para que muestre todos los empleados, incluido King, que no tiene supervisor. Ordene los resultados por el número de empleado. Guarde la sentencia SQL en un archivo de texto denominado lab_05_05.sql. Ejecute la consulta en lab_05_05.sql.

… 6. Cree un informe para el departamento de recursos humanos que muestre los apellidos, los números de departamento y todos los empleados que trabajan en el mismo departamento que un empleado dado. Proporcione a cada columna una etiqueta adecuada. Guarde el archivo de comandos en un archivo denominado lab_05_06.sql.

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-33

Práctica 5 (continuación) 7. El departamento de recursos humanos necesita un informe sobre grados de trabajo y salarios. Para familiarizarse con la tabla JOB_GRADES, muestre en primer lugar su estructura. A continuación, cree una consulta que muestre el apellido, el puesto, el nombre de departamento, el salario y el grado de todos los empleados.

… Si desea afrontar un desafío mayor, realice estos ejercicios: 8. El departamento de recursos humanos quiere determinar los nombres de todos los empleados contratados después de Davies. Cree una consulta para mostrar el nombre y la fecha de contratación de cualquier empleado contratado después que Davies.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-34

Práctica 5 (continuación) 9. El departamento de recursos humanos necesita encontrar el nombre y la fecha de contratación de todos los empleados contratados antes que sus supervisores, junto con los nombres y las fechas de contratación de éstos. Guarde el archivo de comandos en un archivo denominado lab5_09.sql.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-35

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 5-36

Uso de Subconsultas para Resolver Consultas

Objetivos Al finalizar esta lección, debería estar capacitado para: • Definir subconsultas • Describir los tipos de problemas que pueden resolver las subconsultas • Enumerar los tipos de subconsultas • Escribir subconsultas de una sola fila y de varias filas

Objetivos En esta lección, obtendrá información sobre funciones más avanzadas de la sentencia SELECT. Puede escribir subconsultas en la cláusula WHERE de otra sentencia SQL para obtener valores basados en un valor condicional desconocido. Esta lección trata subconsultas de una sola fila y subconsultas de varias filas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-2

Uso de una Subconsulta para Resolver un Problema ¿Quién tiene un salario mayor que Abel? Consulta principal:

¿Qué empleados tienen salarios mayores que el de Abel? Subconsulta:

¿Cuál es el salario de Abel?

Uso de Subconsultas para Resolver Problemas Supongamos que desea escribir una consulta para averiguar quién gana un salario mayor que el de Abel. Para resolver este problema, necesita dos consultas: una para buscar cuánto gana Abel y una segunda para buscar quién gana más que esa cantidad. Puede resolver este problema combinando las dos consultas, colocando una consulta dentro de la otra consulta. La consulta interna (o subconsulta) devuelve un valor que se utiliza en la otra consulta (o consulta principal). Utilizar una subconsulta es equivalente a realizar dos consultas secuenciales y utilizar el resultado de la primera consulta como valor de búsqueda en la segunda consulta.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-3

Sintaxis de Subconsultas

SELECT FROM WHERE

•

select_list table expr operator (SELECT FROM

select_list table);

La subconsulta (consulta interna) se ejecuta una vez antes de la consulta principal (consulta externa). La consulta principal utiliza el resultado de la subconsulta.

Sintaxis de Subconsultas Una subconsulta es una sentencia SELECT que está embebida en una cláusula de otra sentencia SELECT. Puede crear sentencias potentes sentencias a partir de otras más simples mediante subconsultas. Pueden resultar muy útiles si necesita seleccionar filas de una tabla con una condición que depende de los datos de la propia tabla. Puede colocar la subconsulta en diferentes cláusulas SQL, como éstas: • Cláusula WHERE • Cláusula HAVING • Cláusula FROM En la sintaxis: operator incluye una condición de comparación como, por ejemplo, >, = o IN Nota: Hay dos clases de condiciones de comparación: operadores de una sola fila (>, =, >=, <, <>, <=) y operadores de varias filas (IN, ANY, ALL). La subconsulta se suele conocer como sentencia SELECT anidada, subSELECT o SELECT interna. La subconsulta se suele ejecutar en primer lugar y se utiliza su salida para completar la condición de consulta para la consulta principal (o externa).

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-4

Uso de Subconsultas

SELECT last_name 11000 FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

Uso de Subconsultas En la diapositiva, la consulta interna determina el salario del empleado Abel. La consulta externa toma el resultado de la consulta interna y lo utiliza para mostrar todos los empleados que ganan mĂĄs que esta cantidad.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-5

Instrucciones para el Uso de Subconsultas • •

Escriba las subconsultas entre paréntesis. Sitúe las subconsultas a la derecha de la condición de comparación. La cláusula ORDER BY de la subconsulta no es necesaria a menos que se realice un análisis de los N principales. Utilice operadores de una sola fila con subconsultas de una sola fila y operadores de varias filas con subconsultas de varias filas.

•

Instrucciones para el Uso de Subconsultas • Las subconsultas deben ir entre paréntesis. • Sitúe las subconsultas a la derecha de la condición de comparación para que se lean mejor. •

Con Oracle8i y versiones posteriores, se puede utilizar una cláusula ORDER BY y es necesaria en la subconsulta para realizar análisis de los N principales. -

•

Antes Oracle8i, sin embargo, las subconsultas no podían contener una cláusula ORDER BY. Sólo se podía utilizar una cláusula ORDER BY para una sentencia SELECT; si se especificaba, tenía que ser la última cláusula de la sentencia SELECT principal. Se utilizan dos clases de condiciones de comparación en las subconsultas: operadores de una sola fila y operadores de varias filas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-6

Tipos de Subconsultas •

Subconsulta de una sola fila Consulta principal Subconsulta

•

devuelve

ST_CLERK

Subconsulta de varias filas Consulta principal Subconsulta

devuelve

ST_CLERK SA_MAN

Tipos de Subconsultas • Subconsultas de una sola fila: Consultas que devuelven sólo una fila de la sentencia SELECT interna. • Subconsultas de varias filas: Consultas que devuelven más de una fila de la sentencia SELECT interna. Nota: También hay subconsultas de varias columnas, que son consultas que devuelven más de una columna de la sentencia SELECT interna. Se tratan en el curso Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-7

Subconsultas de Una Sola Fila • •

Devuelven sólo una fila Utilizan operadores de comparación de una sola fila Operador Significado = Igual que >

Mayor que

Mayor o igual que

Menor que

Menor o igual que

Distinto de

Subconsultas de Una Sola Fila Una subconsulta de una sola fila es la que devuelve una fila de la sentencia SELECT interna. Este tipo de subconsulta utiliza un operador de una sola fila. La diapositiva ofrece una lista de operadores de una sola fila. Ejemplo Muestre los empleados cuyos identificadores de puesto sean los mismos que los del empleado 141: SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141);

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-8

Ejecución de Subconsultas de Una Sola Fila

SELECT last_name, job_id, salary FROM employees ST_CLERK WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > 2600 (SELECT salary FROM employees WHERE employee_id = 143);

Ejecución de Subconsultas de Una Sola Fila Una sentencia SELECT se puede considerar un bloque de consulta. El ejemplo de la diapositiva muestra los empleados cuyos identificadores de puesto son iguales que el del empleado 141 y cuyos salarios son mayores que el del empleado 143. El ejemplo consta de tres bloques de consulta: la consulta externa y dos consultas internas. Los bloques de consultas internas se ejecutan en primer lugar, con lo que se crean los resultados de consulta ST_CLERK y 2600, respectivamente. A continuación se procesa el bloque de la consulta externa, utilizando los valores que devolvieron las consultas internas para completar sus condiciones de búsqueda. Las dos consultas internas devuelven valores únicos (ST_CLERK y 2600, respectivamente), por lo cual esta sentencia SQL se denomina subconsulta de una sola fila. Nota: Las consultas externas e internas pueden obtener datos de tablas diferentes.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-9

Uso de Funciones de Grupo en una Subconsulta

SELECT last_name, job_id, salary 2500 FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

Uso de Funciones de Grupo en una Subconsulta Puede mostrar datos de una consulta principal mediante una función de grupo en una subconsulta para devolver una sola fila. La subconsulta está entre paréntesis y se coloca después de la condición de comparación. El ejemplo de la diapositiva muestra el apellido de empleado, el identificador y el salario de todos los empleados cuyo salario sea igual al salario mínimo. La función de grupo MIN devuelve un solo valor (2500) a la consulta externa.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-10

La Cláusula HAVING con Subconsultas • •

Oracle Server ejecuta las subconsultas en primer lugar. Oracle Server devuelve los resultados en la cláusula HAVING de la consulta principal.

SELECT FROM GROUP BY HAVING

department_id, MIN(salary) employees department_id 2500 MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

La Cláusula HAVING con Subconsultas Puede utilizar subconsultas no sólo en la cláusula WHERE, sino también en la cláusula HAVING. Oracle Server ejecuta la subconsulta y los resultados se devuelven en la cláusula HAVING de la cláusula principal. La sentencia SQL de la diapositiva muestra todos los departamentos que tienen un salario mínimo mayor que el del departamento 50.

… Ejemplo Busque el puesto con el salario medio más bajo. SELECT FROM GROUP BY HAVING

job_id, AVG(salary) employees job_id AVG(salary) = (SELECT MIN(AVG(salary)) FROM employees GROUP BY job_id);

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-11

¿Qué Problema Hay en Esta Sentencia?

SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id); ERROR at line 4: ORA-01427: single-row subquery returns more than one row

Operador de una sola fila con subconsulta de varias filas

Errores en las Subconsultas Un error común en las subconsultas se produce cuando se devuelve más de una fila para una subconsulta de una sola fila. En la sentencia SQL de la diapositiva, la subconsulta contiene una cláusula GROUP BY, lo que implica que devolverá varias filas, una para cada grupo que encuentre. En este caso, los resultados de la subconsulta son 4400, 6000, 2500, 4200, 7000, 17000 y 8300. La consulta externa toma estos resultados y los utiliza en su cláusula WHERE. La cláusula WHERE contiene un operador de igualdad (=), operador de comparación de una sola fila que espera sólo un valor. El operador = no puede aceptar más de un valor de la subconsulta y, por tanto, genera el error. Para corregir este error, cambie el operador = a IN.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-12

¿Devolverá Filas Esta Sentencia?

SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas'); no rows selected

La subconsulta no devuelve ningún valor.

Problemas con las Subconsultas Un problema habitual de las subconsultas se produce cuando la consulta interna no devuelve ninguna fila. En la sentencia SQL de la diapositiva, la subconsulta contiene una cláusula WHERE. Presumiblemente, la intención es buscar el empleado cuyo apellido es Haas. La sentencia es correcta, pero no selecciona ninguna fila cuando se ejecuta. No hay ningún empleado llamado Haas. Así pues, la subconsulta no devuelve ninguna fila. La consulta externa toma los resultados de la subconsulta (nulos) y utiliza esos resultados en su cláusula WHERE. La salida externa no encuentra ningún empleado cuyo identificador de puesto sea igual a nulo, así que no devuelve ninguna fila. Si existiera un puesto con un valor nulo, la fila no se devolvería porque la comparación de dos valores nulos genera un valor nulo; así pues, la condición WHERE no es verdadera.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-13

Subconsultas de Varias Filas • •

Devuelven más de una fila Utilizan operadores de comparación de varias filas Operador

Significado

Igual que algún miembro de la lista

ANY

Compara el valor con cada valor devuelto por la subconsulta

ALL

Compara el valor con todos los valores devueltos por la subconsulta

Subconsultas de Varias Filas Las subconsultas que devuelven más de una fila se denominan subconsultas de varias filas. Puede utilizar operadores de varias filas, en lugar de los de una sola fila, con las subconsultas de varias filas El operador de varias filas espera uno o más valores: SELECT last_name, salary, department_id FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);

Ejemplo Busque los empleados que ganan un salario igual al salario mínimo para cada departamento. La consulta interna se ejecuta en primer lugar, generando un resultado de consulta. A continuación se procesa el bloque de consulta principal y utiliza los valores que devolvió la consulta interna para completar su condición de búsqueda. De hecho, la consulta principal aparece en Oracle Server de este modo: SELECT last_name, salary, department_id FROM employees WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-14

Uso del Operador ANY en Subconsultas de Varias Filas SELECT employee_id, last_name, job_id, salary FROM employees 9000, 6000, 4200 WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

…

Subconsultas de Varias Filas (continuación) El operador ANY (y su sinónimo, el operador SOME) compara un valor con cada valor devuelto por la subconsulta. El ejemplo de la diapositiva muestra empleados que no son programadores informáticos y cuyo salario es menor que el de cualquier programador informático. El salario máximo que gana un programador es de 9.000 dólares. <ANY significa menos que el máximo. >ANY significa más que el mínimo. =ANY es equivalente a IN.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-15

Uso del Operador ALL en Subconsultas de Varias Filas SELECT employee_id, last_name, job_id, salary FROM employees 9000, 6000, 4200 WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

Subconsultas de Varias Filas (continuación) El operador ALL compara un valor con todos los valores devueltos por una subconsulta. El ejemplo de la diapositiva muestra los empleados cuyos salarios son menores que los de todos los empleados con el identificador de puesto IT_PROG y cuyos puestos no son IT_PROG. >ALL significa más que el máximo y <ALL, menos que el mínimo. El operador NOT se puede utilizar con los operadores IN, ANY y ALL.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-16

Valores Nulos en una Subconsulta

SELECT emp.last_name FROM employees emp WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr); no rows selected

Devolución de Valores Nulos en el Juego Resultante de una Subconsulta La sentencia SQL de la diapositiva intenta mostrar todos los empleados que no tienen subordinados. Por lógica, esta sentencia SQL debería haber devuelto 12 filas. Sin embargo, la sentencia SQL no devuelve ninguna fila. Uno de los valores devueltos por la consulta interna es un valor nulo y por eso la consulta entera no devuelve ninguna fila. El motivo es que todas las condiciones que comparan un valor nulo dan como resultado un valor nulo. Así pues, siempre que sea probable que en el juego de resultados de una subconsulta haya valores nulos, no utilice el operador NOT IN. El operador NOT IN es equivalente a <> ALL. Observe que no hay problema en que en el juego de resultados de una subconsulta haya valores nulos si se utiliza el operador IN. El operador IN es equivalente a =ANY. Por ejemplo, para mostrar los empleados que tienen subordinados, utilice esta sentencia SQL: SELECT emp.last_name FROM employees emp WHERE emp.employee_id

IN (SELECT mgr.manager_id FROM employees mgr);

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-17

Devoluci贸n de Valores Nulos en el Juego Resultante de una Subconsulta (continuaci贸n) De forma alternativa, se puede incluir una cl谩usula WHERE en la subconsulta para mostrar todos los empleados que no tienen subordinados: SELECT last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-18

Resumen En esta lección ha aprendido a: • Identificar cuándo puede ayudar una subconsulta a resolver una cuestión • Escribir subconsultas cuando una consulta se base en valores desconocidos SELECT FROM WHERE

select_list table expr operator (SELECT select_list FROM table);

Resumen En esta lección ha aprendido a utilizar subconsultas. Una subconsulta es una sentencia SELECT que está embebida en una cláusula de otra sentencia SQL. Las subconsultas son útiles cuando una consulta se basa en un criterio de búsqueda con valores intermedios desconocidos. Las subconsultas tienen estas características: • Pueden transferir una fila de datos a una sentencia principal que contenga un operador de una sola fila como, por ejemplo, =, <>, >, >=, < o <= • Pueden transferir varias filas de datos a una sentencia principal que contenga un operador de varias filas como, por ejemplo, IN • Oracle Server las procesa en primer lugar, después de lo cual las cláusulas WHERE o HAVING utilizan los resultados • Pueden contener funciones de grupo

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-19

Práctica 6: Visión General Esta práctica cubre los temas siguientes: • Creación de subconsultas para consultar valores basándose en criterios desconocidos • Uso de subconsultas para averiguar los valores que existen en un juego de datos y no en otros

Práctica 6: Visión General En esta práctica, escribirá consultas complejas mediante sentencias SELECT anidadas. Preguntas sobre Papel Es preferible que cree primero la consulta interna para estas preguntas. Asegúrese de que se ejecuta y de que genera los datos que prevé antes de codificar la consulta externa.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-20

Práctica 6 1. El departamento de recursos humanos necesita una consulta que pida al usuario el apellido de un empleado. La consulta muestra entonces el apellido y la fecha de contratación de cualquier empleado del mismo departamento que aquel cuyo apellido se suministre (excepto ese empleado). Por ejemplo, si el usuario introduce Zlotkey, busca todos los empleados que trabajen con Zlotkey (excepto Zlotkey).

2. Cree un informe que muestre el número de empleado y el apellido de todos los empleados que ganan más que el sueldo medio. Clasifique los resultados en orden ascendente de salario.

3. Escriba una consulta que muestre el número de empleado y el apellido de todos los empleados que trabajen en un departamento en que haya algún empleado cuyo apellido contenga una u. Guarde la sentencia SQL en un archivo de texto denominado lab_06_03.sql. Ejecute la consulta.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-21

Práctica 6 (continuación) 4. El departamento de recursos humanos necesita un informe que muestre el apellido, el número de departamento y el identificador de puesto de todos los empleados cuyos identificadores de ubicación de departamento sean 1700.

Modifique la consulta para que se pida al usuario un identificador de ubicación. Guarde esto en un archivo denominado lab_06_04.sql. 5. Cree un informe para recursos humanos que muestre el apellido y el salario de todos los empleados que informen a King.

6. Cree un informe para recursos humanos que muestre el número de departamento, el apellido y el identificador de puesto de todos los empleados del departamento ejecutivo.

Si le queda tiempo, realice el siguiente ejercicio: 7. Modifique la consulta en lab_06_03.sql para mostrar el número de empleado, el apellido y el salario de todos los empleados que ganen más que el salario medio y que trabajen en un departamento con algún empleado cuyo apellido contenga una u. Vuelva a guardar lab_06_03.sql como lab_06_07.sql. Ejecute la sentencia en lab_06_07.sql.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 6-22

Uso de Operadores SET

Objetivos Al finalizar esta lección, debería estar capacitado para: • Describir operadores SET • Utilizar un operador SET para combinar varias consultas en una sola consulta • Controlar el orden de las filas devueltas

Objetivos En esta lección, aprenderá a escribir consultas mediante operadores SET.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-2

Operadores SET A

UNION/UNION ALL A

INTERSECT

MINUS

Operadores SET Los operadores SET combinan los resultados de consultas de dos o más componentes en un resultado. Las consultas que contienen operadores SET se denominan consultas compuestas. Operador

Devuelve

UNION

Todas las filas distintas seleccionadas por cualquiera de las dos consultas

UNION ALL

Todas las filas seleccionadas por cualquiera de las dos consultas, incluidos todos los duplicados

INTERSECT

Todas las filas distintas seleccionadas por ambas consultas

MINUS

Todas las filas seleccionadas por la primera sentencia SELECT y no seleccionadas en la segunda sentencia SELECT

Todos los operadores SET tienen la misma prioridad. Si una sentencia SQL contiene varios operadores SET, Oracle Server las evaluará de izquierda (arriba) a derecha (abajo) si no hay paréntesis que especifiquen explícitamente otro orden. Debe utilizar paréntesis para especificar explícitamente el orden de evaluación en consultas que utilicen el operador INTERSECT con otros operadores SET.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-3

Tablas Utilizadas en Esta Lección Las tablas utilizadas en esta lección son: • EMPLOYEES: Proporciona detalles relativos a todos los empleados actuales • JOB_HISTORY: Registra los detalles de las fechas de inicio y de finalización del primer puesto, así como el número de identificación del puesto y el departamento cuando un empleado cambia de puesto

Tablas Utilizadas en Esta Lección Se utilizan dos tablas en esta lección: EMPLOYEES y JOB_HISTORY. La tabla EMPLOYEES almacena detalles de los empleados. Para los registros de recursos humanos, esta tabla almacena un número de identificación y una dirección de correo electrónico únicos para cada empleado. También se almacenan los detalles de número de identificación de puesto, salario y supervisor del empleado. Algunos empleados ganan una comisión aparte de su salario; también se realiza un seguimiento de esta información. La compañía organiza los roles de los empleados en puestos. Algunos de los empleados llevan en la compañía mucho tiempo y han ocupado puestos diferentes. Esto se controla mediante la tabla JOB_HISTORY. Si un empleado cambia de puesto, se registran en la tabla JOB_ HISTORY los detalles de las fechas de inicio y de finalización del primer puesto, el número de identificación del puesto y el departamento. La estructura y los datos de las tablas EMPLOYEES y JOB_HISTORY se muestran en las próximas páginas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-4

Tablas Utilizadas en Esta Lección (continuación) Se han dado casos en la compañía de personas que han ocupado el mismo puesto más de una vez durante su estancia en la compañía. Por ejemplo, existe el caso del empleado Taylor, que llegó a la compañía en la fecha 24-MAR-1998. Taylor ocupó el cargo SA_REP desde 24MAR-98 a 31-DEC-98 y el cargo SA_MAN de 01-JAN-99 a 31-DEC-99. Taylor volvió al cargo SA_REP, que es su cargo actual. De forma parecida, Whalen llegó a la compañía en la fecha 17-SEP-1987 y ocupó el cargo AD_ASST de 17-SEP-87 a 17-JUN-93 y el cargo AC_ACCOUNT de 01-JUL-94 a 31-DEC-98. Whalen volvió al cargo AD_ASST, que es su cargo actual. DESCRIBE employees

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-5

Tablas Utilizadas en Esta Lección (continuación) SELECT employee_id, last_name, job_id, hire_date, department_id FROM employees;

… DESCRIBE job_history

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-6

Tablas Utilizadas en Esta Lecci贸n (continuaci贸n) SELECT * FROM job_history;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-7

Operador UNION

Operador UNION El operador UNION devuelve todas las filas seleccionadas por cualquiera de las dos consultas. Utilice el operador UNION para devolver todas las filas de varias tablas y eliminar las filas duplicadas. Instrucciones • El número de columnas y los tipos de datos de las columnas que se están seleccionando deben ser idénticos en todas las sentencias SELECT utilizadas en la consulta. No es necesario que los nombres de las columnas sean idénticos. • UNION opera sobre todas las columnas que se están seleccionando. • Los valores NULL no se ignoran durante la comprobación de duplicados. • El operador IN tiene una prioridad más alta que el operador UNION. • Por defecto, la salida se clasifica por orden ascendente de la primera columna de la cláusula SELECT.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-8

Uso del Operador UNION Muestre los detalles del puesto actual y anterior de todos los empleados. Muestre cada empleado sólo una vez. SELECT FROM UNION SELECT FROM

employee_id, job_id employees employee_id, job_id job_history;

… …

Uso del Operador UNION El operador UNION elimina los registros duplicados. Si los registros creados en las tablas EMPLOYEES y JOB_HISTORY son idénticos, sólo se muestran una vez. Observe en la salida de la diapositiva que el registro para el empleado con EMPLOYEE_ID 200 aparece dos veces porque JOB_ID es diferente en cada fila. Observe el ejemplo siguiente: SELECT FROM UNION SELECT FROM

employee_id, job_id, department_id employees employee_id, job_id, department_id job_history;

… … Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-9

Uso del Operador UNION (continuación) En la salida anterior, el empleado 200 aparece tres veces. ¿Por qué? Observe los valores de DEPARTMENT_ID para el empleado 200. Una fila tiene un DEPARTMENT_ID de 90, otro, de 10 y el tercero, de 90. Debido a las combinaciones únicas de identificadores de puesto y de departamento, cada fila para el empleado 200 es única y, por tanto, no se considera un duplicado. Observe que la salida se clasifica por orden ascendente de la primera columna de la cláusula SELECT (en este caso, EMPLOYEE_ID).

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-10

Operador UNION ALL

Operador UNION ALL Utilice el operador UNION ALL para devolver todas las filas de varias consultas. Instrucciones Las instrucciones para UNION y UNION ALL son las mismas, con estas dos excepciones relacionadas con UNION ALL: • A diferencia de lo que sucede con UNION, las filas duplicadas no se eliminan y la salida no se ordena por defecto. • No se puede utilizar la palabra clave DISTINCT.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-11

Uso del Operador UNION ALL Muestre el departamento actual y los anteriores de todos los empleados. SELECT employee_id, job_id, department_id FROM employees UNION ALL SELECT employee_id, job_id, department_id FROM job_history ORDER BY employee_id;

… …

Operador UNION ALL (continuación) En el ejemplo, se han seleccionado 30 filas. La combinación de las dos tablas asciende a un total de 30 filas. El operador UNION ALL no elimina las filas duplicadas. UNION devuelve todas las filas distintas seleccionadas por cualquiera de las dos consultas. UNION ALL devuelve todas las filas seleccionadas por cualquiera de las dos consultas, incluidos todos los duplicados. Observe la consulta de la diapositiva, que ahora está escrita con la cláusula UNION: SELECT FROM UNION SELECT FROM ORDER BY

employee_id, job_id,department_id employees employee_id, job_id,department_id job_history employee_id;

La consulta anterior devuelve 29 filas. Esto se debe a que elimina la fila siguiente (por ser un duplicado):

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-12

Operador INTERSECT

Operador INTERSECT Utilice el operador INTERSECT para devolver las filas que son comunes a varias consultas. Instrucciones • El número de columnas y los tipos de datos de las columnas que se están seleccionando mediante las sentencias SELECT deben ser idénticos en todas las sentencias SELECT utilizadas en la consulta. No es necesario que los nombres de las columnas sean idénticos. • Invertir el orden de las tablas interseccionadadas no altera el resultado. • INTERSECT no ignora los valores NULL.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-13

Uso del Operador INTERSECT Muestre los identificadores de empleado y de puesto de los empleados que actualmente tengan un cargo igual al que tenían cuando se los contrató inicialmente (es decir, han cambiado de puesto pero han vuelto a ocupar su puesto original). SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history;

Operador INTERSECT (continuación) En el ejemplo de la diapositiva, la consulta devuelve los registros que tienen los mismos valores en las columnas seleccionadas en ambas tablas. ¿Cuáles serán los resultados si agrega la columna DEPARTMENT_ID a la sentencia SELECT de la tabla EMPLOYEES y agrega la columna DEPARTMENT_ID a la sentencia SELECT de la tabla JOB_HISTORY y ejecuta esta consulta? Los resultados pueden ser diferentes debido a la introducción de otra columna cuyos valores puedan ser duplicados o no. Ejemplo SELECT employee_id, job_id, department_id FROM employees INTERSECT SELECT employee_id, job_id, department_id FROM job_history;

El empleado 200 ya no forma parte de los resultados porque el valor de EMPLOYEES.DEPARTMENT_ID es diferente al valor de JOB_ HISTORY.DEPARTMENT_ID.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-14

Operador MINUS

Operador MINUS Utilice el operador MINUS para devolver las filas devueltas por la primera consulta que no estén presentes en la segunda (la primera sentencia SELECT menos (MINUS) la segunda sentencia SELECT). Instrucciones • El número de columnas y los tipos de datos de las columnas que se están seleccionando mediante las sentencias SELECT deben ser idénticos en todas las sentencias SELECT utilizadas en la consulta. No es necesario que los nombres de las columnas sean idénticos. • Para que funcione el operador MINUS, todas las columnas de la cláusula WHERE deben estar en la cláusula SELECT.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-15

Operador MINUS Muestre los identificadores de empleado de los empleados que no hayan cambiado de puesto ninguna vez. SELECT FROM MINUS SELECT FROM

employee_id,job_id employees employee_id,job_id job_history;

…

Operador MINUS (continuación) En el ejemplo de la diapositiva, los identificadores de empleado y de puesto de la tabla JOB_ HISTORY se restan de los de la tabla EMPLOYEES. El juego de resultados muestra los empleados que quedan después de la resta; se representan mediante filas que existen en la tabla EMPLOYEES pero no en la tabla JOB_HISTORY. Son los registros de los empleados que no han cambiado de puesto ninguna vez.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-16

Instrucciones para los Operadores SET • • •

Las expresiones de las listas SELECT deben corresponder en número y en tipo de datos. Se pueden utilizar paréntesis para modificar la secuencia de ejecución. La cláusula ORDER BY: – Sólo puede aparecer al final de la sentencia – Aceptará el nombre de columna, los alias de la primera sentencia SELECT o la notación posicional

Instrucciones para los Operadores SET • Las expresiones de las listas SELECT deben corresponder en número y en tipo de datos. Las consultas que utilizan operadores UNION, UNION ALL, INTERSECT y MINUS en su cláusula WHERE deben tener el mismo número y el mismo tipo de columnas de su lista SELECT. Por ejemplo: SELECT employee_id, department_id FROM employees WHERE (employee_id, department_id) IN (SELECT employee_id, department_id FROM employees UNION SELECT employee_id, department_id FROM job_history);

• • •

La cláusula ORDER BY: - Sólo puede aparecer al final de la sentencia - Aceptará el nombre de columna, un alias o la notación posicional El nombre de columna, o el alias, si se utiliza en una cláusula ORDER BY, debe ser de la primera lista SELECT. Los operadores SET se pueden utilizar en subconsultas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-17

Oracle Server y los Operadores SET •

Las filas duplicadas se eliminan automáticamente excepto en UNION ALL.

•

Los nombres de columna de la primera consulta aparecen en el resultado. La salida se clasifica por orden ascendente por defecto excepto en UNION ALL.

•

Oracle Server y los Operadores SET Cuando una consulta utiliza operadores SET, Oracle Server elimina automáticamente las filas duplicadas excepto en el caso del operador UNION ALL. Los nombres de columna de la salida los decide la lista de columnas de la primera sentencia SELECT. Por defecto, la salida se clasifica por orden ascendente de la primera columna de la cláusula SELECT. Las expresiones correspondientes de las listas SELECT de las consultas componentes de una consulta compuesta deben corresponder en número y en tipo de datos. SI las consultas componentes seleccionan datos de carácter, el tipo de datos de los valores de retorno se determinan de este modo: • Si ambas consultas seleccionan valores el tipo de datos CHAR, los valores devueltos tienen el tipo de datos CHAR. • Si alguna de las dos o las dos consultas seleccionan valores del tipo de datos VARCHAR2, los valores devueltos tienen el tipo de datos VARCHAR2.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-18

Correspondencia de Sentencias SELECT Mediante el operador UNION, muestre el identificador de departamento, la ubicación y la fecha de contratación de todos los empleados. SELECT department_id, TO_NUMBER(null) location, hire_date FROM employees UNION SELECT department_id, location_id, TO_DATE(null) FROM departments;

…

Correspondencia de Sentencias SELECT Como las expresiones de las listas SELECT de las consultas deben corresponder en número, puede utilizar columnas ficticias y las funciones de conversión de tipo de datos para cumplir esta regla. En la diapositiva, se proporciona el nombre location como cabecera de columna ficticia. Se utiliza la función TO_NUMBER de la primera consulta para hacerla corresponder con el tipo de datos NUMBER de la columna LOCATION_ID recuperada por la segunda consulta. De forma parecida, se utiliza la función TO_DATE de la segunda consulta para hacerla corresponder con el tipo de datos NUMBER de la columna LOCATION_ID recuperada por la primera consulta.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-19

Correspondencia de la Sentencia SELECT: Ejemplo Mediante el operador UNION, muestre el identificador de empleado, el identificador de puesto y el salario de todos los empleados. SELECT FROM UNION SELECT FROM

employee_id, job_id,salary employees employee_id, job_id,0 job_history;

…

Correspondencia de la Sentencia SELECT: Ejemplo Las tablas EMPLOYEES y JOB_HISTORY tienen varias columnas en común (por ejemplo, EMPLOYEE_ID, JOB_ID y DEPARTMENT_ID). Pero ¿y si desea que la consulta muestre el identificador de empleado, el de puesto y el salario mediante el operador UNION, sabiendo que el salario existe sólo en la tabla EMPLOYEES? EL código de ejemplo de la diapositiva hace corresponder las columnas EMPLOYEE_ID y JOB_ID de las tablas EMPLOYEES y JOB_HISTORY. Se agrega un valor de literal de 0 a la sentencia SELECT de JOB_HISTORY para que corresponda con la columna numérica SALARY de la sentencia SELECT de EMPLOYEES. En el resultado anterior, cada fila de la salida que se corresponde con un registro de la tabla JOB_HISTORY contiene un 0 en la columna SALARY.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-20

Control del Orden de Filas Genere una frase en inglés mediante dos operadores UNION. COLUMN a_dummy NOPRINT SELECT 'sing' AS "My dream", 3 a_dummy FROM dual UNION SELECT 'I''d like to teach', 1 a_dummy FROM dual UNION SELECT 'the world to', 2 a_dummy FROM dual ORDER BY a_dummy;

Control del Orden de Filas Por defecto, la salida se clasifica por orden ascendente de la primera columna. Puede utilizar la cláusula ORDER BY para cambiar esto. La cláusula ORDER BY sólo se puede utilizar una vez en una consulta compuesta. Si se utiliza, la cláusula ORDER BY se debe colocar al final de la consulta. La cláusula ORDER BY acepta el nombre de columna o un alias. Sin la cláusula ORDER BY, el código de ejemplo de la diapositiva genera la siguiente salida por orden alfabético de la primera columna:

Nota: Piense en una consulta compuesta en la que se utilice el operador SET UNION más de una vez. En este caso, la cláusula ORDER BY sólo puede utilizar posiciones y no expresiones explícitas. Comando COLUMN de iSQL*Plus Puede utilizar el comando COLUMN de iSQL*Plus para personalizar las cabeceras de columna. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-21

Comando COLUMN de iSQL*Plus (continuaci贸n) Sintaxis: COL[UMN] [{column|alias} [option]]

Donde OPTION es: CLE[AR]: Limpia cualquier formato de columna HEA[DING] text: Define la cabecera de columna FOR[MAT] format: Cambia la visualizaci贸n de la columna mediante un modelo de formato NOPRINT | PRINT: Suprime o muestra la cabecera y los datos de columna NULL Las siguiente sentencia suprime los datos de columna y la cabecera de t铆tulo para la columna denominada A_DUMMY. Observe que la primera cl谩usula SELECT de la diapositiva anterior crea una columna ficticia denominada A_DUMMY. COLUMN a_dummy NOPRINT

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-22

Resumen En esta lección ha aprendido a: • Utilizar UNION para devolver todas las filas distintas • Utilizar UNION ALL para devolver todas las filas, incluidas las duplicadas • Utilizar INTERSECT para devolver todas las filas que se comparten en ambas consultas • Utilizar MINUS para devolver todas las filas distintas devueltas por la primera consulta pero no por la segunda • Utilizar ORDER BY sólo al final de la sentencia

Resumen • El operador UNION devuelve todas las filas seleccionadas por cualquiera de las dos consultas. Utilice el operador UNION para devolver todas las filas de varias tablas y eliminar las filas duplicadas. • Utilice el operador UNION ALL para devolver todas las filas de varias consultas. A diferencia de lo que sucede con el operador UNION, las filas duplicadas no se eliminan y la salida no se ordena por defecto. • Utilice el operador INTERSECT para devolver las filas que son comunes a varias consultas. • Utilice el operador MINUS para devolver las filas devueltas por la primera consulta que no estén presentes en la segunda consulta. • Recuerde utilizar la cláusula ORDER BY sólo al final de la sentencia compuesta. • Asegúrese de que la expresión correspondiente en las listas SELECT se corresponden en número y en tipo de datos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-23

Práctica 7: Visión General En esta práctica, utilizará los operadores SET para crear informes: • Uso del operador UNION • Uso del operador INTERSECTION • Uso del operador MINUS

Práctica 7: Visión General En esta práctica, escribirá consultas mediante los operadores SET.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-24

Práctica 7 1. El departamento de recursos humanos necesita una lista de identificadores de departamento que no contengan el identificador de puesto ST_CLERK. Utilice los operadores SET para crear este informe.

2. El departamento de recursos humanos necesita una lista de países en los que no haya ningún departamento. Muestre el identificador de país y el nombre de los países. Utilice los operadores SET para crear este informe.

3. Genere una lista de puestos para los departamentos 10, 50 y 20, en ese orden. Muestre el identificador de puesto y el de departamento mediante operadores SET.

4. Cree un informe que muestre los identificadores de empleado y de puesto de los empleados que actualmente tengan un cargo igual al que tenían cuando se los contrató inicialmente (es decir, han cambiado de puesto pero han vuelto a ocupar su puesto original).

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-25

Pr谩ctica 7 (continuaci贸n) 5. El departamento de recursos humanos necesita un informe con estas especificaciones: - Apellido e identificador de departamento de todos los empleados de la tabla EMPLOYEES, independientemente de si pertenecen o no a un departamento - Identificador y nombre de departamento de todos los departamentos de la tabla DEPARTMENTS, independientemente de si tienen o no empleados trabajando en ellos Para ello, escriba una consulta compuesta.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 7-26

Objetivo En esta lecciรณn, aprenderรก a utilizar sentencias DML para insertar filas en una tabla, actualizar las filas existentes de una tabla y suprimir filas existentes de una tabla. Aprenderรก asimismo a controlar transacciones con las sentencias COMMIT, SAVEPOINT y ROLLBACK.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-2

Lenguaje de Manipulación de Datos DML (Lenguaje de manipulación de datos) es una parte fundamental de SQL. Cuando desee agregar, actualizar o suprimir datos en la base de datos, debe ejecutar una sentencia DML. Una recopilación de sentencias DML que forman una unidad lógica de trabajo se denomina transacción. Piense en una base de datos bancaria. Cuando un cliente del banco transfiere dinero de una cuenta de ahorros a una cuenta corriente, la transacción podría consistir en tres operaciones diferentes: extraer de la cuenta de ahorros, sumar a la cuenta corriente y registrar la transacción en los asientos diarios de transacciones. Oracle Server debe garantizar que se realicen las tres sentencias SQL para mantener las cuentas con el saldo adecuado. Si algo impide que una de las sentencias de la transacción se ejecute, las demás sentencias de la transacción se deben deshacer.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-3

Adici贸n de una Nueva Fila a una Tabla El gr谩fico de la diapositiva muestra la agregaci贸n de un nuevo departamento a la tabla DEPARTMENTS.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-4

Adici贸n de una Nueva Fila a una Tabla (continuaci贸n) Puede agregar nuevas filas a una tabla mediante la sentencia INSERT. En la sintaxis: table es el nombre de la tabla column es el nombre de la columna de la tabla que se va a rellenar value es el valor correspondiente para la columna Nota: Esta sentencia con la cl谩usula VALUES agrega s贸lo una fila cada vez a la tabla.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-5

Adición de una Nueva Fila a una Tabla (continuación) Como puede insertar una nueva fila que contiene valores para cada columna, la lista de columnas no es necesaria en la cláusula INSERT. Sin embargo, si no utiliza la lista de columnas, los valores se deben mostrar de acuerdo con el orden por defecto de las columnas de la tabla y se debe proporcionar un valor para cada columna. DESCRIBE

departments

Para mejorar la claridad, utilice la lista de columnas en la cláusula INSERT. Ponga los valores de caracteres y de fecha entre comillas simples; no se recomienda poner valores numéricos entre paréntesis entre comillas simples. Los valores numéricos no se deben poner entre comillas simples, ya que se puede producir una conversión implícita de los valores numéricos que se asignan a las columnas del tipo de datos NUMBER si se incluyen comillas simples.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-6

Métodos para Insertar Valores Nulos

Asegúrese de que puede utilizar valores nulos en la columna de destino verificando el estado de Null? con el comando DESCRIBE de iSQL*Plus. Oracle Server fuerza automáticamente todos los tipos de datos, los rangos de datos y las restricciones de integridad de datos. Cualquier columna que no se muestre explícitamente obtiene un valor nulo en la fila nueva. Errores habituales que se pueden producir durante la entrada de usuario: • Falta de un valor obligatorio para una columna NOT NULL • Un valor duplicado viola la restricción de unicidad • Violación de la restricción de clave primaria • Violación de la restricción CHECK • Incongruencia en el tipo de dato • Valor demasiado ancho que no cabe en la columna Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-7

Inserción de Valores Especiales mediante Funciones SQL Puede utilizar funciones para introducir valores especiales en la tabla. El ejemplo de la diapositiva registra información para el empleado Popp de la tabla EMPLOYEES. Suministra la fecha y la hora actuales en la columna HIRE_DATE. Utiliza la función SYSDATE para la fecha y la hora actuales. También puede utilizar la función USER al insertar filas en una tabla. La función USER registra el nombre de usuario actual. Confirmación de Adiciones a la Tabla SELECT employee_id, last_name, job_id, hire_date, commission_pct FROM employees WHERE employee_id = 113;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-8

Inserción de Valores de Fecha y Hora Específicos Se suele utilizar el formato DD-MON-YY para insertar un valor de fecha. Con este formato, tenga en cuenta que, por defecto, el siglo será el actual. Como la fecha también contiene información de hora, la hora por defecto es medianoche (00.00.00). Si hay que introducir una fecha en un formato diferente al formato por defecto (por ejemplo, con otro siglo o a una hora específica), debe utilizar la función TO_DATE. El ejemplo de la diapositiva registra información para el empleado Raphealy de la tabla EMPLOYEES. Define la columna HIRE_DATE en el 3 de febrero de 1999. Si utiliza la sentencia siguiente en lugar de la que se muestra en la diapositiva, interpretará que el año de la fecha de contratación es 2099. INSERT INTO employees VALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', '03-FEB-99', 'AC_ACCOUNT', 11000, NULL, 100, 30);

Si se utiliza el formato RR, el sistema proporciona automáticamente el siglo correcto, aunque no se trate del actual.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-9

Creación de un Archivo de Comandos para Manipular Datos Puede guardar comandos con variables de sustitución en un archivo y ejecutarlos en el archivo. El ejemplo de la diapositiva registra información para un departamento de la tabla DEPARTMENTS. Ejecute el archivo de comandos y se le solicitará que introduzca las variables de sustitución &. Los valores que introduzca se sustituirán después en la sentencia. Esto le permite ejecutar el mismo archivo de comandos una y otra vez, pero suministrando un juego diferente de valores cada vez que lo ejecute.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-10

Copia de Filas de Otra Tabla Puede insertar sentencias INSERT para agregar filas a una tabla en la que los valores se deriven de tablas existentes. En lugar de la cláusula VALUES, utilice una subconsulta. Sintaxis INSERT INTO table [ column (, column) ] subquery;

En la sintaxis: table es el nombre de la tabla column es el nombre de la columna de la tabla que se va a rellenar subquery es la subconsulta que devuelve filas a la tabla El número de columnas y los tipos de datos de la lista de columnas deben corresponder con el número de valores y los tipos de datos de la subconsulta. Para crear una copia de las filas de una tabla, utilice SELECT * en la subconsulta: INSERT INTO copy_emp SELECT * FROM employees;

Para obtener más información, consulte “SELECT” (subsección “subqueries”) en Oracle Database SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-11

Cambio de Datos en una Tabla La diapositiva ilustra el cambio del nĂşmero de departamento para los empleados del departamento 60 al 30.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-12

Actualización de Filas Puede modificar filas existentes mediante la sentencia UPDATE. En la sintaxis: table es el nombre de la tabla column es el nombre de la columna de la tabla que se va a rellenar value es el valor correspondiente o la subconsulta para la columna condition identifica las filas que se actualizarán y se compone de nombres de columna, expresiones, restricciones, subconsultas y operadores de comparación Confirme la operación de actualización consultando la tabla para mostrar las filas actualizadas. Para obtener más información, consulte “UPDATE” en Oracle Database SQL Reference. Nota: En general, utilice la clave primaria para identificar una única fila. Si utiliza otras columnas, puede provocar que se actualicen varias filas inadvertidamente. Por ejemplo, identificar una sola fila de la tabla EMPLOYEES es peligroso, ya que es posible que haya más de un empleado con el mismo nombre.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-13

Actualización de Filas (continuación) La sentencia UPDATE modifica filas específicas si se especifica la cláusula WHERE. El ejemplo de la diapositiva transfiere el empleado 113 (Popp) al departamento 70. Si omite la cláusula WHERE, se modifican todas las filas de la tabla. SELECT last_name, department_id FROM copy_emp;

… Nota: La tabla COPY_EMP tiene los mismos datos que la tabla EMPLOYEES.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-14

Actualización de Dos Columnas con una Subconsulta Puede actualizar varias columnas en la cláusula SET de una sentencia UPDATE escribiendo varias subconsultas. Sintaxis UPDATE table SET column

= (SELECT column FROM table WHERE condition)

[ , column

[WHERE

condition ]

(SELECT column FROM table WHERE condition)] ;

Nota: Si no se actualiza ninguna fila, se devuelve el mensaje “0 rows updated”.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-15

Actualización de Filas Basándose en Otra Tabla Puede utilizar subconsultas en sentencias UPDATE para actualizar las filas de una tabla. El ejemplo de la diapositiva actualiza la tabla COPY_EMP basándose en los valores de la tabla EMPLOYEES. Cambia el número de departamento de todos los empleados con el identificador de trabajo del empleado 200 al número de departamento actual del empleado 100.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-16

Eliminaci贸n de una Fila de una Tabla El gr谩fico de la diapositiva elimina el departamento de finanzas de la tabla DEPARTMENTS (se asume que no se ha definido ninguna restricci贸n para la tabla DEPARTMENTS).

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-17

Supresión de Filas Puede eliminar filas existentes mediante la sentencia DELETE. En la sintaxis: table es el nombre de la tabla condition identifica las filas que se suprimirán y se compone de nombres de columna, expresiones, restricciones, subconsultas y operadores de comparación Nota: Si no se suprime ninguna fila, se devuelve el mensaje “0 rows deleted”. Para obtener más información, consulte “UPDATE” en Oracle Database SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-18

Supresión de Filas (continuación) Puede suprimir filas específicas especificando la cláusula WHERE en la sentencia DELETE. En el ejemplo de la diapositiva se suprime el departamento de finanzas de la tabla DEPARTMENTS. Puede confirmar la operación de supresión mostrando las filas suprimidas mediante la sentencia SELECT. SELECT FROM WHERE no rows

* departments department_name = 'Finance'; selected.

Si omite la cláusula WHERE, se suprimen todas las filas de la tabla. En el segundo ejemplo de la diapositiva, se suprimen todas las filas de la tabla COPY_EMP, ya que no se ha especificado ninguna cláusula WHERE. Ejemplo Elimine las filas identificadas en la cláusula WHERE. DELETE FROM employees WHERE employee_id = 114; 1 row deleted. DELETE FROM departments WHERE department_id IN (30, 40); 2 rows deleted.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-19

Supresión de Filas Basándose en Otra Tabla Puede utilizar subconsultas para suprimir filas de una tabla basándose en valores de otra tabla. En el ejemplo de la diapositiva se suprimen todos los empleados que están en el departamento cuyo nombre contiene la cadena Public. La subconsulta busca en la tabla DEPARTMENTS el número de departamento basándose en el nombre de departamento que contiene la cadena Public. La subconsulta proporciona a continuación el número de departamento a la consulta principal, que suprime filas de datos de la tabla EMPLOYEES basándose en este número de departamento.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-20

Sentencia TRUNCATE Un método más sencillo para vaciar una tabla es la sentencia TRUNCATE. Puede utilizar esta sentencia para eliminar rápidamente todas las filas de una tabla o de un agrupamiento. Eliminar filas con la sentencia TRUNCATE es más rápido que hacerlo con la sentencia DELETE por estos motivos: • La sentencia TRUNCATE es una sentencia DDL (Lenguaje de Definición de Datos) y no genera información de rollback. La información de rollback se trata más adelante en esta lección. • Al truncar una tabla no se arrancan los disparadores de supresión de la tabla. • Si la tabla es la tabla principal de una restricción de integridad referencial, no se puede truncar. Debe desactivar la restricción antes de emitir la sentencia TRUNCATE. La desactivación de restricciones se trata en una lección posterior.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-21

Uso de Subconsultas en una Sentencia INSERT Puede utilizar una subconsulta en lugar del nombre de tabla en la cláusula INTO de la sentencia INSERT. La lista de selección de esta subconsulta debe tener el mismo número de columnas que la lista de columnas de la cláusula VALUES. Para que la sentencia INSERT funcione correctamente, se deben seguir las reglas de las columnas de la tabla base. Por ejemplo, no podría agregar un identificador de empleado duplicado ni omitir un valor para una columna obligatoria no nula.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-22

Uso de Subconsultas en una Sentencia INSERT (continuaci贸n) El ejemplo muestra los resultados de la subconsulta que se utiliz贸 para identificar la tabla para la sentencia INSERT.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-23

Transacciones de Base de Datos Oracle Server asegura la consistencia de datos basándose en transacciones. Las transacciones le proporcionan más flexibilidad y control al cambiar datos y aseguran la consistencia de los datos en caso de un fallo de proceso de usuario o del sistema. Las transacciones constan de sentencias DML que constituyen un cambio consistente en los datos. Por ejemplo, una transferencia de fondos entre dos cuentas debería incluir el débito en una cuenta y el crédito en otra en la misma cantidad. Ambas acciones deben fallar o tener éxito al mismo tiempo, el crédito no se debería validar sin el débito. Tipos de Transacción

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-24

¿Cuándo se Inicia y Termina una Transacción? Una transacción comienza cuando se encuentra la primera sentencia DML y termina cuando ocurre alguna de estas cosas: • Se emite una sentencia COMMIT o ROLLBACK. • Se emite una sentencia DDL, como CREATE. • Se emite una sentencia DCL. • El usuario sale de iSQL*Plus. • Falla una máquina o se bloquea el sistema. Cuando termina una transacción, la siguiente sentencia SQL ejecutable inicia utomáticamente la siguiente transacción. Una sentencia DDL o una sentencia DCL se validan automáticamente, con lo que terminan implícitamente una transacción.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-25

Ventajas de COMMIT y ROLLBACK Con las sentencias COMMIT y ROLLBACK, tiene el control para hacer que los cambios en los datos sean permanentes.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-26

Sentencias Explícitas de Control de Transacciones Puede controlar la lógica de las transacciones mediante las sentencias COMMIT, SAVEPOINT y ROLLBACK.

Nota: SAVEPOINT no es SQL del estándar ANSI. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-27

Rollback de Cambios hasta un Marcador Puede crear un marcador en la transacción actual mediante la sentencia SAVEPOINT, que divide la transacción de secciones más pequeñas. Entonces, puede desechar los cambios pendientes hasta ese marcador mediante la sentencia ROLLBACK TO SAVEPOINT. Si crea un segundo punto de grabación con el mismo nombre que uno anterior, se suprime ese punto de grabación anterior.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-28

Procesamiento de Transacciones Implícito

Nota: Hay un tercer comando disponible en iSQL*Plus. El comando AUTOCOMMIT se puede activar y desactivar. Si se activa, cada sentencia DML individual se valida en cuanto se ejecuta. No puede hacer rollback en los cambios. Si se desactiva, la sentencia COMMIT aún se puede emitir explícitamente. Además, la sentencia COMMIT se emite al emitirse una sentencia DDL o al salir de iSQL*Plus.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-29

Procesamiento de Transacciones Implícito (continuación) Fallos del Sistema Cuando un fallo del sistema interrumpe una transacción, se hace rollback en toda la transacción automáticamente. Esto impide que el error provoque cambios no deseados en los datos y devuelve las tablas a su estado en el momento de la última validación. De esta forma, Oracle Server protege la integridad de las tablas. Desde iSQL*Plus, se consigue una salida normal de la sesión haciendo clic en el botón Exit. Con SQL*Plus, se consigue una salida normal escribiendo el comando EXIT en el prompt. Cerrar la ventana se interpreta como una salida anormal.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-30

Validación de Cambios Todos los cambios de datos realizados durante la transacción son temporales hasta la validación de la transacción. El estado de los datos antes de que se emitan las sentencias COMMIT o ROLLBACK se puede describir así: • Las operaciones de manipulación de datos afectan principalmente al buffer de la base de datos; por lo tanto, se puede recuperar el estado anterior de los datos. • El usuario actual puede revisar los resultados de las operaciones de manipulación de datos consultando las tablas. • Los demás usuarios no pueden ver los resultados de las operaciones de manipulación de datos realizadas por el usuario actual. Oracle Server instituye la consistencia de lectura para garantizar que cada usuario vea los datos como existían en el momento de la última validación. • Las filas afectadas están bloqueadas; los demás usuarios no pueden cambiar los datos dentro de las filas afectadas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-31

Validación de Cambios (continuación) Puede hacer que todos los cambios pendientes sean permanentes mediante la sentencia COMMIT. Esto es lo que sucede después de una sentencia COMMIT: • Los cambios en los datos se escriben en la base de datos. • El estado anterior de los datos ya no está disponible con las consultas SQL normales. • Todos los usuarios pueden ver los resultados de la transacción. • Se liberan los bloqueos en las filas afectadas; las filas ya no están disponibles para que otros usuarios realicen nuevos cambios en los datos. • Se borran todos los puntos de grabación.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-32

Validaci贸n de Cambios (continuaci贸n) En el ejemplo de la diapositiva se suprime una fila de la tabla EMPLOYEES y se inserta una nueva fila en la tabla DEPARTMENTS. A continuaci贸n, que los cambios se hacen permanentes mediante la emisi贸n de la sentencia COMMIT. Ejemplo Elimine los departamentos 290 y 300 de la tabla DEPARTMENTS y actualice una fila de la tabla COPY_EMP. Haga permanente los cambios en los datos. DELETE FROM departments WHERE department_id IN (290, 300); 1 row deleted. UPDATE employees SET department_id = 80 WHERE employee_id = 206; 1 row updated. COMMIT; Commit Complete.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-33

Rollback en los Cambios Deseche todos los cambios pendientes mediante la sentencia ROLLBACK, lo que da esto como resultado: • Se deshacen los cambios de datos. • Se restaura el estado anterior de los datos. • Se liberan los bloqueos en las filas afectadas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-34

Ejemplo Mientras intenta eliminar un registro de la tabla TEST, puede vaciar la tabla por accidente. Puede corregir el error, volver a emitir la sentencia correcta y hacer permanentes los cambios en los datos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-35

Rollback de Nivel de Sentencia Se puede desechar parte de una transacción mediante un rollback implícito si se detecta un error de ejecución de sentencia. Si falla una única sentencia DML durante la ejecución de una transacción, se deshace su efecto mediante un rollback de nivel de sentencia, pero los cambios realizados por las sentencias DML de la transacción no se desechan. El usuario puede validarlos o hacer rollback en ellos explícitamente. Oracle Server emite una validación implícitamente antes y después de cualquier sentencia DDL. Así pues, si la sentencia DDL no se ejecuta correctamente, no puede hacer rollback en la sentencia anterior porque el servidor emitió una validación. Termine las transacciones explícitamente ejecutando una sentencia COMMIT o ROLLBACK.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-36

Consistencia de Lectura Los usuarios de bases de datos acceden a la base de datos de dos formas: • Operaciones de lectura (sentencia SELECT) • Operaciones de escritura (sentencias INSERT, UPDATE, DELETE) Necesita la consistencia de lectura para que se produzca lo siguiente: • Se garantiza una visualización consistente de los datos al lector y al escritor de base de datos. • Los lectores no ven los datos que están en proceso de cambio. • Se garantiza a los escritores que los cambios en la base de datos se realizan de forma consistente. • Los cambios que realiza un escritor no molestan ni entran en conflicto con los que realice otro escritor. El objetivo de la consistencia de datos es que cada usuario vea los datos tal como existían en el momento de la última validación, antes de iniciarse una operación DML.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-37

Implementación de la Consistencia de Lectura La consistencia de lectura es una implementación automática. Mantiene una copia parcial de la base de datos en segmentos de deshacer. La imagen de lectura consistente se crea a partir de datos validados de la tabla y datos antiguos que se están cambiando y aún no se han validado del segmento de deshacer. Al realizarse una operación de inserción, actualización o supresión en la base de datos Oracle Server lleva a cabo una copia de los datos antes de que se cambien y los escribe en un segmento de deshacer. Todos los lectores, excepto el que emitió en cambio, aún ven la base de datos tal como existía antes de que se iniciaran los cambios; ven la “instantánea” del segmento de deshacer de los datos. Antes de la validación de los cambios en la base de datos, sólo el usuario que está modificando los datos ve la base de datos con las modificaciones. Los demás ven la instantánea del segmento de deshacer. Esto garantiza que los lectores de los datos lean datos consistentes que no se estén cambiando actualmente. Al validarse una sentencia DML, el cambio realizado en la base de datos se hace visible para cualquiera que emita una sentencia de selección después de realizada la validación. El espacio ocupado por los datos antiguos del archivo de segmento de deshacer se libera para su reutilización. Si se hace rollback en la transacción, se deshacen los cambios. • La versión original y más antigua de los datos en el segmento de deshacer se vuelve a escribir en la tabla. • Todos los usuarios ven la base de datos tal como existía antes del inicio de la transacción. Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-38

Resumen En esta lecci贸n, ha aprendido a manipular datos en la base de datos Oracle mediante las sentencias INSERT, UPDATE y DELETE, as铆 como a controlar cambios de datos mediante las sentencias COMMIT, SAVEPOINT y ROLLBACK. Oracle Server garantiza una visualizaci贸n consistente de los datos en todo momento.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-39

Práctica 8: Visión General En esta práctica, agregará filas a la tabla MY_EMPLOYEE, actualizará y suprimirá datos de la tabla y controlará las transacciones.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-40

Práctica 8 El departamento de recursos humanos desea que cree sentencias SQL para insertar, actualizar suprimir datos de empleados. Como prototipo, utiliza la tabla MY_EMPLOYEE, antes de proporcionarle las sentencias al departamento de recursos humanos. Inserte datos en la tabla MY_EMPLOYEE. 1. Ejecute la sentencia en el archivo de comandos lab_08_01.sql para crear la tabla MY_EMPLOYEE que se utilizará en la práctica. 2. Describa la estructura de la tabla MY_EMPLOYEE para identificar los nombres de columna.

3. Cree una sentencia INSERT para agregar la primera fila de datos a la tabla MY_EMPLOYEE a partir de los siguientes datos de ejemplo. No enumere las columnas en la cláusula INSERT. No introduzca aún todas las filas.

4. Rellene la tabla MY_EMPLOYEE con la segunda fila de datos de ejemplo de la lista anterior. Esta vez, enumere las columnas explícitamente en la cláusula INSERT. 5. Confirme la adición a la tabla.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-41

Práctica 8 (continuación) 6. Escriba una sentencia INSERT en un archivo de comandos dinámico denominado loademp.sql para cargar filas en la tabla MY_EMPLOYEE. Concatene la primera letra del primer nombre y los primeros siete caracteres del apellido para producir el identificador de usuario. Guarde este archivo de comandos en un archivo denominado lab_08_06.sql 7. Rellene la tabla con las dos filas siguientes de datos de ejemplo ejecutando la sentencia INSERT en el archivo de comandos que ha creado. 8. Confirme las adiciones a la tabla.

9. Haga permanentes las adiciones de datos. Actualice y suprima datos en la tabla MY_EMPLOYEE. 10. Cambie el apellido del empleado 3 a Drexler. 11. Cambie el salario a 1.000 dólares a todos los empleados que tengan un salario inferior a 900 dólares. 12. Verifique los cambios en la tabla.

13. Suprima a Betty Dancs de la tabla MY_EMPLOYEE. 14. Confirme los cambios en la tabla.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-42

Práctica 8 (continuación) 15. Valide todos los cambios pendientes. Controle la transacción de datos en la tabla MY_EMPLOYEE. 16. Rellene la tabla con las últimas filas de datos de ejemplo mediante las sentencias en el archivo de comandos creado en el paso 6. Ejecute las sentencias en el archivo de comandos. 17. Confirme la adición a la tabla.

18. 19. 20. 21. 22.

Marque un punto intermedio en el procesamiento de la transacción. Vacíe toda la tabla. Confirme que la tabla está vacía. Deseche la operación DELETE más reciente sin descartar la operación INSERT anterior. Confirme que la nueva fila sigue intacta.

23. Haga permanente la adición de datos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-43

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 8-44