sql sobre oracle, Apuntes de Informática

¡Descarga sql sobre oracle y más Apuntes en PDF de Informática solo en Docsity!

Laboratorio de Bases de Datos

Introducci´on a SQL sobre Oracle

Luis A. Gonz´alez Ares

[email protected]

Contenido

• Lenguaje SQL sobre Oracle.

− Descripci´on de las caracter´ısticas y elementos fundamentales del lenguaje. − Ejercicios sobre la sentencia SELECT. − Otras sentencias DML. − Sentencias DDL. − Vistas. − Indices. − Seguridad. − Transacciones. − Cat´alogo.

Nota

El presente material es un resumen de lo impartido en las clases de la Facultad de Inform´atica, que se entrega como documento de apoyo.

Esta obra est´a bajo una licencia Reconocimiento-NoComercial-SinObraDerivada de Creative Commons (Attribution-NonCommercial-NoDerivatives).

SQL*Plus de Oracle

Los fabricantes amplian las caracter´ısticas del est´andar pero dejan elementos sin implementar.

SQL*Plus: Interface de Oracle que permite ejecutar sentencias SQL y comandos propios.

Entrar en SQL*Plus (servidor oracle o xurxo):

$ sqlplus / [@filename]

Comandos SQL*Plus. Uno por fila:

SQL> DESC[RIBE] emp

Nombre Nulo? Tipo

---

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

Sentencias SQL: Multifila y acaban en ;

SQL> SELECT * FROM emp;

Salir de SQL*Plus:

SQL> EXIT

SQL*Plus de Oracle (cont.)

Crear el fichero de configuraci´on login.sql y ubicarlo en el directorio de llamada a SQL*Plus. Por ejemplo:

$ cat login.sql def_editor=vi set pages 50 set feed 1 column empno format 9990 column mgr format 9990 column sal format 99, column comm format 99, column deptno format 90

SQLPlus usa un buffer para almacenar la ´ultima sentencia SQL ejecutada (no afecta a los comandos SQLPlus). Incorpora un editor de l´ıneas elemental para realizar cambios m´ınimos en esa sentencia SQL, como visualizarla (LIST), editarla (ED), etc.

Algunas ´ordenes muy habituales son:

L[IST] [n [m]] Visualiza l´ıneas del buffer ED[IT] fichero[.sql] Edita un fichero [.sql] R[UN] Ejecuta lo almacenado en el buffer / Ejecuta lo almacenado en el buffer SAV[E] fichero[.sql] Almacena el buffer en un fichero GET fichero[.sql] Pasa al buffer un fichero

Sentencias SQL

• Lenguaje de definici´on de datos: Permite crear, manipular y eliminar estructuras CREATE TABLE Crea una tabla. DROP TABLE Elimina una tabla. CREATE VIEW Crea una vista o tabla virtual. DROP VIEW Elimina una vista. ALTER TABLE Modifica la estructura de una tabla. CREATE INDEX Crea un ´ındice para una tabla. ...
• Lenguaje de manipulaci´on de datos: Permite consultar, modificar, introducir y eliminar datos de las estructuras de una BD relacional. SELECT Obtiene datos de la BD. INSERT Introduce datos. UPDATE Actualiza datos. DELETE Borra datos.
• (Oracle) Lenguaje de control de datos: Sentencias que realizan otras funciones, por ejemplo sobre proceso transaccional, concurrencia, etc. COMMIT Confirma una transacci´on. ROLLBACK Anula una transacci´on. GRANT Otorga permisos a usuarios en objetos de la BD. REVOKE Elimina permisos a usuarios en objetos de la BD. ...

Por ejemplo:

DROP TABLE mitabla; SELECT empno, ename FROM emp;

Tipos de Datos

Cada columna de una tabla tiene un tipo de dato asignado, que determina los valores posibles y las operaciones permitidas sobre esos valores.

Un tipo de dato puede asignarse a un dominio, con lo que se asignar´a a cada columna de ese dominio.

• Tipos de datos num´ericos − Oracle NUMERIC(m[,n]) o NUMBER(m[,n]) Ej.: NUMBER(3) NUMBER(5,2)

− SQL

INTEGER o INT, SMALLINT FLOAT, REAL, DOUBLE PRECISION DECIMAL(m,n) o DEC(m,n) o NUMERIC(m,n)

• Tipos de datos car´acter o alfanum´ericos − Oracle CHAR[(n)] Ej.: CHAR(20) CHAR Ej.: ’micasa’ ’A’ VARCHAR2[(n)] LONG

− SQL

CHAR(n) o CHARACTER(n)

VARCHAR(n), CHARVARYING(n), CHARACTER VARYING(n)

