Oracle [Procedure] - Sum 函数忽略 WHERE 子句 [英] Oracle [Procedure] - Sum function ignores WHERE clause

问题描述

我的 ORACLE 过程有问题，似乎 SELECT SUM 忽略了我的 WHERE 子句，并汇总了所有列，而不是仅汇总我想要的列 (deptno).但是，如果我使用这样的功能之一，例如:

I have a problem with a ORACLE Procedure, it seems that SELECT SUM ignores my WHERE clause, and Sums up ALL of the columns instead of only those that i want to (deptno). However if i use one of this functions like this for example :

select SUM(SAL) AS SALSUM FROM SCOTT.EMP WHERE SCOTT.EMP.DEPTNO = 10;

它显示正确的 SUM.可能是什么问题?感谢您的帮助.

It displays the proper SUM. What may be the problem? Thanks for help.

CREATE OR REPLACE PROCEDURE PROCEDURE1(numerdept IN number, money OUT number) IS
    SALSUM NUMBER;
    COMMSUM NUMBER;
WYJATEK EXCEPTION;
BEGIN
    IF numerdept IN (10, 20, 30) THEN
    BEGIN
        select SUM(SAL) INTO SALSUM FROM SCOTT.EMP WHERE SCOTT.EMP.DEPTNO = numerdept;
        select SUM(COMM) INTO COMMSUM FROM SCOTT.EMP WHERE SCOTT.EMP.DEPTNO = numerdept;
        money := SALSUM + COMMSUM;
    END;
    ELSE RAISE WYJATEK;
    END IF;

    EXCEPTION 
    WHEN WYJATEK THEN
    BEGIN
    dbms_output.put_line('Wrong deptno');
    money := NULL; 
    END;
END;
-- checking -- 
SET SERVEROUTPUT ON;
DECLARE
    pension number;
BEGIN
    PROCEDURE1(10, pension);
    dbms_output.put_line(pension);
END;

解决方案

select NVL(SUM(SAL), 0) INTO SALSUM FROM SCOTT.EMP WHERE SCOTT.EMP.DEPTNO = numerdept;
select NVL(SUM(COMM), 0) INTO COMMSUM FROM SCOTT.EMP WHERE SCOTT.EMP.DEPTNO = numerdept;

和

 BEGIN
    dbms_output.put_line('Wrong deptno');
    money := 10; 
END;

推荐答案

你的问题是你的输入参数和你的列同名，所以当查询看到

Your problem is that your input parameter has the same name as your column, so when the query sees

WHERE SCOTT.EMP.DEPTNO = deptno

它将deptno 解释为SCOTT.EMP.DEPTNO，这意味着它对deptno 的所有值都成立.更改输入参数的名称，查询将按预期工作.

it interprets deptno as SCOTT.EMP.DEPTNO, meaning that it is true for all values of deptno. Change the name of your input parameter and the query will work as expected.

您还应该使用 NVL 来确保各个 SUM 值不是 NULL，就好像它们中的任何一个是 NULL 这将使它们的总和 NULL 以及 ie

You should also use NVL to ensure that the individual SUM values are not NULL, as if either of them is NULL that will make the sum of them NULL as well i.e.

select NVL(SUM(SAL), 0) INTO SALSUM FROM SCOTT.EMP WHERE SCOTT.EMP.DEPTNO = numerdept;
select NVL(SUM(COMM), 0) INTO COMMSUM FROM SCOTT.EMP WHERE SCOTT.EMP.DEPTNO = numerdept;

这篇关于Oracle [Procedure] - Sum 函数忽略 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！