Distintos tipos de funciones y ejemplos con SQL para Oracle.

                                                        Conjunto de Funciones: 


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 que devuelven valores numéricos: 

ASCII(cad)= Devuelve el valor ASCII de la primera letra de la cadena "cad". 
INSTR (cad1, cad2 [, comienzo [,m]])= Permite una búsqueda de un conjunto de caracteres en una cadena pero no suprime ningún carácter después. 
LENGTH (cad)= Devuelve el numero de caracteres de cad. 

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.


Ejemplo: 


CREATE OR REPLACE FUNCTION isnumeric (p_string_value IN VARCHAR2)RETURN BOOLEAN
AS

test_value NUMERIC;
BEGIN
BEGIN
test_value := TO_NUMBER (p_string_value);
RETURN (true);

EXCEPTION
WHEN OTHERS THEN
RETURN (false);
END;
END isnumeric;

Clase del 06 de septiembre

En 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;

Cursor por Fabián Moya G.

Desarrollar un cursor que permita investigar a cada Empleado que el inicio de su contrato laboral supere los 7 años de antigüedad detallado en la tabla JOB_HISTORY, ahi que señalar "Años de servicio"donde se generara un registro en la tabla "Antiguedad_Empleado" para cada trabajador . En esta tabla se debe almacenar: ID empleado, ID departamento, salario y la fecha de inicio del contrato y un comentario para aquellos empleados que cumplan con lo solicitado.

Tarea 1 - Diseño de un cursor para estudio de renta de empleados

Solución del cursor :

Antes de declarar el cursor crear la tabla  "estudioSueldo"  :


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



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);
vmensaje varchar2(30);
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);
--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;
if vsueldo >= vpromedio then
vmensaje := 'evaluar carga de trabajo';
else
vmensaje := 'estudiar un aumento';
end if;
end loop;
DBMS_output.put_line(vmensaje);
close cr_empleado;
end

Enunciado cursor

Se desea implementar una base de datos para facilitar la gestión y administración de un cementerio, en dicha base de datos se contemplan diferentes categorías laborales, distintos tipos de enterramiento, facturas por los servicios prestados, incluso se permite que una familia posea su propio panteón para un determinado número de personas.

El cementerio está dividido en sectores, teniendo estos una capacidad y extensión variable que ha de quedar reflejada.

Asimismo se ha quiere tener información sobre los empleados mediante datos personales como nombre y apellidos,dirección, telefóno, salario, antigüedad, etc.

Cursor:

Datos de un Cursor

ceao duoc facebook

centrodealumnosao@gmail.com



declare

_________
_________
_________ variables
_________
_________ cursores

begin
_________
_________
_________
_________

exception
_________
_________
_________
_________
end;


select
from
where
having ( va asociado al group by)
group by
order by


select * from tab



select department_name as "departamento",count(*) as "total empleados"
from employees E,departments D
where E.department_id = D.department_id
having count (employee_id)> 3
group by department_name
order by department_name

desc departments

select * from employees


declare
cursor myCursor is
select department_id, count(*)as "total Empleados"
from employees
group by department_id
order by department_id;
vDepartamento employees.employee_id%type;
vTotalEmpleados integer;
begin
open myCursor;
loop
fetch myCursor into vDepartamento,vTotalEmpleados;
exit when myCursor%notfound;
dbms_output.put_line(vDepartamento || ' ' || vTotalEmpleados);
end loop;
close myCursor;
end

select department_name
from departments
where department_id = 10




declare
vNumDepto integer := 10;
vNomDepto varchar2(30);

begin
select department_name into vNomDepto
from departments
where department_id = vNumDepto;


dbms_output.put_line('el codigo del departamento es : ' || vNumDepto);
dbms_output.put_line('el codigo del departamento es : ' || vNomDepto);
end

cursor 29-ago.2011

permita Estudiar los empleados
--cuyo cargo sea finance_manager o programmer.
--Para aquellos que tienen una renta Menor o igual que el promedio
--entre el valor min y max, definido en la tabla jobs
-- debe indicar "estudiar un aumento".
--Para los que tienen una renta superior al promedio
--hay que indicar "evaluar carga de trabajo".
--Generando un registro en la tabla "estudioSueldo" para cada empleado
--En dicha tabla se debe grabar:
--el ID del Empleado, su Nombre, el Apellido
--La renta, la diferencia con el promedio y el comentario

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);
vmensaje estudioSueldo.mensaje%type;
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);
--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;
if vsueldo >= vpromedio then
vmensaje := 'evaluar carga de trabajo';
else
vmensaje := 'estudiar un aumento';
end if;
end loop;
DBMS_output.put_line(vmensaje);
close cr_empleado;
end

cursor para encontrar los empleados por departamento

DECLARE
CURSOR myCursor is
SELECT DEPARTMENT_ID, count(*)
FROM employees
WHERE DEPARTMENT_ID is not NULL
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;
vDepartamento employees.employee_id%type;
vTotalEmpleados integer;
vNomDepto char(20);
vMensaje varchar2(20);
BEGIN
OPEN myCursor;
DBMS_output.put_line('Departamento Total empleados');
LOOP
FETCH myCursor INTO vDepartamento, vTotalEmpleados;
EXIT WHEN myCursor%NOTFOUND;

SELECT DEPARTMENT_NAME INTO vNomDepto
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = vDepartamento;

IF vTotalEmpleados > 5 THEN
vMensaje := 'Departamento Grande';
ELSE
vMensaje := 'Departamento Chico';
END IF;

DBMS_output.put_line(vNomDepto||''||
vTotalEmpleados||' '||vMensaje);
END LOOP;
CLOSE myCursor;
END