Rows not updated in PL/SQL

I want to update user’s salaries in a given department by 10K if they have T in their names otherwise n*10k where n is the saraly category which is from 1 to 5. But somehow Update query is not updating the rows.

I create a cursor, and fetch them into rec variable. And then check if the name has ‘T’, otherwise I set inc variable to salary category and update the row appropriately.

CREATE OR REPLACE PROCEDURE sal_increase(p_deptno INTEGER) IS 

    cursor curs is select empno, e.ename, e.sal, sc.category  , lowest_sal, highest_sal
        from emp2 e 
        join nikovits.sal_cat sc on lowest_sal <= sal and sal <= highest_sal
        where deptno = p_deptno FOR UPDATE; 
        

    rec curs%ROWTYPE;
    i integer := 1;
    i_out varchar2(30);
    inc integer := 1;
    has_t integer := 0;
    
begin
    
    OPEN curs;
    
    loop
        FETCH curs INTO rec;
        EXIT WHEN curs%NOTFOUND;
        has_t := 0;
        for i in 1..length(rec.ename) loop
            i_out := substr(rec.ename,i,1);
            if i_out = 'T' then
                has_t := 1;
            end if;
        end loop;
        
        if has_t = 0 then
             inc := rec.category;
        end if;
        
        if has_t = 1 then
            DBMS_OUTPUT.PUT_LINE(rec.ename||' has T, increment is 10000');
        else
            DBMS_OUTPUT.PUT_LINE(rec.ename||' Doesnt have T, salery category is '|| rec.category ||' increment is '|| inc ||'*10000');
        end if;
        DBMS_OUTPUT.PUT_LINE('update begins...');
        UPDATE emp2 e1 SET sal = sal + (inc * 10000) WHERE CURRENT OF curs;
        DBMS_OUTPUT.PUT_LINE('After update'||rec.ename||'"salary: '||rec.sal);
    end loop;
    
    CLOSE curs;
    
end;

/

set serveroutput on
execute sal_increase(20);
select empno, e.ename, e.sal, sc.category  , lowest_sal, highest_sal
        from emp2 e 
        join nikovits.sal_cat sc on lowest_sal <= sal and sal <= highest_sal where deptno = 20;

Answer

You don’t need a PL/SQL Block. Only using a single Update statement is enough :

UPDATE emp2 e1 
   SET sal = sal + 
             (
              SELECT 10000 * case when instr(e.ename,'T')>0 then 1 else sc.category end 
                FROM emp2 e
                JOIN sal_cat sc
                  ON lowest_sal <= sal
                 AND sal <= highest_sal
               WHERE empno = e1.empno             
             )
  WHERE deptno = :p_deptno;

where an employees’ salary will have a coefficient equals to 1, if his name contain a letter T

Advertisement

Answer