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.
Login to rate this answer.
dineshv1
Answered On : 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
Login to rate this answer.
- 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
Login to rate this answer.
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:
1 User has rated as useful.
Login to rate this answer.
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.
Login to rate this answer.
