Cerrar

Enviar mensaje

twitter FaceBook You Tube Git Hub Docs.com Enviar email

Manual de SQL (Oracle SQL)

[6]
Funciones de fila

Publicidad

[6.1] introducción a las funciones

Todos los SGBD implementan funciones para facilitar la creación de consultas complejas. Esas funciones varían en cada Sistema Gestor de Bases de Datos. En estos apuntes se comentan funciones presentes en el SGBD Oracle.

Como se observa en la ilustración anterior, las ilustraciones requieren datos de entrada y, a partir de ellos, obtienen un resultado. No siempre requieren parámetros, pero es lo habitual.

Para invocar a una función, en SQL disponemos de esta sintaxis

nombreFunción[(parámetro1[, parámetro2,...])]

Si una función no precisa parámetros (como SYSDATE) no hace falta indicar los paréntesis.

[6.1.1]tipos de funciones

En realidad hay dos tipos de funciones:

En esta unidad se tratan las funciones del primer tipo.

[6.1.2]tabla DUAL

Oracle proporciona una tabla llamada DUAL con la que se permiten hacer pruebas. Esa tabla tiene una sola columna (llamada DUMMY) y una sola filaAsí, por ejemplo la consulta:

SELECT SQRT(5) FROM DUAL;

muestra una tabla con el contenido de ese cálculo (la raíz cuadrada de 5). En esa tabla solo habrá una columna con una fila, la cual mostrará el resultado del cálculo.

DUAL es una tabla interesante para hacer pruebas, ya que podemos utilizar funciones o cálculos sabiendo que devolverán un único resultado: eso sí esos cálculos se realizarán sobre valores independientes (no sobre valores de las tablas de la base de datos).

[6.2] funciones numéricas

[6.2.1]redondeos

Función

Descripción

ROUND(n,decimales)

Redondea el número al siguiente número con el número de decimales indicado más cercano. ROUND(8.239,2) devuelve 8.24

TRUNC(n,decimales)

Los decimales del número se cortan para que sólo aparezca el número de decimales indicado

[6.2.2]matemáticas

Función

Descripción

MOD(n1,n2)

Devuelve el resto resultado de dividir n1 entre n2

POWER(valor,exponente)

Eleva el valor al exponente indicado

SQRT(n)

Calcula la raíz cuadrada de n

SIGN(n)

Devuelve 1 si n es positivo, cero si vale cero y -1 si es negativo

ABS(n)

Calcula el valor absoluto de n

EXP(n)

Calcula en, es decir el exponente en base e del número n

LN(n)

Logaritmo neperiano de n

LOG(n)

Logaritmo en base 10 de n

SIN(n)

Calcula el seno de n (n tiene que estar en radianes)

COS(n)

Calcula el coseno de n (n tiene que estar en radianes)

TAN(n)

Calcula la tangente de n (n tiene que estar en radianes)

ACOS(n)

Devuelve en radianes el arco coseno de n

ASIN(n)

Devuelve en radianes el arco seno de n

ATAN(n)

Devuelve en radianes el arco tangente de n

SINH(n)

Devuelve el seno hiperbólico de n

COSH(n)

Devuelve el coseno hiperbólico de n

TANH(n)

Devuelve la tangente hiperbólica de n

[6.3] funciones de caracteres

[6.3.1]conversión del texto a mayúsculas y minúsculas

Función

Descripción

LOWER(texto)

Convierte el texto a minúsculas (funciona con los caracteres españoles)

UPPER(texto)

Convierte el texto a mayúsculas

INITCAP(texto)

Coloca la primera letra de cada palabra en mayúsculas

[6.3.2]funciones de transformación

Función

Descripción

RTRIM(texto)

Elimina los espaciosa la derecha del texto

LTRIM(texto)

Elimina los espacios a la izquierda que posea el texto

TRIM(texto)

Elimina los espacios en blanco a la izquierda y la derecha del texto y los espacios dobles del interior.

TRIM(caracteres FROM texto)

Elimina del texto los caracteres indicados. Por ejemplo TRIM(‘h’ FROM nombre) elimina las haches de la columna nombre que estén a la izquierda y a la derecha

SUBSTR(texto,n[,m])

Obtiene los m siguientes caracteres del texto a partir de la posición n (si m no se indica se cogen desde n hasta el final). Ejemplo:

SELECT SUBSTR(‘Prueba’,2,3) 
FROM DUAL;

Escribiría el texto rue (ya que son las tres siguientes letras desde la posición dos).

