Funciones

En esta unidad aprenderás a: 1

Identificar las distintas funciones que se pueden usar con la cláusula SELECT.

2

Aplicar las diferentes funciones para obtener información de expresiones o de las columnas de las tablas.

4

4. Funciones 4.1 Introducción

4.1 Introducción Las funciones se usan dentro de expresiones y actúan con los valores de las columnas, variables o constantes. Generalmente producen dos tipos diferentes de resultados: unas producen una modificación de la información original (por ejemplo, poner en minúscula una cadena que está en mayúscula); el resultado de otras indica alguna cosa sobre la información (por ejemplo, el número de caracteres que tiene una cadena). Se utilizan en: cláusulas SELECT, cláusulas WHERE y cláusulas ORDER BY. Es posible el anidamiento de funciones. Existen cinco tipos de funciones: aritméticas, de cadenas de caracteres, de manejo de fechas, de conversión y otras funciones.

4.2 Funciones aritméticas Las funciones aritméticas trabajan con datos de tipo numérico NUMBER. Este tipo incluye los dígitos de 0 a 9, el punto decimal y el signo menos, si es necesario. Los literales numéricos no se encierran entre comillas. Ejemplo: –123.32. Estas funciones trabajan con tres clases de números: valores simples, grupos de valores y listas de valores. Algunas modifican los valores sobre los que actúan; otras informan de algo sobre los valores. Podemos dividir las funciones aritméticas en tres grupos: • Funciones de valores simples. • Funciones de grupos de valores. • Funciones de listas. Al describir los formatos de las funciones utilizaremos los corchetes ([ ]) para indicar que lo que va encerrado es opcional.

A. Funciones de valores simples Las funciones de valores simples son funciones sencillas que trabajan con valores simples. Un valor simple es: un número (como 6522,90), una variable o una columna de una tabla. Para probar algunas de estas funciones usaremos la tabla DUAL, cuya descripción es la siguiente: SQL> DESC DUAL; Nombre ----------DUMMY

¿Nulo? -------

Tipo ----------VARCHAR2(1)

75

Unidad 4

15/2/06

13:33

Página 76

4. Funciones 4.2 Funciones aritméticas

Las funciones de valores simples se muestran en la Tabla 4.1. Función ABS(n) CEIL(n) FLOOR(n) MOD(m, n) NVL(valor, expresión)

POWER(m, exponente) ROUND(número [,m])

SIGN(valor)

SQRT(n) TRUNC(número, [m])

Propósito Devuelve el valor absoluto de 'n'. El valor absoluto es siempre un número positivo. Obtiene el valor entero inmediatamente superior o igual a 'n'. Es lo opuesto a CEIL, devuelve el valor entero inmediatamente inferior o igual a 'n'. Devuelve el resto resultante de dividir 'm' entre 'n'. Esta función se utiliza para sustituir un valor nulo por otro valor. Si 'valor' es NULL, es sustituido por la 'expresión'; si no lo es, la función devuelve 'valor'. NVL se puede usar con cualquier tipo de datos: numéricos, carácter, tipo fecha, pero 'valor' y 'expresión' deben ser del mismo tipo, aunque admiten tipos diferentes. Calcula la potencia de un número. Devuelve el valor de 'm' elevado a un 'exponente'. Devuelve el valor de 'número' redondeado a 'm' decimales. Si 'm' es negativo, el redondeo de dígitos se lleva a cabo a la izquierda del punto decimal. Si se omite 'm', devuelve 'número' con 0 decimales y redondeado. Esta función indica el signo del 'valor'. Si 'valor' es menor que 0, la función devuelve _1; y si 'valor' es mayor que 0, la función devuelve 1. Devuelve la raíz cuadrada de 'n', El valor de 'n' no puede ser negativo. Trunca los números para que tengan un cierto número de dígitos de precisión. Devuelve 'número' truncado a 'm' decimales; 'm' puede ser negativo: si lo es, trunca por la izquierda del punto decimal. Si se omite 'm' devuelve 'número' con 0 decimales.

Tabla 4.1. Funciones de valores simples.

Caso práctico

1

ABS(n). Obtén el valor absoluto del SALARIO - 10000 para todas las filas de la tabla EMPLE: SQL> SELECT APELLIDO,

SALARIO, ABS(SALARIO-10000) FROM EMPLE;

CEIL(n). Prueba con números positivos y negativos: SQL> SELECT CEIL(20.3), CEIL(16), CEIL(-20.3), CEIL(-16) CEIL(20.3) ---------21

CEIL(16) --------16

CEIL(-20.3) -----------20

FROM DUAL;

CEIL(-16) ---------16 (Continúa)

76

4. Funciones 4.2 Funciones aritméticas

(Continuación)

FLOOR(n). Prueba con números positivos y negativos: SQL> SELECT FLOOR(20.3), FLOOR (16), FLOOR(-20.3), FLOOR(-16) FLOOR(20.3) ----------20

FLOOR(16) ---------16

FLOOR(-20.3) ------------21

FROM DUAL;

FLOOR(-16) ----------16

MOD(m, n). Prueba con números positivos y negativos: SQL> SELECT MOD(11,4), MOD(10,-15), MOD(-10,-3), MOD(10.4,4.5) FROM DUAL; MOD(11,4) --------3

MOD(10,-15) ----------10

MOD(-10,-3) -----------1

MOD(10.4,4.5) ------------1,4

NVL(valor, expresión). A partir de la tabla EMPLE obtenemos el SALARIO, la COMISION y la suma de ambos: SQL> SELECT SALARIO, COMISION, SALARIO + COMISION SALARIO ---------1040 1500 1625 2900 1600 3005 2885 3000 4100 1350 1430 1335 3000 1690

FROM EMPLE;

COMISION ----------

SALARIO+COMISION ----------------

390 650

1890 2275

1020

2620

0

1350

14 filas seleccionadas. En este ejemplo, se obtiene la suma del SALARIO y la COMISION. Si la comisión es nula, la columna SALARIO + COMISION da como resultado un valor nulo (no se visualiza nada), debido a que los valores nulos en las expresiones siempre darán como resultado un valor nulo. A partir de la tabla EMPLE obtenemos el SALARIO, la COMISION y la suma de ambos, pero aplicando la función NVL a la comisión. Si es nula sustituimos su valor por 0: SQL> SELECT SALARIO,COMISION, SALARIO + NVL(COMISION,0) FROM EMPLE;

(Continúa)

77

4. Funciones 4.2 Funciones aritméticas

(Continuación)

SALARIO ---------1040 1500 1625 2900 1600 3005 2885 3000 4100 1350 1430 1335 3000 1690

COMISION ---------390 650 1020

0

SALARIO+NVL(COMISION,0) ----------------------1040 1890 2275 2900 2620 3005 2885 3000 4100 1350 1430 1335 3000 1690

14 filas seleccionadas. En este otro ejemplo, al aplicar a la comisión la función NVL en la columna SALARIO + COMISION, no resultan valores nulos. POWER(m, exponente). Prueba con números positivos y negativos: SQL> SELECT POWER(2,4),POWER(2,-4),POWER(3.5, 2.4), POWER(4.5, 2) POWER(2,4) ---------16

POWER(2,-4) ----------,0625

POWER(3.5,2.4) -------------20,2191692

FROM DUAL;

POWER(4.5,2) -----------20,25

ROUND(número [,m]). Prueba con redondeo positivo: SQL> SELECT ROUND(1.56,1), ROUND(1.56), ROUND(1.2234,2), ROUND(1.2676, 3) FROM DUAL; ROUND(1.56,1) ------------1,6

ROUND(1.56) ----------2

