java 透过调用存储过程获取结果集

java 通过调用存储过程获取结果集

转自 http://wilent.iteye.com/blog/40704

??? ??? 一般在java中,数据查询是通过Statement, PreparedStatement获取结果集,今天向大家介绍通过CallableStatement调用存储过程,从而获取结果集.
??? ?? 本文是所用的数据库为oracle.
????? 一.? 测试数据库表:

sql 代码?

1. create?table?wilent_user(??????????????????id?number(5)?primary?key,??
2. ????????????????name?varchar2(100),??????????????????sex?varchar2(1),????--Y为男,F为女;??
3. ????????????????group_id?number(5),??????????????????teach?varchar2(50)????--学历;??
4. ????????????????);????
5. ????????????????create?table?wilent_group(??????????????????????id?number(5)?primary?key,??
6. ????????????????????name?varchar2(100)???????????????????);??
7. ?????????????????????????????????????????insert?into?wilent_group?values(1,'组1');??
8. ????????insert?into?wilent_group?values(2,'组2');??????????insert?into?wilent_group?values(3,'组3');??
9. ????????insert?into?wilent_group?values(4,'组4');??????????insert?into?wilent_group?values(5,'组5');??
10. ?????????????????insert?into?wilent_user?values(1,'吴','Y',1,'大专');??
11. ????????insert?into?wilent_user?values(2,'李','Y',1,'大专');??????????insert?into?wilent_user?values(3,'赵','N',2,'本科');??
12. ????????insert?into?wilent_user?values(4,'金','Y',2,'高中');??????????insert?into?wilent_user?values(5,'钱','N',2,'大专');??
13. ????????insert?into?wilent_user?values(6,'孙','N',1,'大专');??????????insert?into?wilent_user?values(7,'高','Y',3,'本科');??
14. ????????insert?into?wilent_user?values(8,'宋','N',3,'高中');??????????insert?into?wilent_user?values(9,'伍','Y',3,'大专');??
15. ????????insert?into?wilent_user?values(10,'欧','Y',4,'本科');??????????insert?into?wilent_user?values(11,'庄','N',4,'硕士');??
16. ????????insert?into?wilent_user?values(12,'纪','Y',4,'本科');??????????insert?into?wilent_user?values(13,'陈','Y',5,'大专');??
17. ????????insert?into?wilent_user?values(14,'龙','N',5,'大专');??????????insert?into?wilent_user?values(15,'袁','Y',5,'高中');??
18. ????????insert?into?wilent_user?values(16,'杨','Y',1,'本科');??????????insert?into?wilent_user?values(17,'江','N',1,'大专');??
19. ????????insert?into?wilent_user?values(18,'刘','Y',1,'硕士');??????????insert?into?wilent_user?values(19,'郭','N',3,'硕士');??
20. ????????insert?into?wilent_user?values(20,'张','Y',3,'大专');??????????insert?into?wilent_user?values(21,'文','N',3,'硕士');??
21. ????????insert?into?wilent_user?values(22,'李','N',4,'大专');??????????insert?into?wilent_user?values(23,'梅','Y',4,'本科');??
22. ????????insert?into?wilent_user?values(24,'王','N',4,'大专');??????????insert?into?wilent_user?values(25,'吕','N',5,'高中');??
23. ????????insert?into?wilent_user?values(26,'范','Y',5,'本科');??????????insert?into?wilent_user?values(27,'许','N',1,'大专');??
24. ????????insert?into?wilent_user?values(28,'墨','Y',1,'高中');??????????insert?into?wilent_user?values(29,'孔','N',1,'本科');??
25. ????????insert?into?wilent_user?values(30,'蔡','Y',1,'大专');??

??? ?? 二.? oracle 存储过程

??? ?? ?? ??sql 代码?

1. --自定义类型;?
   Create?Or?Replace?Type?wilent_row_table?As?Object??
