今天把oracle中的包和游标学习了下，不废话，网上的的有些代码是错误的，抄来抄去，就自己实践了下，做个记录。直接上图，上代码
通过plsql创建自己的的包，包分为包头和包体。
1.包头如下：
CREATE OR REPLACE PACKAGE JAVALINKTEST
IS
TYPE CURSOR_TYPE IS REF CURSOR; --定义游标
PROCEDURE TEST_CURSOR(INPUT STRING, CURSOR_BACK OUT CURSOR_TYPE); END JAVALINKTEST;
2.包体如下：
CREATE OR REPLACE PACKAGE BODY JAVALINKTEST IS PROCEDURE TEST_CURSOR(INPUT STRING, CURSOR_BACK OUT CURSOR_TYPE)
IS
BEGIN
IF INPUT = '物料' THEN
OPEN CURSOR_BACK FOR SELECT * FROM T_BD_MATERIAL WHERE CFISSYNC = 1;
ELSE
OPEN CURSOR_BACK FOR SELECT * FROM T_BD_CUSTOMER WHERE CFISSYNC = 1; END IF; END TEST_CURSOR;
END JAVALINKTEST;
3.java环境，用的时eclispe，oracle 11g，java代码如下，需要的小伙伴自己引一下jdbc包吧
package cursorTest; import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.oracore.OracleType; public class cursorUse {
static ResultSet rs = null;
static Statement stmt = null;
static Connection conn = null;
static CallableStatement proc = null;
static int i; public static void main(String[] args) { try {
// 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 与数据库建立物理连接
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "easdb", "easdb");
// 调用 游标
proc = conn.prepareCall("call JAVALINKTEST.TEST_CURSOR(?,?)");
proc.setString(1, "hhe");
proc.registerOutParameter(2, OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet) proc.getObject(2); while (rs.next()) {
i+=1;
System.out.println("fid:" + rs.getString(1)+
" 编码："+rs.getString("fnumber")+" 名称："+rs.getString("fname_l2")); }
System.out.println("返回结果集：共"+i+"行");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} } }
运行结果如下：