oracle - 如何在oracle存储过程中使用sum()函数？

Question

下面的示例运行良好并且返回一些行。但我需要行的摘要。

DECLARE
    x number;
Cursor c1 is
    select sal,deptno from emp;
    rw c1%rowtype;
BEGIN
    x:=0;
    open c1;
    LOOP
     fetch c1 into rw;
     FOR i IN 1..rw.deptno LOOP
       x:=x+rw.sal;
     end loop;
     exit when c1%notfound;
     DBMS_OUTPUT.PUT_LINE(x);
    END LOOP;
    close c1;
END;
/

假设您有三名员工，每个员工的工资都不同。工资的到期日为 10 个月、20 个月和 30 个月。工资是长期到期的。所以你想每个月在工资中加上2%的奖金金额，如下所示:

以下描述适用于单一员工 10 个月:

第一个月工资 = 800 => 800*2% = 16.00 => 总计 = 800+16 =816

第 2 个月工资 = 816 => 816*2% = 16.32 => 总计 = 816+16.32 =832.32

...................................................... ................................

第 10 个月工资 = 956.07 => 956.07*% = 19.12 => 总计 = 956.07+19.12 =975.20

第 1 个月的总工资=816。所以第 2 个月的工资=816。这种情况会持续10个月。每个员工的情况都是一样的。所以我需要总列的摘要。谢谢并致以诚挚的问候。

Answer 1

当您在查询中使用聚合函数 SUM 时(与您自己添加时不同)，您不需要转换 NULL。 SUM 负责处理它。虽然，正如 @DavidAldridge 指出的，如果您期望汇总记录组中的所有行都可能包含 NULL，那么您的总和也将为 NULL。如果您想返回一个值，可以按如下方式包装总和 coalesce(sum(sal),0)

这将为您提供所有工资的总和

select SUM(sal) TotalSal from emp;

这将为您提供按部门划分的 SUM

select SUM(sal) TotalDeptSal, deptno 
from emp
group by deptno;

在您的问题中，您发布了您需要在存储过程中执行它，而您的代码作为匿名 block 。如果您想从存储过程返回单个值，您可以选择声明带有返回参数的函数或带有输出参数的存储过程。要从 Oracle 中的存储过程返回记录集，您需要声明一个 refcursor 输出参数

CREATE OR REPLACE PROCEDURE Get_TotalSal_ByDept (
    p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
    OPEN p_recordset FOR
        select SUM(sal) TotalDeptSal, deptno 
        from emp
        group by deptno;
END;

编辑

我看到您添加了行 - 总计。与原来的问题相比并没有太大变化。不过，不需要使用光标。您可以运行 2 个查询并返回 2 个输出参数，一个包含部门数据，另一个包含总计数据。

CREATE OR REPLACE PROCEDURE Get_SalByDept_WithTotal (
    p_total OUT NUMBER,
    p_recordset OUT SYS_REFCURSOR) AS
BEGIN 
    select SUM(sal) INTO p_total from emp;

    OPEN p_recordset FOR
        select SUM(sal) TotalDeptSal, deptno 
        from emp
        group by deptno;
END;