Ingeniero Técnico en Informática de Sistemas

SQL (Structured Query Language) Plus

Compartir 0 Me sirvió 0 No me sirvió

DESARROLLO DE APLICACIONES EN LENGUAJES DE 4º GENERACIÓN

INDICE DE LA ASIGNATURA

Introducción a los sistemas gráficos de bases de datos

Organización de sistema gestor de bases de datos

Lenguaje SQL: Definición, control y manipulación de datos

Utilización de plantillas y menús

Utilización de generadores de funciones de impresión

Programación en PL/SQL

Designer 2000.

CRITERIOS DE EVALUACIÓN

Las tres primeras unidades se hará un examen individual

Las dos siguientes unidades (4-5) se irá preparando un proyecto y se irá evaluando

Al final habrá que evaluar un proyecto conjunto.

26/10/99

SISTEMA GESTOR DE BASE DE DATOS

DEFINICIÓN: Es un conjunto de procedimientos (lenguaje) que controlan una base de datos.

HISTORIA: Antes se usaban ficheros tradicionales en la gestión de datos y en cada área se creaba una gestión de base de datos diferente. Pero esto creaba el problema de tener datos duplicados en diferentes gestores de bases de datos. En esta fase los datos estaban intrínsecamente unidos a los programas que los gestionaban, puesto que esos datos sólo podían ser leídos con los procesos que había creado el analista. Por eso se llamaban sistemas orientados a procesos. Se derrochaba la memoria y el proceso, ya que al repetirse los datos, a la hora de modificar alguno de ellos había que modificarlo en todos los ficheros en los que aparecía dicho dato. Este sistema tenía una gran falta de flexibilidad frente a los cambios.

Una base de datos tiene que permitir:

Evitar la duplicidad de los datos salvo en el caso de que se quiera tener una seguridad en caso de estropearse la unidad principal
Una descripción de los datos y las relaciones entre ellos se almacenen en la base de datos.
Ahorro de memoria y proceso.
Centralizar la información en un solo tipo de fichero, es decir, normalizar la información.
Relacionar los ficheros de datos.
Mejorar las disponibilidad y la seguridad de los datos.

INCONVENIENTES DE UTILIZAR UN SISTEMA GESTOR DE BASE DE DATOS

Se incrementan los costes

Mas CPU

Mas memoria

Mas licencias

Se necesita personal especializado en el uso de las bases de datos.
La implantación del proyecto es larga y dura (como mi...)
Rentabilidad a medio plazo.
Ausencia real de normas.

DEFINICION:

Es un conjunto, colección o depósito de datos
Las interrelaciones de los datos están almacenados con los datos.
No se permite la redundancia lógica. Si está permitida la redundancia física.
Han de atender a múltiples usuarios y aplicaciones
Independencia de los datos frente a las aplicaciones.
Definición, descripción e incluso documentación (metadatos) relativa a los datos están almacenadas con los datos.

“ Colección o depósito de datos integrada, con redundancias controladas y con una estructura que refleja las interrelaciones existentes en el mundo real; los datos que han de estar compartidos por diferentes usuarios y aplicaciones deben mantenerse independientes de ésta y su definición, descripción, única para cada tipo de datos, han de estar almacenadas con los mismos. Los procedimientos de actualización, recuperación habrán de ser capaces de respetar la integridad, seguridad y confidencialidad del conjunto de datos”

Dentro de una base de datos podemos distinguir tres niveles diferentes de abstracción.

Estructura lógica (esquema externo) (Vistas)

Estructura lógica global (Esquema)

Estructura física (Esquema interno): como están almacenados todos los datos físicamente.

¿Qué es un sistema gestor de base de datos?

Es un conjunto de programas, procedimientos y lenguajes que permiten o suministran a los usuarios los medios necesarios para escribir, recuperar y manipular los datos almacenados; manteniendo su integridad, confidencialidad y seguridad.

Existen funciones de tres tipos diferentes:

Definición y Descripción de datos
Funciones de manipulación de datos
Funciones de utilización (Se engloban dentro de 2 grupos)
Rutinas y software de acceso: Nos permiten acceder a los datos de la base de datos desde el programa
Funciones que permiten al administrador de la base de datos administrarla, como por ejemplo:

Copias de seguridad
Carga masiva de datos
Reorganización de objetos
Obtener estadisticas de utilización

ARQUITECTURA DE UN SISTEMA GESTOR DE BASE DE DATOS

La arquitectura de las bases de datos están regidas por las instituciones Ansi,X3 y Sparc. Tenemos que tener una arquitectura a 3 niveles:

Nivel interno
Nivel conceptual: Todos los datos de la base de datos
Nevel externo: lo que percive el usuario.

También tendremos que tener unos lenguajes para el sistema gestor de base de datos. Habrá de diferentes tipos:

Huésped: Son lenguajes que precisan de otro, que se llama anfitrión, para comunicarse con la base de datos, como por ejemplo el SQL

Autocontenido: Funciona de manera autonoma para comunicarse con la base de datos. El SQL también es un ejemplo de este tipo de lenguaje.

Procedimentales / No procedimentales: Los procedimentales son aquellos en los que hay que bajar mucho el nivel de detelle de las instrucciones para lograr el objetivo que queremos y los no procedimentales son todo lo contrario.

Diferido (BACH)/conversacionales: (SQL interactivo)

Lenguajes navegacionales

Todos estos lenguajes han de permitirnos las siguientes cosas:

Lenguajes de definición y descripción de datos: Permiten hacer definiciones de

estructuras de registros y relaciones entre ellos. Tienen que cumplir las siguientes caracteristicas:

27/10/99

*Ejercicio:

Buscar en access todos aquellos paralelismos con las especificaciones de lenguajes DML y DDL.

DIFERENTES ESTRUCTURAS DE LAS BASES DE DATOS

Tenemos varios tipos dependiendo del enfoque:

Enfoque relacional
Enfoque jerarquico
Enfoque en red

En el enfoque relacional los datos aparecen con una visión uniforme en forma de tablas. Ejemplos de bases de datos relacionales son: informix, oracle, db2, adabas, sybase, ...

En el enfoque jerárquico los datos tienen un esquema en forma de árbol. Sus características son:

El enfoque red viene de un desarrollo de la idea del jerárquico, en el cual puede haber un hijo que tenga varios padres. Tienen peor rendimiento que los de enfoque jerárquico, pero tienen una mayor flexibilidad.

TIPOS DE ALMACENES DE DATOS:

El nacimiento de las Bases de datos llevo a que los datos de definición de las bases de datas van a estar incluidos en las propias bases de datos.

Diccionario de datos: Reúne la información sobre los datos almacenados en la base de datos como descripción, significados, estructuras, consideraciones de seguridad,... todo lo que los usuarios necesitan para entender el significado de los datos (metadatos)
Directorio de datos: toda aquella información que permite al sistema gestor de bases de datos acceder físicamente a los datos.
Catalogo de datos: Es un directorio de datos más restringido, con bajo contenido semantico, pero sus datos estan dispuestos en modo de tablas, y que pueden consultarse con las mismas herramientas de los lenguajes de manipulación de datos.

EJERCICIO:

“Hacer un diseño de un modelo relacional que permita la gestión de prestamos de los libros de una biblioteca”. Partimos de un fichero:

Fichas donde se recogen las características del libro: Titulo, Autor, Editorial, Año, Nº de ejemplares.
Fichas relativas a los prestamos con los datos: Nombre del prestatario, fecha del préstamo, fecha de devolución

El bibliotecario quería además:

Desearía tener en las fichas el idioma en el que está escrito el libro.
Quiere reflejar el tema y el subtema de los que trata el libro.
De los autores, además del nombre, se quiere saber, la nacionalidad y la institución en la que trabaja.
Quiere establecer tres tipos de prestatarios: alumnos, que solo se lo pueden prestar un fin de semana; alumnos en proyecto de fin de carrera, se les puede dejar hasta dos libros una semana; profesores y otras bibliotecas, hasta un año.

<Resolución en el cuaderno>

1.SOPORTE FÍSICO DE UNA BASE DE DATOS ORACLE

Una base de datos de oracle puede ocupara un numero variable de ficheros. Los ficheros serán soportados en el sistema bajo el que se ejecuta el oracle, pero el sistema gestor de base de datos, la estructura de almacenamiento dentro de los ficheros va a ser independiente del sistema operativo en el que se almacene.

2. ESPACIOS PARA TABLAS

Es una agrupación de archivos físicos. Puede ser uno o varios. También se llama Tablespace. Vista de forma lógica, para el oracle, es una sola entidad, a pesar de que esté formado por varios ficheros.

Un ejemplo de creación de tabla sería:

CREATE TABLE empleado

( nom.empleado char(20),

cod.empleado integer

... )

in TABLESPACE users;

2.1 ESPACIO PARA TABLAS (SYSTEM)

System contiene tablas que sirven para la gestión interna de la base de datos, como por ejemplo, información de seguridad, descripción de los datos, etc.

2.2 ESPACIO PARA TABLAS (TEMP)

Se necesita para poder hacer gestiones temporales. Se podría tener uno o varios, pero por lo menos hay que tener uno

2.3 ESPACIO PARA TABLAS (TOOLS)

Este no es imprescindible. Sirve para almacenar tablas de utilidades

2.4 ESPACIO PARA TABLAS (USERS)

Sirve para meter datos que no son importantes o que no tienen mucho uso.

