原文：
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