GUÍA BÁSICA PARA INSTALAR
Y CREAR BASES DE DATOS Jacome Daniel
Piñeiros Paúl
Pilacuán Mónica
Salguero Francisco
Piñeiros Christian
Valenzuela Fernando
ORACLE INTRODUCCION:
Oracle Database 11g para Windows ofrece una solución optimizada de base de datos para aquellas implementaciones que requieren escalabilidad, confiabilidad y alto desempeño empresarial. Al utilizar un modelo de servicios Windows nativo y basado en threads, Oracle Database 11g garantiza gran desempeño y escalabilidad. La base de datos Oracle se integra por completo con las características avanzadas del sistema operativo Windows y el hardware subyacente, como el soporte NUMA y de Paginación. Oracle ofrece desempeño de primer nivel a través del soporte de memorias extendidas, archivos binarios y de gran volumen, y grid computing. Oracle Database 11g para Windows ha evolucionado de un puerto del servidor de base de datos UNIX a una aplicación nativa bien integrada que aprovecha al máximo los servicios y características del sistema operativo Windows y el hardware subyacente. Oracle continúa mejorando el desempeño, la escalabilidad y la capacidad de su servidor de base de datos Windows, mientras que al mismo tiempo genera una plataforma estable, altamente funcional en la cual crear aplicaciones. Oracle se encuentra completamente comprometido a brindar la base de datos de mayor desempeño para las plataformas Windows de 32 y 64 bits.
ARQUITECTURA ORACLE En Oracle se usa dos términos que son usados muy habitualmente por los profesionales en bases de datos Oracle instancia (instance) y base de datos (database). El termino database se refiere al almacenamiento físico de la información, mientras que instance se refiere al software que se esta ejecutando en un servidor y provee acceso a los datos. Una instancia corre en un computador donde esta almacenada la base de datos en disco. Oracle usa un área de memoria compartida llamada System Global Area (SGA) y un área de memoria privada para cada proceso llamada Program Gobal Area (PGA). Una instancia puede ser parte de solo una database y multiples instancias pueden ser parte de la misma database. Una instancia puede ser interpretada como un puente hacia la base de datos como se muestra en la figura.
ESTRUCTURA LÓGICA DE ORACLE Tablespace Una tablespace es una estructura lógica. Cada tablespace esta formada por estructuras físicas llamadas datafiles; cada tablespace contiene uno o mas datafiles. Cuando se crea una tabla, se puede especificar el tamaño de la tabla en la cual se creará. Los espacios de tablas se utilizan para mantener juntos los datos de usuarios o de aplicaciones para facilitar su mantenimiento o mejorar las prestaciones del sistema.
Un espacio de tablas puede quedarse offline debido a un fallo de disco, permitiendo que el SGBD continúe funcionando con el resto.
Los espacios de tablas pueden estar montados sobre dispositivos ópticos si son de sólo lectura.
Permiten distribuir a nivel lógico/físico los distintos objetos de las aplicaciones.
Son una unidad lógica de almacenamiento, pueden usarse para aislar completamente los datos de diferentes aplicaciones.
Oracle permite realizar operaciones de backup/recovery a nivel de espacio de tabla mientras la BD sigue funcionando.
Archivos de una Base de Datos Hay tres tipos de archivos físicos en una base de datos:
Archivos de Control
Archivos de datos
Archivos de Redo log
Estos tres archivos representan la base de datos física. Archivos de control Contiene las localizaciones de otros archivos físicos que son de la base de datos, a demás de información acerca del contenido y el estado de la base de datos como:
El nombre de a base de datos
Fecha de creación
Nombres y localizaciones de los archivos de datos y ficheros redo log
Información del tablespace.
Una base de datos debería tener como mínimo dos archivos de control en diferentes discos físicos. Si no hay una copia de un archivo de control, se corre el riesgo de perder porciones de la base de datos. Aunque se puede reconstruir estos archivos, esto puede traer riesgos al momento de reconstruirlo. Se puede especificar muchas copias del archivo de control en los parámetros del archivo de esta manera: control_files
= (/u00/oradata/control.001.dbf, /u01/oradata/control.002.dbf, /u02/oradata/control.003.dbf)
Archivos de Datos Los archivos de datos contienen los datos almacenados actualmente en al base de datos, las tablas e índices, el diccionario de datos que mantiene información acerca de estas estructuras de datos. Los ficheros de datos contienen los datos de la base de datos. Tienen las siguientes características:
Un fichero sólo puede estar asociado con una base de datos.
Los ficheros de datos tienen atributos que permiten reservar automáticamente para ellos extensiones cuando se acaba el espacio.
Uno o más ficheros de datos forman una unidad lógica de almacenamiento llamada tablespace.
Número de secuencia del redo log en curso
Información de checkpoint
Información de los archivo redo log
Información de backup
Se trata de un fichero binario, sin el cual no es posible arrancar la BD. Por ello es conveniente mantener varias copias del mismo, en diferentes discos. Se lee al montar la BD. Su tamaño es fijo, y depende de los parámetros indicados al crear la BD con CREATE DATABASE; como por ejemplo MAXLOGFILES y MAXDATAFILES. Archivos Redo log Estos archivos contienen una grabación de los cambios hechos a la base de datos como resultado de transacciones y actividades internas de Oracle. Esto puede servir para hacer un control de las transacciones en el caso de algún fallo para recuperar la base de datos. Los ficheros redo log se organizan en grupos. Una BD requiere al menos dos grupos. Cada fichero redo log dentro de un grupo se llama miembro. La idea es que cada grupo tenga tantos miembros como discos disponemos para mantener las copias de los redo. Lo usual es tener 3 grupos de redo con 2 miembros cada uno.
Los ficheros redo log se usan de manera circular: cuando uno se llena, el LGWR comienza a escribir en el siguiente grupo (“log switch”), hasta volver al primero. Cuando ocurre un “log switch”, también sucede un “checkpoint”; y se actualiza el fichero de control. Podemos forzar un log switch o un checkpoint explícitamente con:
ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM CHECKPOINT;
El LGWR escribe al hacer commit, o cada 3 segundos, o si el buffer redo log se llena 1/3, y antes de que el DBWR vuelque los cambios de los buffers de datos a los ficheros de la BD. Añadir grupos y miembros de redo Añadir grupos al Redo Log Online: ALTER DATABASE ADD LOGFILE GROUP 3 (‘/u04/oradata/CURSO98/redo03a.log’, ‘/u03/oradata/CURSO98/redo03b.log’) SIZE 1M;
Añadir miembros Redo Log Online : ALTER DATABASE ADD LOGFILE MEMBER ‘/u03/oradata/CURSO98/redo01b.log’ TO GROUP 1, ‘/u03/oradata/CURSO98/redo02b.log’ TO GROUP 2;
Eliminar grupos y miembros de redo Eliminar grupos del Redo Log Online (por ejemplo si he creado otros más grandes): ALTER DATABASE DROP LOGFILE GROUP 3;
No puede haber menos de 2 grupos; no se puede borrar el grupo activo; al borrar un grupo no se eliminan los ficheros del sistema operativo. Eliminar miembros Redo Log Online: ALTER DATABASE DROP LOGFILE MEMBER ‘/u03/oradata/CURSO98/redo03b.log’;
TIPOS DE DATOS DE ORACLE ALFANUMÉRICOS: Van encerrados entre comillas simples, por ejemplo: “Oracle11” Tipo de datos
char
varchar2 nchar
Oracle 11g Tamaño máximo de 2000 bytes.
Tamaño máximo de 4000 bytes. Tamaño máximo de 2000 bytes.
nvarchar2
Tamaño máximo de 4000 bytes.
long
Tamaño máximo de 2GB.
Características Almacena cadenas de longitud fija, es decir siempre ocupará lo mismo, independientemente del valor que contenga, el número de caracteres que se pueden almacenar se rigen mediante la siguiente fórmula: nº caracteres = bytes/carácter set para ASCII Almacena cadenas de longitud variable. Almacena cadenas alfanuméricas de longitud fija que sólo almacena caracteres Unicode. El juego de caracteres del tipo de datos (datatype) sólo puede ser AL16UTF16 ó UTF8 y se especifica cuando se crea la base de datos Oracle. Almacena cadenas de caracteres alfanuméricos de longitud variable, puede almacenar caracteres ASCII, EBCDIC o Unicode. Cadenas de longitud variable, (compatible hacia atrás)
NUMÉRICOS: Son números simples, por ejemplo: 123 Tipos de datos
number(p,s)
Float (p)
Oracle 11g La precisión puede variar desde 1 a 38. Escala pueden ir de -84 a 127. La precisión binaria puede moverse en el rango de 1 a 126.
Características Donde p es la precisión, es decir el número de dígitos contando los decimales que contendrá el número como máximo y la s es la escala, es decir el máximo de números decimales. Por ejemplo, el number (5,-2) redondeará siempre a centenas, si queremos introducir el valor 1355, en realidad se almacena 1400. Donde p es la precisión binaria, almacena un número en punto decimal sin restricciones de dígitos decimales.
FECHA: Las fechas van en comillas simples, por ejemplo: ‘1-dic-2000’ Tipo de datos
date
Oracle 11g Una fecha entre enero 1, 4712 AC y el 31 de dic, 9999 DC.
Características Almacena un valor de fecha y hora, El formato por defecto de las fechas es: ‘DD-Mon-YYYY’. Internamente una fecha se almacena como un número de días desde cierto punto de inicio. Por ejemplo: ‘1-jan-2001’+10 = ‘11-jan-2001’
OTROS: Tipo de datos Rowid
Oracle 11g Guarda punteros a filas concretas.
Características Es un identificador único para la fila dentro de una base de datos, no hay dos filas con el mismo ROWID
DDL (Data Definition Languaje - lenguaje de definición de datos) Las sentencias DDL son aquellas utilizadas para la creación de una base de datos y todos sus componentes: tablas, índices, relaciones, disparadores (triggers), procedimientos almacenados, etc. Create Database Sirve para crea una base de datos. Desde el punto de vista físico, una base de datos es, para oracle, un conjunto de ficheros, a saber:
datafiles, ficheros de datos, definidos en la creación de la base de datos. log files, ficheros de log, definidos también en la creación de la base de datos. init.ora, fichero de texto que contiene los parámetros de configuración de la base de datos. control files, ficheros de control, definidos en el init.ora password file, fichero con la password del BDA y los operadores (todos los demás usuarios están definidos en tablas).
Así para crear una base de datos, una vez instalado oracle, debemos seguir los siguientes pasos: 1) Definir ORACLE_SID ORACLE_HOME = E:\Oracle\Product\10.0.0 ORACLE_SID = GESTION
2) Crear el fichero INIT.ORA C:\>ORACLE_HOME\database\initGESTION.ora control_files = (/path/to/control1.ctl,/path/to/control2.ctl,/path/to/control3.c tl) undo_management = AUTO undo_tablespace = UNDOTBS1 db_name = GESTION db_block_size = 8192 sga_max_size = 1073741824 sga_target = 1073741824
3) Definir fichero de passwords $ORACLE_HOME\bin\orapwd file=ORACLE_HOME\database\pwdGESTION.ora password=oracle entries=10
Podemos generar los pasos 2) y 3) con una sola instrucción: oradim -new -sid GESTION -intpwd -maxusers 20 -startmode auto -pfile E:\Oracle\Product\10.0.0\Database\initGESTION.ora
4) Arrancar la instancia C:\>sqlplus / as sysdba sql> startup nomount
5) Crea la base de datos con el nombre(o SID) GESTION y el char set WE8ISO8859P1 CREATE DATABASE GESTION LOGFILE 'E:\OraData\GESTION\LOG1GESTION.ORA' SIZE 2M, 'E:\OraData\GESTION\LOG2GESTION.ORA' SIZE 2M, 'E:\OraData\GESTION\LOG3GESTION.ORA' SIZE 2M, 'E:\OraData\GESTION\LOG4GESTION.ORA' SIZE 2M, 'E:\OraData\GESTION\LOG5GESTION.ORA' SIZE 2M EXTENT MANAGEMENT LOCAL MAXDATAFILES 100 DATAFILE 'E:\OraData\GESTION\SYS1GESTION.ORA' SIZE 50 M DEFAULT TEMPORARY TABLESPACE temp TEMPFILE 'E:\OraData\GESTION\TEMP.ORA' SIZE 50 M UNDO TABLESPACE undo DATAFILE 'E:\OraData\GESTION\UNDO.ORA' SIZE 50 M NOARCHIVELOG CHARACTER SET WE8ISO8859P1;
6) Ejecutar sql de creación: catalog.sql y catproc.sql Sintaxis completa: CREATE DATABASE nombreDB opciones
Donde las opciones: DATAFILE filespec DATAFILE filespec [MAXSIZE int K | M] MAXDATAFILES int EXTENT MANAGEMENT DEFAULT TEMPORARY filespec]
AUTOEXTEND OFF AUTOEXTEND ON [NEXT int K | M] LOCAL TABLESPACE tablespace [TEMPFILE
[EXTENT MANAGEMENT LOCAL] [UNIFORM [SIZE int K | M]] UNDO TABLESPACE tablespace [DATAFILE filespec] LOGFILE [GROUP int] filespec MAXLOGFILES int MAXLOGMEMBERS int MAXLOGHISTORY int MAXINSTANCES int ARCHIVELOG | NOARCHIVELOG CONTROLFILE REUSE CHARACTER SET charset NATIONAL CHARACTER SET charset SET TIMEZONE = 'time_zone_region' SET TIMEZONE = '{+|-} hh:mm' FORCE LOGGING USER SYS IDENTIFIED BY password USER SYSTEM IDENTIFIED BY password
Se puede poner más de un DATAFILE o LOGFILE separando los nombres de fichero con comas DATAFILE filespec1, filespec2, filespec3 Si no se especifican claves, Oracle establece "change_on_install" para SYS y "manager" para SYSTEM. Después de crear la base de datos podemos cambiar entre los modos ARCHIVELOG, NOARCHIVELOG con la sentencia ALTER DATABASE.
TABLAS Cada usuario de una base de datos posee un esquema. El esquema tiene el mismo nombre que el usuario y sirve para almacenar los objetos de esquema, es decir los objetos que posee el usuario. Creación de tablas Es posible definir restricciones (constraint) con la sentencia CREATE. Nombre de las tablas: deben cumplir las siguientes reglas:
Deben comenzar con una letra. No deben tener más de 30 caracteres. Sólo se permiten utilizar letras del alfabeto (inglés), números o el signo de subrayado (también el signo $ y #, pero esos se utilizan de manera especial por lo que no son recomendados). No puede haber dos tablas con el mismo nombre para el mismo usuario (pueden coincidir los nombres si están en distintos esquemas). No puede coincidir con el nombre de una palabra reservada. Sintaxis CREATE TABLE [esquema.] nombreDeTabla (nombreDeLaColumna1 tipoDeDatos [, ...]);
Inserción Permite añadir datos a las tablas. Sintaxis INSERT INTO tabla [(columna1 [, columna2...])] [,valor2]);
VALUES (valor1
Indicando la tabla se añaden los datos que se especifiquen tras el apartado values en un nuevo registro. Los valores deben corresponderse con el orden de las columnas. Si no es así se puede indicar tras el nombre de la tabla y entre paréntesis. Borrar tablas La orden DROP TABLE seguida del nombre de una tabla, permite eliminar la tabla en cuestión. Al borrar una tabla:
Desaparecen todos los datos. Cualquier vista y sinónimo referente a la tabla seguirán existiendo, pero ya no funcionarán. Sólo es posible realizar esta operación si se es el propietario de la tabla o se posee el privilegio DROP ANY TABLE. El borrado de una tabla es irreversible, y no hay ninguna petición de confirmación, por lo que conviene ser muy cuidadoso con esta operación.
Renombrar la Tabla La orden RENAME permite el cambio de nombre de cualquier objeto. Sintaxis RENAME nombreViejo TO nombreNuevo
Modificar tablas La versátil ALTER TABLE permite hacer cambios en la estructura de una tabla. Añadir columnas Sintaxis: ALTER TABLE nombreTabla ADD (nombreColumna TipoDatos [Propiedades] [,columnaSiguiente tipoDatos [propiedades]...)
Permite añadir nuevas columnas a la tabla. Se deben indicar su tipo de datos y sus propiedades si es necesario (al estilo de CREATE TABLE). Las nuevas columnas se añaden al final, no se puede indicar otra posición. Borrar columnas Sintaxis: ALTER TABLE nombreTabla DROP(columna);
Elimina la columna indicada de manera irreversible e incluyendo los datos que contenía. No se puede eliminar la última columna (habrá que usar DROP TABLE). Modificar columna Permite cambiar el tipo de datos y propiedades de una determinada columna. Sintaxis: ALTER TABLE nombreTabla MODIFY(columna tipo [propiedades] [columnaSiguiente tipo [propiedades] ...]
Los cambios que se permiten son:
Incrementar precisión o anchura de los tipos de datos. Sólo se puede reducir la anchura si la anchura máxima de un campo si esa columna posee nulos en todos los registros, o todos los valores so o no hay registros. Se puede pasar de CHAR a VARCHAR2 y viceversa (si no se modifica la anchura). Se puede pasar de DATE a TIMESTAMP y viceversa.
Constraints Para cambiar las restricciones y la clave primaria de una tabla debemos usar ALTER TABLE. Crear una clave primaria (primary key): Ejemplo: ALTER TABLE T_PEDIDOS ADD CONSTRAINT PK_PEDIDOS PRIMARY KEY (numpedido,lineapedido);
Crear una clave externa, para integridad referencial (foreign key): ALTER TABLE T_PEDIDOS ADD CONSTRAINT FK_PEDIDOS_CLIENTES FOREIGN KEY (codcliente) REFERENCES T_CLIENTES (codcliente));
Crear un control de valores (check constraint): ALTER TABLE T_PEDIDOS ADD CONSTRAINT CK_ESTADO CHECK (estado IN (1,2,3));
Crear una restricción UNIQUE: ALTER TABLE T_PEDIDOS ADD CONSTRAINT UK_ESTADO UNIQUE (correosid);
Normalmente una restricción de este tipo se implementa mediante un índice único (ver CREATE INDEX). Borrar una restricción: Ejemplo: ALTER TABLE T_PEDIDOS DROP CONSTRAINT CON1_PEDIDOS;
Deshabilita una restricción: Ejemplo: ALTER TABLE T_PEDIDOS DISABLE CONSTRAINT CON1_PEDIDOS;
habilita una restricción: Ejemplo: ALTER TABLE T_PEDIDOS ENABLE CONSTRAINT CON1_PEDIDOS;
La sintaxis ALTER TABLE para restricciones es: ALTER TABLE [esquema.]tabla constraint_clause,... [ENABLE enable_clause | DISABLE disable_clause] [{ENABLE|DISABLE} TABLE LOCK] [{ENABLE|DISABLE} ALL TRIGGERS];
donde constraint_clause puede ser alguna de las siguientes entradas: ADD out_of_line_constraint(s) ADD out_of_line_referential_constraint DROP PRIMARY KEY [CASCADE] [{KEEP|DROP} INDEX] DROP UNIQUE (column,...) [{KEEP|DROP} INDEX] DROP CONSTRAINT constraint [CASCADE] MODIFY CONSTRAINT constraint constrnt_state MODIFY PRIMARY KEY constrnt_state MODIFY UNIQUE (column,...) constrnt_state RENAME CONSTRAINT constraint TO new_name
donde a su vez constrnt_state puede ser: [[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE|DEFERRED}] [RELY | NORELY] [USING INDEX using_index_clause] [ENABLE|DISABLE] [VALIDATE|NOVALIDATE] [EXCEPTIONS INTO [schema.]table]
Clave primaria Una clave primaria (primary key) necesita tener asociado un índice único (unique index). Es posible especificar el tablespace donde queremos crear el índice. La clave primaria de una tabla la forman las columnas que indican a cada registro de la misma. La clave primaria hace que los campos que la forman sean NOT NULL (sin posibilidad de quedar vacíos) y que los valores de los campos sean de tipo UNIQUE (sin posibilidad de repetición). Si la clave está formada por un solo campo. Sintaxis CREATE TABLE cliente( dni VARCHAR2(9) PRIMARY KEY,
nombre VARCHAR(50));
O, poniendo un nombre a la restricción: CREATE TABLE cliente(dni VARCHAR2(9) CONSTRAINT cliente_pk PRIMARY KEY, nombre VARCHAR(50)) ;
Si la clave la forman más de un campo: CREATE TABLE alquiler(dni VARCHAR2(9), cod_pelicula NUMBER(5), CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicula) ;
Clave secundaria o foránea Una clave secundaria o foránea, es uno o más campos de una tabla que están relacionados con la clave principal de los campos de otra tabla. Sintaxis CREATE TABLE alquiler(dni VARCHAR2(9), cod_pelicula NUMBER(5), CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicula), CONSTRAINT dni_fk FOREIGN KEY (dni) REFERENCES clientes(dni), CONSTRAINT pelicula_fk FOREIGN KEY (cod_pelicula) REFERENCES peliculas(cod) );
Esta completa forma de crear la tabla alquiler incluye sus claves foráneas, el campo dni hace referencia al campo dni de la tabla clientes y el campo cod_pelicula que hace referencia al campo cod de la tabla películas. También hubiera bastado con indicar sólo la tabla a la que hacemos referencia, si no se indican los campos relacionados de esa tabla, se toma su clave principal (que es lo normal). Alter table Sirve para cambiar la definición de una tabla. Podemos cambiar tanto columnas como restricciones (ver CONSTRAINTS). Sintaxis: ALTER TABLE [esquema.]tabla {ADD|MODIFY|DROP}...
Ejemplos: Añadir una columna a una tabla: ALTER TABLE T_PEDIDOS ADD TEXTOPEDIDO Varchar2(35);
Cambiar el tamaño de una columna en una tabla: ALTER TABLE T_PEDIDOS MODIFY TEXTOPEDIDO Varchar2(135);
Hacer NOT NULL una columna en una tabla: ALTER TABLE T_PEDIDOS MODIFY (TEXTOPEDIDO NOT NULL);
Eliminar una columna a una tabla: ALTER TABLE T_PEDIDOS DROP COLUMN TEXTOPEDIDO;
Valor por defecto de una columna: ALTER TABLE T_PEDIDOS MODIFY TEXTOPEDIDO Varchar2(135) DEFAULT 'ABC...';
Comentario Pone un comentario en el diccionario de datos. Sintaxis para tablas y vistas: COMMENT ON TABLE [esquema.]tabla IS 'comentario'; COMMENT ON TABLE [esquema.]vista IS 'comentario'; COMMENT ON TABLE [esquema.]vista_materilizada IS 'comentario';
Sintaxis para columnas: COMMENT ON COLUMN [esquema.]tabla.columna IS 'comentario'; COMMENT ON COLUMN [esquema.]vista.columna IS 'comentario'; COMMENT ON COLUMN [esquema.]vista_materilizada.columna IS 'comentario';
Ejemplo de tabla y columna: COMMENT ON TABLE T_PRODUCTOS IS 'Tabla de productos'; COMMENT ON COLUMN T_PRODUCTOS.numproduct IS 'Código de 6 dígitos del producto'; COMMENT ON COLUMN T_PRODUCTOS.desproduct IS 'Descripcion del producto';
Borrar comentario Para borrar un comentario hay que ponerle la cadena vacía ''. Ejemplos: COMMENT ON TABLE T_PRODUCTOS IS ''; COMMENT ON COLUMN T_PRODUCTOS.numproduct IS '';
Create Rollback Segment Crea un Rollback Segment (RBS) Sintaxis: CREATE [PUBLIC] ROLLBACK SEGMENT rbs_name options Opciones: TABLESPACE tablespace_name STORAGE storage_clause Un RBS público está disponible para más de una instancia. Global Temporary Tables Crea una tabla temporal personal para cada sesión. Eso significa que los datos no se comparten entre sesiones y se eliminan al final de la misma. Sintaxis: CREATE GLOBAL TEMPORARY TABLE tabla_temp ( columna datatype [DEFAULT expr] [column_constraint(s)] [,columna datatype [,...]] ) {ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS};
Con la opción ON COMMIT DELETE ROWS se borran los datos cada vez que se hace COMMIT en la sesión. Con la opción ON PRESERVE DELETE ROWS los datos no se borran hasta el final de la sesión.
Create Sequence Crea un objeto capaz de darnos números consecutivos únicos. CREATE SEQUENCE secuencia INCREMENT BY n START WITH n {MAX VALUE n | NOMAXVALUE} {MIN VALUE N | NOMINVALUE} {CYCLE | NOCYCLE} {CACHE N | NOCACHE} {ORDER | NOORDER};
En realidad es un generador de identificadores únicos que no bloquea transacciones. Es muy útil para generar primary keys. Si no nos gusta perder números usamos NOCACHE. Sintaxis: CREATE SEQUENCE S_PROVEEDORES MINVALUE 1 START WITH 1 INCREMENT BY 1 NOCACHE;
Si nos interesa la velocidad: CREATE SEQUENCE S_PROVEEDORES MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;
Así obtenemos el siguiente valor: SELECT S_PROVEEDORES.NEXTVAL FROM DUAL;
También podemos obtener el valor actual: SELECT S_PROVEEDORES.CURRVAL FROM DUAL;
Create Synonym Crea un sinónimo para algún objeto de la base de datos. Sintaxis: CREATE [OR REPLACE] [PUBLIC] SYNONYM [esquema.]Sinónimo FOR [esquema.]objeto [@dblink]
Con la opción 'PUBLIC' se crea un sinónimo público accesible a todos los usuarios, siempre que tengan los privilegios adecuados para el mismo. (ver GRANT) Sirve para no tener que usar la notación 'esquema.objeto' para referirse a un objeto que no es propiedad de usuario. Ejemplo: CREATE PUBLIC SYNONYM T_PEDIDOS FOR PROGRAMADOR.T_PEDIDOS;
No es necesario recompilarlos cuando se redefinen las tablas, de hecho puedes existir sin que exista el objeto al que refererencian. El acceso es un poco más eficiente cuando se accede por sinónimos públicos. Cuando en una sentencia no citamos el nombre del esquema, Oracle resuelve los nombres en el siguiente orden: usuario actual private synonym public synonym También podemos usarlo para cambiar el objeto que usamos sin tener que cambiar la programación. Ejemplo para cambiar la tabla: CREATE PUBLIC SYNONYM T_PEDIDOS FOR PROGRAMADOR.T_PEDIDOS_PRUEBA;
CONSULTAS Sintaxis SELECT * | {[DISTINCT] columna | expresión [[AS] alias], ...} FROM tabla;
Condiciones Se pueden realizar consultas que restrinjan los datos de salida de las tablas. Para ello se utiliza la cláusula WHERE. Esta cláusula permite colocar una condición que han de cumplir todos los registros, los que no la cumplan no aparecen en el resultado. Operadores de comparación Se pueden utilizar en la cláusula WHERE, son:
Se pueden utilizar tanto para comparar números como para comparar textos y fechas. En el caso de los textos, las comparaciones se hacen en orden alfabético. Sólo que es un orden alfabético estricto. Es decir el orden de los caracteres en la tabla de códigos. Valores lógicos
BETWEEN El operador BETWEEN nos permite obtener datos que se encuentren en un rango. Ejemplo: SELECT tipo, modelo, precio FROM piezas WHERE precio BETWEEN 3 AND 8;
IN Permite obtener registros cuyos valores estén en una lista: Ejemplo: SELECT tipo, modelo, precio FROM piezas WHERE precio IN (3,5, 8);
LIKE Se usa sobre todo con textos, permite obtener registros cuyo valor en un campo cumpla una condición textual. LIKE utiliza una cadena que puede contener estos símbolos:
IS NULL Devuelve verdadero si una expresión contiene un nulo: Ejemplo: SELECT nombre,apellidos FROM personas WHERE telefono IS NULL
CROSS JOIN Utilizando la opción CROSS JOIN se realiza un producto cruzado entre las tablas indicadas NATURAL JOIN Establece una relación de igualdad entre las tablas a través de los campos que tengan el mismo nombre en ambas tablas: Ejemplo: SELECT * FROM piezas NATURAL JOIN existencias;
JOIN USING Permite establecer relaciones indicando qué campo (o campos) común a las dos tablas hay que utilizar Ejemplo: SELECT * FROM piezas JOIN existencias USING(tipo,modelo);
JOIN ON Permite establecer relaciones cuya condición se establece manualmente, lo que permite realizar asociaciones más complejas o bien asociaciones cuyos campos en las tablas no tienen el mismo nombre.
Ejemplo: SELECT * FROM piezas JOIN existencias ON (piezas.tipo = existencias.tipo AND piezas.modelo = existencias.modelo);
Funciones de cรกlculo con grupos Lo interesante de la creaciรณn de grupos es las posibilidades de cรกlculo que ofrece. Para ello se utilizan funciones que permiten trabajar con los registros de un grupo son:
Condiciones HAVING A veces se desea restringir el resultado de una expresiรณn agrupada. Ejemplo: SELECT tipo, modelo, cantidad, SUM (Cantidad) FROM existencias WHERE SUM (Cantidad)>500 GROUP BY tipo, modelo;
Ejemplo Prรกctico: De la tabla Empleados:
y la tabla Clientes:
1.- Mostrar todas las órdenes: el id de la orden, el apellido y nombre del empleado que la atendió el nombre de la compañía cliente y la fecha de orden. Solución: select ORDENID, APELLIDO, NOMBRE, NOMBRECIA, FECHAORDEN from ordenes o join EMPLEADOS e on (o.EMPLEADOID = e.EMPLEADOID) join CLIENTES c on (o.CLIENTEID = c.CLIENTEID);
Más la tabla Detalle_Ordenes:
2.-Mostrar la suma total de cada tipo de producto pedidos en todas las órdenes. Solución: select * from detalle_ordenes select productoid, sum (cantidad) from detalle_ordenes group by productoid;
3.-Mostrar el número de órdenes atendidas por cada empleado, incluidos los que tienen 0 órdenes. Solución: select nombre, apellido, count (o.empleadoid) from empleados e left join ordenes o on e.empleadoid = o.empleadoid group by nombre, apellido;
4.- Muestre los proveedores y la suma de dinero vendido en los productos de ese proveedor. Solución: select prov.nombreprov, sum(d.cantidad * pro.preciounit) from proveedores prov, productos pro, detalle_ordenes d where d.productoid = pro.productoid and prov.proveedorid = pro.proveedorid group by nombreprov;
SUBCONSULTAS Se trata de una técnica que permite utilizar el resultado de una tabla SELECT en otra consulta SELECT. Permite solucionar problemas en los que el mismo dato aparece dos veces. Sintaxis SELECT listaExpresiones FROM tabla WHERE expresión_operador (SELECT listaExpresiones FROM tabla);
Se pueden utilizar instrucciones especiales entre el operador y la consulta. Esas instrucciones son:
VARIABLES DE SUSTITUCIÓN Se utilizan para poder dar parámetros a una consulta. Por ejemplo si a menudo se realiza un listado de clientes en el que queremos mostrar los datos de un cliente identificado por su DNI, entonces se puede utilizar una variable de sustitución para el DNI, de modo que cada vez que se ejecute esa consulta se pedirá el nuevo valor de la variable. Operador & La primera forma de utilizar variables de sustitución es mediante el comando &. Este símbolo utilizado en cualquier parte de la consulta, permite rellenar el contenido de una variable de sustitución. En el caso de que la variable sea de texto, hay que colocar el símbolo & dentro de las comillas que delimitan el texto. Ejemplo: SELECT * FROM Piezas WHERE modelo=&mod; Define Se pueden utilizar variables de sustitución que se definan como variables de usuario mediante la operación DEFINE. Sintaxis: DEFINE variable=valor;
La variable se sobreentiende que es de tipo texto. El valor es el contenido inicial de la variable. La variable así creada tiene vigencia durante toda la sesión de usuario. Se elimina en el cierre de la sesión o si se usa el comando UNDEFINE indicando el nombre de la variable a eliminar. Para cambiar el valor de la variable se debe utilizar otra vez el comando DEFINE. La ventaja respecto al método anterior está en que la misma variable de sustitución se puede utilizar para varios SELECT. La desventaja está en que requiere tocar el código para cambiar el valor de la variable. Listar variables El comand0 DEFINE sin nada más permite mostrar una lista de todas las variables definidas en ese momento. Operador && Se trata de una mezcla entre las anteriores. Cuando en una consulta se utiliza una variable de sustitución mediante dos símbolos ampersand, entonces al ejecutar la consulta se nos preguntará el valor. Pero luego ya no, la variable queda definida como si se hubiera declarado con DEFINE. El resto de veces que se utilice la variable, se usa con un solo &. El cambio de valor de la variable habrá que realizarle con DEFINE. Comando SET Este comando permite cambiar el valor de las variables de entorno del programa. Sintaxis: SET nombreVariable valor Las variables más interesantes a utilizar son:
LIKE Permite copia atributos de una columna a otra: Ejemplo: COLUMN precio_venta FORMAT '9G990D00L'; COLUMN precio_compra LIKE precio_venta; Las dos columnas tendrรกn el mismo formato (separador de miles, decimales y moneda tras los dos decimales) NULL Indica un texto que sustituirรก a los valores nulos. CLEAR Elimina el formato de la columna Lรณgicamente se pueden combinar varias acciones a la vez
BREAK Es uno de los comandos más poderosos. Permite realizar agrupaciones en las consultas, consiguiendo verdaderos informes (en especial si se combina con COMPUTE). Permite dividir la vista en secciones en base al valor de un campo al que se le pueden incluso quitar los duplicados. El comando: BREAK ON tipo; Hace que las columnas con alias tipo no muestren los duplicados, mostrando una vez cada valor duplicado. Para el buen funcionamiento de la orden, el resultado debe de estar ordenado por esa columna. Se pueden hacer varios grupos a la vez: BREAK ON tipo ON modelo; La orden CLEAR BREAK elimina todos los BREAK anteriormente colocados. ON REPORT Permite (en unión con COMPUTE) realizar cálculos de totales sobre el informe completo. DUPLICATES y NODUPLICATES Permiten mostrar o no los duplicados de cada sección. La opción inicial es NODUPLICATES. COMPUTE Permite en unión con BREAK realizar cálculos para las secciones de una consulta. Todo COMPUTE está asociado a un apartado ON de una instrucción BREAL previa. Sintaxis: COM[PUTE] [función [LAB[EL] texto] OF columnaOAlias ON {columnaOAlias|REPORT} función. Es el nombre de la función de cálculo que se usa (SUM, AVG, MIN, MAX, NUM (esta es como COUNT) , STD o VAR)
LABEL. Permite indicar un texto previo al resultado del cálculo, si no se utiliza Se pone el nombre de la función utilizada. OF. Indica el nombre de columna o alias utilizado en la instrucción SELECT a partir de la que se realiza el cálculo ON. Indica el nombre de columna o alias que define la sección sobre la que se realizará el cálculo. Este nombre debe haber sido indicado en un BREAK anterior. REPORT. Indica que el cálculo se calculará para toda la consulta.
DML (Data Manipulation Language) El DML (Data Manipulation Language) lo forman las instrucciones capaces de modificar los datos de las tablas. Al conjunto de instrucciones DML que se ejecutan consecutivamente, se las llama transacciones y se pueden anular todas ellas o aceptar, ya que una instrucción DML no es realmente efectuada hasta que no se acepta (commit). En todas estas consultas, el único dato devuelto por Oracle es el número de registros que se han modificado. Inserción de datos La adición de datos a una tabla se realiza mediante la instrucción INSERT. Sintaxis: INSERT INTO tabla [(listaDeCampos)] VALUES (valor1 [,valor2 ...]) La tabla representa la tabla a la que queremos añadir el registro y los valores que siguen a VALUES son los valores que damos a los distintos campos del registro. Si no se especifica la lista de campos, la lista de valores debe seguir el orden de las columnas según fueron creados (es el orden de columnas según las devuelve el comando DESCRIBE). La lista de campos a rellenar se indica si no queremos rellenar todos los campos. Los campos no rellenados explícitamente con la orden INSERT, se rellenan con su valor por defecto (DEFAULT) o bien con NULL si no se indicó valor alguno. Si algún campo tiene restricción de tipo NOT NULL, ocurrirá un error si no rellenamos el campo con algún valor relleno de registros a partir de filas de una consulta Hay un tipo de consulta, llamada de adición de datos, que permite rellenar datos de una tabla copiando el resultado de una consulta. Ese relleno se basa en una consulta SELECT que poseerá los datos a añadir. Lógicamente el orden de esos campos debe de coincidir con la lista de campos indicada en la instrucción INDEX. Sintaxis: INSERT INTO tabla (campo1, campo2,...) SELECT campoCompatibleCampo1, campoCompatibleCampo2,... FROM tabla(s) [...otras cláusulas del SELECT...] Actualización de registros La modificación de los datos de los registros lo implementa la instrucción UPDATE. Sintaxis:
UPDATE tabla SET columna1=valor1 [,columna2=valor2...] [WHERE condición]
Se modifican las columnas indicadas en el apartado SET con los valores indicados. La cláusula WHERE permite especificar qué registros serán modificados. Hay que tener en cuenta que las actualizaciones no pueden saltarse las reglas de integridad que posean las tablas.
Ejemplo Práctico: Se tiene la tabla Productos con las columnas PRODUCTOID, PROVEEDORID, CATEGORIAID, DESCRIPCION, PRECIOUNIT y EXISTENCIA como se muestra en la tabla.
Y la tabla Categoría con las columnas CATEGORIAID y NOMBRECAT:
1.- Actualizar el precio unitario de los productos de la categoría CARNICOS, subiéndolos en un 10% Solución: update productos set preciounit = preciounit * 1.10 where categoriaid = (select categoriaid from categorias where nombrecat = 'CARNICOS');
Se tienen la tabla Proveedores con las columnas PROVEEDORID, NOMBREPROV, CONTACTO, CELUPROV y FIJOPROV.
2.- Actualizar el teléfono celular del proveedor cuyo contacto es MANUEL ANDRADE, con el valor 099010291 Solución: update proveedores set celuprov = '099010291' where nombreprov = 'MANUEL ANDRADE';
Borrado de registros Se realiza mediante la instrucción DELETE: Sintaxis: DELETE [FROM] tabla [WHERE condición] Es más sencilla que el resto, elimina los registros de la tabla que cumplan la condición indicada. Hay que tener en cuenta que el borrado de un registro no puede provocar fallos de integridad y que la opción de integridad ON DELETE CASCADE (véase página 21, clave secundaria o foránea) hace que no sólo se borren los registros indicados en el SELECT, sino todos los relacionados.
Ejemplo Práctico: De la tabla Productos: 1.- Borrar el producto YOGURT DE SABORES DELETE from productos where descripcion = 'YOGURT DE SABORES';
Comando MERGE Sin duda alguna el comando más poderoso del lenguaje de manipulación de Oracle es MERGE. Este comando sirve para actualizar los valores de los registros de una tabla a partir de valores de registros de otra tabla o consulta. Permite pues combinar los datos de dos tablas a fin de actualizar la primera MERGE compara los registros de ambas tablas según la condición indicada en el apartado ON. Compara cada registro de la tabla con cada registro del SELECT. Los apartados de la sintaxis significan lo siguiente:
tabla es el nombre de la tabla que queremos modificar USING. En esa cláusula se indica una instrucción SELECT tan compleja como queramos que muestre una tabla que contenga los datos a partir de los cuales se modifica la tabla ON. permite indicar la condición que permite relacionar los registros de la tabla con los registros de la consulta SELECT WHEN MATCHED THEN. El UPDATE que sigue a esta parte se ejecuta cuando la condición indicada en el apartado ON sea cierta para los dos registros actuales. WHEN NOT MATCHED THEN. El INSERT que sigue a esta parte se ejecuta para cada registro de la consulta SELECT que no pudo ser relacionado con ningún registro de la tabla.
Transacciones Como se ha comentado anteriormente, una transacción está formada por una serie de instrucciones DML. Una transacción comienza con la primera instrucción DML que se ejecute y finaliza con alguna de estas circunstancias: Una operación COMMIT o ROLLBACK Una instrucción DDL (como ALTER TABLE por ejemplo) Una instrucción DCL (como GRANT)
El usuario abandona la sesión Caída del sistema Hay que tener en cuenta que cualquier instrucción DDL o DCL da lugar a un COMMIT implícito, es decir todas las instrucciones DML ejecutadas hasta ese instante pasan a ser definitivas. COMMIT La instrucción COMMIT hace que los cambios realizados por la transacción sean definitivos, irrevocables. Sólo se debe utilizar si estamos de acuerdo con los cambios, conviene asegurarse mucho antes de realizar el COMMIT ya que las instrucciones ejecutadas pueden afectar a miles de registros. Además el cierre correcto de la sesión da lugar a un COMMIT, aunque siempre conviene ejecutar explícitamente esta instrucción a fin de asegurarnos de lo que hacemos. ROLLBACK Esta instrucción regresa a la instrucción anterior al inicio de la transacción, normalmente el último COMMIT, la última instrucción DDL o DCL o al inicio de sesión. Anula definitivamente los cambios, por lo que conviene también asegurarse de esta operación. Un abandono de sesión incorrecto o un problema de comunicación o de caída del sistema dan lugar a un ROLLBACK implícito. SAVEPOINT Esta instrucción permite establecer un punto de ruptura. El problema de la combinación ROLLBACK/COMMIT es que un COMMIT acepta todo y un ROLLBACK anula todo. SAVEPOINT permite señalar un punto intermedio entre el inicio de la transacción y la situación actual. Sintaxis es: ...instrucciones DML... SAVEPOINT nombre ....instrucciones DML... Para regresar a un punto de ruptura concreto se utiliza ROLLBACK TO SAVEPOINT seguido del nombre dado al punto de ruptura. Cuando se vuelve a un punto marcado, las instrucciones que siguieron a esa marca se anulan definitivamente estado de los datos durante la transacción. Si se inicia una transacción usando comandos DML hay que tener en cuenta que:
Se puede volver a la instrucción anterior a la transacción cuando se desee Las instrucciones de consulta SELECT realizadas por el usuario que inició la transacción muestran los datos ya modificados por las instrucciones DML El resto de usuarios ven los datos tal cual estaban antes de la transacción, de hecho los registros afectados por la transacción aparecen bloqueados hasta que la transacción finalice. Esos usuarios no podrán modificar los valores de dichos registros.
Tras la transacción todos los usuarios ven los datos tal cual quedan tras el fin de transacción. Los bloqueos son liberados y los puntos de ruptura borrados.
Objetos de la base de datos Vistas Una vista no es más que una consulta almacenada a fin de utilizarla tantas veces como se desee. Una vista no contiene datos sino la instrucción SELECT necesaria para crear la vista, eso asegura que los datos sean coherentes al utilizar los datos almacenados en las tablas. Las vistas se emplean para:
Realizar consultas complejas más fácilmente Proporcionar tablas con datos completos Utilizar visiones especiales de los datos
Hay dos tipos de vistas:
Simples. Las forman una sola tabla y no contienen funciones de agrupación. Su ventaja es que permiten siempre realizar operaciones DML sobre ellas. Complejas. Obtienen datos de varias tablas, pueden utilizar funciones de agrupación. No siempre permiten operaciones DML.
Sintaxis: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW vista [(alias[, alias2...]] AS consultaSELECT [WITH CHECK OPTION [CONSTRAINT restricción]] [WITH READ ONLY [CONSTRAINT restricción]] o
OR REPLACE. Si la vista ya existía, la cambia por la actual
o
FORCE. Crea la vista aunque los datos de la consulta SELECT no existan
o
vista. Nombre que se le da a la vista
o
alias. Lista de alias que se establecen para las columnas devueltas por la consulta SELECT en la que se basa esta vista. El número de alias debe coincidir con el número de columnas devueltas por SELECT.
o
WITH CHECK OPTION. Hace que sólo las filas que se muestran en la vista puedan ser añadidas (INSERT) o modificadas (IPDATE). La restricción que sigue a esta sección es el nombre que se le da a esta restricción de tipo CHECK OPTION.
o
WITH READ ONLY. Hace que la vista sea de sólo lectura. Permite grabar un nombre para esta restricción.
La creación de la vista del ejemplo es compleja ya que hay relaciones complicadas, pero una vez creada la vista, se le pueden hacer consultas como si se tratara de una tabla normal.
Incluso se puede utilizar el comando DESCRIBE sobre la vista para mostrar la estructura de los campos que forman la vista.
Ejecución De Comandos DML Sobre Vistas Las instrucciones DML ejecutadas sobre las vistas permiten añadir o modificar los datos de las tablas relacionados con las filas de la vista. Ahora bien, no es posible ejecutar instrucciones DML sobre vistas que:
Utilicen funciones de grupo (SUM, AVG,...) Usen GROUP BY o DISTINCT Posean columnas con cálculos (PRECIO * 1.16)
Además no se pueden añadir datos a una vista si en las tablas referencias en la consulta SELECT hay campos NOT NULL que no aparecen en la consulta (es lógico ya que al añadir el dato se tendría que añadir el registro colocando el valor NULL en el campo). Mostrar La Lista De Vistas La vista del diccionario de datos USER_VIEWS permite mostrar una lista de todas las vistas que posee el usuario actual. Es decir, para saber qué vistas hay disponibles se usa: Sintaxis: SELECT * FROM USER_VIEWS; La columna TEXT de esa vista contiene la sentencia SQL que se utilizó para crear la vista (sentencia que es ejecutada cada vez que se invoca a la vista). Borrar Vistas Se utiliza el comando DROP VIEW: Sintaxis: DROP VIEW nombreDeVista; Secuencias Una secuencia sirve para generar automáticamente números distintos. Se utilizan para generar valores para campos que se utilizan como clave forzada (claves cuyo valor no interesa, sólo sirven para identificar los registros de una tabla). Es una rutina interna de Oracle la que realiza la función de generar un número distinto cada vez. Las secuencias se almacenan independientemente de la tabla, por lo que la misma secuencia se puede utilizar para diversas tablas. Creación de secuencias Sintaxis: CREATE SEQUENCE secuencia [INCREMENT BY n]
[START WITH n] [{MAXVALUE n|NOMAXVALUE}] [{MINVALUE n|NOMINVALUE}] [{CYCLE|NOCYCLE}] Donde: secuencia. Es el nombre que se le da al objeto de secuencia INCREMENT BY. Indica cuánto se incrementa la secuencia cada vez que se usa. Por defecto se incrementa de uno en uno START WITH. Indica el valor inicial de la secuencia (por defecto 1) MAXVALUE. Máximo valor que puede tomar la secuencia. Si no se toma NOMAXVALUE que permite llegar hasta el 1027 MINVALUE. Mínimo valor que puede tomar la secuencia. Por defecto -1026 CYCLE. Hace que la secuencia vuelva a empezar si se ha llegado al máximo valor. Ver Lista De Secuencias La vista del diccionario de datos USER_SEQUENCES muestra la lista de secuencias actuales. La columna LAST_NUMBER muestra cual será el siguiente número de secuencia disponible uso de la secuencia. Los métodos NEXTVAL y CURRVAL se utilizan para obtener el siguiente número y el valor actual de la secuencia respectivamente. Realmente NEXTVAL incrementa la secuencia y devuelve el valor actual. CURRVAL devuelve el valor de la secuencia, pero sin incrementar la misma. Ambas funciones pueden ser utilizadas en: Una consulta SELECT que no lleve DISTINCT, ni grupos, ni sea parte de una vista, ni sea subconsulta de otro SELECT, UPDATE o DELETE Una subconsulta SELECT en una instrucción INSERT La cláusula VALUES de la instrucción INSERT La cláusula SET de la instrucción UPDATE No se puede utilizar (y siempre hay tentaciones para ello) como valor para la cláusula DEFAULT de un campo de tabla. Su uso más habitual es como apoyo al comando INSERT: Ejemplo: INSERT INTO plantas(num, uso) VALUES(numeroPlanta.NEXTVAL, 'Suites'); Modificar Secuencias Se pueden modificar las secuencias, pero la modificación sólo puede afectar a los futuros valores de la secuencia, no a los ya utilizados. Sintaxis: ALTER SEQUENCE secuencia [INCREMENT BY n]
[START WITH n] [{MAXVALUE n|NOMAXVALUE}] [{MINVALUE n|NOMINVALUE}] [{CYCLE|NOCYCLE}] Borrar Secuencias Lo hace el comando DROP SEQUENCE seguido del nombre de la secuencia a borrar. Índices Los índices son esquemas que hacen que Oracle acelere las operaciones de consulta y ordenación sobre los campos a los que el índice hace referencia. Se almacenan aparte de la tabla a la que hace referencia, lo que permite crearles y borrarles en cualquier momento. Lo que realizan es una lista ordenada por la que Oracle puede acceder para facilitar la búsqueda de los datos. Cada vez que se añade un nuevo registro, los índices involucrados se actualizan a fin de que su información esté al día. De ahí que cuantos más índices haya, más le cuesta a Oracle añadir registros, pero más rápidas se realizan las instrucciones de consulta. La mayoría de los índices se crean de manera implícita, como consecuencia de las restricciones PRIMARY KEY (que obliga a crear un índice único sobre los campos clave) , UNIQUE (crea también un índice único) y FOREIGN KEY (crea un índice con posibilidad de repetir valores, índice con duplicados). Estos son índices obligatorios, por los que les crea el propio Oracle. Creación De Índices Aparte de los índices obligatorios comentados anteriormente, se pueden crear índices de forma explícita. Éstos se crean para aquellos campos sobre los cuales se realizarán búsquedas e instrucciones de ordenación frecuente. Sintaxis: CREATE INDEX nombre ON tabla (columna1 [,columna2...]) Ejemplo: CREATE INDEX nombre_completo ON clientes (apellido1, apellido2, nombre); El ejemplo crea un índice para los campos apellido1, apellido2 y nombre. Esto no es lo mismo que crear un índice para cada campo, este índice es efectivo cuando se buscan u ordenan clientes usando los tres campos (apellido1, apellido2, nombre) a la vez. Se aconseja crear índices en campos que: Contengan una gran cantidad de valores Contengan una gran cantidad de nulos Son parte habitual de cláusulas WHERE, GROUP BY u ORDER BY Son parte de listados de consultas de grandes tablas sobre las que casi siempre se muestran como mucho un 4% de su contenido. No se aconseja en campos que: Pertenezcan a tablas pequeñas
No se usan a menudo en las consultas Pertenecen a tablas cuyas consultas muestran más de un 6% del total de registros Pertenecen a tablas que se actualizan frecuentemente Se utilizan en expresiones Los índices se pueden crear utilizando expresiones complejas: Ejemplo: CREATE INDEX nombre_complejo ON clientes (UPPER(nombre)); Esos índices tienen sentido si en las consultas se utilizan exactamente esas expresiones. En oracle existen tres tipos de índices: 1) Table Index 2) Bitmap Join Index 3) Cluster Index 1) Table Index Sintaxis: CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name ON [esquema.]table_name [tbl_alias] (col [ASC | DESC]) index_clause index_attribs
2) Bitmap Join Index: Sintaxis: CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name ON [esquema.]table_name [tbl_alias] (col_expression [ASC | DESC]) FROM [esquema.]table_name [tbl_alias] WHERE condition [index_clause] index_attribs
3) Cluster Index: Sintaxis: CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name ON CLUSTER [esquema.]cluster_name index_attribs
Lista De Índices Para ver la lista de índices se utiliza la vista USER_INDEXES . Mientras que la vista USER_IND_COLUMNS Muestra la lista de columnas que son utilizadas por índices. Borrar Índices La instrucción DROP INDEX seguida del nombre del índice permite eliminar el índice en cuestión.
Fuentes de Información:
Greenwald, Rick and Stackowiak, Robert & Stern, Jonathan. Oracle Essentials. Oracle Database 11g. 4th Edition O´REILLY.
ORACLE. Arquitectura Oracle Database 11g en Windows. Informe Ejecutivo de Oracle Junio de 2007.
R.Alapati, Sam and Kim, Charles. Oracle Database 11g. New Features for DBAs and Developers. Apress.
http://ora.u440.com/ddl/
http://ocw.uoc.edu/informatica-tecnologia-y-multimedia/bases-dedatos/skinless_view
ANEXOS: /*CREACION DE LA BASE DE DATOS PEDIDOS*/ --CREAR UN USUARIO create user pedidos identified by contrasena default tablespace users temporary tablespace temp profile default; --CONECTARSE A LA BASE grant connect, resource to pedidos; connect pedidos/contrasena; --CREACION DE TABLAS CREATE TABLE EMPLEADOS( EMPLEADOID int NOT NULL, NOMBRE char(30) NULL, APELLIDO char(30) NULL, FECHA_NAC date NULL, REPORTA_A int NULL, EXTENSION int NULL, CONSTRAINT PK_EMPLEADOS PRIMARY KEY (EMPLEADOID), CONSTRAINT FK_EMPLEADO_REPORTA FOREIGN KEY (REPORTA_A) EMPLEADOS(EMPLEADOID) ); CREATE TABLE PROVEEDORES( PROVEEDORID int NOT NULL, NOMBREPROV char(50) NOT NULL, CONTACTO char(50) NOT NULL, CELUPROV char(12) NULL, FIJOPROV char(12) NULL, CONSTRAINT PK_PROVEEDORES PRIMARY KEY (PROVEEDORID ) ); CREATE TABLE CATEGORIAS( CATEGORIAID int NOT NULL, NOMBRECAT char(50) NOT NULL, CONSTRAINT PK_CATEGORIAS PRIMARY KEY (CATEGORIAID) ); CREATE TABLE CLIENTES( CLIENTEID int NOT NULL, CEDULA_RUC char(10) NOT NULL, NOMBRECIA char(30) NOT NULL, NOMBRECONTACTO char(50) NOT NULL, DIRECCIONCLI char(50) NOT NULL, FAX char(12) NULL, EMAIL char(50) NULL, CELULAR char(12) NULL, FIJO char(12) NULL, CONSTRAINT PK_CLIENTES PRIMARY KEY(CLIENTEID) );
REFERENCES
CREATE TABLE ORDENES( ORDENID int NOT NULL, EMPLEADOID int NOT NULL, CLIENTEID int NOT NULL, FECHAORDEN date NOT NULL, DESCUENTO int NULL, CONSTRAINT PK_ORDENES PRIMARY KEY(ORDENID), CONSTRAINT FK_ORDENES_CLIEN_ORD_CLIENTES FOREIGN REFERENCES CLIENTES(CLIENTEID), CONSTRAINT FK_ORDENES_EMPLE_ORD_EMPLEADO FOREIGN REFERENCES EMPLEADOS(EMPLEADOID) ); CREATE TABLE PRODUCTOS( PRODUCTOID int NOT NULL, PROVEEDORID int NOT NULL, CATEGORIAID int NOT NULL, DESCRIPCION char(50) NULL, PRECIOUNIT number NOT NULL, EXISTENCIA int NOT NULL, CONSTRAINT PK_PRODUCTOS PRIMARY KEY(PRODUCTOID), CONSTRAINT FK_PRODUCTO_CATE_PROD_CATEGORI FOREIGN REFERENCES CATEGORIAS(CATEGORIAID), CONSTRAINT FK_PRODUCTO_PROV_PROD_PROVEEDO FOREIGN REFERENCES PROVEEDORES(PROVEEDORID) );
KEY KEY
(CLIENTEID) (EMPLEADOID)
KEY
(CATEGORIAID)
KEY
(PROVEEDORID)
CREATE TABLE DETALLE_ORDENES( ORDENID int NOT NULL, DETALLEID int NOT NULL, PRODUCTOID int NOT NULL, CANTIDAD int NOT NULL, CONSTRAINT PK_DETALLE_ORDENES PRIMARY KEY (ORDENID,DETALLEID ), CONSTRAINT FK_DETALLE__ORDEN_DET_ORDENES FOREIGN KEY (ORDENID) REFERENCES ORDENES(ORDENID), CONSTRAINT FK_DETALLE__PROD_DETA_PRODUCTO FOREIGN KEY (PRODUCTOID) REFERENCES PRODUCTOS(PRODUCTOID) ); --INSERCION DE DATOS begin insert into categorias (categoriaid, nombrecat) values (100, 'CARNICOS'); insert into categorias (categoriaid, nombrecat) values (200, 'LACTEOS'); insert into categorias (categoriaid, nombrecat) values (300, 'LIMPIEZA'); insert into categorias (categoriaid, nombrecat) values (400, 'HIGINE PERSONAL'); insert into categorias (categoriaid, nombrecat) values (500, 'MEDICINAS'); insert into categorias (categoriaid, nombrecat) values (600, 'COSMETICOS'); insert into categorias (categoriaid, nombrecat) values (700, 'REVISTAS'); commit; end; /
begin insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (10, 'DON DIEGO', 'MANUEL ANDRADE', '099234567','2124456'); insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (20, 'PRONACA', 'JUAN PEREZ', '0923434467','2124456'); insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (30, 'TONY', 'JORGE BRITO', '099234567','2124456'); insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (40, 'MIRAFLORES', 'MARIA PAZ', '098124498','2458799'); insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (50, 'ALMAY', 'PEDRO GONZALEZ', '097654567','2507190'); insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (60, 'REVLON', 'MONICA SALAS', '099245678','2609876'); insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (70, 'YANBAL', 'BETY ARIAS', '098124458','2450887'); insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (120, 'JURIS', 'MANUEL ANDRADE', '099234567','2124456'); insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (80, 'CLEANER', 'MANUEL ANDRADE', '099234567','2124456'); insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (90, 'BAYER', 'MANUEL ANDRADE', '099234567','2124456'); insert into proveedores (proveedorid, contacto,celuprov,fijoprov) values (110, 'PALMOLIVE', 'MANUEL ANDRADE', '099234567','2124456'); commit; end; /
nombreprov, nombreprov, nombreprov, nombreprov, nombreprov, nombreprov, nombreprov, nombreprov, nombreprov, nombreprov, nombreprov,
begin INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT
commit; end; /
INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO
PRODUCTOS PRODUCTOS PRODUCTOS PRODUCTOS PRODUCTOS PRODUCTOS PRODUCTOS PRODUCTOS PRODUCTOS PRODUCTOS PRODUCTOS PRODUCTOS
VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES
(1,10,100,'SALCHICHAS VIENESAS',2.60,200); (2,10,100,'SALAMI DE AJO',3.60,300); (3,10,100,'BOTON PARA ASADO',4.70,400); (4,20,100,'SALCHICHAS DE POLLO',2.90,200); (5,20,100,'JAMON DE POLLO',2.80,100); (6,30,200,'YOGURT NATURAL',4.30,80); (7,30,200,'LECHE CHOCOLATE',1.60,90); (8,40,200,'YOGURT DE SABORES',1.60,200); (9,40,200,'CREMA DE LECHE',3.60,30); (10,50,600,'BASE DE MAQUILLAJE',14.70,40); (11,50,600,'RIMMEL',12.90,20); (13,60,600,'SOMBRA DE OJOS',9.80,100);
begin INSERT INSERT INSERT INSERT INSERT INSERT INSERT
INTO INTO INTO INTO INTO INTO INTO
EMPLEADOS EMPLEADOS EMPLEADOS EMPLEADOS EMPLEADOS EMPLEADOS EMPLEADOS
VALUES VALUES VALUES VALUES VALUES VALUES VALUES
(1,'JUAN', 'CRUZ', '18/01/67',null, 231); (2,'MARIO', 'SANCHEZ', '01/03/79',1,144); (3,'VERONICA', 'ARIAS', '23/06/77',1, 234); (4,'PABLO', 'CELY', '28/01/77',2, 567); (5,'DIEGO', 'ANDRADE', '15/05/70',2, 890); (6,'JUAN', 'ANDRADE', '17/11/76',3, 230); (7,'MARIA', 'NOBOA', '21/12/79',3, 261);
commit; end; / begin INSERT INTO CLIENTES VALUES (1,'1890786576','SUPERMERCADO ESTRELLA','JUAN ALBAN','AV.AMAZONAS',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (2,'1298765477','EL ROSADO','MARIA CORDERO','AV.AEL INCA',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (3,'1009876567','DISTRIBUIDORA PRENSA','PEDRO PINTO','EL PINAR',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (4,'1876090006','SU TIENDA','PABLO PONCE','AV.AMAZONAS',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (5,'1893456776','SUPERMERCADO DORADO','LORENA PAZ','AV.6 DICIEMBRE',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (6,'1678999891','MI COMISARIATO','ROSARIO UTRERAS','AV.AMAZONAS',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (7,'1244567888','SUPERMERCADO DESCUENTO','LETICIA ORTEGA','AV.LA PRENSA',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (8,'1456799022','EL DESCUENTO','JUAN TORRES','AV.PATRIA',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (9,'1845677777','DE LUISE','JORGE PARRA','AV.AMAZONAS',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (10,'183445667','YARBANTRELLA','PABLO POLIT','AV.REPUBLICA',NULL,NULL,NULL,NULL); commit; end; / begin INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT commit; end; /
INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO
ORDENES ORDENES ORDENES ORDENES ORDENES ORDENES ORDENES ORDENES ORDENES ORDENES
VALUES(1,3,4,'17/06/07', 5); VALUES(2,3,4,'02/06/07', 10); VALUES(3,4,5,'05/06/07', 6); VALUES(4,2,6,'06/06/07', 2); VALUES(5,2,7,'09/06/07', NULL); VALUES(6,4,5,'12/06/07', 10); VALUES(7,2,5,'14/06/07', 10); VALUES(8,3,2,'13/06/07', 10); VALUES(9,3,2,'17/06/07', 3); VALUES(10,2,2,'18/06/07', 2);
begin insert insert insert insert
into into into into
detalle_ordenes detalle_ordenes detalle_ordenes detalle_ordenes
values(1,1,1,2); values(1,2,4,1); values(1,3,6,1); values(1,4,9,1);
detalle_ordenes detalle_ordenes detalle_ordenes detalle_ordenes
values(2,1,10,10); values(2,2,13,20); values(3,1,3,10); values(4,1,9,12);
detalle_ordenes detalle_ordenes detalle_ordenes detalle_ordenes
values(5,1,1,14); values(5,2,4,20); values(6,1,3,12); values(7,1,11,10);
detalle_ordenes detalle_ordenes detalle_ordenes detalle_ordenes
values(8,1,2,10); values(8,2,5,14); values(8,3,7,10); values(9,1,11,10);
commit; end; / begin insert insert insert insert
into into into into
commit; end; / begin insert insert insert insert
into into into into
commit; end; / begin insert insert insert insert
into into into into
insert into detalle_ordenes values(10,1,1,5); commit; end; / /*CONSULTAS EN ORACLE SOBRE LA BASE PEDIDOS*/ --1. Actualizar el precio unitario de los productos de la categorĂa CARNICOS, subiĂŠndolos en un 10% update productos set preciounit=preciounit*1.10 where categoriaid=(select categoriaid from categorias where nombrecat ='CARNICOS'); --2. Actualizar el telĂŠfono celular del proveedor cuyo contacto es MANUEL ANDRADE, con el valor 099010291 update proveedores set celuprov = '099010291' where nombreprov ='MANUEL ANDRADE';
--3.
Borrar el producto YOGURT DE SABORES DELETE from productos where descripcion = 'YOGURT DE SABORES';
--4.
Realizar las siguientes consultas:
/* 4.1 Mostrar todas las órdenes: el id de la orden, el apellido y nombre del empleado que la atendió el nombre de la compañía cliente y la fecha de orden */ select * from ORDENES select * from EMPLEADOS select * from CLIENTES select ORDENID, APELLIDO, NOMBRE, NOMBRECIA, FECHAORDEN from ordenes o join EMPLEADOS e on (o.EMPLEADOID=e.EMPLEADOID) join CLIENTES c on (o.CLIENTEID= c.CLIENTEID); --4.2 Mostrar la suma total de cada tipo de producto pedidos en todas las órdenes. select * from detalle_ordenes select productoid, sum (cantidad) from detalle_ordenes group by productoid ;
--4.3 Mostrar el número de órdenes incluidos los que tienen 0 órdenes.
atendidas
por
cada
empleado,
select nombre, apellido, count (o.empleadoid) from empleados e left join ordenes o on e.empleadoid= o.empleadoid group by nombre, apellido; --4.4 Muestre los proveedores productos de ese proveedor.
y
la
suma
de
dinero
vendido
en
los
--4.4 Muestre los proveedores productos de ese proveedor.
y
la
suma
de
dinero
vendido
en
los
select prov.nombreprov,sum(d.cantidad * pro.preciounit ) from proveedores prov, productos pro, detalle_ordenes d where d.productoid= pro.productoid and prov.proveedorid= pro.proveedorid group by nombreprov;
--5. Realizar el siguiente procedimiento almacenado. --5.1 Escriba un procedimiento almacenado que reciba como parámetro un código de proveedor y -devuelve el número de órdenes en las que están incluidos productos de ese proveedor. create or replace procedure num_ordenes(idproo int) returns integer is declare num int; begin select count(p.productoid) into num from productos p join detalle_ordenes dor on(p.productoid=dor.productoid)join ordenes ord on(ord.ordenid=dor.ordenid) where proveedorid=idproo group by proveedorid; return num; end; / declare num int execute ordenes_proveedor(1, num in out) print 'numero de ordenes: ' + convert(char(20), num);
--5.2 Escriba un procedimiento almacenado que reciba como parámetro un --nombre de una categoría y devuelve el código del producto de esa categoría que tiene más unidades vendidas. select * from categorias select * from productos select * from detalle_ordenes create or replace function mas_vendido (nomCat character(50)) returns integer is declare num int; begin select p.productoid,sum(cantidad)INTO NUM from productos p join categorias c on (c.categoriaid=p.categoriaid) join detalle_ordenes dor on (p.productoid=dor.productoid) where c.nombrecat=nomCat group by descripcion, p.productoid order by sum DESC limit 1; return num; end; / select mas_vendido('CARNICOS');