Si el parámetro n es un número negativo, entonces la posición inicial del texto a extraer se calcula de derecha a izquierda así por ejemplo la instrucción:

SELECT SUBSTR(‘Prueba’,-3,2) 
FROM DUAL;

Mostraría el texto: eb

LENGTH(texto)

Obtiene el tamaño del texto. Así: LENGTH(‘Hola’) devolvería cuatro.

REPLACE(texto, textoABuscar, [textoReemplazo])

Buscar el texto a buscar en un determinado texto y lo cambia por el indicado como texto de reemplazo.

Si no se indica texto de reemplazo, entonces está función elimina el texto que se busca.

LPAD(texto, anchuraMáxima,
[
caracterDeRelleno])

RPAD(texto, anchuraMáxima,
[
caracterDeRelleno])

Rellena el texto a la izquierda (LPAD) o a la derecha (RPAD) con el carácter indicado para ocupar la anchura indicada.

Si el texto es más grande que la anchura indicada, el texto se recorta.

Si no se indica carácter de relleno se rellena el espacio marcado con espacios en blanco.

Ejemplo:

LPAD(‘Hola’,10,’-‘)

devuelve como resultado:

------Hola

REVERSE(texto)

Invierte el texto (le da la vuelta)

INSTR(texto, textoBuscado [,posInicial [, nAparición]])

Obtiene la posición en la que se encuentra el texto buscado en el texto inicial. Ejemplo:

SELECT INSTR(‘es lo que es’,
’es’)
FROM DUAL;

Escribe el valor: 0

Se puede empezar a buscar a partir de una posición inicial concreta, por ejemplo para buscar desde el tercer carácter.

SELECT INSTR(‘es lo que es’,
’es’,3)
FROM DUAL;

Devuelve 11

La posición inicial podría ser negativa y en ese caso buscaríamos desde el lado derecho.

SELECT INSTR(‘es lo que es’,
’es’,1)
FROM DUAL;

También devuelve 11

Incluso podemos indicar el número de aparición del texto buscado:

SELECT INSTR(‘es lo que es’,
’es’,1,2)
FROM DUAL;

Vuelve a mostrar 11 (posición de la segunda vez que aparece la palabra es). Si no se encuentra el texto a buscar, la función devuelve el número cero.

TRANSLATE(texto,
caracteresACambiar,
caracteresSustitutivos)

Potentísima función que permite transformar caracteres. Se cambia una serie de caracteres por otros que se indiquen en el mismo orden.

De tal modo que, el primer carácter a cambiar se cambia por el primer carácter sustitutivo, el segundo por el segundo y así sucesivamente. Ejemplo:

SELECT TRANSLATE(‘prueba’,’ue’,
’wx’)
FROM DUAL;

El resultado sería el texto prwxba, de tal forma que la u se cambia por la w y la e por la x.

Si la segunda cadena es más corta, los caracteres de la primera que no encuentran sustituto, se eliminan. Ejemplo:

SELECT TRANSLATE(‘prueba’,’ue’,’w’)
FROM DUAL;

Da como resultado prwba

[6.3.3]otras funciones de caracteres

Función

Descripción

ASCII(carácter)

Devuelve el código ASCII del carácter indicado. Si se le pasa más de un carácter, devuelve el código ASCII del primer carácter-

CHR(número)

Devuelve el carácter correspondiente al código ASCII indicado

SOUNDEX(texto)

Devuelve el valor fonético del texto. Es una función muy interesante para buscar textos de los que se no se sabe con exactitud su escritura. Por ejemplo:

SELECT * FROM personas 
WHERE SOUNDEX(apellido1)                 
=SOUNDEX
(‘Smith’)

En el ejemplo se busca a las personas cuyo primer apellido suena como Smith

[6.3.4]funciones de trabajo con nulos

Permiten definir valores a utilizar en el caso de que las expresiones tomen el valor nulo.

Función

Descripción

NVL(valor,sustituto)

Si el valor es NULL, devuelve el valor sustituto; de otro modo, devuelve valor

NVL2(valor,sustituto1, sustituto2)

Variante de la anterior, devuelve el valor sustituto1 si valor no es nulo. Si valor es nulo devuelve el sustituto2

NULLIF(valor1,valor2)

Devuelve nulo si valor1 es igual a valor2. De otro modo devuelve valor1

COALESCE(listaExpresiones)

Devuelve la primera de las expresiones que no es nula. Ejemplo:

