oracle 利用游标查询数据,Oracle 使用函数 function查询数据返回游标

create or replace function test111(itemNumber in varchar2) return SYS_REFCURSOR

is

return_cursor SYS_REFCURSOR;

begin

OPEN return_cursor FOR SELECT 'a' FROM dual WHERE 1 = itemNumber;

RETURN return_cursor;

end test111;

使用如下sql返回 游标，在pl sql developer可以直接点开查询结果

select test111(1) from dual;

适用条件： 在Sql语句过长时可以适用，避免在java代码中有过长的sql代码！

jdbc调用结果集

package com.dahuatech.job;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import oracle.jdbc.driver.OracleResultSet;

import oracle.jdbc.driver.OracleTypes;

public class Test {

public static void main(String[] args) throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@10.30.5.106:1521:agile9";

Connection conn = DriverManager.getConnection(url, "agile", "***");

String sql = "{? = call test111(?)}";

CallableStatement cst = conn.prepareCall(sql);

cst.registerOutParameter(1, OracleTypes.CURSOR);

cst.setString(2, "1");

cst.execute();

OracleResultSet rs = (OracleResultSet) cst.getObject(1);

while (rs.next()) {

System.out.println(rs.getString("a"));

}

}

}