Is it possible to use "return" in stored procedure?

CREATE PROCEDURE Pname(in_Tid IN VARCHAR2,in_IP IN VARCHAR2,outstaticip OUT VARCHAR2,outcount OUT NUMBER)
AS
BEGIN
select STATIC_IP into outstaticip from OP_TTER_MAPPING where TERMINAL_ID = in_Tid;
if in_IP = outstaticip then
return 1;
else
select COUNT(*) into outcount from OP_TTER_MAPPING where DYNAMIC_IP_LOW <= in_IP AND DYNAMIC_IP_HIGH >= in_IP AND TERMINAL_ID = in_Tid;
if outcount = 1 then
return 1;
else
return 0;
end if;
end if;
END;

1. Is it possible to use return in stored procedure like above?
2. If we can use return, how can i get that return value in `Executesql("begin Pname(----)END")` method



**EDIT**

Now I edited my return value in stored procedure like this, am I doing it right ?


CREATE PROCEDURE P_ValidateTIDIP(in_Tid IN VARCHAR2,in_IP IN VARCHAR2,outstaticip OUT VARCHAR2,outcount OUT NUMBER,outretvalue OUT NUMBER)
AS
BEGIN
select STATIC_IP into outstaticip from OP_TTER_MAPPING where TERMINAL_ID = in_Tid;
if in_IP = outstaticip then
outretvalue:=1;
else
select COUNT(*) into outcount from OP_TTER_MAPPING where DYNAMIC_IP_LOW <= in_IP AND DYNAMIC_IP_HIGH >= in_IP AND TERMINAL_ID = in_Tid;
if outcount = 1 then
outretvalue:=1;
else
outretvalue:=0;
end if;
end if;
END;

Use FUNCTION:

CREATE OR REPLACE FUNCTION test_function
RETURN VARCHAR2 IS

BEGIN
RETURN 'This is being returned from a function';
END test_function;

In Stored procedure, you return the values using `OUT` parameter **ONLY**. As you have defined two variables in your example:

outstaticip OUT VARCHAR2, outcount OUT NUMBER

Just assign the return values to the out parameters i.e. `outstaticip` and `outcount` and access them back from calling location. What I mean here is: when you call the stored procedure, **you will be passing those two variables as well. After the stored procedure call, the variables will be populated with return values.**

If you want to have `RETURN value` as return from the PL/SQL call, then use `FUNCTION`. **Please note that in case, you would be able to return only one variable as return variable.**

CREATE PROCEDURE pr_emp(dept_id IN NUMBER,vv_ename out varchar2 )
AS
v_ename emp%rowtype;
CURSOR c_emp IS
SELECT ename
FROM emp where deptno=dept_id;
BEGIN
OPEN c;
loop
FETCH c_emp INTO v_ename;
return v_ename;
vv_ename := v_ename
exit when c_emp%notfound;
end loop;
CLOSE c_emp;


END pr_emp;

<!-- begin snippet: js hide: false -->

<!-- language: lang-sql-->

-- IN arguments : you get them. You can modify them locally but caller won't see it
-- IN OUT arguments: initialized by caller, already have a value, you can modify them and the caller will see it
-- OUT arguments: they're reinitialized by the procedure, the caller will see the final value.
CREATE PROCEDURE f (p IN NUMBER, x IN OUT NUMBER, y OUT NUMBER)
IS
BEGIN
x:=x * p;
y:=4 * p;
END;
/

SET SERVEROUTPUT ON

declare
foo number := 30;
bar number := 0;
begin
f(5,foo,bar);
dbms_output.put_line(foo || ' ' || bar);
end;
/

-- Procedure output can be collected from variables x and y (ans1:= x and ans2:=y) will be: 150 and 20 respectively.

-- Answer borrowed from:

[To see links please register here]

<!-- end snippet -->

It is possible.

When you use Return inside a procedure, the control is transferred to the calling program which calls the procedure. It is like an exit in loops.

It won't return any value.