procedure????????

[deepakprusty]

Hi friends,
I hv a doubt in Procedure...Can we use return statement in Procedure??





Thnx in Advance
Deepak

[debasisdas]

If you want to return values from a procedure use OUT parameter.

[amitpatel66]

Hi friends,
I hv a doubt in Procedure...Can we use return statement in Procedure??





Thnx in Advance
Deepak

We cannot use RETURN statement in procedure. RETURN is used in FUNCTIONS.

We can make use of OUT MODE for an input parameter in proceudres to return a value.

Check this sample:
You can modify the value od OUT parameter within the procedure but cannot modify the value of IN parameter.

[code=oracle]

CREATE OR REPLACE PROCEDURE add_num(num1 IN NUMBER, num2 OUT number) AS
BEGIN
num2:= num1 + num2;
END add_num;
/

-- Call the above procedure:

declare
a number := 0;
b number :=0;
BEGIN
a:=10;
b:=10;
add_num(a,b);
DBMS_OUTPUT.PUT _LINE(b);
END;
/

20

PLSQL Procedure successfully completed

[/code]

[subashsavji]

[code=oracle]

Create Or Replace Procedure Factproc( N In Number, A Out Number) Is
Begin
If N=1 Then
A := 1;
Return;
Elsif N=2 Then
A := 2;
Return;
Else
Factproc(n-1,a);
A := A*n;
Return;
End If;
End;
/

Declare
A Number;
Begin
Factproc(2,a);
Dbms_output.put _line(a);
End;
/
Declare
A Number;
Begin
Factproc(1,a);
Dbms_output.put _line(a);
End;
/

Declare
A Number;
Begin
Factproc(5,a);
Dbms_output.put _line(a);
End;
/

[/code]

[rahulkoshti]

U can't use return statement in procedures..
For that purpose you can use OUT parameter type..As

create or replace procedure_name ( variable1 OUT data_type)
---
begin
---
end;

[draji]

Return statement can be used in procedures, but its aim is different. You cannot return a value. Return is used only as an escape statement.

CREATE OR REPLACE PROCEDURE try_return(para m NUMBER)
IS
BEGIN
IF param = 1 THEN
dbms_output.put _line('yes , it is the last I see it ');
RETURN ;
END IF;
dbms_output.put _line(' This should not appear if param is 1 - return');
END;

[debasisdas]

Return is used only as an escape statement.

Then what is the use. If it returns the control before complete complete execution of the code ,why should one use it in a procedure.