Is it possible to use return in stored procedure

Question

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  lt   in IP AND   DYNAMIC IP HIGH  gt   in IP AND TERMINAL ID   in Tid   if outcount   1 then  return 1   else  return 0    end if   end if   END     Is it possible to use return in stored procedure like above   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  lt   in IP AND DYNAMIC IP HIGH  gt   in IP AND TERMINAL ID   in Tid      if outcount   1 then       outretvalue  1      else      outretvalue  0     end if  end if  END

User · Answer

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

User · Answer

Use FUNCTION   CREATE OR REPLACE FUNCTION test function RETURN VARCHAR2 IS  BEGIN   RETURN  This is being returned from a function   END test function

User · Answer

-- 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  https   stackoverflow com a 9484228 1661078

User · Answer

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

User · Answer

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

[oracle] Is it possible to use "return" in stored procedure?

Examples related to oracle

Examples related to stored-procedures

Examples related to plsql

Examples related to oracle10g

Examples related to oracle11g