sql - 错误的 PL/SQL - 值太多/缺少右括号

问题描述

我有这两张桌子：

我有这段代码无论如何都不起作用，我不知道问题出在哪里，在另一次尝试中它从“太多的值”变为“缺少正确的括号”，但我拥有所有这些。

我从这段代码开始并试图找出错误：

    DECLARE
      CURSOR c IS 
           SELECT * FROM employees
           ORDER BY sal DESC;
      l c%ROWTYPE;
             
    BEGIN
      OPEN c;
      LOOP
        FETCH c INTO l;
        EXIT WHEN c%NOTFOUND;
        UPDATE employees
               SET salary=salary*(1+ (SELECT ((avg_salary-salary)+5)/100 
                                       FROM (SELECT AVG(salary) AS avg_salary 
                                                 FROM employees emp
                                                      JOIN department dep
                                                           ON emp.department_id=dep.row_id
                                                 GROUP BY dep.row_id) s
                                       WHERE s.department_id=employees.department_id))
               WHERE emno=l.empno;
        COMMIT;
      END LOOP;
    END;
    /

我最终得到了这个，这仍然行不通：-太多的值

 DECLARE
  CURSOR c_crs IS 
       SELECT * FROM tst_employees
       ORDER BY salary DESC;
  l_rec c_crs%ROWTYPE;
BEGIN
  OPEN c_crs;
  LOOP
    FETCH c_crs INTO l_rec;
    EXIT WHEN c_crs%NOTFOUND;
    UPDATE tst_employees emp
           SET salary=salary*(1+ (SELECT ((s.avg_salary-empl.salary)+5)/100
                                 FROM 
                                    (SELECT dep.department_id, AVG(emp.salary) AS avg_salary 
                                    FROM tst_employees emp
                                           JOIN tst_departments dep
                                                 ON emp.department_id=dep.department_id
                                    GROUP BY dep.department_id) s, tst_employees emp
                                 WHERE s.department_id=emp.department_id ))
            WHERE emp.employee_id=l_rec.employee_id;
      COMMIT;
  END LOOP;
  CLOSE c_crs;
END;
/

或者这个： -- 缺少正确的括号：

DECLARE
  CURSOR c_crs IS 
       SELECT * FROM tst_employees
       ORDER BY salary DESC;
  l_rec c_crs%ROWTYPE;
BEGIN
  OPEN c_crs;
  LOOP
    FETCH c_crs INTO l_rec;
    EXIT WHEN c_crs%NOTFOUND;
    UPDATE tst_employees te
           SET salary=salary*(1+ (SELECT empl.employee_id, empl.department_id, ((s.avg_salary-empl.salary)+5)/100
                                 FROM 
                                    (SELECT dep.department_id, AVG(emp.salary) AS avg_salary 
                                    FROM tst_employees emp
                                           JOIN tst_departments dep
                                                 ON emp.department_id=dep.department_id
                                    GROUP BY dep.department_id) s, tst_employees empl
                                 WHERE s.department_id=empl.department_id
                                ORDER BY empl.employee_id ))
            WHERE te.employee_id=l_rec.employee_id;
      COMMIT;
  END LOOP;
  CLOSE c_crs;
END;
/

请帮我修复主要代码，以便它可以工作并根据需要重新计算工资，我不知道为什么它不起作用。我想根据每个员工所在部门的平均工资，根据更新子句 (s.avg_salary-empl.salary)+5)/100 中的公式提高工资

标签： sqloracleplsql

我不知道为什么你有一个所有员工的游标，并在所有员工都可以用一个更新语句更新他们的工资时一次循环遍历他们。一次循环遍历它们也会给循环结束的员工带来更大的加薪，因为在每个循环中，每个部门的平均值都会随着每次更新的应用而上升。

像这样一个简单的更新语句应该可以工作：

UPDATE tst_employees te
   SET salary =
             salary
           * (  (  (  (  (SELECT AVG (salary)
                            FROM tst_employees te2
                           WHERE te2.department_id = te.department_id)
                       - empl.salary)
                    + 5)
                 / 100)
              + 1);

sql - 错误的 PL/SQL - 值太多/缺少右括号

问题描述

解决方案

推荐阅读