CREATE TABLE test (
col1 VARCHAR2(1),
	col2  VARCHAR2(1),
	col3  VARCHAR2(1)
);
INSERT INTO test VALUES (NULL, 
‘B’, ‘C’);
INSERT INTO test VALUES (‘A’, 
NULL, ‘C’);
INSERT INTO test VALUES (NULL, 
NULL, ‘C’);
INSERT INTO test VALUES (‘A’, 
‘B’, ‘C’);
SELECT COALESCE(col1, col2, 
col3) FROM test;

(continuación COALESCE)

El resultado es:

B
A
C
A

Otro ejemplo (muestra el primer teléfono no nulo, si ambos son nulos muestra el texto: Sin teléfono):

SELECT nombre,apellido1,
  COALESCE(
telefono_fijo,
telefono_movil,
	‘Sin telefono’)
FROM empleados;

[6.4] funciones de fecha

[6.4.1]manejo de fechas e intervalos

Las fechas se utilizan muchísimo en todas las bases de datos. Oracle proporciona dos tipos de datos para manejar fechas, los tipos DATE y TIMESTAMP. En el primer caso se almacena una fecha concreta, en el segundo caso se almacena un instante de tiempo (fecha y hora) más concreto que puede incluir incluso fracciones de segundo.

Hay que tener en cuenta que a los valores de tipo fecha se les pueden sumar números y se entendería que estamos sumando días a la fecha (SYSDATE +2 suma dos días a la fecha actual). Si tiene decimales entonces se suman días, horas, minutos y segundos (SYSDATE +2.5 suma dos días y 12 horas a la fecha actual). La diferencia entre dos fechas también obtiene un número de días.

21intervalos

Los intervalos son datos relacionados con las fechas en sí, pero que no son fechas. Hay dos tipos de intervalos el INTERVAL DAY TO SECOND que sirve para representar días, horas, minutos y segundos; y el INTERVAL YEAR TO MONTH que representa años y meses.

Para los intervalos de año a mes los valores se pueden indicar de estas formas:

/* 123 años y seis meses */
INTERVAL ‘123-6’ YEAR TO MONTH
/* 123 años */
INTERVAL ‘123’ YEAR TO MONTH
/* 6 meses */
INTERVAL ‘6’ MONTH TO MONTH

La precisión en el caso de indicar tanto años como meses, se indica sólo en el año. En intervalos de días a segundos los intervalos se pueden indicar como:

/* 4 días 10 horas 12 minutos y 7 con 352 segundos */
INTERVAL ‘4 10:12:7,352’ DAY TO SECOND(3)
/* 4 días 10 horas 12 minutos */
INTERVAL ‘4 10:12’ DAY TO MINUTE
/* 4 días 10 horas */
INTERVAL ‘4 10’ DAY TO HOUR
/* 4 días*/
INTERVAL ‘4’ DAY
/*10 horas*/
INTERVAL ‘10’ HOUR
/*25 horas*/
INTERVAL ‘253’ HOUR
/*12 minutos*/
INTERVAL ‘12’ MINUTE
/*30 segundos */
INTERVAL ‘30’ SECOND
/*8 horas y 50 minutos */
INTERVAL ‘8:50’ HOUR TO MINUTE;
/*7 minutos 6 segundos*/
INTERVAL ‘7:06 MINUTE TO SECOND;
/*8 horas 7 minutos 6 segundos*/
INTERVAL ‘8:07:06’ HOUR TO SECOND;

Esos intervalos se pueden sumar a valores de tipo DATE o TIMESTAMP para hacer cálculos. Gracias a ello se permiten sumar horas o minutos por ejemplo a los datos de tipo TIMESTAMP.

[6.4.2]obtener la fecha y hora actual

Función

Descripción

SYSDATE

Obtiene la fecha y hora actuales

SYSTIMESTAMP

Obtiene la fecha y hora actuales en formato TIMESTAMP

22calcular fechas

Función

Descripción

ADD_MONTHS(fecha,n)

Añade a la fecha el número de meses indicado por n

MONTHS_BETWEEN(fecha1, fecha2)

Obtiene la diferencia en meses entre las dos fechas (puede ser decimal)

NEXT_DAY(fecha,día)

Indica cual es el día que corresponde a añadir a la fecha el día indicado. El día puede ser el texto ‘Lunes’, ‘Martes’, ‘Miércoles’,... (si la configura­ción está en español) o el número de día de la semana (1=lunes, 2=martes,...)

