jdbc调用数据库（函数，存储过程，包）

存储过程：

create or replace procedure inandoutpro(a in out number,b in out number) as
begin
       a:=a+b;
       b:=a-b;
       a:=a-b;
end inandoutpro;

java类调用存储过程：

Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "ronaldo");
		String procedure = "{call inandoutpro(?,?)}";
		CallableStatement cs = conn.prepareCall(procedure);
		cs.setInt(1, 5);
		cs.setInt(2, 7);
		cs.registerOutParameter(1, java.sql.Types.INTEGER);
		cs.registerOutParameter(2, java.sql.Types.INTEGER);

		cs.executeUpdate();

		int a = cs.getInt(1);
		int b = cs.getInt(2);
		System.out.println(a + "==" + b);

函数（行转列）：

create or replace function rowToCol(QuerySql varchar2) return varchar2 as
begin
     declare type cu_rtoc is ref cursor;
     proscu cu_rtoc;
     string_name varchar2(200);
     row_cu product%rowtype;
     begin
          open proscu for QuerySql;
          loop
              fetch proscu into row_cu;
              exit when proscu%notfound;
              string_name:=string_name||row_cu.pname||',';
          end loop;
          return rtrim(string_name,',');--去掉最后的逗号
     end;
end rowToCol;

java类调用函数：

Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "ronaldo");
		String procedure = "{?=call rowToCol(?)}";
		CallableStatement cs = conn.prepareCall(procedure);
		cs.registerOutParameter(1, Types.VARCHAR);// 获得返回的字符串
		String sql = "select * from product";
		cs.setString(2, sql);
		cs.executeUpdate();
		String names = cs.getString(1);
		System.out.println(names);
		conn.close();

包（游标）：

--package包（接口）
create or replace package pro_package as
       type cu_ref is ref cursor;
       procedure pro_product(cur_ref out cu_ref);
end pro_package;

--实现包（实现类）
create or replace package body pro_package as
      procedure pro_product(cur_ref out cu_ref) is
      begin
           open cur_ref for 'select * from product';
      end pro_product;
end pro_package;

java类调用包里的过程返回游标：

Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "ronaldo");
		String callsql = "{call pro_package.pro_product(?)}";
		OracleCallableStatement cs = (OracleCallableStatement) conn
				.prepareCall(callsql);
		cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
		cs.executeUpdate();
		ResultSet rs = null;
		rs = cs.getCursor(1);
		while (rs.next()) {
			System.out.println(rs.getString(1) + "==" + rs.getString(2) + "=="
					+ rs.getString(3));
		}
		rs.close();
		conn.close();