DECLARE CURSOR v_cursor IS SELECT * FROM B_AREAS; v_area VARCHAR2(100); BEGINOPEN v_cursor; LOOP FETCH v_cursor INTO v_area; IF (v_cursor%NOTFOUND) THEN DBMS_OUTPUT.PUT_LINE('last'||v_area); EXIT ; END IF; DBMS_OUTPUT.PUT_LINE(v_area); END LOOP; CLOSE v_cursor; END; ---- X O E S M W N H lastH--不知道这样是否可以满足要求
create or replace procedure kill_latch_free is cursor kill_latch_cur is select v.sid,v.serial# from v$session v,v$session_wait sw where sw.event='latch free' and v.sid=sw.sid; v_sid number; v_serial number; v_num number; begin open kill_latch_cur; v_num:=kill_latch_cur%rowcount; dbms_output.put_line(kill_latch_cur%rowcount); --查看复合条件的记录数 loop fetch kill_latch_cur into v_sid,v_serial; exit when kill_latch_cur%NOTFOUND; execute immediate 'alter system kill session '''||v_sid||','||v_serial||''''; dbms_output.put_line('alter system kill session '''||v_sid||','||v_serial||''''); end loop; close kill_latch_cur; dbms_output.put_line('总共killed:'||v_num||' latch free sessions!'); return; end kill_latch_free; /
to tgm78() rowcount 属性是读取游标当前所在的行位置,而不是记录数吧%ROWCOUNT Attribute: How Many Rows Fetched So Far? When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. In the next example, you use %ROWCOUNT to take action if more than ten rows have been fetched:LOOP FETCH c1 INTO my_ename, my_deptno; IF c1%ROWCOUNT > 10 THEN ... END IF; ... END LOOP;
SQL> declare 2 cursor cur_test is 3 select * from test_tab; 4 is_count number ; 5 begin 6 select count(*) into is_count 7 from test_tab ; 8 dbms_output.put_line('Cursor record number is'||to_char(is_count)); 9 end; 10 /Cursor record number is :9PL/SQL procedure successfully completedSQL> select * from test_tab;COL_A COL_B COL_C ---------- ---------- -------------------- A 1 A 2 B 2 B 3 C 4 D 1 D 12 D 121 D 12 9 rows selected其实最好的办法是把游标中的语句写成动态的
那用包子的方法好了 先用count得到记录数 在处理游标
to 寻to tgm78() rowcount 属性是读取游标当前所在的行位置,而不是记录数吧%ROWCOUNT Attribute: How Many Rows Fetched So Far? When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. In the next example, you use %ROWCOUNT to take action if more than ten rows have been fetched:LOOP FETCH c1 INTO my_ename, my_deptno; IF c1%ROWCOUNT > 10 THEN ... END IF; ... END LOOP;********************** 英文的解释是不错的,Attribute: How Many Rows Fetched So Far?
细细分析,是返回当前已经fetched的行数
cur_tt%rowcount就可以获得游标的记录总行数了
How Many Rows Fetched So Far?这个话意思是:游标到目前共读取了多少行,而并非游标的行数
CURSOR v_cursor IS SELECT * FROM B_AREAS;
v_area VARCHAR2(100);
BEGINOPEN v_cursor;
LOOP
FETCH v_cursor INTO v_area;
IF (v_cursor%NOTFOUND)
THEN
DBMS_OUTPUT.PUT_LINE('last'||v_area);
EXIT ;
END IF;
DBMS_OUTPUT.PUT_LINE(v_area);
END LOOP;
CLOSE v_cursor;
END; ----
X
O
E
S
M
W
N
H
lastH--不知道这样是否可以满足要求
cursor kill_latch_cur is
select v.sid,v.serial# from v$session v,v$session_wait sw where sw.event='latch free' and v.sid=sw.sid;
v_sid number;
v_serial number;
v_num number;
begin
open kill_latch_cur;
v_num:=kill_latch_cur%rowcount;
dbms_output.put_line(kill_latch_cur%rowcount); --查看复合条件的记录数
loop
fetch kill_latch_cur into v_sid,v_serial;
exit when kill_latch_cur%NOTFOUND; execute immediate 'alter system kill session '''||v_sid||','||v_serial||''''; dbms_output.put_line('alter system kill session '''||v_sid||','||v_serial||'''');
end loop;
close kill_latch_cur;
dbms_output.put_line('总共killed:'||v_num||' latch free sessions!');
return;
end kill_latch_free;
/
但为了得到记录数而多fetch一轮,效率不是很低???上面的达不到效果,因为IF (v_cursor%NOTFOUND)只前已经将最后一条数据取出来,按一般数据处理了。
When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. In the next example, you use %ROWCOUNT to take action if more than ten rows have been fetched:LOOP
FETCH c1 INTO my_ename, my_deptno;
IF c1%ROWCOUNT > 10 THEN
...
END IF;
...
END LOOP;
2 cursor cur_test is
3 select * from test_tab;
4 is_count number ;
5 begin
6 select count(*) into is_count
7 from test_tab ;
8 dbms_output.put_line('Cursor record number is'||to_char(is_count));
9 end;
10 /Cursor record number is :9PL/SQL procedure successfully completedSQL> select * from test_tab;COL_A COL_B COL_C
---------- ---------- --------------------
A 1
A 2
B 2
B 3
C 4
D 1
D 12
D 121
D 12 9 rows selected其实最好的办法是把游标中的语句写成动态的
先用count得到记录数
在处理游标
When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. In the next example, you use %ROWCOUNT to take action if more than ten rows have been fetched:LOOP
FETCH c1 INTO my_ename, my_deptno;
IF c1%ROWCOUNT > 10 THEN
...
END IF;
...
END LOOP;**********************
英文的解释是不错的,Attribute: How Many Rows Fetched So Far?