Download - 2.- Oracle Operaciones Basicas
![Page 1: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/1.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Consultas:Unión de una Tabla Consigo Misma
SELECT worker.last_name || ' trabaja para '
|| manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
…
![Page 2: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/2.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Unión de Tablas Utilizando la Sintaxis SQL: 1999
Utilice una unión para consultar datos de más de una tabla.
SELECT table1.column, table2.columnFROM table1[CROSS JOIN table2] |[NATURAL JOIN table2] |[JOIN table2 USING (column_name)] |[JOIN table2 ON(table1.column_name = table2.column_name)] |[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)];
SELECT table1.column, table2.columnFROM table1[CROSS JOIN table2] |[NATURAL JOIN table2] |[JOIN table2 USING (column_name)] |[JOIN table2 ON(table1.column_name = table2.column_name)] |[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)];
![Page 3: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/3.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de Uniones Cruzadas
• La cláusula CROSS JOIN produce varios productos entre dos tablas.
• Es lo mismo que un producto Cartesiano entre las dos tablas.
SELECT last_name, department_nameFROM employeesCROSS JOIN departments ;
…
![Page 4: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/4.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de Uniones Naturales
• La cláusula NATURAL JOIN se basa en todas las columnas de las dos tablas que tienen el mismo nombre.
• Selecciona filas de las dos tablas que tienen los mismos valores en todas las columnas coincidentes.
• Si las columnas que tienen el mismo nombre tienen distintos tipos de dato, se devuelve un error.
![Page 5: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/5.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT department_id, department_name, location_id, cityFROM departmentsNATURAL JOIN locations ;
Recuperación de Registros conUniones Naturales
![Page 6: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/6.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de Uniones con la Cláusula USING
• Si varias columnas tienen los mismos nombres pero los tipos de dato no coinciden, la cláusula NATURAL JOIN se puede modificar con la cláusula USING para especificar las columnas que se deben utilizar para una unión de igualdad.
• Utilice la cláusula USING para hacer coincidir solamente una columna cuando coincidan varias.
• No utilice un nombre o alias de tabla en las columnas de referencia.
• Las cláusulas NATURAL JOIN y USING son mutuamente excluyentes.
![Page 7: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/7.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT e.employee_id, e.last_name, d.location_idFROM employees e JOIN departments dUSING (department_id) ;
Recuperación de Registros con la Cláusula USING
…
![Page 8: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/8.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de Uniones con la Cláusula ON
• La condición de unión para la unión natural es básicamente una unión de igualdad de todas las columnas con el mismo nombre.
• Para especificar condiciones arbitrarias o especificar columnas para unir, se utiliza la cláusula ON.
• La condición de unión se separa de otras condiciones de búsqueda.
• La cláusula ON facilita la comprensión del código.
![Page 9: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/9.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id);
Recuperación de Registros con la Cláusula ON
…
![Page 10: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/10.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de Uniones en Tres Sentidos con la Cláusula ON
SELECT employee_id, city, department_nameFROM employees e JOIN departments dON d.department_id = e.department_id JOIN locations lON d.location_id = l.location_id;
…
![Page 11: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/11.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uniones INNER frente a OUTER
• En SQL: 1999, la unión de dos tablas que devuelve solamente las filas coincidentes es una unión interna.
• Una unión entre dos tablas que devuelve los resultados de la unión interna así como las tablas izquierda (o derecha) de filas no coincidentes es una unión externa izquierda (o derecha).
• Una unión entre dos tablas que devuelve los resultados de una unión interna así como los de una unión izquierda y derecha es una unión externa completa.
![Page 12: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/12.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;
LEFT OUTER JOIN
…
![Page 13: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/13.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT OUTER JOIN departments dON (e.department_id = d.department_id) ;
RIGHT OUTER JOIN
…
![Page 14: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/14.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT e.last_name, e.department_id, d.department_nameFROM employees eFULL OUTER JOIN departments dON (e.department_id = d.department_id) ;
FULL OUTER JOIN
…
![Page 15: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/15.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id)AND e.manager_id = 149 ;
Condiciones Adicionales
![Page 16: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/16.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
¿Qué Son Funciones de Grupo?Las funciones de grupo operan sobre juegos de filas para proporcionar un resultado por grupo.
EMPLOYEES
El salario máximo en la tabla EMPLOYEES.
…
![Page 17: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/17.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Tipos de Funciones de Grupo
• AVG
• COUNT
• MAX
• MIN
• STDDEV
• SUM
• VARIANCE
![Page 18: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/18.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT [column,] group_function(column), ...FROM table[WHERE condition][GROUP BY column][ORDER BY column];
Sintaxis de las Funciones de Grupo
![Page 19: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/19.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)FROM employeesWHERE job_id LIKE '%REP%';
Uso de las Funciones AVG y SUM
Puede utilizar AVG y SUM para datos numéricos.
![Page 20: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/20.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de las Funciones MIN y MAX
Puede utilizar MIN y MAX para cualquier tipo de dato.
SELECT MIN(hire_date), MAX(hire_date)FROM employees;
![Page 21: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/21.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT COUNT(*)FROM employeesWHERE department_id = 50;
Uso de la Función COUNT
COUNT(*) devuelve el número de filas de una tabla.
![Page 22: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/22.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de la Función COUNT
• COUNT(expr) devuelve el número de filas con valores no nulos para expr.
• Visualice el número de valores de departamento de la tabla EMPLOYEES, excluyendo los valores nulos.
SELECT COUNT(commission_pct)FROM employeesWHERE department_id = 80;
![Page 23: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/23.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT COUNT(DISTINCT department_id)FROM employees;
Uso de la Palabra Clave DISTINCT
• COUNT(DISTINCT expr) devuelve el número de valores distintos no nulos de expr.
• Visualice el número de valores de departamento distintos de la tabla EMPLOYEES.
![Page 24: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/24.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT AVG(commission_pct)FROM employees;
Funciones de Grupo y Valores Nulos
Las funciones de grupo ignoran los valores nulos de la columna.
![Page 25: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/25.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT AVG(NVL(commission_pct, 0))FROM employees;
Uso de la Función NVL con Funciones de Grupo
La función NVL fuerza a las funciones de grupo a que incluyan valores nulos.
![Page 26: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/26.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de Grupos de Datos
EMPLOYEES
El salariomedio de la tabla
EMPLOYEES para cada
departamento.
4400
…
9500
3500
6400
10033
![Page 27: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/27.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT column, group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][ORDER BY column];
Creación de Grupos de Datos: Sintaxis de la Cláusula GROUP BY
Divida las filas de una tabla en grupos más pequeñosutilizando la cláusula GROUP BY.
![Page 28: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/28.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT department_id, AVG(salary)FROM employeesGROUP BY department_id ;
Uso de la Cláusula GROUP BY
Todas las columnas de la lista SELECT que no estén en las funciones de grupo deben estar en la cláusula GROUP BY.
![Page 29: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/29.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de la Cláusula GROUP BY
La columna GROUP BY no tiene que estar en la lista SELECT.
SELECT AVG(salary)FROM employeesGROUP BY department_id ;
![Page 30: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/30.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Agrupación por más de una Columna
EMPLOYEES
“Sume los salarios de
la tabla EMPLOYEES
para cada cargo, agrupado por departamento.
…
![Page 31: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/31.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT department_id dept_id, job_id, SUM(salary)FROM employeesGROUP BY department_id, job_id ;
Uso de la Cláusula GROUP BY enVarias Columnas
![Page 32: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/32.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Consultas no VálidasUtilizando Funciones de Grupo
Toda columna o expresión de la lista SELECT que nosea una función agregada debe estar en lacláusula GROUP BY.
SELECT department_id, COUNT(last_name)FROM employees;
SELECT department_id, COUNT(last_name)FROM employees;
SELECT department_id, COUNT(last_name) *ERROR at line 1:ORA-00937: not a single-group group function
SELECT department_id, COUNT(last_name) *ERROR at line 1:ORA-00937: not a single-group group function
La columna falta en la cláusula GROUP BY La columna falta en la cláusula GROUP BY
![Page 33: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/33.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Consultas no VálidasUtilizando Funciones de Grupo
• No se puede utilizar la cláusula WHERE para restringir grupos.
• Utilice la cláusula HAVING para restringir grupos.
• No se pueden utilizar funciones de grupo en la cláusula WHERE.
SELECT department_id, AVG(salary)FROM employeesWHERE AVG(salary) > 8000GROUP BY department_id;
SELECT department_id, AVG(salary)FROM employeesWHERE AVG(salary) > 8000GROUP BY department_id;
WHERE AVG(salary) > 8000 *ERROR at line 3:ORA-00934: group function is not allowed here
WHERE AVG(salary) > 8000 *ERROR at line 3:ORA-00934: group function is not allowed here
No se puede utilizar la cláusula WHERE para restringir grupos No se puede utilizar la cláusula WHERE para restringir grupos
![Page 34: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/34.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Exclusión de Resultados de Grupo
El salariomáximo
por departamentocuando es mayor que
$10.000
EMPLOYEES
…
![Page 35: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/35.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT column, group_functionFROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];
Exclusión de Resultados de Grupo: La Cláusula HAVING
Utilice la cláusula HAVING para restringir grupos:
1. Las filas se agrupan.
2. Se aplica la función de grupo.
3. Se muestran los grupos que coinciden con la cláusula HAVING.
![Page 36: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/36.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de la Cláusula HAVING
SELECT department_id, MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary)>10000 ;
![Page 37: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/37.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT job_id, SUM(salary) PAYROLLFROM employeesWHERE job_id NOT LIKE '%REP%'GROUP BY job_idHAVING SUM(salary) > 13000ORDER BY SUM(salary);
Uso de la Cláusula HAVING
![Page 38: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/38.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Anidamiento de Funciones de Grupo
Visualice el salario medio máximo.
SELECT MAX(AVG(salary))FROM employeesGROUP BY department_id;
![Page 39: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/39.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de una Subconsultapara Resolver un Problema
¿Quién tiene un salario mayor que el de Abel?
¿Qué empleados tienen salarios mayores que el de Abel?
Consulta Principal:
??
¿Cuál es el salario de Abel???
Subconsulta
![Page 40: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/40.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Sintaxis de Subconsulta
• La subconsulta (consulta interna) se ejecuta una vez antes de la consulta principal.
• El resultado de la subconsulta lo utiliza la consulta principal (consulta externa).
SELECT select_listFROM tableWHERE expr operator
(SELECT select_list FROM table);
![Page 41: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/41.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT last_nameFROM employeesWHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
Uso de una Subconsulta
11000
![Page 42: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/42.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Instrucciones para Utilizar Subconsultas
• Escriba las subconsultas entre paréntesis.
• Coloque las subconsultas a la derecha de la condición de comparación.
• La cláusula ORDER BY de la subconsulta no es necesaria salvo que esté realizando un análisis N principal.
• Utilice operadores de una sola fila con subconsultas de una sola fila y operadores de varias filas con subconsultas de varias filas.
![Page 43: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/43.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Tipos de Subconsultas
Consulta principal
Subconsulta devuelve
ST_CLERK
• Subconsulta de varias filas
ST_CLERKSA_MAN
Consulta principal
Subconsulta devuelve
• Subconsulta de una sola fila
![Page 44: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/44.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Subconsultas de una Sola Fila
• Devuelven una sola fila
• Utilizan operadores de comparación de una sola fila
Operador
=
>
>=
<
<=
<>
Significado
Igual que
Mayor que
Mayor o igual que
Menor que
Menor o igual que
No igual a
![Page 45: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/45.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT last_name, job_id, salaryFROM employeesWHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141)AND salary > (SELECT salary FROM employees WHERE employee_id = 143);
Ejecución de Subconsultas de una Sola Fila
ST_CLERK
2600
![Page 46: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/46.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT last_name, job_id, salaryFROM employeesWHERE salary = (SELECT MIN(salary) FROM employees);
Uso de Funciones de Grupoen una Subconsulta
2500
![Page 47: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/47.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Cláusula HAVING con Subconsultas
• Oracle Server ejecuta en primer lugar las subconsultas.
• Oracle Server devuelve resultados a la cláusula HAVING de la consulta principal.
SELECT department_id, MIN(salary)FROM employeesGROUP BY department_idHAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);
2500
![Page 48: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/48.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Subconsultas de Varias Filas
• Devuelven más de una fila
• Utilizan operadores de comparación de varias filas
Operador
IN
ANY
ALL
Significado
Igual a cualquier miembro de la lista
Compara el valor con cada valor devuelto
por la subconsulta
Compara el valor con todos los valores
devueltos por la subconsulta
![Page 49: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/49.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso del Operador ANY en Subconsultas de Varias Filas
9000, 6000, 4200
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG';
…
![Page 50: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/50.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG';
Uso del Operador ALL en Subconsultas de Varias Filas
9000, 6000, 4200
![Page 51: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/51.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Valores Nulos en una Subconsulta
SELECT emp.last_nameFROM employees empWHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr);
no rows selectedno rows selected
![Page 52: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/52.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Funciones SQL
FunciónFunciónEntrada
arg 1arg 1
arg 2arg 2
arg arg nn
La función realizala acción
Salida
Valor deValor de resultadoresultado
![Page 53: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/53.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Dos Tipos de Funciones SQL
FuncionesFunciones
Funciones deFunciones deuna sola filauna sola fila
Funciones deFunciones de varias filasvarias filas
![Page 54: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/54.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Funciones de una Sola Fila
Las funciones de una sola fila:
• Manipulan elementos de datos.
• Aceptan argumentos y devuelven un valor.
• Actúan sobre cada fila devuelta.
• Devuelven un resultado por fila.
• Pueden modificar el tipo de dato.
• Se pueden anidar.
• Aceptan argumentos que pueden ser una columna o una expresión.
function_name [(arg1, arg2,...)]function_name [(arg1, arg2,...)]
![Page 55: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/55.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Funciones de una Sola Fila
ConversiónConversión
CarácterCarácter
NúmeroNúmero
FechaFecha
GeneralGeneral Funciones deFunciones deuna sola filauna sola fila
![Page 56: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/56.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Funciones de Caracteres
Funciones de Funciones de CaracteresCaracteres
LOWERUPPERINITCAP
CONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE
Funciones de manipulaciónFunciones de manipulación de mayúsculas/minúsculasde mayúsculas/minúsculas
Funciones de manipulaciónFunciones de manipulación de caracteresde caracteres
![Page 57: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/57.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Function Result
Funciones de Manipulación de Mayúsculas/Minúsculas
Estas funciones convierten las mayúsculas/minúsculas para cadenas de caracteres.
LOWER('SQL Course')
UPPER('SQL Course')
INITCAP('SQL Course')
sql course
SQL COURSE
Sql Course
![Page 58: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/58.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de Funciones de Manipulación de Mayúsculas/Minúsculas
Muestre el número de empleado, el nombre y el número de departamento para el empleado Higgins:
SELECT employee_id, last_name, department_idFROM employeesWHERE last_name = 'higgins';no rows selectedno rows selected
SELECT employee_id, last_name, department_idFROM employeesWHERE last_name = 'higgins';no rows selectedno rows selected
SELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = 'higgins';
![Page 59: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/59.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
CONCAT('Hello', 'World')
SUBSTR('HelloWorld',1,5)
LENGTH('HelloWorld')
INSTR('HelloWorld', 'W')
LPAD(salary,10,'*')
RPAD(salary, 10, '*')
TRIM('H' FROM 'HelloWorld')
HelloWorld
Hello
10
6
*****24000
24000*****
elloWorld
Función Resultado
Funciones de Manipulación de Caracteres
Estas funciones manipulan cadenas de caracteres:
![Page 60: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/60.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?"FROM employeesWHERE SUBSTR(job_id, 4) = 'REP';
Uso de Funciones de Manipulación de Caracteres
1
2
31 2
3
![Page 61: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/61.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Funciones Numéricas • ROUND: Redondea el valor a los decimales especificados.
ROUND(45.926, 2) 45.93
• TRUNC: Trunca el valor a los decimales especificados.
TRUNC(45.926, 2) 45.92
• MOD: Devuelve el resto de la división.
MOD(1600, 300) 100
• ABS: Calcula el valor absoluto de n.
ABS(-16) 16
• CEIL: Calcula el menor numero entero mayor o igual que n.
CEIL(16.7) 17
• FLOOR: Calcula el mayor numero entero menor o igual que n.
FLOOR(16.7) 16
• POWER: Devuelve m elevado a la n potencia, n debe ser entero
POWER(3,2) 9
![Page 62: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/62.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;
Uso de la Función ROUND
DUAL es una tabla ficticia que puede utilizar para verlos resultados de funciones y cálculos.
1 2
3
31 2
![Page 63: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/63.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;
Uso de la Función TRUNC
31 2
1 2
3
![Page 64: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/64.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = 'SA_REP';
Uso de la Función MOD
Calcule el resto de un salario después de dividirlo por 5000 para todos los empleados cuyos cargos son representantes de ventas.
![Page 65: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/65.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Trabajo con Fechas
• La base de datos Oracle almacena fechas en un formato numérico interno: siglo, año, mes, día, horas, minutos, segundos.
• El formato de visualización de fecha por defecto es DD-MON-RR.
– Le permite almacenar fechas del siglo XXI en el siglo XX especificando solamente los dos últimos dígitos del año.
– Le permite almacenar fechas del siglo XX en el siglo XXI de la misma forma.
SELECT last_name, hire_dateFROM employeesWHERE last_name like ''G%';';
![Page 66: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/66.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Trabajo con Fechas
SYSDATE es una función que devuelve:
• Fecha
• Hora
![Page 67: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/67.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Aritmética con Fechas
• Sume o reste un número a/de una fecha para producir un valor de fecha.
• Reste dos fechas para buscar el número de días entre ellas.
• Sume horas a una fecha dividiendo el número de horas por 24.
![Page 68: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/68.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de Operadores Aritméticoscon Fechas
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;
![Page 69: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/69.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Funciones de Fecha
Número de mesesentre dos fechas
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND
TRUNC
Suma meses de calendario a una fecha
Siguiente día de la fecha especificada
Último día del mes
Redondea la fecha
Trunca la fecha
Función Descripción
![Page 70: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/70.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
Uso de Funciones de Fecha
• ADD_MONTHS ('11-JAN-94',6)
• NEXT_DAY ('01-SEP-95','FRIDAY')
• LAST_DAY('01-FEB-95')
19.6774194
'11-JUL-94'
'08-SEP-95'
'28-FEB-95'
![Page 71: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/71.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Funciones de Conversión
Conversión implícitaConversión implícita de tipos de datode tipos de dato
Conversión explícitaConversión explícita de tipos de datode tipos de dato
ConversiónConversión de tipos de datode tipos de dato
![Page 72: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/72.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Conversión Implícita de Tipos de Dato
Para las asignaciones, Oracle Server puede convertir automáticamente lo siguiente:
VARCHAR2 or CHAR
De A
VARCHAR2 or CHAR
NUMBER
DATE
NUMBER
DATE
VARCHAR2
VARCHAR2
![Page 73: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/73.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Conversión Implícita de Tipos de Dato
Para la evaluación de la expresión, Oracle Serverpuede convertir automáticamente lo siguiente:
VARCHAR2 or CHAR
De A
VARCHAR2 or CHAR
NUMBER
DATE
![Page 74: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/74.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Conversión Explícita de Tipos de Dato
NUMBER CHARACTER
TO_CHAR
TO_NUMBER
DATE
TO_CHAR
TO_DATE
![Page 75: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/75.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Conversión Explícita de Tipos de Dato
NUMBER CHARACTER
TO_CHAR
TO_NUMBER
DATE
TO_CHAR
TO_DATE
![Page 76: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/76.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de la Función TO_CHAR con Fechas
El modelo de formato:
• Se debe escribir entre comillas sencillas y es sensible a mayúsculas/minúsculas.
• Puede incluir cualquier elemento de formato de fecha válido.
• Tiene un elemento fm para eliminar espacios rellenados o suprimir ceros a la izquierda.
• Se separa del valor de fecha con una coma.
TO_CHAR(date, 'format_model')TO_CHAR(date, 'format_model')
![Page 77: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/77.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
YYYY
Elementos del Modelo de Formato de Fecha
YEAR
MM
MONTH
DY
DAY
Año completo en números
Años en letra
Valor de dos dígitos para el mes
Abreviatura de tres letras del día de la semana
Nombre completo del día de lasemana
Nombre completo del mes
MON Abreviatura de tres letras del mes
DD Día del mes en número
![Page 78: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/78.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Elementos del Modelo de Formato de Fecha
• Los elementos de hora formatean la porción de hora de la fecha.
• Agregue cadenas de caracteres escribiéndolas entre comillas dobles.
• Los sufijos numéricos escriben los números en letra.
HH24:MI:SS AM 15:45:32 PM
DD "of" MONTH 12 of OCTOBER
ddspth fourteenth
![Page 79: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/79.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de la Función TO_CHAR con Fechas
SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATEFROM employees;
…
![Page 80: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/80.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de la Función TO_CHAR con Números
Estos son algunos de los elementos de formato que puede utilizar con la función TO_CHAR para mostrar un valor numérico como carácter:
TO_CHAR(number, 'format_model')TO_CHAR(number, 'format_model')
9
0
$
L
.
,
Representa un número.
Obliga a mostrar un cero.
Coloca un signo de dólar flotante.
Utiliza el símbolo de divisa local flotante.
Imprime una coma decimal.
Imprime un indicador de miles.
![Page 81: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/81.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT TO_CHAR(salary, '$99,999.00') SALARYFROM employeesWHERE last_name = 'Ernst';
Uso de la Función TO_CHAR con Números
![Page 82: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/82.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de las Funciones TO_NUMBER y TO_DATE
• Convierta una cadena de caracteres en formato numérico utilizando la función TO_NUMBER:
• Convierta una cadena de caracteres en formato de fecha utilizando la función TO_DATE:
• Estas funciones tienen un modificador fx que especifica la coincidencia exacta para el argumento de caracteres y un modelo de formato de fecha de una función TO_DATE.
TO_NUMBER(char[, 'format_model'])TO_NUMBER(char[, 'format_model'])
TO_DATE(char[, 'format_model'])TO_DATE(char[, 'format_model'])
![Page 83: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/83.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de las Funciones TO_NUMBER y TO_DATE
• Convierta una cadena de caracteres en formato numérico utilizando la función TO_NUMBER:
• Convierta una cadena de caracteres en formato de fecha utilizando la función TO_DATE:
• Estas funciones tienen un modificador fx que especifica la coincidencia exacta para el argumento de caracteres y un modelo de formato de fecha de una función TO_DATE.
TO_NUMBER(char[, 'format_model'])TO_NUMBER(char[, 'format_model'])
TO_DATE(char[, 'format_model'])TO_DATE(char[, 'format_model'])
![Page 84: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/84.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Formato de Fecha RR
Año Actual 1995199520012001
Fecha Especificada 27-OCT-9527-OCT-1727-OCT-1727-OCT-95
Formato RR 1995201720171995
Formato YY 1995191720172095
Si los dos dígitos del añoactual son:
0–49
0–49 50–99
50–99
La fecha que se devuelve está en el siglo actual.
La fecha que se devuelve está en el siglo siguiente al actual.
La fecha que se devuelve está en el siglo anterior al actual.
La fecha que se devuelve está en el siglo actual.
Si el año especificado de dos dígitos es:
![Page 85: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/85.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Ejemplo de Formato de Fecha RR
Para buscar empleados contratados antes de 1990, utilice el formato RR, que produce los mismos resultados tanto si se ejecuta el comando en 1999 o ahora:
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')FROM employeesWHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');
![Page 86: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/86.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Funciones Generales
Estas funciones trabajan con cualquier tipo de dato y están relacionadas con el uso de valores nulos.
• NVL (expr1, expr2)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, ..., exprn)
![Page 87: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/87.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Función NVL
Convierte un valor nulo en un valor real.
• Los tipos de dato que se pueden utilizar son fechas, caracteres y numéricos.
• Los tipos de dato deben coincidir:
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'No Job Yet')
![Page 88: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/88.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SALFROM employees;
Uso de la Función NVL
…
1 2
12
![Page 89: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/89.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) resultFROM employees;
Uso de la Función NULLIF
…
1
23
1 2 3
![Page 90: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/90.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de la Función COALESCE
• La ventaja de la función COALESCE sobre la función NVL es que puede tomar varios valores alternativos.
• Si la primera expresión no es nula, devuelve dicha expresión; en caso contrario, realiza una fusión (COALESCE) de las expresiones restantes.
![Page 91: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/91.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT last_name, COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;
Uso de la Función COALESCE
…
![Page 92: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/92.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Expresiones Condicionales
• Proporcionan el uso de la lógica IF-THEN-ELSE dentro de una sentencia SQL.
• Utilizan dos métodos:
– Expresión CASE
– Función DECODE
![Page 93: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/93.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Expresión CASE
Facilita las consultas condicionales realizando eltrabajo de una sentencia IF-THEN-ELSE:
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr]END
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr]END
![Page 94: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/94.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY"FROM employees;
Uso de la Expresión CASE
Facilita las consultas condicionales realizando eltrabajo de una sentencia IF-THEN-ELSE:
…
…
![Page 95: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/95.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Función DECODE
Facilita las consultas condicionales realizando el trabajo de una sentencia CASE o IF-THEN-ELSE:
DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default])
DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default])
![Page 96: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/96.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de la Función DECODE
SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARYFROM employees;
…
…
![Page 97: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/97.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de la Función DECODE
SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATEFROM employeesWHERE department_id = 80;
Muestre el tipo impositivo aplicable para cada empleado del departamento 80.
![Page 98: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/98.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Lenguaje de Manipulación de Datos
• Se ejecuta una sentencia DML cuando:
– Agrega filas nuevas a una tabla.
– Modifica las filas existentes de una tabla.
– Elimina filas existentes de una tabla.
• Una transacción consta de una recopilación de sentencias DML que forman una unidad de trabajo lógica.
![Page 99: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/99.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Adición de una Fila Nueva a una Tabla
DEPARTMENTS Fila nueva
……inserte una fila nueva en la tabla DEPARMENTS…
![Page 100: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/100.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Sintaxis de la Sentencia INSERT
• Agregue nuevas filas a una tabla mediante la sentencia INSERT.
• Con esta sintaxis sólo se inserta una fila cada vez.
INSERT INTO table [(column [, column...])]VALUES (value [, value...]);
INSERT INTO table [(column [, column...])]VALUES (value [, value...]);
![Page 101: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/101.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Inserción de Filas Nuevas
• Inserte una fila nueva que contenga valores para cada columna.
• Enumere los valores en el orden por defecto de las columnas de la tabla.
• Opcionalmente, liste las columnas en la cláusula INSERT.
• Escriba los valores de caracteres y de fecha entre comillas simples.
INSERT INTO departments(department_id, department_name, manager_id, location_id)VALUES (70, 'Public Relations', 100, 1700);1 row created.
![Page 102: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/102.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
INSERT INTO departmentsVALUES (100, 'Finance', NULL, NULL);1 row created.1 row created.
INSERT INTO departments (department_id, department_name )VALUES (30, 'Purchasing');1 row created.1 row created.
Inserción de Filas con Valores Nulos
• Método implícito: Omita la columna de la lista de columnas.
• Método explícito: Especifique la palabra clave NULL en la cláusula VALUES.
![Page 103: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/103.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100);1 row created.1 row created.
Inserción de Valores Especiales
La función SYSDATE registra la fecha y la hora actuales.
![Page 104: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/104.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
INSERT INTO employeesVALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30);1 row created.1 row created.
Inserción de Valores de Fecha Específicos
• Agregue un empleado nuevo.
• Verifique la adición.
![Page 105: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/105.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
• Escriba la sentencia INSERT con una subconsulta.
• No utilice la cláusula VALUES.
• Haga coincidir el número de columnas de la cláusula INSERT con el de la subconsulta.
INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%';
4 rows created.4 rows created.
Copia de Filasdesde otra Tabla
![Page 106: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/106.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Cambio de los Datos de una Tabla
EMPLOYEES
Actualice las filas de la tabla EMPLOYEES.
![Page 107: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/107.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Sintaxis de la Sentencia UPDATE
• Modifique las filas existentes con la sentencia UPDATE.
• Actualice más de una fila cada vez si es necesario.
UPDATE tableSET column = value [, column = value, ...][WHERE condition];
UPDATE tableSET column = value [, column = value, ...][WHERE condition];
![Page 108: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/108.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
UPDATE employeesSET department_id = 70WHERE employee_id = 113;1 row updated.1 row updated.
• Si incluye la cláusula WHERE, las filas específicas se modifican.
• Se modifican todas las filas de la tabla si omite la cláusula WHERE.
Actualización de las Filas de una Tabla
UPDATE copy_empSET department_id = 110;22 rows updated.22 rows updated.
UPDATE copy_empSET department_id = 110;22 rows updated.22 rows updated.
![Page 109: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/109.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
UPDATE employeesSET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114;1 row updated.1 row updated.
Actualización de Dos Columnascon una Subconsulta
Actualice el cargo y el salario del empleado 114 para quecoincida con el del empleado 205 .
![Page 110: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/110.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
UPDATE copy_empSET department_id = (SELECT department_id FROM employees WHERE employee_id = 100)WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200);1 row updated.1 row updated.
Actualización de Filas Basándose en otra Tabla
Utilice subconsultas en sentencias UPDATE para actualizar las filas de una tabla basándose en valores de otra tabla.
![Page 111: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/111.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
UPDATE employees *ERROR at line 1:ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found
UPDATE employees *ERROR at line 1:ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found
UPDATE employeesSET department_id = 55WHERE department_id = 110;
UPDATE employeesSET department_id = 55WHERE department_id = 110;
Actualización de Filas:Error de Restricción de Integridad
El número de departamento 55 no existe
![Page 112: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/112.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Suprima una fila de la tabla DEPARTMENTS
Eliminación de una Fila de una Tabla
DEPARTMENTS
![Page 113: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/113.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Sentencia DELETE
Puede eliminar las filas existentes de una tablautilizando la sentencia DELETE.
DELETE [FROM] table[WHERE condition];
DELETE [FROM] table[WHERE condition];
![Page 114: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/114.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
• Se suprimen filas específicas si incluye la cláusula WHERE.
• Se suprimen todas las filas de la tabla si omite la cláusula WHERE.
Supresión de Filas de una Tabla
DELETE FROM departments WHERE department_name = 'Finance';1 row deleted.1 row deleted.
DELETE FROM departments WHERE department_name = 'Finance';1 row deleted.1 row deleted.
DELETE FROM copy_emp;22 rows deleted.22 rows deleted.
DELETE FROM copy_emp;22 rows deleted.22 rows deleted.
![Page 115: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/115.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
DELETE FROM employeesWHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%');1 row deleted.1 row deleted.
Supresión de Filas Basándose en otra Tabla
Utilice subconsultas en sentencias DELETE para eliminar las filas de una tabla basándose en los valores de otra tabla.
![Page 116: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/116.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Supresión de Filas:Error de Restricción de Integridad
DELETE FROM departmentsWHERE department_id = 60;
DELETE FROM departmentsWHERE department_id = 60;
DELETE FROM departments *ERROR at line 1:ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
DELETE FROM departments *ERROR at line 1:ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
No puede suprimir una fila que contenga una clave primaria utilizada como clave ajena en otra tabla.
![Page 117: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/117.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de una Subconsulta enuna Sentencia INSERT
INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50) VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000, 50);
1 row created.
INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50) VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000, 50);
1 row created.
![Page 118: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/118.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de una Subconsulta enuna Sentencia INSERT
• Verifique los resultadosSELECT employee_id, last_name, email, hire_date, job_id, salary, department_idFROM employeesWHERE department_id = 50;
SELECT employee_id, last_name, email, hire_date, job_id, salary, department_idFROM employeesWHERE department_id = 50;
![Page 119: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/119.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de la Palabra Clave WITH CHECK OPTION en Sentencias DML
• Se utiliza una subconsulta para identificar la tabla y las columnas de la sentencia DML.
• La palabra clave WITH CHECK OPTION le prohíbe que cambie filas que no están en la subconsulta.
INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id = 50 WITH CHECK OPTION)VALUES (99998, 'Smith', 'JSMITH', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000);INSERT INTO *ERROR at line 1:ORA-01402: view WITH CHECK OPTION where-clause violation
INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id = 50 WITH CHECK OPTION)VALUES (99998, 'Smith', 'JSMITH', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000);INSERT INTO *ERROR at line 1:ORA-01402: view WITH CHECK OPTION where-clause violation
![Page 120: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/120.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Visión General de la Función Valorpor Defecto Explícito
• Con la función valor por defecto explícito, puede utilizar la palabra clave DEFAULT como valor de columna donde desee el valor por defecto de columna.
• Esta función se agrega para asegurar el cumplimiento con el estándar SQL: 1999.
• Esto permite al usuario controlar dónde y cuándo se debe aplicar el valor por defecto a los datos.
• Se pueden utilizar valores por defecto explícitos en sentencias INSERT y UPDATE.
![Page 121: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/121.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de Valores por Defecto Explícitos
INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, 'Engineering', DEFAULT);
INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, 'Engineering', DEFAULT);
UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10;
UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10;
• DEFAULT con INSERT:
• DEFAULT con UPDATE:
![Page 122: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/122.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Sentencia MERGE
• Proporciona la capacidad de actualizar o insertar datos condicionalmente en una tabla de base de datos
• Realiza una actualización (UPDATE) si existe la fila y una inserción (INSERT) si es una fila nueva:
– Evita actualizaciones separadas.
– Aumenta el rendimiento y la facilidad de uso.
– Es útil en aplicaciones de almacenes de datos.
![Page 123: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/123.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Sintaxis de la Sentencia MERGE
Puede insertar o actualizar filas condicionalmente enuna tabla utilizando la sentencia MERGE.
MERGE INTO table_name table_alias USING (table|view|sub_query) alias ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);
MERGE INTO table_name table_alias USING (table|view|sub_query) alias ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);
![Page 124: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/124.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, ... c.department_id = e.department_idWHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id);
Fusión de Filas
Inserte o actualice filas en la tabla COPY_EMP para quecoincida con la tabla EMPLOYEES.
![Page 125: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/125.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Fusión de Filas
MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET ...WHEN NOT MATCHED THEN INSERT VALUES...;
MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET ...WHEN NOT MATCHED THEN INSERT VALUES...;
SELECT * FROM COPY_EMP;
no rows selected
SELECT * FROM COPY_EMP;
no rows selected
SELECT * FROM COPY_EMP;
20 rows selected.
SELECT * FROM COPY_EMP;
20 rows selected.
![Page 126: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/126.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Objetos de Base de Datos
Objeto Descripción
Tabla Unidad básica de almacenamiento; está
formada por filas y columnas.
Vista Representa lógicamente subjuegos de datos de una o varias tablas.
Secuencia Generador de valor numérico.
Índice Mejora el rendimiento de algunas
consultas.
Sinónimo Proporciona nombres alternativos a
objetos.
![Page 127: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/127.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Reglas de Nomenclatura
Los nombres de tablas y columnas:
• Deben empezar por una letra.
• Deben tener entre 1 y 30 caracteres.
• Sólo deben contener A–Z, a–z, 0–9, _, $ y #.
• No deben duplicar el nombre de otro objeto propiedad del mismo usuario.
• No deben ser palabras reservadas de Oracle Server.
![Page 128: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/128.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Sentencia CREATE TABLE
• Debe tener:
– El privilegio CREATE TABLE
– Un área de almacenamiento
• Especifique:
– Nombre de tabla
– Nombre de columna, tipo de dato de columna y tamaño de columna
CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);
![Page 129: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/129.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Referencia a Tablas de otro Usuario
• Las tablas pertenecientes a otros usuarios no están en el esquema del usuario.
• Debe utilizar el nombre de propietario como prefijo para dichas tablas.
![Page 130: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/130.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Opción DEFAULT
• Especifique un valor por defecto para una columna durante una inserción.
• Los valores literales, las expresiones o las funciones SQL son valores válidos.
• El nombre de otra columna o una pseudocolumna son valores no válidos.
• El tipo de dato por defecto debe coincidir con el tipo de dato de columna.
... hire_date DATE DEFAULT SYSDATE, ...
![Page 131: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/131.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
• Cree la tabla.
• Confirme la creación de la tabla.
Creación de Tablas
CREATE TABLE dept(deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13));
Table created.Table created.
DESCRIBE dept
![Page 132: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/132.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Tablas de la Base de Datos Oracle
• Tablas de Usuario:
– Son una recopilación de tablas creadas y mantenidas por el usuario
– Contienen información de usuario
• Diccionario de Datos:
– Es una recopilación de tablas creadas y mantenidas por Oracle Server
– Contienen información de la base de datos
![Page 133: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/133.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT table_name FROM user_tables ;
SELECT * FROM user_catalog ;
Consulta del Diccionario de Datos
• Vea tablas, vistas, sinónimos y secuencias propiedad del usuario.
• Vea los nombres de tablas propiedad del usuario.
SELECT DISTINCT object_type FROM user_objects ;
• Vea tipos de objeto distintos propiedad del usuario.
![Page 134: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/134.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Tipos de Dato Tipo de Dato Descripción
VARCHAR2(size) Dato de caracteres de longitud variable
CHAR(size) Dato de caracteres de longitud fija
NUMBER(p,s) Dato numérico de longitud variable
DATE Valores de fecha y hora
LONG Dato de caracteres de longitud variable de hasta 2 gigabytes
CLOB Dato de caracteres de hasta 4 gigabytes
RAW y LONG RAW Dato raw binario
BLOB Dato binario de hasta 4 gigabytes
BFILE Dato binario almacenado en un archivoexterno; hasta 4 gigabytes
ROWID Sistema numérico de base 64 que representa la dirección única de una fila en su tabla.
![Page 135: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/135.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de una Tabla Utilizando una Sintaxis de Subconsulta
• Cree una tabla e inserte filas combinando la sentencia CREATE TABLE y la opción AS subquery.
• Haga coincidir el número de columnas especificadas con el número de columnas de subconsulta.
• Defina columnas con nombres de columna y valores por defecto.
CREATE TABLE table [(column, column...)]AS subquery;
![Page 136: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/136.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de una Tabla Utilizandouna Subconsulta
DESCRIBE dept80
CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80;Table created.Table created.
![Page 137: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/137.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Sentencia ALTER TABLE
Utilice la sentencia ALTER TABLE para:
• Agregar una columna nueva
• Modificar una columna existente
• Definir un valor por defecto para la nueva columna
• Borrar una columna
![Page 138: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/138.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Sentencia ALTER TABLE
Utilice la sentencia ALTER TABLE para agregar, modificar o borrar columnas.
ALTER TABLE tableADD (column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE tableMODIFY (column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE tableDROP (column);
![Page 139: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/139.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Adición de una Columna
DEPT80
“Agregue una columna nueva a la tabla DEPT80”.
DEPT80
Columna nueva
![Page 140: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/140.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Adición de una Columna
• Puede utilizar la cláusula ADD para agregar columnas.
• La nueva columna pasa a ser la última.
ALTER TABLE dept80ADD (job_id VARCHAR2(9));Table altered.Table altered.
![Page 141: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/141.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Modificación de una Columna
• Puede cambiar el tipo de dato, el tamaño y el valor por defecto de una columna.
• Un cambio en el valor por defecto sólo afecta a las inserciones posteriores en la tabla.
ALTER TABLE dept80MODIFY (last_name VARCHAR2(30));Table altered.Table altered.
![Page 142: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/142.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Eliminación de una Columna
Utilice la cláusula DROP COLUMN para borrar lascolumnas que ya no necesite de la tabla.
ALTER TABLE dept80DROP COLUMN job_id; Table altered.Table altered.
![Page 143: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/143.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
ALTER TABLE tableSET UNUSED (column);
ALTER TABLE tableSET UNUSED COLUMN column;
ALTER TABLE tableSET UNUSED (column);
ALTER TABLE tableSET UNUSED COLUMN column;
La Opción SET UNUSED
• Utilice la opción SET UNUSED para marcar una o varias columnas como no utilizadas.
• Utilice la opción DROP UNUSED COLUMNS para eliminar las columnas marcadas como no utilizadas.
OR
ALTER TABLE tableDROP UNUSED COLUMNS;
ALTER TABLE tableDROP UNUSED COLUMNS;
![Page 144: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/144.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Eliminación de una Tabla
• Se suprimen todos los datos y la estructura de la tabla.
• Se valida cualquier transacción pendiente.
• Se borran todos los índices.
• No puede realizar rollback de la sentencia DROP TABLE.
DROP TABLE dept80;Table dropped.Table dropped.
![Page 145: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/145.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Cambio del Nombre de un Objeto
• Para cambiar el nombre de una tabla, una vista, una secuencia o un sinónimo, ejecute la sentencia RENAME.
• Debe ser el propietario del objeto.
RENAME dept TO detail_dept;Table renamed.Table renamed.
![Page 146: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/146.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Truncamiento de una Tabla
• La sentencia TRUNCATE TABLE:
– Elimina todas las filas de una tabla.
– Libera el espacio de almacenamiento utilizado por dicha tabla.
• No puede realizar rollback de la eliminación de filas si utiliza TRUNCATE.
• También puede eliminar filas utilizando la sentencia DELETE.
TRUNCATE TABLE detail_dept;Table truncated.Table truncated.
![Page 147: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/147.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Adición de Comentarios a una Tabla
• Puede agregar comentarios a una tabla o a una columna utilizando la sentencia COMMENT.
• Los comentarios se pueden visualizar a través de las vistas del diccionario de datos:
– ALL_COL_COMMENTS
– USER_COL_COMMENTS
– ALL_TAB_COMMENTS
– USER_TAB_COMMENTS
COMMENT ON TABLE employeesIS 'Employee Information';Comment created.Comment created.
![Page 148: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/148.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Resumen
Sentencia Descripción
CREATE TABLE Crea una tabla.
ALTER TABLE Modifica estructuras de tabla.
DROP TABLE Elimina las filas y la estructura de la tabla.
RENAME Cambia el nombre de una tabla, vista, secuencia o sinónimo.
TRUNCATE Elimina todas las filas de una tabla y libera el espacio de almacenamiento.
COMMENT Agrega comentarios a una tabla o una vista.
En esta lección, debería haber aprendido a utilizar sentencias DDL para crear, modificar, borrar y cambiar el nombre a tablas.
![Page 149: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/149.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
¿Qué Son las Restricciones?
• Las restricciones fuerzan las reglas a nivel de tabla.
• Las restricciones evitan la supresión de una tabla si hay dependencias.
• Son válidos los siguientes tipos de restricción:
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK
![Page 150: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/150.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Instrucciones sobre Restricciones
• Asigne un nombre a una restricción; si no lo hace, Oracle Server genera un nombre con el formato SYS_Cn.
• Cree una restricción:
– Al mismo tiempo que se crea la tabla, o bien
– Una vez creada la tabla.
• Defina una restricción a nivel de columna o de tabla.
• Visualice una restricción en el diccionario de datos.
![Page 151: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/151.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Definición de Restricciones
CREATE TABLE [schema.]table (column datatype [DEFAULT expr]
[column_constraint],...[table_constraint][,...]);
CREATE TABLE employees( employee_id NUMBER(6), first_name VARCHAR2(20), ... job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));
![Page 152: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/152.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Definición de Restricciones
• Nivel de restricción de columna
• Nivel de restricción de tabla
column [CONSTRAINT constraint_name] constraint_type,column [CONSTRAINT constraint_name] constraint_type,
column,... [CONSTRAINT constraint_name] constraint_type (column, ...),
column,... [CONSTRAINT constraint_name] constraint_type (column, ...),
![Page 153: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/153.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Restricción NOT NULL
Asegura que no se permiten valores nulos para la columna:
Restricción NOT NULL (Ninguna fila puede contenerun valor nulo paraesta columna)
Ausencia de restricción NOT NULL (Cualquier fila puede contener un valor nulo para esta columna)
Restricción NOT NULL
…
![Page 154: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/154.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,...
La Restricción NOT NULL
Se define a nivel de columna:
Nombrada
por el sistema
Nombradapor el usuario
![Page 155: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/155.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Restricción UNIQUE
EMPLOYEES Restricción UNIQUE
INSERT INTO
No permitido: ya existe
Permitido
…
![Page 156: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/156.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Restricción UNIQUE
Definida a nivel de tabla o de columna:
CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL,... CONSTRAINT emp_email_uk UNIQUE(email));
![Page 157: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/157.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Restricción PRIMARY KEY
DEPARTMENTS PRIMARY KEY
INSERT INTONo permitido (valor nulo)
No permitido (50 ya existe)
…
![Page 158: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/158.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
CREATE TABLE departments( department_id NUMBER(4), department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL, manager_id NUMBER(6), location_id NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id));
La Restricción PRIMARY KEY
Definida a nivel de tabla o de columna:
![Page 159: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/159.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Restricción FOREIGN KEY DEPARTMENTS
EMPLOYEESFOREIGNKEY
INSERT INTO No permitido(9 no existe)
PermitidoPermitido
PRIMARYKEY
…
…
![Page 160: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/160.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Restricción FOREIGN KEY
Definida a nivel de tabla o de columna:
CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL,... department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE(email));
![Page 161: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/161.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Palabras Clave de la Restricción FOREIGN KEY
• FOREIGN KEY: Define la columna de la tabla secundaria a nivel de restricción de tabla.
• REFERENCES: Identifica la tabla y la columna en la tabla principal.
• ON DELETE CASCADE: Suprime las filas dependientes de la tabla secundaria cuando se suprime una fila en la tabla principal.
• ON DELETE SET NULL: Convierte los valores de clave ajena dependientes en valores nulos.
![Page 162: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/162.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
La Restricción CHECK
• Define una condición que debe satisfacer cada fila.
• No se permiten las siguientes expresiones:
– Referencias a las pseudocolumnas CURRVAL, NEXTVAL, LEVEL y ROWNUM
– Llamadas a las funciones SYSDATE, UID, USER y USERENV
– Consultas que hagan referencia a otros valores de otras filas
..., salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0),...
![Page 163: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/163.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Adición de una Sintaxis de Restricción
Utilice la sentencia ALTER TABLE para:
• Agregar o borrar una restricción, sin modificar su estructura
• Activar o desactivar restricciones
• Agregar una restricción NOT NULL utilizando la cláusula MODIFY
ALTER TABLE table ADD [CONSTRAINT constraint] type (column);
ALTER TABLE table ADD [CONSTRAINT constraint] type (column);
![Page 164: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/164.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Adición de una Restricción
Agregue una restricción FOREIGN KEY a la tablaEMPLOYEES que indique que ya debe existir undirector como empleado válido en la tabla EMPLOYEES.
ALTER TABLE employeesADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);Table altered.Table altered.
![Page 165: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/165.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Eliminación de una Restricción
• Elimine la restricción de director de la tabla EMPLOYEES.
• Elimine la restricción PRIMARY KEY de la tabla DEPARTMENTS y borre la restricción FOREIGN KEY asociada en la columna EMPLOYEES.DEPARTMENT_ID.
ALTER TABLE employeesDROP CONSTRAINT emp_manager_fk;Table altered.Table altered.
ALTER TABLE employeesDROP CONSTRAINT emp_manager_fk;Table altered.Table altered.
ALTER TABLE departmentsDROP PRIMARY KEY CASCADE;Table altered.Table altered.
ALTER TABLE departmentsDROP PRIMARY KEY CASCADE;Table altered.Table altered.
![Page 166: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/166.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Desactivación de Restricciones
• Ejecute la cláusula DISABLE de la sentencia ALTER TABLE para desactivar una restricción de integridad.
• Aplique la opción CASCADE para desactivar restricciones de integridad dependientes.
ALTER TABLE employeesDISABLE CONSTRAINT emp_emp_id_pk CASCADE;Table altered.Table altered.
ALTER TABLE employeesDISABLE CONSTRAINT emp_emp_id_pk CASCADE;Table altered.Table altered.
![Page 167: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/167.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Activación de Restricciones
• Active una restricción de integridad desactivada actualmente en la definición de tabla utilizando la cláusula ENABLE.
• Se crea un índice UNIQUE o PRIMARY KEY automáticamente si activa una restricción de clave UNIQUE o PRIMARY KEY.
ALTER TABLE employeesENABLE CONSTRAINT emp_emp_id_pk;Table altered.Table altered.
ALTER TABLE employeesENABLE CONSTRAINT emp_emp_id_pk;Table altered.Table altered.
![Page 168: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/168.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Restricciones en Cascada
• La cláusula CASCADE CONSTRAINTS se utiliza junto con la cláusula DROP COLUMN.
• La cláusula CASCADE CONSTRAINTS borra todas las restricciones de integridad referenciales que hacen referencia a las claves primaria y única definidas en las columnas borradas.
• La cláusula CASCADE CONSTRAINTS también borra todas las restricciones de varias columnas definidas en las columnas borradas.
![Page 169: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/169.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Restricciones en Cascada
Ejemplo:
ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;Table altered.Table altered.
ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;Table altered.Table altered.
ALTER TABLE test1 DROP (pk, fk, col1) CASCADE CONSTRAINTS;Table altered.Table altered.
ALTER TABLE test1 DROP (pk, fk, col1) CASCADE CONSTRAINTS;Table altered.Table altered.
![Page 170: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/170.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT constraint_name, constraint_type,search_condition
FROM user_constraintsWHERE table_name = 'EMPLOYEES';
Visualización de Restricciones
Consulte la tabla USER_CONSTRAINTS para visualizartodas las definiciones y los nombres de restricciones.
…
![Page 171: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/171.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT constraint_name, column_nameFROM user_cons_columnsWHERE table_name = 'EMPLOYEES';
Visualización de las ColumnasAsociadas a Restricciones
Visualice las columnas asociadas a los nombres de restricción en la vista USER_CONS_COLUMNS.
…
![Page 172: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/172.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Descripción
Unidad básica de almacenamiento; está formada por filas y columnas.
Representa lógicamente subjuegos de datos deuna o más tablas.
Genera valores de clave primaria.
Mejora el rendimiento de algunas consultas.
Nombre alternativo para un objeto.
Objeto
Tabla
Vista
Secuencia
Índice
Sinónimo
Objetos de una base de datosVISTA
![Page 173: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/173.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Tabla EMPLOYEES:
¿Qué es una Vista?
![Page 174: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/174.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
¿Para qué se usan las Vistas?
•Para restringir el acceso a los datos•Para simplificar las consultas complejas•Para proporcionar independencia de datos•Para presentar diferentes vistas de los mismos datos
![Page 175: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/175.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Función Vistas Simples Vistas Complejas
Número de tablas Una Una o varias
Contiene funciones No Sí
Contiene grupos de datos No Sí
Operaciones DMLa través de una vista Sí No siempre
Vistas simples y vistas complejas
![Page 176: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/176.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de una Vista
• Embeba una subconsulta dentro de la sentencia CREATE VIEW.
• La subconsulta puede contener sintaxis SELECT compleja.
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY [CONSTRAINT constraint]];
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY [CONSTRAINT constraint]];
![Page 177: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/177.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de una Vista
• Cree una vista, EMPVU80, que contenga detalles de los empleados del departamento 80.
• Describa la estructura de la vista utilizando el comando DESCRIBE de iSQL*Plus.
DESCRIBE empvu80DESCRIBE empvu80
CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80;View created.View created.
![Page 178: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/178.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de una Vista
• Cree una vista utilizando alias de columna en la subconsulta.
• Seleccione las columnas desde esta vista mediante los nombres de alias especificados.
CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50;View created.View created.
![Page 179: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/179.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT *FROM salvu50;
Recuperación de datos de una vista
![Page 180: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/180.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
USER_VIEWSUSER_VIEWS EMPVU80EMPVU80SELECT employee_id, last_name, salaryFROM employeesWHERE department_id=80;
iSQL*Plus
SELECT *FROM empvu80;
EMPLOYEES
Oracle ServerOracle Server
Consulta de una vista
![Page 181: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/181.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Modificación de una Vista
• Modifique la vista EMPVU80 utilizando la cláusula CREATE OR REPLACE VIEW. Agregue un alias para cada nombre de columna.
• Los alias de columna de la cláusula CREATE VIEW están enumerados en el mismo orden que las columnas de la subconsulta.
CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id)AS SELECT employee_id, first_name || ' ' || last_name, salary, department_id FROM employees WHERE department_id = 80;View created.View created.
![Page 182: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/182.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de una Vista Compleja
Cree una vista compleja que contenga funciones de grupo para visualizar los valores de dos tablas.
CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name;View created.View created.
![Page 183: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/183.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Reglas para la Realización de Operaciones DML en una Vista
• Puede realizar operaciones DML en vistas simples.
• No puede eliminar una fila si la vista contiene:
– Funciones de grupo
– Una cláusula GROUP BY
– La palabra clave DISTINCT
![Page 184: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/184.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Reglas para la Realización de Operaciones DML en una Vista
No puede modificar datos en una vista si ésta contiene:
• Funciones de grupo
• Una cláusula GROUP BY
• La palabra clave DISTINCT
• Columnas definidas por expresiones
![Page 185: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/185.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Reglas para la Realización de Operaciones DML en una Vista
No puede agregar datos a través de una vista si ésta incluye:
• Funciones de grupo
• Una cláusula GROUP BY
• La palabra clave DISTINCT
• Columnas definidas por expresiones
• Columnas NOT NULL en las tablas base que no estén seleccionadas por la vista
![Page 186: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/186.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
• Se puede asegurar de que las operaciones DML realizadas en la vista permanecen dentro del dominio de la misma utilizando la cláusula WITH CHECK OPTION.
• Cualquier intento de cambiar el número de departamento para una fila en la vista falla porque viola la restricción WITH CHECK OPTION.
CREATE OR REPLACE VIEW empvu20AS SELECT * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck ;View created.View created.
Uso de la Cláusula WITH CHECK OPTION
![Page 187: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/187.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Denegación de Operaciones DML
• Puede asegurarse de que no se produce ninguna operación DML al agregar la opción WITH READ ONLY a la definición de la vista.
• Cualquier intento de realizar un DML en una fila de la vista da como resultado un error de Oracle Server.
![Page 188: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/188.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Denegación de Operaciones DML
CREATE OR REPLACE VIEW empvu10 (employee_number, employee_name, job_title)AS SELECT employee_id, last_name, job_id FROM employees WHERE department_id = 10 WITH READ ONLY;View created.View created.
![Page 189: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/189.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
DROP VIEW empvu80;View dropped.View dropped.
DROP VIEW view;DROP VIEW view;
Eliminación de una Vista
Puede eliminar una vista sin perder datos ya que la vista se basa en tablas subyacentes de la base de datos.
![Page 190: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/190.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
•Las consultas de N principales solicitan los n valores más grandes o más pequeños de una columna. Por ejemplo:
¿Cuáles son los diez productos más vendidos?¿Cuáles son los diez productos menos vendidos?
•Tanto los juegos de valores más grandes como los más pequeños se consideran consultas de N principales.
Análisis de los N principales
![Page 191: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/191.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Ejecución de Análisis de los“N Principales”
La estructura de nivel superior de una consulta de análisis de N principales es:
SELECT [column_list], ROWNUM FROM (SELECT [column_list] FROM table ORDER BY Top-N_column)WHERE ROWNUM <= N;
SELECT [column_list], ROWNUM FROM (SELECT [column_list] FROM table ORDER BY Top-N_column)WHERE ROWNUM <= N;
![Page 192: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/192.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Ejemplo de Análisis de los N Principales
Para visualizar los salarios y los nombres de los tres empleados que más ganan desde la tabla EMPLOYEES:
SELECT ROWNUM as RANK, last_name, salary FROM (SELECT last_name,salary FROM employees ORDER BY salary DESC)WHERE ROWNUM <= 3;
31 2
1 2 3
![Page 193: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/193.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Objetos de Base de Datos
Descripción
Unidad básica de almacenamiento; está formada por filas y columnas.
Representa lógicamente subjuegos de datos de una o más tablas.
Genera valores de clave primaria.
Mejora el rendimiento de algunas consultas.
Nombre alternativo para un objeto.
Objeto
Tabla
Vista
Secuencia
Índice
Sinónimo
![Page 194: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/194.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
¿Qué es una Secuencia?
Una secuencia:
• Genera automáticamente números únicos.
• Es un objeto compartible.
• Normalmente se utiliza para crear un valor de clave primaria.
• Sustituye al código de aplicación.
• Acelera la eficacia del acceso a los valores de secuencia al almacenarse en memoria caché.
![Page 195: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/195.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Sintaxis de la SentenciaCREATE SEQUENCE
Defina una secuencia para generar números secuencialesautomáticamente:
CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
![Page 196: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/196.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de una Secuencia
• Cree una secuencia llamada DEPT_DEPTID_SEQ para utilizarla para la clave primaria de la tabla DEPARTMENTS.
• No utilice la opción CYCLE.
CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;Sequence created.Sequence created.
CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;Sequence created.Sequence created.
![Page 197: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/197.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Confirmación de Secuencias
• Verifique los valores de secuencia en la tabla USER_SEQUENCES del diccionario de datos.
• La columna LAST_NUMBER muestra el siguiente número de secuencia disponible si se especifica NOCACHE.
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;
![Page 198: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/198.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Pseudocolumnas NEXTVAL y CURRVAL
• NEXTVAL devuelve el siguiente valor de secuencia disponible. Devuelve un valor único cada vez que se hace referencia a ella, incluso para usuarios distintos.
• CURRVAL obtiene el valor de la secuencia actual.
• NEXTVAL se debe emitir para dicha secuencia antes de que CURRVAL contenga un valor.
![Page 199: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/199.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de una Secuencia
• Inserte un nuevo departamento llamado “Support” en el identificador de ubicación 2500.
• Visualice el valor actual para la secuencia DEPT_DEPTID_SEQ.
INSERT INTO departments(department_id, department_name, location_id)VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);1 row created.1 row created.
INSERT INTO departments(department_id, department_name, location_id)VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);1 row created.1 row created.
SELECT dept_deptid_seq.CURRVALFROM dual;
SELECT dept_deptid_seq.CURRVALFROM dual;
![Page 200: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/200.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Uso de una Secuencia
• El almacenamiento en memoria caché de valores de secuencia proporciona un acceso más rápido a dichos valores.
• Los intervalos en los valores de secuencia se pueden producir cuando:
– Se realiza un rollback.
– El sistema falla.
– Se está utilizando una secuencia en otra tabla.
• Si la secuencia se creó con NOCACHE, visualice el siguiente valor disponible, consultando la tabla USER_SEQUENCES.
![Page 201: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/201.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Modificación de una Secuencia
Cambie el valor de aumento, el valor máximo, el valor mínimo, la opción de ciclo o la opción de caché.
ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;Sequence altered.Sequence altered.
ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;Sequence altered.Sequence altered.
![Page 202: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/202.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Instrucciones para la Modificación de una Secuencia
• Debe ser el propietario o tener el privilegio ALTER para la secuencia.
• Sólo se ven afectados los números de secuencia futuros.
• Se debe borrar y volver a crearla secuencia para reiniciarla en un número diferente.
• Se realiza alguna validación.
![Page 203: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/203.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Eliminación de una Secuencia
• Elimine una secuencia del diccionario de datos utilizando la sentencia DROP SEQUENCE.
• Una vez que se ha eliminado, ya no se puede hacer referencia a la secuencia.
DROP SEQUENCE dept_deptid_seq;Sequence dropped.Sequence dropped.
DROP SEQUENCE dept_deptid_seq;Sequence dropped.Sequence dropped.
![Page 204: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/204.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
¿Qué es un Índice?
Un índice:
• Es un objeto de esquema.
• Oracle Server lo utiliza para acelerar la recuperación de filas utilizando un puntero.
• Puede reducir la E/S de disco mediante un método de ruta de acceso rápido para encontrar datos.
• Es independiente de su tabla indexada.
• Oracle Server lo utiliza y mantiene automáticamente.
![Page 205: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/205.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
¿Cómo Se Crean los Índices?
• Automáticamente: Un índice único se crea automáticamente al definir una restricción UNIQUE o PRIMARY KEY en una definición de tabla.
• Manualmente: Los usuarios pueden crear índices no únicos en columnas para acelerar el acceso a las filas.
![Page 206: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/206.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación de un Índice
• Cree un índice en una o varias columnas.
• Mejore la velocidad de acceso de consulta a la columna LAST_NAME de la tabla EMPLOYEES.
CREATE INDEX emp_last_name_idxON employees(last_name);Index created.Index created.
CREATE INDEX emp_last_name_idxON employees(last_name);Index created.Index created.
CREATE INDEX indexON table (column[, column]...);
CREATE INDEX indexON table (column[, column]...);
![Page 207: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/207.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Cuándo Se Crea un Índice
Debe crear un índice si:
• Una columna contiene un amplio rango de valores.
• Una columna contiene un gran número de valores nulos.
• Una o más columnas se utilizan juntas frecuentemente en una cláusula WHERE o en una condición de unión.
• La tabla es grande y se espera que la mayoría de las consultas recuperen menos del 2-4 por ciento de las filas.
![Page 208: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/208.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Cuándo No Se Crea un Índice
Normalmente no merece la pena crear un índice si:
• La tabla es pequeña.
• Las columnas no se suelen utilizar como condición en la consulta.
• Se espera que la mayoría de las consultas recuperen más del 2-4 por ciento de las filas de la tabla.
• La tabla se actualiza frecuentemente.
• Se hace referencia a las columnas indexadas como parte de una expresión.
![Page 209: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/209.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
SELECT ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns icWHERE ic.index_name = ix.index_nameAND ic.table_name = 'EMPLOYEES';
Confirmación de Índices
• La vista USER_INDEXES del diccionario de datos contiene el nombre del índice y su unicidad.
• La vista USER_IND_COLUMNS contiene el nombre del índice, el nombre de la tabla y el nombre de la columna.
![Page 210: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/210.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Índices Basados en Funciones
• Un índice basado en función es un índice basado en expresiones.
• La expresión de índice se crea desde columnas de tabla, constantes, funciones SQL y funciones definidas por el usuario.
CREATE INDEX upper_dept_name_idxON departments(UPPER(department_name));
Index created.
SELECT *FROM departmentsWHERE UPPER(department_name) = 'SALES';
![Page 211: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/211.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Eliminación de un Índice
• Elimine un índice del diccionario de datos utilizando el comando DROP INDEX.
• Elimine el índice UPPER_LAST_NAME_IDX del diccionario de datos.
• Para borrar un índice, debe ser el propietario del mismo o tener el privilegio DROP ANY INDEX.
DROP INDEX upper_last_name_idx;Index dropped.Index dropped.
DROP INDEX upper_last_name_idx;Index dropped.Index dropped.
DROP INDEX index;DROP INDEX index;
![Page 212: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/212.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Sinónimos
Simplifique el acceso a los objetos mediante la creación de un sinónimo (un objeto con otro nombre). Con lossinónimos, puede:
• Facilitar la referencia a una tabla propiedad de otro usuario
• Reducir nombres largos de objeto.
CREATE [PUBLIC] SYNONYM synonymFOR object;
CREATE [PUBLIC] SYNONYM synonymFOR object;
![Page 213: 2.- Oracle Operaciones Basicas](https://reader036.vdocuments.co/reader036/viewer/2022062301/563dbb4d550346aa9aac03f0/html5/thumbnails/213.jpg)
Copyright © Oracle Corporation, 2001. Todos los derechos reservados.
Creación y Eliminación de Sinónimos
• Cree una abreviatura para la vista DEPT_SUM_VU.
• Borre un sinónimo.
CREATE SYNONYM d_sumFOR dept_sum_vu;Synonym Created.Synonym Created.
CREATE SYNONYM d_sumFOR dept_sum_vu;Synonym Created.Synonym Created.
DROP SYNONYM d_sum;Synonym dropped.Synonym dropped.
DROP SYNONYM d_sum;Synonym dropped.Synonym dropped.