2.5 ESPACIO PARA TABLAS (ROLLBACK)

Hay que exigir integridad a los datos. En el caso de que haya algún fallo durante la ejecución de los procesos, el oracle lo que hace es guardar la información de los datos que se van a usar antes de ejecutar el proceso, con lo cual cuando el sistema vuelve a funcionar bien el propio oracle se encarga de verificar si realmente se acabo el proceso bien y en caso negativo vuelve a ejecutarlo. Se verifica que el proceso terminó bien con la marca de finalización correcta llamada comit

2.6 ESPACIO PARA TABLAS (DATA E INDEX)

El índice (index) contiene una serie de claves de una estructura de registros, y por cada una serie de claves tiene una dirección por la cual se busca en dicha estructura. Hay que tener cuidad de crear los índices necesarios, ya que a la hora de hacer modificaciones en una tabla conlleva el tener que modificar todos sus índices.

RECOMENDACIONES

Es muy recomendable usar un espacio de tabla diferente para cada aplicación.

También es bueno usar un tablespace diferente para los datos y para los índices.

FICHEROS REDO

En el momento que algún programa modifica alguna fila de una tabla el oracle la guarda en memoria, y la modifica. Una vez que se le manda la marca de confirmación Comit, este lo que hace es guardar los cambios en el REDO (archivos de Rehacer). El oracle es totalmente asíncrona a la hora de guardar los datos en la tabla. Simplemente guarda las modificaciones en las tablas de rehacer. En el caso de que se vaya la luz o el sistema se estropea. Se tienen al memos dos porque se puede trabajar en oracle de dos formas. Archive log y no archive. Con el Archive log cuando se llena el Redo 1 se hace un Back Up y llena el redo 2. Si se llenan los dos redos se para el sistema hasta que alguno de los dos se vacía. Con el modo no archive no pide cintas de back up

FICHEROS DE CONTROL (CONTROL FILE)

Guarda la información de los ficheros de la base de datos, y tendrá un registro por cada fichero que componga la base de datos. En el momento en el que falte algún fichero el oracle no arranca. También hay información de control acerca de ....... por razones de integridad. Hay una opción del oracle que hace que se cargue con dos control file, y los dos ficheros son iguales y guarda la información en ellos por duplicado. Estos estarían en discos diferentes.

29/10/99

PROCESOS DEL ORACLE

Se pueden distinguir dos tipos de procesos en el oracle:

Procesos de usuarios (cliente) como por ejemplo SQL, SQLPLUS, SQLFORM... procesos que se arrancan cada vez que un usuario trabaja contra la base de datos.

Procesos de servidor: son procesos que están cargados constantemente en memoria y trabajan con los procesos usuarios para coger sus solicitudes y se comunican con la base de datos.

Los diferentes procesos servidor que hay son:

DBWR: Escritor de la base de datos (database writer). Es el que se encarga de hacer las escrituras, las modificaciones y las lecturas en los ficheros de la base de datos. Es el único proceso que puede hacer esto directamente en la base de datos.
LGWR: Escritor de los registros de rehacer (log writer). Es el único proceso que puede leer y escribir en los ficheros redo. Y además, cada vez que se hace un checkpoint en el sistema para grabar la información de la memoria en las tablas graba también una marca de sincronismo.

Estos dos procesos anteriores son obligatorios

CKPT: Puerto de comprobación/sincronismo (checkpoint): No es obligatorio. Solamente es útil en aquellas estaciones de trabajo en las que se hacen muchos checkpoint. Se encargaría esta función, opcionalmente, de grabar la información de los puntos de sincronismo, descargando de esta tarea al log writer en sistemas con alta frecuencia de puntos de sincronismo.
SMON: Supervisor del sistema. (System monitor): Este proceso de varias cosas. Por un lado se encarga de hacer todas las recuperaciones que hay que hacer cuando se paró el sistema. Si cuando se arranca el sistema se da cuenta de que el oracle terminó mal la última vez, recupera toda la información y la prepara correctamente. También se encarga de liberar espacio temporal disponible, es decir, que no está siendo utilizado, periódicamente. También se encarga, periódicamente, de compactar los huecos libres en los espacios de datos. Se trata de un proceso obligatorio, y su forma de funcionamiento es estar dormido y de vez en cuando actuar, comprobando todos sus objetivos descritos antes.
PMON: Supervisor de procesos (Program monitor): Es obligatorio y tiene la misma forma de actuar que el Smon. Este se encarga de recuperar un programa de usuario fallido.
ARCM: Archivador (Archiver): Es el que se encarga de hacer las copias de los redo log cuando se llenan y estamos funcionando en el modo archive log. Por lo tanto este fichero será opcional para cuando estemos en modo archive log.
RECO: Recuperador(Recoverer): Se encarga de tener los datos sincronizados cuando estos no están centralizados en una sola base de datos.

SGA: AREA GLOBAL DEL SISTEMA (SYSTEM GLOBAL AREA)

CACHE DEL BUFFER DE DATOS: Es una zona de la memoria en la que se van guardando cosas que se van a grabar en el disco. No se graban inmediatamente, sino que se van metiendo en la cache y se grabarán en un momento no definido, es decir, que es una grabación asíncrona. En caso de hacer una cache conviene que no sea pequeña, puesto que si lo es el rendimiento es peor.

BUFFER DE LOS REGISTROS DE REHACER: Igual que los de antes, solo que en vez de con los ficheros de datos, con los ficheros redo.

ZONA COMPARTIDA: Dentro de esta tenemos dos partes:

Cache del diccionario: información de las tablas de datos, como las descripciones, etc. Es un cache específico para almacenar y leer estos diccionarios. Siempre que se pida información sobre los diccionarios de datos primero mirará si esta el esta cache y si no es así lo leerá del disco.

Zona SQL compartida: Para ejecutar una sentencia SQL se necesita: un plan de ejecución, un texto de la sentencia y una lista de objetos referenciados. Para ejecutarla hace varios pasos:

Comprobar si es exactamente igual a otra en el área compartida.

Si los objetos referenciados son los mismos.

Si el usuario que lo está ejecutando tiene permiso para acceder a los datos.

PGA: AREA GLOBAL DEL PROGRAMA (PROGRAM GLOBAL AREA)

Es la zona donde se guardan los datos relativos a un proceso. Por lo tanto hay siempre una PGA por cada usuario que se conecta a oracle.

Se que se arranca una instancia Oracle cuando se esta creando una zona de me memoria que comprende una SGA y unos procesos de servidor. Cada base de datos está asociada a una instancia.

ASIGNACIÓN DE ESPACIO ORACLE (Data blocks, Extents y Segmentos)

Data block: Para oracle, un data block es la unidad más pequeña de memoria. Cualquier objeto que ocupe espacio, por lo menos usará un data block. El oracle administra sus archivos de datos (Data files) en Data Blocks, que es la unidad de asignación. Cuando se crea una base de datos en oracle se define que tamaño va a tener el data block y este será siempre el mismo en esa base de datos. Puede ser de 2 kb o de 4 kb. Cuando se quiere recuperar la información de una fila de una tabla va toda la información que hay en el data block con ella. Si una fila está en varios data blocks se recuperarán todos.

Contenido de un Data block: Tiene una cabecera al principio con información de control para el oracle. Mantiene una información del directorio de datos que tiene el nombre de las tablas que tienen fila. Guarda también un directorio con las filas que tienen almacenadas. A continuación se guardan los datos propiamente dichos en filas de tablas y a continuación va el espacio libre. Este espacio libre sirve para que en el momento de actualizar un dato y este ocupe más que antes el espacio sirva de colchón para que entre. En el caso de que no entre tendría que meter el dato en otro Data Block. El espacio libre del data block se le asigna con la instrucción pctfree

Extent: Es una espacio contiguo de almacenamiento, es decir, un grupo consecutivo de data blocks. Se reserva con la instrucción extent en el cual se le indica un numero de kb. con los cuales se redondeará hacia arriba con múltiplo del tamaño del data block para que este coincida.

Segmento: Es la agrupación de una o varias extents. No tienen por que estar consecutivas y ni siquiera tienen que estar en el mismo fichero. Con el parámetro next se le indica al oracle de que tamaño es el extent siguiente. Estos extents formarían un segmento de datos. Estos extents se van creando a medida que se van llenando, de forma que se puede definir el tamaño de cada uno que se puede crear.

Hay varios tipos de segmentos en una base de datos:

Segmentos de datos
Segmentos de índice
Segmentos de RollBack (agrupan extens del tablespace RollBack)
Segmentos temporales (agrupan extens del tablespace Temp)

OBJETOS DE UNA BASE DE DATOS DE ORACLE

Una agrupación lógica de objetos que pertenecen a un usuario se denomina esquema de usuario. Por ejemplo, una tabla viene identificada por un nombre de tabla y por un propietario. Con lo cual puede haber dos tablas con el mismo nombre pero de diferente propietario.

No todos los objetos ocupan espacio físico, como puede ser una tabla. Un esquema de usuario no esta sujeto a un solo tablespace, sino que se puede tener un esquema repartido en varios tablespace. En un mismo tablespace puede haber objetos de diferentes esquemas de usuario. Con lo cual se deduce que no hay una relación directa entre esquemas de usuario y tablespaces.

· TABLAS:

Es el objeto de oracle que contiene datos. Se organiza en filas y columnas. Los tipos de datos que van a parar a una columna se corresponden con el tipo de dato que se definió para esa columna. Cuando se crea una tabla se especifican características sobre la ubicación física.

