Bulk Rowcount
Lets assume, emp table has below data
EMPNO
|
ENAME
|
JOB
|
MGR
|
SAL
|
DEPTNO
| |
1
|
7782
|
MANAGER
|
7839
|
2450.00
|
10
| |
2
|
7839
|
KING
|
PRESIDENT
|
5000.00
|
10
| |
3
|
7934
|
MILLER
|
CLERK
|
7782
|
1300.00
|
10
|
4
|
7566
|
JONES
|
MANAGER
|
7839
|
2975.00
|
20
|
5
|
7788
|
SCOTT
|
ANALYST
|
7566
|
3000.00
|
20
|
6
|
7876
|
CLERK
|
7788
|
1100.00
|
20
| |
7
|
7369
|
SMITH
|
CLERK
|
7902
|
800.00
|
20
|
8
|
7499
|
ALLEN
|
SALESMAN
|
7698
|
1600.00
|
30
|
9
|
7521
|
WARD
|
SALESMAN
|
7698
|
1250.00
|
30
|
10
|
7844
|
TURNER
|
SALESMAN
|
7698
|
1500.00
|
30
|
11
|
7900
|
JAMES
|
CLERK
|
7698
|
950.00
|
30
|
12
|
7698
|
BLAKE
|
MANAGER
|
7839
|
2850.00
|
30
|
13
|
7654
|
MARTIN
|
SALESMAN
|
7698
|
1250.00
|
30
|
14
|
7902
|
FORD
|
ANALYST
|
7566
|
3000.00
|
60
|
I am writing a script to update data in bulk using a FORALL loop; I also wanted to know the number of rows got updated in each iteration. Using ROWCOUNT, I will know the total count but not for each iteration: See the sample script below
DECLARE
TYPE larr_typ_dept IS TABLE OF NUMBER;
larr_dept larr_typ_dept := larr_typ_dept(10, 60);
BEGIN
FORALL i IN larr_dept.FIRST .. larr_dept.LAST
UPDATE emp SET sal = sal + 100 WHERE deptno = larr_dept(i);
dbms_output.put_line('The number of records updated (Count By ROWCOUNT) ' || SQL%ROWCOUNT);
FOR j IN larr_dept.FIRST .. larr_dept.LAST
dbms_output.put_line('The number of records updated in ' || j ||
'th iteration; For Dept ' || larr_dept(j) ||
' (Count By BULK_ROWCOUNT) :: ' || SQL%BULK_ROWCOUNT(j));
END LOOP ;
END;
Out put would be
The number of records updated (Count By ROWCOUNT) 4
The number of records updated in 1th iteration; For Dept 10 (Count By BULK_ROWCOUNT) :: 3
The number of records updated in 2th iteration; For Dept 60 (Count By BULK_ROWCOUNT) :: 1
No comments:
Post a Comment