数据库中的包test下的函数getTodoList函数:
- create or replace package test is---包头
- type cursorref is ref cursor;---定义游标
- function getTodoList(userid varchar2, username varchar2) return cursorref;--函数
- end test ;
- create or replace package body test is-----包体
- function getTodoList
- (userid varchar2,
- username varchar2)
- return cursorref
- is
- vs_userid varchar2(200);
- vs_sql varchar2(2000);
- p_cursor cursorref;
- begin
- vs_sql :='select Email,School,Address from userInfo where id='||userid||' name='||username;
- open p_cursor for vs_sql;
- return p_cursor;
- end;
- end test;
java代码调用:
- public List returnInfo(){
- List list=new ArrayList();
- try {
- Context initCtx=new InitialContext();//InitialContext此类是执行命名操作的初始上下文
- DataSource ds=(DataSource)initCtx.lookup("java:comp/env/jdbc/LiferayPool");
- Connection conn=ds.getConnection();
- CallableStatement callsta=conn.prepareCall("{?=call pkg_flow.getTodoList(?,?)}");
- callsta.setString(2, "001");
- callsta.setString(3, "laozhang");
- callsta.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
- callsta.execute();
- ResultSet rs=(ResultSet)callsta.getObject(1);
- if(rs!=null){
- while(rs.next()){
- System.out.println(rs.getString("email")+"___"+rs.getString("school")+"___"+rs.getString("address"));
- //可从rs取值放入到list中......
- }
- }
- } catch (NamingException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return list;
- }