CODEWITHSUNDEEP
oracle-databaseplsql
Itsonlyme
Itsonlyme

Reputation: 109

How to use a function within a function

Now, to be perfectly transparent this is homework. The first part was to create a stored function to calculate the discount price of an item. It accepts one parameter for the Item ID and returns the value of the discount price. The function name is discount_price. This i have done and it works fine.

The next part is: "5. Write a script that creates and calls a stored function named item_total that calculates the total amount of an item in the Order_Items table (discount price multiplied by quantity). To do that, this function should accept one parameter for the item ID, it should use the discount_price function that you created in exercise 2, and it should return the value of the total for that item."

My question is, how do I pass the value of one function into another? I just need the basic syntax. My textbook contains no examples and I can't find a clear answer anywhere.

Upvotes: 1

Views: 418

Answers (2)

Aleksej
Aleksej

Reputation: 22949

You can call a function within a function exactly in the same way you call it from a statement or a query; for example:

create function innerFunction(a number) return number is
begin
    return a * 2;
end;

create function outerFunction(a number) return number is
begin
    return innerFunction(a) * 3;
end;

create function calledFunction(a number) return number is
    n number;
begin
    n := outerFunction(a) * 5;
    return n;
end;

SQL> select calledFunction(1) from dual;

CALLEDFUNCTION(1)
-----------------
               30

SQL> select calledFunction(calledFunction(calledFunction(1))) from dual;

CALLEDFUNCTION(CALLEDFUNCTION(CALLEDFUNCTION(1)))
-------------------------------------------------
                                            27000


SQL> declare
  2  x number;
  3  begin
  4  x := calledFunction(1);
  5  dbms_output.put_line(x);
  6  end;
  7  /
30

Upvotes: 4

Ravindra HV
Ravindra HV

Reputation: 2608

Believe this link has the examples you are looking for. Basically you call it just like how you'd normally call it in sql-plus or sql-developer.

For example :

returl_val := SUBSTR(string_in,4,1);

Upvotes: 1

Related Questions