Thanks for the question, Peter.

Asked: January 12, 2016 - 3:24 pm UTC

Last updated: January 13, 2016 - 3:42 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

I'm looking at some code that opens an explicit cursor using a single table query with a where clause, for update. The query selects about 75K rows for context in case it matters. As it processes each row, it needs to split amounts into separate new records (accounting data being redistributed to other accounting categories). It deletes WHERE CURRENT OF and then inserts multiple records that would match the cursor's selection criteria. The cursor has no order by. The data is committed after the cursor is closed.

Clearly, there are multiple better ways to do this. But for the sake of understanding, is a query's result set being processed row-by-row in a cursor loop isolated from DML performed on the same table matching the selection criteria after the cursor is opened? I believe it would be isolated from other transactions and the result set would get only data committed at the time the cursor is opened. But I'm not certain if updates in the same transaction would affect the cursor result set.

I follow the practice to never use PL/SQL when it can be done with SQL, but I'm not certain I can rewrite this in pure SQL because when the amounts are split (rules of how many splits vary record by record), it has to give some category the odd penny.

Thanks.

and Chris said...

When you open a cursor, the data set is read consistent from that point in time:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:27330770500351

So your cursor will have the values from the point you opened it. Using the where current of for the deletes means it'll delete the row you're processing:

set serveroutput on 
create table t as 
  select rownum x from dual
  connect by level <= 10;

declare
  l t%rowtype; 
  cursor cur is 
    select * from t for update;
begin
  open cur;
  
  update t set x = 1;
 
  loop
    fetch cur into l;
    exit when cur%notfound;
    dbms_output.put_line(l.x);
  
    delete t 
    where  current of cur;
    dbms_output.put_line('Deleted ' || sql%rowcount || ' rows');
  end loop;
end;
/
PL/SQL procedure successfully completed.
1
Deleted 1 rows
2
Deleted 1 rows
3
Deleted 1 rows
4
Deleted 1 rows
5
Deleted 1 rows
6
Deleted 1 rows
7
Deleted 1 rows
8
Deleted 1 rows
9
Deleted 1 rows
10
Deleted 1 rows

Your session will be able to see the results of the updates though. Depending upon what else happens in the transaction, this may not be what you want.

If you replace the where current of delete to a delete by value (where x = :val), although the cursor has the old values, the table itself has the new ones:

rollback;

declare
  l t%rowtype; 
  cursor cur is 
    select * from t for update;
begin
  open cur;
  
  update t set x = 1;
 
  loop
    fetch cur into l;
    exit when cur%notfound;
    dbms_output.put_line(l.x);
  
    delete t 
    where  x = l.x;
    dbms_output.put_line('Deleted ' || sql%rowcount || ' rows');
  end loop;
end;
/
PL/SQL procedure successfully completed.
1
Deleted 10 rows
2
Deleted 0 rows
3
Deleted 0 rows
4
Deleted 0 rows
5
Deleted 0 rows
6
Deleted 0 rows
7
Deleted 0 rows
8
Deleted 0 rows
9
Deleted 0 rows
10
Deleted 0 rows

===============

Addenda

btw, the "missing cent" problem is indeed solvable with SQL

SQL> with
  2  people as
  3    ( select 'p1' p from dual union all
  4      select 'p2' p from dual union all
  5      select 'p3' p from dual
  6    ),
  7  total_amt as
  8   ( select 10 amt , count(*) num from people )
  9  select
 10    p,
 11    case
 12      when  row_number() over ( order by p ) = num then amt - (num-1)*round(amt/num,2)
 13      else  round(amt/num,2)
 14    end portion
 15  from people, total_amt;

P     PORTION
-- ----------
p1       3.33
p2       3.33
p3       3.34

3 rows selected.

Cheers,
Connor

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Missing Cent

Peter, January 13, 2016 - 1:31 am UTC

Indeed, I arrived at a very similar solution right after my post. Here's my version that simulates more closely how our data is structured.

