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.
An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and the value can be read. The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value.
PL/SQL procedure parameters can have one of three possible modes: IN, OUT, or IN OUT.
Input parameters allow the caller to pass a data value to the stored procedure or function. Output parameters allow the stored procedure to pass a data value or a cursor variable back to the caller. User-defined functions cannot specify output parameters.
An input/output parameter is a parameter that functions as an IN or an OUT parameter or both. The value of the IN/OUT parameter is passed into the stored procedure/function and a new value can be assigned to the parameter and passed out of the module. An IN/OUT parameter must be a variable, not a constant.
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 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With