天天看點

java 通過調用存儲過程擷取結果集

        一般在java中,資料查詢是通過statement, preparedstatement擷取結果集,今天向大家介紹通過callablestatement調用存儲過程,進而擷取結果集.        本文是所用的資料庫為oracle.       一.  測試資料庫表:               

sql 代碼

create table wilent_user(  

                id number(5) primary key,  

                name varchar2(100),  

                sex varchar2(1),    --y為男,f為女;  

                group_id number(5),  

                teach varchar2(50)    --學曆;  

                );  

                create table wilent_group(  

                    id number(5) primary key,  

                    name varchar2(100)  

                 );  

                        insert into wilent_group values(1,'組1');  

        insert into wilent_group values(2,'組2');  

        insert into wilent_group values(3,'組3');  

        insert into wilent_group values(4,'組4');  

        insert into wilent_group values(5,'組5');  

        insert into wilent_user values(1,'吳','y',1,'大專');  

        insert into wilent_user values(2,'李','y',1,'大專');  

        insert into wilent_user values(3,'趙','n',2,'大學');  

        insert into wilent_user values(4,'金','y',2,'高中');  

        insert into wilent_user values(5,'錢','n',2,'大專');  

        insert into wilent_user values(6,'孫','n',1,'大專');  

        insert into wilent_user values(7,'高','y',3,'大學');  

        insert into wilent_user values(8,'宋','n',3,'高中');  

        insert into wilent_user values(9,'伍','y',3,'大專');  

        insert into wilent_user values(10,'歐','y',4,'大學');  

        insert into wilent_user values(11,'莊','n',4,'碩士');  

        insert into wilent_user values(12,'紀','y',4,'大學');  

        insert into wilent_user values(13,'陳','y',5,'大專');  

        insert into wilent_user values(14,'龍','n',5,'大專');  

        insert into wilent_user values(15,'袁','y',5,'高中');  

        insert into wilent_user values(16,'楊','y',1,'大學');  

        insert into wilent_user values(17,'江','n',1,'大專');  

        insert into wilent_user values(18,'劉','y',1,'碩士');  

        insert into wilent_user values(19,'郭','n',3,'碩士');  

        insert into wilent_user values(20,'張','y',3,'大專');  

        insert into wilent_user values(21,'文','n',3,'碩士');  

        insert into wilent_user values(22,'李','n',4,'大專');  

        insert into wilent_user values(23,'梅','y',4,'大學');  

        insert into wilent_user values(24,'王','n',4,'大專');  

        insert into wilent_user values(25,'呂','n',5,'高中');  

        insert into wilent_user values(26,'範','y',5,'大學');  

        insert into wilent_user values(27,'許','n',1,'大專');  

        insert into wilent_user values(28,'墨','y',1,'高中');  

        insert into wilent_user values(29,'孔','n',1,'大學');  

        insert into wilent_user values(30,'蔡','y',1,'大專');  

        二.  oracle 存儲過程               

--自定義類型; 

create or replace type wilent_row_table as object  

(  

       group_name varchar2(100),  

       group_count number(4),  

       male_count number(4),  

       woman_count number(4),  

       da_count number(4),  

       ben_count number(4)  

);  

/  

--定義一個嵌套表類型;

create or replace type wilent_tab_type is table of wilent_row_table;  

--傳回一個遊标類型;  

create or replace package wilent_types as   

       type cursor_type is ref cursor;  

end wilent_types;  

create or replace procedure wilent_group_count(recordset out wilent_types.cursor_type)  

as  

  v_tab wilent_tab_type := wilent_tab_type();               

  index_max number(4);                         --wilent_group最大的id;  

  index_min number(4);                         --wilent_group最小的id;  

  index_for number(4);  

  group_name varchar2(100);  

  user_count number(4);  

  male_count number(4);  

  woman_count number(4);  

  da_count number(4);  

  ben_count number(4);  

begin  

     dbms_output.put_line('as');  

     select max(g.id) into index_max from wilent_group g;  

     --dbms_output.put_line(index_max);  

     select min(g.id) into index_min from wilent_group g;  

     --dbms_output.put_line(index_min);  

     for index_for in index_min..index_max loop  

         --添加新記錄;  

         v_tab.extend;  

         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;  

         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';  

         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='大學';  

         --把記錄寫入;  

         v_tab(v_tab.last) := wilent_row_table(group_name,user_count,male_count,woman_count,da_count,ben_count);  

     end loop;  

     --把記錄放在遊标裡;  

     open recordset for  

      --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;  

end wilent_group_count;  

--測試wilent_group_count();  

declare  

  recordset wilent_types.cursor_type;  

    wilent_group_count(recordset);  

end;  

                三. java代碼:           

java 代碼

package com.wilent.oracle;  

import java.sql.callablestatement;  

import java.sql.connection;  

import java.sql.resultset;  

import java.sql.sqlexception;  

import oracle.jdbc.driver.oracletypes;  

import com.wilent.db.connectionmanager;  

public class testprocedure {  

    public static void main(string[] args) {  

        //獲得conn連接配接,讀者可以自行寫;  

        connection conn = connectionmanager.getconnection();  

        resultset rs = null;  

        try {  

            callablestatement proc = conn.preparecall("{call wilent_group_count(?)}");  

            proc.registeroutparameter(1, oracletypes.cursor);  

            proc.execute();  

            rs = (resultset) proc.getobject(1);  

            system.out.println("組名\t總計\t男性\t女性\t大專\t大學");  

            while(rs.next())  

            {  

                stringbuffer buffer = new stringbuffer();  

                buffer.append(rs.getstring("group_name"));  

                buffer.append("\t");  

                buffer.append(rs.getint("group_count"));  

                buffer.append(rs.getint("male_count"));  

                buffer.append(rs.getint("woman_count"));  

                buffer.append(rs.getint("da_count"));  

                buffer.append(rs.getint("ben_count"));  

                system.out.println(buffer.tostring());  

            }  

        } catch (exception e) {  

            e.printstacktrace();  

        }  

        finally{  

            try {  

                conn.close();  

            } catch (sqlexception e) {  

                e.printstacktrace();  

    }  

    四. 運作結果

    組名    總計    男性    女性    大專    大學     組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