Tipos de datos de las columnas:

CHAR: Sirven para almacenar cadenas de caracteres alfanuméricas, de longitud fija, es decir, que hay que especificar una longitud cuando se crea, hasta un máximo de 255 caracteres. Dos cadenas de este tipo, oracle las compara, en el caso de que sean de la misma longitud, carácter a carácter hasta que hay una diferencia y con ella decreta cual es la mayor y la menor. Si son de diferente longitud rellena con blancos la más pequeña.

VARCHAR2: Almacena cadenas de caracteres de longitud variable. Cuando se define una columna de este tipo se declara el nombre de la columna, el tipo y la longitud máxima que puede tener. La longitud máxima que se le puede especificar es de 2000 caracteres. Solo almacena la información representativa, es decir, que al contrario que en el caso anterior, en este no pone blancos hasta que se llena la longitud de caracteres. Por lo tanto su longitud depende del contenido del campo. Este aprovecha mucho mejor el espacio puesto que no guarda espacios en blanco no significativos.

VARCHAR: Es exactamente igual que el tipo anterior. Está reservado para futuros usos, con lo cual puede ocurrir que en futuras versiones de oracle este comando haga otra cosa. Con lo cual no se debe usar.

NUMBER: Sirve para almacenar datos numéricos con una precisión de hasta 38 dígitos significativos. Con esa precisión el numero más grande que se puede archivar es desde 10-130 hasta 9.99999... · 10125. Los negativos son igual pero en negativo. Se puede especificar la precisión y la escala. La precisión es el numero total de dígitos y la escala es el numero de dígitos de la cifra. Ejemplo: para poder archivar numeros del estilo ##.###.###,## sería una precisión de 10 y una escala de 2. Se definiriá de la forma:

SALARIO NUMBER(10,2)

LONG: Son campos que permiten guardar datos de hasta 2 gigas de datos. Oracle interpreta, es decir, que si mueve esa información entre dos bases de datos, el es capaz de convertir esa cadena con un juego de caracteres a otro con un juego de caracteres diferentes. Tiene restricciones: solo se puede definir una columna de tipo long por tabla; una columna de tipo long no es indexable; las columnas de tipo long no pueden aparecer en una relación de tipo referencial.

RAW, LONG RAW: Sirve para almacenar tipos de datos binarios, como imágenes o sonidos. El oracle no interpreta dichos datos, sino que tendrá que interpretar los datos un programa externo. La diferencia entre los dos radica en el tamaño: RAW admite un tamaño de 2000 caracteres y LONG RAW admite hasta 2 gigas de longitud

DATE: Sirve para almacenar datos de tipo fecha/hora, como por ejemplo DD/MM/YYYY HH.MM:SS El rango de este tipo de dato es de 1/01/4712 BC hasta 31/12/4712 AC. El formato por defecto es DD-MM-YY. Para cambiarlo habrá que usar funciones de oracle para crear máscaras. Si se introduce la fecha y no la hora, por defecto pone las 12:00:00 AM, y en el caso contrario la fecha por defecto que pone es el primer día del año en curso.

El valor nulo

En una columna de una tabla de oracle se puede archivar el valor nulo. Esto indica ausencia de valor. No es lo mismo definir una columna numérica que admita nulos con el valor nulo que introducir el valor cero. Por defecto, una columna puede admitir el valor nulo. Cuando en una fila se encuentra en una columna el valor nulo, se archiva la marca del valor nulo, y no se queda vacía. Se recomienda no utilizar nulos el columnas numéricas, porque se puede operar con el valor nulo. Un numero sumado a nulo da de resultado nulo.

Hay otra forma de poner valores por defecto si no se sabe el valor. Se puede decir que esa columna tenga un valor por defecto, que entrará en juego cuando no se introduzca ningún valor en esa columna.

· VISTAS:

Es una versión particularizada de una tabla. En una vista aparecerá la información de una tabla, pero no necesariamente con todas las columnas. Solamente aparecerán las columnas que interesen. Se puede incluir en una sola vista columnas de varias tablas. En esas vistas se podrá eliminar, añadir o modificar filas. En el caso de añadir una fila, al no aparecer todas las columnas, se añadirían valores nulos o por defecto a los campos de las columnas que no aparecen.

Este no es un objeto físico. Es un objeto lógico. No ocupa espacio en el disco. Lo que hace es, físicamente, referirse a otros objetos (tablas) para mostrar información. Este concepto de vista apareció para tener más seguridad y confidencialidad en los datos de la base de datos. Confidencialidad por no mostrar todos los datos. También se puede simplificar con él la visión del modelo de datos, para que no parezca demasiado complejo. Otra utilidad de las vistas es la de poner un nombre a las columnas para saber que datos se han de introducir en ellas.

· PRIVILEGIOS:

En oracle, por defecto, cuando se crea una tabla, nadie tiene acceso a dicha tabla a no ser que el que la crea le de privilegios a otros usuarios. Los privilegios se otorgan con la sentencia grant. Un privilegio es el permiso que se le da a los demás usuarios para que puedan leer los datos de mis tablas, o para modificarlas o insertar datos. Los cuatro privilegios básicos son: Select, Insert, Delete, Update. Se pueden dar de forma separada o todos juntos. Una vez que un usuario tiene privilegios sobre una tabla que no es suya, este usuario puede transferir dicho privilegio a otros usuarios. También se pueden revocar, con la instrucción revoke los derechos a un usuario para que lea los datos de las tablas propias, y si este usuario había dado derechos sobre la tabla a algún otro usuario, este también pierde dichos privilegios.

· ROLES:

Es un objeto que nos permite administrar los privilegios de una forma ordenada. Es un objeto orientado a la seguridad de los datos. Para no tener que dar privilegios uno a uno a todos los usuarios se crean los objetos rol. Se crea un objeto rol al que se le dan todos los derechos que se quieran dar a un grupo de usuarios y después se van dando privilegios a los usuarios para poder usar dicho rol. Con lo cual, todos los usuarios que tengan privilegios sobre el rol, a su vez tendrán los mismos privilegios que tiene el rol. Si mas tarde se quiere modificar alguno de los privilegios que tiene ese grupo, solo hay que ir a modificar los privilegios del rol.

· SINÓNIMOS:

Un sinónimo es asignar un alias a una tabla. Esto sirve para que cada vez que te quieras referir a dicha tabla, no tengas que poner el nombre y el propietario de la tabla. Con poner el sinónimo ya se hace referencia a dicha tabla.

· ÍNDICES:

Sirven para permitir un acceso más rápido a los datos. Es una tabla más reducida, que hace referencia a una columna de una tabla de datos, y que los guarda en un orden ascendente o descendente. Este objeto ocupa espacio físico en la base de datos. Un índice puede estar formado por varias columnas concatenadas, siempre en orden. Una vez que se crea un índice hay que especificar el nombre de la tabla del que va a ser índice. También hay que especificarle las columnas por las que se va a hacer el índice y el orden tanto de los campos como de la indexación. El elemento de un índice no puede ser único, pero solo hace una única referencia a la tabla.

RESTRICCIONES DE INTEGRIDAD

El oracle nos ofrece varias de estas restricciones:

NOT NULL (relacionado con una columna de la tabla): Esa columna, para cualquier fila que se introduzca en dicha tabla, ha de tener un valor, es decir, que no permite introducir una fila en la tabla sin haber dado un valor a dicha columna.

RESTRICCIÓN DE CLAVE ÚNICA (Unique Key): Esta es una restricción para una columna o para un conjunto de ellas. No puede haber dos filas en una tabla que tengan el mismo valor en dicha o dichas columnas.

RESTRICCIÓN DE CLAVE PRIMARIA (Primary Key): Se asocia a una o varias columnas. Identifica de forma única y unívoca a cada una de las filas de una tabla, con lo cual no puede tener duplicados y no pueden admitir valores nulos.

RESTRICCIÓNES DE INTEGRIDAD REFERENCIAL: Intervienen varios objetos:

Foreign Key (clave ajena): Columna o conjunto de columnas que van a ser referenciadas en otra tabla
Clave referenciada: Es aquella clave única o clave referenciada de la tabla referenciada.
Tabla ajena: Tabla en la que se encuentra la clave ajena
Tabla referenciada: Es la tabla en la que se encuentra la clave referenciada.

UPDATA O DELETE RESTRICT: No deja actualizar o borrar una fila de una tabla si de ella dependen otros datos o si se rompe la integridad referencial de la base de datos.

DELETE CASCADA: Cuando se borra una fila de una tabla se eliminan también las filas de las tablas que dependen de dicha fila.

RESTRICCIÓN DE TIPO CHECK: Son las restricciones de condición que tienen que cumplir las columnas para poder introducir los datos.

SQL Y SQL PLUS

Es un lenguaje no procedural, en el cual hay que dar muy pocas instrucciones para manejar la base de datos. El SQL plus es una herramienta que nos permite manejar el SQL. Es útil para manejar informes básicos.

El lenguaje SQL maneja funciones de los lenguas de programación DDL y DML, es decir, que puede definir las tablas y tipos de datos y también puede insertar, modificar y borrar datos de las tablas definidas.

Dentro de la parte de definición de datos tenemos las siguientes instrucciones:

CREATE TABLE

CREATE TABLESPACE

CREATE INDEX

CREATE VIEW

