达梦数据库中如何用jdbc获取存储过程中引用游标返回的结果集

1.创建一个包

CREATE OR REPLACE PACKAGE PKG_TEST
 IS
   TYPE CURSOR_TYPE IS REF CURSOR;      --定义游标
   PROCEDURE TEST_CURSOR(INPUT varchar, CURSOR_BACK OUT CURSOR_TYPE);
 
END PKG_TEST;

CREATE OR REPLACE PACKAGE BODY PKG_TEST IS

PROCEDURE TEST_CURSOR(INPUT varchar, CURSOR_BACK OUT CURSOR_TYPE)
IS
BEGIN

OPEN CURSOR_BACK FOR SELECT * FROM sysobjects WHERE TYPE$ = INPUT;


END TEST_CURSOR;
END PKG_TEST;

CALL PKG_TEST.TEST_CURSOR('SCHOBJ',NULL);

 

2.编写一个java程序调用这个存储过程，获取存储过程的out参数返回的引用游标的结果集

import java.sql.*;

import dm.jdbc.driver.DmdbType;

public class test {
	
	  String jdbcString = "dm.jdbc.driver.DmDriver";
	  String urlString = "jdbc:dm://192.168.104.53:35236";
	  //  定义连接用户名 
	  String userName = "SYSDBA";  
	  //  定义连接用户口令 
	  String password = "SYSDBA";  
	  //  定义连接对象 
	  static Connection conn = null;  
	  /* 加载JDBC 驱动程序 
	    * @throws SQLException  异常  */  
	  public void loadJdbcDriver() throws SQLException { 
	    try {  
	      System.out.println("Loading JDBC Driver...");  
	      //  加载JDBC 驱动程序 
	      Class.forName(jdbcString); 
	    } catch (ClassNotFoundException e) { 
	      throw new SQLException("Load JDBC Driver Error : " + e.getMessage()); 
	    } catch (Exception ex) {  
	      throw new SQLException("Load JDBC Driver Error : "  
	          + ex.getMessage());  
	    } 
	  } 
	  
	  public void connect() throws SQLException { 
		  try {  
		    System.out.println("Connecting to DM Server..."); 
		    //  连接DM 数据库 
		    conn = DriverManager.getConnection(urlString, userName, password);  
		  } catch (SQLException e) { 
			    System.out.println("error_code: " + e.getErrorCode());
		          System.out.println("sql_stat: " + e.getSQLState());
		          System.out.println("message: " + e.getMessage());
		          System.out.println("local_message: " + e.getLocalizedMessage());
		      
		  } 
		} 
	  
	  
	  public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub
		    test basicApp = new test(); 
		//  加载驱动程序 
		    basicApp.loadJdbcDriver(); 
		//  连接DM 数据库 
		    basicApp.connect(); 
		    
		    CallableStatement cstmt = conn.prepareCall("call PKG_TEST.TEST_CURSOR(?, ?)");
	        cstmt.setString(1, "SCHOBJ");
	        cstmt.registerOutParameter(2, DmdbType.CURSOR);
	        cstmt.execute();

	        ResultSet rs = (ResultSet)cstmt.getObject(2);
	        while (rs.next())
	        {
	            System.out.println(rs.getString(1));
	        }
	        rs.close();

	        cstmt.close();
	        conn.close();

		    conn.close();

		}
	  
}

 

 

更多资讯请上达梦技术社区了解： https://eco.dameng.com