IBM技术交流

posts - 1, comments - 0, trackbacks - 0, articles - 0

BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合

:: 管理

2009年1月9日

用java 调用 oracle 返回结果为游标的函数

oracle 函数
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));
       }
}

posted @ 2009-01-09 11:02 越战越勇阅读(126) | 评论 (0) | 编辑收藏

仅列出标题