Funciones SQL

Funciones de valores simples

- ABS(n)= Devuelve el valor absoluto de (n).
- CEIL(n)=Obtiene el valor entero inmediatamente superior o igual a "n".
- FLOOR(n) = Devuelve el valor entero inmediatamente inferior o igual a "n".
- MOD (m, n)= Devuelve el resto resultante de dividir "m" entre "n".
- NVL (valor, expresión)= Sustituye un valor nulo por otro valor.
- POWER (m, exponente)= Calcula la potencia de un numero.
- ROUND (numero [, m])= Redondea números con el numero de dígitos de precisión indicados.
- SIGN (valor)= Indica el signo del "valor".
- SQRT(n)= Devuelve la raíz cuadrada de "n".
- TRUNC (numero, [m])= Trunca números para que tengan una cierta cantidad de dígitos de precisión.
- VAIRANCE (valor)= Devuelve la varianza de un conjunto de valores.

Funciones de grupos de valores:

- AVG(n)= Calcula el valor medio de "n" ignorando los valores nulos.
- COUNT (* | Expresión)= Cuenta el numero de veces que la expresión evalúa algún dato con valor no nulo. La opción "*" cuenta todas las filas seleccionadas.
- MAX (expresión)= Calcula el máximo.
- MIN (expresión)= Calcula el mínimo.
- SUM (expresión)= Obtiene la suma de los valores de la expresión.
- GREATEST (valor1, valor2…)= Obtiene el mayor valor de la lista.
- LEAST (valor1, valor2…)= Obtiene el menor valor de la lista.


Funciones que devuelven valores de caracteres:

- CHR(n) = Devuelve el carácter cuyo valor en binario es equivalente a "n".
- CONCAT (cad1, cad2)= Devuelve "cad1" concatenada con "cad2".
- LOWER (cad)= Devuelve la cadena "cad" en minúsculas.
- UPPER (cad)= Devuelve la cadena "cad" en mayúsculas.
- INITCAP (cad)= Convierte la cadena "cad" a tipo titulo.
- LPAD (cad1, n[,cad2])= Añade caracteres a la izquierda de la cadena hasta que tiene una cierta - longitud.
- RPAD (cad1, n[,cad2])= Añade caracteres a la derecha de la cadena hasta que tiene una cierta - longitud.
- LTRIM (cad [,set])= Suprime un conjunto de caracteres a la izquierda de la cadena.
- RTRIM (cad [,set])= Suprime un conjunto de caracteres a la derecha de la cadena.
- REPLACE (cad, cadena_busqueda [, cadena_sustitucion])= Sustituye un carácter o caracteres de una cadena con 0 o mas caracteres.
- SUBSTR (cad, m [,n])= Obtiene parte de una cadena.
- TRANSLATE (cad1, cad2, cad3)= Convierte caracteres de una cadena en caracteres diferentes, según un plan de sustitución marcado por el usuario.


Funciones para el manejo de fechas:


- SYSDATE= Devuelve la fecha del sistema.
- ADD_MONTHS (fecha, n)= Devuelve la fecha "fecha" incrementada en "n" meses.
- LAST_DAY (fecha)= Devuelve la fecha del último día del mes que contiene "fecha".
- MONTHS_BETWEEN (fecha1, fecha2)= Devuelve la diferencia en meses entre las fechas "fecha1" y "fecha2".
- NEXT_DAY (fecha, cad)= Devuelve la fecha del primer día de la semana indicado por "cad" después de la fecha indicada por "fecha".


Funciones de conversión:

- TO_CHAR= Transforma un tipo DATE ó NUMBER en una cadena de caracteres.
- TO_DATE= Transforma un tipo NUMBER ó CHAR en DATE.
- TO_NUMBER= Transforma una cadena de caracteres en NUMBER.

Desarrollo CursorPruebaUno

DECLARE
cursor crContarPropiedades(countNUMEMPLEADO char) is
SELECT count(NUMPROPIEDAD)
FROM PROPIEDAD
WHERE NUMEMPLEADO = countNUMEMPLEADO
GROUP BY NUMEMPLEADO;

cursor crDatosEmpleados is
SELECT NUMEMPLEADO, NOMBRE, APELLIDO
FROM EMPLEADO;

vNumeroEmpleado EMPLEADO.NUMEMPLEADO%TYPE;
vNombreEmpleado EMPLEADO.NOMBRE%TYPE;
vApellidoEmpleado EMPLEADO.APELLIDO%TYPE;
contador integer := 0;
totalPares integer := 0;

begin
open crDatosEmpleados();
loop
fetch crDatosEmpleados into vNumeroEmpleado, vNombreEmpleado, vApellidoEmpleado;
open crContarPropiedades(vNumeroEmpleado);
loop
fetch crContarPropiedades into contador;
exit when crContarPropiedades %notfound;
end loop;
close crContarPropiedades;

