madhuri_v123

 Profile Answers by madhuri_v123 Questions by madhuri_v123

• May 15th, 2012

 

RETURN clause in functions is used to return value after executing series of execution steps in a function.

As function can be used in select statement, the output of this statement is nothing but the value RETURN from the function.

dineshv1

• 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

Waseem Mehmood

 Profile Answers by Waseem Mehmood 

• Sep 4th, 2012

 

- 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

shivigupta

 Profile Answers by shivigupta Questions by shivigupta

• Oct 4th, 2012

 

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:

shivigupta

 Profile Answers by shivigupta Questions by shivigupta

• Oct 9th, 2012

 

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

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

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.

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.