2. (?????????group_name?Varchar2(100),??
3. ???????group_count?Number(4),?????????male_count?Number(4),??
4. ???????woman_count?Number(4),?????????da_count?Number(4),??
5. ???????ben_count?Number(4)??);??
6. /????
7. --定义一个嵌套表类型;Create?Or?Replace?Type?wilent_tab_type?Is?Table?Of?wilent_row_table;??
8. /??--返回一个游标类型;??
9. Create?Or?Replace?Package?wilent_types?As??????????Type?cursor_type?Is?Ref?Cursor;??
10. End?wilent_types;??/??
11. Create?Or?Replace?Procedure?wilent_group_count(recordSet?Out?wilent_types.cursor_type)??As??
12. ??v_tab?wilent_tab_type?:=?wilent_tab_type();?????????????????index_max?Number(4);?????????????????????????--wilent_group最大的id;??
13. ??index_min?Number(4);?????????????????????????--wilent_group最小的id;????index_for?Number(4);??
14. ??????group_name?Varchar2(100);??
15. ??user_count?Number(4);????male_count?Number(4);??
16. ??woman_count?Number(4);????da_count?Number(4);??
17. ??ben_count?Number(4);??Begin??
18. ?????dbms_output.put_line('as');???????Select?Max(g.Id)?Into?index_max?From?wilent_group?g;??
19. ?????--dbms_output.put_line(index_max);???????Select?Min(g.Id)?Into?index_min?From?wilent_group?g;??
20. ?????--dbms_output.put_line(index_min);???????For?index_for?In?Index_min..index_max?Loop??
21. ?????????--添加新记录;???????????v_tab.Extend;??
22. ?????????Select?Name?Into?group_name?From?wilent_group?Where?Id=index_for;???????????Select?Count(*)?Into?user_count?From?wilent_user?u,?wilent_group?g?Where?u.group_id=g.Id?And?g.Id=index_for;??
23. ?????????Select?Count(*)?Into?male_count?From?wilent_user?u,?wilent_group?g?Where?u.group_id=g.Id?And?g.Id=index_for?And?sex='Y';???????????Select?Count(*)?Into?woman_count?From?wilent_user?u,?wilent_group?g?Where?u.group_id=g.Id?And?g.Id=index_for?And?sex='N';??
24. ?????????Select?Count(*)?Into?da_count?From?wilent_user?u,?wilent_group?g?Where?u.group_id=g.Id?And?g.Id=index_for?And?teach='大专';???????????Select?Count(*)?Into?ben_count?From?wilent_user?u,?wilent_group?g?Where?u.group_id=g.Id?And?g.Id=index_for?And?teach='本科';??
25. ?????????--把记录写入;???????????v_tab(v_tab.Last)?:=?wilent_row_table(group_name,user_count,male_count,woman_count,da_count,ben_count);??
26. ?????End?Loop;?????????
27. ?????--把记录放在游标里;???????Open?recordset?For??
28. ????? --Table(Cast(v_tab?As?wilent_tab_type))目的是把v_tab强转为wilent_tab_type表?
    ??????????Select?group_name,group_count?,male_count?,woman_count?,da_count?,ben_count??From?Table(Cast(v_tab?As?wilent_tab_type))?Order?By?group_name;??
29. End?wilent_group_count;??/??
30. ??--测试wilent_group_count();??
31. declare????recordset?wilent_types.cursor_type;??
32. Begin??????wilent_group_count(recordset);??
33. End;??

?????
????????? 三. java代码:

??????????java 代码?

1. package?com.wilent.oracle;????
2. import?java.sql.CallableStatement;??import?java.sql.Connection;??
3. import?java.sql.ResultSet;??import?java.sql.SQLException;??
4. ??import?oracle.jdbc.driver.OracleTypes;??
5. ??import?com.wilent.db.ConnectionManager;??
6. ??public?class?TestProcedure?{??
7. ????public?static?void?main(String[]?args)?{??????????//获得conn连接,读者可以自行写;??
8. ????????Connection?conn?=?ConnectionManager.getConnection();??????????ResultSet?rs?=?null;??
9. ????????try?{??????????????CallableStatement?proc?=?conn.prepareCall("{call?wilent_group_count(?)}");??
10. ????????????proc.registerOutParameter(1,?OracleTypes.CURSOR);??????????????proc.execute();??
11. ??????????????????????????rs?=?(ResultSet)?proc.getObject(1);??
12. ????????????System.out.println("组名\t总计\t男性\t女性\t大专\t本科");??????????????while(rs.next())??
13. ????????????{??????????????????StringBuffer?buffer?=?new?StringBuffer();??
14. ????????????????buffer.append(rs.getString("group_name"));??????????????????buffer.append("\t");??
15. ????????????????buffer.append(rs.getInt("group_count"));??????????????????buffer.append("\t");??
16. ????????????????buffer.append(rs.getInt("male_count"));??????????????????buffer.append("\t");??
17. ????????????????buffer.append(rs.getInt("woman_count"));??????????????????buffer.append("\t");??
18. ????????????????buffer.append(rs.getInt("da_count"));??????????????????buffer.append("\t");??
19. ????????????????buffer.append(rs.getInt("ben_count"));??????????????????System.out.println(buffer.toString());??
20. ????????????}??????????}?catch?(Exception?e)?{??
21. ????????????e.printStackTrace();??????????}??
22. ????????finally{??????????????try?{??
23. ????????????????conn.close();??????????????}?catch?(SQLException?e)?{??
24. ????????????????e.printStackTrace();??????????????}??
25. ????????}??????}??
26. }?
    

??? 四. 运行结果

??? 组名??? 总计??? 男性??? 女性??? 大专??? 本科
??? 组1??? 10??? ??? 6??? ? 4????? 6????? 2
??? 组2??? 3??? ???? 1????? 2????? 1????? 1
??? 组3??? 6??? ???? 3????? 3????? 2????? 1
??? 组4??? 6??? ???? 3????? 3????? 2????? 3
??? 组5??? 5??? ???? 3????? 2????? 2????? 1
???

查看更多 下一篇