Funciones en oracle

Una función es un conjunto de instrucciones en PL/SQL, que pueden ser llamados usando el nombre con que se le haya creado. Se diferencian de los procedimientos, en que las funciones retornan un valor al ambiende desde donde fueron llamadas.
La sintaxis para crear una función es la siguiente:
CREATE [OR REPLACE] FUNCTION name [(param [IN] datatype) . . .]
RETURN datatype
[IS|AS] pl/sql_subprogram


El uso de OR REPLACE permite sobreescribir una función existente. Si se omite, y la función ya existe, se producirá, un error. El unico modificador permitido para los parámetros es IN, y si se omite, se tomará por defecto. Es decir, solo se permiten parámetros de entrada.


A continuación se presenta un ejemplo de creación de una función:


SQL> CREATE FUNCTION get_bal (acc_no IN NUMBER)
1> RETURN NUMBER
2> IS
3> acc_bal NUMBER(11,2); /* declaración de una variable */
4> BEGIN
5> SELECT balance
6> INTO acc_bal /* asignación */
7> FROM accounts
8> WHERE account_id = acc_no;
9> RETURN(acc_bal);
10> END
La función get_bal retorna el balance de una cuenta dada.
Si se desea eliminar (borrar) una función, se usa la instrucción:
SQL> DROP FUNCTION name;

Secuencias
    ORACLE proporciona los objetos de secuencia para la generación de códigos numericos automáticos. 
    Las secuencias son una solución fácil y elegante al problema de los codigos autogenerados.
    LA sintaxis general es la siguiente:

CREATE SEQUENCE <secuence_name> [MINVALUE <min_val>] [MAXVALUE <max_val>] [START WITH <ini_val>] [INCREMENT BY <inc_val>] [NOCACHE | CACHE <cache_val>][CYCLE] [ORDER];

    El siguiente ejemplo crea una secuencia SQ_PRODUCTOS.

CREATE SEQUENCE SQ_PRODUCTOS MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;

    Se puede simplificar la orden, tomando los valores por defecto. El ejemplo anterior quedaría del siguiente modo:

CREATE SEQUENCE SQ_PRODUCTOS;

    Para obtener el siguiente valor de una secuencia debemos utilizar la función NEXTVAL. NEXTVAL se puede utilizar el cualquier sentencia SQL DML (SELECT, INSERT, UPDATE).

SELECT SQ_PRODUCTOS.NEXTVAL FROM DUAL;

    Podemos obtener el último valor generado por la secuencia con la función CURRVAL. Para poder ejecutar la función CURRVAL debemos haber ejecutado previamente la función NEXTVAL.

SELECT SQ_PRODUCTOS.CURRVAL FROM DUAL;

    Para eliminar una secuencia definitivamente de la base de datos debemos utilizar la sentencia DROP.

DROP SEQUENCE SQ_PRODUCTOS ;

Trabajo de la clase pasada

-- RESPUESTA NUMERO 1 DEL TALLER PBD

CREATE OR REPLACE FUNCTION fnCambiarNombre(pNumCliente CHAR, pNombre VARCHAR2(30)) RETURN VARCHAR
IS
DECLARE
    vNombreAntiguo Cliente.nombre%TYPE;
BEGIN
    SELECT nombre INTO vNombreAntiguo
    FROM Cliente
    WHERE numCliente = pNumCliente;

    UPDATE Cliente
    SET nombre = pNombre;
    WHERE numCliente = pNumCliente;
   
    RETURN vNombreAntiguo;
    EXCEPTION
    WHEN no_data_found THEN
    dbms_output.put_line('Cliente no Encontrado :'||pNumCliente);
END;

-- RESPUESTA NUMERO 2 DEL TALLER PBD

SELECT numCliente, nombre, fnCambiaNombre(‘CR76’, ‘Juan’)
FROM Cliente
WHERE numcliente = ‘CR76’
SELECT fnCambiarNombre('CR76', 'Juancho') FROM dual;
-- este select no es valido, no se puede hacer uno cuando esta funcionando la function
-- este que se hara acontinuacion si es valido
BEGIN
    dbms_output.put_line('Nombre Antiguo : '||fnCambiarNombre('CR76', 'Juancho'));
END;

-- RESPUESTA NUMERO 3 DEL TALLER PBD

CREATE VIEW ArriendoPorCliente AS
SELECT numArriendo, renta, nombre, c.numCliente
FROM Arriendo a, Cliente c
WHERE a.numCliente = c.numCliente;

SELECT * FROM ArriendoPorCliente

