1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

pl/sql

Discussion in 'SQL PL/SQL' started by suvarna, Apr 21, 2011.

  1. suvarna

    suvarna Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    1)Write a pl/sql function calc_inc to calculate salary increment by passing empno,inc due date as parameters
    If Experience <1yr no increment
    Experience>=1 and <2 yrs 10%of sal
    Experience >=3 and <4 yrs 15% of sal
    Experience >=4 yrs 20% of sal


    2)Write a procedure to insert sal_incr table for all employee that exit in emp table by calling the above function (calc_inc). Auto generate and insert control_no as
    Max(control_no+1) of previous transation

    Sal_incr table structure

    Control_no,
    Emp_code,
    Inc_eff_date,
    Sal_inc

    3)Write a data base trigger to update sal column of emp table whenever sal_inc column of sal_incr table is inserted/updated/deleted.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please post your attempts to solve these problems so we can see where you may have gone wrong and offer suggestions. As for #2 I cannot support using the max() function to retrieve the last known value from your table; read here to understand why:

    http://oratips-ddf.blogspot.com/2008/06/out-of-sequence.html
     
  3. suvarna

    suvarna Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Create or replace function calc_inc (empno in number, inc in date)
    Return number is
    Expe number;
    Sal number;
    Begin
    If expe<1 then
    Dbms_output.put_line(‘no inc’);
    Elsif (expe >=1 and expe < 2) then
    Expe :=(sal/10)*100;
    Dbms_output.put_line(expe);
    Elsif (expe >=3 and expe < 4) then
    Expe:=(sal/10)*100;
    Dbms_output.put_line(expe);
    Elsif expe >4 then
    Expe :=(sal/20)*100;
    Dbms_output.put_line (expe);
    End if;
    End;

    I could do function, but unable to do second n third. Atleast leavin the max(cont+1) can u help for the
    remaing thing in second n third question.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your function won't work even though it compiles successfully; nowhere in the code do you set Empe = inc, and checking empe rather than Empe will return nothing. Your dbms_output lines will not be displayed, either:

    Code ( (Unknown Language)):
    > set serveroutput on size 1000000
    SQL> select calc_inc(empno, hiredate) from emp;
    CALC_INC(EMPNO,HIREDATE)
    ------------------------
     
     
     
     
     

    CALC_INC(EMPNO,HIREDATE)
    ------------------------
     

    14 rows selected.
    SQL>
    Your logic is also incorrect as you set your Expe variable to the new salary (it WILL get reset but it makes no sense to someone reading the code), you don't calculate the years of experience from the passed hire date, you don't calculate the increased salary correctly and you eliminated an increase percentage listed in the problem. Correcting these mistakes we get:

    Code (SQL):
    CREATE OR REPLACE FUNCTION calc_inc (p_empno IN NUMBER, p_hiredt IN DATE)
    RETURN NUMBER IS
            --
            -- Define variables
            --
            Expe NUMBER:=1;
            Sal NUMBER:=1;
            NewSal NUMBER:=1;
    BEGIN
            --
            -- Retrieve the current salary for the passed empno
            --
            SELECT sal INTO Sal FROM emp WHERE empno = p_empno;
            --
            -- Determine years of experience on the job
            --
            SELECT round((sysdate - p_hiredt)/365.25,0) INTO Expe FROM dual;
            --
            -- Calculate the salary increase based on experience
            --
            IF Expe<1 THEN
                    --
                    -- No increase
                    --
                    NewSal:=Sal*1;
            Elsif (Expe >=1 AND Expe < 2) THEN
                    --
                    -- 10 percent increase
                    --
                    NewSal:=Sal*1.1;
            Elsif (Expe >=3 AND Expe < 4) THEN
                    --
                    -- 15 percent increase
                    --
                    NewSal:=Sal*1.15;
            Elsif (Expe > 4) THEN
                    --
                    -- 20 percent increase
                    --
                    NewSal:=Sal*1.2;
            END IF;
    --
    -- Return the adjusted salary
    --
    RETURN NewSal;
    END;
    /
    which returns:

    Code ( (Unknown Language)):
    > select empno, sal, hiredate, round((sysdate-hiredate)/365.25,0) expe, calc_inc(empno, hiredate) from emp;
         EMPNO        SAL HIREDATE        EXPE CALC_INC(EMPNO,HIREDATE)
    ---------- ---------- --------- ---------- ------------------------
          7369        800 17-DEC-80         30                      960
          7499       1600 20-FEB-81         30                     1920
          7521       1250 22-FEB-81         30                     1500
          7566       2975 02-APR-81         30                     3570
          7654       1250 28-SEP-81         30                     1500
          7698       2850 01-MAY-81         30                     3420
          7782       2450 09-JUN-81         30                     2940
          7788       3000 09-DEC-82         28                     3600
          7839       5000 17-NOV-81         29                     6000
          7844       1500 08-SEP-81         30                     1800
          7876       1100 12-JAN-83         28                     1320
         EMPNO        SAL HIREDATE        EXPE CALC_INC(EMPNO,HIREDATE)
    ---------- ---------- --------- ---------- ------------------------
          7900        950 03-DEC-81         29                     1140
          7902       3000 03-DEC-81         29                     3600
          7934       1300 23-JAN-82         29                     1560
    14 rows selected.
    SQL>
     
    suvarna likes this.
  5. suvarna

    suvarna Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    3) create or replace trriger t1 after insert on sal_incr
    declare e1 sal_incr%rowtype
    cursor c1 is select * from sal_incr
    begin
    open c1;
    loop
    fetch c1 into e1;
    exit when c1%notfound;
    end loop;
    if e1.sal_incr= 'insert'
    then
    update emp_table set sal_inc=sal+e1.newsal;
    end if;
    close c1;
    commit;
    end;

    Is the login or programe correct sir?
     
  6. suvarna

    suvarna Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    sorry logic*
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No -- I don't understand the purpose of the loop as all it will do is loop through the values until the end and then use the last value returned. You also haven't followed the instructions as you have not written code to handle updates and deletes, and the sal_incr column is a number containing the actual increase amount. I misread the original instructions so my previous function is incorrect as it returns the adjusted salary, not the salary adjustment. The corrected code is:

    Code (SQL):
    SQL> CREATE OR REPLACE FUNCTION calc_inc (p_empno IN NUMBER, p_hiredt IN DATE)
      2  RETURN NUMBER IS
      3
      4          --
      5          -- Define variables
      6          --
      7          Expe NUMBER:=1;
      8          Sal NUMBER:=1;
      9          SalInc NUMBER:=1;
     10  BEGIN
     11          --
     12          -- Retrieve the current salary for the passed empno
     13          --
     14          SELECT sal INTO Sal FROM emp WHERE empno = p_empno;
     15          --
     16          -- Determine years of experience on the job
     17          --
     18          SELECT round((sysdate - p_hiredt)/365.25,0) INTO Expe FROM dual;
     19          --
     20          -- Calculate the salary increase based on experience
     21          --
     22          IF Expe<1 THEN
     23                  --
     24                  -- No increase
     25                  --
     26                  SalInc:=Sal*0;
     27          Elsif (Expe >=1 AND Expe < 2) THEN
     28                  --
     29                  -- 10 percent increase
     30                  --
     31                  SalInc:=Sal*.1;
     32          Elsif (Expe >=3 AND Expe < 4) THEN
     33                  --
     34                  -- 15 percent increase
     35                  --
     36                  SalInc:=Sal*.15;
     37          Elsif (Expe > 4) THEN
     38                  --
     39                  -- 20 percent increase
     40                  --
     41                  SalInc:=Sal*.2;
     42          END IF;
     43  --
     44  -- Return the salary increase
     45  --
     46  RETURN SalInc;
     47  END;
     48  /
     
    You also need to write the procedure to insert the sal_incr table values and using max(control_no)+1 will not work on an empty table as it will return NULL. I hope you read my blog post aboujt this so you can see why this can't work when more than one person could be running this code.

    Write the procedure then post your code. Don't worry about the trigger until you can write a successfully executing procedure.
     
  8. suvarna

    suvarna Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    2)Create or replace procedure sal_incr is
    Cursor c1 is select * from emp;
    S number;
    Begin
    For i in c1
    Loop
    S:=cal_inc(i.empno,sysdate);
    Insert into sal_incr values (nvl((select max(cntlno) from sal_incr),0)+1,
    i.empno, sysdate, round(s));
    end loop;
    end;

    3)Create or replace trigger t1 after insert or update or delete on sal_incr
    for each row
    declare
    cnt number:=0;
    begin
    select count(*) into cnt
    from emp where empno=: old.ecode;
    if cnt>0 then
    update emp set sal=: old.sal_incr;
    else
    insert into emp(empno,sal) values:) old.ecode,old.sal_incr);
    end if;
    end;

    sir wat do say about these programes r they correct????
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why are you passing sysdate to the function rather than the hiredate? Passing sysdate will cause everyone to not receive an increase. And what is the purpose of selecting a count(*) of employees having a unique empno? The count will always be 1. Also the sal_incr is NOT the increased salary but the amount TO increase the salary; it needs to be added TO the original sal value. And deletes are not covered; what happens when a sal_incr record deleted? Is that employee no longer working for the company? Your instructions as posted clearly state that deletes must also be handled.