SQL
Parte II
Capítulos 1. Sentencias Básicas de SQL
2. Restricción y Ordenamiento de Datos
3. Funciones de SQL
4. Obteniendo Datos desde Múltiples Tablas
5. Obteniendo Datos usando Funciones de Agrupación
6. Sub-Consultas
7. Manejo de los Valores de Entrada con SQL*Plus
8. Manipulando Datos
9. Creación y Manipulación de Tablas
10. Restricciones de Integridad
11. Vistas
12. Secuencias, Índices y Sinónimos
Capítulo 1
Sentencias Básicas de SQL
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Objetivos
• Mostrar las capacidades que tienen las sentencias
SQL
• Ejecutar algunas sentencias básicas
• Diferenciar entre las sentencias SQL y los comandos
SQL*Plus
SQL
(Structured Query Language)
• Es un Lenguaje Estructurado de Consultas
• Permite la comunicación con el DBMS y tiene las
siguientes ventajas:
– Eficiente
– Fácil de aprender y usar
– Funcionalmente completo (SQL permite definir, recuperar y
manipular los datos en las tablas)
Sentencias SQL
Sentecias
SQL
Control de
Transacciones
COMMIT
ROLLBACK
SAVEPOINT
Lenguaje de
Definición de
Datos
(DDL)
CREATE
ALTER
DROP
RENAME
TRUNCATE
Lenguaje de
Manipulación
de Datos
(DML)
INSERT
UPDATE
DELETE
Recuperación
de Datos
SELECT
Lenguaje de
Control de
Cambios
(DCL)
GRANT
REVOKE
Características de una Sentencia
SELECT
Selección Proyección
Join
Tabla 1
Tabla 1 Tabla 1
Tabla 2
Estructura de una Sentencia SQL
SELECT [distinct] {*| columnas [alias]| ...}
FROM tabla
[WHERE condicion(es)];
• SELECT identifica que columnas (proyección)
• FROM identifica que tabla (join)
• WHERE restringe la consulta (selección, join)
Seleccionando Columnas
• Se pueden mostrar todas las columnas de una tabla
usando el asterisco (*)
SQL> SELECT * SQL> SELECT cod,nombre,apellido,
2 FROM empleados; 2 salario, fecha_nac,
3 lugar_nac, suc_cod
4 FROM empleados;
COD NOMBRE APELLIDO SALARIO FECHA_NAC LUGAR_NAC SUC_COD
-------------------------------------------------------------
5 JUAN PEREZ 500 12-NOV-70 COCHABAMBA 3
7 MARIA SILEZ 350 08-ABR-75 LA PAZ 2
9 RICARDO QUIROGA 120 25-JUN-77 COCHABAMBA 7
Seleccionando Columnas
• Para seleccionar columnas específicas se tiene
que especificar en la parte del SELECT los
nombres de las columnas que interesan
SQL> SELECT nombre, apellido
2 FROM empleados;
NOMBRE APELLIDO
----------------------------------
JUAN PEREZ
MARIA SILEZ
RICARDO QUIROGA
Definición de una Columna Alias
• Renombra el título de una columna
• Es útil con cálculos
• Es lo que sigue después del nombre de una
columna; opcionalmente se puede escribir AS entre
el nombre de la columna y el alias
• Requiere doble comilla si el alias contiene espacios o
caracteres especiales
Columnas Alias
SQL> SELECT nombre “Nombre”, salario “Sueldo”,
2 lugar_nac “Lugar de Nacimiento”
3 FROM empleados ;
Nombre Sueldo Lugar de Nacimiento
--------------------------------------------------
JUAN 500 COCHABAMBA
MARIA 350 LA PAZ
RICARDO 120 COCHABAMBA
Definición de una Tabla Alias
• Renombra el título de una tabla
• Es un sobrenombre que se le da a una tabla y que
debe ser único para toda la consulta
• Es lo que sigue después del nombre de la tabla
• Es útil cuando se quiera recuperar datos de más de
una tabla
Tablas Alias
SQL> SELECT emp.nombre, emp.apellido,
2 emp.salario, emp.lugar_nac
3 FROM empleados emp
4 WHERE emp.lugar_nac = ’COCHABAMBA’;
NOMBRE APELLIDO SALARIO LUGAR_NAC
--------------------------------------------------
JUAN PEREZ 500 COCHABAMBA
RICARDO QUIROGA 120 COCHABAMBA
GUSTAVO HERRERA 300 COCHABAMBA
...
Definición del Valor NULL
• NULL es un valor desconocido o inaplicable
• NULL no es lo mismo que cero tampoco un espacio
en blanco
Tipos de Datos
Las columnas pueden ser de los siguientes tipos de
datos:
Tipo Dato Descripción
NUMBER(p,s) Valor del numero tiene como máximo p
dígitos y s dígitos para redondeo
VARCHAR2(s) Valor de caracteres variable con longitud
máxima de s
DATE Fecha y tiempo comprendida entre el 1 de
enero del 4712 A.C. y el 31 de diciembre del
9999 D.C.
CHAR(s) Valor de caracteres con longitud máxima de
s
Tipo Number(p,e)
• Donde p (precisión) es el numero total de dígitos,
máximo 38 dígitos de precisión y e (escala) indica
donde se produce el redondeo
• Contiene dígitos en el rango de 1.0x10-129 a
9.99x10124
• Acepta números positivos o negativos
• Contiene punto decimal
• Se los puede escribir con notación regular o científica
Expresiones Aritméticas
Se pueden crear expresiones aritméticas sobre
Números y Fechas (solo + y -) usando operadores
aritméticos
Operador Descripción
+ Adición
- Sustracción
* Multiplicación
/ División
Operadores Aritméticos
SQL> SELECT nombre, salario, salario+20
2 FROM empleados;
NOMBRE SALARIO SALARIO+20
------------------------------------
JUAN 500 520
MARIA 350 370
RICARDO 120 140
Tipo Varchar2(s)
• Permite almacenar cadenas de caracteres de
longitud s
• La longitud es variable a diferencia del tipo Char que
es fija
• Funciones
– Concatenación (||)
– To_Char
– Length
– ...
Operador Concatenación
• Permite concatenar dos o más cadenas de caracteres
• Es representado por dos líneas verticales (||)
SQL> SELECT nombre||apellido AS “Nombres”
2 FROM empleados;
Nombres
--------------------------------
JUANPEREZ
MARIASILEZ
RICARDOQUIROGA
Funcion To_Char
• Convierte un número o fecha a una cadena de caracteres VARCHAR2 de acuerdo al formato especificado en la máscara
SQL> SELECT nombre, TO_CHAR(fecha_nac,’DD-MM-YY’)
2 FROM empleados;
NOMBRE TO_CHAR(FECHA_NAC,’DD-MM-YY’)
--------------------------------------------
JUAN 12-11-70
MARIA 08-04-75
RICARDO 25-06-77
Cadena de Caracteres Literales
• Un literal es un valor explícito (numérico, caracter, cadena o booleano) que no esta representado por un identificador
• En el caso de un literal de cadena es una secuencia de caracteres encerrados entre comillas simples
SQL> SELECT nombre||’ ’||’tiene un
2 salario de’||’ ’||salario
3 AS “Detalle de Empleados”
4 FROM empleados;
Detalle de Empleados
--------------------------------
JUAN tiene un salario de 500
MARIA tiene un salario de 350
RICARDO tiene un salario de 120
Cadena de Caracteres Literales
Tipo Date
• Almacena una fecha válida según el formato
establecido en una máscara
• Fecha y tiempo comprendida entre el 1 de enero del
4712 A.C. y el 31 de diciembre del 9999 D.C.
• Todo literal que sea fecha debe de estar encerrado
entre comillas simples
• Funciones
– To_Date
Funcion To_Date
• Convierte una cadena de caracteres representando una
fecha a un valor fecha de acuerdo al formato especificado
en la máscara (si el formato es omitido, el formato será
DD-MON-YY)
SQL> SELECT nombre||’ estará de vacación el ’||
2 TO_DATE(’20-DIC-2002’,’DD-MM-YY’) AS vacaciones
3 FROM empleados;
VACACIONES
--------------------------------------------
JUAN estará de vacación el 20-12-02
MARIA estará de vacación el 20-12-02
RICARDO estará de vacación el 20-12-02
Los resultados obtenidos de las consultas son todas las
filas, incluyendo duplicados
SQL>SELECT lugar_nac SQL> SELECT ALL lugar_nac
2 FROM empleados; 2 FROM empleados;
LUGAR_NAC
---------------------------
COCHABAMBA
LA PAZ
COCHABAMBA
Filas Duplicadas
Se pueden eliminar las filas duplicadas escribiendo DISTINCT en la cláusula SELECT
SQL> SELECT DISTINCT lugar_nac
2 FROM empleados;
LUGAR_NAC
---------------------------
COCHABAMBA
LA PAZ
Eliminación de Filas Duplicadas
SQL*Plus
• SQL*Plus es una herramienta de Oracle que
reconoce y acepta las sentencias SQL en el Servidor
Oracle para su ejecución
• Contiene además su propio lenguaje de comandos
Capacidades de SQL*Plus
• Describe la estructura de las tablas
• Permite ejecutar sentencias SQL
• Guarda y añade sentencias SQL en archivos
• Ejecuta archivos guardados
• Carga comandos desde un archivo al buffer para
editar
Interacción entre SQL y SQL*Plus
SQL*Plus
Buffer
Servidor
BD
Sentencias SQL Sentencias SQL
ComandosSQL*Plus
ResultadoConsulta
Reporte conFormato
Sentencias SQL Vs. Comandos
SQL*Plus
SQL
• Un lenguaje
• ANSI standard
• Palabras reservadas no
pueden ser abreviadas
• Sentencias manipulan
datos y definiciones de
tablas en la base de
datos
SQL*Plus
• Un ambiente
• Propio de Oracle
• Palabras reservadas
pueden ser abreviadas
• Comandos no permiten
la manipulación de
valores en la base de
datos
• Desde un Ambiente Windows:
• Desde una línea de comandos:
sqlplus[username[/password[@database]]]
Ingreso SQL*Plus
Mostrando la Estructura de las
Tablas
Para describir la estructura de una tabla se usa el comando de SQL*Plus DESCRIBE
DESC[RIBE] tabla
SQL> DESCRIBE empleados;
Name Null? Type
---------------------------------------------
COD NOT NULL VARCHAR2(6)
NOMBRE NOT NULL VARCHAR2(20)
APELLIDO NOT NULL VARCHAR2(20)
SALARIO NUMBER(5)
FECHA_NAC DATE
LUGAR_NAC VARCHAR2(20)
SUC_COD NOT NULL VARCHAR2(6)
Mostrando la Estructura de las
Tablas
Salida de SQL*Plus
• Para salir de SQL*Plus se tiene el comando EXIT
SQL> EXIT;
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 2
Restricción y Ordenamiento de Datos
Objetivos
• Limitar las filas recuperadas de la ejecución de una
consulta
• Ordenar las filas recuperadas de la ejecución de una
consulta
Uso de la Cláusula WHERE
• Permite seleccionar determinadas filas de una tabla
que cumplan con una condición
SELECT [distinct] { * | columnas [alias], ...}
FROM tabla
[WHERE condicion(es)];
• La cláusula WHERE debe de estar inmediatamente después de la cláusula FROM
SQL> SELECT nombre, apellido
2 FROM empleados
3 WHERE nombre = ‘MARIA’;
NOMBRE APELLIDO
--------------------------------
MARIA SILEZ
Uso de la Cláusula WHERE
• Puede estar compuesta por varias condiciones
SQL> SELECT nombre, apellido, salario
2 FROM empleados
3 WHERE nombre = ’RICARDO’
4 AND suc_cod = ’7’;
NOMBRE APELLIDO SALARIO
-------------------------------------
RICARDO QUIROGA 120
Uso de la Cláusula WHERE
Condición(es) en el WHERE
• Una condición en la cláusula WHERE esta
compuesta por:
• Donde la condición podrá ser verdadero o falso, y
de acuerdo a eso se seleccionarán sólo las filas
que cumplen la condición
• En una condición los valores de caracter son
sensibles a las mayúsculas y minúsculas, y los
valores de fechas son sensibles al formato
Columna
Condición
Columna, Constante,Lista de Valores
Operador deComparación
Operadores de Comparación
Operador Descripción
= Igual a
> Mayor que
>= Mayor que o igual a
< Menor que
<= Menor que o igual a
<> Diferente
Los operadores de comparación son usados en
condiciones que comparan una expresión con otra
Otros Operadores de Comparación
• BETWEEN se usa para mostrar las filas
...AND... basándose en un rango de
valores
• IN (lista) se usa para ir probando o
comparando con los valores de la
lista
• LIKE se usa para realizar búsquedas
sensibles
- % denota cero o mas caracteres
- _ denota un caracter
• IS NULL se usa para probar con valores
nulos
• Se usa para recuperar filas basándose en un rango de valores
• En el rango se tiene que especificar el valor mínimo y el valor máximo
SQL> SELECT nombre, salario
2 FROM empleados
3 WHERE salario BETWEEN 350 AND 1000;
NOMBRE SALARIO
-------------------------
JUAN 500
MARIA 350
JOSE 800
...
Operador BETWEEN…AND…
Valor mínimo Valor máximo
• Se usa para ir probando o comparando con los valores de la lista
• Este operador puede ser usado con cualquier tipo
SQL> SELECT nombre, apellido, lugar_nac
2 FROM empleados
3 WHERE suc_cod IN (’2’,’3’,’7’);
NOMBRE APELLIDO LUGAR_NAC
--------------------------------------
JUAN PEREZ COCHABAMBA
MARIA SILEZ LA PAZ
RICARDO QUIROGA COCHABAMBA
...
Operador IN (lista)
• Se usa para realizar búsquedas sensibles
- % Equivale a una cadena de caracteres de longitud
comprendida entre 0 y n
- _ Equivale a un único caracter
Operador LIKE
SQL>SELECT nombre
2 FROM empleados
3 WHERE nombre LIKE (’J%’);
NOMBRE
-------------------
JUAN
JOSE
...
SQL> SELECT nombre
2 FROM empleados
3 WHERE nombre LIKE (’_A%’);
NOMBRE
-------------------
MARIA
JANNETH
...
• Se usa para probar con valores nulos
• No se podría usar un = porque un valor nulo no es mismo que un número o un caracter o cualquier tipo de dato
SQL> SELECT nombre, lugar_nac
2 FROM empleados
3 WHERE lugar_nac IS NULL;
NOMBRE LUGAR_NAC
------------------------------
JOSE
FERNANDO
...
Operador IS NULL
Operadores Lógicos
Operador Descripción
AND Retorna TRUE si ambas
condiciones son verdaderas
OR Retorna TRUE si por lo
menos una condición es
verdadera
NOT Retorna TRUE si la
condición es falsa
Un operador lógico combina el resultado de dos
condiciones para producir un solo resultado
basándose en ambas, o para invertir el resultado de
una sola condición
• Retorna TRUE si ambas condiciones son verdaderas
SQL> SELECT nombre, apellido, salario
2 FROM empleados
3 WHERE lugar_nac = ’COCHABAMBA’
4 AND suc_cod = ’3’;
NOMBRE APELLIDO SALARIO
-------------------------------------
JUAN PEREZ 500
GUSTAVO HERRERA 300
JIMENA TORRICO 350
...
Operador AND
• Retorna TRUE si por lo menos una condición es verdadera
SQL> SELECT nombre, apellido, lugar_nac, suc_cod
2 FROM empleados
3 WHERE lugar_nac = ’LA PAZ’
4 OR suc_cod = ’3’;
NOMBRE APELLIDO LUGAR_NAC SUC_COD
---------------------------------------------
JUAN PEREZ COCHABAMBA 3
MARIA SILEZ LA PAZ 2
JIMENA TORRICO SUCRE 3
...
Operador OR
• Retorna TRUE si la condición es falsa
SQL> SELECT nombre, apellido, lugar_nac
2 FROM empleados
3 WHERE lugar_nac NOT IN(’COCHABAMBA’,’LA PAZ’);
NOMBRE APELLIDO LUGAR_NAC
-------------------------------------
JIMENA TORRICO SUCRE
ALVARO VARGAS ORURO
...
Operador NOT
• Ordena el resultado obtenido al ejecutar una consulta - ASC: en orden ascendente, por defecto
- DESC: en orden descendente
SELECT expr
FROM tabla
[WHERE condicion(es)]
[ORDER BY {columna, expr}[ASC|DESC]];
Uso de la Cláusula ORDER BY
• La cláusula ORDER BY siempre debe de estar al final de una sentencia SELECT
• Se puede ordenar por una columna que no esté en la lista SELECT
• Se puede ordenar no solo por una columna sino también por varias, vale decir:
ORDER BY columna1, columna2, ...
• Se puede especificar una expresión o un alias para ordenar
ORDER BY alias1, alias2,...
ORDER BY columna1||columna2
ORDER BY columna1*columna2
Uso de la Cláusula ORDER BY
SQL> SELECT nombre, apellido, salario
2 FROM empleados
3 ORDER BY salario;
SQL> SELECT nombre, apellido, lugar_nac, salario
2 FROM empleados
3 ORDER BY nombre, salario;
SQL> SELECT nombre, apellido, salario*12 sueldo
2 FROM empleados
3 ORDER BY sueldo;
SQL> SELECT nombre, apellido, lugar_nac, salario
2 FROM empleados
3 ORDER BY nombre||TO_CHAR(salario);
Uso de la Cláusula ORDER BY
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 3
Funciones de SQL
Objetivos
• Mostrar los dos tipos de funciones disponibles en
SQL
• Usar funciones para manipular caracteres, números
y fechas en las sentencias SQL
• Mostrar el uso de las funciones de conversión
Funciones de SQL • Las funciones son una característica potente de SQL, y
pueden ser usadas para:
- Realizar cálculos sobre datos
- Modificar datos individualmente
- Dar formato a las fechas y números para ser mostrados
- Convertir tipos de datos entre columnas
• Existen dos distintos tipos de funciones
Funciones
Funciones paramás de una Fila
Funciones parauna Fila
Funciones para una Fila • Aceptan varios argumentos y retornan solo un valor
• Trabajan con cada fila resultante
• Retornan un resultado por fila
• Pueden modificar los tipos de datos
• Pueden estar anidadas
nombreFuncion(columna|expresion,[arg1, arg2,...])
• Un argumento puede ser:
- Una constante
- Un valor variable
- Un nombre de columna
- Una expresión
Funciones para una Fila
Funciones para una Fila
Conversiones
FechasNúmeros
Caracteres
Funciones para Manipular Caracteres Se dividen en:
• Funciones para Conversión de Mayúsculas-Minúsculas
- LOWER
- UPPER
- INITCAP
• Funciones para Manipulación de Cadenas de Caracteres
- CONCAT
- SUBSTR
- LENGTH
- INSTR
- LPAD
Funciones para Convertir
Mayúsculas-Minúsculas
Función Descripción
LOWER(columna|expresion) Convierte una cadena de
caracteres a minúsculas
UPPER(columna|expresion) Convierte una cadena de
caracteres a mayúsculas
INITCAP(columna|expresion) Convierte el primer
caracter de cada palabra
a mayúscula y los demás
a minúsculas
Función Resultado
LOWER(‘CADENA caracter’)
UPPER(‘CADENA caracter’)
INITCAP(‘CADENA caracter’)
cadena caracter
CADENA CARACTER
Cadena Caracter
Funciones para Convertir
Mayúsculas-Minúsculas
Funciones para Manipulación de
Cadenas de Caracteres Función Descripción
CONCAT(columna1|expresion1,
columna2|expresion2)
Permite concatenar dos cadenas
de caracteres
SUBSTR(columna|expresion,m[,n]) Retorna caracteres específicos
empezando desde la posición m,
n caracteres adelante
LENGTH(columna|expresion) Retorna la longitud de la cadena
de caracteres
INSTR(columna|expresion,c) Retorna la posición del caracter c
en la cadena
LPAD(columna|expresion,n,’cadena’) Si es que el primer argumento
tiene una longitud menor que n
se completará desde derecha a
izquierda con ‘cadena’
Funciones para Manipulación de
Cadenas de Caracteres
Función Resultado
CONCAT(‘Cadena’, ‘Grande’)
SUBSTR(‘Cadena’, 1, 3)
LENGTH(‘Cadena’)
INSTR(‘Cadena’, ‘e’)
LPAD(salario,10,’*’)
CadenaGrande
Cad
6
4
*******500
Funciones para Manipular Números
Función Descripción
ROUND(columna|
expresión, n)
Redondea la columna,
expresión o valor a n lugares
decimales o si n es omitido,
sin lugares decimales
TRUNC(columna|
expresión, n)
Trunca la columna, expresión
o valor a n lugares decimales
o si n es omitido, sin lugares
decimales
MOD (m , n) Retorna el residuo de la
división de m entre n
Funciones para Manipular Números
Función Resultado
ROUND(24.416, 2)
TRUNC(24.416, 2)
MOD(150, 20)
24.42
24.41
10
Funciones para Manipular Fechas
Función Descripción
MONTHS_BETWEEN Numero de meses entre dos
fechas
ADD_MONTHS Añade un mes calendario a
una fecha
NEXT_DAY Siguiente día en una fecha
especifica
LAST_DAY Ultimo día del mes
ROUND Redondea una fecha
TRUNC Trunca un fecha
• MONTHS_BETWEEN(fecha1, fecha2)
Encuentra el número de meses entre la fecha1 y la fecha2. El
resultado puede ser positivo como negativo. Si la fecha1 es mayor
que la fecha2, es resultado es positivo, si es menor el resultado
será negativo
• ADD_MONTHS(fecha, n)
Añade n números de meses calendario a la fecha. El valor de n
debe ser entero y puede ser negativo
• NEXT_DAY(fecha, ‘caracter’)
Encuentra la fecha del siguiente día de la semana especificado en
‘caracter’ a partir de fecha
• LAST_DAY(fecha)
Encuentra la fecha del último día del mes que contiene fecha
Funciones para Manipular Fechas
Funciones para Manipular Fechas
Función Resultado
MONTHS_BETWEEN(’01-SEP-95’,’11-JAN-94’)
ADD_MONTHS(’05-JAN-99’, 6)
NEXT_DAY(’14-SEP-98’,’FRIDAY’)
LAST_DAY(’09-NOV-98’)
19.6774194
‘05-JUL-99’
’18-SEP-98’
’30-NOV-98’
• ROUND(fecha[,’fmt’])
Retorna la fecha redondeada a la unidad especificada por el
formato ‘fmt’ (que puede ser años, meses). Si el formato ‘fmt’
es omitido, la fecha será redondeada al día más cercano
• TRUNC(fecha[,’fmt’])
Retorna la fecha con el tiempo truncado a la unidad
especificada por el formato ‘fmt’ (que puede ser años,
meses). Si el formato ‘fmt’ es omitido, la fecha será truncada
al día más cercano
Funciones para Manipular Fechas
Funciones para Manipular Fechas
Función Resultado
ROUND(’25-JUL-98’,’MONTH’)
ROUND(’25-JUL-98’,’YEAR’)
TRUNC(’25-JUL-98’,’MONTH’)
TRUNC(’25-JUL-98’,’YEAR’)
’01-AUG-98’
’01-JAN-99’
’01-JUL-98’
’01-JAN-98’
Funciones para Realizar Conversiones
Función Descripción
TO_CHAR(numero|fecha[,’fmt’]) Convierte un numero o fecha a una
cadena de caracteres VARCHAR2
con el formato fmt
TO_NUMBER(cadena[,’fmt’]) Convierte una cadena de
caracteres conteniendo dígitos a
un numero con el formato opcional
fmt
TO_DATE(cadena[,’fmt’]) Convierte una cadena de
caracteres representando una
fecha a un valor fecha de acuerdo
al formato fmt especificado(si el
formato fmt es omitido, el formato
será DD-MON-YY)
Funciones para Realizar Conversiones
Función Resultado
TO_CHAR(8500, ’99,999’)
TO_CHAR(’05-NOV-99’, ’DD-MM-YYYY’)
TO_NUMBER(‘1435’)
TO_DATE(’06-02-97’,’DD/MON/YYYY’)
‘8,500’
’05-11-1999’
1435
06/FEB/1997
Funciones Anidadas
• Las funciones pueden estar anidadas en cualquier
nivel
• Las funciones anidadas son evaluadas desde del
nivel mas interno al mas externo
F3(F2(F1(columna,arg1),arg2),arg3)
Resultado1
Resultado2
Resultado3
Función Resultado
ADD_MONTHS(TO_DATE(’05-JAN-99’,’DD-MON-YY’ ),6)
TO_CHAR(NEXT_DAY(’14-SEP-98’,’FRIDAY’),’DD/MM/YYYY’)
TO_CHAR(ROUND(’25-JUL-98’,’MONTH’),’DD-MM-YYYY’)
‘05-JUL-99’
’18/09/1998’
’01-08-1998’
Funciones Anidadas
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 4
Obteniendo Datos desde Múltiples Tablas
Objetivos
• Escribir sentencias SQL para acceder a datos de
mas de una tabla usando joins internos y non-
equijoins
• Ver los datos que generalmente no satisfacen una
condición join usando el join externo
• Hacer join de una tabla a si misma
Obteniendo Datos desde
Múltiples Tablas
COD NOMBRE APELLIDO ... SUC_COD
-------------------------------------
5 JUAN PEREZ 3
7 MARIA SILEZ 2
9 RICARDO QUIROGA 7
14 JIMENA TORRICO 3
18 JOSE VARGAS 3
...
EMPLEADOS
COD DESCR TELEFONO ...
-------------------------------------
1 SUD 225874
2 NORTE 501458
3 CENTRAL 255656
7 SACABA 298756
9 QUILLACOLLO 268751
...
SUCURSALES
COD NOMBRE SUC_COD DESCR
-------------------------------------
5 JUAN 3 CENTRAL
7 MARIA 2 NORTE
9 RICARDO 7 SACABA
14 JIMENA 3 CENTRAL
18 JOSE 3 CENTRAL
...
Uso de la Sentencia JOIN
Se usa un join para realizar una consulta a más de
una tabla
SELECT tabla1.columna, tabla2.columna
FROM tabla1, tabla2
WHERE tabla1.columna1 = tabla2.columna2;
• Se escribe la condición join en la parte de la cláusula
WHERE
• En necesario el nombre de la columna con el nombre
de la tabla cuando el mismo nombre de columna
aparece en mas de una tabla
Tipos de Join
Join Descripción
Join Interno =
Non-Equijoin BETWEEN
Join Externo (+)
Self Join Con una tabla
Join Interno
COD NOMBRE APELLIDO ... SUC_COD
-------------------------------------
5 JUAN PEREZ 3
7 MARIA SILEZ 2
9 RICARDO QUIROGA 7
14 JIMENA TORRICO 3
18 JOSE VARGAS 3
...
EMPLEADOS
COD DESCR TELEFONO ...
-------------------------------------
1 SUD 225874
2 NORTE 501458
3 CENTRAL 255656
7 SACABA 298756
9 QUILLACOLLO 268751
...
SUCURSALES
Llave Foranea Llave Primaria
Es la relación que existe entre dos tablas a través de
sus llaves primarias y foráneas
Join Interno
SQL> SELECT empleados.nombre, empleados.apellido
2 empleados.suc_cod, sucursales.cod,
3 sucursales.descr
4 FROM empleados, sucursales
5 WHERE empleados.suc_cod = sucursales.cod;
NOMBRE APELLIDO SUC_COD COD DESCR
-------------------------------------------------
JUAN PEREZ 3 3 CENTRAL
MARIA SILEZ 2 2 NORTE
RICARDO QUIROGA 7 7 SACABA
...
Non-Equijoin
COD NOMBRE APELLIDO SALARIO FECHA_NAC ...
-----------------------------------------------
5 JUAN PEREZ 500 12-NOV-70
7 MARIA SILEZ 350 08-ABR-75
9 RICARDO QUIROGA 120 25-JUN-77
14 JIMENA TORRICO 380 01-DIC-76
18 JOSE VARGAS 800 19-JUN-78
...
EMPLEADOS
CATEGORIA FECHA_MIN FECHA-MAX AUMENTO
-------------------------------------------
1 01-JAN-70 31-DIC-75 15
2 01-JAN-76 31-DIC-81 10
3 01-JAN-82 31-DIC-87 5
4 01-JAN-88 31-DIC-93 1
...
BONOS
La fecha de nacimiento de la tabla EMPLEADOS
esta entre la fecha mínima y la fecha máxima en
la tabla BONOS
Non-Equijoin
SQL> SELECT e.nombre, e.apellido, e.fecha_nac,
2 e.salario, b.categoria, b.aumento
3 FROM empleados e, bonos b
4 WHERE e.fecha_nac
5 BETWEEN b.fecha_min AND b.fecha_max;
NOMBRE APELLIDO FECHA_NAC SALARIO CATEGORIA AUMENTO
------------------------------------------------------------
JUAN PEREZ 12-NOV-70 500 1 15
MARIA SILEZ 08-ABR-75 350 1 15
...
Join Externo
COD NOMBRE APELLIDO ... SUC_COD
-------------------------------------
5 JUAN PEREZ 3
7 MARIA SILEZ 2
9 RICARDO QUIROGA 7
14 JIMENA TORRICO 3
18 JOSE VARGAS 3
...
EMPLEADOS
COD DESCR TELEFONO ...
-------------------------------------
1 SUD 225874
2 NORTE 501458
3 CENTRAL 255656
7 SACABA 298756
...
9 QUILLACOLLO 268751
SUCURSALES
No existen empleados en la
sucursal 9 QUILLACOLLO
Join Externo
• Se usa el join externo para ver también las filas que
no satisfacen la condición
• Es representado con el signo (+)
SQL> SELECT e.nombre, s.cod, s.descr
2 FROM empleados e, sucursales s
3 WHERE e.suc_cod (+) = s.cod
4 ORDER BY e.suc_cod;
NOMBRE COD DESCR
---------------------------------------------
MARIA 2 NORTE
JUAN 3 CENTRAL
RICARDO 7 SACABA
...
9 QUILLACOLLO
COD CI NOMBRE APELLIDO ... CLI_COD
----------------------------------------
14 4587962 GUSTAVO ALVAREZ
19 4788965 MARCELA SANCHEZ
22 4155877 CARLOS ZENTENO
35 3587442 HERMAN CACERES
...
CLIENTE(TITULAR) CLIENTE(AUTORIZADO)
Cod en la tabla CLIENTE(TITULAR) es
el mismo que cli_cod en la tabla
CLIENTE(AUTORIZADO)
COD CI NOMBRE APELLIDO ... CLI_COD
----------------------------------------
31 4854896 KAREN ACHA 10
41 4755166 MARCO ALCOCER 14
66 4877514 PAOLA CACERES 35
87 4621547 LUIS CACERES 35
...
Self join
Self join SQL> SELECT autorizado.nombre||’ es autorizado por
2 ’||titular.nombre
3 FROM clientes titular, clientes autorizado
4 WHERE autorizado.cli_cod = titular.cod;
AUTORIZADO.NOMBRE||’ ES AUTORIZADO POR ’||TITULAR.NOMBRE
---------------------------------------------------------
KAREN es autorizado por MARISOL
MARCO es autorizado por GUSTAVO
PAOLA es autorizado por HERNAN
LUIS es autorizado por HERMAN
ANA es autorizado por CARLOS
...
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 5
Obteniendo Datos Usando Funciones de Agrupación
Objetivos
• Identificar las funciones de agrupación disponibles
• Describir el uso de las funciones de agrupación
• Agrupar datos usando la cláusula GROUP BY
• Incluir o excluir filas agrupadas usando la cláusula
HAVING
¿Qué son las Funciones de
Agrupación? • Las funciones de agrupación operan sobre conjuntos
de filas para dar un resultado por grupo
COD NOMBRE APELLIDO SALARIO ...
-------------------------------------------
5 JUAN PEREZ 500
7 MARIA SILEZ 350
9 RICARDO QUIROGA 120
18 JOSE VARGAS 400
14 JIMENA TORRICO 350
...
EMPLEADOS
MIN(SALARIO)
------------
120
Salario Mínimo
de la tabla
EMPLEADOS
Tipos de Funciones de Agrupación
Función Descripción
AVG([DISTINCT|ALL]n) Valor promedio de n,
ignora valores nulos
COUNT({*|[DISTINCT|ALL]expr}) Cuenta el Nº de filas de
una columna
MAX([DISTINCT|ALL]expr) Calcula el valor máximo
de una columna
Función Descripción
MIN([DISTINCT|ALL]expr) Calcula el valor mínimo de
una columna
STDDEV([DISTINCT|ALL]n)
Desviación estándar de n,
ignora valores nulos
SUM ([DISTINCT|ALL]n) Suma los valores de n,
ignora valores nulos
VARIANCE ([DISTINCT|ALL]n) Varianza de n, ignora
valores nulos
Tipos de Funciones de Agrupación
Funciones AVG y SUM
Se pueden usar las funciones AVG y SUM para datos
numéricos
SQL> SELECT AVG(salario), MAX(salario),
2 MIN(salario), SUM(salario)
3 FROM empleados;
AVG(SALARIO) MAX(SALARIO)
------------ -------------
344 500
MIN(SALARIO) SUM(SALARIO)
------------ -------------
120 1720
COD NOMBRE APELLIDO SALARIO ...
-------------------------------------------
5 JUAN PEREZ 500
7 MARIA SILEZ 350
9 RICARDO QUIROGA 120
18 JOSE VARGAS 400
14 JIMENA TORRICO 350
EMPLEADOS
Funciones MIN y MAX
Se pueden usar las funciones MIN y MAX para
cualquier tipo de dato
SQL> SELECT MIN(fecha_nac), MAX(fecha_nac)
2 FROM empleados;
MIN(FECHA_NAC)
--------------
12-NOV-70
MAX(FECHA_NAC)
--------------
19-JUN-78
COD NOMBRE APELLIDO ... FECHA_NAC ...
----------------------------------------------
5 JUAN PEREZ 12-NOV-70
7 MARIA SILEZ 08-ABR-75
9 RICARDO QUIROGA 25-JUN-77
18 JOSE VARGAS 19-JUN-78
14 JIMENA TORRICO 01-DIC-76
EMPLEADOS
Función COUNT
COUNT(*) devuelve el número de filas en una tabla.
Tiene dos formatos:
- COUNT(*) incluye nulos
- COUNT(expr) No toma en cuenta nulos
SQL> SELECT COUNT(*)
2 FROM empleados
3 WHERE suc_cod = ’3’;
COUNT(*)
---------
3
COD NOMBRE APELLIDO ... SUC_COD
---------------------------------------
5 JUAN PEREZ 3
7 MARIA SILEZ 2
9 RICARDO QUIROGA 7
14 JIMENA TORRICO 3
18 JOSE VARGAS 3
EMPLEADOS
Creando Grupos de Datos
COD ... SALARIO SUC_COD
-------------------------------
7 350 2
15 120 2
5 500 3
18 400 3
14 350 3
21 350 3
2 350 5
17 300 5
22 350 5
9 120 7
11 400 7
EMPLEADOS
SUC_COD AVG(SALARIO)
----------------------
2 235
3 400
5 333.33
7 260
Salario
Promedio de la
tabla
EMPLEADOS
por sucursal
235
400
333.33
260
Uso de la Cláusula GROUP BY
SELECT columna, funcion_agrupacion(columna)
FROM tabla
[WHERE condicion]
[GROUP BY expresion_agrupacion]
[ORDER BY columna];
La cláusula GROUP BY sirve para dividir una tabla en
grupos de filas que comparten características comunes
Todas las columnas en la lista SELECT que no estén
entre funciones de agrupación deben de estar en la
cláusula GROUP BY
SQL> SELECT suc_cod, AVG(salario)
2 FROM empleados
3 GROUP BY suc_cod;
SUC_COD AVG(SALARIO)
----------------------------
2 350
3 400
5 333.33
7 260
Uso de la Cláusula GROUP BY
Uso de la Cláusula HAVING
Se usa la cláusula HAVING para restringir los grupos
obtenidos
- Primero las filas son agrupadas
- La función de agrupación es aplicada
- Los grupos que cumplen la cláusula HAVING serán mostrados
SELECT columna, funcion_agrupacion
FROM tabla
[WHERE condicion]
[GROUP BY expresion_group_by]
[HAVING condicion_agrupacion]
[ORDER BY columna];
SQL> SELECT suc_cod, MAX(salario)
2 FROM empleados
3 GROUP BY suc_cod
4 HAVING MAX(salario)> 370;
SUC_COD MAX(SALARIO)
---------------------
3 500
7 400
Uso de la Cláusula HAVING
COD ... SALARIO SUC_COD
------------------------------
7 350 2
15 120 2
5 500 3
18 400 3
14 350 3
21 350 3
2 350 5
17 300 5
22 350 5
9 120 7
11 400 7
EMPLEADOS
Así como las funciones de SQL, las funciones de
agrupación pueden ser anidadas
SQL> SELECT MAX(AVG(salario))
2 FROM empleados
3 GROUP BY suc_cod;
MAX(AVG(SALARIO))
-------------------
400
Funciones de Agrupación Anidadas
AVG(SALARIO)
----------------
235
400
333.33
260
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 6
Sub-Consultas
Objetivos
• Describir que es una sub-consulta
• Tipos de sub-consultas
• Escribir algunos ejemplos
Sub-Consultas
Es una sentencia SELECT que esta dentro de una
cláusula de otra sentencia SELECT
SELECT lista_select
FROM tabla
WHERE expr operador
(SELECT lista_select
FROM tabla);
• Puede existir tanto en la cláusula FROM como en la
cláusula WHERE
• La sub-consulta (consulta interna) se ejecuta antes de
la consulta principal
• El resultado de la sub-consulta es usado por la consulta
principal (consulta externa)
SQL> SELECT nombre
2 FROM empleados
3 WHERE salario >
4 ( SELECT salario
5 FROM empleados
6 WHERE cod = ’9’);
NOMBRE
---------------------
JUAN
MARIA
...
Uso de una Sub-Consulta
120
SQL> SELECT nombre
2 FROM empleados e,
3 ( SELECT salario
4 FROM empleados
5 WHERE cod = ’9’) sueldo9
6 WHERE e.salario > sueldo9.salario;
NOMBRE
---------------------
JUAN
MARIA
...
Uso de una Sub-Consulta
120
Siempre tiene que
tener un alias
Al usar una Sub-Consulta
• Encerrar las sub-consultas entre paréntesis
• Colocar las sub-consultas del lado derecho del
operador de comparación
• No escribir la cláusula ORDER BY en una sub-
consulta
• Usar operadores que devuelven una fila con sub-
consultas que devuelven una fila
• Usar operadores que devuelven mas de una fila con
sub-consultas que devuelven mas de una fila
Clases de Sub-Consultas
• Sincrónicas
Existen columnas de la consulta principal en la sub-consulta
SELECT e.nombre, ...
FROM empleados e
WHERE .... >
( SELECT ...
FROM sucursales
WHERE cod = e.suc_cod);
Existen columnas de la
consulta principal
• Asincrónicas
Ambas consultas son independientes
SELECT ...
FROM empleados
WHERE .... >
( SELECT ...
FROM sucursales
WHERE cod = ’7’ );
Son independientes
Clases de Sub-Consultas
Tipos de Sub-Consultas • Sub-Consultas que devuelven una fila
• Sub-Consultas que devuelven más de una fila
• Sub-Consultas que devuelven más de una columna
Consulta Principal
Sub Consulta
Retorna
JUAN
Consulta Principal
Sub Consulta
RetornaJUAN
MARIA
Consulta Principal
Sub Consulta
RetornaJUAN 500
MARIA 350
Sub-Consultas que devuelven
una fila • Retornan solo una fila
• Se pueden usar los siguientes operadores:
Operador Descripción
= Igual a
> Mayor que
>= Mayor o igual que
< Menor que
<= Menor o igual que
<> Diferente
SQL> SELECT nombre, lugar_nac
2 FROM empleados
3 WHERE lugar_nac =
4 ( SELECT lugar_nac
5 FROM empleados
6 WHERE cod = ’9’)
7 AND salario >
8 ( SELECT salario
9 FROM empleados
10 WHERE cod = ’5’);
NOMBRE LUGAR_NAC
----------------------
JUAN COCHABAMBA
Ejecutando Sub-Consultas que
devuelven una fila
COCHABAMBA
500
Sub-Consultas que devuelven
más de una fila • Retornan más de una fila
• Se puede usar los siguientes operadores:
Operador Descripción
IN Igual a cualquier elemento de
una lista
ANY Compara un valor con cada
valor resultante de la sub-
consulta
ALL Compara un valor con todos
los valores resultantes de la
sub-consulta
SQL> SELECT nombre, salario, suc_cod
2 FROM empleados
3 WHERE salario IN
4 ( SELECT MIN(salario)
5 FROM empleados
6 GROUP BY suc_cod);
NOMBRE SALARIO SUC_COD
----------------------------------
FERNANDO 180 1
MARCOS 150 2
VANIA 120 3
GUSTAVO 180 7
RICARDO 120 9
Ejecutando Sub-Consultas que
devuelven más de una fila
Ejecutando Sub-Consultas que
devuelven más de una fila Para los casos en que se usen los operadores ANY y
ALL, el comportamiento será el siguiente:
WHERE a > ALL (SELECT a FROM ....) será mas o menos
equivalente a un AND
Compara a con 5
cada uno de estos 8
Valores 9
WHERE a > ANY (SELECT a FROM ....) será mas o menos
equivalente a un OR
Compara a con 5
cualquiera de estos 8
Valores 9
Sub-Consultas que devuelven
más de una columna • Retornan más de una columna
SELECT columna, columna, ...
FROM tabla
WHERE (columna, columna, ...) IN
SELECT columna, columna, ...
FROM tabla
WHERE condicion);
SQL> SELECT cod, descr, costo
2 FROM videos
3 WHERE (cod, descr) IN
4 ( SELECT cod, descr
5 FROM videos
6 WHERE cat_cod = ’3’)
7 AND cat_cod <> ’3’;
COD DESCR COSTO
---------------------------------------
005 DURO DE MATAR 3
008 MAXIMA VELOCIDAD 3.5
016 TOP GUN 3
Ejecutando Sub-Consultas que
devuelven más de una columna
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 7
Manejo de los Valores de Entrada con SQL*Plus
Objetivos
• Escribir consultas que requieren una variable de
entrada
• Usar algunos caracteres especiales para la
sustitución de variables
• Definir variables de usuario
Sustitución de Variables
• Se usa la sustitución de variables en SQL*Plus para
almacenar temporalmente valores
• Se puede sustituir variables de la siguiente manera:
- Ampersand (&)
- Doble Ampersand (&&)
- Comandos DEFINE y ACCEPT
Se usa el ampersand(&) antecediendo a una
variable para permitir al usuario el ingreso de una
valor para esa variable
SQL> SELECT nombre, apellido, salario
2 FROM empleados
3 WHERE cod = &codigo_emp;
Enter value for codigo_emp: 7
NOMBRE APELLIDO SALARIO
-----------------------------------
MARIA SILEZ 350
Uso de & para la Sustitución de
Variables
Se tiene que usar comillas simples para sustituir
valores de tipo fecha y caracter
SQL> SELECT nombre, apellido, salario
2 FROM empleados
3 WHERE lugar_nac = ’&lugar’;
Enter value for lugar: COCHABAMBA
NOMBRE APELLIDO SALARIO
-----------------------------------
JUAN PEREZ 500
RICARDO QUIROGA 120
Sustitución de Variables con
valores de tipo Fecha y Caracter
Se puede usar la sustitución de variables para
especificar:
- Condición WHERE
- Cláusula ORDER BY
- Expresiones de Columnas
- Nombres de Tablas
- Sentencia SELECT entera
Especificando nombres de
columnas, expresiones y textos
en tiempo de ejecución
SQL> SELECT nombre, apellido, lugar_nac, &columna
2 FROM empleados
3 WHERE &condicion
4 ORDER BY &orden_columna;
Enter value for columna: salario
Enter value for condicion: salario >= 500
Enter value for orden_columna: nombre
NOMBRE APELLIDO LUGAR_NAC SALARIO
---------------------------------------------
JUAN PEREZ COCHABAMBA 500
OSCAR VARGAS LA PAZ 800
...
Especificando nombres de
columnas, expresiones y textos
en tiempo de ejecución
Se usa el doble ampersand(&&) si se quiere reusar
el valor de una variable sin necesidad de volver a
escribirlo nuevamente
SQL> SELECT nombre, apellido, &&columna
2 FROM empleados
3 ORDER BY &columna;
Enter value for columna: salario
NOMBRE APELLIDO SALARIO
-----------------------------------
RICARDO QUIROGA 120
MARIA SILEZ 350
JUAN PEREZ 500
...
Uso de && para la Sustitución
de Variables
Definición de Variables de
Usuario
• Se puede predefinir variables usando uno de los
siguientes comandos:
- DEFINE: crea una variable de usuario de tipo
CHAR
- ACCEPT: lee una línea de entrada de datos del
usuario y lo almacena en una variable
• Si se necesita predefinir una variable que incluye
espacios, se debe de encerrar el valor entre comillas
simples cuando se use el comando DEFINE
Definición de Variables de
Usuario
Comando Descripción
DEFINE variable = valor Crea una variable de usuario de
tipo CHAR y le asigna el valor
DEFINE variable Muestra la variable, su valor y
su tipo de dato
DEFINE Muestra todas las variables de
usuario con su valor y su tipo de
dato
ACCEPT Lee una línea de entrada de
datos del usuario y lo almacena
en una variable
Comando ACCEPT
• Crea una entrada de datos personalizada cuando el
usuario ingrese el valor a una variable
• Oculta el ingreso del valor a una variable por razones
de seguridad
ACCEPT variable [tipo_dato] [FORMAT formato]
[PROMPT texto] [HIDE]
Comando ACCEPT
ACCEPT columna PROMPT ’Ingrese el Lugar de Nacimiento: ’
SELECT nombre, apellido, lugar_nac
FROM empleados
WHERE lugar_nac = UPPER(‘&columna’);
/
Ingrese el Lugar de Nacimiento: cochabamba
NOMBRE APELLIDO LUGAR_NAC
--------------------------------------
JUAN PEREZ COCHABAMBA
RICARDO QUIROGA COCHABAMBA
...
Comandos DEFINE y UNDEFINE
• Una variable se mantiene definida hasta que se haga
lo siguiente:
- Se use el comando UNDEFINE para limpiarla
- Se salga de SQL*Plus
• Se puede verificar los cambios con el comando
DEFINE
• Para definir variables en cada sesión, hay que
modificar el archivo login.sql para que las variables
sean creadas desde el inicio
Usando el Comando DEFINE
• Crear una variable para mantener el lugar de
nacimiento:
SQL> DEFINE lugar = cochabamba
SQL> DEFINE
DEFINE LUGAR = “cochabamba”
• Usar esa variable como cualquier otra variable
SQL> SELECT *
2 FROM empleados
3 WHERE lugar_nac = UPPER(‘&lugar’);
Usando el Comando UNDEFINE
• Para borrar una variable, se usa el comando
UNDEFINE
SQL> UNDEFINE lugar
SQL> DEFINE
symbol lugar is UNDEFINED
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 8
Manipulando Datos
Objetivos
• Describir cada sentencia DML
• Insertar filas en una tabla
• Actualizar las filas de una tabla
• Eliminar filas en una tabla
• Control de transacciones
Lenguaje de Manipulación de Datos
(DML)
• Las sentencias DML permiten:
- Insertar nuevas filas en una tabla
- Modificar datos existentes en una tabla
- Eliminar datos existentes en una tabla
• Una transacción consiste en una colección de
sentencias DML que forman una unidad lógica de
trabajo
La Sentencia INSERT
• Para añadir nuevas filas en una tabla se debe de
usar la sentencia INSERT
INSERT INTO tabla [(columna [, columna...])]
VALUES (valor [, valor...]);
• Solo una fila es insertada cuando se ejecuta esta
sentencia con esta sintaxis
Uso de la Sentencia INSERT
• Se inserta una nueva fila conteniendo valores para
cada columna
• La lista de valores a insertar por defecto esta en el
orden de las columnas de la tabla
• Es opcional la lista de las columnas de la tabla en la
sentencia INSERT
• Se tiene que encerrar entre comillas simples los
valores de tipo caracter o fecha
Uso de la Sentencia INSERT
SQL> INSERT INTO clientes(cod, ci, nombre, apellido,
2 telefono, cli_cod)
3 VALUES (12,’4578213’,’GUSTAVO’,’SOTO’,’257841’,’08’);
SQL> INSERT INTO clientes
2 VALUES (12,’4578213’,’GUSTAVO’,’SOTO’,’257841’,’08’);
SQL> INSERT INTO clientes(cod, apellido, nombre, ci,
2 cli_cod, telefono)
3 VALUES (12,’SOTO’,’GUSTAVO’,’4578213’,’08’,’257841’);
Insertando filas con Valores Nulos
• Método Implícito: se omite la columna de la lista de
columnas
SQL> INSERT INTO clientes(cod,ci,nombre,apellido)
2 VALUES (12,’4578213’,’GUSTAVO’,’SOTO’);
• Método Explícito: se especifica la palabra NULL
SQL> INSERT INTO clientes(cod,ci,nombre,apellido,
2 telefono, cli_cod)
3 VALUES(12,’4578213’,’GUSTAVO’,’SOTO’,NULL,NULL);
Insertando Valores usando
Sustitución de Variables
SQL> INSERT INTO clientes(cod, ci, nombre, apellido,
2 telefono, cli_cod)
3 VALUES (&codigo,’&ci’,’&nombre’,’&apellido’,
4 ’&fono’,’&cliente_autorizador’);
Enter value for codigo: 19
Enter value for ci: 4875321
Enter value for nombre: MARCELO
Enter value for apellido: SANCHEZ
Enter value for fono: 268874
Enter value for cliente_autorizador: 12
1 row created.
La Sentencia UPDATE
• Para modificar las filas de una tabla se usa la
sentencia UPDATE
UPDATE tabla
SET columna = valor [, columna = valor]
[WHERE condicion];
• Se actualiza mas de una fila a la vez, si es que fuese
necesario
Uso de la Sentencia UPDATE
• Una fila específica o varias filas pueden ser
modificadas cuando se especifica la cláusula WHERE
SQL> UDPATE empleados
2 SET suc_cod = ’7’
3 WHERE cod = ’5’;
• Todas las filas de la tabla son modificadas si se omite
la cláusula WHERE
SQL> UDPATE empleados
2 SET suc_cod = ’7’;
La Sentencia DELETE
• Para eliminar las filas existentes en una tabla se usa
la sentencia DELETE
DELETE [FROM] tabla
[WHERE condicion];
Uso de la Sentencia DELETE
• Una fila específica o varias filas pueden ser eliminadas
cuando se especifica la cláusula WHERE
SQL> DELETE FROM empleados
2 WHERE cod = ’5’;
• Todas las filas de la tabla son eliminadas si se omite la
cláusula WHERE
SQL> DELETE FROM empleados;
Transacciones en la Base de Datos
• Consiste en una de las siguientes sentencias:
- Sentencias DML que realizan cambios en los datos
- Una sentencia DDL
- Una sentencia DCL
Transacciones en la Base de Datos
• Comienza cuando la primera sentencia SQL
ejecutable es ejecutada
• Termina con uno de los siguientes eventos:
- Se ejecuta un COMMIT o ROLLBACK
- Sentencias DDL o DCL son ejecutadas (commit automático)
- El usuario sale
- El sistema falle
Ventajas de las Sentencias
COMMIT y ROLLBACK
• Aseguran la consistencia de los datos
• Se pueden ver los cambios en los datos antes de
volverlos permanentes
Control de Transacciones
Sentencia Descripción
COMMIT Termina la transacción actual y hace
todos los cambios en los datos
permanentes
SAVEPOINT name Marca un savepoint dentro la
transacción actual
ROLLBACK [TO
SAVEPOINT name]
ROLLBACK termina la transacción
actual y deshace todos los cambios
realizados en los datos; ROLLBACK
TO SAVEPOINT name deshace todos
los cambios realizados en los datos
pero solo hasta el savepoint name
Control de Transacciones
INSERT DELETEINSERTUPDATE
Transacción
COMMIT Savepoint BSavepoint A
ROLLBACK to Savepoint B
ROLLBACK to Savepoint A
ROLLBACK
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 9
Creación y Manipulación de Tablas
Objetivos
• Describir los objetos más importantes de la Base de
Datos
• Crear tablas
• Modificar la estructura de las tablas
• Eliminar, renombrar y truncar tablas
Objetos de la Base de Datos
Objeto Descripción
Tabla Unidad básica de almacenamiento
compuesta por filas y columnas
Vista Unidad lógica que representa un
subconjunto de datos de una o más tablas
Secuencia Objeto que genera números en secuencia
que pueden ser usados por columnas de las
tablas
Indice Objeto que permite mejorar el rendimiento
de algunas consultas
Sinónimos Permite dar nombres alternativos a objetos
Sentencia CREATE TABLE
• Para crear una tabla se usa la sentencia CREATE
TABLE
• Para usar esta sentencia se debe tener:
- Privilegios para crear tablas
- Un área de almacenamiento
CREATE TABLE [esquema.] tabla
(columna tipo_dato [DEFAULT expr] [,...]);
• Se debe de especificar:
- Nombre de la tabla
- Nombre de columna, tipo de dato de la columna y longitud de
la columna
Uso de la Sentencia CREATE TABLE
• Creación de una tabla
CREATE TABLE empleados (
cod VARCHAR2 (6) NOT NULL,
nombre VARCHAR2 (30) NOT NULL,
apellido VARCHAR2 (30) NOT NULL,
salario NUMBER (8,2) NOT NULL,
fecha_nac DATE,
lugar_nac VARCHAR2 (20),
suc_cod VARCHAR2 (6) NOT NULL );
• Confirmación de la creación de la tabla
SQL> DESCRIBE empleados;
Tablas en la Base de Datos Oracle
• Tablas de Usuario (User Tables)
- Colección de tablas creadas y mantenidas por el usuario
- Contiene información del usuario
• Diccionario de Datos (Data Dictionary)
- Colección de tablas creadas y mantenidas por el servidor
Oracle
- Contiene información de la base de datos
Sentencia ALTER TABLE
• Se puede usar la sentencia ALTER TABLE para:
- Añadir una nueva columna
- Modificar una columna existente
ALTER TABLE tabla
ADD (columna tipo_dato [DEFAULT expr]
[, columna tipo_dato]...);
ALTER TABLE tabla
MODIFY (columna tipo_dato [DEFAULT expr]
[, columna tipo_dato]...);
Añadiendo una Columna
• Se usa la cláusula ADD para añadir columnas
SQL> ALTER TABLE empleados
2 ADD (est_civil VARCHAR2(3));
• La nueva columna llegará a ser la ultima columna de la
tabla
• Se puede añadir o modificar columnas, pero no se
puede eliminarlas de la tabla
Modificando una Columna
• Se puede cambiar el tipo de dato de una columna,
longitud y valor por defecto usando la cláusula
MODIFY
SQL> ALTER TABLE empleados
2 MODIFY (nombre VARCHAR2(25),
3 apellido VARCHAR2(25));
• Un cambio en los valores por defecto afectará solo a
las inserciones posteriores que se hagan en la tabla
Sentencia DROP TABLE
• Cuando se quiera eliminar una tabla se usa la
sentencia DROP TABLE
DROP TABLE tabla;
• Todos los datos y la estructura de la tabla es borrada
• Cualquier transacción pendiente es terminada
(commit)
• Todos los índices son borrados
• No se puede hacer un ROLLBACK a esta sentencia
Cambio de Nombre a un Objeto
• Para cambiar el nombre a una tabla, vista, secuencia o
sinónimo se ejecuta la sentencia RENAME
RENAME antiguo_nombre TO nuevo_nombre;
• Se debe ser el propietario de ese objeto
Sentencia TRUNCATE TABLE
• Con esta sentencia se puede:
- Remover todas las filas de una tabla
- Liberar el espacio de almacenamiento usado por una tabla
TRUNCATE TABLE tabla;
• No se puede hacer un ROLLBACK de las filas
removidas cuando se use TRUNCATE
• Para remover todas las filas de una tabla se puede
usar también la sentencia DELETE
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 10
Restricciones de Integridad
Objetivos
• Describir restricciones de integridad
• Crear y mantener restricciones de integridad
Restricciones de Integridad
• Las restricciones de integridad (constraints) aseguran
la consistencia de los datos a nivel de las tablas
• Previenen la eliminación de una tabla si existen
dependencias
• Las siguientes restricciones de integridad son validas
en Oracle:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
Tipos de Restricciones de
Integridad
Restricción Descripción
NOT NULL Especifica que para una columna no contenga
valores nulos
UNIQUE Especifica que para columna o combinación de
columnas sus valores sean únicos para todas
las filas de la tabla
PRIMARY KEY Identifica únicamente a cada fila de la tabla
FOREIGN KEY Establece y asegura una relación de llave
foránea entre una columna y la llave primaria
de una tabla referenciada
CHECK Especifica una condición que debe de ser
verdadera
Creación de una Restricción de
Integridad
CREATE TABLE [esquema.] tabla
(columna tipo_dato [DEFAULT expr]
[constraint_columna],
...
[constraint_tabla] [,...]);
CREATE TABLE empleados (
cod VARCHAR2 (6) NOT NULL,
nombre VARCHAR2 (30) NOT NULL,
...
suc_cod VARCHAR2 (6) NOT NULL,
CONSTRAINT emp_pk
PRIMARY KEY (cod));
Creación de una Restricción de
Integridad
• Restricción de Integridad a nivel de columna
columna [CONSTRAINT nombre_constraint] tipo_constraint,
• Restricción de Integridad a nivel de tabla
columna, ...
[CONSTRAINT nombre_constraint] tipo_constraint
(columna, ...),
Restricción de Integridad NOT NULL Asegura que valores nulos no sean ingresados en las
columnas
EMPLEADOS
COD NOMBRE APELLIDO LUGAR_NAC ... SUC_COD
5 JUAN PEREZ COCHABAMBA 3
7 MARIA SILEZ LA PAZ 2
9 RICARDO QUIROGA COCHABAMBA 7
90 OSCAR VARGAS 7
...
Restricción NOT NULL (las filas
no pueden contener un valor
nulo para esta columna)
Ausencia de la restricción NOT NULL
(cualquier fila puede contener un
valor nulo para esta columna)
Restricción NOT
NULL
Definida solo para nivel de columnas
SQL> CREATE TABLE empleados (
2 cod VARCHAR2 (6) NOT NULL,
3 nombre VARCHAR2 (30) NOT NULL,
4 apellido VARCHAR2 (30) NOT NULL,
5 salario NUMBER (8,2) NOT NULL,
6 fecha_nac DATE,
7 lugar_nac VARCHAR2 (20),
8 suc_cod VARCHAR2 (6) NOT NULL );
Restricción de Integridad NOT NULL
Restricción de Integridad UNIQUE
CATEGORIAS
COD DESCR
1 DRAMA
2 ACCION
3 COMEDIA
4 TERROR
5 SUSPENSO
6 ACCION
7 INFANTIL
INSER INTO
Restricción UNIQUE
No permitido (ACCION ya existe)
Permitido
Restricción de Integridad UNIQUE
Definida para ambos niveles, tanto a nivel de tablas
como también a nivel de columnas
SQL> CREATE TABLE categorias (
2 cod VARCHAR2 (6) NOT NULL,
3 descr VARCHAR2 (100) NOT NULL,
4 CONSTRAINT cat_descr_uk UNIQUE(descr));
Restricción de Integridad
PRIMARY KEY
COD DESCR
1 DRAMA
2 ACCION
3 COMEDIA
4 TERROR
5 SUSPENSO
2 CIENCIA FICCION
INFANTIL
INSER INTO
PRIMARY KEY
No permitido (el código 20 ya existe)
No permitido (el código es nulo)
CATEGORIAS
Definida para ambos niveles, tanto a nivel de tablas
como también a nivel de columnas
SQL> CREATE TABLE categorias (
2 cod VARCHAR2 (6) NOT NULL,
3 descr VARCHAR2 (100) NOT NULL,
4 CONSTRAINT cat_descr_uk UNIQUE(descr)
5 CONSTRAINT cat_cod_pk PRIMARY KEY(cod));
Restricción de Integridad
PRIMARY KEY
Restricción de Integridad
FOREIGN KEY
COD DESCR DIRECCION ...
2 NORTE C. VENUS 28
3 CENTRAL C. BAPTISTA 145
INSER INTO
PRIMARY KEY
No permitido
(la sucursal 5
no existe en
sucursales)
EMPLEADOS
COD NOMBRE APELLIDO LUGAR_NAC ... SUC_COD
5 JUAN PEREZ COCHABAMBA 3
7 MARIA SILEZ LA PAZ 2
...
SUCURSALES
FOREIGN KEY
14 JOSE LOPEZ LA PAZ ... 5
15 JAIME CACERES COCHABAMBA ... 2 Permitido
Definida para ambos niveles, tanto a nivel de tablas
como también a nivel de columnas
SQL> CREATE TABLE empleados (
2 cod VARCHAR2 (6) NOT NULL,
3 nombre VARCHAR2 (30) NOT NULL,
4 apellido VARCHAR2 (30) NOT NULL,
5 salario NUMBER (8,2) NOT NULL,
6 fecha_nac DATE,
7 lugar_nac VARCHAR2 (20),
8 suc_cod VARCHAR2 (6) NOT NULL,
9 CONSTRAINT emp_suc_fk FOREIGN KEY (suc_cod)
10 REFERENCES sucursales (cod));
Restricción de Integridad
FOREIGN KEY
Restricción de Integridad
FOREIGN KEY
• FOREIGN KEY
Define la columna en la tabla hija
• REFERENCES
Identifica la tabla y columna en la tabla maestro
• ON DELETE CASCADE
Permite la eliminación en la tabla maestro y
eliminación de las filas dependientes en la tabla hija
Define una condición que cada fila debe satisfacer
..., cod VARCHAR2(6) NOT NULL,
CONSTRAINT emp_cod_ck
CHECK (cod BEWTEEN 1 AND 100),...
Restricción de Integridad
CHECK
Adicionar Restricciones de
Integridad
ALTER TABLE tabla
ADD [CONSTRAINT constraint] tipo (columna);
• Se puede adicionar o eliminar, pero no modificar una
restricción de integridad
• Se puede habilitar o deshabilitar restricciones de
integridad
• Se puede adicionar la restricción NOT NULL pero
usando la cláusula MODIFY
Eliminar Restricciones de
Integridad
ALTER TABLE tabla
DROP PRIMARY KEY | UNIQUE (columna) |
CONSTRAINT constraint [CASCADE]);
SQL> ALTER TABLE sucursales
2 DROP PRIMARY KEY CASCADE;
SQL> ALTER TABLE empleados
2 DROP CONSTRAINT emp_suc_fk;
Deshabilitar Restricciones de
Integridad
ALTER TABLE tabla
DISABLE CONSTRAINT constraint [CASCADE];
• Se ejecuta la cláusula DISABLE de la sentencia
ALTER TABLE para desactivar una restricción de
integridad
• Se usa la opción CASCADE para deshabilitar
restricciones de integridad dependientes
Habilitar Restricciones de
Integridad
ALTER TABLE tabla
ENABLE CONSTRAINT constraint;
• Para habilitar una restricción de integridad
deshabilitada en la definición de una tabla se usa la
cláusula ENABLE
• Un índice UNIQUE o PRIMARY KEY es
automáticamente creado si se habilita una restricción
de integridad UNIQUE o PRIMARY KEY
Visualizar Objetos de la Base
de Datos
Se pueden mostrar los objetos creados en la Base de
Datos accediendo a las tablas del diccionario de
datos:
- USER_TABLES
Muestra las tablas de un usuario
- USER_OBJECTS
Muestra los distintos tipos de objetos de un usuario
- USER_CATALOG
Muestra las tablas, vistas, sinónimos y secuencias de un
usuario
- USER_CONSTRAINTS
Muestra las restricciones de integridad de un usuario
Visualizar Restricciones de
Integridad
La consulta a la tabla USER_CONSTRAINTS muestra
todas las definiciones y nombres de restricciones de
integridad
SQL> SELECT constraint_name, constraint_type,
2 search_condition
3 FROM user_constraints
4 WHERE table_name = ’EMPLEADOS’;
CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION
-----------------------------------------------------
SYS_C00674 C COD IS NOT NULL
SYS_C00675 C NOMBRE IS NOT NULL
EMP_SUC_FK R
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 11
Vistas
Objetivos
• Definir una vista
• Crear vistas
• Recuperar datos a través de una vista
• Modificar vistas
• Eliminar una vista
¿Qué es una Vista?
Es una tabla lógica basada en una tabla u otra vista
COD NOMBRE APELLIDO LUGAR_NAC ... SUC_COD
5 JUAN PEREZ COCHABAMBA 3
7 MARIA SILEZ LA PAZ 2
9 RICARDO QUIROGA COCHABAMBA 7
90 OSCAR VARGAS 7
...
COD NOMBRE APELLIDO
9 RICARDO QUIROGA
90 OSCAR VARGAS
TABLA EMPLEADOS
VISTA EMPLEADOSVI
¿Porqué usar Vistas?
• Restringir el acceso a la Base de Datos
• Hacer las consultas complejas mas sencillas
• Permitir la independencia de datos
• Presentar diferentes vistas para los mismos datos
Vistas Simples y Complejas
Característica Vistas Simples Vistas Complejas
Numero de Tablas Una Una o mas
Contienen Funciones No Si
Contienen Grupos de Datos No Si
DML a través de una vista Si Nunca
Creación de Vistas
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW vista
[(alias[, alias]...)]
AS sub-consulta
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
• Se puede crear una vista usando la sentencia
CREATE VIEW
• La sub-consulta puede contener sentencias SELECT
complejas
• La sub-consulta no puede contener cláusulas
ORDER BY
Creación de Vistas
SQL> CREATE VIEW empleadosvi
2 AS SELECT cod, nombre, apellido
3 FROM empleados
4 WHERE suc_cod = ’7’;
SQL> CREATE VIEW empleadosvi2
2 AS SELECT cod CODIGO, nombre NOMBRE_EMP,
3 apellido APELLIDO_EMP
4 FROM empleados
5 WHERE suc_cod = ’3’;
Modificación de Vistas
• Se puede modificar una vista usando el comando
CREATE OR REPLACE VIEW
• Los alias de columnas en la cláusula CREATE VIEW
estarán listados en el mismo orden de las columnas de
la sub-consulta
SQL> CREATE OR REPLACE VIEW empleadosvi2
2 (codigo, empleado_nombre, empleado_apellido)
3 AS SELECT cod, nombre, apellido
4 FROM empleados
5 WHERE suc_cod = ’7’;
Creación de Vistas
Complejas
Una vista compleja contiene funciones de agrupación para mostrar los valores de dos tablas
SQL> CREATE VIEW emp_sucursales_vi
2 (codigo, sucursal, salario_min, salario_max)
3 AS SELECT e.suc_cod, s.descr, MIN(e.salario),
4 MAX(e.salario)
5 FROM empleados e, sucursales s
6 WHERE e.suc_cod = s.cod
7 GROUP BY e.suc_cod, s.descr;
Reglas para realizar operaciones
DML en Vistas
• Se pueden realizar operaciones DML sobre vistas
simples
• No se pueden eliminar filas si la vista contiene lo
siguiente:
- Funciones de agrupación
- Una cláusula GROUP
- La palabra DISTINCT
Reglas para realizar operaciones
DML en Vistas
• No se pueden modificar los datos en una vista si ésta
contiene lo siguiente:
- Funciones de agrupación
- Una cláusula GROUP
- La palabra DISTINCT
- Columnas definidas por expresiones
Reglas para realizar operaciones
DML en Vistas
• No se pueden añadir datos si existen:
- Funciones de agrupación
- Una cláusula GROUP
- La palabra DISTINCT
- Columnas definidas por expresiones
- Columnas NOT NULL en las tablas base que no hayan sido
seleccionadas por la vista
Cláusula WITH CHECK OPTION Se puede asegurar que operaciones DML con la vista
estén dentro el dominio de ésta usando la cláusula WITH CHECK OPTION
SQL> CREATE OR REPLACE VIEW empvi7
2 AS SELECT *
3 FROM empleados
4 WHERE suc_cod = ’7’
5 WITH CHECK OPTION CONSTRAINT empvi7_ck;
SQL> UPDATE empvi7
2 SET suc_cod = ’3’
3 WHERE cod = ’9’;
ERROR at line 1:
ORA-01402: VIEW CHECK OPTION where-clause violation
Denegando Operaciones DML
• Se puede asegurar que operaciones DML no ocurran
usando la opción WITH READ ONLY
• Cualquier intento de realizar una operación DML llevará a un mensaje del Servidor Oracle
SQL> CREATE OR REPLACE VIEW empvi7
2 AS SELECT cod, nombre, apellido, salario
3 FROM empleados
4 WHERE suc_cod = ’7’
5 WITH READ ONLY;
Eliminación de Vistas
Al eliminar una vista no se pierden los datos porque la
vista esta basada en las tablas de la Base de Datos
DROP VIEW vista
Parte II - SQL
Sentencias Básicas
de SQL
Obteniendo Datos
Usando Funciones de
Agrupación
Restricción y
Ordenamiento de Datos
Sub-Consultas Obteniendo Datos
desde Múltiples Tablas
Manejo de los Valores
de Entrada con
SQL*Plus
Funciones de SQL
Manipulando Datos Creación y
Manipulación de Tablas
Restricciones de
Integridad Vistas
Secuencias, Indices y
Sinónimos
Capítulo 12
Secuencias, Índices y Sinónimos
Objetivos
• Definir secuencias, índices y sinónimos
• Crear, mantener y usar secuencias
• Crear, mantener y usar índices
• Crear, mantener y usar sinónimos
¿Qué es una Secuencia?
• Genera automáticamente números únicos
• Es un objeto compartible
• Es usado generalmente para crear valores de llaves
primarias
• Reduce código de la aplicación
• Acelera la eficiencia de acceso a los valores de la
secuencia cuando es cargada en la memoria cache
Sentencia CREATE SEQUENCE
Define una secuencia para generar automáticamente
números secuenciales
CREATE SEQUENCE secuencia
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
Sentencia CREATE SEQUENCE
SQL> CREATE SEQUENCE sec_emp
2 INCREMENT BY 1
3 START WITH 91
4 MAXVALUE 100
5 NOCACHE
6 NOCYCLE;
Se puede verificar los valores de secuencia en la tabla
del diccionario de datos USER_SEQUENCES
SQL> SELECT sequence_name, min_value, max_value,
2 increment_by, last_number
3 FROM user_sequences;
La columna LAST_NUMBER muestra el siguiente
número de secuencia disponible
Pseudocolumnas NEXTVAL y
CURRVAL
• NEXTVAL retorna el siguiente valor de secuencia
disponible
- El valor que retorna es único cada vez que éste es
referenciado por diferentes usuarios
• CURRVAL obtiene el valor de secuencia actual
- NEXTVAL debe de ser declarado para la secuencia antes de
que CURRVAL contenga un valor
Reglas para usar NEXTVAL y
CURRVAL
• Se puede usar NEXTVAL y CURRVAL en:
- La lista SELECT de una sentencia SELECT que no es
parte de una sub-consulta
- La lista SELECT de una sub-consulta en una sentencia
INSERT
- La cláusula VALUES de una sentencia INSERT
- La cláusula SET de una sentencia UPDATE
• No se puede usar NEXTVAL y CURRVAL en:
- La lista SELECT de una vista
- Una sentencia SELECT con la palabra DISTINCT
- Una sentencia SELECT con las cláusulas GROUP BY,
HAVING o ORDER BY
- Una sub-consulta en sentencias SELECT, DELETE o
UPDATE
- Una expresión DEFAULT en las sentencias CREATE
TABLE o ALTER TABLE
Reglas para usar NEXTVAL y
CURRVAL
Sentencia ALTER SEQUENCE
Permite cambiar el valor de incremento, valor máximo,
valor mínimo, opción cycle u opción cache
ALTER SEQUENCE secuencia
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
Sentencia ALTER SEQUENCE
• Para modificar una secuencia se debe ser el dueño o
tener privilegios ALTER para la secuencia
• Solo los futuros números secuenciales serán afectados
• La secuencia debe ser eliminada o re-creada para
empezar la secuencia en un numero diferente
• Algunas validaciones son realizadas
SQL>ALTER SEQUENCE sec_emp
2 INCREMENT BY 1
3 MAXVALUE 99999
4 NOCACHE
5 NOCYCLE;
Sentencia DROP SEQUENCE
• Para eliminar una secuencia del diccionario de datos se
usa la sentencia DROP SEQUENCE
• Una vez eliminada, la secuencia no podrá ser
referenciada
DROP SEQUENCE secuencia
SQL> DROP SEQUENCE sec_emp;
¿Qué es un Indice?
• Es un objeto esquema
• Es usado por el Servidor Oracle para acelerar la
recuperación de filas usando un puntero
• Es independiente de la tabla en la que esta indexado
• Es usado y mantenido automáticamente por el
Servidor Oracle
¿Cómo son creados los Indices?
• Automáticamente
Un índice único es creado automáticamente cuando se
define restricciones de PRIMARY KEY o UNIQUE en la
definición de una tabla
• Manualmente
Los usuarios pueden crear índices no únicos en las
columnas para acelerar el tiempo de acceso a las filas
Sentencia CREATE INDEX
• Se puede crear un índice para una o más columnas
CREATE INDEX indice
ON tabla (columna [, columna]...);
SQL> CREATE INDEX emp_suc_idx
2 ON empleados(suc_cod);
• La vista del diccionario de datos USER_INDEXES
contiene el nombre del índice y si éste es único
• La vista USER_IND_COLUMNS contiene el nombre del
índice, el nombre de la tabla y el nombre de la columna
Cuando crear un Indice
• La columna es usada frecuentemente en la cláusula
WHERE o en una condición de join
• La columna contiene un amplio rango de valores
• La columna contiene un número grande de valores null
• Dos o más columnas son usadas frecuentemente juntas
en la cláusula WHERE o en una condición join
• La tabla es grande y se espera que la mayoría de las
consultas recuperen menos que un 2-4% de las filas
Cuando no crear un Indice
• La tabla es pequeña
• Las columnas no son a menudo usadas como una
condición de consulta
• La mayoría de las consultas esperadas recuperen mas
que un 2-4% de las filas
• La tabla es actualizada frecuentemente
Sentencia DROP INDEX
• Elimina un índice del diccionario de datos
• Para eliminar un índice se debe de ser el dueño del
índice o tener privilegios DROP ANY INDEX
DROP INDEX indice;
SQL> DROP INDEX emp_suc_idx;
Sinónimos
Se puede simplificar el acceso a objetos creando un
sinónimo (otro nombre para un objeto)
• Se puede hacer referencia a una tabla que es de otro
usuario
• Reduce los nombres de objetos largos
CREATE [PUBLIC] SYNONYM sinonimo
FOR objeto;
• Crear un nombre corto para la vista
EMP_SUCURSALES_VI
SQL> CREATE SYNONYM suc_vi
2 FOR emp_sucursales_vi;
• Eliminar el sinónimo
SQL> DROP SYNONYM suc_vi;
Creación y Eliminación de
Sinónimos