if contador/2 <> 1 then
dbms_output.put_line(vNumeroEmpleado||' '||vNombreEmpleado||' '||vApellidoEmpleado||' '||contador);
else
dbms_output.put_line(vNumeroEmpleado||' '||vNombreEmpleado||' '||vApellidoEmpleado||' '||contador||'*');
totalPares := totalPares+1;
end if;

exit when crDatosEmpleados %notfound;
end loop;

dbms_output.put_line('El total de empleados que manejan una cantidad par de propiedades es : '||totalPares);
close crDatosEmpleados;
END;

TAREA UNO FINAL - CURSOR V.2.0

Desarrolle un cursor, que permita estudiar los empleados (“*Employees*”)
cuyo cargo sea “*Finance Manager*” o “*Programmer*”, generando un registro
en la tabla "*EstudioSueldo*" para cada empleado. En dicha tabla se debe
registrar: el ID del Empleado, Nombre, Apellido, renta, la diferencia con el
promedio y un comentario

Para aquellos empleados que tienen una renta (salary) menor o igual que el
promedio entre el valor min y max, definido en la tabla *jobs* el comentario
debe indicar "*estudiar un aumento*". Para los que tienen una renta
superior al promedio hay que indicar "*evaluar carga de trabajo*" en el
mensaje.

SOLUCION

-PRIMERO BORRAR Y CREAR TABLA "ESTUDIOSUELDO"

drop table estudioSueldo;

create table estudioSueldo(
idEmpleado number(6),
nombre varchar2(20),
apellido varchar2(25),
renta number(8,2),
promedio number(8,2),
diferencia number(8,2),
mensaje varchar2(30));

-SEGUNDO CREAR EL CURSOR QUE INSERTA EN LA TABLA "ESTUDIOSUELDO"

--Inicio del Cursor
DECLARE
CURSOR crEmpleado is
SELECT first_name, last_name, e.job_id, employee_id, salary
FROM employees e, jobs j
WHERE e.job_id=j.job_id
AND (lower(job_title)='finance manager'
OR upper(job_title)='PROGRAMMER');
--variables
vNombre employees.first_name%type;
vApellido employees.last_name%type;
vJob_id employees.job_id%type;
vEmpleado_id employees.employee_id%type;
vSueldo employees.salary%type;
vSueldoMin employees.salary%type;
vSueldoMax employees.salary%type;
vPromedio number(8,2);
vMensaje varchar2(30);
BEGIN
OPEN crEmpleado;
loop
FETCH crEmpleado INTO vNombre, vApellido,
vJob_id, vEmpleado_id, vSueldo;
EXIT WHEN crEmpleado%NOTFOUND;

SELECT min_salary, max_salary into vsueldomin, vsueldomax
FROM jobs
WHERE vjob_id=job_id;

vpromedio:=(vsueldomin + vsueldomax)/2;
IF vsueldo >= vpromedio THEN
vmensaje := 'evaluar carga de trabajo';
ELSE
vmensaje := 'estudiar un aumento';
END IF;

INSERT INTO estudioSueldo (idEmpleado, nombre,
apellido, renta ,promedio, diferencia, mensaje)
VALUES (vEmpleado_id, vNombre, vApellido,
vSueldo, vPromedio, (vSueldo-vPromedio), vMensaje);

end loop;
CLOSE cr_empleado;
END;

-TERCERO MOSTRAR DATOS DE LA TABLA "ESTUDIOSUELDO"

SELECT * FROM estudioSueldo;

Cursor 6/09

Esta clase Declaramos 2 Cursores, uno de ellos recibia el atributo del otro
y ademas le incluimos un contador al segundo cursor
-------------------------------------------------------------------------------------------
DECLARE
CURSOR crListaAtributos(pNombreTabla varchar2) is
SELECT column_name ,DATA_TYPE
FROM all_tab_columns
WHERE table_name = pNombreTabla;

CURSOR crListaTablas is
SELECT distinct tablas.table_name
FROM all_tables tablas
WHERE table_name not like '%$%'
AND upper(owner) not like '%SYS%';
vNombreTabla all_tables.table_name%type;
vAtributoTabla all_tab_columns.table_name%type;
vTipoDato all_tab_columns.DATA_TYPE%type;
contadorAtributos integer;
BEGIN
OPEN crListaTablas;
loop
FETCH crListaTablas into vNombreTabla;
exit when crListaTablas%notfound;
dbms_output.put_line('•••••••••••••••••••••••••••••••••••');
dbms_output.put_line(vNombreTabla);
dbms_output.put_line('------------------------------------------');