DROP TABLE

DROP TABLESPACE

DROP INDEX

DROP VIEW

CLAUSULA DE ALMACENAMIENTO

Esto habrá que aplicarlo a todos aquellos objetos que ocupan espacio físico a la hora de crearlos.

STORAGE (INITIAL NK/NM {en Kb o en Mb})

El parámetro Initial indica el tamaño de la primera extensión en Kb o en Mb

El parámetro next indica el tamaño de la siguiente extent también en Kb o en Mb

El parámetro pctincrease indica el incremento de las extensiones cada vez que se crea una nueva.

El parámetro miniextents indica el numero mínimo de extensiones que queremos que tenga nuestro objeto, con lo cual nos reservará n extens al principio

El parámetro maxextents indica el número máximo de extens que queremos en nuestro objeto.

CREACIÓN DE UN TABLESPACE

Se crea con la orden:

CREATE TABLESPACE nombre DATAFILE “nombre de fichero” SIZE nM REUSE

Con el reuse le indicamos que si existe un fichero con ese nombre y del tamaño que le hemos indicado, que lo reuse, es decir, que lo limpie y lo adapte a nuestro uso. Se le puede indicar también AUTOEXTEND ON con el cual irá cogiendo nuevos extents para ese fichero. Si se le indica on después se le puede indicar NEXT nM con lo cual le indicamos el tamaño del siguiente segmento, y después se le puede indicar un MAXSIZE en el cual se le puede indicar UNLIMITED para indicarle que sea ilimitado o un numero de megas determinado.

También se le puede indicar en la misma línea DEFAULT STORAGE(cláusulas de almacenamiento) con lo cual, cada objeto que se cree dentro de este tablespace tendrán por defecto las cláusulas de almacenamiento que se indiquen a no ser que se le indiquen otras específicamente.

CREACIÓN DE UNA TABLA

La sentencia para crear una tabla es como sigue:

CREATE TABLE tablename

( columna 1 tipo de dato longitud NOT NULL DEFAULT valor expresion restricciones de integridad)

