Scribd Logo
Upload

Welcome to Scribd!

  • 14 views

    DBMS9

    Uploaded by

    shaik9391469742
    The document describes two PL/SQL programs: 1) A function that performs arithmetic operations (addition, subtraction, multiplication, division) on two input variables and returns the result. 2) A function that calculates salary increments for employees based on their salary, updates the employee table with the new salaries, and prints the results. Salary increments are 30%, 20%, or 10% depending on the salary amount.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    DBMS9

    Uploaded by

    shaik9391469742
    14 views3 pages
    The document describes two PL/SQL programs: 1) A function that performs arithmetic operations (addition, subtraction, multiplication, division) on two input variables and returns the result. 2) A function that calculates salary increments for employees based on their salary, updates the employee table with the new salaries, and prints the results. Salary increments are 30%, 20%, or 10% depending on the salary amount.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes two PL/SQL programs: 1) A function that performs arithmetic operations (addition, subtraction, multiplication, division) on two input variables and returns the result. 2) A function that calculates salary increments for employees based on their salary, updates the employee table with the new salaries, and prints the results. Salary increments are 30%, 20%, or 10% depending on the salary amount.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    14 views3 pages

    DBMS9

    Uploaded by

    shaik9391469742
    The document describes two PL/SQL programs: 1) A function that performs arithmetic operations (addition, subtraction, multiplication, division) on two input variables and returns the result. 2) A function that calculates salary increments for employees based on their salary, updates the employee table with the new salaries, and prints the results. Salary increments are 30%, 20%, or 10% depending on the salary amount.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 3

    9a.

    Aim: Write a func on that Accepts Two variables and performs Arithme c Opera ons
     Addi on
     Subtrac on
     Mul plica on
     Division
    Procedure:
    1. Declare the func on using the CREATE OR REPLACE FUNCTION statement.
    2. Declare a local variable.
    3. Perform the addi on, Subtrac on, Mul plica on and Division.
    4. Print The output of the func on.
    PL/SQL Commands:
    CREATE OR REPLACE FUNCTION perform_operations(A NUMBER, B NUMBER)
    RETURN NUMBER AS
    result NUMBER;
    BEGIN
    -- Addition
    result := A + B;
    DBMS_OUTPUT.PUT_LINE('Addition: ' || result);
    -- Subtraction
    result := A - B;
    DBMS_OUTPUT.PUT_LINE('Subtraction: ' || result);
    -- Multiplication
    result:= A * B;
    DBMS_OUTPUT.PUT_LINE('Multiplication: ' || result);
    -- Division
    IF B <> 0 THEN --‘<>’ operator represents “not equals” to in PL/SQL
    result:= A / B;
    DBMS_OUTPUT.PUT_LINE('Division: ' || result);
    ELSE
    DBMS_OUTPUT.PUT_LINE('Division by zero is not allowed.');
    result:= NULL;
    END IF;
    RETURN result;
    END;
    /
    Output:
    function created
    --To use this function, call it in a PL/SQL block as follows:
    DECLARE
    result NUMBER;
    BEGIN
    result:= perform_operations(2, 5);
    END;
    /
    Output:
    Statement processed
    Addition: 7
    Subtraction: -3
    Multiplication: 10
    Division: 0

    9b. Aim: To Write a PL/SQL block that updates salary of an employee in Employee table by using incr funcon
    which takes employee number as argument and calculates increment and returns increment based on the following
    criteria.
    If salary <= 3000, increment = 30% of salary
    If salary > 3000 and <= 6000, increment = 20% of salary
    else increment = 10% of salary
    Procedure:
    1. Create an employee table.
    2. Insert emp_id, emp_name, emp_salary using SQL.
    3. Declare the func on “incr” using the CREATE OR REPLACE FUNCTION statement that returns the
    incremented salaries.
    4. Output the incremented salaries of all employees.
    COMMANDS:
    --SQL BLOCK TO CREATE AN EMPLOYEE TABLE
    CREATE TABLE Employee (
    emp_number NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100),
    emp_salary NUMBER
    );

    INSERT INTO Employee (emp_number, emp_name, emp_salary)


    VALUES (1, 'David Philip', 4000);

    INSERT INTO Employee (emp_number, emp_name, emp_salary)


    VALUES (2, 'Kamal', 2500);
    INSERT INTO Employee (emp_number, emp_name, emp_salary)
    VALUES (3, 'Raj Kumar', 6000);
    Output:
    Table created.
    1 row(s) inserted.
    1 row(s) inserted.
    1 row(s) inserted.

    --PL/SQL block to create a function that returns the incremented salaries bases on employee number.
    Note: In pl/sql, the “%TYPE” attribute can be used to declare variables with the same data type as a specific column in a table.
    CREATE OR REPLACE FUNCTION incr(p_emp_number IN Employee.emp_number%TYPE)
    RETURN Employee.emp_salary%TYPE
    IS
    v_increment Employee.emp_salary%TYPE;
    BEGIN
    -- Calculate the increment based on the given criteria
    SELECT CASE
    WHEN emp_salary <= 3000 THEN emp_salary * 0.3
    WHEN emp_salary <= 6000 THEN emp_salary * 0.2
    ELSE emp_salary * 0.1
    END
    INTO v_increment
    FROM Employee
    WHERE emp_number = p_emp_number;

    -- Return the increment


    RETURN v_increment;
    END;
    /
    Output:
    Function created.

    --PL/SQL block to call the incr function and print the incremented salaries for all employees in the Employee table.
    DECLARE
    v_emp_number Employee.emp_number%TYPE;
    v_incremented_salary Employee.emp_salary%TYPE;
    BEGIN
    FOR emp IN (SELECT emp_number FROM Employee) LOOP
    v_emp_number := emp.emp_number;
    v_incremented_salary := incr(v_emp_number);
    DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_number || ', Incremented Salary: ' || v_incremented_salary);
    END LOOP;
    END;
    /
    Output:
    Statement processed.
    Employee Number: 1, Incremented Salary: 800
    Employee Number: 2, Incremented Salary: 750
    Employee Number: 3, Incremented Salary: 1200

    You might also like