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!

Doubt on function and procedure reg

Discussion in 'SQL PL/SQL' started by laxman, Nov 20, 2009.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear sir,
    I have few doubt mentioned below.
    Doubt 1: How to return more than one value in functions

    e.g: create or replace Math( a IN number, b IN number)
    )
    return number IS
    sum number,
    Mult number
    begin
    sum:= a + b;
    mult:= a*b;
    return sum;
    end;

    now my doubt is how to call from pl/sql block and print the value of both calculated value.

    Doubt 2: in case of procedure how we can implement this logic

    Thanks and regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To be honest your example won't compile; this will:

    create or replace function fMath( a IN number, b IN number)
    )
    return number IS
    sum number;
    Mult number;
    begin
    sum:= a + b;
    mult:= a*b;
    return sum;
    end;
    /

    You could return a VARCHAR2:

    create or replace function fMath( a IN number, b IN number)
    )
    return varchar2 IS
    sum number;
    Mult number;
    result varchar2(10);
    begin
    sum:= a + b;
    mult:= a*b;
    result := sum||', '||mult;
    return result;
    end;
    /

    You could also return a ref cursor; I'll leave that for you to write.

    A procedure can return any number of values as OUT parameters; simply declare as many OUT parameters as required to return the values desired,
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Re: Doubt review on function and procedure reg

    Thanks a lot sir for clearing my doubts.
    one small doubt related to this topic that if suppose i want to use OUT parameter in this function to return one of the output,will it work ? if so how will we display the result of sum and mult in calling environment .

    e.g:

    create or replace function fMath( a IN number, b IN number,mult OUT number)
    return number
    IS
    sum number;
    begin
    sum:= a + b;
    mult:= a*b;
    return sum;
    end;

    and if i want to call this function from pl/sql block how can i able to write a logic

    kindly help me in this issues
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Doubt review on function and procedure reg

    Functions return values,mainly from use in SELECT statements. Using OUT parameters in functions prevents using those functions in queries. You should use a PROCEDURE to return multiple values in multiple variables. You CAN return a ref cursor which will return multiple columns ( and multiple rows ) into a single variable from a function; the dbms_xplan.display function is a good example.
     
  5. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear sir,
    In your previous mail,you suggest me to use ref cursor or dbms_xplan.display function ,firstly i beg pardon that i am keep on asking you the same topic ,this time also i expecting a spoon feeding from you,i would be highly greatful if you kindly send me the function code using ref cursor or dbms_xplan.display function also i am unawre of dbms_xplan.display function!!!.

    Kindly do this favour

    Thanks n regards
    Laxman
     
  6. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455