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