ROUND(1.2234,2) --------------1,22

ROUND(1.2676,3) --------------1,268

Prueba con redondeo negativo: SQL> SELECT ROUND(145.5, -1), ROUND(145.5, -2), ROUND(145.5, -3), ROUND(141,-1), ROUND(145,-1) FROM DUAL; ROUND(145.5,-1) ROUND(145.5,-2) --------------- --------------150 100

ROUND(145.5,-3) ROUND(141,-1) ROUND(145,-1) --------------- ------------- ------------0 140 150

(Continúa)

78

4. Funciones 4.2 Funciones aritméticas

(Continuación)

SIGN(valor). Prueba con números positivos y negativos: SQL> SELECT SIGN(-10), SIGN(10) FROM DUAL; SIGN(-10) ----------1

SIGN(10) ---------1

SQRT(n). SQL> SELECT SQRT(25), SQRT(25.6) FROM DUAL; SQRT(25) -------5

SQRT(25.6) ---------5,05964426

TRUNC(número, [m]). Con truncamiento positivo: SQL> SELECT TRUNC(1.5634, 1), TRUNC(1.1684, 2), TRUNC(1.662) FROM DUAL; TRUNC(1.5634,1) --------------1,5

TRUNC(1.1684,2) --------------1,16

TRUNC(1.662) -----------1

Con truncamiento negativo: SQL> SELECT TRUNC(187.98,-1), TRUNC(187.98,-2), TRUNC(187.98,-3) FROM DUAL; TRUNC(187.98,-1) ---------------180

TRUNC(187.98,-2) ---------------100

TRUNC(187.98,-3) ---------------0

Actividades propuestas

1

¿Cuál sería la salida de ejecutar estas funciones? ABS(146)= CEIL(2)= CEIL(-2.3)= FLOOR(-2)= FLOOR(2)= MOD(22,23)= POWER(10,0)=

ABS(-30)= CEIL(1.3)= CEIL(-2)= FLOOR(-2.3)= FLOOR(1.3)= MOD(10,3)= POWER(3,2)=

POWER(3,-1)= ROUND(-33.67,2)= ROUND(-33.27,1)= TRUNC(67.232)= TRUNC(67.232,2)= TRUNC(67.58,1)=

ROUND(33.67)= ROUND(-33.67,-2)= ROUND(-33.27,-1)= TRUNC(67.232,-2)= TRUNC(67.58,-1)=

79

4. Funciones 4.2 Funciones aritméticas

B. Funciones de grupos de valores Hasta ahora nos hemos ocupado de funciones que operan con valores simples; no obstante, hay otras funciones estadísticas, como SUM, AVG y COUNT, que actúan sobre un grupo de filas para obtener un valor. Estas funciones permiten obtener la edad media de un grupo de alumnos, el alumno más joven, el más viejo, el número total de miembros de un grupo, etcétera. Los valores nulos son ignorados por las funciones de grupos de valores y los cálculos se realizan sin contar con ellos. Estas funciones se muestran en la Tabla 4.2. Función

Propósito

AVG(n) COUNT (* | expresión)

Calcula el valor medio de 'n' ignorando los valores nulos. Cuenta el número de veces que la expresión evalúa algún dato con valor no nulo. La opción '*' cuenta todas las filas seleccionadas. Calcula el máximo valor de la 'expresión'. Calcula el mínimo valor de la 'expresión'. Obtiene la suma de valores de la 'expresión' distintos de nulos. Obtiene la varianza de los valores de 'expresión' distintos de nulos.

MAX(expresión) MIN(expresión) SUM(expresión) VARIANCE (expresión)

Tabla 4.2. Funciones de grupos de valores.

Caso práctico

2

AVG(n). Cálculo del salario medio de los empleados del departamento 10 de la tabla EMPLE: SQL> SELECT AVG(SALARIO) FROM EMPLE WHERE DEPT_NO=10; AVG(SALARIO) -----------2891,66667 COUNT (* | expresión). Cálculo del número de filas de la tabla EMPLE: SQL> SELECT COUNT(*) FROM EMPLE; COUNT(*) -------14 Cálculo del número de filas de la tabla EMPLE donde la COMISION no es nula: SQL> SELECT COUNT(COMISION) FROM EMPLE; COUNT(COMISION) --------------4 MAX(expresión). Cálculo del máximo salario de la tabla EMPLE: SQL> SELECT MAX(SALARIO) FROM EMPLE; (Continúa)

80

4. Funciones 4.2 Funciones aritméticas