LAST_DAY(fecha)

Obtiene el último día del mes al que pertenece la fecha. Devuelve un valor DATE

EXTRACT(valor FROM fecha)

Extrae un valor de una fecha concreta. El valor puede ser day (día), month (mes), year (año), etc.

GREATEST(fecha1, fecha2,..)

Devuelve la fecha más moderna la lista

LEAST(fecha1, fecha2,..)

Devuelve la fecha más antigua la lista

ROUND(fecha [,’formato])

Redondea la fecha al valor de aplicar el formato a la fecha. El formato puede ser:

  • ‘YEAR’ Hace que la fecha refleje el año completo
  • ‘MONTH’ Hace que la fecha refleje el mes completo más cercano a la fecha
  • ‘HH24’ Redondea la hora a las 00:00 más cercanas
  • ‘DAY’ Redondea al día más cercano

TRUNC(fecha [,formato])

Igual que el anterior pero trunca la fecha en lugar de redondearla.

[6.5] funciones de conversión

Oracle es capaz de convertir datos automáticamente a fin de que la expresión final tenga sentido. En ese sentido son fáciles las conversiones de texto a número y viceversa. Ejemplo:

SELECT 5+’3’ FROM DUAL --El resultado es 8 
SELECT 5 || ‘3’ FROM DUAL -- El resultado es 53 

También ocurre eso con la conversión de textos a fechas, podemos usar las fechas como un texto y si usamos el formato predeterminado de nuestro sistema, Oracle lo entenderá. Por ejemplo en

SELECT SYSDATE - 1/1/2000 FROM DUAL;

Nos devolverá los días que han pasado desde el año 2000 ya que entiende 1/1/2000 como una fecha y no como la división de tres números. No obstante para las fechas deberemos saber cómo especificarlas y eso además depende de cada sistema.

Por ello lo más seguro es utilizar funciones de conversión, ya podremos pasar de unos tipos de datos a otros y además en la forma que especifiquemos.

[6.5.1]TO_CHAR

Permite convertir una fecha o un número a texto . Su sintaxis es:

TO_CHAR(expresión [,modeloDeFormato])

El primer parámetro es la expresión que queremos convertir. El segundo (aunque es opcional es raro no utilizarle) es un texto (por lo que va entrecomillado) que contiene la forma de convertir deseada. Los códigos posibles se explican más adelante.

Ejemplo:

SELECT TO_CHAR(SYSDATE,’dd/month/yyyy’) FROM DUAL;

Resultado:

19/abril     /2014

23prefijo fm

SI anteponemos el texto fm a los códigos de formato (tanto para convertir fechas como para números), entonces se eliminan en el resultado todos los ceros a la izquierda que se obtenga y todos los espacios interiores.

Ejemplo:

SELECT TO_CHAR(SYSDATE,fmdd/month/yyyy’) FROM DUAL;

Resultado:

19/abril/2014

[6.5.2]TO_NUMBER

El funcionamiento es igual que TO_CHAR, sólo que la labor de TO_NUMBER es la contraria, convierte un texto en un número. Se utiliza para introducir valores en las tablas, normalmente.

Esta función admite el prefijo fx en el texto con los códigos de conversión que obliga a que la expresión encaje exactamente con el modelo (de otro modo los ceros a la izquierda se ignoran)

[6.5.3]TO_DATE

Funciona como las anteriores, pero se encarga de convertir un texto en una fecha. Imprescindible para añadir fechas a las tablas.

Admite también el uso del prefijo fx para que la expresión a convertir encaje exactamente con el modelo de códigos de conversión (de otro modo se ignoran los espacios en blanco)

[6.5.4]códigos de las funciones de conversión

Las funciones TO_CHAR, TO_NUMBER y TO_DATE para poder realizar su labor de conversión utilizan un parámetro que es un texto en el que se indican códigos especiales para especificar la forma de convertir. Se explican a continuación los códigos posibles tanto para convertir de fecha a texto (y viceversa) como de número a texto (y viceversa).

24fechas

En el caso de las fechas se indica el formato de conversión, que es una cadena que puede incluir estos símbolos (en una cadena de texto):

Función

Descripción

RR

Formato de año en dos cifras. De modo que si el año actual es superior al 50 del siglo en el que estemos, si especificamos un año inferior al 50, se referirá al siglo anterior.

Si estamos en un año inferior al 50, los años indicamos con cifras superiores a 50 se referirán al siglo anterior.