WITH work AS (SELECT 1 AS ssn, 'A' AS home_can, 100.01 AS gross_amt FROM DUAL
              UNION ALL
              SELECT 2 AS ssn, 'A' AS home_can, 100.01 AS gross_amt FROM DUAL
              UNION ALL
              SELECT 3 AS ssn, 'A' AS home_can, 100.01 AS gross_amt FROM DUAL
              )
    ,dist AS (SELECT 1 AS ssn, 'A' AS home_can, 'A1' AS dist_can, .65 AS dist_pct FROM DUAL
              UNION ALL
              SELECT 1 AS ssn, 'A' AS home_can, 'A2' AS dist_can, .35 AS dist_pct FROM DUAL
              UNION ALL
              SELECT 2 AS ssn, 'A' AS home_can, 'A4' AS dist_can, .90 AS dist_pct FROM DUAL
              UNION ALL
              SELECT 2 AS ssn, 'A' AS home_can, 'A3' AS dist_can, .05 AS dist_pct FROM DUAL
              UNION ALL
              SELECT 2 AS ssn, 'A' AS home_can, 'A6' AS dist_can, .03 AS dist_pct FROM DUAL
              UNION ALL
              SELECT 2 AS ssn, 'A' AS home_can, 'A5' AS dist_can, .02 AS dist_pct FROM DUAL
              UNION ALL
              SELECT 3 AS ssn, 'A' AS home_can, 'A1' AS dist_can, .33 AS dist_pct FROM DUAL
              UNION ALL
              SELECT 3 AS ssn, 'A' AS home_can, 'A2' AS dist_can, .33 AS dist_pct FROM DUAL
              UNION ALL
              SELECT 3 AS ssn, 'A' AS home_can, 'A3' AS dist_can, .33 AS dist_pct FROM DUAL
              UNION ALL
              SELECT 3 AS ssn, 'A' AS home_can, 'A4' AS dist_can, .01 AS dist_pct FROM DUAL
              )
    SELECT s.*
          ,CASE WHEN row_num > 1 THEN dist_amt ELSE dist_amt + ( gross_amt - dist_bal ) END AS dist_adj_amt -- let the last (largest) bucket have the odd penny
      FROM (SELECT d.ssn, d.home_can, d.dist_can, d.dist_pct, w.gross_amt
                  ,ROUND(w.gross_amt*d.dist_pct,2) AS dist_amt
                  ,SUM(ROUND(w.gross_amt*d.dist_pct,2)) OVER (PARTITION BY d.ssn ORDER BY d.dist_pct     , d.dist_can     ) AS dist_bal
                  ,ROW_NUMBER()                         OVER (PARTITION BY d.ssn ORDER BY d.dist_pct DESC, d.dist_can DESC) AS row_num
              FROM dist d JOIN work w ON w.ssn = d.ssn AND w.home_can = d.home_can
            ) s
     ORDER BY s.ssn, s.dist_pct, s.dist_can;

SSN H DI   DIST_PCT  GROSS_AMT   DIST_AMT   DIST_BAL    ROW_NUM DIST_ADJ_AMT
--- - -- ---------- ---------- ---------- ---------- ---------- ------------
  1 A A2        .35     100.01         35         35          2           35
  1 A A1        .65     100.01      65.01     100.01          1        65.01
  2 A A5        .02     100.01          2          2          4            2
  2 A A6        .03     100.01          3          5          3            3
  2 A A3        .05     100.01          5         10          2            5
  2 A A4         .9     100.01      90.01     100.01          1        90.01
  3 A A4        .01     100.01          1          1          4            1
  3 A A1        .33     100.01         33         34          3           33
  3 A A2        .33     100.01         33         67          2           33
  3 A A3        .33     100.01         33        100          1        33.01
 
10 rows selected

January 13, 2016 - 3:42 am UTC

nice work

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Missing Cent

More to Explore

Analytics

DML on same table while in a cursor loop

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Missing Cent

More to Explore

Analytics