Why procedure can not be used in Select Query??

Hi all,
We can use function in Select Query , but Procedure can not?
What is the reason?

Kiran Marla

 

Hi Kiran,

This is because Function must return a value, while procedure may or may not. Also function can be used as an assignment statement, while Procedure can never be used as an assignment.

So, function's return output can be selected from DUAL table, provided it must obey the purity level rules.
Procedure can always be invoked as a procedural statement only.

 

Hi SBH,

Appreciation for reply. I am bit confused, Can you explain it clearly

Kiran Marla

 

Hi Kiran,

As you must be aware that function and Procedures are named PL/SQL blocks, with the difference that function must ALWAYS return a logical value, while procedure may or may not. If procedure has to return, it should be through OUT parameter.

Now if a Functions returns single value, it would be same as substituting the value in SELECt query as a literal. For example,

Code (SQL):

SQL> SELECT 8 NUM FROM DUAL;

NUM
------
8

Now, if I have a function with return value as 8 as below.

Code (SQL):

CREATE OR REPLACE FUNCTION FUNTEST RETURN NUMBER IS
BEGIN
RETURN 8;
END;

Now, functions are always used as Assignment statement. So selecting the function from dual, would be same as selecting the result from dual. Selecting the above function from dual, would display 8.

Code (SQL):

SQL> SELECT FUNTEST NUM FROM DUAL;

NUM
------
8

Instead of function, if i had a procedure which returned 8, it would through OUT parameter. And procedure cannot be used as assignment statement also. For executing procedure, I must have the environment variables also, to capture the OUT parameters. So there is no point in having them in SELECT statement. Therefore, Procedures will always be used as procedural statement.

Hope this clarifies your confusion !!

 

Hi,
Clears my confusion a little, I need to read for several times to digest it.
Thanks SBH for your kind cooperation

Kiran Kumar