Es el formato de dos cifras más natural y más pensado para el futuro.

Y

Última cifra del año

YY

Año en formato de dos cifras

YYY

Año en formato de tres cifras

YYYY

Año en formato de cuatro cifras

SYYYY

Igual que el anterior, pero si el año es anterior a Cristo muestra un signo negativo

I

IY

IYY

IYYY

Año en formato de una, dos, tres o cuatro cifras según la notación ISO.

YEAR

Año en formato hablado (en inglés). Ejemplo:

SELECT TO_CHAR(SYSDATE,’day/month/year’) 
FROM DUAL;

Resulta:

sábado/abril/twenty fourteen

CC

Siglo (por ejemplo 21)

SCC

Siglo pero si es anterior a Cristo lo indica con un signo menos

BC o AD

Indica BC (en español usará AC) o AD (en español DC) si el año es anterior o posterior, respectivamente, al nacimiento de Jesucristo

MM

Mes en formato de dos cifras

MON

Las tres primeras letras del mes

MONTH

Nombre completo del mes

RM

Mes en romano

DY

Día de la semana en tres letras

DAY

Día completo de la semana

D

Día de la semana (del 1 al 7)

DD

Día del mes en formato de dos cifras (del 1 al 31)

DDD

Día del año

Q

Trimestre

WW o W

Semana del año

IW

Semana del año en formato ISO

J

Día juliano. Número de días transcurridos desde el 31 de Diciembre del 4713 A.C.

HH12

Hora en formato de 1 a 12

HH24

Hora en formato 24 horas (de 0 a 23)

MI

Minutos (0 a 59)

SS

Segundos (0 a 59)

SSSS

Segundos desde medianoche

AM o PM

Indicador de meridiano utilizado para mostrar la hora en formato de 12 horas. Mostrará AM o PM según corresponda

/ . ,:;’

Posición de los separadores, donde se pongan estos símbolos aparecerán en el resultado

Ejemplos:

SELECT TO_CHAR(SYSDATE, ‘DD/MONTH/YYYY, DAY HH:MI:SS’) 
FROM DUAL ;
/* Sale : 16/AGOSTO /2004, LUNES 08:35:15, por ejemplo*/

En la función TO_CHAR es posible indicar texto literal. Para ello se le encierra entre comillas dobles. Ejemplo:

SELECT TO_CHAR(SYSDATE, ‘Day DD “de” MONTH “de” YYYY’) 
FROM DUAL ;
/* Sale por ejemplo: Lunes 16 de Agosto de 2004  */

A los códigos anteriores se les pueden poner estos sufijos (sólo funcionan en inglés):

Función

Descripción

TH

Ordinal (en inglés). Ejemplo:

SELECT TO_CHAR(SYSDATE,’ddth/mm/yyyy’) 
FROM DUAL;

Mostrará:

19th/04/014

SP

Muestra los números como texto (en inglés), ejemplo:

SELECT TO_CHAR(SYSDATE,’ddsp/mm/yyyy’) 
FROM DUAL;

Escribe:

nineteen/04/2014

SPTH o THSP

Muestra los números como texto ordinal (en inglés). Ejemplo:

SELECT TO_CHAR(SYSDATE,’ddspth/mm/yyyy’) 
FROM DUAL;

Resulta:

nineteenth/04/2014

[6.5.5]números

Para convertir números a textos se usa está función cuando se desean características especiales. En ese caso en el formato se pueden utilizar estos símbolos:

Función

Descripción

9

Representa una posición para un dígito numérico

0

Representa una posición para un dígito numérico; si en esa posición no hay número, se rellenará con un cero

$

Usa formato dólar

L

Usa el símbolo local de la moneda

S

Posición para el signo del número

D

Posición del símbolo decimal (en español, la coma)

G

Posición del separador de grupo (en español el punto)

.

Punto decimal (utilizado para números en formato británico)

,

Coma de separador de grupo (utilizado para números en formato británico)

MI

Signo menos (se usa sólo a la derecha del número)

PR

Muestra los números negativos entre paréntesis

EEEE

Número en notación científica

U

Símbolo del euro

V

Multiplica al número por 10 tantas veces como se indique

B

Muestra el valor cero como espacio y no como cero

Ejemplo:

SELECT nombre, 
  TO_CHAR(salario,’99G990D00L’) AS salario_formato 
FROM personal;

Resultados posibles:

nombre

salario_formato

Pedro

21.000,00€

Paula

12.000,00€

Itziar

27.000,00€

