How to use loop for query data sum db until empty db-CodePudding

SELECT 
    locations, lotid, loadseq, startdate,
    SUM(DECODE (bintype||binnum, 'DB1', binvalues)) db1, 
    SUM(DECODE (bintype||binnum, 'DB2', binvalues)) db2 
FROM 
    test_summary_bin 
WHERE 
    lotid = 'C659300'
GROUP BY 
    locations, lotid, loadseq, startdate
ORDER BY 
    startdate

I have coding and I am curious about how to loop for select [sum (DECODE (bintype||binnum, 'DB1', binvalues)) db1] until entry . Because the data has DB a lot of columns.

CodePudding user response：

 BEGIN
   FOR c_loop IN  (
    SELECT 
    test_summary_bin.locations , 
    test_summary_bin.lotid, 
    test_summary_bin.loadseq, 
    test_summary_bin.startdate,
    sum (DECODE (bintype||binnum, 'DB', binvalues)) db1 --This command I need to run with loop
    FROM test_summary_bin 
    WHERE lotid = 'C659300' 
    group by test_summary_bin.locations, test_summary_bin.lotid, test_summary_bin.loadseq, test_summary_bin.startdate )
    
    LOOP
  DBMS_OUTPUT.PUT_LINE (c_loop.locations || ' ' || c_loop.lotid || ' ' 
         || c_loop.loadseq || ' ' || c_loop.startdate);
    END LOOP ;

      END ;

**I tried that but I have no ideal **

CodePudding user response：

Do you mean cursor?

You can setup a cursor for this query, and loop through the result

DECLARE
  -- cursor that stores SQL query output
  CURSOR c_employees IS
    select full_name from employees;
  -- a row variable as a temporary hold during for-loop
  r_employee c_employees%rowtype;
BEGIN
  for r_employee in c_employees loop
    dbms_output.put_line(r_employee.full_name);
  end loop;

See more on: https://www.tutorialspoint.com/plsql/plsql_cursors.htm

CodePudding user response：

https://i.stack.imgur.com/DBoSG.png

I need to do this table. I was post up there for my syntax.