Return value from cursor FOR LOOP calculation using DBI-CodePudding

I'm trying to understand how to return a calculated value from a SQL query that involves a cursor FOR LOOP using DBI. As an example, I use the mtcars dataset loaded into an Oracle database (v19c). It looks like this:

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

Now, as an example, let's say I want to iteratively sum MPG. I realise that I could simply use the SUM function instead of a loop, but here the summing process is a placeholder for another function so humour me for the sake of a simple example!

Below, I declare my running total variable bar and then loop over the MPG column of the MTCARS table adding each MPG value to the running total. When the loop finishes, I have a total that I'd like to return, but I can't figure out how to do so. RETURN seems to only return integers, so doesn't really make sense here.

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

How should I return a value from a calculation like the one above? For this example, I would expect to get the value 642.9, but I get TRUE.

CodePudding user response：

Like I said in comment this is one way to do it (my example is on 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;