pl/sql SQL 语句被忽略和缺少右括号 [英] pl/sql SQL Statement ignored and missing right parenthesis

问题描述

此代码必须将部门 ID 为 100 的员工的工资相加，因此会出现缺少右括号"的错误

this code has to sum salary of employees of department_id 100.so it gives this error "missing right parenthesis"

DECLARE
v_department_name VARCHAR(100);
v_department_manager VARCHAR(100);
v_totalsalary NUMBER(30);

BEGIN

SELECT departments.department_name, concat(employees.first_name, 
employees.last_name), employees.salary INTO v_department_name, 
v_department_manager, v_totalsalary 
FROM employees JOIN departments ON employees.department_id = 
departments.department_id 
WHERE employees.salary = (SELECT departments.department_id, 
sum(employees.salary)
FROM EMPLOYEES
where departments.department_id=100
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID);

DBMS_OUTPUT.PUT_LINE ('Department Name is  : ' || v_department_name || 'And 
Department Manager is : ' || v_department_manager || 'Total Amount of Salary 
is : ' || v_totalsalary );

END;

推荐答案

缺少右括号"错误显然是由子查询中的 ORDER BY 子句引起的(这里是不允许的).

The "missing right parenthesis" error is clearly caused by the ORDER BY clause in the subquery (where it is not allowed).

清除该错误后，您会收到值太多"错误，因为您将单个变量 (salary) 与返回两个值的子查询的输出 (Department_id AND sum(salary)).不知道为什么您认为需要在子查询的 SELECT 子句中包含 department_id.

Once you clear that error, you get the "too many values" error, because you are comparing a single variable (salary) to the output from a subquery that returns two values (department_id AND sum(salary)). Not sure why you thought you need to include the department_id in the SELECT clause of the subquery.

当您在问题中包含错误消息时，请包含消息的全文(显示错误发生的行号和位置 - 一个至关重要的细节！)

When you include error messages in your question, include the full text of the message (which shows the line number and position at which the error occurred - a crucial detail!)

一次一小步.暂时忘记PL/SQL；你能用 SQL 写出正确的查询，它会返回部门名称、经理姓名和部门所有员工的工资总和吗?如果你能做到这一点，那么围绕它的 PL/SQL 就很容易了.

Take it one small step at a time. Forget for the moment PL/SQL; are you able to write the correct query in SQL, which will return the department name, the manager's name and the sum of the salaries of all the employees in the department? If you can do that, then the PL/SQL around it is easy.

这是在一个 SQL 语句中获取所有值的一种方法:

Here is one way to get all the values in one SQL statement:

select   d.department_name,
         m.first_name || ' ' || m.last_name as manager_name,
         sum(e.salary) as sum_salary
from     departments d
         join
         employees   m   on d.manager_id    = m.employee_id
         join
         employees   e   on d.department_id = e.department_id
where    d.department_id = 100
group by d.department_id, d.department_name, m.first_name, m.last_name
;

DEPARTMENT_NAME  MANAGER_NAME     SUM_SALARY
---------------  ---------------  ----------
Finance          Nancy Greenberg       51608

也许 80% 的编写好的 PL/SQL 代码只是编写好的、高效的 SQL 语句.如果您对此查询有任何困难，您可能应该在接下来的几天或几周内将大部分时间花在编写 SQL 语句上；当您觉得这个查询(在我的回答中)简单"、简单"、标准"(确实如此！)时，请返回 PL/SQL.

Perhaps 80% of writing good PL/SQL code is simply writing good, efficient SQL statements. If you have any difficulty with this query, you should probably spend the majority of your time writing SQL statements, for the next few days or weeks; return to PL/SQL when you feel this query (in my answer) is "simple", "easy", "standard" (which it is!)

这篇关于pl/sql SQL 语句被忽略和缺少右括号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！