( columna 2

( columna 3

8/11/1999

CREACIÓN DE UN ÍNDICE

Cuando se quiere crear un único índice, sin que tenga duplicados se especifica escribiendo CREATE UNIQUE INDEX . Para crear el índice se pondrá lo siguiente:

CREATE INDEX indice.nombre

ON nombre.tabla (columna 1,columna 2,...,columna x)

TABLESPACE nombre

PCTFREE n

STORAGE {clausulas de almacenamiento}

INITIAL nK

NEXT nK

PCTINCREASE n

Como en casi todas las definiciones, las últimas definiciones (desde pctfree) tienen ya valores por defecto, que si nos sirven no tenemos porque definirlos.

EJEMPLO:

“Ejemplo de creación de una tabla en SQL de Oracle”

CREATE TABLE empleados

(cod.empleado NUMBER(4),

nombre.empleado VARCHAR(2),

dir.empleado VARCHAR(2),

cod.dep NUMBER(4),

salario.empleado NUMBER(8,2) );

“Ejemplo de creación de clave única para la tabla de empleados”

CONSTRAIN dir.única UNIQUE (nombre.empleado,dir.empleado)

Si lo que se quiere es crear una clave única de un solo campo de la tabla se puede especificar a continuación de la definición del dato

dir.empleado VARCHAR(40) CONSTRAIN dir.unique UNIQUE USING INDEX ...

Al crear la clave única oracle crea inmediatamente un índice al cual le podemos definir sus características a continuación.

USING INDEX PCTFREE 20 TABLESPACE empleados ...

Todo esto va incluido dentro de los paréntesis de creación de la tabla, como parámetros de esta, y a continuación se pueden seguir definiendo más parámetros de la tabla como los tamaños de extents, etc.

“Ejemplo de creación de una clave primaria en la tabla de empleados”

CONSTRAIN cod.empleado.PK PRIMARY KEY (cod.empleado)

“Ejemplo de creación de restricciones NOT NULL”

Se usará en el ejemplo la tabla de empleados y la siguiente tabla.

CREATE TABLE dept

( id.dep NUMBER(4),

descrip.dep VARCHAR(20))

La tabla dep será padre de la tabla empleados, con lo cual si se quiere definir la clave ajena será de la forma siguiente, dentro de la definición de la tabla hija:

CONSTRAIN dep.dep.fk FOREING KEY (cod.dep) REFERENCES dep(id.dep)

Si se quiere que se tenga en cuenta la forma de borrar los registros en cascada habrá que añadir al final de la definición del constrain las palabras:

ON DELETE CASCADE.

EJERCICIO:

Tenemos dos tablespaces, una para datos (DATE) y otro para índices (INDEX). Vamos a crear una tabla de artículos que va a tener las siguientes columnas:

cod_art (numérico), habrá unos 50.000 articulos aprox.
desc_art (alfanumérico)
existencias_art (numérico), el articulo más numeroso tendrá 2.000.000 de unidades.

Después tendremos otra tabla llamada pedidos que tendrá los siguientes campos:

cod_ped (numérico), tendremos 10.000.000 de pedidos hasta ahora y crecerá el número de pedidos un 20% cada año. Después del primer año solo podrá tener dos extents.
cod_art
fecha_ped (fecha)
fecha_cierre_ped (fecha)
num_art (numérico)

La tabla de de pedidos tendrá una clave única por código de pedidos y codigo de articulos. Habrá una integridad referencial entre articulos y pedidos por el codigo de articulo.

CREATE TABLESPACE data

CREATE TABLE artículos

(cod_art NUMBER(5) CONSTRAIN unq_art UNIQUE

USING INDEX

PCTFREE 10

TABLESPACE index

STORAGE

(INITIAL 260K

NEXT 100K

PCINCREASE 10

MAXEXTENTS 10

desc_art VARCHAR2(20),

exist_art NUMBER(7)

TABLESPACE data

PCTFREE 10

PCTUSED 15

STORAGE

(INITIAL 2M

NEXT 1M

PCTINCREASE 20

MINEXTENTS 2

MAXEXTENTS 5)

);

CREATE TABLE pedidos

(cod_ped NUMBER(9),

cod_art NUMBER(5),

fecha_ped DATE,

fecha_cierre_ped DATE,

num_art NUMBER(5)

CONSTRAIN clv UNIQUE (cod_ped,cod_art)

USING INDEX

PCTFREE 10

TABLESPACE index

STORAGE

(INITIAL 120M

NEXT 20M

PCTINCREASE 20

MAXEXTENTS 20

)

CONSTRAIN integridad FOREING KEY (cod_art)

REFERENCES artículos(cod_art) ON DELETE CASCADE

TABLESPACE data

STORAGE

INITIAL 350M

NEXT 70M

PCTINCREASE 20);

EJERCICIO:

“ Banco: tiene unos 10 millones de clientes. Los datos por cliente serán:

Codigo de cliente(numerico)
Nombre del cliente (carácter)
Dirección (carácter)
Tlfn (numerico)
Fecha de alta del cliente
Fecha de baja del cliente

Habra otra tabla de cuentas con 15 millones con los datos:

codigo de cuenta (numerico)
fecha de apertura
fecha de cierre
saldo (numerico)
codigo de cliente
tipo de cuenta

Existirá otra tabla de tipos con los datos:

tipo de cuenta (numerico)
descripcion (carácter)

Entre clientes un cuentas habrá una relación con integridad referencial por codigo de cliente y entre cuentas y tipos habrá otra relación con integridad referencial por tipo de cuenta. Las dos relaciónes tendrán la propiedad de borrar en cascada. Habrá menos de 100 elementos en la tabla de tipos. Habrá un crecimiento anual del 5% en el de clientes, con lo cual en la tabla de cuentas el crecimiento será al menos del 5%, aunque habrá que tener en cuenta que cada cliente puede tener más de una cuenta.

CREACIÓN DE SINÓNIMOS

La estructura para crear sinónimos es la siguiente:

CREATE SYNONYM nombre FOR nombre de tabla o de vista ;

Con esto nos evitamos tener que escribir cada vez que nos queremos referir a una tabla o a una vista, su nombre y su dueño. Cuando se crea un sinomino es para uso propio, pero si queremos que lo pueda usar cualquiera se pondrá:

CREATE PUBLIC SYNONYM ...

CREACIÓN DE ROLES

Estos sirven para no tener que estar usuario por usuario dando los mismos privilegios para un grupo. Se crea un rol y se le da privilegios, y después a una serie de usuarios se les da el privilegio de poder usar el rol con lo cual tiene los mismos privilegios que le dimos al rol.

CREATE ROLE nombre.rol IDENTIFIED BY password;

Estos roles pueden estar protegidos por contraseña con la palabra IDENTIFIED BY, y si no se quiere proteger por password se tiene que poner NOT IDENTIFIED. Solamente lo puede borrar el usuario que lo ha creado.

Para usar el role hay que usar el comando SET. Este se activa poniendo:

SET ROLE nombre_role IDENTIFIED BY password ;

En el caso de que la password no estuviera bien, daría un error y no daría los privilegios del role.

ELIMINACIÓN DE OBJETOS

La sentencia para borrar objetos es DROP.

ELIMINACIÓN DE TABLAS

Para borrar una tabla se utiliza la instrucción:

DROP TABLE nombre_tabla

Si esa tabla tiene alguna relación de integridad referencial no dejaría borrarla, al haber datos que están relacionados con los datos de esta tabla. Para poder borrarlo se puede usar la instrucción de la forma siguiente:

DROP TABLE nombre_tabla CASCADE CONSTRAIN

De esta forma inhabilita las relaciones de integridad referencial que tenga esa tabla y la da de baja. Con las tablas hijas no hay ningún problema a la hora de borrarlas, sino que el CASCADE CONSTRIAN es para tablas padre que tienen tablas hijas. No borra las tablas hija, sino que quita las relaciones y borra las padres, quedándose las tablas hijas huérfanas.

Cuando se borra una tabla se producen los siguientes efectos:

Se borran todas las filas

Se borran todos los índices relacionados.

Libera y devuelve todo el espacio (data blocks) de la tabla al tablespace

Las vistas relacionadas con esa tabla quedan marcadas como invalidas. No se borran, sino que se marcan.

ELIMINACIÓN DE ÍNDICES

Para eliminar un índice se usa la instrucción:

DROP INDEX nombre_índice

ELIMINACIÓN DE SINÓNIMOS

Para eliminar un sinomio se usa la instrucción:

DROP SYNONYM nombre_sinónimo

Y si era público se especifica PUBLIC

ELIMINACIÓN DE ROLES

Para eliminar un role se usa la instrucción:

DROP ROLE nombre_role

BORRAR FILAS DE UNA TABLA

Si queremos borrar todas las filas de una tabla podemos usar la instrucción:

TRUNCATE TABLE nombre_tabla {DROP STORAGE | REUSE STORAGE}

Si usamos la instrucción DELETE podremos despues recuperar los datos por medio del ROLL BACK pero con el truncate se borra directamente de forma muy rápida y sin la posibilidad de recuperar los datos. Con la opción DROP STORAGE elimina todos los datos y libera todas las extens para el tablespace menos la primaria. Si ponemos REUSE STORAGE simplemente borrara las filas y conservará los extents vacíos.

MODIFICACIÓN DE LAS CARACT. DE UN OBJETO

Se usa el comando ALTER. Se pueden modificar los siguientes objetos:

ALTER TABLE

Podemos modificar las siguientes características de la tabla:

Añadir columnas
Añadir restricciones de integridad
Redefinir una columna
Modificar características de almacenamiento
Habilitar, deshabilitar o borrar restricciones de integridad
Alocar explícitamente una expresión.

EJEMPLO:

ALTER TABLE tabla_name

ADD (nombre_columna tipo_dato DEFAULT expresión Restricción de integridad

ADD (Restricción de integridad a la tabla)

MODIFY(nombre_columna tipo_de_dato longitud DEFAULT expresión Restricciones_de_integridad)

DROP constraint

PCTFREE n

PCTUSE n

STORAGE

(INITIAL Nk

NEXT Nk

PCTINCREASE n

MAXEXTENT

MINEXTENT)

No dejara hacer modificaciones de tipos datos en columnas a no ser que la tabla esté vacia. Si que se puede modificar en algunos casos la longitud de las columnas. También podremos modificar el default y también las restricciones de integridad, pero siempre y cuando las filas que están introducidas ya en la tabla permitan hacer esas restricciones de integridad. También se puede permitir alterar algunos de los parámetros de almacenamiento de la tabla.

Se podrá hacer una modificación del parámetro INITIAL, pero solo tendrá efecto a la hora de que el administrador haga la función de recuperación de datos, cuando se crea otra vez la extent y se vuelven a meter los datos.

Se puede cambiar el tipo de dato de una columna si y solo si la tabla está vacía. Si se puede cambiar el tipo de dato de una columna en una tabla con datos si es para pasar una columna de tipo char a varchar, siempre y cuando no sea menor el numero de bytes del varchar2 que del char. Si en esa columna hay todo nulos también se puede cambiar el tipo de dato.

También se puede cambiar la precisión en los tipos de datos numéricos. Si se aumenta la precisión del numérico no da ningún problema. Si se disminuye la precisión habrá problemas.

Si lo que se quiere es cambiar una clave única, es decir, añadir una, puede que no te deje en el caso de que se haya repetido un código. Lo mismo pasa a la hora de crear claves primarias y relaciones de integridad referencial.

Cuando se quiere añadir columnas a una tabla que tiene filas ya metidas, obligatoriamente esa columna tiene que admitir nulos, puesto que para los elementos que ya existen en la tabla el valor que va a tomar esa columna es el nulo.

ALTER INDEX

Sirve para modificar los parámetros de un índice. Se pueden cambiar los parametros: PCTFREE Y STORAGE (INITIAL, NEXT, PCTINCREASE,MIN..,MAX)

ALTER ROLE

Sirve para modificar los parametros de un rol. Se puede cambiar la password del role con:

ALTER ROLE nombre_rol NOT IDENTIFIED (para quitarselo)

ALTER ROLE nombre_rol IDENTIFIED BY password. (para poner password nueva)

GESTION DE PRIVILEGIOS

Para dar privilegios a un role o a un usuario se usa la orden grant. La estructura de este comando es la siguiente:

GRANT privilegio, privilegio, ...[ ALL ] ON nombre_objeto TO {usuario, role, public}

WITH GRAN OPTION

Con la opción public después del TO se le da dicho privilegio a todo el mundo. Los objetos posibles son tabla o vista.

WITH GRAN OPTION sirve para dar la opción a quien le das el privilegio de poder dar ese privilegio a otra persona. Es decir, que el que recibe el privilegio puede dárselo a otro.

PRIVILEGIOS

Sobre tablas se pueden dar los siguientes privilegios

SELECT: permite ver la tabla

UPDATE: permite actualizar datos de la tabla

DELETE: permite borrar datos de la tabla

INSERT: permite insertar filas en la tabla

ALTER: permite alterar las propiedades de la tabla

INDEX: permite definir indices sobre la tabla

REFERENCES: permite crear relaciones de integridad referencial sobre la tabla

Sobre vistas se pueden dar los siguientes privilegios:

SELECT, UPDATE, DELETE, INSERT

Para quitar los derechos se usa la instrucción REVOKE. Las estructura de esta sentencia es la siguiente:

REVOKE privilegio,.. [ALL] ON nombre.objeto FROM {usuario,role,public} CASCADE CONSTRAIN

COMANDOS DML

Ahora viene una descripción del manejo de las bases de datos.

COMANDO SELECT

Sirve para seleccionar la tabla que vamos a manejar. Su estructura es la siguiente:

SELECT * FROM tabla;

Para recuperar algunas de las columnas de la tabla se usa la siguiente instrucción:

SELECT columna1, columna2, columan3, ... FROM tabla

También se puede recuperar datos de una tabla según una condición que le planteemos:

SELECT * FROM tabla WHERE cond;

Esa condición puede ser que en un campo hubiera un dato determinado.

Los operadores que nos podemos encontrar son: <,>,<=,>=,=,<>,in, not in, between, not between

Tambien se pueden crear máscaras con el comando like

SELECT * FROM tabla WHERE dirección_empleado LIKE `ruiz%'

Enseña a todos los empleados que vivan en una calle que empieze por ruiz. Se puede decir que saque las filas ordenadas con la instrucción ORDER BY columna1, columna2,... detrás de la instrucción select.

Funciones numéricas:

ABS nos devuelve el valor absoluto

CEIL devuelve el mayor entero siguiente

COS devuelve el coseno

EXP devuelve el exponencial

FLOOR es la inversa del CEIL

SQRT devuelve la raiz cuadrada de n

TAN devuelve la tangente

ROUND (N,M) Redondea el primero a el segundo cifras decimales

POWER Eleva el primero al seguno

MOD devuelve el resto de la division del primero por el segundo

SIN devuelve el seno de n

SIGN nos devuelve el signo del numero introducido

TRUNC (n,m) Este corta las cifras decimales

Operaciones con caracteres:

Para concatenar caracteres se usa la instrucción ||

SELECT `ABC'||'DEF' FROM DUAL

También se puede usar el comando CONCAT(`JUAN','DIEGO') FROM DUAL

La instrucción INITCAP devuelve el string que se le pasa con el primer carácter en mayuscula.

La instrucción LOWER(`CaSa') FROM DUAL pasa todos los caracteres en mayuscula de la cadena a minusculas

La instrucción LTRIM(CHAR[,SET]) suprime todos los caracteres por la izquierda hasta que se encuentra un carácter distinto al especificado en el segundo parametro

LTRIM(`xyzXjhg','z') el resultado será Xjhg

La instrucción RTRIM es igual que la anteriór pero en vez de por la izq es por la derecha. Estas instrucciónes son muy buenas para quitar espacios en blanco a cadenas de caracteres.

La instrucción REPLACE (CHAR, CAD1, CAD2) reemplaza las ocurrencias que haya en char de la cadena 1 por las de la cadena 2

REPLACE(`TORERO','RO','RA') devolverá TORERA

La instrucción SUBSTR(CHAR,M,N)

EJERCICIO:

Cuenta	Cod_cliente	Nombre_cliente	Dirección_cli	Codigo_postal	Tipo_cuenta	Saldo_medio_a	Saldo_actual

Preguntas:

1.- Filas que tengan por apellido Lopez

2.- Clientes que vivan en la C/Alta

3.- Vivan en la calle del pez y tengan un saldo mayor de 1.500.000

4.- Vivan en la calle Castilla o aledaños y tengan una cuenta corriente de mas de 250.000

5.1.- Tengan depósitos anuales de menos de 2.000.000 o cuentas corrientes de más de 500.000

5.2.- Tengan depósitos anuales de menos de 2.000.000 y cuentas corrientes de más de 500.000

6.- Tengan cuentas corrientes o depósitos con un saldo inferior a 300.000

7.- Calcular el interes neto que debo pagar a los clientes con depósitos anuales de saldo entre 1.000.000 y 2.000.000 (I=3%)

Respuestas:

1.- SELECT * FROM cuentas WHERE nom_clientes LIKE “%López%”

2.- SELECT * FROM cuentas WHERE dirección_cliente =”C/Alta”

3.- SELECT * FROM cuentas WHERE dirección_cliente=”C/Pez” and saldo_actual>1.500.000

4.- SELECT * FROM cuentas WHERE codigo_postal='39003' and saldo_actual>250.000

5.1.- SELECT * FROM cuentas WHERE (tipo_cuenta=3 and saldo_actual<2.000.000) or (tipo_cuenta=2 and saldo_actual>500.000)

5.2.- (SELECT cod_cli FROM cuentas WHERE tipo_cuenta=3 and saldo_actual<2.000.000) INTERSECT (SELECT cod_cli FROM cuentas WHERE tipo_cuenta=2 and saldo_actual>500.000

6.- SELECT * FROM cuentas WHERE saldo_actual < 300.000

7.- SELECT nombre_cliente,(saldo_actual*3/100) FROM cuentas WHERE (saldo_actual >1.000.000 and saldo_actual<2.000.000)

Funciones de grupo: GROUP BY obligatorio

AVG(n): Calcula la media de los datos de una columna. Un ejemplo de su uso sería:

SELECT AVG(saldo_actual) FROM cuentas WHERE tipo_cuenta=1;

MAX(n): Calcula el máximo elemento de una columna
MIN(n): Calcula el mínimo elemento de una columna
SUM(n): Calcula la suma de una columna
COUNT(n): Calcula el numero de filas de una columna.

Funciones con fechas:

SYSDATE: nos devuelve la fecha y la hora del día.
LAST_DAY (date): devuelve la fecha del último día del mes que se le introduce. Por ejemplo: LAST_DAY (5/12/1999) devolverá 31/12/1999.
ADD_MONTH(date, n) añade n meses a la fecha especificada.
MONTHS_BETWEEN(date1, date2) devuelve el numero de meses que hay entre las dos fechas

Sumar un entero a una fecha te devuelve n días más de la fecha.

EJERCICIO:

Cod_emplea	Cod_dept	Fecha_alta	Nom_emple	Dir_emp	Cod_cat	Sal_base	Compl1	Compl2	Coef_irpf

Cod_departamento	descripcion

Cod_categoria	Descripción

1.- Calcular la masa salarial anual teniendo en cuenta que tenemos 2 pagas extraordinarias al año y estas comprometen al salario base

2.- Haremos una subida salarial del 2% lineal tanto al salario base como a los complementos

3.- A los del departamento de atención del cliente se les hará además una subida del 3% lineal del complemto 1.

4.- Para aquellos trabajadores que tengan un sueldo inferior a 150.000 se les baja un punto del coeficiente de IRPF. Para los que ganan entre 150.000 y 300.000 se les baja 0'25 y a los que ganan más de 300.000 se les sube 0'5 puntos

Borrardo de filas de una tabla DELETE

Para borrar filas de una tabla se usa el comando DELETE. Por ejemplo:

DELETE FROM nombre_tabla WHERE condición;

Actualización de datos UPDATE

El comando UPDATE sirve para actualizar los datos de uno o más campos de una o más filas. Su estructura es:

UPDATE nombre_tabla SET columna1=valor, columna2=valor, ... WHERE condición;

Si no ponemos una condición cambiará el valor de esa columna a todas la filas de una tabla.

Insertar filas en una tabla INSERT

El comando INSERT sirve para insertar nuevas filas en una tabla. Su estructura es la siguiente:

INSERT INTO nombre_tabla columna1,columna2,columna3,... VALUES(valor1, ...)

El nombre de las columnas tiene que estar en el orden en el que se creo la tabla. Si omitimos alguna de las columnas en la lista, y en consecuencia no le damos ningún valor, se le asignará el valor por defecto que tenga definido dicha columna. En caso de que no tenga definido ningún valor por defecto dará un error por no incluir esa columna.

SOLUCIÓN AL EJERCICIO ANTERIOR

1.- SELECT (SUM(sal_base)*14)+(SUM(compl1)+SUM(compl2))*12 FROM empleados

2.- UPDATE empleados SET sal_base=(sal_base * 2/100)+sal_base, compl1=(compl1*2/100)+compl1, compl2=(compl2*2/100)+compl2

3.- UPDATE empleados SET compl1=(compl1*3/100)+compl1 WHERE cod_dep in (SELECT cod_dep FROM departamentos WHERE descripcion LIKE “atención al cliente”)

4.-

UPDATE empleados SET coef_irpf=coef_irpf-1 WHERE sal_base<=150.000

UPDATE empleados SET coef_irpf=coef_irpf-0.25 WHERE sal_base between(150.000, 300.000)

UPDATE empleados SET coef_irpf=coef_irpf+0.5 WHERE sal_base>=300.000

Funciones de conversión

Tenemos básicamente 3 funciones de conversión: TO_CHAR

TO_CHAR(x,formato) Transforma datos de columnas a formato carácter. Puede transformar tanto datos numéricos como de fecha. Se le especificará un formato, que será como una máscara.
TO_NUMBER(char,formato): Sirve para transformar caracteres numéricos a una variable numérica.
TO_DATE(char, formato): Sirve para transformar una fecha que está en formato de carácter a una variable fecha.

Formatos máscara para números

Indicando nueves en la máscara se indicará la precisión que queremos que tenga el número. Por ejemplo:

TO_CHAR(124,'99999') = “__124”

Indicando una S le decimos que nos muestre el signo:

TO_CHAR(124,'S9999')=”_+124”

Con la D, el `.' o `,' le indicamos donde irá el punto. Se pondrá `.' o `,' según que es lo que esté definido en el sistema como separador decimal.

TO_CHAR(1234.678,'9999.9')=”1234.7”

Con la G, la `,' o `.', según lo que esté especificado por el administrador en el sistema, pondremos los separadores de los miles:

TO_CHAR(12345.678,'9G999D99') = “12.345,68” (En el caso de que esté configurado el punto como separador de miles y que la coma sea el separador de decimales.

Si ponemos E's pondrá formato de potencias de 10

Si ponemos L nos pondrá el símbolo de la moneda.

Formato de máscara para fecha

A la hora de convertir variables de tipo carácter con números a tipos de variable numérico tendremos que poner la máscara dependiendo de cómo ofrezca el char la información numérica, es decir, que habría que decir que máscara tiene la variable de tipo carácter con los números. En el caso de que el formato que le incluimos no sea el correcto nos dará un error.

Formato de mascara para fecha

Poniendo Y te pone el año. Se puede poner de 1 a 4, dependiendo de las cifras con las que queramos el año. Si ponemos YEAR nos pondrá el año en letras. Con MM nos mondrá el número del mes. Con MONTH nos pondrá el mes en letras.

CONTINUACIÓN DEL EJERCICIO ANTERIOR

5.- Dar de alta a un nuevo empleado

6.- La lista de empleados que se incorporan a la empresa después del 1 de Marzo de 1981

7.- Crear una nueva categoría `Directivos estrategicos', con cod_catg 90. Cambiar a esta categoría los empleados que pertenecían a la categoría de directivos y que tienen un salario_base de más de 1.000.000

SOLUCIONES:

5.- INSERT INTO empleados cod_emp, cod_dep, fecha_alta, nom_empleado, dir_empleado, cod_categoría, sal_base,compl1,compl2,coef_irpf VALUES (...)

6.- SELECT * FROM empleados WHERE fecha_alta > TO_DATE(“01/03/1981”,DD/MM/YYYY)

7.-

A) INSERT INTO categorías cod_categoria, desc_categoría VALUES (90,”Directivos estratégicos”)

B) UPDATE empleados SET cod_cat=90 WHERE cod_cat in (SELECT cod_cat FROM categorías WHERE descripcion LIKE `Directivos') and sal_base < 500.000

16/11/1999

MANEJO DEL ENTORNO DE SQL PLUS

Para ver todas las tablas que tenemos cada usuario tenemos que ver las filas que tiene la tabla cat, que es el tabla del esquema de usuario de cada uno.

SELECT * FROM CAT;

17/11/1999

EJERCICIO:

Pasar los ejercicios anteriores al SQL PLUS

CREACIÓN DE VISTAS EN EL SQL PLUS

CREATE VIEW empl_view AS (SELECT cod_emp,nombre,dirección,fecha_alta from EMPL);

Este comando creará un objeto vista con únicamente las columnas que se le especifican en el select que está entre paréntesis. Una vez creada la vista podemos manejarla como si fuera una tabla, con la salvedad de que si queremos meter datos, en el caso de que no se vean todas las columnas de la tabla a la que se referencia (o de las tablas), estos datos que no se ven tendrán que poder tener el valor nulo o un valor por defecto, porque ese será el valor que se le asigne.

18/11/1999

Se puede hacer que en una vista solo aparezcan ciertas filas de la tabla, simplemente poniendo las condiciones del where en la sentencia select que se especifica entre los paréntesis. También se puede especificar los nombres que aparecerán en la cabecera de la tabla

INSTRUCCIÓNES PARA INTERACTUAR CON VARIOS SELECT

Estas instrucciones son: UNION, UNION ALL, INTERSEC, MINUS. Con estas instrucciones se puede hacer salidas conbinadas entre varias instrucciones select con diferentes tablas. Lo lógico es que la salida de los dos select tengan las mismas columnas o el mismo tipo de columnas.

Con el UNION saldrian las columnas de los dos select como si hubiera sido uno solo. Con el UNION ALL sería la misma salida que con el anterir, pero con la salvedad de que no daldrían columnas repetidas. Con INTERSEC solo saldrían aquellas filas que fuesen iguales en los dos select. Con el MINUS saldrían todas las filas que están en el primer select menos las filas que aparezcan en el segundo select.

Un ejemplo sería:

(SELECT * FROM empleados WHERE cod_emp<3) UNION (SELECT * FROM empleados WHERE cod_emp >5)

19/11/99

AGRUPACIONES DE FILAS DENTRO DE TABLAS

Este comando sirve para hacer agrupaciones de filas por el valor de columnas o para utilizar set funciones de grupo. Por ejemplo, para sacar la masa salarial por cada categoria se podría usar la siguiente instrucción:

SELECT cod_categoria, SUM(sal_base) FROM empleados GROUP BY (cod_categoría)

Esto lo que hace es sacar las sumas de los salarios base de todos los empleados, pero agrupadas por la categoría de los empleados. También se pueden hacer varias agrupaciones diferentes dentro de una misma sentencia select:

SELECT cod_dep,cod_cat,SUM(sal_base) FROM empleados GROUP BY cod_dep,cod_cat

REDIRECCIONAR SALIDAS A FICHEROS

Para redireccionar la salida de datos a un fichero se usa el comando SPOOL seguido de un nombre de fichero con ruta. Todas aquellas salidas que hagamos con el SQL plus nos irá al fichero especificado. A parte de salir por pantalla también se guardará en un fichero. Para que no nos siga guardando las salidas por pantalla a fichero se usa SPOOL OFF.

USO DE VARIABLES EN FICHEROS DE COMANDOS DE SQL

A la hora de crear ficheros de texto con instrucciones, se pueden poner variables de la forma &1, &2,... de tal forma, que cuando se ejecuta dicho archivo de texto con la instrucción @ se le especifican a continuación los valores de las variables en orden respectivamente.

Para definir variables se usa el comando DEFINE. Una vez definida una variable se puede usar en el entorno SQL. Sería como tener un sinónimo almacenado en la memoria que se puede usar en cualquier sentencia SQL. Estas variables van siempre precedidas del símbolo &. Para referirse a una variable dentro de una sentencia aparecerá entre comillas en el caso de que tenga que estar entre ellas. Si cuando se ejecuta una sentencia en SQL con una variable esta no existe, te pedirá seguidamente cual es el valor que se le asocia. En este último caso la variable que no existía no se crea, pero en el caso de que queramos que se cree dicha variable podremos hacerlo con dos símbolos & precediendo al nombre en vez de con uno.

COMANDOS PARA EL FORMATEO DE FORMULARIOS

Comando SET: Este comando sirve para modificar algunos de los parametros del SQL plus. Estos parametros son:

SET PAUSE on/off: Hace un pause cada vez que los datos visualizados con un select llenan la página.
SET HEADING on/off : Activa o desactiva las cabeceras de los comandos select.
SET ECHO on/off
SET FEEDBACK on/off: Enseña el numero de filas resultantes de una sentencia select.
SET PAGESIZE n: Especifica el numero de líneas que hay por cada página. Por defecto son 14 páginas.
SET LINESIZE n: Especifica el numero de caracteres que va a tener cada línea. Por defecto son 80 caracteres.

Comandos para el formateos de salidas: Estos son los comandos que nos van a permitir formatear la salida de los select, como poner títulos, encabezados, etc.

TTITLE

Este comando sirve para poner un titulo a la salida de datos de un comando select. Por ejemplo:

TTITLE `Informe de pruebas | DAI'

Con este comando saldrá un titulo de dos líneas (separadas por el símbolo | ) antes de cada sentencia select. En caso de que no le especifiquemos nada nos pondrá el título con una justificación centrada. Podremos nosotros especificarle otra justificación con las expresiones:

COL n, LEFT, RIGHT o CENTER. Podemos usar la expresión SKIP para que salte un numero determinado de líneas antes de escribir el título. Ejemplo

TTITLE COL 4 “Informe de prueba” SKIP CENTER “DAI”

Con este ejemplo nos escribiría el primer titulo con la justificación en la cuarta columna, y el segundo dos líneas más abajo con la justificación centrada.

También se le puede decir que el texto salga en negrita con la expresión BOLD, con lo cual el texto sale tres veces en tres líneas diferentes resaltándolo.

BREAK

Con esta sentencia hace rupturas en el select de una tabla según los valores de una columna de dicha tabla. Es muy util en el caso de querer dividir en pequeñas tablas una tabla según el valor que tenga una columna.

Break on cod_dep

Con este ejemplo nos haría diferenciaciones entre los empleados de diferentes departamentos. Si le añadimos la expresión SKIP a continuación nos dejará un espacio en blanco después de cada apartado.

COMPUTE

Nos permite hacer cálculos sobre los datos de la tabla. Este comando está siempre ligado a un comando BREAK anterior. Con lo cual, si ponemos:

COMPUTE SUM of sal_base on cod_dep;

Nos sacará las sumas de los salarios base dentro de un departamento.

EJERCICIO:

Calcular los sueldos mínimos y medios por departamento en la tabla de empleados.

SOLUCIÓN:

BREAK ON cod_dep SKIP 1;

COMPUTE MIN OF sal_base+compl1+compl2 ON cod_dep;

SELECT cod_emp,cod_dep,nombre,apellidos,sal_base+compl1+compl2 from empleados order by cod_emp;

Con esto se calcula el salario mínimo, y para calcular la media de salarios dentro de un mismo departamento seguiríamos limpiando la sentencia compute y modificandola para que calcule la media de cada apartado:

CLEAR BREAK;

COMPUTE AVG OF sal_base+colmpl1+compl2 ON cod_dep;

SELECT cod_emp,cod_dep,nombre,apellidos,sal_base+compl1+compl2 from empleados order by

TRANSACCIONES ORACLE

Es un conjunto de sentencias de SQL que comienzan cuando empieza el programa, cuando hacemos un commit, cuando se ejecuta una sentencia feedback o cuando se encuentra una sentencia DDL. Termina la transacción cuando se acaba el programa, después de ejecutar un commit, después de un rollback o después de una sentencia DDL.

Los cambios se hacen efectivos en el sistema gestor de base de datos cuando ocurren una de estas cuatro cosas. Todos esos cambios que estamos realizando no son realmente buenos, es decir, que no se modifica el fichero, hasta que se realiza una de esas cuatro operaciones. En el caso de que se vaya la luz, se perderían todos los cambios que se han efectuado desde la ultima vez que ocurrió una de esas cuatro cosas.

JOIN

Podemos hacer en un mismo select una consulta de datos de diferentes tablas, siempre y cuando exista alguna relación entre dichas tablas. Así, podemos sacar el listado de empleados de nuestra base de datos y podemos poner la descripción del departamento de cada empleado haciendo referencia con el cod_dep al fichero de departamentos.

SELECT empleados.cod_emp,empleados.nombre,departamentos.desc_dep,empleados.sal_base FROM empleados,departamentos WHERE empleados.cod_dep=departamentos.cod_dep;

Los campos irán precedidos por el nombre de la tabla, y después del FROM tendremos que especificar el nombre de las tablas referenciadas. Después habrá que decirle cuales son las relaciones entre campos de los diferentes tablas después del WHERE

INSTRUCCIÓN BTITTLE

Esta instrucción formatea los pies de página de la misma forma que el TTITLE para las cabeceras de página.

REPORT

En los break podemos especificar un report de la forma BREAK ON REPORT para que haga una sentencia COMPUTE al final de la ejecución de una sentencia SELECT

COLUMN

Podemos definir el nombre que queremos que salga en una columna en vez de la salida normal que es el nombre de la columna en la tabla. Para ello usaremos el comando COLUMN de la siguiente forma:

COLUMN cod_emp HEADING `codigo de empleado';

También con este comando podemos definir una longitud de campo máxima, para que no nos muestre toda su información en caso de que sea muy larga. Para ello haremos lo siguiente

COLUMN desc_dep FORMAT A20;

Puede ocurrir que si una palabra no entre en el campo después de formatearlo la corte o que escriba justamente debajo lo que queda de texto. Esto se puede controlar desde la propia instrucción con:

Wrap: Los caracteres que no entran nos los pone justamente debajo.

Wordwrap: Lo mismo que el anterior pero con palabras enteras.

Truncate: Nos cortaría las palabras y no mostraría la información que no entra.

También podemos alinear los titulos de las columnas con el comando JUSTIFY y especificandole que tipo de justificación queremos: CENTER, RIGHT o LEFT

CAMBIO DE PASSWORD

Para cambiar la password tendremos que ejecutar la siguiente instrucción:

ALTER USER nombre.usuario IDENTIFIED BY nuevo_password;

24/11/99

OTROS COMANDOS

COMANDOS ANY E IN

Con el ANY se le puede especificar una serie de valores entre paréntesis y separados por comas. Se usa en condiciones y la condición se cumpliría cuando se cumpliera con alguno de los valores que se le especifican. Con el in tendrá que cumplirse la condición de que el valor tiene que estar en la lista que se le especifica entre paréntesis y separado por comas.

Select * from emp where cod_emp in (1,2,3,4,5)

Select * from emp where sal_base >(sal1,sal2,sal3)

COMANDO ALL

El comando ALL es muy parecido al comando ANY. En el anterior la condición se tenía que cumplir con alguno de los valores que se le especificaban entre paréntesis. Con el ALL se tiene que cumplir la condición con todos los valores que se le especifican entre paréntesis.

COMANDO DECODE

Select apellidos,DECODE (cod_banco,'111222','santander','222333','Bankinter', '333444',Asturias','ninguno') from emp;

Este comando nos mostrará un dato dependiendo del valor que tenga el campo que se le especifica al principio. Se le van dando valores en pareja, el primero es el valor que tiene que tener el campo y el segundo el nombre que aparecerá, y al final se le especifica un string que aparecerá si el valor que tiene dicho campo no está especificado en el decode.

EJERCICIO:

“La empresa para la que ya tenemos una base de datos con los empleados también quiere tener la opción de poder dar prestamos a sus empleado, para lo cual tendremos que crear una tabla de prestamos:

cod_prest numérico not null

cod_emp numérico not null

tipo_prest numérico not null

fecha_con date not null

fecha_can date null

importe numérico not null

interes numérico default = 0

n_años numérico not null

También necesitamos una tabla de tipos de prestamos que tendrá dos columnas: cod_prest numerico y not null; y desc_prest de tipo carácter que admita hasta 50 caracteres. Habrá restricciones de integridad referencial entre prestamos y empleados por el cod_emp y entre prestamos y tipos de prestamos por el campo tipo_prestamo.

También tendremos otra tabla de pagos_prestamo que tendrá los campos:

Cod_prest numerico not null

Fecha_pago date not null

Importe_pago numerico

Esta última tabla va a tener una clave externa por cod_prest a la tabla de prestamos.

Crear una vista con las mismas columnas que tenga la tabla prestamos pero que solo muestre los prestamos activos.

Crear una vista con los prestamos cerrados.

Hacer un informe con los empleados a los que se les ha prestado durante el año en curso. Tendrá que estar agrupado por el tipo de préstamo. Para cada tipo de préstamo obtener el importe total prestado y al final del informe que aparezca el total prestado. En el informe debe aparecer el nombre del empleado, el importe del préstamo, la fecha de concesión y el tipo de préstamo.

Utilizar el informe anterior para obtener otro con los prestamos abiertos el primer trimestre.

Obtener un informe con todos los prestamos “abiertos” agrupados por departamentos y tipo de préstamo.

Informe:

Crear una vista a partir de la tabla pagos_préstamo en la que aparezca el código de préstamo, la suma de importes para cada préstamo y el nº de pagos.

Obtener para cada empleado con un préstamo activo, el código del préstamo, el importe

total de pagos efectuados y el nº de pagos efectuado.

Obtener un informe en el que aparezcan reflejados los importes medios de los prestamos de cada tipo concedidos en los últimos 3 años.

Obtener un informe con el nombre de los empleados que tengan activo mas de un prestamo.

Obtener la lista de los empleados que no han tenido nunca ningún préstamo agrupado por departamentos y ordenados por categorias.

Obtener la lista de los empleados que han pagado durante este año mas de 100.000 pts en concepto de amortozación de prestamos. Agrupados por:

Codigo de empleado y amortización

Nombre de cliente y amortización.

Añadir a la tabla de prestamos una nueva columna `saldo' que representa el saldo de cada préstamo y actualizarlos con los valores reales de los saldos.

Consultas al catalogo:

Espacio libre disponible para cada uno de nosotros.

Obtener un informe con información de las tablas: nombre columna, tipo de dato. Agrupado por nombre de tabla y ordenado por columnas.

Obtener información nueva de lo que ocupan realmente, vuestras tablas.

Obtener las constraint que teneis definidas.

FALTA ALGO DE APUNTES (1 HORA)

USER INDEXES

Información para cada índice del que soy propietario.

Nos dara : index.name, table_name, uniqueness, tablespace_name, initial_extent,

min_extent, nax extent, pct_free, pct_increase.

User_ind_columns

Nos dara informacion como: index_name, table_name, ncolumn_position, column_length.

USER_RESOURCE_LIMITS

Una fila con los recursos sobre los objetos en los que tienes un limite.

USER_ROLE_PRIVS

Roles a los que tenemos acceso: username, granted role.

Granter: el que otorga el privilegio.

Grantee: recibe el privilegio.

USER_SEGMENTS:

Información relativa sobre los segmentos en los que yo soy propietario.

Segment_name, segment_type,tablespace, bytes, blocks etc es lo que nos dara de info.

USER_SYNONYM:

Info relativa a los sinónimos que yo he definido. Nos da información sobre:

Synonym_name, table_owner, table_name.

USER_TABLE

Información sobre mis tablas.

Table_name, table-space_name, pct_free, pct_used, max_extent etc.

USER_TAB_COLUMNS

Guarda una fila por cada una de las columnas que hay en cada una de las tablas de nuestra propiedad. Table_name, column_name, data_type, data_legth, data_precision, data_scale, default, column id etc.

USER_TABS_COMMENTS

Lo mismo que el anterior pero en vez de filas guarda comentarios.

USER_TAB_PRIVS

Informacion de la tablas sobre las que hemos dado un privilegio.

USER_TS_QUOTAS

Información relativa a la quota definida en un tablespace.

PL/SQL

CURSORES

=========

Declaración, apertura, acceso a filas y cerrado (fases de un cursor)

El cursor se define en la declare (parrafo de declaracion de variables):

Declare

Cursor nombre_cursor is select .......

Ejemplo cursor c1 is select cod_cliente, nombre from clientes where cuota > 20000;

En este momento todavia on ha ido a la base de datos a hacer la consulta.

Dentro del parrafo BEGIN del programa se abre el cursor

BEGIN

Open cursor en nuestro caso c1.

FETCH nombre_cursor INTO var1, var2 etc (en el ejemplo seria fetch c1 into

v_cod_cliente, v_nombre;

(por cada fetch el apuntador salta de fila y lee la siguiente es un tratamiento secuencial).

Y se termina con un CLOSE nombre_cursor; (las filas dejan de estar disponibles para mi programa).

Si la sentencia select de la definicion del cursor llevase una variable por ejemplo where cuota > v_cuota la sentencia open verificara los valores y los sustituira para obtener resultados concretos.

Al abrir el cursor se pueden definir las variables que utilizara el propio cursor.

Cursor nombre_cursor ([variable tipo [=valor]) is select .......

Ejemplo : cursor c1 (cuota_minima unmber) is select descripcion from actividades where cuota_socio > cuota_mínima;

Open c1 (5000 cuota mínima que pedimos y resto de variables si las hubiese).

% notfound sera true si el ultimo fetch ejecutado contra un cursor no encuentra ninguna fila en el cursor el atributo notfound estará a true y viceversa a falso. Not found valdrá null cuando se haya echo un open sin un primer fetch.

%Found devolvera true cuando el ultimo fetch haya encontrado fila y estara a false si el ultimo fetgh sino encuentra filas.

%rowcount: n º de fials recuperadas por fecht

ejemplo

open

loop

sentencias;

fetch c1 into.........;

if % c1 rowcount > 100 then

exit;

end if;

end loop;

%isopen esta a true si el cursor esta habierto y viceversa.

Para codificar un programa en pl/sql se puede hacer en un ficheero de taxto o irlo codificando directamente en SQL plus. Según vea un declare ya sabe que es un pl/sql y no una sentencia de sql plus.

Ejercicios

1.- Actualizar el nombre del cliente `a1111' a `Antonio Jose'

2.- Actualizar la cuota del cliente a1111 incrementándosela en un 10% si la antigüedad es mayor de

2 años o un 8% si es mayor de tres años o un 5% en otro caso.

ROWID: es un número con tres campos con una extructura XXXXXXXX.YYYY.ZZZZ. Las x es el numero de bloque o data block al que pertenece la fila (identificativo unico que se asigna al dar de alta una fila en una tabla y que permanece inalterable mientras exista la tabla) y puede ser consultada. Las YYYY es el numero de data block ZZZZ es el numero de fichero o data file donde se aloja el data block.

FALTA MUCHO Y MUCHO Y MUCHO

EJERCICIOS

1.- Hacer PL/SQL que actualice las cuotas de los clientes activos con el criterio:

Antigüedad menor de 2 años = 10%
Antigüedad menor de 3 años = 8%
Antigüedad mayor de 3 años = 5%

2.- Crear una tabla como la de clientes. Construir un PL/SQL que cargue en la nueva tabla las filas de la tabla clientes.

3.- Para mantener los saldos de la tabla de prestamo.empleado.

4.- Para mantener la tabla de los pagos para los prestamos activos.

Desarrollo de aplicaciones en lenguajes de 4º Generación

Tal vez te pueda interesar:

Descargar

Enviado por:	Samuel Suero
Idioma:	castellano
País:	España

Palabras clave:

Lenguaje de consulta estructurado relacional Sistema gestor de bases de datos Sintaxis

Te va a interesar