PDF de programación - Elementos de Oracle

Universidade da Coruña

Departamento de Computación

Elementos de Oracle

Luis A. González Ares

[email protected]

HACLUCEElementos de Oracle – Planteamiento

Objetivos:
• Mostrar algunos elementos destacados de Oracle.

Contenido:
• Secuencias.
• Papelera de reciclaje.
• Funciones de interés.
• Variables de sustitución.
• Sinónimos.
• Índices.
• Tratamiento de datos temporales.
• Tratamiento de valores nulos.
• Consulta retrospectiva - Flashback query
• Columnas derivadas. Columnas virtuales.
• Índices invisibles.
• Tablas de solo lectura.
• Disparadores.

Elementos de Oracle – Luis Glez. Ares – [email protected]

1

Oracle – Elementos

Secuencias

Cada tabla en un esquema relacional debe tener una clave primaria, esto es, que garantice
que dos filas diferentes tienen valores diferentes en ella y que no toma valores nulos.

Normalmente las claves primarias toman valores numéricos, lo que redunda en ventajas en el
rendimiento. Que esos valores sean o no significativos es una decisión de diseño, aunque si
aportan alguna semántica siempre será beneficioso.

En situaciones en las que necesitamos garantizar que una columna toma valores diferentes
sin importarnos dichos valores, como el caso de las claves subrogadas o sustitutas, Oracle
posee el elemento secuencia, que asigna de forma automática valores a una columna.

Una secuencia se crea con CREATE SEQUENCE, que de forma abreviada es:

CREATE SEQUENCE <nombre_secuencia>
INCREMENT BY

<incremento>

START WITH <inicio>

Se accede a ella mediante:

<nombre_secuencia>.NEXTVAL

Incrementa el valor de la secuencia y
devuelve el nuevo resultado.

<nombre_secuencia>.CURRVAL

Devuelve el valor actual de la secuencia, una vez
que ya se ha incrementado su valor con NEXTVAL.

Se modifica con ALTER SEQUENCE. Por ejemplo el incremento se modifica con:

ALTER SEQUENCE <nombre_secuencia> INCREMENT BY <nuevo_incremento>

Elementos de Oracle – Luis Glez. Ares – [email protected]

2

Oracle – Elementos

Secuencias (cont.)

Ejemplo.- Crear una secuencia que comience en 80 y se incremente de dos en dos.
Usarla luego para introducir (80, Marketing) en dept9(deptno, dname):

CREATE SEQUENCE sqemp1 INCREMENT BY 2

START WITH 80;

INSERT INTO dept9 (deptno, dname) VALUES (sqemp1.NEXTVAL,’Marketing’);

Una misma secuencia puede usarse para automatizar la asignación de valores de varias co-
lumnas de una o más tablas.

Ejemplo.- Usar la secuencia anterior para crear un nuevo empleado (82,’GUTIERREZ’):

INSERT INTO emp9 (empno, ename) VALUES (sqemp1.NEXTVAL,’GUTIERREZ’);

La referencia de las secuencias creadas por el usuario se encuentra en la vista del catálogo
USER_SEQUENCES.

Normalmente no se usan ni NEXTVAL ni CURRVAL en una consulta.
Además presentan limitaciones para su uso en una subconsulta, como columnas de una vista,
con DISTINCT, ORDER BY, GROUP BY, HAVING y con los operadores conjuntistas: UNION, INTERSECT
y MINUS.

Una secuencia se elimina como otro elemento cualquiera del esquema:

DROP SEQUENCE <nombre_secuencia>

Elementos de Oracle – Luis Glez. Ares – [email protected]

3

Oracle – Elementos

Papelera de reciclaje

Oracle dispone de una implementación de una papelera de reciclaje en la que se almacenan
las tablas eliminadas con DROP TABLE y los objetos asociados a las tablas eliminadas, como
índices, vistas, etc.

Al realizar el DROP TABLE de una tabla, realmente se efectúa un cambio de nombre de la tabla
y de sus objetos asociados, comenzando su nombre por BIN.

