PL/SQL Stored procedure to calculate interest

Procedure and Functions

  • A stored procedure is nothing but a named PL/SQL code block that is compiled and stored in one of the Oracle engine's system tables. It is a logically grouped set of SQL and PL/SQL statements, which performs a specific task.
  • The Functions are same as stored procedure.
  • The main difference between a procedure and a function is, a function must always return a value and the procedure may or may not return value. A function should have explicit return statement.
Q. Write a PL/SQL stored procedure titled as 'COMPOUND_INTR' to calculate the amount of interest on a bank account, which compounds interest yearly. Condition is given as A stored procedure should accept the values of 'p', 'r' and 'y' as parameters and insert the Interest and total amount into temp table.

Answer:

The following formula is used to calculate the interest.   

I = p(1 + r / 100)y

Where,
I) 'I' is the total interest earned.
II) 'p' is principal amount.
III) 'r' is the rate of interest as a decimal less than 1, and
IV) 'y' is the number of years.

Procedure:

The following code will create procedure  COMPOUND_INTR that will calculate the compound interest.

CREATE OR REPLACE PROCEDURE COMPOUND_INTR (P IN NUMBER,R IN NUMBER,Y IN NUMBER) AS I NUMBER(6,2);
BEGIN I:=P*POWER(1+R/100,Y)-P;
DBMS_OUTPUT.PUT_LINE(I);
END;
/


User can use any of the following Commands to call the procedure.

1.
BEGIN COMPOUND_INTR (1000,10,3);
END;

2.
EXEC COMPOUND_INTR (1000,10,3);

Output:

compound interest
▲