-- RESPUESTA NUMERO 4 DEL TALLER PBD

UPDATE ArriendoPorCliente
SET renta = 6500;
WHERE numArriendo = 10024;
-- SI se puede modificar el dato de la tabla creada
UPDATE ArriendoPorCliente
SET renta = 6500, nombre = 'Maria'
WHERE numArriendo = 10024;
-- NO se puede modificar mas de 2 datos de una tabla de union

trigger de ingreso de giro

create or replace trigger trCuentaBancaria
before insert on giro
for each row
declare
vSaldoCuenta Cuenta.saldo%type;
vSaldoLinea LineaCredito.saldo%type;
vIdLinea LineaCredito.idLinea%type;
vLineaUsada LineaCredito.saldo%type;
vTotalDisponible LineaCredito.saldo%type;
NO_HAY_FONDOS EXCEPTION;
err_num NUMBER;
err_msg VARCHAR2(255);
begin
dbms_output.put_line('idGiro : '||:new.idGiro);
dbms_output.put_line('idCuenta : '||:new.idCuenta);
dbms_output.put_line('Fecha : '||:new.fecha);
dbms_output.put_line('Monto : '||:new.monto);

-- Obtenemos el saldo de la cuenta corriente
select saldo into vSaldoCuenta
from Cuenta
where idCuenta = :new.idCuenta;
dbms_output.put_line('Monto Solicitado : '||:new.monto|| ' Saldo en Cuenta Corriente : '|| vSaldoCuenta);



if :new.monto <= vSaldoCuenta then update Cuenta set saldo = saldo - :new.monto where idCuenta = :new.idCuenta; else -- Obtenemos el saldo en la linea de credito select saldo, idLinea into vSaldoLinea, vIdLinea from LineaCredito where idCuenta = :new.idCuenta; vTotalDisponible := vSaldoCuenta + vSaldoLinea; dbms_output.put_line('Monto Solicitado : '||:new.monto|| ' Saldo en Cuenta Corriente : '|| vSaldoCuenta || 'Saldo en linea de Credito : '||vSaldoLinea); dbms_output.put_line('Total Disponible : '||vTotalDisponible); if :new.monto > vTotalDisponible then
-- Si No tenemos suficiente dinero se anula la operación
RAISE NO_HAY_FONDOS;
else
-- Ocupo todo el saldo de la Cuenta Corriente
update Cuenta
set saldo = 0
where idCuenta = :new.idCuenta;

-- y el resto lo obtenemos de la linea de credito
vLineaUsada := :new.monto - vSaldoCuenta;

update LineaCredito
set saldo = saldo - vLineaUsada
where idLinea = vIdLinea;

-- Ahora se inserta un registro en la tabla UsoLinea
insert into UsoLinea (idUso,idLinea,fecha,monto) values(seqUsoLinea.NextVal,vIdLinea, sysdate,vLineaUsada);
end if;
end if;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Cuenta no encontrada *************** : '||:new.idCuenta);
RAISE_APPLICATION_ERROR(-20001, '*** Error *** => Cliente no posee linea de credito y su saldo es insuficiente');
WHEN NO_HAY_FONDOS THEN
RAISE_APPLICATION_ERROR(-20000, '*** Error *** => No hay suficiente dinero para su transacción');
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SQLERRM;
DBMS_OUTPUT.put_line('Error: '||TO_CHAR(err_num));
DBMS_OUTPUT.put_line(err_msg);
END;

cursor que lista la cantidad de empleados por departamentos

declare
cursor curCtaEmpleados is
select DEPARTMENT_ID, count(*)
from employees
group by DEPARTMENT_ID
order by count(*) desc, DEPARTMENT_ID;
vIdDepartamento employees.DEPARTMENT_ID%type;
vTotalEmpleados integer;
vNombreDepartamento departments.department_name%type;
begin
open curCtaEmpleados;
loop
fetch curCtaEmpleados into vIdDepartamento, vTotalEmpleados;
exit when curCtaEmpleados%NOTFOUND;
select department_name into vNombreDepartamento
from departments
where department_id = vIdDepartamento;
-- vNombreDepartamento := 'Ventas';
dbms_output.put_line(vIdDepartamento || ' '||vNombreDepartamento || ' ' ||vTotalEmpleados);
end loop;
close curCtaEmpleados;
end

cuenta pares

declare
contador integer := 0;
x integer := 1;
begin
while x <= 50
loop
if mod(x,2) = 0 then
contador := contador +1;
end if
x := x+1;
end loop;
dbms_output.put_line('Total numeros pares es: ' || contador);
end

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