Si aprendes a usar el estamento RETURN en tus PL-SQL Function en Oracle, podrás controlar el flujo de los procesos dentro de tus funciones. Podrás determinar en que momento se debe retornar un valor para terminar la ejecución de una PL-SQL Function en Oracle y retornar el control al proceso que llamo esta función.
No debes confundir el estamento RETURN con la cláusula RETURN que se especifica cuando se declara una PL-SQL Function en Oracle, ya que esta cláusula permite definir el tipo de dato que debe devolver o retornar una función.
Una PL-SQL Function en Oracle siempre debe tener al menos un estamento RETURN para que retorne un valor y termine la ejecución de esta, de lo contrario, se producirá un error en tiempo de ejecución. También dentro de una PL-SQL Function en Oracle puedes colocar todos los RETURN que desees, pero eso sería una programación muy deficiente, lo ideal es que tengas un RETURN o un máximo de dos.
Revisemos algunos ejemplos prácticos para que entiendas mas claramente como se debe usar el estamento RETURN en tus PL-SQL Function en Oracle.
Ejemplo #1: Supone que debes crear una función que retorne la edad de un empleado, vemos como se hace:
/* PL-SQL Function en Oracle */
FUNCTION Fnc_Edad_Empleado(pin_id_empleado in number) RETURN Number Is
lin_Edad number(5);
Begin
SELECT emp.edad INTO lin_Edad FROM t_empleado emp WHERE emp.id_empleado = pin_id_empleado;
RETURN lin_Edad;
End Fnc_Edad_Empleado;
En este ejemplo se crea la PL-SQL Function en Oracle Fnc_Edad_Empleado para retornar la edad del empleado, fíjate que al definir la función se indica que debe retornar un valor de tipo numérico (RETURN Number Is) y finalmente luego de hacer el SELECT se realiza el RETURN con la edad obtenida.
TÚ REPORTE: “Cómo Construir un SELECT Eficiente en ORACLE en Solo 7 Pasos”
Ejemplo #2: En el ejemplo anterior no se están controlando los errores, si ocurre un error se producirá una caída en tiempo de ejecución, vemos como se debe controlar el error:
/* PL-SQL Function en Oracle */
FUNCTION Fnc_Edad_Empleado(pin_id_empleado in number) RETURN Number Is
lin_Edad number(5);
Begin
SELECT emp.edad INTO lin_Edad FROM t_empleado emp WHERE emp.id_empleado = pin_id_empleado;
RETURN lin_Edad;
EXCEPTION
When Others Then
RETURN (-1);
End Fnc_Edad_Empleado;
En este ejemplo se agregó el estamento EXCEPTION para controlar cualquier error que ocurra y se realiza un RETURN con el valor -1 para indicar que ha ocurrido un error, con este cambio el programa continuará ejecutándose sin generar errores en tiempo ejecución. No debes olvidar de colocar el estamento RETURN, ya que Oracle espera que la función tenga un valor de retorno, de lo contrario, la PL-SQL Function en Oracle producirá un nuevo error en tiempo de ejecución.
Te propongo que pruebes de inmediato estos ejemplos en tu Base de Datos con tus propios PL-SQL Procedures en Oracle y PL-SQL Function en Oracle, para que comiences a ejercitar la mano y aprendas a dominar esta nueva técnica en tus PL-SQL Packages en Oracle.
Estoy muy interesado en conocer tu opinión y/o comentario sobre este artículo, también cuéntame sobre qué es lo que quieres saber de PL-SQL Function en Oracle y/o dime cuál es tu principal frustración a la hora de trabajar con PL-SQL Function en Oracle. Estaré muy atento respondiendo tus preguntas y/o comentarios, gracias.
Saludos
Roberto Vicencio – Escritor del Reporte:
“Cómo Construir un SELECT Eficiente en ORACLE en Solo 7 Pasos”
Bonita la explicacion, pero mi problema es al momento de ejecutar la funcion, naturalmente el valor de retorno es lo que me importa porque es la informacion esa la que necesito, pero como capturo en una variable ese valor, siempre que lo ago en sql me genera un error, por ejemplo pongo @variable = exec procedimiento 7, donde 7 es el parametro que se le manda pero siempre genera un error, entonces la prgunta es como acer para apturar ese valor devuelto por la funcion, dentro de sql, no desde un lenguaje de programacion si no en sql, me explico, gracias
Hola Juan, gracias por comunicarte, me inmagino que lo que quieres conseguir lo estas haciendo por Sql*Plus, en esta herramienta para obtener el valor de retorno de una función debes hacer lo siguiente:
SQL> var tu_variable number; — declarar variable
SQL> exec :tu_variable:= tu_funcion(tu_parametro); — ejecutar la función
SQL> print tu_variable; — imprimir el resultado por pantalla
Recurda que esto solo te sirve para ejecutar una función no para ejecutar un procedimiento.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle
PD: Ya recibiste mi reporte gratuito, si aún no lo has hecho puedes obtenerlo aquí:
https://dominatuspackagesen0racle.com
Hol saludos!! agradeceria tu ayuda!
Tengo una funcion sencilla con este codigo:
create or replace
function fincrementarsueldo(aporciento number, amaxsueldo number)
return number
is
maximo number:=0;
contador number:=0;
begin
select max(sueldo) into maximo from empleados;
while maximo<= amaxsueldo loop
update empleados set sueldo=sueldo+(sueldo*aporciento);
contador:=contador+1;
select max(sueldo) into maximo from empleados;
end loop;
return maximo;
end fincrementarsueldo;
pero al ejecutar la funcion me sale este error:
select fincrementarsueldo(0.05,3000) from dual;
Error SQL: ORA-14551: no se puede realizar una operación DML dentro de una consulta
ORA-06512: en "SYSTEM.FINCREMENTARSUELDO", línea 9
14551. 00000 – "cannot perform a DML operation inside a query "
*Cause: DML operation like insert, update, delete or select-for-update
cannot be performed inside a query or under a PDML slave.
*Action: Ensure that the offending DML operation is not performed or
use an autonomous transaction to perform the DML operation within
the query or PDML slave.
Hola Mabel, gracias por comunicarte y por tu comentario, este error te ocurre porque estas ejecutando la función dentro de un select from dual, para solucionarlo debes usar una sintaxis diferente como la siguiente:
lin_maximo number;
begin lin_maximo:= fincrementarsueldo(0.05,3000); end;
O bien desde sql-pluss:
SQL> var lin_maximo number
SQL> call fincrementarsueldo(0.05,3000) INTO :lin_maximo;
PD: Ya obtuviste mi libro-reporte gratuito sobre “Cómo Construir un SELECT Eficiente en Oracle en solo 7 Pasos”, si aun no lo tienes puedes obtenerlo desde aquí:
https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Puedo colocar un return dentro de in IF?, afecta en el flujo de la funcion o del store, es recomendable o no
Hola Benito, gracias por comunicarte y por tu comentario, un Return generalmente se usa al final de una función para retornar un valor de esta, lo ideal es que el return siempre sea solo 1 máximo 2, puedes usar un return dentro de un IF pero no es recomendable ya que si abusas se pierde el flujo del proceso, lo ideal es que en un IF calcules el valor que se debe retornar en una variable y hacer solo una vez el return, algo como siguiente:
if a > b then
valor:= 1;
else
valor:= 0;
end if;
return valor;
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle
https://dominatuspackagesen0racle.com/
gracias por aportar el conocimiento, mi duda es como puedo recuperar un registro eliminado de una tabla ya que ese registro sirve para recuperar un proceso mal ingresado en otra tabla, agradeceria su ayuda.
Hola Luis, gracias por comunicarte y por tu comentario, si eliminas un registro y aun no has hecho el commit puedes recuperarlo haciendo un rollback, si ya hiciste el commit el registro fue borrado físicamente de la tabla, la única forma de recuperarlo es que tengas un respaldo de la tabla o de la base de datos, gracias.
PD: Si te gusta el contenido del blog comparte este enlace https://dominatuspackagesen0racle.com/ con tus amigos o personas que también les pueda interesar este contenido.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
https://dominatuspackagesen0racle.com/
es posible que una funcion retorne un valor de tipo CLOB?
Hola Eduardo, gracias por comunicarte y por tu comentario, te aconsejo que mejor lo retornes como variable de salida, es decir, variable_clob out clob, asi lo he usado y me ha dado resultado, no lo he usado como return(variable_clob) pero solo habria que probarlo si funciona, gracias.
PD: Ya obtuviste mi libro-reporte gratuito sobre ‘Cómo Construir un SELECT Eficiente en Oracle en Solo 7 Pasos’, si aun no lo tienes puedes obtenerlo desde aquí:
https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Hola amigo, yo quiero saber si esto es posible, esto es un bloque anónimo que tiene una estructura algo asi:
declare
var number(1);
begin
begin
aqui va consulta para devolver 0 o 1
EXCEPTION
WHEN NO_DATA_FOUND THEN
var:=0;
end;
if(var=0) then
RETURN ‘aqui va una subconsulta la cual genera los valores esperados’;
else
RETURN ‘aqui va una subconsulta la cual genera los valores esperados’;
end if;
end;
Mi pregunta es: puedo hacer ese return? que que cuando ejecuto el bloque me dice que el return no puede contener ningún valor. Pero esta consulta se realiza perfectamente el otra aplicacion
Hola Yelireth, gracias por comunicarte, efectivamente no puedes usar un Return con valor dentro de una sentencia Declare, debido a que un Declare solo se ejecuta y no puede ser llamado desde otro proceso que espere ese valor de retorno, es mas el Return con valor solo puede ser usado desde una Function.
PD: Ya pudiste ver mi libro reporte sobre ‘Cómo Crear un SELECT Eficiente en ORACLE en Solo 7 Pasos’, si aun no lo tienes en tus manos puedes obtenerlo gratis desde aquí: https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
No me regresa datos, no se en donde pueda estar el error..
FUNCTION get_mapping (p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_source_loc IN VARCHAR2,
p_dest_loc IN VARCHAR2,
p_ship_type IN VARCHAR2,
p_cost_type IN VARCHAR2,
p_acces_code IN VARCHAR2)
RETURN VARCHAR2
AS
lv_mapping VARCHAR2 (100);
BEGIN
IF p_ship_type IN (‘STO’, ‘SHUTTLE’, ‘OUTBOUND’)
THEN
SELECT SUBSTR (p_attribute2, 1, 1)
|| bolinf.xbol_otm_freight_accr_po_pkg.get_element (
p_attribute2,
2,
‘_’
)
|| SUBSTR (p_attribute3, 1, 1)
|| bolinf.xbol_otm_freight_accr_po_pkg.get_element (
p_source_loc,
3,
‘_’
)
|| p_ship_type
|| SUBSTR (p_cost_type, 1, 1)
|| DECODE (SUBSTR (p_acces_code, 1, 3),
‘FSC’, SUBSTR (p_acces_code, 1, 3),
‘ADD’, SUBSTR (p_acces_code, 1, 3),
p_acces_code)
INTO lv_mapping
FROM DUAL;
RETURN (lv_mapping);
END IF;
IF p_ship_type IN (‘INBOUND’, ‘INBOUND SHUTTLE’)
THEN
SELECT SUBSTR (p_attribute2, 1, 1) || SUBSTR (p_attribute3, 1, 1)
|| bolinf.xbol_otm_freight_accr_po_pkg.get_element (
p_dest_loc,
3,
‘_’
)
|| p_ship_type
|| SUBSTR (p_cost_type, 1, 1)
|| DECODE (SUBSTR (p_acces_code, 1, 3),
‘FSC’, SUBSTR (p_acces_code, 1, 3),
‘ADD’, SUBSTR (p_acces_code, 1, 3),
p_acces_code)
INTO lv_mapping
FROM DUAL;
RETURN (lv_mapping);
END IF;
IF p_ship_type = ‘DEPOT’
THEN
SELECT SUBSTR (p_attribute2, 1, 1) || SUBSTR (p_attribute3, 1, 1)
|| bolinf.xbol_otm_freight_accr_po_pkg.get_element (
p_attribute3,
2,
‘_’
)
|| p_ship_type
|| SUBSTR (p_cost_type, 1, 1)
|| DECODE (SUBSTR (p_acces_code, 1, 3),
‘FSC’, SUBSTR (p_acces_code, 1, 3),
‘ADD’, SUBSTR (p_acces_code, 1, 3),
p_acces_code)
INTO lv_mapping
FROM DUAL;
RETURN (lv_mapping);
END IF;
IF p_ship_type = ‘DROPOFF’
THEN
SELECT SUBSTR (p_attribute2, 1, 1)
|| SUBSTR (p_attribute3, 1, 1)
|| SUBSTR (p_source_loc, 1, 8)
|| p_ship_type
|| SUBSTR (p_cost_type, 1, 1)
|| DECODE (SUBSTR (p_acces_code, 1, 3),
‘FSC’, SUBSTR (p_acces_code, 1, 3),
‘ADD’, SUBSTR (p_acces_code, 1, 3),
p_acces_code)
INTO lv_mapping
FROM DUAL;
RETURN (lv_mapping);
END IF;
IF p_ship_type = ‘DROPSHIP OUTBOUND’
THEN
SELECT SUBSTR (p_attribute2, 1, 1)
|| SUBSTR (p_attribute3, 1, 1)
|| p_ship_type
|| SUBSTR (p_cost_type, 1, 1)
|| DECODE (SUBSTR (p_acces_code, 1, 3),
‘FSC’, SUBSTR (p_acces_code, 1, 3),
‘ADD’, SUBSTR (p_acces_code, 1, 3),
p_acces_code)
INTO lv_mapping
FROM DUAL;
RETURN (lv_mapping);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_mapping := NULL;
RETURN (lv_mapping);
END get_mapping;
Hola Eduardo, gracias por comunicarte, a simple vista el procedure se ve correcto, pero esta ocurriendo un error en los if o select y debe estar saliendo por la exception donde retorna null, puedes poner un valor ahí para saberlo, también te recomiendo hacer un debug o poner banderas con dbms_output.put_line(‘primer select’) por ejemplo.
PD: Quédate atento a tu correo porque estaré enviándote mas contenido, si te ha gustado lo que has visto del blog te invito a compartir el enlace https://dominatuspackagesen0racle.com/cmbl/ con tus amigos o personas que también les pueda interesar ver esta información, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.