批量kill殺死某些會話session的PL/SQL

原文：http://blog.itpub.net/9240380/viewspace-666622/

SQL> declare
2 v_sid v$session.sid%type; --定義如下兩個type類型，用於接收cursor
v_serial# v$session.serial#%type;
3 4 cursor cur_session is select sid,serial# from v$session where program ='plsqldev.exe'; --#定義cursor
5 begin
6 open cur_session; --打開cusor
7 loop --打開游標馬上開始迴圈，因為cursor是一條條取數據的
8 fetch cur_session into v_sid,v_serial#; --把游標的數據放入上面定義的type變數中
9 --根據以上的type變數及游標生成批量殺session的動態sql腳本，並執行
10 execute immediate 'alter system kill session '''||v_sid||','||v_serial#||''' immediate';
11 exit when cur_session%notfound; --要加個異常處理，不然永遠是死迴圈
12 dbms_output.put_line('cursor date have been fetched ending');
13 end loop; --loop也有成雙匹配出現
14 close cur_session; --游標處理完後，關閉游標
15 end;
16 /
declare
*
ERROR at line 1:
ORA-00030: User session ID does not exist.
ORA-06512: at line 10

明顯殺會話時候，會話不存在。再執行類似的PL/SQL 塊

SQL> SET serverout ON
SQL> DECLARE
2 v_ename EMP.ENAME%TYPE;
3 v_salary EMP.SAL%TYPE;
4 CURSOR c_emp IS SELECT ename,sal FROM emp;
5 BEGIN
6 OPEN c_emp;
7 loop
8 exit when c_emp%notfound;
9 FETCH c_emp INTO v_ename,v_salary;
10 DBMS_OUTPUT.PUT_LINE('Salary of Employee: '|| v_ename ||' is '|| v_salary);
11 end loop;
12 CLOSE c_emp;
13 END ;
14 /
Salary of Employee: SMITH is 800
Salary of Employee: ALLEN is 1600
Salary of Employee: WARD is 1250
Salary of Employee: JONES is 2975
Salary of Employee: MARTIN is 1250
Salary of Employee: BLAKE is 2850
Salary of Employee: CLARK is 2450
Salary of Employee: SCOTT is 3000
Salary of Employee: KING is 5000
Salary of Employee: TURNER is 1500
Salary of Employee: ADAMS is 1100
Salary of Employee: JAMES is 4400
Salary of Employee: FORD is 3000
Salary of Employee: MILLER is 1300
Salary of Employee: MILLER is 1300

結果最後一行迴圈執行了2次，在殺會話plsql中，殺最後一個會話操作也執行了2次，所以會遇到報錯。

調整PL/SQL 塊語句，將exit when cur_session%notfound;  放在fetch 之後，也就是要迴圈執行的語句之前就解決了

declare
v_sid v$session.sid%type; --定義如下兩個type類型，用於接收cursor
v_serial# v$session.serial#%type;
cursor cur_session is select sid,serial# from v$session where program ='plsqldev.exe'; --#定義cursor
begin
open cur_session; --打開cusor
loop --打開游標馬上開始迴圈，因為cursor是一條條取數據的
fetch cur_session into v_sid,v_serial#; --把游標的數據放入上面定義的type變數中
exit when cur_session%notfound; --要加個異常處理，不然永遠是死迴圈
--根據以上的type變數及游標生成批量殺session的動態sql腳本，並執行
execute immediate 'alter system kill session '''||v_sid||','||v_serial#||''' immediate';
dbms_output.put_line('cursor date have been fetched ending');
end loop; --loop也有成雙匹配出現
close cur_session; --游標處理完後，關閉游標
end;
/

SQL> select sid,serial#,status from v$session where program ='plsqldev.exe';

SID         SERIAL#    STATUS
---------- ---------- --------
26         27         INACTIVE
1159       189        INACTIVE

SQL>
SQL> declare
2 v_sid v$session.sid%type; --定義如下兩個type類型，用於接收cursor
3 v_serial# v$session.serial#%type;
4 cursor cur_session is select sid,serial# from v$session where program ='plsqldev.exe'; --#定義cursor
5 begin
6 open cur_session; --打開cusor
7 loop --打開游標馬上開始迴圈，因為cursor是一條條取數據的
8 fetch cur_session into v_sid,v_serial#; --把游標的數據放入上面定義的type變數中
9 exit when cur_session%notfound; --要加個異常處理，不然永遠是死迴圈
10 --根據以上的type變數及游標生成批量殺session的動態sql腳本，並執行
11 execute immediate 'alter system kill session '''||v_sid||','||v_serial#||''' immediate';
12 dbms_output.put_line('cursor date have been fetched ending');
13 end loop; --loop也有成雙匹配出現
14 close cur_session; --游標處理完後，關閉游標
15 end;
16 /
cursor date have been fetched ending
cursor date have been fetched ending

PL/SQL procedure successfully completed.

SQL> select sid,serial#,status from v$session where program ='plsqldev.exe';

no rows selected