1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Why procedure can not be used in Select Query??

Discussion in 'SQL PL/SQL' started by kiran.marla, Oct 9, 2010.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi all,
    We can use function in Select Query , but Procedure can not?
    What is the reason?

    Kiran Marla
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    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.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi SBH,

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

    Kiran Marla
     
  4. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    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 !!
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi,
    Clears my confusion a little, I need to read for several times to digest it.
    Thanks SBH for your kind cooperation

    Kiran Kumar