游标的理解

以前也曾遇到过错误"java.sql.SQLException: ORA-01000: 超出打开游标的最大数"，笼统地就认为是resultset或statement没有关闭导致。今天才明白个究竟。

一、PL SQL Deverloper

1. 开一个PL SQL窗口1，一次性执行下面5条语句，并不取完所有的记录：

select * from user_objects where rownum<=200;

select * from user_objects where rownum<=200;

select * from user_objects where rownum<=200;

select * from user_objects where rownum<=200;

select * from user_objects where rownum<=200;

2. 在另一个Deverloper的dba窗口2，可以查到:

select value from v$sesstat where sid=867 and statistic#=3

------------------

value = 7

说明那5条语句各在数据库打开了一个游标。

3. 将窗口1的5个语句的所有记录都取完，窗口2的SQL重新执行一次

value = 2

记录全部取完的语句，Deverloper会主动关闭游标，所以游标数量又变回2。

二、Java

1. 一个connection上可以挂多个statement，以前理解的connection上当前只能有一个statement、resultset是错的。

2. 例子

private static void query() { PreparedStatement st1 = null; ResultSet rs1 = null; PreparedStatement st2 = null; ResultSet rs2 = null; String sql; try { sql = "SELECT object_name from user_objects"; st1 = con.prepareStatement(sql); rs1 = st1.executeQuery(); sql = "SELECT table_name from user_tables"; st2 = con.prepareStatement(sql); rs2 = st2.executeQuery(); rs1.next(); System.out.println("object:" + rs1.getString(1)); rs2.next(); System.out.println("table:" + rs2.getString(1)); rs1.next(); System.out.println("object:" + rs1.getString(1)); rs2.next(); System.out.println("table:" + rs2.getString(1)); } catch (Exception ex) { ex.printStackTrace(); } finally { try {rs1.close();} catch (Exception ex) {} try {rs2.close();} catch (Exception ex) {} try {st1.close();} catch (Exception ex) {} try {st2.close();} catch (Exception ex) {} } }

执行结果是:

object:IX1_TEST
table:TEST
object:IX2_TEST
table:TEST2

显然，rs1、rs2同时都是有效的。