Funciones en PL/SQL

La función se puede usar como parte de la expresión SQL, es decir, podemos usarla con los comandos seleccionar/actualizar/combinar. Una de las características más importantes de una función es que, a diferencia de los procedimientos, debe devolver un valor. 

Sintaxis: Crear una función

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name  type [, ...])] 

// this statement  is must for functions 
RETURN return_datatype  
{IS | AS} 

BEGIN 
    // program code

[EXCEPTION
   exception_section;

END [function_name];

Ejemplo:

Tenemos que encontrar la fuerza total de los estudiantes usando funciones presentes en diferentes secciones en una escuela 

// first lets create a table
create table section(s_id int, s_name varchar(20), strength int );
 
// Inserting values into table
insert into section values(1, 'computer science', 20);
insert into section values(2, 'portal', 45);
insert into section values(3, 'geeksforgeeks', 60);
 
// Defining function
create or replace function totalStrength
 
// Defining return type
return integer
as
total integer:=0;
 
begin                       
 
// calculating the sum and storing it in total
select sum(strength) into total from section;
return total;
 
// closing function
end totalStrength;
 
set serveroutput on;
 
declare
answer integer;
 
begin
answer:=totalstrength();
   dbms_output.put_line('Total strength of students is  ' || answer); 
end;

Producción:

Total strength of students is  125

Ahora, tomemos un ejemplo para demostrar cómo declarar, definir e invocar una función PL/SQL simple que calculará y devolverá el reverso de un número. 

set serveroutput on;
declare
  
    a int;
    c int;
    n int;
   rev int:=0;
    r int;
 
// Defining function
function reverse_it( x  IN int)
return  int
as
z int;
 
// function code
begin
    n := x;
         
    while (n > 0)
    loop
        r := mod(n, 10);
        rev := (rev * 10) + r;
        n := trunc(n / 10);
    end loop;
     
    z := rev;
    return z;
     
end  ; 
 
BEGIN 
   a := 123456789;   
    
   c := reverse_it(a); 
   dbms_output.put_line('the reverse of number is  ' || c); 
 
END; 

Producción:

the reverse of number is  987654321

Implementemos una función recursiva para calcular el factorial de un número Ejemplo de funciones recursivas: 

DECLARE 
   num int; 
   answer int; 
 
// Defining function 
FUNCTION factorial(x number) 
RETURN int  
IS 
   f int; 
 
BEGIN 
   IF x = 0 THEN 
      f := 1; 
   ELSE 
      f := x * factorial(x - 1); 
   END IF; 
RETURN f; 
END; 
   
BEGIN 
   num := 5; 
   answer := factorial(num); 
   dbms_output.put_line(' Factorial of  '|| num || ' is ' || answer); 
END; 

Producción:

 Factorial of  5 is 120

El manejo de excepciones se puede hacer usando un bloque de excepción en las funciones, pero no se puede hacer el manejo de excepciones usando un bloque try-catch . Ejemplo: 

set serveroutput on;
 
declare
a int;
b float;
myexp exception;
 
function  sqroot(x int)
return float
 
as
answer float;
 
begin
 
if x < 0 then
raise myexp;
 
// pre-defined sqrt()  to
// calculate square root
else
answer := SQRT(x);
 
end if;
return answer;
 
exception
when myexp then
dbms_output.put_line('square of negative number is 
                     not allowed so returning the same
                     number');
return x;
end;
 
begin
b := sqroot(-2);
dbms_output.put_line('the value is '|| b);
end;

Producción:

square of negative number is  not allowed so returning the same number
the value is -2

ventajas:

1. Podemos hacer una sola llamada a la base de datos para ejecutar un bloque de declaraciones, por lo que mejora el rendimiento al ejecutar SQL varias veces. Esto reducirá el número de llamadas entre la base de datos y la aplicación.
2. Podemos dividir el trabajo general en pequeños módulos, lo que se vuelve bastante manejable y también mejora la legibilidad del código.
3. Promueve la reutilización.
4. Es seguro ya que el código permanece dentro de la base de datos, por lo que oculta los detalles internos de la base de datos de la aplicación (usuario). El usuario solo realiza una llamada a las funciones PL/SQL. Por lo tanto, la seguridad y la ocultación de datos están garantizadas.

Sugerencia: recuerde también lo que significa descartar una función. Entonces, para eliminar una función, se usa la declaración de función DROP. 

Sintaxis: declaración de la función DROP

DROP Function <function_name>;

Ejemplo:

DROP Function func1;