时间戳

首页 > 解决方案 > PL/SQL 中未更新的行

sql - PL/SQL 中未更新的行

问题描述

我想将给定部门中的用户工资更新 10K，如果他们的名字中有 T，否则为 n*10k，其中 n 是从 1 到 5 的 saraly 类别。但不知何故，更新查询不会更新行。

我创建了一个游标，并将它们提取到 rec 变量中。然后检查名称是否有“T”，否则我将 inc 变量设置为薪金类别并适当地更新该行。

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;

错误的结果 薪资类别表 员工表

标签： sqloracleplsqlsql-update

解决方案

您不需要 PL/SQL 块。仅使用单个 Update 语句就足够了：

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;

如果员工的姓名包含字母，则员工的薪水系数等于 1T

推荐阅读