OPEN crListaAtributos(vNombreTabla);
contadorAtributos := 0;
loop
FETCH crListaAtributos into vAtributoTabla, vTipoDato;
exit when crListaAtributos%notfound;
dbms_output.put_line(vAtributoTabla||' *'||vTipoDato);
contadorAtributos := (contadorAtributos+1);
end loop;
dbms_output.put_line('------------------------------------------');
dbms_output.put_line('TOTAL ATRIBUTOS :'||contadorAtributos);
CLOSE crListaAtributos;
end loop;
CLOSE crListaTablas;
end;
-------------------------------------------------------------------------------------------

Beta Cursor v1.1

Nos muestra:
-nombre
-apellido
-job_id
-empleado_id
-sueldo
-mensaje
-diferencia(entre promedio y sueldo)
*Solo nos falta modifircar para que ingrese en una nueva tabla.
-------------------------------------------------------------------------------------

declare
cursor cr_empleado is
select first_name, last_name, e.job_id, employee_id, salary, job_title
from employees e, jobs j
where e.job_id=j.job_id
and (lower(job_title)='finance manager'
or upper(job_title)='PROGRAMMER');

--variables
vnombre employees.first_name%type;
vapellido employees.last_name%type;
vjob_id employees.job_id%type;
vjob_title employees.job_title%type;
vempleado_id employees.employee_id%type;
vsueldo employees.salary%type;
vsueldomin employees.salary%type;
vsueldomax employees.salary%type;
vpromedio number(8,2);
vmensaje varchar2(30);
vdif number(8,2);

begin
open cr_empleado;
DBMS_output.put_line('NOMBRE/APELLIDO/TRABAJO_ID/EMPLEADO_ID/SUELDO/SITUACION/DIFERENCIA');
DBMS_output.put_line('------------------------------------------------------------------');

loop
FETCH cr_empleado INTO vnombre, vapellido, vjob_id, vempleado_id,
vsueldo, vjob_title;
exit when cr_empleado%NOTFOUND;
--traer el valor min y max para ver si el promedio coincide
select min_salary, max_salary into vsueldomin, vsueldomax
from jobs
where vjob_id=job_id;

vpromedio:=(vsueldomin + vsueldomax)/2;
--DBMS_output.put_line('SUELDO MINIMO:$'||vsueldomin||' '||'SUELDO MAXIMO :$'||vsueldomax||' '||'PROMEDIO :$'||vpromedio);
if vsueldo >= vpromedio then
vmensaje := 'evaluar carga de trabajo';
else
vmensaje := 'estudiar un aumento';
end if;

vdif := vpromedio - vsueldo;
--OJO ._. revisar enunciado

DBMS_output.put_line(vnombre ||'/'|| vapellido ||'/'|| vjob_id
||'/'|| vempleado_id ||'/'|| vsueldo||'/'|| vmensaje ||'/'|| vdif);

INSERT INTO VALUES();

end loop;
DBMS_output.put_line('------------------------------------------------------------------');
close cr_empleado;
end

problema en grupo a desarrollar en hr de sql10g

desarrollar un cursor en el que junte a todos los empleados que trabajan en el departamento
de nombre "Shipping"
determinar si el empleado es "extrangero" si tabaja fuera de us
y si no señalar que es "nacional"
luego almacenar en una tabla "nacionalidadEmpleados"mostrando los siguentes datos:
nombre, apellido, salario , nacionalidad.

problema individual en hr de oracles10g

Desarrolle un Cursor, que permita Estudiar los empleados
--cuyo departamento estén en el continente de us
--mostrar en orden el orden del trabajador mas antiguo al menos antiguo
--Generando un registro en la tabla "historialTrabajo" para cada empleado
--En dicha tabla se debe grabar:
--el ID del Empleado, su Nombre, el Apellido
--La renta y su localidad y START_DATE

beta cursor

declare
cursor cr_empleado is
select first_name, last_name, e.job_id, employee_id, salary

from employees e, jobs j
where e.job_id=j.job_id
and (lower(job_title)='finance manager'
or upper(job_title)='PROGRAMMER');

vnombre employees.first_name%type;
vapellido employees.last_name%type;
vjob_id employees.job_id%type;
vempleado_id employees.employee_id%type;
vsueldo employees.salary%type;
vsueldomin employees.salary%type;
vsueldomax employees.salary%type;
vpromedio number(8,2);
begin
open cr_empleado;
loop
FETCH cr_empleado INTO vnombre, vapellido, vjob_id, vempleado_id,
vsueldo;
exit when cr_empleado%NOTFOUND;
DBMS_output.put_line(vnombre ||' '|| vapellido ||' '|| vjob_id
||' '|| vempleado_id ||' '|| vsueldo);


select min_salary, max_salary into vsueldomin, vsueldomax
from jobs
where vjob_id=job_id;
vpromedio:=(vsueldomin + vsueldomax)/2;
if vsueldo >= vpromedio then
vmensaje := 'evaluar carga de trabajo';
else
vmensaje := 'estudiar un aumento';
end if;



end loop;
close cr_empleado;
end