语源科技BlogJava-IBM技术交流http://www.blogjava.net/xiuyith/zh-cnThu, 07 May 2026 07:23:50 GMTThu, 07 May 2026 07:23:50 GMT60用java 调用 oracle 返回结果为游标的函数http://www.blogjava.net/xiuyith/archive/2009/01/09/250635.html越战越勇越战越勇Fri, 09 Jan 2009 03:02:00 GMThttp://www.blogjava.net/xiuyith/archive/2009/01/09/250635.htmlhttp://www.blogjava.net/xiuyith/comments/250635.htmlhttp://www.blogjava.net/xiuyith/archive/2009/01/09/250635.html#Feedback0http://www.blogjava.net/xiuyith/comments/commentRss/250635.htmlhttp://www.blogjava.net/xiuyith/services/trackbacks/250635.htmloracle  函数
Create or REPLACE FUNCTION AAAAA (
    P_USER_ID_I         IN  VARCHAR2             --接收输入参数
)
RETURN   SYS_REFCURSOR
AS
    P_RESULT_SET_O         SYS_REFCURSOR; --返回游标
    X_SQL VARCHAR2(200);
BEGIN
     X_SQL :='select user_id,user_name,email from user_mst where user_id like ''%' || P_USER_ID_I ||'%''';  --生成SQL语句
     OPEN P_RESULT_SET_O FOR X_SQL;
     RETURN P_RESULT_SET_O;   --返回游标
END AAAAA;

JAVA 程序调用
CallableStatement cstmt = null;
ResultSet rs = null;
try {
String callSql = "{? = call AAAAA(?)}";
cstmt = conn.prepareCall(callSql);
    
cstmt.setString(2, "userName");

cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

cstmt.execute();

rs = (ResultSet) cstmt.getObject(1);
if (rs != null) {
       System.out.print("usercd");
       System.out.print("userName");                
       System.out.println("EMAIL");
       while (rs.next()) {        
    System.out.print(rs.getString(1)+"   ");
    System.out.print(rs.getString(2)+"   ");                
    System.out.println(rs.getString(3));
       }    
}



越战越勇 2009-01-09 11:02 发表评论
]]>