Los objetos eliminados pasan a la vista USER_RECYCLEBIN o RECYCLEBIN y pueden referenciarse
indicando su nuevo nombre entre comillas. Podemos consultar el contenido de RECYCLEBIN
con un SELECT o de forma abreviada con SHOW RECYCLEBIN

Sobre los objetos de la papelera no pueden realizarse sentencias DDL o DML diferentes a
SELECT.

DROP TABLE k5;

SELECT *
FROM

RECYCLEBIN;

SHOW

RECYCLEBIN

ORIGINAL NAME
---------------- ------------------------------ ------------ -------------------
K5
2009-01-14:20:40:36

BIN$YHGlepy+3mngQAoKLgdoNw==$0 TABLE

RECYCLEBIN NAME

OBJECT TYPE

DROP TIME

SELECT * FROM "BIN$YHGlepy+3mngQAoKLgdoNw==$0";

Elementos de Oracle – Luis Glez. Ares – [email protected]

4

Oracle – Elementos

Papelera de reciclaje (cont.)

Al realizar DROP TABLE realmente solo se efectúa un cambio de nombre, ya que todas las
referencias físicas de la tabla se mantienen inalterables.

Los objetos de RECYCLEBIN se eliminan de ella al ejecutar alguna de las sentencias siguientes:

PURGE TABLE <tabla>

PURGE INDEX <indice>

PURGE RECYCLEBIN

Elimina definitivamente la tabla

Elimina definitivamente el índice

Elimina todos los objetos de RECYCLEBIN

FLASHBACK TABLE <tabla> TO BEFORE DROP Restaura la tabla a su nombre original

la papelera almacena varias versiones de una misma tabla, o sea, si se han creado y
Si
eliminado varias tablas con el mismo nombre, la sentencia FLASHBACK TABLE restaura la que se
ha eliminado más recientemente (LIFO).

Si despues de restaurar una tabla, posteriormente deseamos restaurar otra con el mismo
nombre, debemos renombrarla:

FLASHBACK TABLE k0 TO BEFORE DROP;
FLASHBACK TABLE k0 TO BEFORE DROP RENAME TO kk0;

La papelera puede desactivarse de forma general poniendo a OFF el parámetro inicial RECYCLEBIN,
o de forma puntual usando al eliminar una tabla una variante de DROP:

DROP TABLE <tabla> PURGE

Elementos de Oracle – Luis Glez. Ares – [email protected]

5

Oracle – Elementos

Funciones de interés

Algunas funciones de interés son (véanse otras en el manual):

INITCAP(cadena)

Primer carácter de cada palabra a mayúsculas:

INITCAP(’MIs fIcherOs’)

Mis Ficheros

UPPER/LOWER(cadena)

Pasa a mayúsculas/minúsculas:

UPPER(’MIs fIcherOs’)

MIS FICHEROS /

LOWER(’MIs fIcherOs’)

mis ficheros

SUBSTR(’Cadena’,n[,m])

Subcadena que empieza en n y tiene m caracteres:

SUBSTR(’Cadena’,2,3)

ade

RTRIM(cadena)

Elimina los espacios en blanco a la derecha:

RTRIM(’ 12 45 ’)

’ 12 45’

LTRIM(cadena)

Elimina los espacios en blanco a la izquierda:

LTRIM(’ 12 45 ’)

’12

45 ’

TRIM(cadena)

Elimina los espacios en blanco a ambos lados:

TRIM(’ 12 45 ’)

’12 45’

LENGTH(cadena)

Longitud de la cadena:

LENGTH(’ 12 45 ’)

8

LPAD(cad1,n,cad2)
RPAD(cad1,n,cad2)

LPAD(’123’,5,’*’)

cad1 con lg. n, ajustada a la dcha, rellenando a la izda con cad2
cad1 con lg. n, ajustada a la izda, rellenando a la dcha con cad2
**123

RPAD(’123’,5,’*’)

/

123**

Elementos de Oracle – Luis Glez. Ares – [email protected]

6

Oracle – Elementos

Funciones de interés (cont.)

POWER(n,m)

POWER(3,2)

SQRT(n)

SQRT(25)

Eleva n a la m-ésima potencia:

9

Raíz cuadrada de n:

5

FLOOR(n)

Mayor entero menor o igual a n

FLOOR(11.2)