Klaus

19.000,00€

Malena

26.000,00€

Rodrigo

14.000,00€

[6.5.6]conversión avanzada, CAST

CAST es una función muy versátil que permite convertir el resultado a un tipo concreto. Sintaxis:

CAST(expresión AS tipoDatos)

Ejemplo:

SELECT CAST(2.34567 AS NUMBER(7,6)) FROM DUAL; 

Lo interesante es que puede convertir de un tipo a otro. Por ejemplo imaginemos que tenemos una columna en una tabla mal planteada en la que el precio de las cosas se ha escrito en Euros. Los datos son (se muestra sólo la columna precio:

precio

25.2 €

2.8 €

123.65 €

.78 €

.123 €

20 €

Imaginemos que queremos doblar el precio, no podremos porque la columna es de tipo texto, por ello debemos tomar sólo la parte numérica y convertirla a número, después podremos mostrar los precios multiplicados por dos:

SELECT 2 * CAST(SUBSTR(precio,1,INSTR(precio,’€’)-2) AS NUMBER) 
FROM precios;

La combinación de SUBSTR e INSTR es para obtener sólo los números. Incluso es posible que haya que utilizar REPLACE para cambiar los puntos por comas (para utilizar el separador decimal del idioma español).

[6.6] funciones condicionales

[6.6.1]expresiones CASE

Realmente CASE no es una función al uso. Pero su utilidad es la misma, al final devuelve un valor.

Se trata de un elemento que da mucha potencia a las instrucciones SQL ya que simula una estructura de tipo if-then-else que es una de las bases de la programación en lenguajes de ordenador. La sintaxis es:

CASE [expresión] 
  WHEN expresión_comparación1 THEN valor_devuelto1
  [WHEN expresión_comparación2 THEN valor_devuelto2
     [ELSE valor_devuelto_else]]
END;

La idea es:

Ejemplo:

SELECT nombre,
	CASE cotizacion   WHEN 1 THEN salario*0.85
				WHEN 2 THEN salario*0.93
				WHEN 3 THEN salario*0.96
				ELSE salario
	END
FROM empleados;

En el ejemplo, se calcula una columna a partir del salario de modo que el cálculo varía en función de lo que vale la columna cotización. En el caso de que esa columna no valga ni uno, ni dos, ni tres, se mostrará el salario tal cual (para eso sirve el apartado ELSE).

La expresión de comparación (a diferencia de lo que ocurre con la función DECODE) puede ser compleja. Para ello no se indica expresión alguna y se colocan expresiones más complejas en los apartados WHEN. Ejemplo:

SELECT nombre, 
  CASE   WHEN nota>=5 AND nota<7 THEN ‘Suficiente’
             WHEN nota>=7 AND nota<9 THEN ‘Notable’
             WHEN nota>=9 THEN ‘Sobresaliente’
             ELSE ‘Suspenso’ 
    END AS calificacion
FROM alumnos

25función DECODE

Función que permite realizar condiciones en una consulta. Simplifica la función anterior (aunque tiene menos posibilidades que la anterior). Se evalúa una expresión y se colocan a continuación pares valor-resultado de forma que si se la expresión equivale al valor, se obtiene el resultado indicado.

Se puede indicar un último parámetro con el resultado a efectuar en caso de no encontrar ninguno de los valores indicados (equivalente al apartado ELSE de la función anterior).

Sintaxis:

DECODE(expresión, valor1, resultado1 
        [,valor2, resultado2,...]
		[,valorPordefecto])

Ejemplo:

SELECT nombre,
	DECODE(cotizacion,1, salario*0.85,
      		2,salario * 0.93,
 			3,salario * 0.96,
				salario)
FROM empleados;

En el ejemplo dependiendo de la cotización se muestra rebajado el salario: un 85% si la cotización es uno, un 93 si es dos y un 96 si es tres. Si la cotización no es ni uno ni dos ni tres, sencillamente se muestra el salario sin más.

[6.7] manejo de expresiones regulares

[6.7.1]expresiones regulares

Las expresiones regulares son una parte cada vez más importante de los lenguajes de programación. Han existido desde que aparecieron los lenguajes de programación de alto nivel y cada vez tienen más importancia, hasta el punto de que lenguajes como Perl hacen de las expresiones regulares su elemento fundamental.

En el caso de las bases de datos, también tienen una enorme importancia. En especial para crear restricciones de validación (CHECK). Las expresiones regulares permiten indicar máscaras que se refieren a un formato concreto que debe de cumplir el texto; un patrón, en definitiva, al que el texto se debe de ajustar.

Por ejemplo si nuestra base de datos almacena números de teléfonos españoles, éstos se forman por nueve números. Podremos crear una expresión regular, por ejemplo mediante la función REGEXP_LIKE, que impida introducir teléfonos que no cumplan tener exactamente 9 números.

Una expresión regular está formada por un texto que contiene símbolos especiales que sirven para determinar en qué forma hay que cumplir la expresión. Tradicionalmente hay dos normas de expresiones regulares:

Además Oracle ha incluido la especificación que compatibiliza las expresiones regulares con las lenguas incluidas en Unicode, lo que evita problemas habituales con las expresiones regulares en otros lenguajes. A continuación se indican las posibilidades que tiene Oracle para indicar expresiones regulares.

[6.7.2]caracteres de uso habitual en las expresiones regulares

Código

Significado

c

Si c es un carácter cualquiera (por ejemplo a, H, ñ, etc.) indica, donde aparezca dentro de la expresión, que en esa posición debe aparecer dicho carácter para que la expresión sea válida.

Es decir si nuestra expresión regular es simplemente ‘a’ será válido para esa expresión cualquier texto que contenga la letra a.

cde

Siendo c, d, y e caracteres, indica que esos caracteres deben aparecer de esa manera en la expresión.

^x

Comenzar por. Indica el texto debe empezar por la expresión x. Por ejemplo:

‘^Ja’ es una expresión que da por válido cualquier texto que empiece por jota y luego lleve una a.

x$

Finalizar por. Indica que el String texto terminar con la expresión x.

[cde]

Opción, son válidos uno de estos caracteres: c, d ó e

c-d

Cumplen esa expresión los caracteres que, en orden ASCII, vayan del carácter c al carácter d. Por ejemplo a-z representa todas las letras minúsculas del alfabeto inglés.

[^x]

No es válido ninguno de los caracteres que cumplan la expresión x. Por ejemplo [^dft] indica que no son válidos las caracteres d, f ó t.

.

Cualquier carácter. El punto indica que en esa posición puede ir cualquier carácter.

x+

La expresión a la izquierda de este símbolo se puede repetir una o más veces

x*

la expresión a la izquierda de este símbolo se puede repetir cero o más veces

x?

El carácter a la izquierda de este símbolo se puede repetir cero o una veces

x{n}

Significa que la expresión x aparecerá n veces, siendo n un número entero positivo.

x{n,}

Significa que la expresión x aparecerá n o más veces

x{m,n}

Significa que la expresión x aparecerá de m a n veces.

(x)

Permite indicar un subpatrón dentro del paréntesis. Ayuda a formar expresiones regulares complejas.

x|y

La barra indica que las expresiones x e y son opcionales, se puede cumplir una u otra.

[6.7.3]caracteres especiales

Nos permiten indicar caracteres que de otra forma no se pueden escribir (como el salto de línea por ejemplo), ase les llama también secuencias de escape y proceden del lenguaje C.

Símbolo

Descripción

\t

Tabulador

\n

Salto de línea

\r

Salto de carro

\v

Tabulador vertical

\\

La propia barra

También se utiliza si necesitamos poner como literales algunos de los símbolos especiales de las expresiones regulares. Por ejemplo el símbolo punto (.) significa cualquier carácter, pero si indicamos \. entonces es el propio carácter punto. Por ejemplo si nuestra expresión verifica que un texto contiene una dirección de correo electrónico, pondríamos: .*@.*\..* y eso daría válido cualquier texto que empiece con lo que sea (.*), que luego tenga el símbolo @, luego lo que sea (.*) luego, obligatoriamente, un punto (\.) y luego lo que sea.

Lo mismo para otros símbolos: \$, \[, \],…

[6.7.4]símbolos procedentes del modelo POSIX

Deben utilizarse siempre dentro de los corchetes, así por ejemplo si nuestra expresión se refiere a una letra, se pondría [[:alpha:] y si es cualquier carácter excepto letra, se usa [^[:alpha:] y si fuera una letra o el número5, sería: [[:alpha:]3]

código

significado

[:alpha:]

Letra

[:alnum:]

Letra o número

[:blank:]

Espacio o tabulador

[:cntrl:]

Carácter de control

[:digit:]

Número

[:graph:]

Carácter visible (no valen ni espacios ni caracteres de control

[:lower:]

Letra minúscula

[:print:]

Carácter visible o el espacio en blanco

[:punct:]

Caracteres de puntuación

[:space:]

Espacios en blanco y símbolos que añaden espacios (como el salto de línea, tabulador,…)

[:upper:]

Letra mayúscula

[:xdigit:]

Dígito hexadecimal

[6.7.5]símbolos procedentes del modelo Perl de expresión regular (y válidos en Oracle)

código

significado

\d

Dígito, vale cualquier dígito numérico

\D

Todo menos dígito

\s

Espacio en blanco

\S

Cualquier carácter salvo el espacio en blanco

\w

Word, carácter válido dentro de los que PHP considera para identificar variables. Es decir, letras, números o el guion bajo.

\W

Todo lo que no sea un carácter de tipo Word.

[6.7.6]ejemplos de expresiones regulares

Ejemplo1: DNI, ocho números y una letra:

‘^[0-9]{8][A-Z]$’

Ejemplow: NIF, Puede ser como un DNI o bien las letras X, Y o Z seguidas de siete números y una letra:

‘^([XYZ]|[0-9])[0-9]{7][A-Z]$’

Se delimita entre ^y $ para que exactamente el contenido del texto que cumpla la expresión sea así. De otro modo este texto cumpliría la expresión:

holhola17672612Cholahola --No es válido, gracias a 
y $

Ejemplo 3: Nº de serie con una o dos letras mayúsculas, tres números, seguido de una letra un guión y dos números:

‘^[A-Z]{1,2}[0-9]{3}[A-Z]-[0-9]{2}$’

Ejemplo 4: Código postal, números del 01000 al 52999 (se hacen tres grupos: del 10000 al 19999; del 01000 al 09000; y del 51000 al 52999; de otro modo dejaríamos algún caso de más)

‘^([1-4][0-9]{4}) | (0[1-9][0-9]{3})| (5[1-2][0-9]{3})$’

Ejemplo 5: Correo electrónico. Al menos una letra con posibilidad de punto, y, si lo hay, habría letra a sus dos lados y al menos un punto tras el símbolo de arroba. Al final un punto y nombre de dominio de dos o tres letras. Nota: \w significa palabra)

‘^[\w]+(\.[\w]+)*@[\w]+ (\.[\w]+)* (\.[a-z]{2,3})$’

[6.7.7]funciones de Oracle paras uso con expresiones regulares

código

significado

REGEXP_LIKE(texto,
expresionRegular [,flag])

Devuelve verdadero si el texto cumple la expresión regular.

El flag que se puede indicar como tercer parámetro es una de estas letras (entrecomi­lladas):

i. No distingue entre mayúsculas y minúsculas

c. Distingue entre mayúsculas y minúsculas

n. Hace que el punto represente a todos los caractere4s excepto al salto de línea (\n).

x. Ignora los espacios en blanco

REGEXP_COUNT(texto, expresionRegular [,posición [,flag]])

Cuenta las veces que aparece la expresión regular en el texto. Se puede especificar la posición en el texto desde la que empezamos a buscar y el parámetro flag comentado anteriormente.

REGEXP_INSTR(texto, expresionRegular [,posInicial [, nAparición [,modoPos [,flag [,subexpr]]]]]))

Es una versión de la función INSTR, pero con más potencia. Busca la expresión regular en el texto y devuelve su posición (o cero si no la encuentra).

Se puede empezar a buscar desde una posición (indicando un número en el parámetro posInicial) e incluso indicar que buscamos no la primera aparición (que es como funciona por defecto) sino que buscamos el número de aparición que indiquemos en el cuarto parámetro.

El quinto parámetro vale cero (valor por defecto) si la posición devuelta indica el primer carácter en el que aparece la expresión; y vale uno si la posición devuelta es la del siguiente carácter respecto al texto que cumpla la expresión regular.

flag es el parámetro comentado en la función REGEXP_LIKE.

REGEXP_REPLACE(texto, expresionRegular [, nuevoTexto [,posInicial [, nAparición [,flag]]]]))

Busca la expresión regular en el texto y reemplaza todo el texto que la cumple por el nuevo texto indicado.

Si no hay nuevoTexto, entonces se eliminan los caracteres que cumplan la expresión regular

REGEXP_SUBSTR(texto, expresionRegular [,posInicial [, nAparición [,flag [,subexpr]]]]))

Busca la expresión regular en el texto y extrae los caracteres que cumplen la expresión. Es, por lo tanto, una versión más potente de SUBSTR.