package xx.qq.app; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import org.springframework.beans.factory.BeanFactory; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.mchange.v2.c3p0.ComboPooledDataSource; /** * @author Jack Zhang Email:fish2-2@163.com * @date 2011-08-22 */ public class AppTest { public static void main(String[] args) throws Exception { ApplicationContext context = new ClassPathXmlApplicationContext( new String[] { "applicationContext.xml" }); BeanFactory factory = (BeanFactory) context; ComboPooledDataSource dataSource = (ComboPooledDataSource) factory .getBean("dataSource"); Connection con = dataSource.getConnection(); CallableStatement st =con.prepareCall("{call package_user.PERSON_QUERY(?,?)}"); //st.setString(1, ""); st.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); st.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER); st.execute(); ResultSet rs = null; rs = (ResultSet)st.getObject(1); while(rs.next()) { System.out.println(rs.getObject(1)+" "+rs.getObject(2)); } int count =st.getInt(2); //获取输出参数 System.err.println(count); if (rs != null) rs.close(); if (st != null) st.close(); if (con != null) con.close(); } }
CREATE OR REPLACE PACKAGE package_user AS TYPE MY_RESULTSET_CURSOR IS REF CURSOR; PROCEDURE PERSON_QUERY(RS OUT MY_RESULTSET_CURSOR,C OUT INTEGER); END;
create or replace package body package_user AS PROCEDURE PERSON_QUERY(RS OUT MY_RESULTSET_CURSOR,C OUT INTEGER) AS BEGIN OPEN RS FOR select * from emp; update A set ename='AAA'; SELECT COUNT(*) INTO C from emp; END PERSON_QUERY; END;