11

NVL(expresion,valor)

Sustitución de valor nulo:

NVL(COMM,0)

Si COMM es no nulo = COMM. Si COMM es nulo = 0.

NVL2(expresion,v1,v2)

Sustitución condicional de valor nulo:

NVL2(COMM,5,8)

Si COMM es no nulo = 5.

Si COMM es nulo = 8.

SYSDATE

SYSDATE

Fecha y hora actual:

14/12/06 (depende del formato defectivo de la instalación)

USER

Nombre del usuario.

De agrupamiento:

MIN(expresion)
Mínimo

MAX(expresion)
Máximo

AVG(expresion)
Media

STDDEV(expresion)
Desviación estándar

VARIANCE(expresion)
Varianza

COUNT(expresion)
Número de elementos

Elementos de Oracle – Luis Glez. Ares – [email protected]

7

Oracle – Elementos

Variables de sustitución

Las variables de sustitución permiten parametrizar una sentencia, pudiendo ejecutarla varias
veces e introducir, en tiempo de ejecución, los datos que la hacen diferente.

Ejemplo.- Sentencia que obtiene las filas que cumplen una condición:

SQL> SELECT empno, ename, sal, deptno

emp

FROM
WHERE sal > 1000
AND

deptno = 10

EMPNO ENAME
SAL DEPTNO
----- ---------- ------- ------
10
10
10

7782 CLARK
7839 KING
7934 MILLER

2,450
5,000
1,300

Objetivo: parametrizar la sentencia.

Método: usar variables para introducir los valores de los datos.

Elementos de Oracle – Luis Glez. Ares – [email protected]

8

Oracle – Elementos

Variables de sustitución (cont.)

Parametrizar los valores de los datos

SQL> SELECT empno, ename, sal, deptno

emp

FROM
WHERE sal > 1000
AND

deptno = &dept

Introduzca un valor para dept: 20
antiguo
nuevo

deptno = 20

deptno = &dept

4: AND

4: AND

EMPNO ENAME
SAL DEPTNO
----- ---------- ------- ------
20
20
20
20

7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD

2,975
3,000
1,100
3,000

La aparición de los valores antiguo y nuevo se debe a la activación del VERIFY:

SQL> SET VERIFY OFF

Elementos de Oracle – Luis Glez. Ares – [email protected]

9

Oracle – Elementos

Variables de sustitución (cont.)

Varias variables

SQL> SELECT empno, ename, sal, deptno

emp

FROM
WHERE ename LIKE ’&nome’
AND

deptno = &dept

Introduzca un valor para nome: S%
Introduzca un valor para dept: 20

EMPNO ENAME
SAL DEPTNO
----- ---------- ------- ------
20
20

7369 SMITH
7788 SCOTT

800
3,000

-> Variable CHAR

-> Variable CHAR

Las variables de sustitución pueden ponerse en el lugar de:
• Un valor.
• Una columna.
• Una tabla.
• Expresiones en: ORDER BY y SELECT.
• Predicados en: WHERE y HAVING.

Elementos de Oracle – Luis Glez. Ares – [email protected]

10

Oracle – Elementos

Variables de sustitución (cont.)

Sustitución de columnas y expresiones

SQL> SELECT empno, ename, sal, &cln1

-> Columna

FROM
WHERE &con

emp

-> Predicado en WHERE

Introduzca un valor para cln1: job
Introduzca un valor para con: SAL > 3000

EMPNO ENAME
----- ---------- ------- ---------
5,000 PRESIDENT

7839 KING

SAL JOB

Ha sustituido el nombre de una columna de un SELECT y un predicado en un WHERE.

SQL> INSERT INTO dept (deptno, dname, loc)

VALUES (&dp,’&dn’, &lc)

-- Atención al tipo de dato

Introduzca un valor para dp: 50
Introduzca un valor para dn: VENTAS
Introduzca un valor para lc: ’BCN’

-- Dos alternativas para CHAR! (p.e. NULL)

Elementos de Oracle – Luis Glez. Ares – [email protected]

11

Oracle – Elementos

Variables de sustitución (cont.)

Reutilizar el valor de una variable
Utilizando & siempre pide el valor para la variab