This content has been marked as final. Show 11 replies
myself: Yes, It can return multiple values.It's wrong. Oracle PL/SQL functions can't return multiple values.
In one book i find that using functions we can return multiple values but it is poor programming practice.why this is a programming practice ?
What it behind the book? May be, the author meant, that returned variable can contain multiple values. It is really a "poor programming practice".
It was necessary to answe, that the functions can be used both in SQL and PL/SQL. Procedures can be used only in PL/SQL.
Thanks for pointing out that a function may use OUT parameter. I never saw it before.
It is not fair to say that a procedure does return one or more values.
A function ALLWAYS return one value and a procedure return NOTHING.
Both can use IN/OUT parameters. The IN OUT are like pointers in C, and have nothing to do with a "return value", which is what a function returns.
A function can return a collection of multiple values, like a table. This is very usefull !
"Both can use IN/OUT parameters. The IN OUT are like pointers in C, "
OUT and IN OUT Parameter Types are Used by Call by Value fashion,
to make it Call by Reference like pointer u have to use
pls add your suggestion and comments
mail : email@example.com
You are basically correct, although according to the docs NOCOPY is a hint, not a directive, so even with NOCOPY the parameters might be passed by value.
NOCOPY offers us better performance but unhandled exceptions may cause problems by leaving the parameters in an indeterminate state.
On the topic of bad programming practice, I think creating functions with OUT parameters is bad form, but my reasons for believing that are aesthetic or emotional rather than the result of any scientific study.
And although the return value for a function can be a nested table, an array of objects or a ref cursor I would still count them as returning a single value because the function's result has to be assigned to a variable.
Thanks for pointing out that a function may use OUT parameter. I never saw it before.I just noticed a function with an out parameter is not usable in sql.
SQL> var s varchar2(40)
SQL> var i number
SQL> select DBMS_UTILITY.GET_PARAMETER_VALUE ('db_block_size',:i, :s) from dual;
select DBMS_UTILITY.GET_PARAMETER_VALUE ('db_block_size',:i, :s) from dual
ERROR at line 1:
ORA-06572: Function GET_PARAMETER_VALUE has out arguments
In fact, a procedure CANNOT contain an expression in its RETURN statement.
Another difference is function must return something. There is no such restriction on procedure.
And, a procedure cannot be called as part of a expression (it must be a function).
SQL> create or replace procedure test_return is 2 begin 3 return(10) ; 4 end ; 5 / Warning: Procedure created with compilation errors. SQL> show errors Errors for PROCEDURE TEST_RETURN: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/3 PLS-00372: In a procedure, RETURN statement cannot contain an expression 3/3 PL/SQL: Statement ignored SQL>
SQL> create or replace procedure test_return is 2 begin 3 return ; 4 end ; 5 / Procedure created. SQL> variable x number ; SQL> exec :x := test_return ; BEGIN :x := test_return ; END; * ERROR at line 1: ORA-06550: line 1, column 13: PLS-00222: no function with name 'TEST_RETURN' exists in this scope ORA-06550: line 1, column 7: PL/SQL: Statement ignored SQL>