can we use more than one function inside one PL/SQL Block

Question

DECLARE
     avg_sal number(8,2);
     c number(8);
     total number(8,2);
     at number(8);
     a number(8);
     rt number(8);
     r number(8);
     y number(8);
     yr number(8);
     c number(8);
     ch number(8);
     FUNCTION cmd_int(amt number,rate number,intrest number)
     RETURN number
     IS
     cint number(8,2);
     BEGIN
     END;

     FUNCTION smp_int(amt number,rate number,intrest number)
     RETURN number
     IS
     sint number(8,2);
     BEGIN
         sint:=(amt*rate*intrest)/100;
         return total/c;
     END;
BEGIN
     dbms_output.put_line('Enter amount :'||:a);
     at:=:a;
     dbms_output.put_line('Enter rate :'||:r);
     rt:=r;
     dbms_output.put_line('Enter year :'||:y);
     yr:=y;
     dbms_output.put_line('1 Compound Intrest');
     dbms_output.put_line('2 Simple Intrest');
     dbms_output.put_line('Enter your choice :'||:c);
     ch:=c;
     CASE 
     WHEN ch:=1 THEN 
        ci:=cmd_int(at,rt,yr);
        dbms_output.put_line('Compound Intrest :'||ci);
     WHEN ch:=2 THEN
        si:=smp_int(at,rt,yr);
        dbms_output.put_line('Simple Intrest :'||si);
END;

The above is the code which I want to execute there are two functions cmd_int and smp_int so can I able to execute more than one function inside one PL/SQL Block? Thank you!

Basically i want to execute 1st part when my choice is 1 and second part when my choice is 2.

Yes - why do you think you can't? You do have a mostake but it's nothing to do with having or calling two functions. How about showing us the error you get? — Alex Poole, Jan 31 '16 at 10:10
@AlexPoole sure! ORA-06550: line 5, column 6: PLS-00103: Encountered the symbol "AT" when expecting one of the following: this is the error. — Kartik Shah, Jan 31 '16 at 10:23
I've addressed that in my answer now too. Why do you think that error is anything to do with the functions? Also PL/SQL isn't designed to be interactive, and your prompting for bind variables isn't likely to work well even in SQL Developer - you'll be asked for the values before seeing the dbms_output 'prompts' . The client should supply the values to the block. — Alex Poole, Jan 31 '16 at 10:26
@AlexPoole yes you are true error has nothing to do with function but can you able to solve the error? because i am not able to understand error — Kartik Shah, Jan 31 '16 at 10:28

Alex Poole · Accepted Answer · 2016-01-31T10:39:23.523

Yes, you can define and call as many functions and procedures as you like (and subblocks - up to a limit anyway). The functionality of PL/SQL would be rather curtailed if you couldn't.

But the rest of the code has to be valid. You're missing an end case:

      ...
     CASE 
     WHEN ch:=1 THEN 
        ci:=cmd_int(at,rt,yr);
        dbms_output.put_line('Compound Intrest :'||ci);
     WHEN ch:=2 THEN
        si:=smp_int(at,rt,yr);
        dbms_output.put_line('Simple Intrest :'||si);
     END CASE;
END;

And AT is a reserved word in PL/SQL so you cannot have a variable with that name, you will need to change it.

You also seem to be confusing PL/SQL local variables (e.g. r) with bind variables (:r), and you're trying to have interaction between the block and the user, which PL/SQL is not designed for. Once the PL/SQL block is valid your client will either complain that not all variables are bound, or will prompt for them all before executing the block, and you will only see the 'prompt' strings from dbms_output after the block has executed.

Your application or client needs to gather all the values outside the PL/SQL block; Michael Schaefers has shown a common method for SQL Developer or SQL*Plus. You can still combine that with a PL/SQL block if that is part of your assignment.

ORA-06550: line 5, column 6: PLS-00103: Encountered the symbol "AT" when expecting one of the following: this is the error i got after making changes — Kartik Shah, Jan 31 '16 at 10:22

score 2 · Answer 2 · edited May 23 '17 at 12:17

To technically answer the question of the headline: yes, you can use more than one function inside a PL/SQL block, as the following example code shows:

set serveroutput on;

DECLARE
     FUNCTION cmd_int(amt number,rate number,intrest number)
     RETURN number
     IS
     cint number(8,2);
     BEGIN
      return 4;
     END;

     FUNCTION smp_int(amt number,rate number,intrest number)
     RETURN number
     IS
     sint number(8,2);
     BEGIN
         return 5;
     END;
BEGIN
     dbms_output.put_line('Result 1: ' || cmd_int(1,2,3));
     dbms_output.put_line('Result 2: ' || smp_int(1,2,3));
END;
/

Executing this block yields to

Result 1: 4
Result 2: 5

Now to your problem:

I suggest you create two separate functions cmd_int and smp_int via CREATE OR REPLACE FUNCTION ... that do, what you want. Since these two are used in the same logical context you can also create a package CREATE PACKAGE INTEREST and define both functions within this package.

Then, to ask for user input and to actally use the functions I suggest you stick to an sqlplus script using the ACCEPT command or to handle everything in your client application (should you have one).

See Oracle Documenation on CREATE FUNCTION, CREATE PACKAGE and sqlplus ACCEPT

The basic Idea of an sqlplus script would be

SET SERVEROUTPUT ON;

ACCEPT a NUMBER PROMPT 'Enter amount: ';
ACCEPT r NUMBER PROMPT 'Enter rate: ';
ACCEPT y NUMBER PROMPT 'Enter year: ';
ACCEPT c NUMBER PROMPT '1 Compount Interest, 2 Simple Interest: ';

SELECT CASE WHEN &&c = 1 THEN cmd_int(a,r,y) ELSE smp_int(a,r,y) END AS Interest FROM DUAL;

More infos about using sqlplus can be found here

score 0 · Answer 3 · answered Jan 31 '16 at 10:12

0

Yes. You can define more functions and procedures within a pl/sql block.

answered Jan 31 '16 at 10:12

Pavel Gatnar

3,987
2
19
29

can we use more than one function inside one PL/SQL Block

3 Answers3