What is the main purpose of RETURN in functions?

What is the main purpose of RETURN in functions when we can calculate a value and pass it through using OUT mode in procedure or functions?

Questions by sudnic

Showing Answers 1 - 6 of 6 Answers


  • Jul 10th, 2012

Function will return the value where procedure can not .
lv_msg := function( IN OUt parameters); -- this can be called from another procedure and scope of the lv_msg will be till the procedure end .

lv_msg:= procedure( In OUT parameters); -- this will be the error and the scope will be till the called procedure end

  Was this answer useful?  Yes

- Function always returns a value but procedure may or may not
- Procedure will return a value by using OUT parameter
- If i want to create some calculation with the table columns on run time, we can do this by only functions
- Procedures cant be use for this kind of things, column level calculations cant be use in procedures

The RETURN statement immediately ends the execution of a subprogram and returns control to the caller. Execution continues with the statement following the subprogram call. (Do not confuse the RETURN statement with the RETURN clause in a function spec, which specifies the datatype of the return value.)

A subprogram can contain several RETURN statements. The subprogram does not have to conclude with a RETURN statement. Executing any RETURN statement completes the subprogram immediately.

In procedures, a RETURN statement does not return a value and so cannot contain an expression. The statement returns control to the caller before the end of the procedure.

In functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier, which acts like a variable of the type specified in the RETURN clause. Observe how the function balance returns the balance of a specified bank account:

create or replace procedure proc1 as
dbms_output.put_line(Before Return Clause);
dbms_output.put_line(After Return Clause);

set serveroutput on;
dbms_output.put_line(In Calling program);

This code shows the use of return statement in procedures.In procedure proc1 After Return Clause will never get printed as control will transfer the control
to the caller.So the output will be:

Before Return Clause
In Calling program
PL/SQL procedure successfully completed.

  Was this answer useful?  Yes

Deepika S Verma

  • Mar 14th, 2014

Its a myth that function returns value whereas procedure does not, actually procedure also return value (i.e. 0 or 1 ) at the time of exiting which indicates the success or error. Whereas Function returns the static value(value gets after code processing). But the main concept is procedures return value is used by oracle for internal use and functions value can be used by coder.

The basic purpose of functions is to compute and return the value whereas procedure is for processing. To enhance the purpose of function oracle introduce few concepts which only a function can perform, like -

(1) We cannot call procedure in projection list of select statement because it does not return value.(UDF Functions)
(2) We can use functions as table (i.e. using function name in place of table), Mostly we call it pipelined functions.

Out parameters are used for connecting the processing between procedures, Thats why structured language ask coder to ignore the use of OUT parameters in functions.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions