|
|
|
|
| Re: Why we cannot use procedure in select statement? [message #613076 is a reply to message #613072] |
Tue, 29 April 2014 07:48   |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
aaditya321 wrote on Tue, 29 April 2014 17:56why we cannot use procedure in select statement while function can be used?
Because a function must ALWAYS return a logical value, while procedure may or may not.
You could even use function as an assignment, but cannot do that with procedure. So, procedure can only be invoked purely in a procedural method.
Now you might ask that procedure could also have OUT parameter to return a value, then why can't use it in select just like a function. Since a procedure needs environment variables to hold the OUT parameters, there is no point in using them in select statement. In a function you could simply use RETURN keyword to return the value.
Let's see :
In SQL :
SQL> SELECT 1 val FROM dual;
VAL
----------
1
The same could be acheived with function using return :
SQL> CREATE OR REPLACE FUNCTION f_test
2 RETURN NUMBER
3 AS
4 BEGIN
5 RETURN 1;
6 END;
7 /
Function created
SQL> SELECT f_test val FROM dual;
VAL
----------
1
But, is it possible using a procedure? No, because you need to use a OUT parameter and store it in a variable. So, there is no point in having a procedure in select statement.
[Updated on: Tue, 29 April 2014 07:58] Report message to a moderator |
|
|
|
| Re: Why we cannot use procedure in select statement? [message #613079 is a reply to message #613076] |
Tue, 29 April 2014 08:01 |
 |
sarakhater
Messages: 1
Registered: April 2014
|
Junior Member |
|
|
I would like to clarify that the answer for whether you should use a stored procedure or a function is completely dependent upon your business requirement and design workflow, provided you are clear about your program objective. If you are unclear about your objective, just the way your question is, no amount of coding procedures and functions would be useful.
You must note that stored procedures and functions serve different purposes in PL/SQL programming. These are as follows:
1. Stored procedures:
a. Stored procedures represent named blocks (as opposed to anonymous blocks) that are capable of accepting parameters and work on them.
b. Stored procedures define an independent procedural workflow where you can perform a series of DML and/or other operations.
c. Stored procedures do not have to return a value. Hence, they cannot be called from inside an SQL statement. Stored procedures must be executed from a PL/SQL block- named or anonymous.
d. Merits:
• A procedure does not have to return a value (This can be a demerit too).
• Can be used to perform a series of DML or DDL (yes, this is possible through dynamic SQL with a few restrictions) operations.
• Can be simply called as an independent statement from a PL/SQL block. e.g.,
myProcedure (x, y);
e. Demerits:
• Cannot be called from an SQL query - DML or a SELECT statement.
• Cannot be used in indexes.
2. Functions:
a. Functions are named blocks that are capable of accepting parameters and return a value.
b. Functions also define a procedural workflow but when used in SQL statements, you cannot perform any DML or DDL.
c. A function must be called from a SQL or PL/SQL statement where the value returned by the function is utilized- i.e., assigned to a variable, passed as a parameter, etc.
d. Merits:
• Can be used in an SQL query - DML or a SELECT statement.
• Can be used in function-based indexes if the function is deterministic (meaning for a definite set of inputs the function returns the same output every time it is called).
e. Demerits:
• If the function being called from an SQL query contains any DML, the query fails.
• It is obligatory for a function to return a value. Hence a function call cannot be an independent statement like a procedure call.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Why we cannot use procedure in select statement? [message #613104 is a reply to message #613086] |
Tue, 29 April 2014 23:13 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
For Oracle programmers, this sometimes sounds like a silly question, but it is worth noting that in Microsoft SQL Server, Stored Procedures will "return" (I think) the last executed SQL statement in the procedure. This is probably possible because the same engine serves both SQL and T-SQL.
This causes a bit of tech-shock when Microsoft programmer come over to Oracle.
If I was to hazard a guess, I would say that Oracle has not replicated this functionality because its SQL and PL/SQL engines are separate, thereby requiring a more rigidly defined interface (like functions returning an explicitly defined collection type). I imagine that this would make loosely-coupled interfaces - like Stored Procs returning essentially unstructured data - much more difficult and error-prone to implement.
That's my theory, but the real answer is:
RoachCoach oh-so-rightly said...Because Oracle made it that way
Ross Leishman
|
|
|
|
|
|
|
|
| Re: Why we cannot use procedure in select statement? [message #613275 is a reply to message #613274] |
Fri, 02 May 2014 08:01 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
sss111ind wrote on Fri, 02 May 2014 18:26May be because function must return only single value
Strictly speaking, yes, a function only returns one value. However, depending on your requirement you could use a proper type to return a set of values or you could use a PIPELINE function to return a table of values.
EDIT : Reiterated by previous comment for better clarity.
[Updated on: Fri, 02 May 2014 08:04] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
