Forum

Forums
SQL
Other Documents
Write a PL/SQL Func...
 
Share:
Notifications
Clear all

Write a PL/SQL Function to Calculate Increment for the Employees. Function will take increment percentage and salary as input and return increment amount as output.

 
    Last Post
RSS

Posts: 17
 Rahul Updhyay
Topic starter
Aug 23, 2020 1:02 pm
(@rahul)
Active Member
Joined: 4 years ago

Write a PL/SQL Function to calculate increment for the employees. Function will take increment percentage and salary as input and return increment amount as output.

This topic was modified 4 years ago 2 times by KV Kumar
3 Replies
Posts: 71
 John
Guest
Nov 15, 2020 8:05 am
(@John)
Trusted Member
Joined: 4 years ago

You can achieve this using a simple SQL update statement.

Let us consider a table called employee. Here I got four rows.

IDE          Name_NM               Salary_SA

1                  Nayani                   1000
2                  Kapil                      1000
3                  Sundhar              10000
4                  Devis                   20000

I want to increase the salary of employees by 10 percent. For this, I can write a query as below

UPDATE Employee SET Salary= (Select Salary+ (Select Salary*0.1))

This will increase all employee salaries by 10 percent.

IDE           Name_NM                  Salary_SA

1              Nayani                1100
2              Kapil                   1100
3              Sundhar             11000
4              Devis                 22000

For suppose I want to increase a particular employee salary by 20 percent I can write a query as

UPDATE Employee SET Salary= (Select Salary+ (Select Salary*0.1)) Where ID = 2

 

 

This post was modified 4 years ago 2 times by KV Kumar
Reply

Self Learning Video Tutorials - Software Course

Posts: 27
 KV Kumar
Admin
Feb 03, 2021 2:20 pm
(@vinodkrsetty)
Eminent Member
Joined: 6 years ago

Hey, try this function it worked for me. 

SQL> create or replace function calculate_emp_salary_increment
2 (incpercent in number,
3 salary in number
4 )
5 return number
6 as
7 begin
8 return salary * incpercent/100;
9 end;
10 /

Function created.

SQL> select emp_id, emp_name, salary, calculate_emp_salary_increment(20, salary) INCREMENT
2 from employee;

EMP_ID EMP_NAME SALARY INCREMENT
---------- ---------- ---------- ----------
1234   MIKE   2450               245
1235   Peter   5000               500
1236   JOHN  1300                130
This post was modified 4 years ago 2 times by KV Kumar
Reply
Posts: 81
 SQL Admin
Admin
Nov 17, 2024 10:24 am
(@sql-admin)
Estimable Member
Joined: 4 years ago

PL/SQL Function to Calculate Increment for Employees

To create a PL/SQL function that calculates the increment amount for employees based on an increment percentage and salary, follow the code and explanation below:

PL/SQL Function Code

plsql
 
-- Step 1: Create the PL/SQL function
CREATE OR REPLACE FUNCTION calculate_increment (
p_increment_percent IN NUMBER,
p_salary IN NUMBER
) RETURN NUMBER IS
v_increment_amount NUMBER;
BEGIN
-- Step 2: Calculate the increment amount
v_increment_amount := (p_increment_percent / 100) * p_salary;

-- Step 3: Return the calculated increment amount
RETURN v_increment_amount;
END calculate_increment;
/

Explanation of the Function

  1. CREATE OR REPLACE FUNCTION calculate_increment:
    • Defines a function named calculate_increment that takes two input parameters: p_increment_percent (increment percentage) and p_salary (employee's salary).
  2. RETURN NUMBER:
    • Specifies that the function returns a NUMBER data type.
  3. v_increment_amount:
    • A local variable used to store the calculated increment amount.
  4. v_increment_amount := (p_increment_percent / 100) * p_salary;:
    • The calculation formula that determines the increment amount based on the given percentage and salary.
  5. RETURN v_increment_amount;:
    • Returns the increment amount to the caller.

Example Usage of the Function

You can call the calculate_increment function in an SQL query or PL/SQL block as shown below:

plsql
 
DECLARE
increment_amount NUMBER;
BEGIN
-- Example: Call the function with a 10% increment and a salary of 5000
increment_amount := calculate_increment(10, 5000);
DBMS_OUTPUT.PUT_LINE('Increment Amount: ' || increment_amount);
END;
/

Expected Output:

mathematica
 
Increment Amount: 500.00

Benefits of Using This Function

  • Reusability: This function can be called multiple times with different inputs, making it flexible for calculating increments for various employees.
  • Modularity: Encapsulates the logic in a single function for better maintainability and readability.
Reply

Self Learning Video Tutorials - Software Course

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: