使用DBI从光标返回值进行循环计算

Question

我试图了解如何从涉及使用DBI循环的光标的SQL查询中返回计算值。例如，我使用加载到Oracle数据库（V19C）的 mtcars 数据集。看起来像这样：

head(DBI::dbGetQuery(con, "SELECT * FROM TEST.MTCARS"))

#        MANUFACTURER  MPG CYL DISP  HP DRAT    WT  QSEC VS AM GEAR CARB       
# 1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
# 2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
# 3        Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
# 4    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
# 5 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
# 6           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

现在，例如，我想迭代的总和mpg。我意识到我可以简单地使用sum函数而不是循环，但是在这里，求和过程是另一个函数的占位符，因此为了一个简单的示例而幽默我！

在下面，我声明我的运行总数bar，然后在mtcars表的MPG列上循环，将每个mpg值添加到运行总计中。循环结束后，我有一个我想返回的总数，但是我不知道该怎么做。 返回似乎仅返回整数，因此在这里没有真正的意义。

tmp <- DBI::dbGetQuery(con,
"DECLARE
  bar NUMBER:=0;

BEGIN
  FOR foo IN (SELECT MPG FROM TEST.MTCARS)
  LOOP
    bar := bar + foo.MPG;
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE(bar);
END;")

tmp
#[1] TRUE

我应该如何从上面的计算中返回值？在此示例中，我希望获得值642.9，但我实现了。

Answer 1

就像我在评论中说的那样，这是一种方法（我的示例是在Scott@hr上）：

create or replace type sum_type is table of number;

create or replace function your_sum_fun
return sum_type PIPELINED is
    v_sum number:=0;
begin
    for i in (SELECT salary FROM employees)loop
       v_sum:=v_sum+i.salary;
    end loop;
    pipe row (v_sum);
    RETURN;
end;
select * from table(your_sum_fun);

或

create or replace function your_sum_fun
return sum_type PIPELINED is
    v_sum number:=0;
begin
    select sum(salary) into v_sum from employees;
    pipe row (v_sum);
    RETURN;
end;