6

Please tell me what are the differences between IN,OUT,IN OUT parameters in PL/SQL. And also how can i return more than one values using PL/SQL Procedure.

Burhan Ali
  • 2,258
  • 1
  • 28
  • 38
Weli Nuwan
  • 105
  • 2
  • 2
  • 6
  • 1
    possible duplicate of [What exactly are IN, OUT, IN OUT parameters in PL/SQL](http://stackoverflow.com/questions/32634123/what-exactly-are-in-out-in-out-parameters-in-pl-sql) – user272735 Sep 18 '15 at 04:28

1 Answers1

7

What are IN/OUT/INOUT parameters?

These are parameters you define as part of the function argument list that get returned back as part of the result. When you create functions, the arguments are defaulted to IN parameters when not explicitly specified (which means they are passed in and not returned) which is why you sometimes see PgAdmin do something like IN somevariable variabletype when you use the function wizard.

You can have INOUT parameters as well which are function inputs that both get passed in, can be modified by the function and also get returned.

SQL OUTPUT parameters - return multiple records

--SQL returning multiple records
CREATE OR REPLACE FUNCTION fn_sqltestmulti(param_subject varchar, 
  OUT test_id integer, OUT test_stuff text) RETURNS SETOF record
AS $$
  SELECT test_id, test_stuff 
    FROM testtable where test_stuff LIKE $1;
$$
LANGUAGE 'sql' VOLATILE;

--example
SELECT * FROM fn_sqltestmulti('%stuff%');

 --OUTPUT--
 test_id |     test_stuff
---------+--------------------
       1 | this is more stuff
       2 | this is new stuff

MORE EXAMPLES

waldyr.ar
  • 14,424
  • 6
  • 33
  • 64
  • 5
    I'm not sure how the Postgres community uses the term, but the tags on SO imply that "plsql" is only for Oracle, and "plpgsql" is for Postgres. – Jon Heller Jul 24 '12 at 04:04
  • Even with PostgreSQL, get shows error at `SELECT * FROM fn_sqltestmulti()`... Correcting with something like `SELECT * FROM fn_sqltestmulti() AS x(test_id integer, test_stuff text)`, see [this](http://stackoverflow.com/a/11911949/287948) or [this](http://stackoverflow.com/a/23439305/287948) questions. – Peter Krauss May 05 '14 at 16:51
  • 1
    A note to other readers: The question is about Oracle [tag:plsql], but the answer is about Postgres [tag:plpgsql]. They are similar but different languages. For Oracle see the fine manual: [Subprogram parameters](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS00806). The manual explains the related concepts like formal and actual parameters, parameter passing methods and modes, aliasing, default values and positional, named and mixed notations. – user272735 Sep 18 '15 at 04:20