7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 1/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 2/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 3/226
ADMINISTRACIÓN DE BASES DEDATOS CON ORACLE 9I
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 4/226
ADMINISTRACIÓN DE BASES DE DATOS CON ORACLE 9I.
Autor: Formación Digital, S.L.Edita: Formación Digital, S.L.
"Queda rigurosamente prohibida, sin la autorización escrita del autor, la reproducción parcial o total deesta obra por cualquier medio o procedimiento, comprendidos la reprografía y el tratamientoinformático, y la distribución de ejemplares de ella mediante alquiler o préstamos públicos".
Depósito legal: SE-5484-08
© 2008, Formación Digital, S.L.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 5/226
1. INSTALACIÓN ORACLE 9i .................................................................12. ARQUITECTURA DE ORACLE 9I .......................................................13
3. CREACIÓN DE TABLESPACES ..........................................................31
4. USUARIOS......................................................................................43
5. TABLAS ..........................................................................................61
6. OPERACIONES SOBRE TABLAS........................................................91
7. CONSULTAS..................................................................................123
8. PL/SQL ........................................................................................143
9. FUNCIONES, PROCEDIMIENTOS, PAQUETES Y DISPARADORES.....165
10. CURSORES.................................................................................18711. ÍNDICES ....................................................................................199
GLOSARIO .........................................................................................217
í n
d i c e g e n e r a l
©
f o r m a c i ó n d i g i t a l
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 6/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 7/226
1
Instalación de
Oracle 9i 1
1.1. VENTANA DE “UBICACIÓN DE LOS ARCHIVOS” ........................... 3
1.2. VENTANA DE “PRODUCTOS DISPONIBLES” .................................. 4
1.3. VENTANA DE “TIPOS DE INSTALACIÓN” ...................................... 5
1.4. VENTANA DE “CONFIGURACIÓN DE BASE DE DATOS” .................. 6
1.5. VENTANA DE “ORACLE SERVICES PARA MICROSOFT6
TRANSACTION SERVER” .............................................................. 6
1.6. VENTANA DE “IDENTIFICACIÓN DE BASES DE DATOS” .............. 7
1.7. VENTANA DE “UBICACIÓN DE ARCHIVOS DE B.D.”....................... 81.8. VENTANA DE “JUEGO DE CARACTERES DE LA B.D.” ...................... 9
1.9. VENTANA “RESUMEN”.................................................................10
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 8/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 9/226
3
Instalación de
Oracle 9i 1
1.1. VENTANA DE “UBICACIÓN DE LOS ARCHIVOS”
En dicha ventana se detalla la ruta de origen del software de instalación, que en este proceso
de instalación es: “C:\Instalacion 9i\stage\products.jar1”
Además se especifican dos datos muy importantes como los que se detallan seguidamente:
−
Nombre. Directorio de instalación de los programas necesarios para el funcionamiento
de la propia aplicación Oracle (se aconseja no cambiar este directorio). Para la versión
Oracle 9i es “OraHome92”, para Oracle 8i es “OraHome8i”, etc.
−
Ruta de Acceso. En esta opción se detalla la ruta de instalación. Se recomienda tenermuy claro dónde se ubicarán los programas y aplicaciones que necesitará Oracle para
poder funcionar. En nuestro caso: “C:\oracle\ora92”. Este directorio es conocido como
ORACLE_HOME.
1 Ficheros con extensión JAR son paquetes de JAVA.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 10/226
4
Instalación de
Oracle 9i 1
Una vez pasada esta ventana, podemos observar las carpetas creadas en el disco duro:
1.2. VENTANA DE “PRODUCTOS DISPONIBLES”
En esta ventana se nos permite seleccionar tres posibles opciones:
La primera es aquella que nos instala las herramientas de administración además de
crearnos una Base de Datos. Se recomienda para la primera vez que se instale Oracle 9i en
un Servidor de B.D.2
2 B.D. (Base de Datos)
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 11/226
5
Instalación de
Oracle 9i 1
La segunda opción instala las herramientas necesarias para administrar, pero sin crear una
B.D.
Finalmente, la tercera opción sólo instala los programas necesarios para poder acceder a una
B.D. Oracle desde un ordenador o puesto cliente.
1.3. VENTANA DE “TIPOS DE INSTALACIÓN”
En ella, al igual que otros productos o programas informáticos, se selecciona qué clase de
instalación se desea realizar, además de especificarnos el tamaño que se requiere de disco
duro para las distintas opciones. Diferenciamos entre:
− Enterprise Edición (2.86 GB)
− Edición Estándar (2.80 GB)
− Personal Edición (2.80 GB)
− Personalizado
Al igual que en otros paquetes informáticos, la instalación más completa es la Enterprise
Edición.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 12/226
6
Instalación de
Oracle 9i 1
1.4. VENTANA DE “CONFIGURACIÓN DE BASE DE DATOS”
Esta ventana nos permite especificar el tipo de base de datos que se desea crear.
Las B.D. del tipo “Procesamiento de Transacciones”, son aquellas que reciben un gran
número de transacciones “al segundo” por parte de un volumen elevado de usuarios. Ejm.-
Entidades Bancarias.
Las B.D. del tipo “Almacenes de Datos”, son las B.D. conocidas con “Data WareHouse”,
destinadas a la toma de decisiones sobre grandes volúmenes de datos e información a
procesar e interpretar, sin ningún nivel de transacciones.
1.5. VENTANA DE “ORACLE SERVICES PARA MICROSOFT TRANSACTION SERVER”
En ella se especifica el número del puerto por el cuál Oracle escuchará las peticiones de
transacciones realizadas desde la aplicación Microsoft Transaction Server (MTS). En este
caso el 2030, el cuál se aconseja no cambiar salvo que este número de puerto se esté
usando por otro programa o aplicación.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 13/226
7
Instalación de
Oracle 9i 1
1.6. VENTANA DE “IDENTIFICACIÓN DE BASES DE DATOS”
Es una de las más importantes por no decir la más importante. En ella se especifica el
nombre de la B.D. que creará Oracle de forma genérica, además del SID (Oracle System
IDentifier) que es un identificador único para cada B.D. que se cree. Dicho SID podemos
usarlo para “instanciar” a la B.D. creada.
Por defecto le pondremos el nombre “GLOBAL” a la B.D. y a su vez el propio programa de
instalación asignará como SID el nombre de “GLOBAL”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 14/226
8
Instalación de
Oracle 9i 1
1.7. VENTANA DE “UBICACIÓN DE ARCHIVOS DE B.D.”
En ella se especifica la ruta física dónde Oracle creará los ficheros necesarios para el
funcionamiento, control y gestión de los “DATOS” de la B.D. que se va a crear durante el
proceso de instalación.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 15/226
9
Instalación de
Oracle 9i 1
Por cuestiones de rendimiento, Oracle aconseja que la ruta especificada en la ventana
anterior, esté en un disco duro distinto al que se ha usado para ubicar el software de
instalación, es decir, distinto a la ruta indicada en la ventana de “Ubicación de los Archivos”
(C:\oracle\ora9).
1.8. VENTANA DE “JUEGO DE CARACTERES DE LA B.D.”
En ella elegimos el juego de caracteres que usará la base de datos. Éste es seleccionado por
el propio programa de instalación basándose en el sistema operativo que posea el ordenador
dónde se va a instalar Oracle.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 16/226
10
Instalación de
Oracle 9i 1
1.9. VENTANA “RESUMEN”
Muestra información de las opciones seleccionadas durante las distintas ventanas del proceso
de instalación.
Durante el proceso de instalación, se crea la B.D. mediante el “Asistente”. Una vez finalizada
dicha creación, Oracle nos solicita las password para los usuarios SYS y SYSTEM por motivos
de seguridad. En otras versiones 8i y posteriores, esta opción no existía, siendo las
password:
SYS / change_on_install
SYSTEM / manager
Dicha ventana, además de solicitarnos dichos cambios, nos muestra información de la B.D.
creada, qué fichero posee los parámetros de iniciación de dicha B.D. y dónde está ubicado
físicamente.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 17/226
11
Instalación de
Oracle 9i 1
El proceso de instalación se dará por finalizado cuando se presente la ventana siguiente:
Si observamos la estructura de carpetas que tendremos en el disco duro será la siguiente:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 18/226
12
Instalación de
Oracle 9i 1
− A la hora de instalar Oracle hay que tener muy claro dónde se instalará, es
decir, el ORACLE_HOME.
− También conviene determinar previamente el tipo de instalación y tipo de
B.D. que se desea crear en el proceso de instalación.
− Dos de los datos más importantes a considerar son el nombre de la Base de
Datos y el SID.
− Una vez finalizada la creación de la B.D., Oracle nos solicitará las password
para los usuarios SYS y SYSTEM por motivos de seguridad.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 19/226
13
Arquitectura
de Oracle 9i 2
2.1. ESTRUCTURA DE ALMACENAMIENTO LOGICA Y FISICA ................15
2.2. RELACIÓN TABLESPACES Y DATAFILES........................................15
2.3. SEGMENTOS, EXTENSIONES Y BLOQUES DE DATOS......................172.4. ESPACIO DE TABLAS SYSTEM (Tablespace SYSTEM) ....................19
2.5. DIFERENTES OPCIONES DE UN TABLESPACE................................21
2.5.1. Tablespaces Permanentes (PERMANENT), Temporales
(TEMPORARY) y Deshacer (UNDO) ...................................21
2.5.2. Tablespaces en línea (ONLINE) o fuera de línea
(OFFLINE) ........................................................................22
2.5.3. Espacios de Tablas (Tablespaces) administrado
Localmente (Local) y por Diccionario (Dictionary) ............23
2.5.4. Tablespaces en modalidad de sólo lectura ylectura/escritura ..............................................................24
2.6. FICHEROS DE DATOS (DATAFILES)..............................................24
2.7. OTROS TABLESPACES ..................................................................26
2.8. FICHEROS DE REDO LOG..............................................................27
2.9. FICHEROS DE CONTROL...............................................................28
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 20/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 21/226
15
Arquitectura
de Oracle 9i 2
2.1. ESTRUCTURA DE ALMACENAMIENTO LOGICA Y FISICA
Oracle almacena su información en “estructuras de almacenamiento físicas” y “estructuras de
almacenamiento lógicas”.
La estructura de almacenamiento lógica, es una organización “conceptual” de los datos que
está compuesta por:
−
Tablespaces
−
Segmentos
− Extensiones
−
Bloques de Datos
La estructura de almacenamiento física son las unidades “tangibles” de almacenamiento, es
decir los ficheros del Sistema Operativo. Dicha estructura se compone de los siguientes
elementos:
−
Ficheros de Datos (DataFiles)
−
Ficheros REDO LOG (REDO0x.LOG)
− Ficheros de CONTROL. (CONTROL0x.CTL)
2.2. RELACIÓN TABLESPACES Y DATAFILES
Definimos el Tablespaces como el lugar lógico dónde Oracle agrupa a los ficheros de datos
(DataFiles), por tanto:
Espacio de Tabla
Tabla A Tabla B Tabla C .............
Fichero de Dato 1 Fichero de Dato 2
Un “Espacio de Tabla”, es una estructura lógica de los datos dentro de la B.D. deOracle, asociada a uno o más “Ficheros de Datos” físicos de disco duro.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 22/226
16
Arquitectura
de Oracle 9i 2
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 23/226
17
Arquitectura
de Oracle 9i 2
2.3. SEGMENTOS, EXTENSIONES Y BLOQUES DE DATOS
Un Tablespaces está compuesto por más de un segmento, definiendo un segmento como un
espacio que se asigna para un tipo específico de estructura de almacenamiento lógica,
teniendo segmentos de índices, segmentos de rollback y segmentos de datos.
Los segmentos pueden repartirse entre distintos Datafiles de un mismo Tablespaces.
A su vez, los segmentos se dividen en “extensiones”, las cuales definimos como un conjunto
contiguo de bloques, por lo que un segmento puede tener más de una extensión.
El bloque de datos de Oracle, es la unidad mínima de almacenamiento que se corresponde
con uno o más bloques físicos del Sistema Operativo dónde se ha creado la B.D.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 24/226
18
Arquitectura
de Oracle 9i 2
Reflejando lo explicado mediante un diagrama entidad-relación tendríamos:
Tablespaces
Seg. 1 Seg. 2
E1 E2 E3 E4
B1 B3 B7B2 B4 B5 B6 B8
Uno o más bloque de S.O.
B.D.
TABLESPACE
SEGMENTO
EXTENSIONES
BLOQUES BLOQUES DE S.O.
DataFiles
PARTE Lógica PARTE Física
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 25/226
19
Arquitectura
de Oracle 9i 2
Un “Mapa de Tablespaces” proporcionado por Oracle sería:
2.4. ESPACIO DE TABLAS SYSTEM (Tablespace SYSTEM)
Toda B.D. de Oracle tiene al menos un espacio de tabla llamado SYSTEM.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 26/226
20
Arquitectura
de Oracle 9i 2
La ruta de los “Archivos de Datos (DataFiles)” (físicos) asociados al Tablespace SYSTEM es
por defecto:
Unidad:\ORACLE\ORADATA\ SID\system01.dbf
(Teniendo en cuenta que SID es el nombre de la INSTANCIA a la B.D., bien creada de forma
genérica en el proceso de instalación o mediante la aplicación “Database Configuration
Assistant”).
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 27/226
21
Arquitectura
de Oracle 9i 2
El tablespace SYSTEM almacena:
−
El “Diccionario de Datos” de la B.D.
Diccionario de Datos.- Es un conjunto de Tablas y Vistas que almacenan
información interna de la B.D (NO almacena los datos).
Ejemplo.- La tabla DBA_TABLESPACES, que nos proporciona información de todos los
TABLESPACES existentes en nuestra B.D.
− Almacena el código fuente y compilado de los programas PL/SQL (procedimientos,
funciones, disparadores o trigger, paquetes).
− Las vistas, sinónimos, secuencias y las especificaciones de un objeto, son definiciones
que no almacenan datos, pero dichas definiciones se almacenan en el “diccionario de
datos” que se engloba en el Tablespaces SYSTEM.
2.5. DIFERENTES OPCIONES DE UN TABLESPACE
2.5.1. Tablespaces Permanentes (PERMANENT), Temporales (TEMPORARY) yDeshacer (UNDO)
Permanent.- La mayoría de los tablespaces son permanentes, es decir, se crean para
almacenar tablas, índices, información de restauración de datos (RollBack).
Temporary.- Son espacios de tablas que se crean para operaciones SQL que conlleven
ordenaciones, uniones y generación de índices.
Undo.- Anteriormente ROLLBACK, son espacios de tablas que almacenan las acciones aejecutar en una sentencia Rollback.
Si inicialmente un tablespaces es creado PERMANENT y después queremos que sea
TEMPORARY, podemos modificarlo mediante la siguiente sentencia SQL:
ALTER TABLESPACE "nombre_tablespace_temporal" TEMPORARY;
IMPORTANTE.- El Tablespace no tiene funcionalidad hasta que no se le asocia a un
usuario.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 28/226
22
Arquitectura
de Oracle 9i 2
ALTER USER "nombre_usuario" TEMPORARY
TABLESPACE "nombre_tablespace_temporary"
2.5.2. Tablespaces en línea (ONLINE) o fuera de línea (OFFLINE)
− OnLINE. Permiten a los usuarios y aplicaciones hacer uso de los datos.
−
OffLINE. No permite que sus datos estén disponibles para los usuarios y/o las
aplicaciones, aunque la BD esté arrancada o levantada.
Un tablespace se coloca OffLINE cuando existe algún problema con dicho tablespace y
tenemos que deshabilitar su uso. También emplearemos la opción OffLine ante la situaciónen la cuál tenemos un tablespaces que contiene datos históricos que no se usan pero que no
podemos eliminar.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 29/226
23
Arquitectura
de Oracle 9i 2
2.5.3. Espacios de Tablas (Tablespaces) administrado Localmente (Local) y por
Diccionario (Dictionary)
Oracle utiliza dos modalidades para administrar la petición y asignación de espacio en un
Tablespace.
Tablespaces gestionados localmente “Extensiones Gestionadas Localmente” (EXTENT
MANAGEMENT LOCAL) , esta es la opción POR DEFECTO, a partir de la versión 8i.
Los tablespaces que gestionan sus propias extensiones, conservan un bitmap en los 64 Kbyte
primero de cada archivo de datos para mantener un seguimiento del estado libre o utilizado
de los bloques de ese archivo de datos.
Cada bit del bitmap se corresponde con un bloque o grupo de bloques. Cada vez que una
extensión se asigna o se libera para volver a utilizarla, Oracle cambia los valores del bitmap
para que muestren el nuevo estado de los bloques. Esta modalidad, requiere menos
reorganización.
La otra modalidad es mediante administración DICTIONARY (Managed in the Dictionary),
usada en versiones anteriores. La información del espacio de tabla que se está usando, se
almacena en una serie de tablas existentes en el diccionario de datos.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 30/226
24
Arquitectura
de Oracle 9i 2
2.5.4. Tablespaces en modalidad de sólo lectura y lectura/escritura
Por defecto, todo Tablespace cuando se crea, es de lectura/escritura. Dicha modalidad es
cambiada a sólo lectura cuándo sabemos que los datos existentes en los objetos que
almacena ese tablespaces serán manipulados a nivel de consultas.
2.6. FICHEROS DE DATOS (DATAFILES)
Un DATAFILE es un fichero físico de almacenamiento de un tablespace. Cómo mínimo un
tablespace está asociado a un DataFile, pudiendo ser esta asociación de más de un DataFile.
IMPORTANTE.- Se aconseja que un tablespace vaya asociado a más de un datafile y que
éstos se encuentren en distintos discos duros, para reducir el factor E/S.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 31/226
25
Arquitectura
de Oracle 9i 2
NOTA.- Toda B.D. posee un límite de DataFiles que se pueden crear.
Si un TABLESPACE se llena, ORACLE produciría un error al no existir más capacidad de
almacenamiento para los objetos asignados a ese tablespace. Para solucionar esto podemos:
− Asignar un nuevo DATAFILE al TABLESPACE.
−
Aumentar el tamaño del DATAFILE ya existente.
− Al crear el TABLESPACE, asignar un DATAFILE de crecimiento automático.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 32/226
26
Arquitectura
de Oracle 9i 2
2.7. OTROS TABLESPACES
− Tablespaces USERS.- Almacena la información y datos pertenecientes a los usuarios
existentes en la B.D.
−
Tablespaces TEMP.- En dicho tablespaces, Oracle almacena información temporal desu propio funcionamiento (para tener una similitud, es como el “papel sucio” que
usamos en nuestro trabajo).
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 33/226
27
Arquitectura
de Oracle 9i 2
−
Tablespaces UNDOTBS1.- Este tablespaces en versiones anteriores se llamaba
ROLLBACK_DATA, siendo su funcionalidad almacenar la información de deshacer, es
decir, rehacer una transacción mal realizada y la coherencia de lectura antes de
ejecutar una instrucción commit.
− Tablespaces TOOLS.- Este tablespaces se utiliza para almacenar objetos de otras
aplicaciones de Oracle (Form, Report, Designer,...) o de otros fabricantes.
− Tablespaces INDX.- Almacena información correspondiente a los índices de las tablas.
Es aconsejable y recomendable crear un tablespaces independiente al de datos para
asignar la gestión de los índices.
2.8. FICHEROS DE REDO LOG
Son ficheros físicos dónde Oracle registra todas las modificaciones o transacciones que se
producen en la B.D. Esta información se gestiona en memoria (por rendimiento), siendo los
procesos internos de Oracle los encargados de escribirlo posteriormente a los ficheros.
Se recomienda mínimo dos ficheros de REDO, por B.D. existente.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 34/226
28
Arquitectura
de Oracle 9i 2
Funcionan de forma cíclica, es decir, si tenemos dos archivos (redo01.log y redo02.log), se
escribe 1º en el fichero redo01.log activo, cuando este se llena, pasamos a escribir en el
fichero redo02.log y al llenarse este, se borra la información del Redo01.log y se usa ese
espacio liberado para almacenar la siguiente información de transacciones.
IMPORTANTE.- Esto nos lleva a diferenciar entre B.D. en modo ARCHIVELOG y
NOARCHIVELOG.
Si una B.D. esta en modo ARCHIVELOG, esto quiere decir que Oracle siempre realizará una
copia del fichero REDO LOG antes de borrar y volver a escribir. Es la forma de mantener
segura la información existente en la B.D. Este NO es el método predeterminado.
El modo NOARCHIVELOG (Por defecto), no realiza copia de los ficheros REDO LOG,
recuperándose sólo las últimas transacciones realizadas.
2.9. FICHEROS DE CONTROL
Almacenan información de todos los ficheros asociados y relacionados en una B.D., en ellos
se almacena, por ejemplo, que fichero REDO se debe tratar para una posible recuperación.
Si al comprobar en éstos que el estado de la B.D. no es correcto, NUNCA arrancará.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 35/226
29
Arquitectura
de Oracle 9i 2
− Oracle organiza la información en estructuras de datos físicas y lógicas.
−
Todo TABLESPACE tiene como mínimo un DATAFILE. Si existiesen más de un
DATAFILE, se recomienda a discos distintos.
−
En la versión Oracle 9i los tablespaces son gestionados localmente, no por
diccionario de datos.
−
Oracle almacena toda la información de DESHACER en los ficheros REDO.
− En los ficheros de CONTROL Oracle almacena la información de todos los
ficheros implicados en el funcionamiento de la B.D.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 36/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 37/226
31
Creación de
Tablespaces 3
3.1. DEFINICIÓN DE SQL. (STRUCTURED QUERY LANGUAGE) ............33
3.2. CREACIÓN DE TABLESPACES.......................................................33
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 38/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 39/226
33
Creación de
Tablespaces 3
3.1. DEFINICIÓN DE SQL. (STRUCTURED QUERY LANGUAGE)
SQL es un lenguaje estructurado de consulta que nos permitirá interactuar hacia una B.D.
relacional. Las instrucciones o sentencias SQL, las podemos dividir en tres tipos: DDL, DML y
DCL.
− DDL (Data Definition Language ).- Nos permite manipular y definir la estructura dónde
se almacenarán los datos. Éstas son las instrucciones propias para crear, modificar y
borrar tablespaces, tablas, índices, etc., es decir: CREATE, ALTER ,DROP.
− DML (Data Management Language).- Son las que nos permiten el manejo más básico
de los datos, tales como insertarlos, consultarlos, modificarlos y borrarlos, es decir:INSERT, SELECT, UPDATE, DELETE.
− DCL (Data Control Language).- Son las instrucciones que permiten asignar funciones
de acceso, privacidad en los datos, política de seguridad, etc. Este tipo de
instrucciones o tareas son las propias que debe realizar el DBA. Por ejemplo: crear un
usuario, asignarle permiso de acceso, etc.
CREATE USER "USUARIO_PRUEBA" .......;
GRANT "CONNECT" TO "USUARIO_PRUEBA";
3.2. CREACIÓN DE TABLESPACES
Un tablespaces podemos crearlo mediante la ejecución directa de una sentencia DDL o
mediante la utilización de la herramienta gráfica de Oracle conocida como “Enterprise
Manager Console”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 40/226
34
Creación de
Tablespaces 3
Crearemos un tablespaces llamado “PRUEBA” usando la herramienta gráfica. A partir de ella,
obtendremos la sentencia SQL, la cuál iremos comentando.
− Paso 1.- Abrimos la aplicación “Enterprise Manager Console”.
− Paso 2.- Una vez en ella, seleccionamos la B.D. sobre la cuál se creará el Tablespace.
Para poder crear un Tablespace sobre una B.D., deberemos tener permiso. En nuestro
caso lo llevaremos a cabo mediante el usuario System.
− Paso 3.- Seleccionamos el apartado “Almacenamiento” y dentro de éste, la carpeta
“Tablespaces”. Haciendo clic derecho sobre ella, nos aparecerá una ventana flotante
donde seleccionamos la opción “CREAR”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 41/226
35
Creación de
Tablespaces 3
− Paso 4.- En la ventana que nos aparece, escribimos el nombre “PRUEBA”. En dicha
ventana, podemos observar cómo Oracle de forma automática asigna el mismo
nombre del tablespace al fichero de datos y lo ubicará por defecto en
“C:\ORACLE\ORADATA\GLOBAL\” siendo GLOBAL el nombre de la B.D. y asigna como
tamaño del DataFile, 5 Mbyte.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 42/226
36
Creación de
Tablespaces 3
opciones por defecto asignadas a los parámetros de almacenamiento son las siguientes:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 43/226
37
Creación de
Tablespaces 3
− Paso 5.- Una vez pulsado el botón de CREAR, nos debe aparecer el mensaje de
Tablespace creado. Una vez hecho esto, podremos comprobar como aparece dentro de
los tablespaces existentes en la carpeta “Tablespaces”.
La sintaxis de la sentencia SQL que crearía el TABLESPACE “prueba” sería:
CREATE TABLESPACE "PRUEBA"
LOGGING
DATAFILE 'C:\ORACLE\ORADATA\GLOBAL\PRUEBA.ora' SIZE 5M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE
MANAGEMENT AUTO
Lo primero que nos encontramos es el nombre del TABLESPACE, en este caso: “PRUEBA”.
A continuación, la cláusula LOGGING, que puede ser LOGGING o NOLOGGING, nos
permite establecer a nivel global sobre el Tablespaces la opción de generar información de
REDO (Deshacer) sobre los objetos tablas e índices. Por defecto es LOGGING, aunque es
modificable a nivel de objeto individualmente, es decir, al crear una tabla o especificar un
índice.
− DATAFILE 'C:\ORACLE\ORADATA\GLOBAL\PRUEBA.ora' SIZE 5M .- Ubicación,
nombre y tamaño del DataFile.
− EXTENT MANAGEMENT.- Esta cláusula hace referencia a la gestión de los segmentos
del tablespace. Un tablespace puede ser gestionado de forma LOCAL o por
DICTIONARY (ver el Tema 2, aparatado 2.5.3).
• EXTENT MANAGEMENT LOCAL.- En ella, se especifican cómo se gestionarán las
extensiones del Tablespaces. Por defecto, los tamaños de segmentos son
gestionados automáticamente por el sistema.
• EXTENT MANAGEMENT LOCAL UNIFORM 1M.- El espacio de tabla está
gestionado con extensiones uniformes de 1 Mega.
−
SEGMENT SPACE MANAGEMENT AUTO.- Esta cláusula sólo es importante para
espacios de tablas temporales que estén gestionados localmente. Sirve para
especificar si Oracle debe realizar el seguimiento del espacio libre y del utilizado en los
segmentos del espacio de tablas utilizando listas de extensiones libres (MANUAL) o
mapas de bits (AUTO).
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 44/226
38
Creación de
Tablespaces 3
Los DataFiles poseen un tamaño inicial: DATAFILE
'C:\ORACLE\ORADATA\GLOBAL\PRUEBA.ora' SIZE 5M. Dicho valor, por defecto no es
ampliable automáticamente. Si se llena el DataFiles, debemos crear otro Datafiles para que
el Tablespaces siga operativo.
Para especificar que un DataFiles sea ampliable automáticamente, podemos hacerlo
gráficamente mediante la pestaña “Almacenamiento” correspondiente a las propiedades del
Datafiles.
Una vez activada esa opción, debemos especificar el valor Kbytes o Mbytes que queremos
que crezca el Datafiles y si deseamos que su crecimiento sea ilimitado (UNLIMITED) o
limitado (MAXSIZE valor).
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 45/226
39
Creación de
Tablespaces 3
Gráficamente sería:
La sentencia SQL sería:
CREATE TABLESPACE "PRUEBA"
LOGGING
DATAFILE 'C:\ORACLE\ORADATA\GLOBAL\PRUEBA.ora' SIZE 5M
AUTOEXTEND ON
NEXT 15K
MAXSIZE 100M
EXTENT MANAGEMENT LOCAL SEGMENTSPACE MANAGEMENT AUTO
Si no se especificase el Tamaño Máximo como limitado, la cláusula MAXSIZE tomaría el
valor UNLIMITED.
Importante: el valor mínimo del NEXT debe ser un Tamaño de Bloque, en este caso 8.192
Bytes. ( 8 KByte). Si se especificase un valor inferior, Oracle lo ajusta.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 46/226
40
Creación de
Tablespaces 3
Para eliminar un tablespaces, es tan simple como hacer clic derecho sobre el nombre del
Tablespace, dentro de la aplicación “Enterprise Manager Console” y seleccionar la opción
“Eliminar”. En versiones anteriores, tras borrar un Tablespaces, el DataFile o Datafiles
asociados no eran eliminados físicamente del Sistema Operativo. Esta opción de Oracle nos
lo permite.
Tanto el número de Tablespaces que puede poseer una B.D. es limitado al igual que el
número de DataFiles por Tablespaces.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 47/226
41
Creación de
Tablespaces 3
− SQL es un lenguaje que nos permite interactuar con una B.D.
− El primer paso a la hora de trabajar con una B.D. Oracle, es crear un
TABLESPACE.
− Todo TABLESPACE lleva asociado como mínimo un DATAFILE.
− Al crear el TABLESPACE, se aconseja que este sea “gestionado localmente”.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 48/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 49/226
43
Usuarios 4
4.1. DEFINICIÓN ...............................................................................45
4.2. ASIGNACIÓN DE PRIVILEGIOS ...................................................45
4.3. ASIGNACIÓN DE ROLES ..............................................................47
4.4. CREACIÓN DE USUARIOS............................................................48
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 50/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 51/226
45
Usuarios 4
4.1. DEFINICIÓN
Los usuarios son los propietarios de los objetos de la B.D. (Tablas, Índices, Funciones,
Procedimientos, Paquetes, Secuencias, etc.).
En todo S.G.B.D. Oracle, diferenciamos dos usuarios muy importantes:
a) El usuario SYS.- Es el propietario de las tablas del DICCIONARIO DE DATOS, que es
dónde se almacena información correspondiente a la estructura de la B.D. Además de
tener acceso a la ejecución de los paquetes predefinidos ( DBMS_ ).
b) El usuario SYSTEM.- Esta cuenta de usuario es la cuenta por defecto delAdministrador de la B.D. Se aconseja y recomienda crear otra cuenta para el
administrador y no usar la cuenta “System”. Este usuario, está autorizado a tener
acceso a las vistas que permiten obtener información de las tablas del Diccionario de
Datos.
Un aspecto que debemos tener presente es que a partir de la versión 9i desaparece la cuenta
de usuario INTERNAL/Oracle.
Por tanto, podemos decir que las cuentas SYS y SYSTEM son las de mayor control y podersobre la B.D., por lo que ES OBLIGATORIO cambiar su clave. Este cambio es solicitado en
el proceso de instalación.
Los usuarios se asignan a Tablespaces creados previamente para el almacenamiento de los
objetos. Se recomienda siempre dos tablespaces el “Default” o de “Datos” y el “Temporal”.
La cuenta de un usuario puede bloquearse (lock) para evitar acceder a la B.D.
Cada vez que se crea un objeto de la B.D. se crea bajo una cuenta de usuario.
4.2. ASIGNACIÓN DE PRIVILEGIOS
A los usuarios, se le asigna PRIVILEGIOS que son acciones que pueden realizar sobre los
objetos creados que se vayan a crear o sobre el “Sistema”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 52/226
46
Usuarios 4
Diferenciamos:
− Privilegios de Sistema.- Permite al usuario realizar algún tipo de acción que afecte a
todo el Sistema. Son unos 155 aproximadamente. Por ejemplo:
• CREATE SESSION.- Permiso para conectarse al servidor de B.D. Si no tuviera
dicho permiso, mostraría el siguiente error:
ERROR:
ORA-01045: user USR_PRUEBA lacks CREATE SESSION privilege; logon
denied
• CREATE TABLE.- Poder crear tablas en su propio esquema.
• CREATE ANY TABLE.- Crear tablas en cualquier esquema de la B.D.
• SELECT ANY TABLE.- Consultar tablas de todos los esquemas.
• EXECUTE ANY PROCEDURE.- Ejecutar procedimientos, funciones o paquetes en
toda la B.D.
• SELECT ANY DICTIONARY.- Nos permite utilizar herramientas de gestión de
Oracle sin ser DBA. Por ejemplo “Enterprise Manager Console”, pero con muchas
limitaciones.
• GRANT ANY ROLE.- Nos muestra todos los Roles disponibles, PERO no quiere decir
que podamos asignarlos o revocarlos.
• GRANT ANY PRIVILEGE.- Igual que “GRANT ANY ROLE”, pero aplicado a privilegios.
− Privilegios de Objetos.- Permite al usuario realizar una acción específica sobre un
objeto u objetos de la B.D., como tablas, vistas, procedimientos, funciones, etc. Por
ejemplo: En la imagen gráfica que se adjunta, podemos ver como el usuario
USR_PRUEBA_2, se le ha concedido los privilegios de Alter, Delete, Insert, Index,
References, Select y Update, sobre el objeto TABLA1 pertenecientes al usuario
USR_PRUEBA.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 53/226
47
Usuarios 4
• ALTER.- Modificar el objeto.
• DELETE.- Borrar filas del objeto.
• INSERT.- Insertar filas (registros) en el objeto.
• INDEX.- Crear índices en la tabla.
• REFERENCES.- Crear claves externas que hagan referencia a la tabla.
• SELECT.- Seleccionar columnas (campos).
• UPDATE.- Actualizar filas de la tabla.
• EXECUTE.- Ejecutar funciones, procedimientos, paquetes, clases Java.
4.3. ASIGNACIÓN DE ROLES
Los ROLES son conjuntos de privilegios que se crean para hacer más sencillo el proceso de
administración. Por defecto, Oracle nos proporciona ROLES ya creados sobre los cualespodemos añadir más privilegios. También podemos crear nuevos ROLES personalizados que
engloben a otros roles existentes y nuevos privilegios. Dentro de los ROLES predefinidos,
destacamos:
− CONNECT.- Podemos catalogarlo como un Rol “Básico” ya que permite al usuario que
se le asigna conectarse con la B.D., crear tablas, vistas, sinónimos, secuencias y otros
objetos de la B.D.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 54/226
48
Usuarios 4
− RESOURCE.- Al usuario que se le concede, se le permite crear tablas, secuencias,
índices, clusters de datos, procedimientos, funciones, paquetes, etc, es el rol asignado
a los desarrolladores o programadores.
−
DBA.- Es el asignado a los Administradores de B.D., siendo el más poderoso.
− SELECT_CATALOG_ROLE.- Permite consultar las vistas del Diccionario de Datos,
pertenecientes al DBA, es decir, las vistas “DBA_”.
− EXECUTE_CATALOG_ROLE.- Podemos ejecutar los paquetes predefinidos DBMS.
(DBMS_UTILITY, DBMS_OUTPUT, DBMS_SHARED_POOL, etc.).
Tanto los Roles como los Privilegios, se pueden crear “con opción de Administración” (WITH
ADMIN OPTION), permitiendo al propio usuario que se le ha asignado gestionarlo, por
ejemplo revocarlo, conceder a otro usuario ese mismo privilegio o rol. Por defecto se asignan
con opción de NO administración.
4.4. CREACIÓN DE USUARIOS
Igual que con los tablespaces, para crear un usuario podemos hacerlo desde la herramientagráfica “Enterprise Manager Console” o mediante sentencia SQL.
Desde la herramienta gráfica:
− Paso 1.- Abrimos la herramienta “Enterprise Manager Console”, tras conectar como
usuario “System”, seleccionamos el apartado “Seguridad” y después la carpeta
“Usuarios”. Sobre dicha carpeta hacemos clic derecho y seleccionamos la opción
“Crear”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 55/226
49
Usuarios 4
− Paso 2.- En este paso sólo escribiremos el nombre del usuario: USR_PRUEBA, y como
clave: usr_prueba, asignándoles el tablespaces por defecto PRUEBA_DEFAULT y como
temporal el PRUEBA_TEMPORAL. Si no se especificase ninguno, el tablespaces por
defecto es el USERS y el temporal TEMP ya que es un Tablespaces de tipo TEMPORAL y
es definido como temporal por defecto.
El tipo de AUTENTIFICACIÓN puede ser del tipo:
• Contraseña.- Oracle solicita cada vez que un usuario decide tener acceso a una
herramienta suya, un nombre de usuario y una contraseña. Su comprobación se
realiza en la propia gestión de usuarios de Oracle.
• Externo.- También conocida como de autentificación por “Sistema Operativo”, es
decir, el usuario debe estar registrado como usuario del Sistema Operativo.
• Global.- Este tipo de autentificación es la usada en aquellas redes empresariales,
dónde los usuarios tienen que tener acceso a más de una B.D.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 56/226
50
Usuarios 4
Si observamos la pestaña ROL, podemos comprobar como se le asigna de forma
predeterminada el Rol “CONNECT”.
La sentencia SQL correspondiente a la creación del usuario "USR_PRUEBA" sería:
CREATE USER "USR_PRUEBA"
PROFILE "DEFAULT"
IDENTIFIED BY "usr_prueba"
DEFAULT TABLESPACE "PRUEBA_DEFAULT"
TEMPORARY TABLESPACE "PRUEBA_TEMPORAL"
ACCOUNT UNLOCK;
GRANT "CONNECT" TO "USR_PRUEBA";
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 57/226
51
Usuarios 4
El Rol “Connect” engloba a los siguientes privilegios por defecto:
− ALTER SESSION.- Modificar una “Session” de conexión.
− CREATE CLUSTER.- Permite la creación de clusters de datos. Los clusters de datos
son la alternativa a la indexación, reduciendo las Entrada/Salida a disco para acceder
a las tablas.
− CREATE DATABASE LINK.- Crear enlaces a B.D. distribuidas.
−
CREATE SEQUENCE.- Crear secuencias numéricas.
− CREATE SESSION.- Nos permite conectarnos al servidor de B.D. y establecer una
sesión.
− CREATE SYNONYM.- Crear Sinónimos de objetos de la B.D. de otros esquemas,
para poder usarlos sin referenciar al esquema.
− CREATE TABLE.- Crear Tablas.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 58/226
52
Usuarios 4
− CREATE VIEW.- Crear Vista sobre las tablas. Una Vista es un subconjunto de
columnas de una tabla.
Aunque el Rol “Connect” engloba el privilegio “Create Table”, esto no proporciona CUOTA al
usuario sobre el TABLESPACES. Por ello, para poder usar los Tablespaces asociados a un
usuario, debemos especificar la CUOTA (QUOTA) ilimitada (UNLIMITED) del usuario hacia el
TABLESPACES o un valor de cuota.
Gráficamente sería:
Mediante sentencia SQL:
ALTER USER "USR_PRUEBA"
QUOTA UNLIMITED ON "PRUEBA_DEFAULT"
QUOTA UNLIMITED ON "PRUEBA_TEMPORAL"
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 59/226
53
Usuarios 4
Los comandos para asignar y quitar privilegios y roles a los usuarios son GRANT y REVOKE
respectivamente.
Sintaxis:
Para conceder:
Privilegios de Sistema a Usuarios:
GRANT privilegio [ , privilegio] ........
TO usuario [ , usuario] ........
[WITH ADMIN OPTION]
Privilegios de Objetos a Usuarios:
GRANT privilegio [ , privilegio] ........|| ALL [PRIVILEGES]
ON [esquema.] Objeto
TO usuario [ , usuario] ........
[WITH GRANT OPTION]
Ejemplo:
GRANT DELETE ON "USR_PRUEBA"."TABLA1" TO "USR_PRUEBA"
ROLES a Usuarios:
GRANT nombre_del_rol [ ,nombre_del_rol]......
TO usuario [,usuario]......
[WITH GRANT OPTION];
Ejemplo:
GRANT "AUTHENTICATEDUSER","DBA" TO "USR_PRUEBA";
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 60/226
54
Usuarios 4
Para quitar o revocar:
Privilegios de Sistema a Usuarios:
REVOKE privilegio [ , privilegio] ........ FROM usuario;
Privilegios de Objetos a Usuarios:
REVOKE privilegio [ , privilegio] ........|| ALL [PRIVILEGES]
ON [esquema.] Objeto
FROM usuario [ , usuario] ........
Ejemplo:
REVOKE INSERT
ON "USR_PRUEBA"."TABLA1"
FROM "USR_PRUEBA"
Revocar un Rol:
REVOKE nombre_del_rol [ ,nombre_del_rol]...... FROM usuario;
Podemos crear ROLES propios formado por otros roles y privilegios de sistema y de objetos.
Esto se recomienda cuando tenemos que crear muchos usuarios de la B.D. y asignarles roles
y privilegios.
Para ello, podemos usar la herramienta gráfica EMC (Enterprise Manager Console) o
mediante la sentencia SQL.
CREATE ROLE nombre_rol
[NOT IDENTIFIED || IDENTIFIED “contraseña”];
GRANT "rol_existente" TO "nombre_rol";
GRANT privilegio_de_sistema TO " nombre_rol ";
GRANT privilegio_de_objeto ON "esquema"."objeto" TO " nombre_rol ";
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 61/226
55
Usuarios 4
Ejemplo:
CREATE ROLE "ROL_PRUEBA" IDENTIFIED BY "clave_rol";
GRANT "DBA" TO "ROL_PRUEBA";
GRANT ALTER ANY CLUSTER TO "ROL_PRUEBA"
GRANT DELETE ON "USR_PRUEBA"."TABLA1" TO "ROL_PRUEBA"
Gráficamente sería:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 62/226
56
Usuarios 4
Toda la información correspondiente a los Roles y Privilegios es almacenada en las vistas del
Diccionario de Datos, estas son:
− DBA_ROLES.- Nombre de los ROLES y estado de contraseña.
−
DBA_ROLE_PRIVS.- Usuarios que poseen ROLES.
− DBA_SYS_PRIVS.- Usuarios con Privilegios de Sistema.
− DBA_TAB_PRIVS.- Usuarios con Privilegios sobre las Tablas.
− DBA_COL_PRIVS.- Usuarios con Privilegios sobre columnas.
− ROLE_ROLE_PRIVS.-Roles concedidos a otros roles.
− ROLE_SYS_PRIVS.- Privilegios de Sistemas otorgados a Roles.
− ROLE_TAB_PRIVS- Privilegios de Tablas otorgados a Roles.
Por ejemplo: ¿Qué ROLES tiene asignado el usuario “USR_PRUEBA”?
Select * from DBA_ROLE_PRIVS where GRANTEE='USR_PRUEBA';
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 63/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 64/226
58
Usuarios 4
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 65/226
59
Usuarios 4
− Los usuarios SYS y SYSTEM son los de mayor “PODER” sobre la B.D.
− El nivel de “AUTORIZACIÓN” de los usuarios, se les concede por medio de
los PRIVILEGIOS y ROLES.
− Puede crear a los usuarios mediante sentencias SQL o a través de
“Enterprise Manager Console”.
− Un usuario puede tener o no un perfil, pero en caso de tenerlo, sólo puede
poseer uno.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 66/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 67/226
61
Tablas 5
5.1. DEFINICIÓN ................................................................................63
5.2. TIPOS DE DATOS .........................................................................63
5.3. DEFINICIÓN DE ESQUEMA ...........................................................66
5.4. PASOS PARA CREAR UNA TABLA ..................................................67
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 68/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 69/226
63
Tablas 5
5.1. DEFINICIÓN
Las TABLAS son la estructura básica en los diseños de B.D. Se componen de una serie de
columnas (campos) cuyo conjunto se denomina filas (registros). Ellas contienen o almacenan
los datos por columnas agrupadas en filas.
Es aconsejable que toda tabla tenga una columna denominada clave, es decir, es aquella
columna que identifica de forma única a cada fila. Puede estar compuesta por más de una
columna. A esa columna o columnas se le denomina PRIMERY KEY.
Ejemplo:
Tabla: VEHICULOS
Campos o Columnas: Matricula, Modelo, Color, Cilindrada, Fecha_Compra, Precio_Venta
Filas o Registros:
3234-BCV, OPEL, Blanco, 1600, 10/10/1998, 6100.56
SE-2356-FG, SEAT, Azul, 1400, 12/11/2000, 6500.75
VEHICULOS
Matricula Modelo Color Cilindrada Fecha_Compra Precio_Venta
3234-BCV OPEL Blanco 1600 10/10/1998 6100,55 €
SE-2356-FG SEAT Azul 1400 12/11/2000 6500,75 €
La columna “Primery Key” es la columna “Matricula” que es única por cada fila.
5.2. TIPOS DE DATOS
Los DATOS de las columnas de una tabla pueden ser de los siguientes TIPOS:
− CHAR.- Columna de caracteres de longitud FIJA, cuyo valor máximo es 2000
caracteres.
−
VARCHAR2.- Columna de caracteres de longitud VARIABLE, cuyo valor máximo es
4000 caracteres.
Oracle permite almacenar y manejar datos de caracteres de muchos idiomas, característica
conocida como National Language Support (NLS). Esto implica que algunos caracteres de
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 70/226
64
Tablas 5
estos idiomas necesitan más de un byte para su almacenamiento, de ahí que usemos los
tipos de datos: NCHAR, NVARCHAR2 y CLOB.
− NCHAR.- Columna de longitud FIJA para juego de caracteres MULTIBYTE. Su tamaño
máximo es 2000 caracteres o 2000 Bytes por fila, dependiendo del juego de
caracteres, siendo la configuración predeterminada de 1 Byte.
− NVARCHAR2.- Columna de longitud VARIABLE para juego de caracteres MULTIBYTE. Su
tamaño máximo es 4000 caracteres o 4000 Bytes por fila, dependiendo del juego de
caracteres, siendo la configuración predeterminada de 1 byte.
− NUMBER.- Columna numérica de longitud VARIABLE. Abarca los tipos de datos
ANSI/ISO3 NUMERIC, DECIMAL, DEC, INTEGER, INT, SMALLINT. Si queremos
representar un número decimal sería NUMBRE(9,2), que quiere decir: 9 dígitos de los
que 2 son decimales.
El número de BYTE que ocupa el número a almacenar, se calcula dividiendo la longitud
total por 2, se redondea a un número entero y se suma 1. Por tanto NUMBER (9)
ocuparía 6 Bytes.
− DATE.- Columna de longitud FIJA de 7 Bytes, que nos permite almacenar la fecha y la
hora. Su formato predeterminado es: DD-MON-YY HH:MI:SS. Con HH en formato AM
y PM.
− LONG.- Columna que puede contener una cadena de longitud variable, con longitud
máxima de 2GBytes.
− RAW.- Columna de longitud VARIABLE para datos binarios, de longitud máxima 2000
Bytes. Podemos decir que RAW es similar a VARCHAR2, pero con la diferencia de que
no se realizan conversiones entre conjuntos de caracteres distintos, de diferentes B.D.
− LONG RAW.- Columna de longitud variable para datos binarios, de longitud máxima
2GBytes.
− ROWID.- Datos binarios que representan un identificador de fila (RowID).
Los identificadores de fila se almacenan internamente como un número binario de
longitud fija, siendo esta longitud variable entre distintos S.O. Para poder manipular
3 American National Standards/International Organization for Standardization
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 71/226
65
Tablas 5
los identificadores de fila, estos pueden ser convertidos a una cadena de caracteres
mediante la función ROWIDTOCHAR. Esta función genera una salida de 18 caracteres
con el formato:
BBBBBBBB.FFFF.AAAA
Donde:
BBBBBBBB.- Identifica el bloque de dato dentro del Datafile.
FFFF.- Identifica la Fila dentro del bloque.
AAAA.- El número de archivo.
Cada parte es un número hexadecimal, por ejemplo:
0000001E.00FF.0001 representa el bloque 30. fila 255 u fichero 1.
Los tipos de la Familia LOB disponibles a partir de Oracle 8 o superior se crearon para
solucionar y ampliar las prestaciones de los tipos LONG y LONG RAW.
Los LOB internos son BLOB, CLOB y NCLOB y el LOG externo es BFILE. Los LOB internos, se
manipulan usando órdenes DML SQL o el paquete predefinido DBMS_LOB.
− BLOB.- Objeto binario de GRAN TAMAÑO, hasta 4 GBytes.
− CLOB.- Objeto de caracteres de GRAN TAMAÑO, hasta 4 Gbytes.
− NCLOB.- Tipo de dato CLOB, para juego de caracteres multibyte, hasta 4Gbytes.
− BFILE.- Archivo binario externo, siendo el S.O. quien determina el Tamaño. Su
acceso es de sólo lectura. Su información no es gestionada por Oracle, sino por el S.O.
por lo que no están sujetos a transacciones.
− UROWID.- Datos binarios utilizados para el direccionamiento de datos, de longitud
máxima 4000 bytes. Usado para el manejo de tablas e índices particionados.
− FLOAT.- Abarca los tipos de datos ANSI/ISO estándar FLOAT, REAL, DOUBLE
PRECISION, todos ellos podemos englobarlo dentro del NUMBER.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 72/226
66
Tablas 5
5.3. DEFINICIÓN DE ESQUEMA
Los ESQUEMAS (Schemas) es dónde se organiza de forma LOGICA los distintos objetos
relacionados de la B.D. (Tablas, Índices, Vistas, Sinónimos, Secuencias, Agrupamientos,
Tipos de Origen, Tipos de Usuario).
Esta forma de organización lógica, nos permite tener objetos con el mismo nombre, por
ejemplo dos tablas, en esquemas distintos sin crear ningún conflicto a la gestión interna de
Oracle.
Hay que tener MUY CLARO que toda nuestra estructura lógica de la B.D. se organiza en el
Esquema, además de que un esquema está directamente relacionado con un
USUARIO, tal que el ESQUEMA tiene el mismo nombre que el USUARIO. Por ello, se crean
al crear un objeto perteneciente al usuario.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 73/226
67
Tablas 5
5.4. PASOS PARA CREAR UNA TABLA
Para crear una tabla podemos crearla mediante una sentencia SQL o mediante la
herramienta gráfica OMC (Oracle Manager Console). Si usamos el OMC, Oracle nos
proporciona un asistente.
Los pasos serían:
− Paso 1.- Abrimos el OMC y seleccionamos el apartado “Esquema” y hacemos clic
derecho sobre cualquiera de los esquemas. Esto hace que nos aparezca una ventana
flotante para poder crear distintos objetos.
− Paso 2.- Seleccionamos el objeto “Tabla” y pulsamos el botón “&Crear”, comprobando
que tengamos activa la opción “&Usar Asistente”. En este punto, nos aparecerá la
primera ventana del asistente “Paso 1 de 13”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 74/226
68
Tablas 5
En ella especificamos el nombre de la Tabla, “VEHICULOS”. Al especificar el esquema del cuál
va a formar parte ese objeto, automáticamente se le asigna su Tablespaces de Datos.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 75/226
69
Tablas 5
− Paso 3.- La siguiente ventana “Paso 2 de 13”, nos permite especificar las columnas
que componen nuestra tabla.
Campos o Columnas: Matricula, Modelo, Color, Cilindrada, Fecha_Compra,
Precio_Venta
− Paso 4.- La siguiente ventana del asistente “Paso 3 de 13”, nos permitirá especificar
qué columna de las que componen la Tabla es la clave primaria. Además de qué
nombre deseamos poner a la restricción que comprueba y verificará que sea único por
cada fila el valor introducido en dicha columna o campo. Oracle crea una asignación
automática.
Para ello, sólo tendremos que seleccionar la opción “Sí, deseo crear una clave
primaria”, y especificar en la columna “Orden” el orden de creación. Por nombre de
restricción, no usaremos en este ejemplo el “Asignado por el Sistema”, por lo que
usaremos el nombre PK_VEHICULO.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 76/226
70
Tablas 5
− Paso 5.- La siguiente ventana del asistente, nos permite especificar las restricciones
de valor nulo y de valor único en las distintas columnas de nuestra B.D. Aquellas
columnas que deseemos sean únicas, podemos especificar el nombre de la restricción,
o bien, que Oracle los asigne automáticamente.
En nuestro ejemplo sólo especificaremos que la columna “COLOR” pueda tomar el valor
NULO (NULL).
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 77/226
71
Tablas 5
− Paso 6.- Es este paso del asistente “Paso 5 de 13”, se detalla qué columna de nuestra
tabla es una clave ajena de otra tabla, también conocida como FOREIGN KEY. Para
este ejemplo no tenemos ninguna.
Si la hubiese, tendríamos que detallar el esquema en el que está la tabla “Maestra”
(Esquema de Referencia), nombre de la misma (Tabla de Referencia) y columna de la
tabla que se “hereda” (Columna de Referencia).
− Paso 7.- El “Paso 6 de 13”, nos permite realizar un control sobre posibles valores a
tomar por la columna o conversiones automáticas.
En nuestro ejemplo obligaremos a que los posibles valores de la columna “MODELO”
sean: OPEL, SEAT, RENAULT, MERCEDES, AUDI. También que los valores de la
“MATRICULA” se encuentren en mayúscula: MATRICULA = UPPER(MATRICULA).
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 78/226
72
Tablas 5
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 79/226
73
Tablas 5
− Paso 8.- En el “Paso 7 de 13”, se permite la modificación de los parámetros de
almacenamiento por defecto para la Tabla.
Si NO SE SELECCIONA la opción “Sustituir parámetros de almacenamiento por
defecto”, Oracle gestionará el tamaño automáticamente, basándose en el “Tamaño
Inicial” y en la “Tasa de Crecimiento” aproximado de cada fila de la tabla.
• Número Inicial de Filas: Número de filas de la tabla. Oracle, utiliza el número de
filas para determinar el tamaño de la primera extensión de segmento.
•
Ratio de Crecimiento: Ratio de crecimiento de la tabla esperado en filas / unidadde tiempo (día, semana, mes, año). Este valor es usado por Oracle para
determinar el tamaño (en bytes) de la siguiente extensión de segmento.
Las opciones Actividad de Actualización y Actividad de Inserción permiten a
Oracle definir los parámetros del bloque de datos óptimos que minimizan el
encadenamiento y la migración de filas.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 80/226
74
Tablas 5
• Actividad de Actualización.- Determina el porcentaje de espacio de cada bloque
de datos de la tabla reservado para futuras actualizaciones de las filas de la tabla
(PCTFREE).
o
Superior: Define el porcentaje de cada bloque de datos para actualizaciones
de las filas existentes y permite inserciones de las nuevas filas hasta rellenar
un máximo de un 90 por ciento de cada bloque de datos.
o Tamaño de Fila de Aumento por Actualización: Disponible si la Actividad
de Actualización es alta, reserva el 20 por ciento de cada bloque de datos para
actualizaciones de las filas existentes, incluidas las supresiones.
o Inferior o Ninguno: Reserva el 5 por ciento de cada bloque de datos para
actualizaciones de las filas existentes y permite inserciones de nuevas filas
hasta rellenar un máximo de un 95 por ciento de cada bloque de datos.
• Actividad de Inserción.-Determina el porcentaje mínimo de espacio utilizado que
Oracle mantiene para cada bloque de datos de la tabla (PCTUSED).
o Superior: Define el porcentaje mínimo de espacio utilizado en un 40 por
ciento del bloque de datos de la tabla.
o Tamaño de Fila de Aumento por Actualización: Disponible si la Actividad
de Inserción es alta, define el porcentaje mínimo de espacio utilizado en un 60
por ciento del bloque de datos de la tabla.
o Inferior o Ninguno: Define el porcentaje mínimo de espacio utilizado en un
60 por ciento del bloque de datos de la tabla.
− Paso 9.- En este paso, se especificará si queremos particionar la Tabla.
Con el particionamiento, se solucionan los problemas de rendimiento y soporte a tablas
e índices de tamaño muy grande ( 2 GByte) dividiéndolas en diferentes partes. Esto
permite a las sentencias SQL manejar las distintas particiones y no la Tabla al
completo o el índice.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 81/226
75
Tablas 5
El poder particionar, también nos permite asignar las particiones a más de un Tablespacesdistinto, por lo que podemos:
− Reducir la posibilidad de corrupción de datos, por tratarse de tamaños muy grandes.
− Podemos hacer copias de seguridad de las particiones de forma individual.
− Reducir o dividir la carga de E/S si asignamos esas particiones en unidades físicas de
disco distintas.
El particionamiento, podemos hacerlo por bloques de filas, según los rangos de valores
de columna especificados.
IMPORTANTE: Si una Tabla posee un tipo de dato LONG o LONG RAW, NO podremos
particionarla. En su lugar, a la hora de diseñar la tabla, definimos el campo del tipo LOB.
Aunque el “Asistente” sólo nos permite particionar por un solo campo, la Tabla puede ser
particionada por más de un campo.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 82/226
76
Tablas 5
Nota.- El particionamiento sólo es permitido en versiones superiores a Oracle 8.
Una forma de ver si nuestro SGBD Oracle lo permite es abriendo la aplicación SQL*Plus:
− Paso 10.- Si no hemos especificado el particionamiento, pasamos directamente a la
ventana “Paso 13 de 13”, la cual nos muestra la sentencia SQL de creación de la
Tabla.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 83/226
77
Tablas 5
Si hubiésemos seleccionado la partición, los pasos siguientes antes de llegar al “Paso 13 de
13” nos solicitan información para definir el particionaminto.
Una vez pulsado el botón “Terminar” en la ventana “Paso 13 de 13”, nos debe aparecer la
ventana de confirmación indicando que la Tabla ha sido creada.
Si observamos en el apartado de “Esquema” de la consola del Oracle Enterprise Manager,
podemos ver como dentro de la carpeta “Tablas”, aparece la tabla creada, “VEHICULOS”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 84/226
78
Tablas 5
La sintaxis de creación de la TABLA mediante sentencia SQL es:
CREATE TABLE "USR_PRUEBA"."VEHICULOS"
("MATRICULA" VARCHAR2(15) NOT NULL,
"MODELO" VARCHAR2(15) NOT NULL,
"COLOR" VARCHAR2(10),
"CILINDRADA" NUMBER(4) NOT NULL,
"FECHA_COMPRA" DATE NOT NULL,
"PRECIO_VENTA" NUMBER(7, 2) NOT NULL,
CONSTRAINT "PK_VEHICULO" PRIMARY KEY("MATRICULA"),
CHECK(MATRICULA=UPPER(MATRICULA)),
CHECK(MODELO IN ('OPEL', 'SEAT','RENAULT','MERCEDES','AUDI')))
TABLESPACE "PRUEBA_DEFAULT";
Ahora bien, Oracle nos proporciona una utilidad para obtener la sentencia DDL de un objeto.
Para obtener el de la tabla creada, sólo tendremos que posicionarnos en el objeto desde la
consola del OEM y hacer clic derecho. Del menú que aparece, seleccionamos la opción
“Mostrar DDL de Objeto....”
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 85/226
79
Tablas 5
Oracle nos muestra una ventana con el código SQL, PERO mucho más detallado, que el
mostrado por el asistente de creación de tablas.
Organizando la sentencia, ésta quedaría:
CREATE TABLE "USR_PRUEBA"."VEHICULOS"
("MATRICULA" VARCHAR2(15 byte) NOT NULL,
"MODELO" VARCHAR2(15 byte) NOT NULL,
"COLOR" VARCHAR2(10 byte),
"CILINDRADA" NUMBER(4) NOT NULL,
"FECHA_COMPRA" DATE NOT NULL,"PRECIO_VENTA" NUMBER(7, 2) NOT NULL,
CONSTRAINT "PK_VEHICULO" PRIMARY KEY("MATRICULA") USING INDEX
TABLESPACE "PRUEBA_DEFAULT"
STORAGE (
INITIAL 64K
NEXT 0K
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 86/226
80
Tablas 5
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0)
PCTFREE 10 INITRANS 2 MAXTRANS 255,
CONSTRAINT "SYS_C003038" CHECK(MATRICULA=UPPER(MATRICULA)),
CONSTRAINT "SYS_C003039" CHECK(MODELO IN ('OPEL', 'SEAT',
'RENAULT','MERCEDES','AUDI')))
TABLESPACE "PRUEBA_DEFAULT"
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0)
LOGGING
En esta sentencia, podemos distinguir:
− Los valores de los campos VARCHAR2, se especifican en byte.
− La clave primaria es usada como índice.
− Se detallan los valores de almacenamiento de la TABLA, concretamente de las
extensiones:
STORAGE (INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0)
MUY IMPORTANTE:
En los TABLESPACES “gestionados localmente”, el tablespaces se encarga de
gestionar su propio espacio manteniendo un mapa de bits en cada DATAFILES de los
bloques libres y ocupados del DataFiles, por lo que cada vez que una extensión se
libera o asigna para un nuevo uso, Oracle actualizará dicho mapa de bits. En este
sentido conviene aclara que dichas extensiones no necesitan ser agrupadas
manualmente ya que Oracle las determinará automáticamente.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 87/226
81
Tablas 5
1. INITIAL - Valor de la primera Extensión del objeto (Tabla). En este caso es
tamaño de 8 bloques de 8Kbyte cada uno.
2. NEXT.- Su valor es almacenado en el “Diccionario de Datos” y se utiliza para la
creación de las nuevas EXTENSIONES de la Tabla. Cualquier cambio en este valor
sólo afecta a las nuevas extensiones.
3. MINEXTENTS.- Mínimo número de extensiones que debe presentar una tabla, es
decir, número mínimo de extensiones que debe tener un segmento.
4. MAXEXTENTS.- Número máximo de extensiones que puede presentar una Tabla.
Podemos asignarles un valor o indicar que es ilimitado.
Si es ILIMITADO, permite crear un número de extensiones que sólo está limitada
por la cantidad de espacio contiguo existente en el tablespaces.
Si se indica un VALOR, este es el número máximo de extensiones. Por defecto, si
no se especifica nada el valor asignado dependerá del bloque de datos por defecto.
5. PCTINCREASE.- Su valor se almacena en el “Diccionario de Datos”, y es usado
por Oracle para calcular el tamaño de futuras extensiones creadas a partir de laanterior que se creó. Valor en porcentaje.
Si es distinto de 0, el proceso en segundo plano SMON realizará agrupaciones
periódicas de extensiones libres contiguas.
Su valor es 0, ya que hemos creado un TABLESPACES “Gestionado localmente”,
por lo que no actuará el proceso SMON.
− Además de los parámetros de almacenamiento de extensiones, cada tabla posee unos
parámetros de bloques que controlan su utilización.
Estos valores, pueden ser asignados durante la creación de la tabla o posteriormente,
con el objetivo de mejorar la utilización del espacio y minimizar la migración de filas
entre los bloques de la tabla.
Dependiendo de dicho parámetro, las modificaciones pueden afectar a todos los
bloques o sólo a los nuevos bloques.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 88/226
82
Tablas 5
Un Bloque o Data Block se compone de las siguientes partes:
• Cabecera de Bloque.- Contiene datos relativos al bloque, como la dirección del
bloque y el tipo de segmento al que pertenece (Tabla, índice o rollback).
• Directorio de Tablas.- Guarda información de las Tablas que tienen datos en el
bloque.
• Directorio de Filas.- Guarda información sobre las filas de las tablas que se
encuentran en ese momento en el bloque. Esta información está compuesta por la
dirección de la fila en la zona “Datos de Filas”, en la cuál Oracle debe localizar los
Datos.
Al espacio ocupado por la “Cabecera de Bloque”, “Directorio de Tablas” y
“Directorio de Filas” se le denomina “overhead”.
• Espacio Libre.- Zona reservada para la inserción de nuevas filas en el Bloque o
para las actualizaciones que requieran más tamaño del que disponían
originalmente a cuando fueron insertados. Este es el caso, por ejemplo, de las
columnas de tipo Varchar2.
• Datos de Filas o Espacio de Datos.- Almacena las filas de datos de las Tablas,
Índices y Segmentos de Rollback, es decir, los datos propiamente dichos.
Los parámetros que afectan al bloque son:
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
1. PCTFREE.- Su valor indica el PORCENTAJE de espacio mínimo libre reservado en
cada bloque de datos para ampliaciones resultantes de actualizaciones e
inserciones de filas en el bloque de datos. Se establece al crear la tabla o el índice.
Por defecto 10 %, pudiendo asignar un valor entre 0 y 99.
Por ejemplo: Si ponemos su valor a 20 al crear la tabla, cuando insertemos en la
tabla y el espacio libre en el bloque baje de este 20%, no se podrá insertar más en
ese bloque, dejando el espacio sobrante para ampliaciones de actualizaciones.
El valor de este parámetro solo afecta a las futuras inserciones.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 89/226
83
Tablas 5
2. PCTUSED.- En este parámetro, se especifica la cantidad de espacio libre que debe
haber en el bloque para poder insertar en él. Su valor afecta a todos los bloques.
Su valor por defecto es 40 %.
Por ejemplo:
ALTER TABLE "USR_PRUEBA"."VEHICULOS" PCTUSED 40;
Esto quiere decir que en un bloque NO se pueden insertar nuevas filas de datos,
hasta que el espacio ocupado por las filas de ese bloque no baje del 40 %.
Nota.- En datos del tipo índice, no tiene utilidad este parámetro (PCTUSED), por
la propia estructura interna de estos datos. (Árbol binario).
Las vistas del Diccionario de Datos, asociadas al usuario SYS que nos permiten
saber el valor de dicho parámetro son “dba_tables” y “dba_indexes”.
Por ejemplo:
select TABLE_NAME,PCT_FREE,PCT_USED
from dba_tables
where OWNER='USR_PRUEBA';
Otro aspecto a tener muy en cuenta, es el ENCADENAMIENTO y LA MIGRACIÓN
DE FILAS.
− Encadenamiento de Filas (Chained row).- Sucede cuando los datos de una
Fila no caben en un sólo bloque y Oracle debe distribuirlos en más de un
bloque reservado para la extensión.
Esto sucede con columnas del tipo de datos Long, Long Raw, CLOB, etc.
− Migración de Filas.- Sucede cuando se realizan actualizaciones en columnas
de datos de tipo variable, por ejemplo Varchar2. Si no tenemos espacio
suficiente para la actualización, valor del PCTFREE, Oracle migra toda la fila a
otro bloque con capacidad libre.
Estos dos fenómenos, reducen considerablemente el RENDIMIENTO de Oracle a
la hora de manejar y manipular los datos.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 90/226
84
Tablas 5
¿Cómo DETECTAR estos fenómenos?
Para ello, usamos la sentencia SQL:
ANALYZE TABLE [esquema].tabla
{COMPUTE STATISTICS | ESTIMATE STATISTICS [SAMPLE interger {
ROW | PERCENT } ] };
− COMPUTE STATISTICS.- Nos proporciona información estadística,
basándonos en un recorrido completo de la tabla.
− ESTIMATE STATISTICS.- Genera información estadística, basada en un
número de filas (SAMPLE interger ROW) de la tabla, por defecto 1064, o en
un porcentaje de filas (SAMPLE interger PERCENT).
La información a analizar o a estudiar, se guarda en la comuna CHAIN_CNT de la
vista DBA_TABLES, del Diccionario de Datos. Para consultar dicho valor usaremos
la sentencia SQL:
SELECT chain_cnt FROM dba_tables
WHERE table_name=’tabla’ AND owner=’usuario_propietario’;
Si observamos que su valor es grande, necesitaremos reorganizar la tabla:
TRUNCATE TABLE [esquema.] tabla
[ { DROP | REUSE } STORAGE ];
Por defecto, la opción es DROP que borra todas las filas de la tabla y libera el
espacio ocupado por la tabla, pudiendo ser usado por otra tabla.
− REUSE.- Elimina las filas de las tablas y conserva ese espacio para que se
puedan seguir almacenando datos en la tabla.
Hay que tener mucho cuidado con este tipo de sentencia DDL, ya que no genera
segmento de Rollback, por lo que no existe la posibilidad de deshacer.
También, podemos chequear la tabla para comprobar si existen bloques corruptos.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 91/226
85
Tablas 5
ANALIZE TABLE [esquema.] tabla
VALIDATE STRUCTURE [CASCADE];
También, podemos utilizar el paquete predefinido de Oracle, DBMS_UTILITY con el
procedimiento ANALYZE_SCHEMA
(esquema VARCHAR2
método VARCHAR2,
filas_estimadas NUMBER DEFAULT NULL,
porcentaje_estimado NUMBER DEFAULT NULL);
Dónde:
− esquema.- Nombre del esquema que se analiza.
−
método.- Puede ser NULL o ESTIMATE. Si es ESTIMATE, entonces
“filas_estimadas” o “porcentaje_estimado” debe ser distinto de cero.
− filas_estimadas.- Número de filas a estimar.
− porcentaje_estimado.- Porcentaje a estimar, si se ha especificado un valor en
“filas_estimadas”, este parámetro se ignora.
Este paquete predefinido es propiedad del usuario SYS. Para poder ser usado por
otro usuario, debe tener el privilegio de EXECUTE. El procedimiento
ANALYZE_SCHEMA, analiza todas las tablas, agrupaciones e índices del esquema
especificado.
Ejemplo:
execute DBMS_UTILITY.ANALYZE_SCHEMA(‘USR_PRUEBA’,’ESTIMATE’,0,40);
3. INITRANS.- Número inicial de entrada de la transacción reservadas en cada
bloque de datos asociado al objeto. Sus posibles valores, van desde 1 a 255, por
defecto es 1 y se recomienda no cambiarlo.
Cada transacción que actualiza un bloque, necesita una entrada de transacción en
el bloque. Este valor asegura un mínimo de transacciones concurrentes que pueden
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 92/226
86
Tablas 5
actualizar el bloque y ayuda a evitar la sobrecarga de reserva dinámica de espacio
para una entrada de transacción.
4. MAXTRANS.- Número máximo de transacciones simultáneas o concurrentes que
pueden actualizar un bloque de datos asociados al objeto. Su valor puede ir desde
1 a 255, y es calculado en función del tamaño del bloque. El valor asignado se
recomienda no cambiarlo.
Por tanto, si el número de transacciones concurrentes que actualizan un bloque
superan el valor especificado en el parámetro INITRANS, Oracle reserva espacio
dinámicamente para entradas de transacciones en el bloque hasta que se
sobrepase el valor del parámetro MAXTRANS, o hasta que el bloque no tiene másespacio libre.
Todos los parámetros anteriores, pueden ser modificados usando la pestaña
“Almacenamiento” de la “Tabla” dentro del “Esquema” correspondiente, en el Enterprise
Manager Console.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 93/226
87
Tablas 5
En esta ventana, aparece la opción de “Conjunto de Buffers” cuya funcionalidad afecta al
objeto del esquema, es decir, mantiene en memoria caché todos los bloques del objeto.
Los posibles valores son Default, Keep y Recycle.
− Keep.- Mantiene siempre el objeto en memoria para evitar operaciones de
Entrada/Salida a disco.
− Recycle.- Elimina los bloques de la memoria en el momento que no son necesarios,
evitando que un objeto ocupe espacio de caché innecesariamente.
Esta ventana presenta la opción de CÁLCULO AUTOMÁTICO. En la cuál se nos solicita lasiguiente información:
− Ratio de Crecimiento.- Es el “Ratio de crecimiento” de la tabla esperado en
filas/unidad de tiempo (día, semana, mes o año). Siendo el crecimiento mayor en
“día”.
Oracle, utiliza el ratio de crecimiento para determinar el tamaño (en bytes) de la
siguiente extensión (NEXT). Por tanto, el valor mínimo es un bloque (8192 Byte =
8Kbyte).
Cuanto mayor sea la relación filas/día, mayor será el valor de NEXT.
− Actividad de Actualización. (PCTFREE). Determina el porcentaje de espacio que
queda libre para las actualizaciones en cada uno de los bloques de datos del índice.
• Superior.- Define el porcentaje de cada bloque de datos para actualizaciones de
las filas existentes.
Por ejemplo: Para un Ratio de 100 filas / día, tendríamos un NEXT de 40960 Byte
(5 bloques de 8KByte), un PCTFREE del 0 % y un PCTUSED del 60 %.
• Tamaño de Fila de Aumento por Actualización.- Disponible si la Actividad de
Actualización es alta, reserva el 20 por ciento de cada bloque de datos para
actualizaciones de las filas existentes, incluidas las supresiones.
Para el ejemplo anterior, obtendríamos un PCTFREE del 20 %.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 94/226
88
Tablas 5
• Inferior o Ninguno.- Reserva el 5 por ciento de cada bloque de datos para
actualizaciones de las filas existentes, es decir, un PCTFREE del 5 %.
− Actividad de Inserción. (PCTUSED). Determina el porcentaje de espacio que queda
libre para las inserciones en cada uno de los bloques de datos.
• Superior.- Define el porcentaje de espacio reservado para las inserciones en un 40
por ciento. Si incluimos las supresiones, dicho valor aumenta un 20 %, por tanto,
PCTUSED vale 60%.
• Inferior o Ninguno: Define el porcentaje de espacio reservado para las
inserciones siempre en un 60 %.
Algunos ejemplos de sentencias SQL usadas para modificar dichos valores de
almacenamiento serían:
Ejemplo 1:
ALTER TABLE "USR_PRUEBA"."VEHICULOS"
STORAGE ( NEXT 24K MAXEXTENTS UNLIMITED PCTINCREASE 10);
Ejemplo 2:
ALTER TABLE "USR_PRUEBA"."VEHICULOS" MAXTRANS 200;
Ejemplo 3:
ALTER TABLE "USR_PRUEBA"."VEHICULOS" PCTFREE 20 PCTUSED 40
INITRANS 2
STORAGE ( NEXT 40960 PCTINCREASE 0);
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 95/226
89
Tablas 5
− Las TABLAS son la estructura lógica de toda aplicación de B.D.
− Los tipos de DATOS usados con mayor frecuencia son: Number, Varchar2 y
Date.
− Los USUARIOS están asociados a los ESQUEMAS.
− Las TABLAS se pueden crear usando sentencias SQL o la herramienta gráfica.
− Al crear el TABLESPACE, se aconseja que éste sea “gestionado localmente”.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 96/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 97/226
91
Operaciones
sobre tablas 6
6.1. INSERCIONES DE DATOS ............................................................93
6.2. CONSULTAS DE DATOS ...............................................................96
6.3. ACTUALIZACIÓN Y BORRADO DE DATOS ....................................98
6.4. LA CLAUSULA “WHERE” ..............................................................99
6.5. RELACIONES ENTRE INSERT Y SELECT......................................101
6.6. LA VISTA V$NLS_PARAMETERS ................................................103
6.7. FUNCIONES PREDEFINIDAS......................................................105
6.7.1. Funciones de Carácter ...................................................1056.7.2. Funciones de Conversión...............................................107
6.7.3. Funciones Numéricas ....................................................113
6.7.4. Funciones de Fechas .....................................................114
6.7.5. Funciones de grupo .......................................................116
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 98/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 99/226
93
Operaciones
sobre tablas 6
6.1. INSERCIONES DE DATOS
Para insertar filas en una tabla, usaremos la sentencia SQL “INSERT” , cuya sintaxis es:
INSERT INTO [esquema.] tabla [ (columna1, columna2,....)]
VALUES (valor1, valor2, ....);
Ejemplo 1:
INSERT INTO vehiculos(matricula,modelo,color,cilindrada,fecha_compra,precio_venta)
VALUES (‘2037-BBA’, ‘RENAULT’,’Blanco’,1400,’10/01/01’,6010.50);
Igual a:
INSERT INTO vehiculos VALUES (‘2037-BBA’,
‘RENAULT’,’Blanco’,1400,’10/01/01’,6010.50);
Ejemplo 2:
INSERT INTO vehiculos VALUES (‘2235-AAB’, ‘SEAT’,null,1400,’14/03/01’,6650.50);
Igual a:
INSERT INTO
vehiculos(matricula,modelo,color,cilindrada,fecha_compra,precio_venta)
VALUES (‘2235-AAB’, ‘SEAT’,null,1400,’14/03/01’, 6650.50);
Igual a:
INSERT INTO vehiculos(matricula,modelo,cilindrada,fecha_compra,precio_venta)
VALUES (‘2235-AAB’, ‘SEAT’,1400,’14/03/01’, 6650.50);
Sin la columna COLOR, ya que es la única que en el proceso de creación se especificó que
pudiese no tomar valor.
Para ejecutar estas sentencias, Oracle nos proporciona dos herramientas:
SQL Plus
SQLPlus Worksheet
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 100/226
94
Operaciones
sobre tablas 6
Desde SQLPlus Worksheet, una vez conectado, sólo tendríamos que ejecutar la sentencia o
sentencias. Para ejecutar una sentencia, sólo tendremos que pulsar la tecla F5 o hacer clic
en el botón de “Ejecutar”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 101/226
95
Operaciones
sobre tablas 6
Otra posibilidad es tener las instrucciones SQL escritas en un fichero de texto con extensión
SQL y cargar dicho fichero en el Worksheet.
Ahora bien desde la versión 8i, Oracle a través de la herramienta gráfica “Oracle Enterprise
Console”, nos proporciona la introducción de datos. Sólo tendremos que posicionarnos en la
tabla y seleccionar, una vez pulsado el clic derecho, la opción “Ver/Editar Contenido...”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 102/226
96
Operaciones
sobre tablas 6
6.2. CONSULTAS DE DATOS
Para consultar datos de una tabla, hacemos uso de la sentencia SELECT, cuya sintaxis es:
SELECT columna1, columna2,.......
FROM [esquema.] tabla
[WHERE condiciones]
[ORDER BY columna1,columna2,.........]
[GROUP BY columna1,columna2,.........];
Podemos usar el carácter comodín * , para especificar todas las columnas o campos de la
tabla.
Ejemplo 1:
SELECT matricula,modelo,color,cilindrada,fecha_compra,precio_venta FROM vehiculos;
Igual a:
SELECT * FROM vehiculos;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 103/226
97
Operaciones
sobre tablas 6
Las cláusulas WHERE la estudiaremos en un apartado independiente.
Las columnas pueden tener alias, mostrándose este en lugar del nombre de la columna.
− ORDER BY. Esta cláusula es utilizada para mostrar el resultado de la consulta de
forma ordenada por la columna o columnas especificadas.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 104/226
98
Operaciones
sobre tablas 6
Ejemplo:
SELECT * FROM vehiculos ORDER BY modelo;
− GROUP BY. Permite realizar consultas agrupando las columnas.
Ejemplo:
SELECT cilindrada FROM vehiculos group BY cilindrada;
6.3. ACTUALIZACIÓN Y BORRADO DE DATOS
Las actualizaciones o modificaciones de los datos de las tablas se llevan a cabo usando la
sentencia SQL UPDATE.
UPDATE [esquema.] tabla SET columna1=valor_nuevo1, columna2=valor_nuevo2, ......
[WHERE condiciones];
Ejemplo 1:
UPDATE vehiculos SET modelo=’ OPEL’, fecha_compra=’20/03/01’ WHERE matricula=’
2235-AAB’;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 105/226
99
Operaciones
sobre tablas 6
Para borrar los datos de una tabla usaremos la sentencia SQL DELETE, cuya sintaxis es:
DELETE [FROM] [esquema.] tabla
[WHERE condiciones];
Si no se especificase la cláusula WHERE, borraríamos todas las filas de la tabla.
Ejemplo 1:
DELETE FROM vehiculos WHERE matricula=’ 2235-AAB’;
Ejemplo 2:
DELETE FROM vehiculos;
Igual a:
DELETE vehiculos;
6.4. LA CLAUSULA “WHERE”
Esta cláusula forma parte de las instrucciones o sentencias SELECT, UPDATE y DELETE. Su
finalidad es aplicar la acción de las sentencias anteriores a un conjunto de filas que cumplan
la condición o condiciones especificadas en la cláusula WHERE.
Para especificar más de una condición en la cláusula WHERE, usaremos los operadores
booleanos AND, OR y NOT.
Dentro de la propia condición, podemos usar los operadores:
= , > , < , >= , <= , <>, != , LIKE, IN, BETWEEN, IS NULL.
− LIKE. Este operador se usa para comparación con patrones en cadena de caracteres.
El carácter % equivale a un conjunto de caracteres y el carácter _ equivale a un sólo
carácter.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 106/226
100
Operaciones
sobre tablas 6
Ejemplo:
SELECT * FROM vehiculos WHERE matricula LIKE '%AAB';
− IN. Este operador compara la columna con un conjunto de posibles valores.
Ejemplo:
SELECT * FROM vehiculos WHERE modelo IN ('RENAULT','SEAT')
− BETWEEN. Este operador usa los operadores >= y <= en uno sólo.
Ejemplo:
SELECT * FROM vehiculos WHERE cilindrada BETWEEN 1000 and 1400;
SELECT * FROM vehiculos WHERE precio_venta BETWEEN 6010.40 and 6500.50;
− IS NULL. Para consultar filas dónde existan columnas con valor NULL, usamos IS
NULL ya que la condición = NULL, no daría un resultado correcto.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 107/226
101
Operaciones
sobre tablas 6
6.5. RELACIONES ENTRE INSERT Y SELECT
Podemos realizar el INSERT conjuntamente con SELECT para realizar de esta forma copias
masivas de unas tablas a otras dónde las columnas a copiar sean idénticas en tipos de datos.
INSERT INTO vehiculos_copia SELECT modelo,precio_venta FROM VEHICULOS;
Otra forma de hacer una copia de una tabla es crear la tabla y seleccionar los datos en el
proceso de creación.
CREATE TABLE nueva_tabla AS SELECT * FROM [esquema.] tabla;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 108/226
102
Operaciones
sobre tablas 6
La tabla creada contiene la misma estructura de datos que la tabla origen, PERO NO las
restricciones.
Desde la herramienta “Enterprise Manager Console”, podemos hacer una copia de la tabla
usando el botón “Crear como...”. Esta opción nos permite crear una copia de la tabla tanto
de datos como de restricciones.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 109/226
103
Operaciones
sobre tablas 6
6.6. LA VISTA V$NLS_PARAMETERS
La vista v$nls_parameters, me informa de los valores de NLS_LANGUAGE,
NLS_TERRITORY, etc., establecidos en la B.D.
Ejemplo:
Select * from v$nls_parameters;
Valores devueltos por la vista en una B.D. Oracle 8.05:
PARAMETER VALUE
NLS_LANGUAGE SPANISH
NLS_TERRITORY SPAIN
NLS_CURRENCY ¿
NLS_ISO_CURRENCY SPAIN
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE SPANISH
NLS_CHARACTERSET WE8ISO8859P1
NLS_SORT SPANISH
NLS_NCHAR_CHARACTERSET WE8ISO8859P1
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 110/226
104
Operaciones
sobre tablas 6
11 filas seleccionadas.
Valores devueltos por la vista en una B.D. Oracle 9i:
PARAMETER VALUE
NLS_LANGUAGE
SPANISH
NLS_TERRITORY
SPAIN
NLS_CURRENCY €
NLS_ISO_CURRENCY SPAIN
NLS_NUMERIC_CHARACTERS
,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT
DD/MM/RR
NLS_DATE_LANGUAGE
SPANISH
NLS_CHARACTERSET WE8MSWIN1252
NLS_SORT
SPANISH
NLS_TIME_FORMAT
HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT
DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT
DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP
BINARY
NLS_LENGTH_SEMANTICS
BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 filas seleccionadas.
Ver la lista de lenguajes y territorios soportados por Oracle 9i en el Anexo A.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 111/226
105
Operaciones
sobre tablas 6
6.7. FUNCIONES PREDEFINIDAS
El lenguaje SQL, nos proporciona funciones SQL predefinidas. Dichas funciones las podemos
agrupar en diferentes categorías:
6.7.1. Funciones de Carácter
− CHR(x) .- Devuelve el carácter correspondiente al número “x” del conjunto de
caracteres.
Select chr(123) from dual;
− CONCAT(cadena1, cadena2).- Devuelve la unión de la cadena1 con la cadena2, es
idéntica al operador ||.
− INITCAP(cadena).- Convierte a mayúscula la primera letra de la cadena y a
minúscula el resto. Si en la cadena existen palabras separadas por blancos, también el
primer carácter es convertido a mayúscula.
− LOWER(cadena) y UPPER(cadena).- Devuelven la cadena en minúscula (Lower) y
mayúscula (Upper) respectivamente.
− LTRIN(cadena1, [cadena2]) y RTRIN(cadena1, [cadena2]).- Devuelve la
cadena1, quitando los caracteres por la izquierda y derecha respectivamente
especificados en cadena2. Si no se especifica “cadena2”, el valor tomado es el espacio
en blanco.
− REPLACE(cadena, cadena_a_buscar, [nuevo_valor]).- Esta función reemplaza en
cadena la cadena_a_buscar por el valor dado en nuevo_valor. Si nuevo_valor se
omite, en cadena se reemplazan todos los valores existentes que coincidan con
nuevo_valor, por blancos.
− RPAD(cadena1,x, [cadena2]).- Devuelve la cadena1, rellena por la derecha con los
valores de la cadena2, hasta completar el número de caracteres especificados en x. Si
el número de caracteres de cadena1 y cadena2 es menor a x, se repetirá la cadena2.
−
SUBSTR(cadena,a[,b]).- Devuelve una subcadena de la cadena dada, de b
caracteres de longitud desde la posición a.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 112/226
106
Operaciones
sobre tablas 6
Ejemplo:
− TRANSLATE(cadena, valor_inicial, valor_final).- Esta función reemplaza en
cadena los caracteres especificados en valor_inicial por valor_final.
Valor_final, no puede tomar el valor NULL.
Ejemplo:
Select TRANSLATE('ABCABC', 'B', 'X') Resultado from dual;
Resultado: AXCAXC
Las funciones anteriormente explicadas, devuelven cadenas de caracteres. Existen funciones
que reciben como argumento cadenas de caracteres pero devuelven un número. Algunas de
estas son:
− ASCII(cadena).- Devuelve el número correspondiente al carácter especificado en el
parámetro cadena.
Ejemplo: Select ASCII('&') Resultado from dual;
Resultado: 38
Si ejecutamos la sentencia Select CHR(38) Resultado from dual, mostrará el carácter
&.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 113/226
107
Operaciones
sobre tablas 6
− INSTR(cadena1, cadena2 [,a [,b] ] ).- Devuelve la posición dentro de la cadena1
dónde está contenida la cadena2. Por defecto se explora desde la izquierda
empezando en la posición a, si dicho valor es negativo, se comienza desde la derecha.
En b se especifica el número de la repetición dentro de cadena1. Si devuelve un cero
es que cadena2 no existe en cadena1.
Ejemplo:
−
LENGTH(cadena).- Devuelve la longitud de una cadena, incluido los espacios enblanco existentes en cualquier parte de la cadena.
6.7.2. Funciones de Conversión
Son usadas para realizar conversiones entre tipos de datos.
− CONVERT(cadena,conjunto_caracteres_destino[,conjunto_caracteres_fuente]
).- Convierte la cadena de caracteres desde el conjunto_caracteres_fuente al
conjunto_caracteres_destino. Si no se especifica elconjunto_caracteres_fuente, se toma por defecto el conjunto de caracteres
especificado en el proceso de instalación de la B.D.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 114/226
108
Operaciones
sobre tablas 6
Ejemplo:
Select CONVERT('€', 'WE8MSWIN1252') Resultado from dual;
Resultado: €
−
TO_CHAR(fechas [,formato [,NLS_DATE_LANGUAGE] ]).- Convierte la fecha auna cadena de caracteres del tipo varchar2.
El parámetro formato se usa para controlar el resultado de salida.
NLS_DATE_LANGUAGE se usa para el idioma de salida de los componentes de la
cadena devuelta (día y mes) y las abreviaturas de fecha (AM, PM, aC, dC). Los idiomas
soportados se especifican en el parámetro de configuración de Oracle denominado
NLS_LANGUAGE que en nuestro caso es AMERICAN. El valor se asigna de la siguiente
forma: ‘NLS_DATE_LANGUAGE=idioma’.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 115/226
109
Operaciones
sobre tablas 6
El parámetro formato puede tomar los siguientes valores:
“texto” Texto encerrado entre dobles comillas.
D Día de la semana (1-7).
DD Día del mes (1-31).
DDD Día del año (1-366).
DY Nombre Abreviado del día. (Las tres primeras letras)
DAY Nombre del día, rellenado con espacios en blanco hasta nueve
caracteres.
IW Número de Semana del Año.(1-52 ó 53)
W Semana del mes. (1-5)
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 116/226
110
Operaciones
sobre tablas 6
IYYY,IYY,IY,I Los cuatro, tres, dos o un dígito del año.
Y,YYYY Año con una coma en esa posición.
YEAR Año en letras.
HH,HH12,HH24 Horas especificadas de 1 a 12 ó de 0 a 23.
MI Minutos. (0-59)
MM Meses en número. (1-12)
MON Nombre abreviado del mes.
MONTH Nombre del mes, rellenado con espacios en blanco hasta nueve
caracteres.
PM, P.M. PostMeridian (Después de Medio día)
Q Trimestre del año. (1-4)
RM Meses en números romanos.
SS Segundos (0-59)
WW Semana del año (1-53). La primera semana comienza el primer día
del año hasta el séptimo día.
Ejemplos:
Select TO_CHAR (to_date('22/12/03'),' "Semana" IW "del Año 2003" ')
Resultado from dual;
RESULTADO
Semana 52 del Año 2003
− TO_CHAR(numero [,formato [,NLS_NUMERIC_ CHARACTERS
NLS_CURRENCY]]).- Convierte el valor de “numero” de tipo NUMBER a VARCHAR2.
El parámetro formato se usa para controlar el resultado de salida.
En NLS_NUMERIC_CHARACTERS =’’decimalmiles’’ especifica el separador
decimal y de miles (Grupo), entre dobles comillas simples.
En NLS_CURRENCY=’’moneda” se especifica la moneda. El valor por defecto de
este parámetro lo determina NLS_TERRITORY.
Ejemplo:
'NLS_NUMERIC_CHARACTERS=' ',.' ' NLS_CURRENCY=' 'Euros' ' '
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 117/226
111
Operaciones
sobre tablas 6
El parámetro formato puede tomar los siguientes valores:
9 Cada número 9 representa un dígito significativo del resultado.
0 Devuelve ceros por la izquierda.
FM Devuelve un valor sin blancos a la izquierda.
RM o rm Devuelve el número como número romano en mayúscula o minúscula
respectivamente.
EEEE Devuelve el valor utilizando notación científica.
D Devuelve un punto Decimal en la posición especificada.
G Devuelve un separador de grupo en la posición especificada.
. , Devuelve un punto o coma en la posición especificada
independientemente de G o D
L Devuelve el símbolo de Moneda Local en la posición especificada.
C Devuelve la moneda ISO. (EUR)
PR Números negativos entre < >
S Signo + o - por la izquierda.
Ejemplos:
• Select TO_CHAR (1457,'09999') from dual;
Resultado: 01457
• Select TO_CHAR (15,'RM') from dual;
Resultado: XV
• Select TO_CHAR (1000000,'9EEEE') from dual;
Resultado: 1E+06
• Select TO_CHAR (1000000,'9G999G999D99') from dual;
Resultado: 1.000.000,00
• Select TO_CHAR (1000000.78,'9,999,999.99') from dual;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 118/226
112
Operaciones
sobre tablas 6
Resultado: 1,000,000.78
•
Select TO_CHAR (10000.78,'L9G999G999D99') from dual;
Resultado: €10.000,78
•
Select TO_CHAR (10000.78,'C9G999G999D99') from dual;
Resultado: EUR10.000,78
• Select TO_CHAR (-10000.78,'S9G999G999D99') from dual;
Resultado: -10.000,78
− TO_DATE (cadena [,formato [,NLS_DATE_LANGUAGE]]).- Convierte una cadena
VARVHAR2 o CHAR en un valor de tipo DATE. El formato es una cadena del tipo de
formato especificado en TO_CHAR(fechas [,formato [,NLS_DATE_LANGUAGE] ]).
TO_CHAR y TO_DATE tienes funciones inversas.
Ejemplo:
Select TO_DATE('Mar 14 Ene','DY DD Mon') from dual;
Resultado: 14/01/03
− TO_NUMBER(cadena [,formato[,NLS_DATE_LANGUAGE]]).- Convierte la cadena
CHAR o VACHAR2 a un tipo NUMBER. Si se especifica un formato, la cadena debe ser
igual al formato especificado.
Ejemplo:
Select TO_NUMBER ('02.78','09.99') from dual;
Resultado: 2,78
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 119/226
113
Operaciones
sobre tablas 6
6.7.3. Funciones Numéricas
Estas funciones permiten argumentos de tipo NUMBER y devuelven valores de tipo NUMBER.
Algunas de estas funciones son:
− ABS(x).- Devuelve el valor absoluto de x.
− MOD(x,y).- Devuelve el resto de dividir x entre y. Si y es cero, devuelve x.
− POWER(x,y).- Devuelve x elevado a y. No necesitan ser números enteros positivos
ni x ni y, salvo si x es negativo, entonces y debe ser un número entero.
− ROUND(x, [,y]).- Devuelve el valor de x redondeado a y posiciones a partir del
número decimal.
Ejermplo:
Select ROUND(435.56789,4) from dual;
Resultado: 435,5679
− TRUNC(x, [,y]).- Devuelve el valor de x redondeado a y posiciones a partir del
número decimal.
Ejermplo:
Select TRUNC(-123.556), TRUNC(-123.356,2) from dual;
Resultado:
TRUNC(-123.556) es -123
TRUNC(-123.356,2) es -123,35
− SIGN(x).- Devuelve 1 si x es positivo, 0 si x es cero y –1 si x es negativo.
−
SQRT(x).- Devuelve la raíz cuadrada de x.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 120/226
114
Operaciones
sobre tablas 6
6.7.4. Funciones de Fechas
Las funciones de fecha admiten parámetros del tipo DATE y devuelven valores del tipo DATE,
salvo la función MONTHS_BETWEEN que devuelve un valor de NUMBER. Algunas de estas
funciones son:
− SYSDATE.- Devuelve la fecha y hora del sistema, no admite argumentos.
Ejemplo:
Select to_char(SYSDATE,'dd/mm/yy hh24:mi:ss') from dual;
Resultado:08/01/03 23:11:18
− ADD_MONTHS(fecha,x).- Devuelve la fecha más x meses. El valor de x debe ser
un número entero.
− LAST_DAY(fecha).- Devuelve la fecha del último día especificada en fecha. Esta
función es útil para calcular el número de días que faltan para completar el mes actual.
Ejemplos:
Select LAST_DAY('08-01-03') from dual;
Resultado: 31/01/03
Select LAST_DAY('08-01-03') - TO_DATE('08-01-03') from dual;
Resultado: 23
Select LAST_DAY('08-01-03') - TO_DATE(sysdate) from dual;
Resultado: 23
− MONTHS_BETWEEN(fecha1,fecha2).- Devuelve el número de meses existentes
entre ambas fechas. Si los días son los mismos entre ambas fechas, el resultado es un
número entero, si son días distintos se mostrará una parte decimal.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 121/226
115
Operaciones
sobre tablas 6
Ejemplos:
Select MONTHS_BETWEEN('01/01/03','01/01/02') from dual;
Resultado: 12
Select MONTHS_BETWEEN('02/01/03','01/01/02') from dual;
Resultado: 12,0322581
Select MONTHS_BETWEEN('31/01/03','01/01/02') from dual;
Resultado: 12,9677419
− NEW_TIME(fecha,franja_horaria_1, franja_horaria_2).- Devuelve la fecha y
hora de la franja_horaria_2 correspondiente a la fecha especificada en fecha de la
franja_horaria_1. Los valores de los parámetros franja son cadenas de caracteres,
cuyos valores pueden ser:
Franjas Horarias:
AST (Atlantic Standard Time) ( 4 horas menos a la GMT) (Horario Estándar del
Atlántico)
ADT (Atlantic Daylight Time) ( 3 horas menos a la GMT) (Horario de Verano del
Atlántico)
BST (Bering Standart Time)
BDT (Bering Daylight Time)
CST (Central Standart Time) (Horario Estándar de la zona Central)
CDT (Central Daylight Time)
EST (Eastern Standart Time) (Horario Estándar de la costa Este)
EDT (Eastern Daylight Time)
GMT (Greenwich Mean Time) (Horario del meridiano de Greenwich)
HST (Alaska-Hawaii Standart Time)
HDT (Alaska-Hawaii Daylight Time)
MST (Mountain Standart Time) (Horario Estándar de las Rocosas)
MDT (Mountain Daylight Time) (Horario de Verano de las Rocosas)
NST (Newfoundland Standart Time) (Horario Estándar de Terranova)
PST (Pacific Standart Time)
PDT (Pacific Daylight Time)
YST (Yukon Standart Time)
YDT (Yukon Daylight Time)
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 122/226
116
Operaciones
sobre tablas 6
Ejemplos:
Select to_char(NEW_TIME(TO_DATE('8/01/02 22:00:00','DD/MM/YY
HH24:MI:SS'),'GMT','AST')
,'DD/MM/YY HH24:MI:SS') from dual;
Resultado:
08/01/02 18:00:00
Podemos consultar la Página Web:
http://www.barceloviajes.com/franjah/horamundial.asp
− NEXT_DAY(fecha,día_de_la_semana).- Devuelve la fecha del día especificado en
día_de_la_semana posterior a la fecha indicada en fecha.
Ejemplo:
Select NEXT_DAY('8/01/03','Martes') from dual;
Resultado: 14/01/03
6.7.5. Funciones de grupo
Estas funciones devuelven un sólo resultado aplicándose sobre un conjunto de filas de datos.
Son usadas conjuntamente con la cláusula GROUP BY.
Las cláusulas DISTINCT y ALL pueden usarse con algunas de estas funciones de grupo.
Al usar DISTINTC la función sólo toma aquellos valores de la consulta que sean distintos
entre sí.
Al usar ALL la función toma todos los valores devueltos por la consulta, esta es la cláusula
por defecto.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 123/226
117
Operaciones
sobre tablas 6
Partiendo de la tabla EMP del usuario SCOTT, es decir:
Tendríamos:
Ejemplo:
• Select DISTINCT deptno from emp;
Resultado:
DEPTNO
10
20
30
• Select deptno from emp GROUP BY deptno;
Resultado:
DEPTNO
10
20
30
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 124/226
118
Operaciones
sobre tablas 6
− AVG( [ DISTINCT || ALL] columna ).- Devuelve la media de los valores de las
columnas agrupadas.
Ejemplo:
• Select avg(sal) from emp;
Resultado: 2073,21429
• Select avg(sal) Media,deptno from emp group by deptno;
Resultado:
Media DEPTNO
2916,66667 10
2175 20
1566,66667 30
• Select avg(distinct sal),deptno from emp group by deptno;
Si existe algún valor para la columna “sal” repetido, éste no se tiene en
cuenta.
Resultado:
Media DEPTNO
2916,66667 10
1968,75 20
1630 30
− COUNT( * || [ DISTINCT || ALL] columna ).- Número de filas de la consulta.
Ejemplo:
• Select count(*) Total_Filas from emp;
Resultado: 14
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 125/226
119
Operaciones
sobre tablas 6
•
Select count( deptno) Total_Filas from emp where deptno=20;
Resultado: 5
• Select count(distinct deptno) Total_Filas from emp where deptno=20;
Resultado: 1
− MAX(columna ).- Devuelve el valor máximo de la columna consultada.
• Select max(sal) from emp;
Resultado: 5000
•
Select max(sal),deptno from emp group by deptno;
Resultado:
MAX(SAL) DEPTNO
5000 10
3000 20
2850 30
− MAX(columna ).- Devuelve el valor mínimo de la columna consultada.
• Select min(sal) from emp;
Resultado: 800
• Select min(sal),deptno from emp group by deptno;
Resultado:
MIN(SAL) DEPTNO
1300 10
800 20
950 30
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 126/226
120
Operaciones
sobre tablas 6
− SUM([ DISTINCT || ALL] columna ).- Suma de las columnas.
•
Select sum(sal) from emp;
Resultado: 29025
•
Select sum(sal),deptno from emp group by deptno;
Resultado:
SUM(SAL) DEPTNO
8750 10
10875 20
9400 30
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 127/226
121
Operaciones
sobre tablas 6
− Oracle usa sentencias INSERT para las inserciones de datos.
−
Sentencias UPDATE para las actualizaciones de los datos.
−
DELETE para borrado de datos.
− Oracle nos proporciona una serie de funciones predefinidas para el manejos
de los datos.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 128/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 129/226
123
Consultas 7
7.1. CLÁUSULA HAVING...................................................................125
7.2. OPERACIONES DE CONJUNTOS EN CONSULTAS SQL..................126
7.3. UNIONES INTERNAS Y EXTERNAS.............................................127
7.4. SUBCONSULTAS........................................................................133
7.4.1. Select como parte de una cláusula Where o Having .......133
7.4.2. Select como una tabla ...................................................135
7.4.3. Select como una columna a mostrar ..............................135
7.4.4. Select como parte de una cláusula Having .....................1377.5. CONSULTAS RECURSIVAS .........................................................138
7.6. CONSULTAS CON LA FUNCIÓN DECODE.....................................140
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 130/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 131/226
125
Consultas 7
7.1. CLÁUSULA HAVING
La cláusula HAVING se usa conjuntamente con la cláusula GROUP BY. Actúa igual que una
cláusula WHERE, salvo que la condición se aplica al resultado de la agrupación. Sería:
Select campo1, count(campo2) from tabla
Where campo1 = condicción
Groub by campo1
Having count(campo2) = condición;
En este tipo de sentencias, Oracle filtra los registros según la condición del where antes de
agrupar. Una vez filtrado agrupa, y después aplica la condición de la cláusula having.
Veamos un ejemplo aplicado a la tabla EMP (Empleados) del usuario Scott/tiger de Oracle.
Aquí, se solicitan números de los departamentos que posean más de dos empleados cuyo
puesto de trabajo sea “CLERK”. (Comercial).
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 132/226
126
Consultas 7
7.2. OPERACIONES DE CONJUNTOS EN CONSULTAS SQL
SQL, nos permite hacer uso de los operadores de conjuntos para obtener datos de más de
una tabla. Estos son UNION, UNION ALL, INTERSECT y MINUS.
A la hora de hacer uso de estos operadores, los campos o columnas implicadas deben ser del
mismo tipo de dato.
− UNION.- Al usar este operador obtenemos todas las filas de las tablas implicadas,
pero sin repetición de datos.
Por ejemplo, si partimos de una tabla A={1,2,3,4,5} y otra B={4,5,7,8,9} yrealizamos:
Select * from A
UNION
Select * from B;
El resultado es: 1,2,3,4,5,7,8,9.
−
UNION ALL.- Todas las filas de las tablas, incluyendo los valores repetidos. Para lastablas del ejemplo anterior tendríamos:
Select * from A
UNION ALL
Select * from B;
El resultado es: 1,2,3,4,5,4,5,7,8,9.
− INTERSECT.- Nos proporciona las filas comunes de las tablas especificadas.
Para el conjunto de valores de las tablas A y B anteriores obtendríamos:
Select * from A
INTERSECT
Select * from B;
El resultado es: 4,5.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 133/226
127
Consultas 7
− MINUS.- Este operador nos muestra todas las filas de la primera tabla salvo los que
sean comunes a la segunda tabla. Para los datos A={1,2,3,4,5} y B={4,5,7,8,9}
tendríamos:
Select * from A
MINUS
Select * from B;
El resultado es: 1,2,3.
7.3. UNIONES INTERNAS Y EXTERNAS
− Uniones Internas.- Son aquellas que combinan las filas de dos o más tablas usando
para ello campos o columnas de las tablas comunes, es decir, usando las columnas
Primery Key y Foreign Key.
Este tipo de uniones sólo devuelve las filas comunes entre ambas tablas, NUNCA
devuelve filas que no estén en una tabla y en otra no.
Un ejemplo aplicado a las tablas EMP y DEPT del usuario Scott/tiger sería la siguiente:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 134/226
128
Consultas 7
Con esta select obtendríamos los nombres de los empleados, sus puestos de trabajo y
el nombre del departamento al que pertenece.
− Uniones Externas.- Combina filas de tablas relacionadas entre sí por una o más
columnas comunes. La diferencia con las uniones internas, es que este tipo de uniones
puede devolver filas de una tabla que no existan en otra.
Para ello, usamos la simbología(+), en la parte de la condición de la cláusula where
de la tabla de la cual esperamos no obtener filas.
Por ejemplo si tuviésemos dos tablas CLIENTES y PEDIDOS y quisiéramos obtener
todos los datos de TODOS los clientes hayan o no realizado pedidos, el operador (+)
debe ir asociado a la tabla PEDIDOS.
Haciendo uso del usuario Scott/tiger, vamos a insertar un Departamento nuevo en la
tabla DEPT:
insert into dept values(99,'INFORMATICA','SEVILLA');
Si ejecutamos la consulta de la unión interna pero especificando la simbología (+) en
la columna asociada a la tabla EMP obtendremos todos los departamentos
existente tengan o no empleados.
Es decir:
select e.DEPTNO,e.ename,e.job,d.dname,d. DEPTNO
from emp e, dept d
where e. DEPTNO (+)=d. DEPTNO;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 135/226
129
Consultas 7
Ahora bien, el caso contrario:
select e.DEPTNO,e.ename,e.job,d.dname,d.DEPTNO
from emp e, dept d
where e.DEPTNO=d.DEPTNO (+) ;
Me debe mostrar todos los empleados existentes, tengan o no departamento
asociado.
Este caso nunca se dará ya que por la propia definición de la estructura de la tabla
empleado se ha impuesto una restricción FOREIGN KEY hacia el campo DEPTNO.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 136/226
130
Consultas 7
El resultado sería igual al de una consulta interna.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 137/226
131
Consultas 7
Estas UNIONES EXTERNAS son conocidas también como JOIN que pueden ser JOIN
“por la derecha” (RIGHT JOIN) o JOIN “por la izquierda” (LEFT JOIN), dependiendo de
lo que se desee obtener.
El equivalente a:
select e.DEPTNO,e.ename,e.job,d.dname,d. DEPTNO
from emp e, dept d
where e. DEPTNO (+)=d. DEPTNO;
Sería:
select emp.DEPTNO,emp.ename,emp.job,dept.dname,dept.deptno
from dept LEFT JOIN emp ON
emp.deptno=dept.deptno;
Es decir, JOIN LEFT “por la izquierda” y a la izquierda tenemos la tabla DEPT
(departamento), nos seleccionará todos los departamentos tengan o no empleados.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 138/226
132
Consultas 7
Este mismo resultado lo podemos obtener con RIGHT JOIN, pero cambiando las
tablas, es decir, empleado (emp) a la izquierda y departamento (dept) a la derecha.
select emp.DEPTNO,emp.ename,emp.job,dept.dname,dept.deptno
from emp RIGHT JOIN dept ON
emp.deptno=dept.deptno;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 139/226
133
Consultas 7
7.4. SUBCONSULTAS
Una subconsulta es una sentencia SELECT dentro de otra sentencia SELECT (consulta
principal).
La sentencia Select se puede encontrar como:
− Como parte de una cláusula Where.
−
Como una tabla.
−
Una columna a mostrar.
− Como parte de una cláusula Having.
Además, siempre deben ir entre paréntesis y nunca deben contener la cláusula ORDER BY, ni
pueden ser UNION de varias sentencias select.
7.4.1. Select como parte de una cláusula Where o Having
Supongamos que tenemos una tabla Clientes y otra Ventas. En esta última se almacenan el
total de ventas realizadas a los clientes. Partiendo de la estructura:
CLIENTES={ID_CLIENTE, Descrip_Cliente}VENTAS={ID_VENTA, TOTAL_VENTAS, CLI_ID_CLIENTE}
Si tenemos los datos:
CLIENTES
100 Juan Martos Galán
150 La papelería s.a.
160 Librería Técnica s.l.200 María García Acosta
210 José Martín Rubio
VENTAS
1 2.500 100
2 5.000 150
3 9.500 160
4 2.350 200
5 3.750 210
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 140/226
134
Consultas 7
Si queremos obtener los clientes cuyas ventas superan la media de ventas de la empresa,
sería:
Select Descrip_Cliente,total_ventas from clientes,ventas
where ID_CLIENTE=CLI_ID_CLIENTE
and TOTAL_VENTAS > ( select avg(TOTAL_VENTAS) from VENTAS);
Resultado:
La papelería s.a. 5000
Librería Técnica s.l. 9500
José Martín Rubio 3750
Otro ejemplo aplicado a la tabla EMP del usuario Scott/tiger podría ser: ¿Obtener los
nombres y salarios de los empleados cuyo sueldo sea superior a la media de sueldos de la
empresa?
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 141/226
135
Consultas 7
7.4.2. Select como una tabla
Si en el ejemplo anterior se desease mostrar el valor de la media, podríamos incluir la
sentencia select como una tabla referenciada en la consulta principal, es decir, en el “from”.
7.4.3. Select como una columna a mostrar
Este es el caso en el cual la sentencia select es tratada como una columna de la sentencia
select principal.
Por ejemplo, supongamos dos tablas:
CLIENTES={ID_CLIENTE, Descrip_Cliente}
VENTAS={ID_VENTA, TOTAL_VENTAS, FECHA_VENTA, CLI_ID_CLIENTE}
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 142/226
136
Consultas 7
Si tenemos los datos:
CLIENTES
100 Juan Martos Galán
150 La papelería s.a.
160 Librería Técnica s.l.
200 María García Acosta
210 José Martín Rubio
VENTAS
1 2.500 2/1/03 100
2 5.000 10/1/03 150
3 9.500 5/01/03 160
4 2.350 12/01/03 200
5 3.750 15/01/03 210
6 1.250 18/01/03 150
7 3.500 4/01/03 160
8 7.500 4/01/03 210
9 2.900 10/12/02 100
10 7.000 11/01/03 210
Si deseamos obtener la última fecha de venta de cada uno de nuestros clientes sería:
Select Id_cliente,Descrip_Cliente, (select MAX(FECHA_VENTA) from VENTAS where
cli_id_cliente=id_cliente)
From clientes.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 143/226
137
Consultas 7
Este mismo resultado se podría obtener mediante el uso de GROUP BY y ORDER BY.
Select Id_cliente,Descrip_Cliente, MAX(FECHA_VENTA)
From clientes,VENTAS
WHERE id_cliente =cli_id_cliente
GROUP BY Id_cliente,Descrip_Cliente
ORDER BY 1;
7.4.4. Select como parte de una cláusula Having
Hemos dicho que la cláusula HAVING se aplica a las condiciones que afectan a funciones de
agrupamiento (SUM, MAX, COUNT,.....).
Una consulta muy típica es aquella que deseamos que nos muestre “el artículo más vendido
y en qué cuantía” o “el cliente que más nos ha comprado y qué cantidad”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 144/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 145/226
139
Consultas 7
Por ejemplo: supongamos una tabla que almacena la información de los menús de una
aplicación informática:
MENU
ID_MENU DESCRIPCION ID_PADRE
1 ARCHIVO
2 Salir 1
3 NAVEGADOR
4 Refrescar 3
5 Buscar 3
Si deseamos saber cuantos submenús o hijos poseen los nodos o menú principales, debemos
hacer una consulta como:
select m1.id_menu,m1.descripcion,count(*)
from menu m1, menu m2
where m1.id_menu=m2.id_padre
group by m1.id_menu,m1.descripcion;
El resultado sería:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 146/226
140
Consultas 7
7.6. CONSULTAS CON LA FUNCIÓN DECODE
La función DECODE es equivalente a la instrucción de bifurcación IF-THEN-ELSE. Su sintaxis
es:
DECODE(expresion, valor_a_comparar1, valor1, valor_a_comparar2,
valor2,.............,valorfinal)
La expresión se compara con los “valores_a_comparar” si cumple alguno, entonces
devuelve “valor” si no cumple ninguno de ellos, devuelve “valorfinal”.
Dentro de una consulta SQL, sería:
select decode(job,'ANALYST','Analista de cuentas',job) from emp;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 147/226
141
Consultas 7
− La cláusula HAVING es como una cláusula WHERE, pero se aplica a las
columnas de agrupación.
− Podemos usar en las consultas los operadores de conjunto.
− Una consulta puede formar parte de otra consulta.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 148/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 149/226
143
PL/SQL 8
8.1. DEFINICIÓN DE PL/SQL.............................................................145
8.2. PARTES DE UN PROGRAMA PL/SQL............................................146
8.2.1. Bloques ..........................................................................146
8.2.2. Variables y Constantes ...................................................147
8.2.3. Instrucciones o Estructuras de Control...........................155
8.2.4. Tratamientos de Errores (Exception)..............................157
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 150/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 151/226
145
PL/SQL 8
8.1. DEFINICIÓN DE PL/SQL
Podemos definir PL/SQL (Procedural Language/Structured Query Language) como un
lenguaje de programación utilizado para acceder a la información existente en una B.D.
relacional.
La gran ventaja de PL/SQL es que incluye las características de un lenguaje de 3GL y 4GL, es
decir, tercera y cuarta generación. Por lo que podemos usar instrucciones del tipo INSERT,
DELETE, UPDATE, etc. propias de los lenguajes de 4GL, dónde se dice lo que queremos hacer
pero no se detalla cómo hacerlo. Al mismo tiempo, con PL/SQL podemos usar las
características de los lenguajes de 3GL cómo:
− Declaraciones de variables.
− Instrucciones de bifurcación, repetición.
− Declaraciones de procedimientos, funciones, etc.
Ejemplo de código PL/SQL:
DECLARE
vi_password NUMBER := 12525;
vi_nombre VARCHAR2(10) := ‘Juan’;vi_apellido1 VARCHAR2(10) :=’García’;
vi_apellido2 VARCHAR2(10) :=’Martín’;
BEGÍN
UPDATE usuarios SET password = vi_password WHERE nombre = vi_nombre
AND apellido1 = vi_apellido1,
AND apellido2 = vi_apellido2;
IF SQL%NOTFOUND THEN
INSERT INTO usuarios (nombre,apellido1,apellido2,edad)
VALUES (vi_nombre,vi_apellido1,vi_apellido2,vi_edad);
END IF
END;
Podemos observar instrucciones propias de un lenguaje de cuarta y tercera generación.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 152/226
146
PL/SQL 8
8.2. PARTES DE UN PROGRAMA PL/SQL
8.2.1. Bloques
Todo programa PL/SQL se divide en bloques o secciones. La estructura de un bloque es:
− DECLARE
/* Bloque declarativo.- Es dónde declaramos las variables internas, cursores,
procedimientos y funciones locales del bloque. */
− BEGIN
/* Bloque principal o ejecutable.- Dónde desarrollamos la ejecución del programa.
*/
− EXCEPTION
/* Bloque exception o errores.- En él tratamos las excepciones de ERROR*/
− END; - - El punto y coma es obligatorio.
La única parte que es obligatoria dentro de un bloque es la sección o bloque ejecutable. Por
lo que podemos desarrollar bloques sin secciones declarativas y sin secciones de error, bien
porque no se requieran o no las necesitemos.
Ejemplo 1:
BEGÍN-- Bloque ejecutable
END;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 153/226
147
PL/SQL 8
Ejemplo 2:
DECLARE
-- Bloque declarativo
BEGÍN
-- Bloque ejecutable
END;
Los bloques pueden estar situados de forma secuencial o anidados.
8.2.2. Variables y Constantes
Las variables son las encargadas de almacenar la información que interactuará con la B.D.
Las constantes son un tipo de variable cuyo valor es invariable.
Ambas se declaran en la sección “DECLARE” de un bloque PL/SQL.
Su sintaxis es:
nombre_variable [CONSTANT] tipo_de_dato [ [NOT NULL] {DEFAULT || :=} expresión ] ;
Los tipos de datos son:
BINARY_INTEGER
Valores enteros entre:
–2.147.483.647 y 2.147.483.647
Si su valor es desbordado no se produce error.
Esto no ocurre en Oracle 9i.
NUMBER Valores fijos o de coma flotante desde 1-130 a 10125
PLS_INTEGER
Valores enteros entre:
–2.147.483.647 y 2.147.483.647
Si su valor es desbordado produce el error:
ORA-1426: numeric overflow
CHARAlmacena cadena de caracteres de longitud fija: 32.767 bytes. Un
CHAR de un campo o columna de la B.D. sólo admite 2.000 byte.
VARCHAR2Puede contener cadena de caracteres de longitud variable hasta
32.767 bytes. Un VARCHAR2 de un campo o columna de la B.D. sólo
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 154/226
148
PL/SQL 8
admite 4000 caracteres.
DATE Guarda valores relacionados con fechas, horas, minutos y segundos.
BOOLEAN TRUE, FALSE
ROWID Dirección física de una fila de la tabla.
UROWID Dirección física de una fila de la tabla particionada.
CLOB Almacena caracteres de GRAN TAMAÑO, hasta 4 GBytes.
BLOB Almacena objetos binarios de GRAN TAMAÑO, hasta 4 GBytes.
BFILE
Archivo binario externo, siendo el S.O. quien determina el Tamaño. Su
acceso es de sólo lectura. Su información no es gestionada por Oracle,
sino por el S.O. por lo que no están sujetos a transacciones.
Tipos de Datos Especiales:
− RECORD. Es un tipo de dato definido por el usuario que engloba a más de un tipo de
dato simple.
Ejemplo 1:
-- Declaramos el tipo.
TYPE reg_cliente IS RECORD(
id_cliente NUMBER(5),
Descripcion VARCHAR2(30)
);
-- Declaramos una variable del tipo anterior.
vi_registro reg_cliente;
Ejemplo 2:
TYPE reg_fecha_hora IS RECORD
(
dia number(2),
mes numer(2),
anyo number(4),
hora number(2),
minuto number(2)
);
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 155/226
149
PL/SQL 8
-- Declaramos una variable del tipo anterior.
vi_datos reg_fecha_hora;
Para referencia a un componente del tipo RECORD es:
nombre_variable.componente;
Ejemplo:
vi_registro.descripcion;
vi_datos.dia;
− Operador %TYPE y %ROWTYPE. Estos operadores nos sirven para asignar el tipo
de dato de una columna de una tabla (campo) o de toda una fila (registro) de la tabla.
Ejemplo 1:
DECLARE
vi_descripcion clientes.descripcion%TYPE;
Ejemplo 2:
DECLARE
vi_registro_clinete clientes%ROWTYPE;
Ejemplo 3:
Podemos leer un registro concreto de una tabla y almacenar su contenido en unavariable de tipo registro.
DECLARE
TYPE reg_cliente IS RECORD
(
id_cliente NUMBER(3), Descripcion VARCHAR2(30)
);
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 156/226
150
PL/SQL 8
vi_registro reg_cliente;
BEGIN
select id_cliente,descrip_cliente INTO vi_registro from clientes where
id_cliente=200;
dbms_output.put_line(vi_registro.id_cliente);
dbms_output.put_line(vi_registro.descripcion);
END;
Usando el operador %ROWTYPE, tendríamos:
− TABLE. Podemos decir que este tipo de dato es un Array, Vector, Tabla o Matriz
“especial” ya que como diferencia su índice no tiene porque empezar en 0 ó 1,
pudiendo tomar valores desde los números –2.147.483.647 a 2.147.483.647, es
decir, BINARY_INTEGER.
Al igual que los RECORD, para poder hacer uso de Matrices, debemos definir primero
un tipo de dato propio y después una variable de ese tipo.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 157/226
151
PL/SQL 8
Para declarar el TIPO sería:
TYPE nombre_tabla IS TABLE OF tipo_dato INDEX BY BINARY_INTEGER;
Después necesitaremos una Variable de ese tipo:
vi_matriz nombre_tabla;
El “tipo_dato” es un tipo de dato básico. Podemos especificar también como tipo de
dato el tipo de un campo de una tabla.
Ejemplos:
DECLARE
-- Tipos
TYPE matriz_fecha IS TABLE OF date INDEX BY BINARY_INTEGER;
TYPE matriz_cliente IS TABLE OF clientes.id_cliente%TYPE INDEX BY
BINARY_INTEGER;
-- Variables
vi_matriz_fecha matriz_fecha;
vi_matriz_cliente matriz_cliente;
Para tomar un valor de la matriz o asignárselo a la matriz es:
nombre_variable_tipo_matriz (indice);
Para el ejemplo sería:
vi_matriz_fecha(1):=sysdate;
También se pueden crear Matrices de Registros:
TYPE matriz_cliente IS TABLE OF clientes.%ROWTYPE INDEX BY
BINARY_INTEGER;
vi_matriz_cliente matriz_cliente;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 158/226
152
PL/SQL 8
Para referenciar los datos sería:
vi_matriz_cliente(indice).descrip_cliente;
Ejemplo:
Vamos a almacenar en una tabla llamada vi_matriz_cliente en el índice 160 el registro
correspondiente al cliente 160.
Oracle nos permite hacer uso de una serie de ATRIBUTOS de una matriz mediante el
nombre de la variable matriz y el nombre del atributo, es decir:
vi_matriz_cliente.COUNT
Estos atributos son los siguientes:
COUNT Devuelve el número de filas de la tabla.
DELETE Borra filas de la tabla.
EXISTS Devuelve TRUE si existe en la tabla el índice especificado.
FIRST Devuelve el índice de la 1ª fila.
LAST Devuelve el índice de la última fila.
NEXT Devuelve el índice de la siguiente fila a la especificada.
PRIOR Devuelve el índice de la anterior fila a la especificada.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 159/226
153
PL/SQL 8
• vi_matriz.DELETE.- Borra toda la matriz.
• vi_ matriz.DELETE(indice).- Borra el elemento de la matriz cuyo índice es el
especificado en “índice”.
• vi_ matriz.DELETE(indice_inicial, indice_final).- Borra los elementos de la matriz
cuyos índices están entre “indice_inicial” e “indice_final”.
• vi_ matriz.EXISTS(indice).- Devuelve TRUE si la tabla contiene una fila con el
índice especificado en “índice”.
• vi_ matriz.NEXT(indice).-Obtenemos el siguiente índice de la tabla, al
especificado en el índice.
• vi_ matriz.PRIOR(indice).- Obtenemos el índice anterior de la tabla, al
especificado en el índice.
− VARRAY. Este tipo de dato es equivalente a las matrices, vectores o arrays de otros
lenguajes de programación, dónde el índice comienza en 1 y se especifica en su
declaración el índice límite.
Su sintaxis es:
TYPE nombre_del_array IS VARRAY(tamaño_maximo)} OF tipo_dato [NOT NULL]
NOTA: tipo_dato no puede ser: BOOLEAN, NCHAR, NCLOB, NVARCHAR2, REF
CURSOR, TABLE ni por supuesto otro tipo VARRAY.
Ejemplos:
DECLARE
TYPE array_fecha IS VARRAY(20) OF date;
TYPE array_registro IS VARRAY(10) OF cliente%ROWTYPE;
Una vez especificado el tipo, necesitamos la variable:
vi_array array_registro;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 160/226
154
PL/SQL 8
Al tipo de datos VARRAY, podemos aplicarle los mismos atributos que existen para el
tipo e dato TABLE.
Ejemplo de VARRAY:
− REF CURSOR . Este tipo de dato nos permite definir variables de tipo cursor. Serán
tratadas en el tema correspondiente a los cursores.
El AMBITO de una variable está sujeto al módulo DECLARE existente por encima de
un bloque de código BEGIN-END. Por ejemplo:
DECLARE
Variable1 NUMBER(10);
BEGIN
DECLARE
Variable2 VARCHAR2(50);
BEGIN
/* AMBITO de Variable2 */
END;
END;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 161/226
155
PL/SQL 8
8.2.3. Instrucciones o Estructuras de Control
Las instrucciones o estructuras de control de PL/SQL son las típicas de todo lenguaje de
programación, es decir las instrucciones de BIFURCACION y los BUCLES.
− Bifurcación. La sintaxis de la instrucción de bifurcación es:
IF expresión THEN
Sentencias;
[ELSIF expresión THEN
secuencias;]
........
[ELSE
sentencias;]
END IF;
Ejemplo:
Partiendo de los datos de la tabla VENTAS existentes en la imagen gráfica, deseamos
saber si la suma de las ventas del cliente cuyo id_cliente es 100, superan los 5.000
Euros. Como resultado debemos mostrar el mensaje “Total de compras superior a
5.000 Euros” o “Total de compras inferior a 5.000 Euros”.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 162/226
156
PL/SQL 8
El resultado sería:
− Bucles. Un bucle permite ejecutar una sentencia o conjunto de sentencias, un número
determinado de veces.
• Bucles LOOP: Su sintaxis es:
LOOP
Sentencias;
EXIT WHEN condición;
END LOOP;
Este bucle finalizará cuando la condición sea verdadera. La instrucción EXIT
WHEN condición; es equivalente a usar.
IF condicion THEN
EXIT;
END IF;
• Bucles WHILE: Su sintaxis es:
WHILE condicion LOOP
Sentencias;
END LOOP;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 163/226
157
PL/SQL 8
Este bucle finalizará cuando la condición sea falsa.
• Bucles FOR: Su sintaxis:
FOR variable_contador IN [REVERSE] valor_inferior..valor_superior LOOP
Sentencias;
END LOOP;
variable_contador es la variable para recorrer el rango de valores comprendido
entre valor_inferior y valor_superior. (en el caso de REVERSE sería al revés, es
decir desde valor_superior a valor_inferior).
Ejemplo:
Supongamos que tenemos una tabla llamada PARES que queremos rellenar con
todos los números pares desde el 2 al 10000.
DECLARE
vi_valor NUMBER(5) := 0;
BEGIN
FOR vi_contador IN 1..5000 LOOP
vi_valor := vi_valor + 2;
insert into PARES values(vi_valor);
END LOOP;
END;
8.2.4. Tratamientos de Errores (Exception)
La forma en la que Oracle gestiona los errores es mediante las excepciones y los gestores deexcepciones.
Los TIPOS de ERRORES en un programa PL/SQL pueden ser:
−
Errores de Compilación.- Son tratados al compilar el programa PL/SQL y se actúa
sobre ellos de forma interactiva.
− Errores de Ejecución.- Son tratados por los gestores de excepciones y se actúa
sobre ellos programando su resultado. Cuando se produce un error salta una excepción
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 164/226
158
PL/SQL 8
la cual es tomada por el gestor de excepciones, consiguiendo de esta forma separar la
lógica de nuestra aplicación del tratamiento de errores.
EJEMPLO:
DECLARE
vi_valorA NUMBER(2) := 0;
vi_valorB NUMBER(2) := 12;
BEGIN
vi_valorB := vi_valorB / vi_valorA;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error al dividir por cero.');
END;
Las EXCEPCIONES las podemos dividir en dos tipos:
a) Excepciones definidas por el programador. Son aquellas que crea el
programador para su propio control de errores. Se declaran en el bloque DECLARE
del código PL/SQL y son del tipo EXCEPTION.
Ejemplo:
DECLARE
control_error_1 EXCEPTION;
Este tipo de excepciones deben ser controladas por el programador y llamadas
mediante la sentencia “RAISE nombre_excepción;”, pasando el control del código
PL/SQL al módulo EXCEPTION.
b) Excepciones predefinidas en Oracle. Son una serie de excepciones de errores
comunes que Oracle ha predefinido para que puedan ser tratadas en los códigos
PL/SQL.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 165/226
159
PL/SQL 8
Algunas de ellas son:
ORA-0001 DUP_VAL_ON_INDEXSe incumple una restricción UNIQUE o PRIMERY
KEY.
ORA-1001 INVALID_CURSOR Operación incorrecta con un CURSOR.
ORA-1012 NOT_LOGGED_ON Sin conexión con Oracle.
ORA-1017 LOGIN_DENIED Login o password incorrectos.
ORA-1403 NO_DATA_FOUND Ningún dato encontrado.
ORA-1422 TOO_MANY_ROOMS Select....INTO... con más de una fila.
ORA-1476 ZERO_DIVIDE Se intenta dividir por cero.
ORA-1722 INVALID_NUMBER Fallo al convertir a un número.
ORA-6500 STORAGE_ERROR Se produce cuando PL/SQL se queda sin memoria.
ORA-6501 PROGRAM_ERROR Error interno PL/SQL.
ORA-6502 VALUE_ERROR Error de conversión, truncamiento o aritmético.
ORA-6511 CURSOR_ALREADY_OPEN Se ha intentado abrir un cursor que está abierto.
Estas no tienen por que ser llamadas con la sentencia RAISE.
Dentro del módulo o bloque EXCEPTION podemos controlar más de una
EXCEPTION bien separándolas con el operador OR o mediante varias sentencias
WHEN.
Todo podemos resumirlo como:
DECLARE
Nombre_exception_propia EXCEPTION;
BEGIN
. .......;
........;
IF condicion THEN
RAISE Nombre_exception_propia;
END IF;
EXCEPTION
WHEN Nombre_exception_propia THEN
Sentencias;
WHEN Nombre_exception_predefinida THEN
Sentencias;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 166/226
160
PL/SQL 8
WHEN Nombre_exception_predefinidaA OR
Nombre_exception_predefinidaB WHEN
Sentencias;
WHEN others THEN
Sentencias;
END;
IMPORTANTE.- La EXCEPCION ocurre cuando falla la sentencia a realizar.
Ejemplo: En este ejemplo se incumple la PRIMERY KEY de la tabla VENTAS, ya que
existe el id_ventas 10. La “exception” se ejecuta al intentar realizar el INSERT,nunca llega a la línea RAISE vi_error.
Oracle nos proporciona dos funciones PREDEFINIDAS llamadas SQLCODE y
SQLERRM las cuales nos proporcionan el número del error (number) producido yel mensaje de error (longitud máxima 512 caracteres). Estas funciones
predefinidas son muy útiles para la EXCEPTION others.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 167/226
161
PL/SQL 8
Si lo aplicamos al ejemplo anterior tendríamos:
Podemos usar la función predefinida RAISE_APPLICATION_ERROR para crear
nuestros propios mensajes de error.
Ejemplo:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 168/226
162
PL/SQL 8
Si se usa un solo módulo de EXCEPTION, podemos encontrarnos con problemas de
localización del error. Por ejemplo:
DECLARE
vi_id NUMBER := null;
BEGIN
select id_cliente INTO vi_id from clientes where id_cliente=100;
select id_cliente INTO vi_id from clientes where id_cliente=555;
select id_cliente INTO vi_id from clientes where id_cliente=200;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Nº Error: ' || SQLCODE || ' Mensaje: ' ||
SQLERRM);
END;
No sabemos qué SELECT ha producido el error. Por este motivo, para localizarlo,
podemos dividir los select en sub-bloques.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 169/226
163
PL/SQL 8
− Usamos PL/SQL para realizar programas que nos permitan obtener y
manipular la información almacenada en una B.D.
− Un programa PL/SQL se compone de tres bloques: Declaración, Ejecución y
Excepción.
− Los tratamientos de errores pueden ser personalizados por el usuario o usar
los predefinidos por Oracle.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 170/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 171/226
165
Funciones, procedimientos,
paquetes y disparadores 9
9.1. INTRODUCIÓN..........................................................................167
9.2. PROCEDIMIENTOS ....................................................................168
9.3. FUNCIONES ..............................................................................172
9.4. VISTAS RELACIONADAS CON LOS PROCEDIMIENTOS
Y FUNCIONES ...........................................................................175
9.5. PAQUETES ................................................................................1779.6. DISPARADORES........................................................................179
9.7. DISPARADORES O TRIGGER MUTANTES....................................181
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 172/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 173/226
167
Funciones, procedimientos,
paquetes y disparadores 9
9.1. INTRODUCIÓN
Los programas PL/SQL vistos anteriormente son bloques anónimos, es decir, no quedan
almacenados como un objeto dentro de la B.D. para poder ser usados posteriormente. Para
conseguir que un programa PL/SQL quede almacenado en Oracle, debemos englobarlo en
funciones o procedimientos.
Estos objetos son localizables dentro de la B.D. como cualquier otro objeto: Tablas, Indices,
Usuarios, Tablespaces, etc.
Estos objetos son localizables en el “esquema” y dentro de éste en la carpeta “Tipos de
Origen”.
La principal ventaja de englobar los programas PL/SQL dentro de procedimientos o
funciones, es que a parte de almacenarse en la B.D, éstos quedan compilados a la espera de
ser llamados para su ejecución sin necesidad de que Oracle pierda tiempo en volver a
compilar.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 174/226
168
Funciones, procedimientos,
paquetes y disparadores 9
9.2. PROCEDIMIENTOS
Para crear un procedimiento podemos hacerlo desde la herramienta gráfica “Consola de
Oracle Enterprise Manager”:
O mediante la orden CREATE PROCEDURE cuya sintaxis es:
CREATE [OR REPLACE] PROCEDURE nombre_procedimiento
[(argumento [ { IN || OUT || IN OUT } ] tipo,
....
argumento [ { IN || OUT || IN OUT } ] tipo,) ]
{ IS || AS }
<< Declaración de variables LOCALES >>
BEGIN
<< Cuerpo del Procedimiento >>
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 175/226
169
Funciones, procedimientos,
paquetes y disparadores 9
EXCEPTION
<< Tratamiento de la excepciones >>
END [nombre_procedimiento];
CONSIDERACIONES:
1.
Si no se usa la cláusula OR REPLACE y existe un procedimiento con ese nombre,
Oracle no nos dejará crearlo.
2.
El procedimiento puede tener o no argumentos, es decir, valores que posteriormente
serán usados dentro del propio código PL/SQL.
3.
Si no especificamos la modalidad IN || OUT || IN OUT en el argumento, este se
considerará de tipo IN (entrada).
4.
Diremos que:
• IN = Sólo LECTURA
•
OUT = Sólo ESCRITURA
• IN OUT = Lectura / Escritura
5. Un procedimiento es una orden DDL, por lo que lleva de forma implícita un COMMIT.
6.
Un procedimiento puede ser llamado desde un módulo PL/SQL anónimo o desde otro
procedimiento, usando tan solo el nombre del procedimiento.
7. En la declaración del procedimiento es INCORRECTO restringir el argumento con el
tipo.
Es decir:
CREATE PROCEDURE calcular (valor1 NUMBER(3,2), nombre VARCHAR2(30) )
AS
Nos daría un ERROR.
Debemos hacerlo:
CREATE PROCEDURE calcular ( valor1 NUMBER, nombre VARCHAR2 ) AS
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 176/226
170
Funciones, procedimientos,
paquetes y disparadores 9
Por lo que las restricciones de los parámetros o argumentos existentes en la cabecera
de un procedimiento, vendrán determinadas por los parámetros de la aplicación que
llama al procedimiento, conocidos con el nombre de parámetros reales.
Ejemplo:
Declare
Variable_1 NUMBER (5,2);
Variable_2 VARCHAR2(10);
/* Variable_1 y Variable_1 son parámetros reales */
Calcular(Variable_1, Variable_2);
8.
Una forma de restringir en la cabecera del procedimiento es haciendo uso de la orden
%TYPE.
CREATE PROCEDURE calcular ( valor1 tabla.salario%TYPE, nombre VARCHAR2 ) AS
9. Un parámetro puede ser inicializado en la cabecera del procedimiento siguiendo la
siguiente sintaxis:
nombre_parámetro [ IN || IN OUT ] tipo { := | DEFAULT } valor_de_inicilaizacion
Ejemplo:
Partiendo de la tabla CLIENTES cuya estructura es:
ID_CLIENTE NOT NULL NUMBER(3)
DESCRIP_CLIENTE NOT NULL VARCHAR2(30)
Crear un procedimiento llamado insertar_clientes que nos inserte un cliente dado:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 177/226
171
Funciones, procedimientos,
paquetes y disparadores 9
Podemos observar cómo se ha creado un objeto procedimiento en la B.D.:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 178/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 179/226
173
Funciones, procedimientos,
paquetes y disparadores 9
Su sintaxis es:
CREATE [OR REPLACE] FUNCTION nombre_funcion
[(argumento [ { IN || OUT || IN OUT } ] tipo,
....
argumento [ { IN || OUT || IN OUT } ] tipo,) ]
RETURN tipo_de_dato_de_retorno { IS || AS }
<< Declaración de variables LOCALES >>
BEGIN
<< Cuerpo del Procedimiento >>
RETURN expresion;
EXCEPTION
<< Tratamiento de la excepciones >>
END [nombre_funcion];
Ejemplo:
Crear una función que nos devuelva el número total de clientes existentes en nuestra tabla
CLIENTES
CREATE FUNCTION numero_clientes
RETURN number
as
vi_num_clientes NUMBER;
BEGIN
Select count(*) into vi_num_clientes from clientes;
RETURN vi_num_clientes;
END numero_clientes;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 180/226
174
Funciones, procedimientos,
paquetes y disparadores 9
Podemos observar cómo la función es un objeto dentro de la B.D.
Para hacer uso de la función creada, sólo con llamarla en un código PL/SQL nos valdría.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 181/226
175
Funciones, procedimientos,
paquetes y disparadores 9
Para BORRAR una función, sólo tendremos que ejecutar la orden:
DROP FUNCTION nombre_funcion;
9.4. VISTAS RELACIONADAS CON LOS PROCEDIMIENTOS Y FUNCIONES.
Algunas de las vistas del Diccionario de Datos que nos informa de los procedimientos y
funciones son:
−
user_objects
−
user_source
− user_errors
user_objects.- Nos muestra información de los objetos que tiene un usuario. Las columnas
que contienen son las siguientes:
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 182/226
176
Funciones, procedimientos,
paquetes y disparadores 9
DATA_OBJECT_ID
NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED
DATE
LAST_DDL_TIME
DATE
TIMESTAMP
VARCHAR2(19)
STATUSVAR CHAR2(7)
TEMPORARY
VARCHAR2(1)
GENERATED
VARCHAR2(1)
SECONDARY VARCHAR2(1)
Podemos obtener información cómo: Nombre del Objeto, Fecha de Creación, Fecha de última
modificación, estado actual, etc.
user_source.- Contiene el código fuente del procedimiento o de la función. Es decir, dicha
vista está constituida por las columnas:
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
Ejemplo:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 183/226
177
Funciones, procedimientos,
paquetes y disparadores 9
user_errors.- Contiene la información de los mensajes de error en la compilación de un
procedimiento o función.
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(12)
SEQUENCE NOT NULL NUMBER
LINE NOT NULL NUMBER
POSITION NOT NULL NUMBER
TEXT NOT NULL VARCHAR2(4000)
9.5. PAQUETES
Un paquete es una estructura PL/SQL que nos permite almacenar sentencias SQL y
programas PL/SQL. En un paquete se diferencian dos partes:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 184/226
178
Funciones, procedimientos,
paquetes y disparadores 9
− Cabecera del Paquete. Contiene información declarativa sobre el contenido del
paquete. Nunca debe contener el código PL/SQL del cuerpo de una función o
procedimiento.
Su sintaxis es:
CREATE [OR REPLACE] PACKAGE nombre_paquete
{ IS || AS}
[ Cabecera de PROCEDIMIENTO..]
[ Cabecera de FUNCION..]
[ Declaraciones de variable..]
[ Definición de tipo..]
[ Declaración de Excepción..]
[ Declaración_de_cursor;..]
END [nombre_paquete];
− Cuerpo del paquete. Contiene el código de los elementos que forman el paquete, es
decir, el código de la función, procedimiento, etc.
Los elementos del cuerpo de un paquete NUNCA podrán ser compilados si no existe la
declaración de los mismos en la cabecera del paquete.
Su sintaxis es:
CREATE [OR REPLACE] PACKAGE BODY nombre_paquete
{IS || AS?}
[BEGIN] => NO SE PONE en el BODY.
<<Código PL/SQL de los elementos del paquete>>
END [nombre_paquete];
Tanto la cabecera como el cuerpo son almacenados por Oracle en el Diccionario de Datos
como objetos diferentes.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 185/226
179
Funciones, procedimientos,
paquetes y disparadores 9
IMPORTANTE
Todo elemento del paquete (Procedimiento, función,...) puede ser referenciado como se
muestra a continuación:
Nombre_paquete.elemto_del_paquete
9.6. DISPARADORES
Los disparadores mas comúnmente conocidos como TRIGGER son similares a los
procedimientos y a las funciones, salvo que no pueden ser llamados desde otro bloque
PL/SQL ni admiten argumentos.
Cuando sobre una tabla se lleva a cabo una operación DML del tipo INSERT, UPDATE o
DELETE es cuando podemos hacer que se ejecute el disparador.
Su sintaxis es:
CREATE [OR REPLACE] TRIGGER nombre_trigger
{ BEFORE || AFTER } suceso_disparo ON referencia_tabla
[FOR EACH ROW [WHEN condicion_disparo] ]
cuerpo_disparo;
− suceso_disparo.- Los sucesos que activan un disparador son INSERT, UPDATE y
DELETE que se lleven a cabo ANTES (Before) o DESPUES (After) sobre la tabla
especificada en referencia_tabla.
El nivel de acción del disparador, puede ser:
1. Especificado a cada fila de la tabla, ejecutándose el cuerpo_disparo por cada
fila afectada cuando la condicion_disparo es verdadera.
2. Especificado a toda la TABLA (conocido también como a nivel orden), es decir el
cuerpo_disparo se ejecuta cuando se realiza un insert, update o delete sobre
referencia_tabla.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 186/226
180
Funciones, procedimientos,
paquetes y disparadores 9
Por tanto, en total podremos realizan 12 acciones distintas:
I = Insert U=Update D=Delete B= Before A= After O= Orden F=Fila
I , B, O U , B, O D , B, O
I , B, F U , B, F D , B, F
I , A, O U , A, O D , A, O
I , A, F U , A, F D , A, F
El disparador o trigger se ejecuta al ejecutarse una orden DLL, siendo el proceso a
seguir el siguiente:
1.
Si existe, ejecutar el disparador de tipo BEFORE (antes) con NIVEL DE ORDEN.
2. Para cada fila implicada en la orden:
2.1. Si existe, ejecutar por cada fila el disparador BEFORE.
2.2. Si existe, ejecutar por cada fila el disparador AFTER.
3. Si existe, ejecutar el disparador de tipo AFTER (después) con NIVEL DE ORDEN.
− Los pseudo-registros: OLD y: NEW. Sabemos que los disparadores por fila se
ejecutan una vez por cada fila afectada de la orden DML que hace “saltar” al
disparador. Los pseudo-registros :old y :new nos permiten tener acceso a los datos de
la fila afectada.
Estos pseudo-registros son del tipo tabla_asociada_al_disparador %ROWTYPE, es
decir TIPO_REGISTRO. No podemos manejar todo el registro o fila, sólo podemos
hacer uso individual de las columnas que componen la fila o registro.
En la siguiente tabla detallamos qué valores pueden tomarse de las pseudo-columna
según la orden DML que se esté ejecutando:
INSERT UPDATE DELETE
:old NULL Valor antes de actualizar Valor ante de borrar
:new Valor que se ha insertado Nuevo valor actualizado NULL
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 187/226
181
Funciones, procedimientos,
paquetes y disparadores 9
Oracle nos permite programar un trigger o disparador hacia una tabla sobre la cual se vaya a
realizar más de una operación DML (insert, update y delete). Para distinguir qué acción se ha
realizado, podemos hacer uso de INSERTING, UPDATING y DELETING en el
“cuerpo_del_disparador”, es decir:
IF INSERTING THEN
Sentencia;
ELSIF UPDATING THEN
Sentencia;
ELSIF DELETING THEN
Sentencia;
END IF;
Para eliminar un trigger: DROP TRIGGER nombre_trigger.
En diferencia con los procedimientos y funciones, los trigger pueden ser desactivados sin
necesidad de ser borrados.
ALTER TRIGGER nombre_trigger [ DISABLE | ENABLE ]
También podemos desactivar TODOS los trigger de una TABLA mediante:
ALTER TABLE nombre_tabla [ ENABLE ALL TRIGGERS | DISABLE ALL TRIGGERS ];
La vista del diccionario de datos que nos proporciona información sobre los trigger se llama
user_triggers.
9.7. DISPARADORES O TRIGGER MUTANTES
Oracle impone una serie de restricciones a la hora de que el código del cuerpo de un trigger,
pueda actuar o no sobre una tabla. Podemos distinguir:
− TABLAS MUTANTES.- Son aquellas sobre las que actúa una orden DML y la acción
del TRIGGER actúa sobre ellas. Por lo que las sentencias o instrucciones del cuerpo del
disparador NO PODRAN leer o modificar de dicha tabla.
Si el INSERT hacia una tabla sólo afecta a una fila, los trigger tanto del tipo BEFORE
como AFTER no darán el error de tabla mutante. Todo insert del tipo INSERT INTO
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 188/226
182
Funciones, procedimientos,
paquetes y disparadores 9
Select ... producirá trigger mutantes, incluso si el resultado del Select es de una sola
columna o registro.
− TABLAS de RESTRICCIONES.- Son aquellas de las que Oracle necesita leer para
verificar las restricciones de integridad referencial. Por ello, ningún TRIGGER podrá
leer o modificar los campos o columnas que sean clave primaria, única o foreign para
la tabla que se ha creado el disparador. Las columnas o campos, pueden ser
modificados sin ningún tipo de problema.
NOTA.- Estas restricciones se aplican a aquellos disparadores o trigger
programados para actuar a NIVEL DE FILAS. A los disparadores a nivel de orden,
sólo son aplicables cuando el disparador se activa al ejecutarse una orden DELETE
_CASCADE.
Estos errores se producen cuando el disparador se activa, NO CUANDO se crea.
Ejemplo TRIGGER MUTANTE:
Usando la tabla EMP y DEPT del usuario scott/tiger realizar un trigger que compruebe cada
vez que se realiza un INSERT o UPDATE sobre la tabla EMP que no existan más de 6
empleados por departamento.
Estructura Tabla EMP:
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Estructura Tabla DEPT:
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Si ejecutamos la sentencia:
select count(*),deptno from emp group by deptno;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 189/226
183
Funciones, procedimientos,
paquetes y disparadores 9
Tendremos:
Partiendo de esos datos realizaremos y comprobaremos el trigger.
Si intentamos actualizar el número del departamento para el empleado número 7369 al
departamento 10, Oracle nos informa de que la tabla está mutando.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 190/226
184
Funciones, procedimientos,
paquetes y disparadores 9
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 191/226
185
Funciones, procedimientos,
paquetes y disparadores 9
− Debemos utilizar los procedimientos y funciones para hacer que un
programa PL/SQL no sea anónimo.
−
Las agrupaciones de procedimientos y funciones se llevarán a cabo con los
paquetes.
− Las características de los procedimientos, funciones y paquetes las podemos
visualizar mediante las vistas del diccionario de datos o mediante la
herramienta gráfica.
− Los TRIGGER van asociados a una acción INSERT,UPDATE o DELETE de una
tabla.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 192/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 193/226
187
Cursores 10
10.1. TIPOS DE CURSORES .............................................................189
10.2. ATRIBUTOS DE CURSORES .....................................................191
10.3. RECORRIDO DE CURSORES ....................................................192
10.4. BLOQUEOS CON EXCLUSIVIDAD.............................................194
10.5. VARIABLES DE CURSOR .........................................................194
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 194/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 195/226
189
Cursores 10
10.1. TIPOS DE CURSORES
Un cursor es una referencia a una fila o conjunto de filas devueltas por una consulta a tablas
de la B.D.
Diferenciamos dos tipos de cursores:
−
Cursores Explícitos. Se usan para manejar un conjunto de datos (filas) devueltos por
una consulta a la B.D:
Los pasos para operar con ellos son:
1.
Declaración del cursor.- Se lleva a cabo en el apartado DECLARE de un
programa PL/SQL, además de asignarle la orden SELECT que devuelve las filas a
procesar.
Su sintaxis:
CURSOR nombre_cursor IS SELECT ........;
Sólo hay que tener en cuenta que la orden Select no puede ser del tipo
SELECT....INTO....;
Si la consulta llevase variables, éstas deben ser declaradas antes que el cursor, es
decir:
DECLARE
vi_fecha_nac DATE;
CURSOR c_clientes IS SELECT nombre,apellidos WHERE edad >=
vi_fecha_nac;
BEGIN
END;
OTRA FORMA es:
DECLARE
CURSOR c_clientes(vi_fecha_nac DATE) IS SELECT nombre,apellidos
WHERE edad >= vi_fecha_nac;
BEGIN
END;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 196/226
190
Cursores 10
A esta modalidad se le denomina cursores parametrizados.
2.
Abrir el Cursor.- La orden para abrirlo es: OPEN nombre_cursor;
En el momento de abrirlo, es cuando se tienen en cuenta los valores de las
variables.
Si abrimos un cursor que ya estaba abierto, Oracle ejecuta de forma implícita una
orden CLOSE del cursor. También podemos tener más de un cursor abierto, con
distinto nombre.
3.
Leer los Datos.- Para poder leer una fila de los datos devueltos por el cursor
usamos la orden:
FECTH nombre_cursor INTO variables;
o también:
FECTH nombre_cursor INTO registro;
•
Variables.- Conjunto de variables previamente declaradas y separadas por
coma en la orden FETCH.
• Registro.- Es una variable tipo_registro.
Hay que tener muy en cuenta que el número de variables existentes tiene que
coincidir con el mismo número de columnas usadas en la consulta Select, al igual
que con el tipo de dato.
4. Cierre del Cursor.- Debemos cerrar el cursor o cursores para liberar el espacio de
memoria ocupado por éste. La sentencia es: CLOSE nombre_cursor;
− Cursores Implícitos. Se usan para el manejo de órdenes INSERT, UPDATE, DELETE y
SELECT ..... INTO de una sola fila.
No precisan de apertura (OPEN), ni lectura (FETCH), ni cierre (CLOSE) ya que es
Oracle quien lleva a cabo estas acciones de forma automática.
A este cursor se le conoce como cursor SQL.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 197/226
191
Cursores 10
10.2. ATRIBUTOS DE CURSORES
Van asociados al nombre del cursor y devuelven un valor que puede ser procesado en
instrucciones de bifurcación o bucles.
Estos son:
− %FOUND. Devuelve TRUE si la instrucción FETCH ha leído una fila o FALSE si no la ha
leído.
− %NOTFOUND. Actúa de forma inversa a %FOUND, es decir, devuelve TRUE si la
instrucción FETCH NO ha leído una fila y FALSE si la ha leído.
− %ISOPEN. Nos informa si el cursor está abierto, devolviendo TRUE. Si no lo está,
devuelve FALSE.
− %ROWCOUNT. Nos informa del número de filas referenciadas por el cursor.
Estos atributos podemos aplicarlos tanto a los cursores explícitos como implícitos.
Ejemplo aplicado a CURSORES implícitos.
Se desea actualizar una fila de una tabla CLIENTES, si éste no existe, se insertará un nuevo
registro.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 198/226
192
Cursores 10
Otra forma de programar este módulo PL/SQL es haciendo uso de una EXCEPTION de
errores.
10.3. RECORRIDO DE CURSORES
Para recorrer toda la información referenciada por un cursor, podemos usar diferentes
modalidades de bucles. Distinguimos:
−
Bucles LOOP ..... END LOOP. La sintaxis para recorrer todos los registros de una
tabla llamada CLIENTES:
DECLARE
vi_registro CLIENTES%ROWTYPE;
CURSOR c_cliente IS SELECT * FROM clientes;
BEGIN
OPEN c_cliente;
DBMS_OUTPUT.PUT_LINE( 'Datos de los Cliente');
LOOP
FETCH c_cliente INTO vi_registro;
EXIT WHEN c_cliente%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( vi_registro. descrip_cliente );
END LOOP;
CLOSE c_cliente;
COMMIT;
END;
−
Bucles WHILE. El ejemplo anterior pero usando WHILE ..... END LOOP.
DECLARE
vi_registro CLIENTES%ROWTYPE;
CURSOR c_cliente IS SELECT * FROM clientes;
BEGIN
OPEN c_cliente;
DBMS_OUTPUT.PUT_LINE( 'Datos de los Cliente');
FETCH c_cliente INTO vi_registro;
WHILE c_cliente %FOUND LOOP
DBMS_OUTPUT.PUT_LINE( vi_registro. descrip_cliente );
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 199/226
193
Cursores 10
FETCH c_cliente INTO vi_registro;
END LOOP;
CLOSE c_cliente;
COMMIT;
END;
− Bucles FOR. Este tipo de bucle ejecuta una orden OPEN, FETCH y CLOSE de forma
implícita. El ejemplo anterior quedaría:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 200/226
194
Cursores 10
10.4. BLOQUEOS CON EXCLUSIVIDAD
Cuando abrimos un cursor (OPEN) Oracle, toma una instancia de las tablas o conjuntos de
tablas especificadas en el select del cursor. Si otra sesión cambia o modifica los valores de
esas tablas incluyendo la confirmación de los cambios (commit), el cursor anteriormente
abierto, no tiene consistencia sobre los datos leídos, para ello se necesitaría volver a abrir el
cursor.
Oracle nos permite bloqueos exclusivos sobre las filas involucradas en la consulta asociada al
cursor: Para ello, escribiremos al final de la sentencia select:
CURSOR nombre_cursor IS SELECT.....FROM.......FOR UPDATE [OF referencia_columna]
[NOWAIT];
referencia_columna.- Columna o columnas de la tabla o tablas a las cuales se les hace
referencia.
Si otra sesión tuviese las filas bloqueadas en exclusividad, entonces la sentencia
SELECT....FOR UPDATE....esperaría hasta que se libere la exclusividad. Si se desea NO tener
ese proceso en espera, podemos usar la cláusula NOWAIT, por lo que la sentencia OPEN del
cursor no se llevaría a cabo, devolviendo Oracle un mensaje de error.
Además de usar FOR UPDATE, en la sentencia UPDATE o DELETE debemos usar la cláusula,
WHERE CURRENT OF nombre_cursor.
10.5. VARIABLES DE CURSOR
Las variables de cursor, nos permiten DEVOLVER cursores creados en tiempo de ejecución
en subprogramas PL/SQL para su procesamiento en aplicaciones externas.
Para declarar una variable cursor, primero tenemos que definir un tipo:
TYPE tipo_variable_cursor IS REF CURSOR [RETURN tipo_retorno_registro];
tipo_retorno_registro.- Es un tipo nombre_table%ROWTYPE, o bien una variable registro
definida antes del tipo.
Una vez declarado el tipo, tendremos que declarar una variable de ese tipo definido.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 201/226
195
Cursores 10
Ejemplos:
DECLARE
TYPE tipo_registro IS RECORD
( id_cliente CLIENTES.id_cliente%TYPE,
datos_cliente CLIENTES .descrip_cliente%TYPE);
TYPE tipo_ref_cursor IS REF CURSOR RETURN tipo_registro;
variable_cursor tipo_ref_cursor;
La declaración anterior, nos sirve para especificar una variable cursor con un número de
columnas específica. Si se deseasen todas las columnas de la fila sería:
TYPE tipo_ref_cursor IS REF CURSOR ;
variable_cursor tipo_ref_cursor;
Una vez declarada, necesitamos abrirla para su manipulación. Para ello usaremos:
OPEN variable_cursor FOR sentencia_SELECT;
Para leer los valores de la variable cursor, usaremos orden:
FETCH nombre_variable_cursor INTO variable_interna1, variable_interna2,....
Al igual que un cursor, la variable cursor también deben ser cerradas, con la sentencia
CLOSE nombre_variable_cursor;
Ejemplo.
Crearemos un procedimiento llamado mostrar_datos que usando una variable cursor, me
muestre los datos de la tabla CLIENTES.
CREATE OR REPLACE PROCEDURE mostrar_datos
AS
TYPE tipo_cursor IS REF CURSOR ;
variable_cursor tipo_cursor;
vi_id_cliente NUMBER;
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 202/226
196
Cursores 10
vi_descripcion VARCHAR2(30);
BEGIN
OPEN variable_cursor FOR SELECT * FROM CLIENTES;
LOOP
FETCH variable_cursor INTO vi_id_cliente,vi_descripcion;
EXIT WHEN variable_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( vi_id_cliente || ' ' || vi_descripcion);
END LOOP;
END;
El resultado:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 203/226
197
Cursores 10
− Los cursores son referencias a filas de datos, requiriendo ser declarados,
abiertos, leídos y cerrado si son del tipo explícito.
−
Los atributos de cursores nos permiten obtener información de los cursores
para su procesamiento.
− Las variables de cursor nos permiten un empleo más generalizado de los
cursores.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 204/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 205/226
199
Índices 11
11.1. DEFINICIÓN ..........................................................................201
11.2. CARACTERÍTICAS DE LOS ÍNDICES .......................................202
11.3. REGLAS A LA HORA DE CREAR UN ÍNDICE..............................203
11.4. TIPOS DE ÍNDICES ................................................................205
11.4.1. Índices de Tablas, Arbol-B, B-Tree o Normal............205
11.4.2. Índices de Mapas de Bits .........................................205
11.4.3. Índices basados en una función...............................20711.4.4. Índices de Clave Inversa .........................................208
11.4.5. Cluster o Agrupamiento...........................................208
11.5. CREACIÓN DE ÍNDICES ..........................................................210
í n d i c e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 206/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 207/226
201
Índices 11
11.1. DEFINICIÓN
Los índices se aplican a columnas de una tabla, pudiendo estar constituidos por una o más
columnas de la tabla. Su funcionalidad es localizar la información a buscar lo más
rápidamente posible y con el menor número posible de E/S a disco, para conseguir de esta
forma un mayor rendimiento. No siempre es aconsejable crear índices ya que éstos deben
ser gestionados por Oracle, por lo que implica tiempo de gestión, lo cual hace que baje el
rendimiento en lugar de aumentarlo. Los índices sólo pertenecen a una tabla.
A continuación, vamos a ver a groso modo la funcionalidad de los índices.
Supongamos que tenemos una tabla compuesta de 26 columnas o campos cada uno de ellosdel tipo varchar2(30), es decir:
TABLA
Campo1 Campo2 Campo3 ....................... Campo26
Si se desea consultar el dato del Campo3, Oracle necesita extraer el resto de columnas
desde la 1 hasta la 26 y recorrer, por supuesto, toda la tabla. Suponiendo que se extraen 5
filas, tendríamos un buffer de lectura de:
Tamaño de columna: 30
Nº de columnas recuperadas: 26
Filas extraídas: 5
TOTAL BUFFER = 30 x 26 x 5 = 3900 caracteres.
Si en lugar de ser 5 filas fuesen 50.000 filas, tendríamos 39.000.000 de caracteres, es decir,
39 Mbyte de memoria caché.
Por tanto, debemos crear un índice que haga referencia al Campo3, en el cual Oracle
almacena únicamente el contenido de dicho campo y la referencia a esos valores dentro de la
tabla. Cada vez que se produzca una variación en ese campo, también se lleva a cabo en el
objeto índice creado.
Si ahora deseamos consultar la información del Campo3, Oracle recorre el índice. Por lo
tanto, para un total de 5 filas, tendremos 30 x 1 x 5 = 150 caracteres. Visto de otra forma,
para un Buffer de 3.900 caracteres, obtendríamos una lectura de 130 filas extraídas.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 208/226
202
Índices 11
(Total Buffer / Tamaño Columna * Nº de columnas recuperadas = Filas Extraidas )
11.2. CARACTERÍTICAS DE LOS ÍNDICES
Cuando se insertan datos en una tabla, no se sigue ningún tipo de orden, por lo que a la
hora de consultar dichos datos, Oracle necesita recorrer toda la tabla. Al crear un índice,
Oracle ordena la información de dicho índice bien sea éste de tipo carácter, numérico o
fecha.
Al crear un índice, Oracle se encarga de mantenerlo si los datos del campo asociado al índice
son borrados, actualizados o se inserta un dato nuevo en la tabla.
Por otra parte, debemos señalar que los índices pueden ser unívocos o no unívocos. Si un
índice asociado a un campo o columna de una tabla es único, Oracle comprobará cada vez
que se inserte un dato en dicha columna que el valor es único para cumplir la característica
del índice creado.
En cambio, si el índice que se desea crear es de tipo único sobre un campo que contiene
datos, que no son únicos, Oracle jamás podrá crear el índice.
Si se elimina el índice asociado a una o más de una columna de una tabla, los valores de
esas columnas en la tabla no se eliminan.
Anteriormente, hemos mencionado que los índices pueden ser creados sobre más de una
columna. Éstos deben ser creados cuando observamos en la tabla que siempre que se
consulta un campo “x” lo hacemos de otro campo “y”, por lo que deberíamos indexar ambos
campos. A estos índices se les denomina índices concatenados o índices compuestos.
Oracle crea de forma automática índices para las columnas clave o Primary Key.
También para las columnas sobre las cuales se aplique una restricción UNIQUE.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 209/226
203
Índices 11
11.3. REGLAS A LA HORA DE CREAR UN ÍNDICE
a.
Debemos indexar las columnas que formen parte del predicado de una instrucciónSQL, es decir las incluidas en un WHERE y AND. Si hacemos una select de los campos
nombre, apellidos y éstos no forman parte del predicado, entonces no debemos
indexar dichas columnas.
b. Indexar las columnas con valores distintos.
Podemos basarnos en: “Si el valor de una columna dada está en un 5% o menos del
resultado de las filas obtenidas, debemos indexar por esa columna”.
Por ejemplo: Una tabla con 50.000 registros. Un campo tiene valores idénticos cada
10.000 registros, podemos aconsejar que no se indexe por ese campo. Ahora bien, si
esa misma tabla tiene un campo cuyos valores son iguales cada 2.500 registros,
aconsejaremos que se indexe por ese campo.
c. Si de forma muy constante se hace uso de múltiples columnas en el predicado de una
instrucción SQL, debemos crear un índice concatenado o compuesto por esas
columnas.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 210/226
204
Índices 11
d. A la hora de crear un índice, se aconseja y recomienda que éste se cree en un
tablespaces distinto al Tablespace de Datos o Default, al igual que se recomienda que
estos tablespaces estén físicamente en distintos discos duros.
Ya hemos comentado que Oracle crea automáticamente índice para los campos claves
y únicos. Estos índices son asignados al tablespaces “Default” por lo que debemos
especificarle el tablespaces “Index”, bien en la instrucción SQL o mediante la
herramienta gráfica.
Es decir:
CREATE TABLE "USR_PRUEBA"."TABLA"
("C1" VARCHAR2(10) NOT NULL,
"C2" NUMBER(5) NOT NULL,
"C3" VARCHAR2(10) NOT NULL,
PRIMARY KEY("C1") USING INDEX TABLESPACE "tablespace_INDEX",
UNIQUE("C2") USING INDEX TABLESPACE "tablespace_INDEX");
Mediante la herramienta gráfica, entramos en la pestaña “Almacenamiento de
Restricciones”:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 211/226
205
Índices 11
Las consultas de datos a través de los campos o campo que formen la PRIMERY KEY
son mucho más rápidas ya que son índices únicos, por lo que el recorrido del objeto
index no es total.
e.
No es aconsejable hacer índices de columnas que puedan tomar valores nulos.
f. Toda tabla que supere las 1.000 filas debe tener un índice.
11.4. TIPOS DE ÍNDICES
Para poder localizar los datos, todas las filas de una tabla poseen un identificador o etiquetadenominado RowID (Identificador de Fila). Este identificador indica a Oracle la ubicación
física exacta de la fila (Datafile, Bloque de archivo y Fila de archivo).
Existen diferentes tipos de índices:
11.4.1. Índices de Tablas, Arbol-B, B-Tree o Normal
En ellos se almacena el contenido de la columna o columnas y el RowID de las filas. Su
organización se basa en la filosofía del Arbol-B, la cual consiste en una estructuracompuesta de nodos y ramas, manteniéndose ordenada para reducir la búsqueda.
Este tipo de índice, es el creado por Oracle para las columnas establecidas como Primery Key
y Unique. Si se desea crear un índice ÚNICO, Oracle recomienda crear la restricción en la
columna a nivel de tabla para mantener de esta forma la integridad con el resto de
restricciones que se hubiesen establecido sobre la tabla.
Por defecto, siempre que se crea un índice Oracle en el proceso de creación ordena la
columna asociada al índice. Podemos indicarle al crear el índice que no ordene dicha columnadentro del índice ya que esta está ordenada en la tabla.
11.4.2. Índices de Mapas de Bits
Es un tipo especial de índices de tabla que ayudan a optimizar las consultas a grandes
volúmenes de datos con columnas cuyos valores varían muy poco.
En lugar de almacenar los valores claves, se almacenan mapas de bits.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 212/226
206
Índices 11
Cada mapa de bits es un conjunto de bit (unos y ceros), que serán distintos para los valores
de columnas diferentes.
Por ejemplo:
Supongamos que tenemos una tabla indexada (Arbol-B) por la columna C3. Al consultar la
tabla tendríamos:
Select rownum, c3 from tabla;
ROWNUM C3
1 Informático
2 Abogado
3 Arquitecto
4 Abogado
5 Pedagogo
6 Informático
7 Abogado
8 Arquitecto
9 Informático
10 Arquitecto
11 Pedagogo
12 Abogado
13 Informático
14 Pedagogo
15 Arquitecto
Si hacemos una consulta ordenada por C3 y Rownum tendríamos:
ROWNUM C3
2 Abogado
4 Abogado
7 Abogado
12 Abogado
3 Arquitecto
8 Arquitecto
10 Arquitecto
15 Arquitecto
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 213/226
207
Índices 11
1 Informático
6 Informático
9 Informático
13 Informático
5 Pedagogo
11 Pedagogo
14 Pedagogo
Así pues, el objeto índice almacenaría en lugar del rownum el RowId y los valores de C3.
Si observamos el ejemplo, el campo C3 sólo posee 4 valores distintos. Si se crea un índice de
mapa de bit (Bitmap), en el cual cada mapa de bit almacena información de 12 filas de la
tabla, obtendríamos lo siguiente:
ROWNUM C3 rownumINICIAL rownumFINAL Bitmap
2,4,7,12 Abogado 1 12 0101.0010.0001
3,8,10 Arquitecto 1 12 0010.0001.0100
15 Arquitecto 13 24 0010.0000.0000
1,6,9 Informático 1 12 1000.0100.1000
16 Informático 13 24 0001.0000.00005,11 Pedagogo 1 12 0000.1000.0010
14 Pedagogo 13 24 0100.0000.0000
Por tanto, es aconsejable utilizar columnas cuyos valores sean poco variables, ya que
cuantos más valores diferentes existan, mayor será el tamaño del objeto índice y mayor
tiempo para poder ser gestionado.
11.4.3. Índices basados en una función
Son índices basados en expresiones creadas por el usuario aplicadas a las columnas de la
tabla.
El índice almacena el resultado de la expresión y el RowID de la fila.
Así pues, su creación se recomienda para aquellas operaciones de consulta y borrado que
usan frecuentemente la misma expresión en la cláusula WHERE.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 214/226
208
Índices 11
La expresión puede tener las siguientes características:
− Estar formada por operadores aritméticos.
− Funciones predefinidas (Concat, Lower, Upper, Rtrim, Ltrim,etc.) salvo las de
agrupamiento (Sum, Avg, Max, Min......).
− Una función creada por el usuario como objeto PL/SQL de tipo pública.
Por ejemplo: Si al consultar un campo o columna denominada “apellidos” de una tabla, en la
cláusula “where” siempre hacemos uso de UPPER (apellidos), podemos crea un índice de
función UPPER sobre esa columna.
11.4.4. Índices de Clave Inversa
Cuando el campo o columna de la tabla posee valores secuenciales (1,2,3,4.....), al crear el
índice de árbol-B, éste se coloca en una sola rama del árbol, por lo que podemos tener un
índice inservible.
Para ello, podemos especificar que sea de “Clave Inversa”.
11.4.5. Cluster o Agrupamiento
Oracle, nos permite almacenar físicamente en el mismo bloque de datos aquellas tablas a las
que se acceda conjuntamente. Para ello, se crea el cluster que contenga dichas tablas. De
esta forma, se reduce el número de E/S a disco. Los cluster de datos es una alternativa a la
indexación.
Las columnas relacionadas de las tablas se denominan clave de cluster.
Oracle nos proporciona dos tipos distintos de cluster:
a) Cluster de datos indexados. En este tipo, Oracle almacena físicamente una fila en el
cluster por cada valor de la clave de cluster.
Dicha clave de cluster se indexa utilizando un índice de cluster almacenándose sólo
una vez su valor para las distintas tablas del cluster.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 215/226
209
Índices 11
Ejemplo:
Tabla Pedidos = { ID_Pedido, Fecha_Pedido, ....., ID_Articulo_FK, .....}
Tabla Articulos = { ID_Articulo, Descripcion, Cantidad,.........}
Para consultar los artículos que componen los distintos pedidos, se crea una consulta
entre ambas tablas. Si se crea un cluster para dichas tablas, en cada bloque de datos
se almacenará:
Bloque de Datos 1:
ID_Pedido: 100
Todos los artículos incluidos en el pedido 100
Bloque de Datos 2:
ID_Pedido: 200
Todos los artículos incluidos en el pedido 200
..................
Y así sucesivamente.
b) Cluster de datos hash. En ellos, Oracle almacena físicamente una fila, según el
resultado de aplicar una función hash al valor clave del cluster (columnas relacionadas
de las tablas) de la fila.
Además, almacena en el mismo bloque todas aquellas filas que dan el mismo resultado
al aplicarles la función hash, es decir, esas filas forman un grupo hash.
Por tanto, a la hora de consultar datos en tablas con cluster hash, Oracle aplica dicha
función hash al criterio de selección, sabiendo perfectamente en qué bloque (grupo
hash) se encuentran los datos.
Consideraciones:
− Mal funcionamiento de los cluster hash en tablas con constantes actualizaciones e
inserciones.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 216/226
210
Índices 11
− Se aconseja el uso de cluster hash frente a cluster de índice.
− Mejoran el rendimiento en consultas que no incluyan rangos de filas, es decir,
condiciones o criterios de selección con condiciones de igualdad.
En definitiva, Los cluster resultan de gran utilidad cuando se consultan datos de varias tablas
con gran frecuencia, ya que dentro del cluster se almacenan dichas filas y todas ellas en el
mismo bloque de datos, por lo que aumenta el rendimiento y acceso al disco físico. Al igual
que ganamos en la selección, las inserciones, borrados y actualizaciones pueden llegar a ser
menos eficaces que en las tablas no agrupadas en cluster.
11.5. CREACIÓN DE ÍNDICES
Los privilegios relacionados con los índices que se pueden asignar a un usurario son los
siguientes:
CREATE ANY INDEX.
ALTER ANY INDEX.
DROP ANY INDEX.
Los índices al igual que otros objetos de una B.D. Oracle, podemos crearlos mediante una
sentencia SQL o mediante la Consola del Enterprise Manager.
Desde la consola o interfaz gráfica, una vez conectados, sólo tendremos que ir al apartado
Esquema y seleccionar la carpeta “Tabla” o la carpeta “Índices”. Si lo hacemos a través de
la carpeta “Tabla”, tendremos que posicionarnos en la carpeta “índice” de la tabla
correspondiente.
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 217/226
211
Índices 11
Haciendo clic derecho y seleccionando la opción “Crear”, nos aparece la ventana de “Creación
de índice”, cuyo aspecto es el que se muestra a continuación:
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 218/226
212
Índices 11
Las opciones que ofrece son las siguientes:
− Nombre.- Nombre del Objeto índice que se desea crear.
−
Esquema.- Nombre del esquema al que pertenece el objeto que vamos a crear.
− Tablespaces.- Nombre del Tablespaces dónde se almacenará el índice. Se aconseja y
recomienda que sea distinto al de datos y que físicamente esté en disco físico distinto.
− Índice.- En tabla, que es la opción por defecto o en Agrupamiento (Cluster).
− Esquema y Tabla.- Nombre del “Esquema” dónde se encuentra la “Tabla” para la quese crea el índice.
− Alias de tabla para utilizar la expresión de columna.- Alias para la columna de
expresión, que se activa al pulsar el botón “Agregar Expresión de Columna”.
− Listado de las columnas de la tabla sobre las cuales se puede aplicar el índice.
− Botones de “Agregar / Borrar Expresión de Columna”.- Nos permite agregar una
columna de expresión (COLUMN EXPRESSION).
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 219/226
213
Índices 11
Las opciones Único, Bitmap, No Ordenado, Revertir, excluyentes entre si, nos permiten
especificar la modalidad o tipo de índice.
Finalmente, algunos ejemplos de sentencia SQL que nos permiten crear índices son los que
se detallan a continuación:
TABLA: VEHICULO perteneciente al esquema “USR_PRUEBA”.
MATRICULA
MODELO
COLOR
CILINDRADA
FECHA_COMPRA
PRECIO_VENTA
Partimos de que existe un Tablespaces denominado "PRUEBA_INDEX" con “QUOTA no
limitada” para el usuario “usr_prueba”. (ALTER USER "USR_PRUEBA" QUOTA UNLIMITED
ON "PRUEBA_INDEX").
1. Índice de Tabla (Arbol-B) ÚNICO sobre la columna MATRICULA.
CREATE UNIQUE INDEX "USR_PRUEBA"."INDICE1"
ON " USR_PRUEBA"."VEHICULOS" ("MATRICULA")
TABLESPACE "PRUEBA_INDEX";
Si ya existe un índice sobre la columna, Oracle nos comunica el error: “ ORA-01408:
ésta lista de columnas ya está indexada”.
2. Índice de mapa de bit sobre la columna “COLOR”.
CREATE BITMAP INDEX "USR_PRUEBA"."INDICE2"
ON " USR_PRUEBA"."VEHICULOS" ("COLOR")
TABLESPACE "PRUEBA_INDEX";
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 220/226
214
Índices 11
3. Índice de basado en función.
CREATE INDEX "USR_PRUEBA"."INDICE3"
ON " USR_PRUEBA"."VEHICULOS" columna_funcion( UPPER(COLOR))
TABLESPACE "PRUEBA_INDEX";
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 221/226
215
Índices 11
− Los índices permiten un acceso más rápido a los datos.
− Oracle genera índices de forma automática para las columnas consideradas
claves primarias y únicas.
− Los índices deben ir a un tablespace distinto al default.
r e c
u e r d e
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 222/226
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 223/226
217
A
ADD DATAFILE. Cláusula que nos sirve para añadir un fichero de datos a un
tablespace.
AUTOEXTEND. Cláusula que nos permite especificar el crecimiento automático
de los ficheros de datos.
B
BASE DE DATOS. Conjunto de Objetos tratados por un sistema gestor de Base
de Datos. Su funcionalidad es almacenar la información.
BLOQUE LOGICO. Unidad lógica mínima de almacenamiento para los datos,
directamente ligado al bloque físico del sistema operativo.
C
CAMPOS. Unidad Lógica mínima de almacenamiento de un registro de la B.D.
COALESCE. Cláusula usada para realizar eliminar la desfragmentación en los
tablespaces.
COLUMNAS. Ver CAMPO.
CONTROLFILE. Ver FICHEROS DE CONTROL.
D
DATAFILE. Ver FICHEROS DE DATOS.
DBA. Administrador de la B.D.
DEALLOCATE UNUSED. Cláusula que libera espacio de los segmentos de datos
e índices.
E
ESQUEMA. Ver SCHEMA.
EXTENSION. Conjunto de Bloques contiguos.
F
FICHEROS DE CONTROL. Contienen la información necesaria para arrancar la
B.D. Como nombre, fecha de creación, ficheros asociados, etc.
g l
o s a r i o
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 224/226
218
FICHEROS DE DATOS. Ficheros físicos que almacenan la información de los
datos de la B.D.
FICHEROS DE REDO LOG. Nos permiten mantener consistente los datos de la
B.D. en caso de fallo o error.
H
HAVING. Cláusula de una consulta que se aplica a los campos sobre los cuales
existe una función de agrupamiento.
I
INITIAL. Tamaño de la extensión inicial asignada en el momento de creación
del segmento.
INVALID. Estado en el que puede quedar cualquier objeto de la B.D.
J
JAR. Extensión de programas comprimidos en JAVA.
JOIN. Uniones entre consultas.
P
PCTFREE. Porcentaje de espacio en un bloque de dato, reservado para las
modificaciones de los datos almacenados en dicho bloque.
PCTINCREASE. Porcentaje de crecimiento de la siguientes extensión con
respecto a la anterior asignada.
PCTUSED. Porcentaje que determina cuando un bloque de datos es candidato
para recibir inserciones.
PL/SQL. Lenguaje de programación utilizado para acceder a la información
existente en una B.D. relacional mediante sentencias SQL. (Procedural
Language/Structured Query Language).
PRIVILEGIOS. Permisos concedidos a los usuarios para poder realizar tareas en
la B.D.
Q
QUERY. Consulta a la D.B.
g l
o s a r i o
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 225/226
219
R
REDOLOG. Ver FICHEROS REDO LOG.
ROL. Conjunto de privilegios concedidos a un usuario.
ROWID. Identificador único por cada fila de una tabla de la B.D.
RUTA DE ACCESO. Ubicación en disco duro dónde se instalarán los programas
de instalación de Oracle.
S
SCHEMA. Objeto de la B.D. dónde se recoge de forma lógica la estructura de un
diseño relacional.
SEGMETOS DE DATOS. Conjunto de EXTENSIONES que almacena información
de las tablas.
SEGMETOS DE INDICE. Conjunto de EXTENSIONES que almacena información
de los índices de las tablas.
SEGMENTO TEMPORAL. Conjunto de EXTENSIONES creadas por Oracle para
operaciones intermedias en operaciones SQL.
S.G.D.B. Un Sistema Gestor de Base de Datos es un programa informático que
nos permite manejar Bases de Datos. Ejemplo: Oracle, SQL Server, Informix,
DB2, MySQL, Access.
SID. identificador de la instancia de la B.D.
S.Q.L. Conjuntos de sentencias usadas para manejar los datos de una B.D.
(Structured Query Language).
SYS. Usuario propietario de las tablas del Diccionario de Datos.
SYSTEM. Usuario Administrador de la B.D.
T
TABLA. Estructura lógica para almacenar los datos.
TABLESPACE. Estructura lógica de almacenamiento, con ficheros de datos
físicos asociados a ella dónde se almacena información.
TRANSACCION. Conjunto de sentencias que se ejecutan o se deshacen como
un bloque hacia la B.D.
TRUNCATE. Sentencia S.Q.L. que libera extensiones de datos e índices
asociados a la tabla.
g l
o s a r i o
7/21/2019 Manual - Administracion de Bases de Datos Con Oracle 9i
http://slidepdf.com/reader/full/manual-administracion-de-bases-de-datos-con-oracle-9i 226/226
U
UNLIMITED. Cláusula que permite a un usuario a hacer uso de todo el espacio
asignado a un tablespaces sin ningún tipo de restricción.
USUARIOS. Persona autorizada a hacer uso de determinada información
existente en la B.D.
s a r i o