How to use a function within a function

Question

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.

What language is this for, sql?

Nicole Phillips
– Nicole Phillips

2016-11-07 18:44:33 +00:00
Commented Nov 7, 2016 at 18:44 — Nicole Phillips
– Nicole Phillips, Commented Nov 7, 2016 at 18:44
It's in oracle pl/sql

Itsonlyme
– Itsonlyme

2016-11-07 18:46:32 +00:00
Commented Nov 7, 2016 at 18:46 — Itsonlyme
– Itsonlyme, Commented Nov 7, 2016 at 18:46

Aleksej · Accepted Answer · 2016-11-08 06:28:53Z

4

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

edited Nov 8, 2016 at 6:28

answered Nov 7, 2016 at 18:54

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravindra HV · Accepted Answer · 2016-11-07 18:47:55Z

1

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);

answered Nov 7, 2016 at 18:47

Ravindra HV

2,6161 gold badge22 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to use a function within a function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related