Thursday, March 4, 2010

Oracle: Creating Nested PL/SQL Subprograms


You can declare subprograms in any PL/SQL block.
Subprograms must go at the end of the declarative section (i.e. between IS and BEGIN statements)
The Scope of Subprogram is always within the main Program.

FUNCTION GROSSPAY (input NUMBER) RETURN NUMBER
IS
iBonus NUMBER;


/**BEGIN : Local program units **

The Scope of CalcBonus function is within the GROSSPAY function.
It cannot be directly accessed outside of GROSSPAY function.
NOTE : All the nested function must be created within IS and BEGIN statements.
*/
FUNCTION CalcBonus RETURN NUMBER; --Optional declarative section

FUNCTION CalcBonus RETURN NUMBER

IS
dBonus NUMBER ;

BEGIN
CASE
  WHEN input BETWEEN 2000 and 4999 THEN
   dBonus := (input * 10)/100;

  WHEN input BETWEEN 5000 and 6999 THEN

   dBonus := (input * 15)/100;

  WHEN input BETWEEN 7000 and 1000 THEN

   dBonus := (input * 20)/100;

  WHEN input > 7000 THEN

   dBonus := (input * 25)/100;
END CASE;

RETURN dbonus;

END CalcBonus;
/**END : Local program units ***/
BEGIN
   iBonus := input + CalcBonus ; RETURN iBonus;END GrossPay;

No comments:

Post a Comment