6. HELLODBA.COM>declare
7. 2 cursor vc is select t2.extent_id, t1.block_add, t1.cnt
8. 3 from(select to_char(dbms_utility.make_data_block_address(dbms_ro
wid.rowid_relative_fno(ROWID),
9. 4 dbms_ro
wid.rowid_block_number(ROWID)),
10. 5 'XXXXXXXX') block_add,
11. 6 dbms_rowid.rowid_relative_fno(ROWID) relative_fno,
12. 7 dbms_rowid.rowid_block_number(ROWID) block_number,
13. 8 count(1) cnt
14. 9 from &&owner..&&tabname
15. 10 group by dbms_rowid.rowid_relative_fno(ROWID) ,
16. 11 dbms_rowid.rowid_block_number(ROWID)
17. 12 ) t1,
18. 13 demo.tmp_extents t2
19. 14 where t1.relative_fno = t2.relative_fno
20. 15 and t1.block_number >= t2.block_id and t1.block_number < t2.block
_id + t2.blocks
21. 16 order by t2.extent_id, block_add;
22. 17 comp_cnt pls_integer:=-1;
23. 18 array_size pls_integer:=15;
7. 2 cursor vc is select t2.extent_id, t1.block_add, t1.cnt
8. 3 from(select to_char(dbms_utility.make_data_block_address(dbms_ro
wid.rowid_relative_fno(ROWID),
9. 4 dbms_ro
wid.rowid_block_number(ROWID)),
10. 5 'XXXXXXXX') block_add,
11. 6 dbms_rowid.rowid_relative_fno(ROWID) relative_fno,
12. 7 dbms_rowid.rowid_block_number(ROWID) block_number,
13. 8 count(1) cnt
14. 9 from &&owner..&&tabname
15. 10 group by dbms_rowid.rowid_relative_fno(ROWID) ,
16. 11 dbms_rowid.rowid_block_number(ROWID)
17. 12 ) t1,
18. 13 demo.tmp_extents t2
19. 14 where t1.relative_fno = t2.relative_fno
20. 15 and t1.block_number >= t2.block_id and t1.block_number < t2.block
_id + t2.blocks
21. 16 order by t2.extent_id, block_add;
22. 17 comp_cnt pls_integer:=-1;
23. 18 array_size pls_integer:=15;
24. 19 total_io pls_integer:=1;
语句说明:
上述语句,说明定义游标cursor时的select语句里可以用变量。
&&owner..&&tabname ,其中的两点是什么意思?
两点中的第一个点是转义符,在两个替换变量之间的点号需要被转义的。
例如,&&owner的值为scott,&&tabname 的值为test,则&&owner..&&tabname 表示为scott.test
详见《Oracle 辑读写深入分析》