• Tipos de datos temporales − Oracle DATE Ej.: ’06-JAN-2002’ Ej.: ’06-JAN-2002 13:21’

− SQL

DATE YEAR, MONTH, DAY

TIME HOUR, MINUTE, SECOND

TIMESTAMP

INTERVAL

Tablas de los ejemplos(cont.)

SQL> SELECT * FROM emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---

7369 SMITH CLERK 7902 17/12/80 800 20 7499 ALLEN SALESMAN 7698 20/02/81 1,600 300 30 7521 WARD SALESMAN 7698 22/02/81 1,250 500 30 7566 JONES MANAGER 7839 02/04/81 2,975 20 7654 MARTIN SALESMAN 7698 28/09/81 1,250 1,400 30 7698 BLAKE MANAGER 7839 01/05/81 2,850 30 7782 CLARK MANAGER 7839 09/06/81 2,450 10 7788 SCOTT ANALYST 7566 09/12/82 3,000 20 7839 KING PRESIDENT 17/11/81 5,000 10 7844 TURNER SALESMAN 7698 08/09/81 1,500 0 30 7876 ADAMS CLERK 7788 12/01/83 1,100 20 7900 JAMES CLERK 7698 03/12/81 950 30 7902 FORD ANALYST 7566 03/12/81 3,000 20 7934 MILLER CLERK 7782 23/01/82 1,300 10

SQL> SELECT * FROM dept

DEPTNO DNAME LOC

---

10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Valores nulos

El valor nulo NULL representa la ausencia de informaci´on, o bien por desconocimiento del dato, o bien porque no procede.

Debe diferenciarse de cualquier otro valor, entre ellos del valor 0 si se trata de un dato num´erico, y de la cadena de caracteres vac´ıa, si es un dato de tipo car´acter.

Una columna de una tabla podr´a admitir valores nulos (NULL) o no (NOT NULL). Por defecto admite nulos.

Una forma de indicar la no admisi´on de valores nulos:

