curso oracle tema-6
TRANSCRIPT
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 1/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 29
Tema 6. SQL: Lenguaje de Consulta Estructurado
1. SQL Plus
Aunque ya se ha citado a SQL como lenguaje de interacción con
Oracle, así como algunos conceptos sobre lenguajes de definición y
de manipulación de datos, es en esta sección donde se va a estudiar
con cierta profundidad los aspectos básicos de SQL.
Para ello, utilizaremos SQL*Plus, una herramienta de Oracle que
actúa como intérprete de SQL. Así pues, será en SQL*Plus donde se
introducirán nuestras sentencias SQL y donde se verán los resultados
de nuestras consultas. Concretamente en esta parte de SQL*Plus,
describiremos la utilización de la herramienta y estudiaremos con
detalle la sintaxis básica de SQL. Todo ello, ilustrado con bastantes
ejemplos, de forma que sea sencillo la asimilación de este lenguaje.
SQL*Plus proporciona una interfaz de línea de órdenes en un
entorno gráfico (es una aplicación Windows), permitiendo
Abrir y guardar scripts SQL
Realizar algunas operaciones de edición como copiar y pegar
texto, o buscar.
Personalizar el entorno
Conceptos básicos
A continuación se comentan los términos que se utilizan al hablar
de SQL, PL/SQL y SQL*Plus:
Orden o comando. Instrucción que se da a SQL*Plus o a la base
de datos Oracle
Bloque. Grupo de órdenes SQL y PL/SQL
Tabla. Unidad básica de almacenamiento de Oracle
Consulta. Orden SQL de recuperación de información de una o
más tablas (SELECT)
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 2/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 30
Resultado de una consulta. Datos recuperados por una consulta
Informe. Resultados de una consulta formateados con órdenes
de SQL*Plus
Utilización de SQL*P lus
En primer lugar, si nuestra base de datos no es local y está en un
servidor remoto, tendremos que cargar los controladores apropiados
para poder acceder de forma remota. A continuación bastará con
ejecutar SQL*Plus e identificarse como usuario.
Para comenzar a utilizar SQL*Plus, seleccionaremos Inicio-
Programas-Oracle – Application Development-SQL Plus. (El archivo
que se ejecuta es sqlplusw.exe que se encuentra en el directorio bin
dónde se haya instalado oracle)-
Una vez realizado esto, aparecerá un cuadro de diálogo como el de
la figura siguiente para conectarnos a la base de datos. En él
introduciremos nuestro nombre de usuario en el cuadro User name, la
contraseña en el cuadro Password, y el nombre de la base de datos
definido en SQL Net Easy Configuration en el cuadro Host String. A
continuación pulsaremos OK.
Figura 4. Cuadro de diálogo para la conexión a la base de datos.
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 3/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 31
Figura 5. Interpréte de comandos de sql plus. En esta ventana introduciremos nuestras sentencias SQL, que se
irán guardando en un buffer, que posteriormente podremos guardar
para luego poder abrirlo. A continuación, describimos las órdenesdisponibles en los menús del intérprete SQL*Plus.
Menú Archivo
Abrir Abre un archivo de órdenes guardado. Por omisión,tendrán extensión SQL
Guardar Crear guarda el contenido del buffer en un archivo deórdenes
Sustituir sustituye el contenido de un archivoexistente con el contenido actual del buffer
Agregar añade el contenido del buffer al final delarchivo que se especifique
GuardarComo
Guarda el contenido del buffer en un archivo deórdenes
Spool Spool File guarda los resultados de las consultas en un
archivo. Por omisión, estos archivos tienen extensiónLST.
Spool Off desactiva el envío de resultados a unarchivo.
Ejecutar Lista y ejecuta la sentencia SQL o el bloque PL/SQLguardado actualmente en el buffer
Cancelar Cancela el progreso de una operación
Salir Acepta los cambios pendientes y cierra la aplicaciónSQL*Plus
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 4/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 32
Menú Editar
Copiar Copia el texto seleccionado al portapapeles. El métodoabreviado es CONTROL+C
Pegar Pega el contenido del portapapeles. El método abreviado esCONTROL+V
Limpiar Borra el buffer y la pantalla de SQL*Plus. La orden SQLequivalente es CLEAR SCREEN, y el método abreviado esMAYUS+DEL
Editor Llamar Editor carga el contenido del buffer en unaventana del bloc de notas. Por omisión, la orden se guardaen un archivo AFIEDT.BUF que por supuesto se puedecambiar. Si desea que el editor por omisión sea otrodistinto tendrá que utilizar la orden Define Editor .
Definir Editor define el editor que se utilizará por omisiónpara ver el contenido del buffer
Menú Buscar
Buscar Busca un carácter, palabra o grupo de palabras en laventana de SQL*Plus. La búsqueda se realiza desde el
principio de la pantalla mostrada, y cuando llega alfinal, no se continúa buscando desde el principio. Elmétodo abreviado es MAYUS+F3
BuscarSiguiente
Busca la ocurrencia siguiente del elemento buscado
Menú Opciones
Este menú sólo tiene la orden Entorno, que sirve para configurar
una serie de propiedades de SQL*Plus.
El cuadro de diálogo contiene dos áreas: Opciones y Buffer de
pantalla.
El área Opciones proporciona una lista de parámetros que
puede configurar para establecer ciertos aspectos, como el
ancho predefinido para los números, la activación o
desactivación de los encabezados, la definición del número delíneas por página, ...
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 5/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 33
El área Buffer de pantalla permite configurar el número de
caracteres que se pueden mostrar en una línea (Buffer Width) y
el número de líneas por pantalla (Buffer Length).
Debido a que SQL es el lenguaje que utilizaremos para interactuar
con el SGBD, es importante unificar los conocimientos de SQL. Para
ello los próximos temas serán dedicados a la sintaxis SQL tanto como
DDL como DML.
2. Fundamentos SQL
Lenguaje de Definición de Datos (DDL): proporciona órdenes para
definir, modificar o eliminar los distintos objetos de la base de datos
(tablas, vistas, índices...).
Lenguaje de Manipulación de Datos (DML): proporciona órdenes
para insertar, suprimir y modificar datos de las tablas de la base de
datos.
3. Lenguaje de Definición de Datos
Se utilizan las sentencias CREATE, ALTER, DROP, crear, modificar
y borrar cualquier objeto de la base de datos.
A continuación se muestran una serie de ejemplos de uso de estas
sentencias.
Nota: Para poder facilitar el uso de estas sentencias, en el anexo 1
figura el manual de sql reference de oracle.
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 6/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 34
Ejemplo 1 (Creación de un TABLESPACE)
CREATE TABLESPACE user_1DATAFILE ‘D:/oracle/product/oradata/user_1.dbf’ SIZE 30M
DEFAULT STORAGE (INITIAL 10K NEXT 50
MINEXTENTS 1 MAXEXTENTS 999)
OFFLINE;
Ejemplo 2 (Modificar un TABLESPACE)
ALTER TABLESPACE user_1 ONLINE;
Ejemplo 3 (Creación de un USUARIO)
CREATE USER usuario1IDENTIFIED BY usuario1DEFAULT TABLESPACE user_1QUOTA 10M ON user_1TEMPORARY TABLESPACE TEMP;
CREATE USER usuario2IDENTIFIED BY usuario2;
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 7/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 35
Ejemplo 4 (Modificando un USUARIO)
ALTER USER usuario1
ACCOUNT LOCK;
Intentar conectar al sqlplus con el usuario1, y ver que ocurre.(Cuenta bloqueada y sin permisos de conexión)
Ejemplo 5 (Dando permisos al USUARIO)
GRANT CONNECT, RESOURCE, DBA TO usuario1;GRANT CONNECT TO usuario2;
Intentar conectar al sqlplus con el usuario1, y ver que ocurre. (Setiene permiso de conexión pero cuenta bloqueada)
Ejemplo 6 (Modificando un USUARIO)
ALTER USER usuario1ACCOUNT UNLOCK;
ALTER USER usuario1PASSWORD EXPIRE;
Intentar conectar al sqlplus con el usuario1. Se conecta pero solicitaintroducir nueva password.
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 8/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 36
Ejemplo 7 (Crear una TABLA)
CREATE TABLE ORACLE_PRUEBA(NOMBRE VARCHAR2(8) NOT NULL,
TELEFONO NUMBER(12)
);
Ejemplo 8 (Modificar una TABLA)
ALTER TABLE ORACLE_PRUEBAADD DIRECCION VARCHAR2(20);
ALTER TABLE ORACLE_PRUEBAMODIFY DIRECCION VARCHAR2(50) NOT NULL;
Ejemplo 9 (Consultando una tabla)
Nos conectamos con el usuario2
SELECT * FROM ORACLE_PRUEBA;
Ejemplo 10 (Creamos un sinónimo)
Con USUARIO SYSTEM
CREATE PUBLIC SYNONYM ORACLE FORUSUARIO1.ORACLE_PRUEBA;
Ejemplo 11 (Consultando una tabla)
Nos conectamos con el usuario2
SELECT * FROM ORACLE;
Ejemplo 12 (Dando permisos al USUARIO)
REVOKE CONNECT FROM usuario2;
Intentamos hacer connect usuario2/usuario2
Ejemplo 13 (Borrando un USUARIO)
DROP USER USUARIO2 CASCADE;
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 9/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 37
Ejemplo 14 (Creando una SECUENCIA)
Una secuencia sirve para generar automáticamente números
distintos. Se utilizan para generar valores para campos que se
utilizan como clave forzada (claves cuyo valor no interesa, sólo sirven
para identificar los registros de una tabla).
Es una rutina interna de Oracle la que realiza la función degenerar
un número distinto cada vez. Las secuencias se almacenan
independientemente de la tabla, por lo que la misma secuencia se
puede utilizar para diversas tablas.
Creación de secuencias, Sintaxis:
CREATE SEQUENCE secuencia
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
secuencia. Es el nombre que se le da al objeto de secuencia
INCREMENT BY. Indica cuánto se incrementa la secuencia cada
vez que se usa. Por defecto se incrementa de uno en uno
START WITH. Indica el valor inicial de la secuencia (por defecto
1)
MAXVALUE. Máximo valor que puede tomar la secuencia.
MINVALUE. Mínimo valor que puede tomar la secuencia.
CYCLE. Hace que la secuencia vuelva a empezar si se ha
llegado al máximo valor.
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 10/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 38
CREATE SEQUENCE incremento_id
INCREMENT BY 1
START WITH 100;
Ejemplo 15 (Utilizar una SECUENCIA)
INSERT INTO PROVEEDORES VALUES (
CAMPO1
INCREMENTO_ID.NEXTVAL,
CAMPOS);
Ejemplo 16 (SECUENCIA CICLICA)
CREATE SEQUENCE incremento_ciclico
INCREMENT BY 1
START WITH 10
MAXVALUE 30
CYCLE;
Cuando llegue al valor máximo empezará por el valor inicial.
Ejemplo 17 (SECUENCIA DECRECIENTE)
CREATE SEQUENCE secuencia_decremental
INCREMENT BY -1
START WITH 100 MAXVALUE 100
Para crear una secuencia decremental el parámetro INCREMENT
BY tendrá valor -1 y el parámetro START WITH coincidirá con el
parámetro MAXVALUE.
La vista del diccionario de datos USER_SEQUENCES muestra la
lista de secuencias actuales. La columna LAST_NUMBER muestra cual
será el siguiente número de secuencia disponible uso de la secuencia.
Los métodos NEXTVAL y CURRVAL se utilizan para obtener el
siguiente número y el valor actual de la secuencia respectivamente.
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 11/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 39
Ejemplo 18 (Valores SECUENCIA CURRVAL)
SELECT incremento_id.NEXTVAL FROM DUAL;
SELECT incremento_id.CURRVAL FROM DUAL;
Eso muestra en pantalla el siguiente valor de la secuencia.
Realmente NEXTVAL incrementa la secuencia y devuelve el valor
actual. CURRVAL devuelve el valor de la secuencia, pero sin
incrementar la misma.
Ambas funciones pueden ser utilizadas en:
Una consulta SELECT que no lleve DISTINCT, ni grupos, ni sea
parte de una vista, ni sea subconsulta de otro SELECT, UPDATE
o DELETE.
Una subconsulta SELECT en una instrucción INSERT
La cláusula VALUES de la instrucción INSERT
La cláusula SET de la instrucción UPDATE
No se puede utilizar (y siempre hay tentaciones para ello) como
valor para la cláusula DEFAULT de un campo de tabla.
Su uso más habitual es como apoyo al comando INSERT:
INSERT INTO tabla(num, piso)
VALUES(secuencia_id.NEXTVAL, 3);
Ejemplo 19 (Modificar SECUENCIA)
ALTER SEQUENCE secuencia
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n|NOMAXVALUE}]
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 12/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 40
[{MINVALUE n|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
Se pueden modificar las secuencias, pero la modificación sólopuede afectar a los futuros valores de la secuencia, no a los ya
utilizados.
Ejemplo 20 (Borrar SECUENCIA)
DROP SEQUENCE incremento_id;
4. Tipos de datos
Los tipos de datos principales de ORACLE son los siguientes:
VARCHAR2(n): dato de tipo carácter, de n caracteres de
longitud.
NUMBER: dato de tipo numérico de un máximo de 40 dígitos,
además del signo y el punto decimal. Se puede utilizar notación
científica (1.273E2 es igual a 127.3).
NUMBER(n,d): dato de tipo numérico con n dígitos en total
como máximo y d dígitos decimales como mucho. NUMBER(4,2)
tiene como máximo valor 99.99.
DATE: datos de tipo fecha.
Ejemplo 21 (Creación de Tabla con restricciones)
CREATE TABLE PROVEEDORES(EMPRESA VARCHAR2(8),CODIGO NUMBER(6),CIF VARCHAR2(8) UNIQUE,DIRECCION VARCHAR2(50),TELEFONO NUMBER(12) NOT NULLCHECK (LENGTH(TO_CHAR(TELEFONO))>=9),
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 13/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 41
FECHA_ALTA DATE DEFAULT SYSDATE); ALTER TABLE PROVEEDORESADD (CONSTRAINT PK_PROV PRIMARY KEY (EMPRESA, CODIGO)
USING INDEX);
Ejemplo 22 (Añadir Comentarios a Columnas)
COMMENT ON COLUMN PROVEEDORES.EMPRESA IS 'Código de la
Empresa'
5. Funciones de Tipos de datos
Cadenas de caracteres (VARCHAR(n)):
Se delimitan utilizando comillas simples: ‘Hola’, ‘Una cadena’.
Además de los operadores de comparación e igualdad (<, >, =, !=,
...) otras funciones útiles para trabajar con cadenas son:
cad || cad : concatena dos cadenas.
LENGTH(cad): devuelve la longitud de la cadena.
LOWER(cad): convierte todas las letras de la cadena a
minúsculas.
UPPER(cad): ídem a mayúsculas.
SUBSTR(cad, comienzo [, cuenta]): extrae la subcadena de cad
empezando en la posición comienzo y con longitud (lasubcadena) cuenta. El primer caracter de la cadena tiene como
índice el número 1.
Números (NUMBER):
Además de las operaciones típicas con valores numéricos (+, -, *,
/), otras funciones útiles son:
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 14/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 42
ABS(num): devuelve el valor absoluto.
SQRT(num): devuelve la raíz cuadrada.
POWER(b,e): devuelve la potencia be .
GREATEST(num1, num2, ...): devuelve el mayor valor de la
lista de valores.
LEAST(num1, num2, ...): devuelve el menor valor de la lista.
Existen otras funciones para grupos de valores (suma, media,
máximo, ...) que se verán en apartados posteriores.
Fechas (DATE):
El formato de un valor de tipo DATE es: ‘dia-mes-año’, donde
tanto el día como el año tiene formato numérico y el mes se indica
con las tres primeras letras del nombre del mes en el idioma
soportado por el servidor ORACLE. Ejemplos: ‘1-JAN-96’, ‘28-jul-74’.
Además de esta información, un valor de tipo fecha almacena
también la hora en formato hh:mm:ss. Las fechas se pueden
comparar con los operadores típicos de comparación (<, >, !=, =,
...). La función SYSDATE devuelve la fecha actual (fecha y hora). Con
las fechas es posible realizar operaciones aritméticas como sumas y
restas de fechas, teniendo en cuenta que a una fecha se le suman
días y que la diferencia entre dos fechas se devuelve también en días.
Por ejemplo SYSDATE + 1 devuelve la fecha de mañana.
Oracle permite tanto la conversión de tipos implícita como la
explícita. La conversión de tipos implícita significa que cuando Oracle
encuentra en un lugar determinado (por ejemplo en una expresión)
un dato de un tipo diferente al esperado, entonces aplica una serie de
reglas para intentar convertir ese dato al tipo esperado. Por ejemplo,
si un atributo de una tabla determinada es de tipo NUMBER y se
intenta introducir el valor de tipo caracter '1221', entoncesautomáticamente se convierte en su valor numérico equivalente sin
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 15/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 43
producirse ningún error.
La conversión de tipos explícita se realiza básicamente con las
siguientes funciones:
Conversión número-cadena: TO_CHAR(número [, formato]).
Conversión cadena-número: TO_NUMBER(cadena [,formato]).
Conversión fecha-cadena: TO_CHAR(fecha [, formato]).
Conversión cadena-fecha: TO_DATE(cadena [, formato]).
La opción formato permite especificar un modelo de formato o
máscara consistente en una cadena de caracteres que describe el
formato en el que se quiere obtener el resultado o en el que se da el
parámetro. Algunos ejemplos de la utilización de estas funciones son:
TO_CHAR('25-dec-98', 'YY') devuelve '98'.
TO_CHAR(SYSDATE, 'dd-mon-yyyy') devuelve '25-dec-1998'.
TO_CHAR(123.34,'09999.999') devuelve '00123.340'
6. Lenguaje de Manipulación de Datos
Se utilizan las sentencias SELECT, INSERT, UPDATE, DELETE,
consulta, inserción, actualización y borrado de los datos de la tabla
dónde se aplique.
Inserción, modificación y eliminación de DATOS
Una vez que se ha creado de forma conveniente las tablas, el
siguiente paso consiste en insertar datos en ellas, es decir, añadir
registros. Durante la vida de la base de datos será necesario,
además, borrar determinados registros o modificar los valores que
contienen. Los comandos de SQL que se van a estudiar en esteapartado son INSERT, UPDATE y DELETE.
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 16/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 44
Inserción de Datos
El comando INSERT de SQL permite introducir registros en una
tabla de la base de datos. La sintaxis del comando es la siguiente:
INSERT INTO {nombre_tabla}
[(nombre_columna [, nombre_columna] ...)]
{VALUES (valor [, valor] ...)
};
Con el comando INSERT se añade un registro a la tabla. Si se da
una lista de columnas, los valores deben emparejar uno a uno con
cada una de estas columnas. Cualquier columna que no esté en la
lista recibirá el valor NULL. Si no se da esta lista de columnas, se
deberán dar valores para todos los atributos de la tabla y en el orden
en que se definieron con el comando CREATE TABLE.
Si se elige la opción de subconsulta, se introducirán en la tabla los
registros resultantes de la subconsulta expresada como un comandoSELECT que será estudiado posteriormente.
Ejemplo 23 (Inserción de datos)
INSERT INTO ORACLE_PRUEBA(NOMBRE, DIRECCION)
VALUES (‘JUAN’,’CALLE CASTILLA’);
INSERT INTO PROVEEDORESVALUES (‘E1’,12,’354567’,’AVDA. PIO X’,965456345,SYSDATE);
Modificación de Datos
Para la modificación de registros dentro de una tabla se utiliza el
comando UPDATE. La sintaxis del comando es la siguiente:
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 17/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 45
UPDATE {nombre_tabla}
SET {nombre_col = expresión [, nombre_col = expresión, ...] }|
nombre_col [, nombre_col, ...] = (sub_consulta)
[WHERE condición];
Este comando modifica los valores de los atributos especificados
en SET para aquellos registros que verifican condición (si existe). La
subconsulta puede seleccionar de la tabla que se está modificando (o
de otras tablas o vistas), aunque debe devolver un único regsitro. Si
no se utiliza la cláusula WHERE, se modificarán todos los registros de
la tabla. Si se utiliza la cláusula WHERE sólo se modificarán aquellos
registros que verifiquen la condición. Hay que tener en cuenta que las
expresiones se van evaluando según se va ejecutando el comando
UPDATE.
Ejemplo 24 (Modificando datos)
UPDATE ORACLE_PRUEBA
SET DIRECCION = ‘CALLE MADRID 12’ WHERE NOMBRE=’JUAN’;
UPDATE PROVEEDORESSET FECHA_ALTA= SYSDATE;
Eliminación de Datos
Por último, se va a estudiar el comando que permite eliminar
registros concretos de una tabla, el comando DELETE. Su sintaxis es
la siguiente:
DELETE FROM {nombre_tabla}[WHERE condición];
Si se omite la cláusula WHERE, este comando borrará todos los
registros de la tabla, aunque ésta seguirá existiendo, pero sin ningún
registro. Si se introduce la cláusula WHERE, sólo se borrarán aquellos
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 18/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 46
registros que verifiquen la condición.
Ejemplo 25 (Borrando datos)
DELETE FROM ORACLE_PRUEBAWHERE NOMBRE=’JUAN’;
DELETE FROM PROVEEDORES;
Consulta de datos
La sintaxis fundamental del comando SELECT es la siguiente:
SELECT [ALL, DISTINCT]{[*|tabla.*],expresión[,expresión,...]}
FROM tabla [, tabla, ...]
[WHERE condición]
[ORDER BY {expresión | posición} [ASC | DESC]
[expresión | posición} [ASC | DESC] , ...]]
[GROUP BY expresión [, expresión, ...]][HAVING condición];
Las distintas opciones tienen el siguiente significado:
ALL: significa que se devolverán todos los registros que
satisfagan las distintas condiciones, esta opción es la que se
toma por defecto.
DISTINCT: significa que se devolverán valores o conjuntos de
valores únicos, es decir, no repetidos.
*: indica que se realizará la proyección por todos los atributos
de la/s tabla/s o vista/s indicadas en la cláusula FROM. Si se
quiere que sólo se proyecte por los atributos de una tabla se
utiliza tabla.*.
expresión: se puede sustituir por:
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 19/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 47
• nombre de un atributo
• expresión conteniendo atributos y/o funciones.
Si en el listado de la consulta debe aparecer un nombre distinto aldel atributo o al de la expresión se puede utilizar un ALIAS, que
consiste en una cadena de caracteres encerrada entre comillas dobles
y situada junto a la expresión que sustituirá en el listado de la
consulta.
Otro tipo de alias es el que se utiliza para las tablas en la cláusula
FROM. Estos alias se ponen junto al nombre de la tabla y se pueden
utilizar en cualquier punto del comando SELECT sustituyendo así al
nombre de la tabla.
En la cláusula ORDER BY, posición sirve para referenciar un
atributo que ocupa esa posición dentro de la cláusula SELECT.
Desde el punto de vista de las operaciones abstractas de consulta
del modelo relacional, la cláusula SELECT corresponde a la operación
de proyección; la cláusula FROM corresponde a la operación de
producto cartesiano (nos permitirá hacer el JOIN); y la cláusula
WHERE corresponde a la condición de la operación de selección.
Una típica consulta:
SELECT a1, a2, ..., an
FROM r1, r2
WHERE p;
El resultado de un comando SELECT es siempre una tabla, lo que
permite construir consultas complejas anidando varios SELECT. A
continuación se explica el significado del resto de cláusulas del
comando SELECT viendo sus típicos usos.
Obtener toda la información contenida en una tabla:
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 20/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 48
SELECT * FROM nombre_tabla ;
El asterisco indica que se quieren seleccionar todas la columnas de
la tabla, y al no especificarse ninguna condición, todos los registrosson seleccionados.
Realizar la práctica 1, descrita en el libro “Ejercicios_curso_oracle_I ”
Otros usos del comando SELECT
En este apartado se estudiarán algunos aspectos algo másavanzados del comando SELECT, pudiendo de esta forma generar
consultas algo más complejas.
Existen una serie de funciones especiales, que no fueron tratadas
en el apartado anterior, y que van a permitir trabajar con información
relativa a no sólo una única fila, sino a varias. Estas funciones se
conocen con el nombre de funciones agregadas o funciones de grupos
de valores. Un dato a tener en cuenta es que estas funciones
agregadas ignoran el valor NULL y calculan resultados a pesar de su
existencia.
A continuación se exponen algunas de las principales funciones de
grupo:
COUNT: devuelve el número de filas. Su formato es el
siguiente:
COUNT ({* | [DISTINCT | ALL] columna})
COUNT (*): cuenta todas las filas de la tabla, aunque tengan
valores NULL (incluso en todos los atributos).
COUNT (DISTINCT columna): devuelve el número de valores
distintos para la columna, no incluyendo los nulos.
COUNT (ALL columna): devuelve el número de valores de la
columna, no incluyendo los NULL.
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 21/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 49
SUM: devuelve la suma de un conjunto de valores. El formato
es:
SUM ([DISTINCT] columna | expresión)
Devuelve la suma de los valores de la columna o de los devueltos
por expresión. La columna o expresión, evidentemente, debe ser
numérica.
AVG: calcula la media aritmética de un conjunto de valores. Su
formato es:
AVG ([DISTINCT] columna | expresión)
Devuelve la media aritmética de los valores de la columna o de los
devueltos por la expresión. La columna o expresión debe ser
numérica.
MAX: calcula el máximo de un conjunto de valores. Su formato
es:
MAX ([DISTINCT] columna | expresión)
Devuelve el valor máximo de la columna o la expresión.
MIN: calcula el mínimo de un conjunto de valores. Su formato
es:
MIN ([DISTINCT] columna | expresión)
Devuelve el valor mínimo de la columna o la expresión.
Consulta de información agrupada
SQL permite obtener una tabla agrupada que contenga una fila
con información resumen para cada grupo. Para obtener esta
información agrupada se hace uso de la cláusula GROUP BY del
comando SELECT. La cláusula GROUP BY tiene la siguiente sintaxis:
GROUP BY expresión [, expresión, ...]
dónde expresión puede ser un atributo o una expresión
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 22/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 50
conteniendo atributos.
De forma análoga a la cláusula WHERE, se pueden establecer
restricciones sobre qué grupos aparecerán en el resultado de la
consulta. Esto se realiza mediante la cláusula HAVING del comandoSELECT.
Si en una misma consulta aparecen las cláusulas WHERE y
HAVING, primero se aplica la condición de la cláusula WHERE. Las
filas que la satisfacen son colocadas en grupos por la cláusula GROUP
BY, después se aplica la cláusula HAVING a cada grupo, y los grupos
que satisfacen la condición de la cláusula HAVING son utilizados por
la cláusula SELECT para generar filas del resultado de la consulta.
Un detalle importante a tener en cuenta es que las expresiones
que se pongan en la cláusula SELECT serán, o valores constantes, o
funciones agregadas, o bien alguna expresión incluida en la cláusula
GROUP BY. Esto es debido a que las funciones como COUNT, SUM,
AVG, etc., devuelven valores sobre grupos de filas, no sobre filas
individuales. En la cláusula HAVING sólo se podrán utilizar funcionesagregadas.
Subconsultas
Una subconsulta es una consulta dentro de otra, es decir, un
SELECT anidado en la cláusula WHERE o HAVING de otro SELECT.
Esta anidación permite realizar consultas complejas, que no sería
posible, o poco eficiente realizar haciendo consultas simples a base
de JOINS.
El formato de la subconsulta es el siguiente:
SELECT columnas
FROM tabla
WHERE {columna | expresión} operador
( SELECT columna | expresión
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 23/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 51
FROM ...) ;
El operador a utilizar dependerá del número de valores que se van
a obtener en la subconsulta.
Las subconsultas pueden aparecer en cualquier lugar en el que se
pueda colocar la cláusula WHERE, es decir en los comandos SELECT,
INSERT y UPDATE.
Cuando el resultado de la subconsulta es un conjunto de valores
NO se puede utilizar el operador de igualdad, sino el de inclusión.
Operaciones entre conjuntos
A los resultados de las consultas (que se implementan como tablas
temporales) se les pueden aplicar también los operadores
tradicionales de conjuntos (unión, intersección y diferencia),
obteniendo nuevas tablas. Los operadores de conjuntos son:
UNION:
Realiza la operación de unión eliminando las filas duplicadas. Las
dos tablas que se unen deben tener el mismo número de atributos
(aunque no es necesario que tengan los mismos nombres) y éstos
deben tener el mismo tipo de datos.
Ejemplo:
SQL>(SELECT * FROM mecanicos
WHERE nombre = 'PEPE')
UNION
(SELECT * FROM mecanicos
WHERE puesto = 'AMORTIGUACION');
INTERSECT:
Realiza la operación de intersección de conjuntos. Las condiciones
son las descritas anteriormente.
5/10/2018 Curso Oracle Tema-6 - slidepdf.com
http://slidepdf.com/reader/full/curso-oracle-tema-6 24/24
Manual del Curso Oracle
© Cristóbal Núñez Sánchez 52
MINUS:
Realiza la operación resta de conjuntos. Es decir, A -B son las filas
que están en A y no están en B. La operación de resta no es
conmutativa, como muestra el siguiente ejemplo.
Realizar la práctica 2, descrita en el libro “Ejercicios_curso_oracle_I ”
Tema 7. El Diccionario de Datos de ORACLE
El diccionario de datos (DD) de ORACLE es uno de los
componentes más importantes del RDBMS ORACLE. Contiene toda la
información sobre las estructuras y objetos de la base de datos así
como de las tablas, columnas, usuarios, ficheros de datos, etc. Los
datos guardados en el diccionario de datos son también llamados
metadatos. Aunque el DD es usualmente del dominio del
administrador de base de datos (DBA), es una valiosa fuente de
información para los usuarios y desarrolladores. El DD consta dos
niveles: Nivel interno: contiene todas las tablas base que son utilizadas
por el SGBD y que no son normalmente accesibles para el
usuario,
Nivel externo: proporciona numerosas vistas de estas tablas
base para acceder a la información sobre objetos y estructuras
a diferentes niveles con detalle.
1. Tablas del Diccionario de Datos
La instalación de una base de datos ORACLE siempre incluye la
creación de usuarios estándar ORACLE:
SYS: este es el propietario de todas las tablas del DD y de las
vistas. Esta utilidad tiene el enorme privilegio de manejar
objetos y estructuras de una base de datos ORACLE así comocrear nuevos usuarios.