(Continuación) MAX(SALARIO) -----------4100 Obtén el apellido máximo (alfabéticamente) de la tabla EMPLE: SQL> SELECT MAX(APELLIDO) FROM EMPLE; MAX(APELLI ---------TOVAR Obtén el apellido del empleado que tiene mayor salario: SQL> SELECT APELLIDO, SALARIO FROM EMPLE WHERE SALARIO=(SELECT MAX(SALARIO) FROM EMPLE); APELLIDO ---------REY

SALARIO ---------4100

Se necesita una subconsulta para calcular el máximo salario y compararlo después con el salario de cada uno de los empleados de la tabla EMPLE. MIN(expresión). Obtén el mínimo salario de la tabla EMPLE: SQL> SELECT MIN(SALARIO) FROM EMPLE; MIN(SALARIO) -----------1040 Obtén el apellido del empleado que tiene mínimo salario: SQL> SELECT APELLIDO, SALARIO FROM EMPLE WHERE SALARIO=(SELECT MIN(SALARIO) FROM EMPLE); APELLIDO ---------SANCHEZ

SALARIO ---------1040

En primer lugar, se selecciona el salario mínimo y, a continuación, se compara con los salarios de la tabla EMPLE para obtener el apellido que tiene ese salario mínimo. SUM(expresión). Consigue la suma de todos los salarios de la tabla EMPLE: SQL> SELECT SUM(SALARIO) FROM EMPLE; SUM(SALARIO) -----------30460 VARIANCE(expresión). Obtén la varianza de todos los salarios de la tabla EMPLE: SQL> SELECT VARIANCE(SALARIO) FROM EMPLE; VARIANCE(SALARIO) ----------------872226,374

81

Unidad 4

15/2/06

13:33

Página 82

4. Funciones 4.2 Funciones aritméticas

DISTINCT en funciones de grupo En todas las funciones de grupo, al indicar los argumentos se pueden emplear las cláusulas DISTINCT y ALL, aunque no se suelen utilizar en las funciones AVG, SUM, MAX ni MIN, pero sí es más normal su uso en COUNT. Recordemos que DISTINCT realiza una selección de filas cuyos valores en la columna especificada no están duplicados. La cláusula ALL recoge todas las filas aunque sus valores estén duplicados. El formato de COUNT incluyendo DISTINCT y ALL es éste: COUNT ( * | [DISTINCT | ALL]

expresión)

Si COUNT recibe como argumento una expresión o columna, ésta podrá ir precedida de las cláusulas ALL o DISTINCT.

Caso práctico

3

Calcula el número de oficios que hay en la tabla EMPLE: SQL> SELECT

COUNT(OFICIO) "OFICIOS"

FROM EMPLE;

OFICIOS -------14 Esta consulta cuenta todos los oficios de la tabla EMPLE que no sean nulos, estén repetidos o no. Si queremos contar los distintos oficios que hay en la tabla EMPLE, tendríamos que incluir DISTINCT en la función de grupo: SQL> SELECT COUNT(DISTINCT OFICIO) "OFICIOS" FROM EMPLE; OFICIOS -------5 DISTINCT obliga a COUNT a contar sólo el número de oficios distintos.

Actividades propuestas

2

A partir de la tabla EMPLE, visualiza cuántos apellidos empiezan por la letra 'A'. Obtén el apellido o apellidos de empleados que empiecen por la letra 'A' y que tengan máximo salario (de los que empiezan por la letra 'A').

82

4. Funciones 4.2 Funciones aritméticas

C. Funciones de listas Las funciones de listas trabajan sobre un grupo de columnas dentro de una misma fila. Comparan los valores de cada una de las columnas en el interior de una fila para obtener el mayor o el menor valor de la lista. Las funciones de listas se muestran en la Tabla 4.3. Función

Propósito

GREATEST (valor1, valor2, ...) LEAST (valor1, valor2, ...)

Obtiene el mayor valor de la lista. Obtiene el menor valor de la lista.

Tabla 4.3. Funciones de listas.

Caso práctico

4

Sea la tabla NOTAS_ALUMNOS: SQL> DESC NOTAS_ALUMNOS; Nombre ---------------NOMBRE_ALUMNO NOTA1 NOTA2 NOTA3

¿Nulo? ---------NOT NULL

Tipo -----------VARCHAR2(25) NUMBER(2) NUMBER(2) NUMBER(2)

Obtén por cada alumno la mayor nota y la menor nota de las tres que tiene: SQL>SELECT NOMBRE_ALUMNO,GREATEST(NOTA1,NOTA2,NOTA3)"MAYOR",LEAST(NOTA1,NOTA2, NOTA3)"MENOR" FROM NOTAS_ALUMNOS; NOMBRE_ALUMNO ------------------------Alcalde García, M. Luisa Benito Martín, Luis Casas Martínez, Manuel Corregidor Sánchez, Ana Díaz Sánchez, Maria

MAYOR ---------5 8 7 9

MENOR ---------5 6 5 6

La última fila tiene un resultado de NULL, debido a que GREATEST y LEAST no pueden comparar un valor con otro valor nulo. Estas funciones se pueden usar también con columnas de caracteres. Ejemplos: Obtén el mayor nombre alfabético de la lista: SQL> SELECT GREATEST('BENITO','JORGE','ANDRES','ISABEL') FROM DUAL; GREAT ----JORGE

83

4. Funciones 4.3 Funciones de cadenas de caracteres

4.3 Funciones de cadenas de caracteres Las funciones de cadenas de caracteres trabajan con datos de tipo CHAR o VARCHAR2. Estos datos incluyen cualquier carácter alfanumérico: letras, números y caracteres especiales. Los literales se deben encerrar entre comillas simples. Ejemplo de una cadena de caracteres: 'El Quijote'. Las funciones de cadenas permiten manipular cadenas de letras u otros caracteres. Estas funciones pueden calcular el número de caracteres de una cadena, convertir una cadena a mayúsculas o a minúsculas, suprimir o añadir caracteres a la izquierda o a la derecha, etcétera.

A. Funciones que devuelven valores carácter Estas funciones devuelven un carácter o un conjunto de caracteres; son un ejemplo las funciones que devuelven una cadena en mayúscula o una cadena en minúscula, o las que obtienen parte de una cadena. Se muestran en la Tabla 4.4.

Función CHR (n) CONCAT (cad1, cad2) LOWER (cad) UPPER (cad) INITCAP (cad) LPAD (cad1, n [, cad2])

RPAD (cad1, n [, cad2])

LTRIM (cad [, set])

RTRIM (cad [, set])

REPLACE (cad, cadena_búsqueda [,cadena_sustitución]) SUBSTR (cad, m [,n])

TRANSLATE (cad1, cad2, cad3)

Propósito Devuelve el carácter cuyo valor en binario es equivalente a 'n'. Devuelve 'cad1' concatenada con 'cad2'. Es equivalente al operador ||. Devuelve la cadena 'cad' con todas sus letras convertidas a minúsculas. Devuelve la cadena 'cad' con todas sus letras convertidas a mayúsculas. Convierte la cadena 'cad' a tipo título, la primera letra de cada palabra de 'cad' a mayúsculas y el resto, a minúsculas. Esta función añade caracteres a la izquierda de 'cad1' hasta que alcance una cierta longitud 'n'. Devuelve 'cad1' con longitud 'n' y ajustado a la derecha; 'cad2' es la cadena con la que se rellena por la izquierda; cad1 puede ser una columna de una tabla o cualquier literal. Si 'cad2' se suprime, asume como carácter de relleno el blanco. Añade caracteres a la derecha de 'cad1' hasta que alcance una cierta longitud 'n'. Devuelve 'cad1' con longitud 'n' y ajustado a la izquierda; 'cad2' es la cadena con la que se rellena por la derecha; 'cad1' puede ser una columna de una tabla o cualquier literal. Si 'cad2' se suprime, asume como carácter de relleno el blanco. Suprime un conjunto de caracteres a la izquierda de la cadena 'cad'; 'set' es el conjunto de caracteres a suprimir. Devuelve 'cad' con el grupo de caracteres 'set' omitidos por la izquierda. Si el segundo parámetro se omite, devuelve la misma cadena. Por defecto, si la cadena contiene blancos a la izquierda y se omite el segundo parámetro, la función devuelve la cadena sin blancos a la izquierda. Suprime un conjunto de caracteres a la derecha de la cadena 'cad'. Devuelve 'cad' con el grupo de caracteres 'set' omitidos por la derecha. Si se omite 'set', devuelve 'cad' tal como está. Por defecto, si la cadena contiene blancos a la derecha y se omite el segundo parámetro, la función devuelve la cadena sin blancos a la derecha. Sustituye un carácter o varios caracteres de una cadena con 0 o más caracteres. Devuelve 'cad' con cada ocurrencia de 'cadena_búsqueda' sustituida por 'cadena_sustitución'. Obtiene parte de una cadena. Devuelve la subcadena de 'cad', que abarca desde la posición indicada en 'm' hasta tantos caracteres como indique el número 'n'. Si se omite 'n', devuelve la cadena desde la posición especificada por 'm'. El valor de 'n' no puede ser inferior a 1. El valor de 'm' puede ser negativo; en ese caso, devuelve la cadena empezando por su final, y avanzando de derecha a izquierda. Convierte caracteres de una cadena en caracteres diferentes, según un plan de sustitución marcado por el usuario. Devuelve 'cad1' con los caracteres encontrados en 'cad2' y sustituidos por los caracteres de 'cad3'. Cualquier carácter que no esté en la cadena 'cad2' permanece como estaba.

Tabla 4.4. Funciones que devuelven factores carácter.

84

Unidad 4

15/2/06

13:33

Página 85

4. Funciones 4.3 Funciones de cadenas de caracteres

Caso práctico 5

CHR(n). Devuelve las letras cuyo valor ASCII es 75 y 65: SQL> SELECT C K

CHR(75), CHR(65) FROM DUAL;

C A

CONCAT(cad1, cad2). Obtén el apellido de los empleados de la tabla EMPLE de la siguiente manera: El apellido es: APELLIDO. Para ello necesitamos concatenar la cadena 'El apellido es: ' con la columna APELLIDO de la tabla EMPLE: SQL> SELECT CONCAT('El apellido es: ', APELLIDO) FROM EMPLE; CONCAT('ELAPELLIDOES:',APE -------------------------El apellido es: SANCHEZ El apellido es: ARROYO El apellido es: SALA El apellido es: JIMENEZ El apellido es: MARTIN El apellido es: NEGRO El apellido es: CEREZO El apellido es: GIL El apellido es: REY El apellido es: TOVAR El apellido es: ALONSO El apellido es: JIMENO El apellido es: FERNANDEZ El apellido es: MUÑOZ 14 filas seleccionadas. LOWER(cad), UPPER(cad) e INITCAP(cad). Visualiza en mayúsculas, minúsculas y tipo título la cadena: 'oRACle Y sqL': SQL> SELECT LOWER('oRACle Y sqL') "minuscula", UPPER('oRACle Y sqL') "MAYUSCULA", INITCAP('oRACle Y sqL') "Tipo Titulo" FROM DUAL; minuscula -----------oracle y sql

MAYUSCULA -----------ORACLE Y SQL

Tipo Titulo -----------Oracle Y Sql

LPAD(cad1, n [, cad2]) y RPAD(cad1, n [, cad2]). Para cada fila de la tabla NOTAS_ALUMNOS, obtenemos en una columna el nombre del alumno con una longitud de 30 caracteres y rellenando por la izquierda con puntos y en otra columna lo mismo pero rellenando por la derecha: SQL> SELECT LPAD(NOMBRE_ALUMNO,30,'.') "IZQUIERDA", RPAD(NOMBRE_ALUMNO,30,'.') "DERECHA" FROM NOTAS_ALUMNOS; IZQUIERDA -----------------------------......Alcalde García, M. Luisa

DERECHA -----------------------------Alcalde García, M. Luisa...... (Continúa)

85

4. Funciones 4.3 Funciones de cadenas de caracteres

(Continuación) ...........Benito Martín, Luis ........Casas Martínez, Manuel .......Corregidor Sánchez, Ana ...........Díaz Sánchez, Maria

Benito Martín, Luis........... Casas Martínez, Manuel........ Corregidor Sánchez, Ana....... Díaz Sánchez, Maria...........

LTRIM(cad [, set]) y RTRIM(cad [, set]). Usamos las funciones LTRIM y RTRIM sin el segundo parámetro y con una cadena con blancos a la izquierda y a la derecha que, por defecto, eliminará: SQL> SELECT LTRIM('

hola') || RTRIM('

adios

')||'*' FROM DUAL;

LTRIM('HOLA')||R ---------------hola adios* A partir de la tabla MISTEXTOS: SQL> SELECT * FROM MISTEXTOS; TITULO ------------------------------METODOLOGÍA DE LA PROGRAMACIÓN. "INFORMÁTICA BÁSICA." SISTEMAS OPERATIVOS SISTEMAS DIGITALES. "MANUAL DE C."

AUTOR ----------------ALCALDE GARCÍA GARCÍA GARCERÁN GARCÍA ESTRUCH RUÍZ LOPEZ RUÍZ LOPEZ

EDITORIAL --------------MCGRAWHILL PARANINFO OBSBORNE PRENTICE HALL MCGRAWHILL

PAGINA --------140 130 300 190 340

Quitamos los caracteres punto y comilla de la derecha de la columna TITULO: SQL> SELECT RTRIM (TITULO, '."' ) FROM MISTEXTOS; RTRIM(TITULO,'."') -------------------------------METODOLOGÍA DE LA PROGRAMACIÓN "INFORMÁTICA BÁSICA SISTEMAS OPERATIVOS SISTEMAS DIGITALES "MANUAL DE C Quitamos las comillas de la izquierda de la columna TITULO: SQL> SELECT LTRIM (TITULO, '"' ) FROM MISTEXTOS; LTRIM(TITULO,'"') -------------------------------METODOLOGÍA DE LA PROGRAMACIÓN. INFORMÁTICA BÁSICA." SISTEMAS OPERATIVOS SISTEMAS DIGITALES. MANUAL DE C."

REPLACE(cad, cadena_búsqueda [,cadena_sustitución]). Sustituimos 'O' por 'AS' en la cadena 'BLANCO Y NEGRO': SQL> SELECT

REPLACE('BLANCO Y NEGRO', 'O', 'AS')

FROM

DUAL; (Continúa)

86

Unidad 4

15/2/06

13:33

Página 87

4. Funciones 4.3 Funciones de cadenas de caracteres

(Continuación) REPLACE('BLANCOY ---------------BLANCAS Y NEGRAS Si no ponemos nada en la cadena_sustitución, se sustituiría la cadena_búsqueda por nada (NULL). En el siguiente ejemplo, sustituimos 'O' por nada: SQL> SELECT

REPLACE('BLANCO Y NEGRO', 'O') FROM DUAL;

REPLACE('BLA -----------BLANC Y NEGR SUBSTR(cad, m [,n]). Partiendo de la cadena 'ABCDEF', obtenemos en una columna dos caracteres a partir de la tercera posición, en otra columna otros dos caracteres a partir de la tercera posición empezando por el final de la cadena y en una última columna la cadena a partir de su cuarta posición: SQL> SELECT SUBSTR('ABCDEFG',3,2) SUBSTR('ABCDEFG',4) "s3" FROM DUAL; s1 -CD

s2 -EF

"s1",

SUBSTR

('ABCDEFG',-3,2)

"s2",

s3 ---DEFG

Visualiza el apellido y su primera letra para los empleados del departamento 10 de la tabla EMPLE: SQL> SELECT APELLIDO, SUBSTR(APELLIDO,1,1) FROM EMPLE WHERE DEPT_NO =10; APELLIDO ---------CEREZO REY MUÑOZ

S C R M

TRANSLATE(cad1, cad2 , cad3). A partir de la cadena 'LOS PILARES DE LA TIERRA', sustituye la 'A' por 'a', la 'E' por 'e', la 'I' por 'i', la 'O' por 'o', y la 'U' por 'u': SQL> SELECT TRANSLATE('LOS PILARES DE LA TIERRA','AEIOU','aeiou') FROM DUAL; TRANSLATE('LOSPILARESDEL -----------------------LoS PiLaReS De La TieRRa En este ejemplo TRANSLATE sustituye la 'L' por 'l': SQL> SELECT TRANSLATE('LOS PILARES DE LA TIERRA','LAEIOU','l') FROM DUAL; TRANSLATE('LOSP --------------lS PlRS D l TRR Ahora la cadena "cad2" está formada por 'LAEIOU', pero en la cadena "cad3" sólo existe 'l', lo que hace que desaparezcan las vocales en la cadena resultante; ya que sólo la 'L' se reemplaza por 'l'; el resto, 'AEIOU', se reemplaza por nada.

87

4. Funciones 4.3 Funciones de cadenas de caracteres

B. Funciones que devuelven valores numéricos Estas funciones devuelven valores numéricos, como el número de caracteres que tiene una cadena o la posición en la que se encuentra un determinado carácter en una cadena. Se trata de las siguientes mostradas en la Tabla 4.5. Función ASCII(cad) INSTR(cad1,cad2 [,comienzo [,m] ])

LENGTH(cad)

Propósito Devuelve el valor ASCII de la primera letra de la cadena 'cad'. Esta función busca un conjunto de caracteres en una cadena. Devuelve la posición de la 'm_ésima' ocurrencia de 'cad2' en 'cad1', empezando la búsqueda en la posición 'comienzo'. Por omisión, empieza buscando en la posición 1. Devuelve el número de caracteres de 'cad'.

Tabla 4.5. Funciones que devuelven valores numéricos.

Caso práctico

6

ASCII(cad). Obtén el valor ASCII de 'A': SQL> SELECT

ASCII ('A')

FROM

DUAL;

ASCII('A') ---------65 Si ponemos como argumento una cadena de caracteres, visualiza sólo el valor ASCII del primer carácter de la cadena. INSTR(cad1, cad2 [,comienzo [,m] ] ). A partir de la cadena 'II VUELTA CICLISTA A TALAVERA' encuentra la segunda ocurrencia 'TA' desde la posición 3: SQL> SELECT INSTR('II VUELTA CICLISTA A TALAVERA', 'TA', 3, 2) "EJEMPLO" FROM DUAL; EJEMPLO ---------17 Cuando «comienzo» es negativo (-1), se comienza la búsqueda en la posición final y se va de derecha a izquierda en la cadena. Devuelve la posición contando desde la izquierda, es decir, la primera A que encuentra desde la primera posición empezando por el final: SQL> SELECT INSTR('II VUELTA CICLISTA A TALAVERA', 'A', -1) "EJEMPLO" FROM

DUAL;

EJEMPLO ---------29 (Continúa)

88

Unidad 4

15/2/06

13:33

Página 89

4. Funciones 4.4 Funciones para el manejo de fechas

(Continuación)

Si en el ejemplo anterior empleamos un –2, haría que la función comenzase desde la segunda posición empezando por el final; un –3 haría que se iniciase desde la tercera posición y, así, sucesivamente. A partir de la tabla MISTEXTOS, encuentra la posición de la segunda ocurrencia de la letra 'A' en la columna AUTOR a partir del comienzo: SQL> SELECT AUTOR, INSTR(AUTOR,'A',1,2) FROM MISTEXTOS; AUTOR ---------------------ALCALDE GARCÍA GARCÍA GARCERÁN GARCÍA STRUCH RUÍZ LOPEZ RUÍZ LOPEZ

INSTR(AUTOR,'A',1,2) -------------------4 6 6 0 0

Si en la función INSTR, "cad2" es un conjunto de caracteres, entonces la función devuelve la posición donde comienza el primer carácter de ese conjunto. LENGTH(cad). Calcula el número de caracteres de las columnas TITULO y AUTOR para todas las filas de la tabla MISTEXTOS: SQL> SELECT TITULO, LENGTH(TITULO), AUTOR, LENGTH(AUTOR) FROM MISTEXTOS; TITULO LENGTH(TITULO) AUTOR LENGTH(AUTOR) ------------------------------- -------------- ---------------- ------------METODOLOGÍA DE LA PROGRAMACIÓN. 31 ALCALDE GARCÍA 14 "INFORMÁTICA BÁSICA." 21 GARCÍA GARCERÁN 15 SISTEMAS OPERATIVOS 19 GARCÍA STRUCH 13 SISTEMAS DIGITALES. 19 RUÍZ LOPEZ 10 "MANUAL DE C." 14 RUÍZ LOPEZ 10

4.4 Funciones para el manejo de fechas Oracle puede almacenar datos de tipo fecha (DATE) y posee una interesante utilidad para formatear las fechas de cualquier manera que se pueda concebir. Tiene un formato por omisión: 'DD/MM/YY', pero con la función TO_CHAR es posible mostrar las fechas de cualquier modo. Los literales de fecha deben encerrarse entre comillas simples. Ejemplo: '18/11/05'. Recordemos que el tipo de datos DATE se almacena en un formato especial que incluye Siglo/Año/Mes/Día/Hora/Minutos/Segundos. Las funciones para el manejo de fechas se exponen en la Tabla 4.6.

89

Unidad 4

15/2/06

13:33

Página 90

4. Funciones 4.4 Funciones para el manejo de fechas

Función

Propósito

SYSDATE ADD_MONTHS(fecha, n) LAST_DAY(fecha) MONTHS_BETWEEN (fecha1, fecha2) NEXT_DAY(fecha, cad)

Devuelve la fecha del sistema. Devuelve la fecha 'fecha' incrementada en 'n' meses. Devuelve la fecha del último día del mes que contiene 'fecha'. Devuelve la diferencia en meses entre las fechas 'fecha1' y 'fecha2'. Devuelve la fecha del primer día de la semana indicado por 'cad' después de la fecha indicada por 'fecha'. El día de la semana en 'cad' se indica con su nombre, es decir, lunes (monday), martes (tuesday), miércoles (wednesday), jueves (thursday), viernes (friday), sábado (saturday) o domingo (sunday).

Tabla 4.6. Funciones para el manejo de fechas.

Caso práctico

7

SYSDATE. Esta función devuelve la fecha del sistema. Por ejemplo: SQL> SELECT SYSDATE FROM DUAL; SYSDATE -------03/08/05 ADD_MONTHS(fecha, n). A partir de la tabla EMPLE, suma doce meses a la fecha de alta para los empleados del departamento 10: SQL> SELECT FECHA_ALT, ADD_MONTHS(FECHA_ALT, 12) FECHA_AL -------09/06/91 17/11/91 23/01/92

FROM EMPLE WHERE DEPT_NO=10;

ADD_MONT -------09/06/92 17/11/92 23/01/93

LAST_DAY(fecha). Obtén de la tabla EMPLE el último día del mes para cada una de las fechas de alta de los empleados del departamento 10: SQL> SELECT FECHA_ALT, LAST_DAY(FECHA_ALT) FROM EMPLE WHERE DEPT_NO=10; FECHA_AL -------09/06/91 17/11/91 23/01/92

LAST_DAY -------30/06/91 30/11/91 31/01/92 (Continúa)

90

Unidad 4

15/2/06

13:33

Página 91

4. Funciones 4.5 Funciones de conversión

(Continuación)

MONTHS_BETWEEN(fecha1, fecha2). Cálculo de la edad: necesitamos la función "SYSDATE", que devuelve la fecha actual (fecha del sistema) y calculamos los meses transcurridos entre la fecha de hoy y la fecha de nacimiento. Dividimos entre 12 ese resultado y aplicamos la función TRUNC para suprimir decimales: SQL> SELECT TRUNC (MONTHS_BETWEEN (SYSDATE,'18/11/1964') / 12) "Edad actual" FROM DUAL; Edad actual ----------40 NEXT_DAY(fecha, cad). Si hoy es jueves 19 de octubre de 2006 (fecha del sistema «sysdate»), ¿qué fecha será el próximo jueves? SQL> SELECT NEXT_DAY(SYSDATE, 'JUEVES')

"Sig. Jueves"

FROM

DUAL;

Sig. Jue -------26/10/06

4.5 Funciones de conversión La mayoría de las funciones que hemos visto hasta ahora son funciones de transformación, esto es, cambian los objetos. Hay otras funciones que cambian los objetos de una manera especial, pues transforman un tipo de dato en otro. Las funciones de conversión elementales se muestran en la Tabla 4.7. La Tabla 4.8 muestra las máscaras de formato para las fechas y la Tabla 4.9 muestra las máscaras de formatos numéricos. Función

Propósito

TO_CHAR (fecha, ‘formato’)

Convierte una _fecha_ (de tipo DATE) a tipo _VARCHAR2_ en el _formato_ especificado. El _formato_ es un cadena de caracteres que puede incluir las máscaras de formato definidas en la Tabla de control de formato de fechas (Tabla 4.8), y donde es posible incluir literales definidos por el usuario encerrados entre comillas dobles. Esta función convierte un 'número' (de tipo NUMBER) a tipo VARCHAR2 en el 'formato' especificado. Los formatos numéricos se muestran en la Tabla 4.9. Convierte 'cad', de tipo VARCHAR2 o CHAR, a un valor de tipo DATE según el 'formato' especificado. Convierte la 'cadena' a tipo NUMBER según el 'formato' especificado. La cadena ha de contener números, el carácter decimal o el signo menos a la izquierda. No puede haber espacios entre los números, ni otros caracteres.

TO_CHAR (numero, ‘formato’) TO_DATE (cad, ‘formato’) TO_NUMBER (cadena [,’formato’])

Tabla 4.7. Funciones de conversión.

91

4. Funciones 4.5 Funciones de conversión

Máscaras de formato numéricas cc o scc y, yyy ó sy,yyy yyyy yyy yy y q ww w mm ddd dd d hh ó hh12 hh24 mi ss sssss j

Valor del siglo Año con coma, con o sin signo Año sin signo Últimos tres dígitos del año Últimos dos dígitos del año Último dígito del año Número del trimestre Número de semana del año Número de semana del mes Número de mes Número de día del año Número de día del mes Número de día de la semana Hora (1-12) Hora (1-24) Minutos Segundos Segundos transcurridos desde medianoche Juliano

Máscaras de formato de caracteres syear ó year month mon day dy a.m. o p.m. b.c. o a.d.

Año en inglés (ej: nineteen-eighty-two) Nombre del mes (ENERO) Abreviatura de tres letras del nombre del mes (ENE) Nombre del día de la semana (LUNES) Abreviatura de tres letras del nombre del día (LUN) Muestra a.m. ó p.m. dependiendo del momento del día Indicador para el año (antes de Cristo o después de Cristo)

Tabla 4.8. Máscaras de control de formatos de fechas.

Caso práctico 8

A partir de la tabla EMPLE, obtén la fecha de alta (columna FECHA_ALT) formateada, de manera que aparezca el nombre del mes con todas sus letras (month), el número de día de mes (dd) y el año (yyyy), para aquellos empleados del departamento 10: SQL> SELECT TO_CHAR (FECHA_ALT, 'month DD, YYYY') "NUEVA FECHA" FROM EMPLE WHERE DEPT_NO=10; NUEVA FECHA ------------------junio 09, 1991 noviembre 17, 1991 enero 23, 1992 Hay que hacer una observación: el nombre del mes aparece en minúscula porque en el formato se definió month en minúscula, pero también puede aparecer en mayúscula o con la primera letra mayúscula y las siguientes en minúsculas. Las opciones para month son las siguientes: si el nombre de mes es Enero, Month produce Enero, month produce enero, MONTH produce ENERO. Lo mismo ocurre con day y dy. (Continúa)

92

4. Funciones 4.5 Funciones de conversión

(Continuación)

Ahora se desea obtener la fecha de alta de forma que aparezca el nombre del mes con tres letras (mon), el número de día del año (ddd), el último dígito del año (y) y los tres últimos dígitos del año (yyy): SQL> SELECT TO_CHAR (FECHA_ALT, 'mon ddd y yyy') "FECHA" FROM EMPLE WHERE DEPT_NO=10; FECHA ------------jun 160 1 991 nov 321 1 991 ene 023 2 992

Por defecto, el formato para la fecha viene definido por el parámetro NLS_TERRITORY, que especifica el idioma para el formato de la fecha, los separadores de miles, el signo decimal y el símbolo de la moneda. Este parámetro se inicializa al arrancar Oracle. Para el idioma español, el valor de este parámetro es: NLS_TERRITORY=SPAIN. Podemos cambiar el valor por omisión para la fecha con el parámetro NLS_DATE_FORMAT, usando la orden ALTER SESSION. Por ejemplo, para cambiar el formato de la fecha y que aparezca de la siguiente manera: día/nombre mes/ año hora:minutos:segundos utilizaremos la siguiente sentencia: SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD/month/YYYY HH24:MI:SS'; Sesión modificada. SQL> SELECT SYSDATE FROM DUAL; SYSDATE --------------------------03/agosto /2005 17:20:48 También podemos cambiar el lenguaje utilizado para nombrar los meses y los días con el parámetro NLS_DATE_LANGUAGE.

Caso práctico 9

Por defecto, al iniciar nuestra sesión el idioma definido para la fecha es el español. Podemos definir otro idioma, por ejemplo, el francés, de la siguiente manera: SQL> ALTER SESSION SET NLS_DATE_LANGUAGE=French; Si queremos visualizar el nombre del mes y el día de la semana, éstos aparecerán en francés, con lo que la fecha de hoy nos quedará así: SQL> SELECT TO_CHAR(sysdate,'"Hoy es " day "," dd " de " month " de " yyyy') "Fecha" FROM DUAL; Fecha ----------------------------------------------Hoy es mercredi , 03 de août de 2005

93

4. Funciones 4.5 Funciones de conversión

Elemento 9

Ejemplo 999

Descripción Devuelve el valor con el número especificado de dígitos. Si es positivo, deja un espacio. Devuelve el valor con el número especificado de dígitos con el signo menos si es negativo. Si el valor tiene ceros a la izquierda, los deja en blanco, excepto si el valor es 0. SQL> SELECT TO_CHAR(1,’999’), TO_CHAR(-1,’999’), TO_CHAR(01,’999’) , TO_CHAR(0,’999’) FROM DUAL; TO_C TO_C TO_C TO_C ———— ———— ———— ———— 1 -1 1 0

0

9990 999

9990 - Muestra un 0 si el valor es 0. 0999 - Devuelve el valor dejando ceros al principio. SQL> SELECT TO_CHAR(10,’0999’), TO_CHAR(10,’9990’) ,TO_CHAR(10,’990090’) FROM DUAL; TO_CH TO_CH TO_CHAR ————- ————- ——————0010 10 0010

$

$9999

Devuelve el valor con el signo dólar a la izquierda. SQL> SELECT TO_CHAR(10,’$9999’),TO_CHAR(10,’$009’), TO_CHAR(10,’99$’) FROM DUAL; TO_CHA TO_CH —————— ————— $10 $010

B

B999

TO_C ———— $10

Muestra un espacio en blanco si el valor es 0. Es el formato por omisión. SQL> SELECT TO_CHAR(0,’B999’), TO_CHAR(5,’B999’) FROM DUAL; TO_C TO_C ———— ———— 5

MI

999MI

Si el número es negativo, el signo menos sigue al número. Por omisión, el signo se pone a la izquierda. SQL> SELECT TO_CHAR(-55,’999MI’), TO_CHAR (55, ’999MI’) FROM DUAL; TO_C ———— 55-

S

S999

'S' representa el signo. Devuelve el valor con el signo '+' si el valor es positivo o con el signo '_' si es negativo.

999S

SQL> SELECT TO_CHAR(-55,’999S’), TO_CHAR(-55, ’S999’), TO_CHAR(55,’S999’), TO_CHAR(55,’999S’) FROM DUAL; TO_C ———— 55-

PR

TO_C ———— 55

9999PR

TO_C TO_C TO_C ———— ———— ———— -55 +55 55+

Los números negativos se muestran entre estos símbolos: < >. SQL> SELECT TO_CHAR(-55,’9999PR’), TO_CHAR(55, ’9999PR’) FROM DUAL; TO_CHA ——————

D

99D99

TO_CHA —————— 55

Devuelve el carácter decimal en la posición especificada. SQL> SELECT TO_CHAR(34.55,’99D99’) FROM DUAL; TO_CHA —————— 34,55

Tabla 4.9. Tabla de formatos numéricos.

94

4. Funciones 4.5 Funciones de conversión

Elemento G

Ejemplo 9G999

Descripción Devuelve el carácter de grupo (carácter de los miles) en la posición especificada. SQL> SELECT TO_CHAR(1234,’9G999’) FROM DUAL; TO_CHA —————— 1.234 SQL> SELECT TO_CHAR(123456.98, ‘999G999D99’) FROM DUAL; TO_CHAR(123 ——————————123.456,98

C

C999

Devuelve el símbolo ISO del territorio en la posición especificada. SQL> SELECT TO_CHAR(123,’C999’) ”ISO” FROM DUAL; ISO —————— EUR123

L

, (coma)

L999 999L

Devuelve el símbolo de la moneda local en la posición indicada.

9,999

Devuelve la coma en la posición especificada (carácter de los miles).

SQL> SELECT TO_CHAR(123,’L999’) “MONEDA” FROM DUAL; MONEDA ——————— € 123 SQL> SELECT TO_CHAR(1234,’9,999’) FROM DUAL; TO_CHA ——— 1,234

. (punto)

99.99

Devuelve el punto decimal en la posición especificada. SQL> SELECT TO_CHAR(12.34,’99.99’) FROM DUAL; TO_CHA —————— 12.34 SQL> SELECT TO_CHAR(12345.67,’99,999.99’) FROM DUAL; TO_CHAR(12 —————————— 12,345.67

V

999V99

Devuelve el valor multiplicado por 10n, donde 'n' es el número de nueves después 'V'. SQL> SELECT TO_CHAR(123.45,’999V99’), TO_CHAR (123,’999V99’) FROM DUAL; TO_CHA TO_CHA —————— —————— 12345 12300

EEEE

9.9EEEE

Devuelve el valor usando notación científica. SQL> SELECT TO_CHAR(12345,’9.9EEEE’) FROM DUAL; TO_CHAR(1 ————1.2E+04

RN

RN

rn

FM

Devuelve el valor en números romanos. 'RN' devuelve el valor en mayúsculas y 'rn', en minúsculas. SQL> SELECT TO_CHAR(12,’RN’) , TO_CHAR(12,’rn’) FROM DUAL; TO_CHAR(12,’RN’ TO_CHAR(12,’RN’ ——————————————— ——————————————— XII xii

FM90.9

Devuelve el valor alineado a la izquierda. SQL>SELECT TO_CHAR(12.8,’FM90.9’),TO_CHAR(12, ’FM99’), TO_CHAR(-12,’FM99’) FROM DUAL; TO_CH TO_ TO_ ————— ——— ——— 12.8 12 -12

Tabla 4.9 (Continuación). Tabla de formatos numéricos.

95

Unidad 4

15/2/06

13:33

Página 96

4. Funciones 4.5 Funciones de conversión

Los caracteres devueltos en algunos de estos formatos se especifican inicializando una serie de parámetros. Estos parámetros se muestran en la Tabla 4.10. Elemento

Elemento

NLS_NUMERIC_ CHARACTERS

D, G

NLS_ISO_CURRENCY

C

NLS_CURRENCY

L

Descripción Define los caracteres decimal (‘D’) y separador de los miles (‘G’). Formato: NLS_NUMERIC_CHARACTERS= 'DG' ____D: carácter decimal. ____G: separador de miles. Ejemplo:____NLS_NUMERIC_CHARACTERS= ',.' Carácter decimal, la coma, y separador de miles, el punto. Especifica el símbolo del territorio. Para Espaıa el símbolo es 'ESP'. Especifica el símbolo de la moneda local. Para España es '€'.

Tabla 4.10. Parámetros NLS.

Para cambiar el valor de estos parámetros se utiliza la orden ALTER SESSION. Supongamos, por ejemplo, que queremos definir como carácter de los miles el asterisco (*) y como carácter decimal la barra (/). Hemos de usar el parámetro NLS_NUMERIC_CHARACTERS con ALTER SESSION: SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS='/*'; Sesión modificada. SQL> SELECT TO_CHAR(12345.67,'999G999D999') FROM DUAL; TO_CHAR(1234 -----------12*345/670 Los valores para el carácter decimal y de los miles permanecerán hasta que el usuario finalice la sesión o hasta que el usuario aplique de nuevo la orden ALTER SESSION para cambiar estos caracteres: SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS=',.'; Si queremos cambiar el símbolo de la moneda, de manera que, en lugar de '€' aparezca 'Pesetas', usamos el parámetro NLS_CURRENCY. Ejemplo: SQL> ALTER SESSION SET NLS_CURRENCY='PESETAS'; Sesión modificada. SQL> SELECT TO_CHAR(123,'L999') FROM DUAL; TO_CHAR(123,'L -------------PESETAS123

96

4. Funciones 4.5 Funciones de conversión

Caso práctico 10

TO_NUMBER(cadena [,'formato']). Suponemos que el carácter decimal es la coma y el carácter separador de los miles, el punto. SQL> SELECT TO_NUMBER('-123456') "NUMERO1", TO_NUMBER('123,99','999D99') "NUMERO2" FROM DUAL; NUMERO1 ---------123456

NUMERO2 --------123,99

SQL> SELECT TO_NUMBER('123.456','999G999') "NO CONVIERTE" FROM DUAL; NO CONVIERTE -----------123456 Este ejemplo no convierte porque la cadena '123.456' contiene el carácter que define el separador de los miles (en este ejemplo, el punto), y una cadena válida ha de contener el carácter decimal (en este caso la coma). TO_DATE( cad , 'formato'). Cambia el formato de la fecha para que aparezca el año con cuatro dígitos: SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY'; Convertir una cadena a tipo DATE: SQL> SELECT TO_DATE('01012006') FROM DUAL; TO_DATE('0 ---------01/01/2006 Cuando en la orden TO_DATE no se indica el formato, una cadena de caracteres será convertida a fecha sólo si está en el formato que tenga la fecha del sistema. En el siguiente ejemplo no se convierte la cadena a tipo fecha porque no está en el formato 'DDMMYYYY' definido en la sesión para la fecha: SQL> SELECT TO_DATE('010106') FROM DUAL; SELECT TO_DATE('010106') FROM DUAL * ERROR en línea 1: ORA-01861: el literal no coincide con la cadena de formato Lo correcto sería: SQL> SELECT TO_DATE('01012006') FROM DUAL; Obtén el nombre del mes a partir de la cadena '01012007' (antes hay que convertir la cadena a tipo fecha): SQL> SELECT TO_CHAR(TO_DATE ('01012007', 'ddmmyyyy'), 'Month') "MES" FROM DUAL; MES ---------Enero TO_DATE y TO_CHAR son similares; la diferencia que los separa estriba en que TO_DATE convierte una cadena de caracteres en una fecha y TO_CHAR convierte una fecha en una cadena de caracteres. Ambas pueden utilizar las máscaras de formato de fechas.

97

4. Funciones 4.6 Otras funciones

4.6 Otras funciones A continuación, se exponen otras funciones que, por sus características, no se han incluido en los grupos anteriores, aunque no por ello dejan de ser útiles.

DECODE (var, val1, cod1, val2, cod2..., valor_por_defecto) Esta función sustituye un valor por otro. Si «var» es igual a cualquier valor de la lista («val1», «val2»...), devuelve el correspondiente código («cod1», «cod2»...). En caso contrario se obtiene el valor señalado como valor por defecto («valor_por_defecto»); «val» debe ser un dato del mismo tipo de «var». Ésta es una función IF - THEN - ELSE.

VSIZE (expresión) Devuelve el número de bytes que ocupa expresión. Si expresión es nulo, la función devuelve nulo.

DUMP (cadena[, formato [, comienzo[, longitud] ] ]) Esta función visualiza el valor de «cadena», que puede ser un literal o una expresión, en formato de datos interno, en ASCII, octal, decimal, hexadecimal o en formato de carácter. Por defecto, el formato es ASCII o EBCDIC, lo que depende de la máquina. El argumento «formato» puede tener los siguientes valores: 8 10 16 17

devuelve el resultado en octal. devuelve el resultado en decimal. devuelve el resultado en hexadecimal. devuelve el resultado en formato carácter (ASCII o EBCDIC).

«Comienzo» es la posición de inicio de la cadena y «longitud» es el número de caracteres que se van a visualizar.

USER Esta función devuelve el nombre del usuario actual.

UID Devuelve el identificador del usuario actual. Al crear un usuario, Oracle le asigna un número. Este número identifica a cada usuario y es único en la base de datos. Aunque USER, UID y SYSDATE se han incluido entre las funciones, realmente son pseudocolumnas. Una pseudocolumna es una 'columna' que devuelve un valor al seleccionarla, pero que no es una columna actual de una tabla.

98

4. Funciones 4.6 Otras funciones

Caso práctico 11

Sea la tabla EMPLE. Seleccionar todas las filas y codificar el OFICIO. Si el oficio es PRESIDENTE, codificar con un 1; si es EMPLEADO, con un 2; en cualquier otro caso, codificar con un 5: SQL> SELECT APELLIDO, OFICIO, DECODE(UPPER(OFICIO), 'PRESIDENTE', 1, 'EMPLEADO', 2, 5) "Codigo" FROM EMPLE; APELLIDO ---------SANCHEZ ARROYO SALA JIMENEZ MARTIN NEGRO CEREZO GIL REY TOVAR ALONSO JIMENO FERNANDEZ MUÑOZ

OFICIO ---------EMPLEADO VENDEDOR VENDEDOR DIRECTOR VENDEDOR DIRECTOR DIRECTOR ANALISTA PRESIDENTE VENDEDOR EMPLEADO EMPLEADO ANALISTA EMPLEADO

Codigo ---------2 5 5 5 5 5 5 5 1 5 2 2 5 2

14 filas seleccionadas. Calculamos el número de bytes que tiene la columna APELLIDO de la tabla EMPLE para aquellos empleados del departamento 10: SQL> SELECT APELLIDO, VSIZE(APELLIDO) BYTES FROM EMPLE WHERE DEPT_NO=10; APELLIDO --------CEREZO REY MUÑOZ

BYTES ------6 3 5

Representamos en hexadecimal los caracteres 1 al 4 del APELLIDO 'SALA' de la tabla EMPLE: SQL> SELECT APELLIDO, DUMP(APELLIDO,16,1,4) FROM EMPLE WHERE APELLIDO LIKE 'SALA'; APELLIDO ---------SALA

DUMP(APELLIDO,16,1,4) ------------------------Typ=1 Len=4: 53,41,4c,41

Visualiza el usuario que está conectado y su identificador: SQL> SELECT USER, UID FROM DUAL; USER ------SCOTT

UID -----57

La orden SHOW USER muestra el nombre de usuario que está conectado: SQL> SHOW USER

99

4. Funciones Conceptos básicos

Conceptos básicos

Las funciones vistas en la unidad se resumen en el siguiente esquema:

De valores simples

Aritméticas De grupos de valores

FUNCIONES

De listas

De cadenas

Devuelven valores carácter

Devuelven valores numéricos

CHR(n), CONCAT(cad1, cad2) LOWER(cad),UPPER (cad) INITCAP(cad), LPAD(cad1, n [, cad2]), RPAD(cad1, n [, cad2]), LTRIM(cad [, set]), RTRIM(cad [, set]), REPLACE(cad, cadena_búsqueda [,cadena_sustitución]), SUBSTR(cad, m [,n]), TRANSLATE(cad1,cad2, cad3) ASCII(cad), INSTR(cad1,cad2 [,comienzo [,m] ]), LENGTH(cad)

Manejo de fechas

SYSDATE , ADD_MONTHS(fecha, n) LAST_DAY(fecha) MONTHS_BETWEEN(fecha1, fecha2) NEXT_DAY(fecha, cad)

De conversión

TO_CHAR (fecha, ‘formato’) TO_CHAR (numero, ‘formato’) TO_DATE (cad, ‘formato’) TO_NUMBER( cadena [,’formato’])

Otras

100

ABS(n), CEIL(n), FLOOR(n), MOD(m, n), NVL(valor, expresión), POWER(m, exponente), ROUND(número [,m]), SIGN(valor), SQRT(n), TRUNC(número, [m]) AVG(n), COUNT (* | expresión), MAX(expresión), MIN(expresión), SUM(expresión), VARIANCE (expresión) GREATEST(valor1, valor2..) LEAST (valor1, valor2...)

DECODE(var,val1,cod1,val2,cod2,.., valor_por_defecto) VSIZE (expresión), DUMP(cadena[, formato [, comienzo[, longitud]]]), USER, UID

4. Funciones Actividades complementarias

Actividades complementarias

1

Dada la tabla EMPLE, obtén el sueldo medio, el número de comisiones no nulas, el máximo sueldo y el mínimo sueldo de los empleados del departamento 30. Emplea el formato adecuado para la salida para las cantidades numéricas.

2

Visualiza los temas con mayor número de ejemplares de la tabla LIBRERIA y que tengan, al menos, una 'E' (pueden ser un tema o varios).

9

Dada la tabla NACIMIENTOS, realiza una sentencia SELECT que obtenga la siguiente salida: NOMBRE, FECHANAC, FECHA_FORMATEADA, donde FECHA_FORMATEADA tiene el siguiente formato: "Nació el 12 de mayo de 1982".

10

Dada la tabla LIBRERIA, haz una sentencia SELECT que visualice el tema, el último carácter del tema que no sea blanco y el número de caracteres de tema (sin contar los blancos de la derecha) ordenados por tema.

RESULTADO -------------------------------------METODOLOGÍA DE LA PROGRAMACIÓN-^-^-^-^INFORMÁTICA BÁSICA-^-^-^-^-^-^-^-^-^SISTEMAS OPERATIVOS-^-^-^-^-^-^-^-^-^SISTEMAS DIGITALES-^-^-^-^-^-^-^-^-^MANUAL DE C-^-^-^-^-^-^-^-^-^-^-^-^-^-

11

A partir de la tabla NACIMIENTOS, visualiza en una columna el NOMBRE seguido de su fecha de nacimiento formateada (quita blancos del nombre).

12

Convierte la cadena '010712' a fecha y visualiza su nombre de mes en mayúsculas.

4

Visualiza los títulos de la tabla MISTEXTOS sin los caracteres punto y comillas, y en minúscula, de dos formas conocidas.

13

Visualiza aquellos temas de la tabla LIBRERIA cuyos ejemplares sean 7 con el nombre de tema de 'SEVEN'; el resto de temas que no tengan 7 ejemplares se visualizarán como están.

5

Dada la tabla LIBROS, escribe la sentencia SELECT que visualice dos columnas, una con el AUTOR y otra con el apellido del autor.

14

A partir de la tabla EMPLE, obtén el apellido de los empleados que lleven más de 15 años trabajando.

3

Dada la tabla MISTEXTOS, ¿qué sentencia SELECT se debe ejecutar para tener este resultado?

6

Escribe la sentencia SELECT que visualice las columnas de AUTOR y otra columna con el nombre del autor (sin el apellido) de la tabla LIBROS.

15

Selecciona el apellido de los empleados de la tabla EMPLE que lleven más de 16 años trabajando en el departamento 'VENTAS'.

7

A partir de la tabla LIBROS, realiza una sentencia SELECT que visualice en una columna, primero el nombre del autor y, luego, su apellido.

16

Visualiza el apellido, el salario y el número de departamento de aquellos empleados de la tabla EMPLE cuyo salario sea el mayor de su departamento.

8

A partir de la tabla LIBROS, realiza una sentencia SELECT para que aparezcan los títulos ordenados por su número de caracteres.

17

Visualiza el apellido, el salario y el número de departamento de aquellos empleados de la tabla EMPLE cuyo salario supere a la media en su departamento.

101