CREATE TABLE emp( empno VARCHAR2(4) NOT NULL, ename VARCHAR2(15) NOT NULL, job VARCHAR2(9), mgr VARCHAR2(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno VARCHAR2(2) )

Incluyendo un valor nulo en una fila:

INSERT INTO emp VALUES(’1245’, ’Jos´e’, ’Analista’, NULL, ’12-Jan-1997’, 34000, 300.05, ’12’)

INSERT INTO emp VALUES(’1245’, ’Jos´e’, ’Analista’, NULL, TO_DATE(’12/01/1997’, ’dd/mm/yyyy’), 34000, 300.05, ’12’)

La sentencia SELECT

La sentencia SELECT permite seleccionar u obtener datos de una o de varias tablas.

Parte de una o de varias tablas de la BD y el resultado es otra tabla, denominada a veces tabla resultado, pero que no formar´a parte de la BD – no queda almacenada en la BD–.

SELECT [DISTINCT | ALL] {* | [, ] ...} FROM [, , ...] [WHERE <condicion_where>] [GROUP BY <group_expr1>[, <group_expr2>, ...] [HAVING <condicion_having>] [ORDER BY <expr_orderby1 [ASC | DESC]>[, ...]]

La sentencia SELECT tiene varias cl´ausulas o partes diferentes con una funci´on espec´ıfica.

La sentencia SELECT b´asica est´a formada por las cl´asulas SELECT, FROM, WHERE y ORDER BY.

El orden de ejecuci´on de las cl´ausulas y la funci´on de cada una es:

1. FROM (obligatoria) Determina la tabla o tablas de la que se seleccionar´an los datos.
2. WHERE (optativa) Indica un predicado que expresa la condici´on que debe cumplir cada fila que interviene en la consulta. As´ı la consulta se restringe a las filas que cumplen la condici´on.
3. SELECT (obligatoria) Incluye los datos que se solicitan en la consulta, normalmente una o varias expresiones. Alternativamente un * indica todas las columnas de las tablas involucradas. Si hubiese filas repetidas, por defecto aparecen, pero no lo hacen si se incluye DISTINCT.
4. ORDER BY (optativa) Permite determinar el criterio de ordenaci´on de las filas de la tabla resultado. Sin ella obtendremos las mismas filas, pero puede que en ´ordenes distintos, seg´un la estrategia seguida por el SGBD para extraer los datos.

Predicados elementales

Un predicado expresa una condici´on y como en BD relacionales existe una l´ogica de tres valores, verdadero, falso y nulo, la evaluaci´on de una condici´on puede ser TRUE, FALSE o NULL.

Un predicado puede aparecer en una cl´ausula WHERE evaluando la condici´on de cada fila de las tablas involucradas, de forma que s´olo las filas que cumplen la condici´on permanecen involucradas en la consulta, ignorando las restantes.

Los predicados m´as elementales son los de comparaci´on, que comparan dos expresiones seg´un un operador de comparaci´on, que puede ser: < <= = != <> >= >

Ejemplos iniciales

1.– Obtener todos los datos de la tabla de empleados:

SELECT * FROM emp

2.– Obtener los c´odigos y nombres de los empleados que trabajan en el departamento 10:

SELECT empno, ename FROM emp WHERE deptno = 10

EMPNO ENAME

7782 CLARK

7839 KING

7934 MILLER

Ejemplos iniciales (cont.)

6.– Obtener la comisi´on, departamento y nombre de los empleados cuyo salario sea infe- rior a 1.900, orden´andolos por departamento en orden creciente, y por comisi´on en orden decreciente dentro de cada departamento:

SELECT comm AS "COMISION", deptno AS DEPARTAMENTO, ename NOMBRE FROM emp WHERE sal < 1900 ORDER BY deptno, comm DESC

COMISION DEPARTAMENTO NOMBRE

10 MILLER

20 SMITH

20 ADAMS

30 JAMES

1400 30 MARTIN

500 30 WARD

300 30 ALLEN

0 30 TURNER

Elementos de inter´es:

Diversos criterios jerarquizados de ordenaci´on. Asignaci´on de nombres a las columnas del resultado.

Ejemplos iniciales (cont.)

7.– Hallar todas las combinaciones diferentes de valores de puesto de trabajo (JOB) y a˜no de contrataci´on en el departamento 30:

SELECT job, TO_CHAR(hiredate,’yyyy’) CONTRATADO FROM emp WHERE deptno = 30

JOB CONT

---

SALESMAN 1981 SALESMAN 1981 SALESMAN 1981 MANAGER 1981 SALESMAN 1981 CLERK 1981

SELECT DISTINC job, TO_CHAR(hiredate,’yyyy’) CONTRATADO FROM emp WHERE deptno = 30

JOB CONT

---

MANAGER 1981 SALESMAN 1981 CLERK 1981 El DISTINCT afecta a toda la fila.

Predicados

Los predicados expresan condiciones.

Si aparecen en una cl´ausula WHERE indican una condici´on que las filas de las tablas involucradas deben cumplir.

Pueden aparecer tambi´en en una cl´ausula HAVING, como veremos posteriormente.

Pueden ser simples, si incluyen una ´unica condici´on, o compuestos si constan de varias, o sea, si combinan varios predicados simples, unidos por los operadores AND, OR y NOT.

Predicados simples

Inicialmente veremos los predicados simples en su formato m´as elemental. Son los siguientes:

• De comparaci´on. Comparan dos expresiones seg´un un operador de comparaci´on, que puede ser: < <= = != <> >= > || SELECT * FROM emp WHERE sal <= 1900
• De valor nulo. Los predicados de comparaci´on no sirven para determinar los valores nulos. Por ejemplo, no es v´alido COMM = NULL porque ser´ıa discernir si un valor que puede ser desconocido es igual a otro tambi´en desconocido. Se requiere un predicado especial, con formato: IS [NOT] NULL SELECT * SELECT * FROM emp FROM emp WHERE comm IS NULL WHERE comm IS NOT NULL

Predicados simples (cont.)

• De rango (BETWEEN). Compara si los valores de una expresi´on est´an o no entre los de otras dos. Formato: [NOT] BETWEEN AND Expresividad: e1 BETWEEN e2 AND e3 ≡ (e1 >= e2) AND (e1 <= e3) SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000
• De pertenencia a un conjunto (IN). Comprueba si el valor de una expresi´on coincide con alguno de los incluidos en una lista de valores. Formato: expr [NOT] IN (expr1[, expr2, ...]) Expresividad: e IN (a, b) ≡ (e = a) OR (e = b) SELECT * SELECT ename FROM emp FROM emp WHERE deptno IN (10, 30) WHERE job IN (’CLERK’,’SALESMAN’)
• De correspondencia con un patr´on o modelo (LIKE). Comprueba si el valor de una expresi´on alfanum´erica se corresponde con un modelo. El modelo puede incluir dos caracteres que act´uan como comodines: _ Indica un ´unico car´acter, incluido el blanco. % Indica una cadena de caracteres de cualquier longitud, incluida la cadena vac´ıa. Formato: expr_cart [NOT] LIKE modelo SELECT * SELECT * FROM emp FROM emp WHERE ename LIKE ’%NE%’ WHERE ename LIKE ’_____’