存储过程

////调用存储过程方法

import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@SuppressWarnings("unchecked")
protected void doTestConnect() throws Throwable {
Connection con = null;
try {
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//192.168.7.190:1521/DSDB", "JS12110xc",
"JS12110xc");
try {
PreparedStatement pstmt = con
.prepareStatement("{call dbo.uspGetEmployeeManagers(?)}");
pstmt.setInt(1, 50);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {

}
rs.close();
pstmt.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} catch (ClassNotFoundException ce) {
throw ce;
} catch (Exception e) {
throw e;
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
throw e;
}
}

}


public static void executeStoredProcedure(Connection con) ...{
　　 try ...{
　　 CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
　　 cstmt.setInt(1, 5);
　　 cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
　　 cstmt.execute();
　　 System.out.println("MANAGER ID: " + cstmt.getInt(2));
　　 }
　　 catch (Exception e) ...{
　　 e.printStackTrace();
　　 }
　　}


public static void executeUpdateStoredProcedure(Connection con) ...{
　　 try ...{
　　 CallableStatement cstmt = con.prepareCall("{call dbo.UpdateTestTable(?, ?)}");
　　 cstmt.setString(1, "A");
　　 cstmt.setInt(2, 100);
　　 cstmt.execute();
　　 int count = cstmt.getUpdateCount();
　　 cstmt.close();
　　
　　 System.out.println("ROWS AFFECTED: " + count);
　　 }
　　 catch (Exception e) ...{
　　 e.printStackTrace();
　　 }
　　}

CallableStatement cstmt = con.prepareCall("{? = call dbo.CheckContactCity(?)}");

      cstmt.registerOutParameter(1, java.sql.Types.INTEGER);

      cstmt.setString(2, "Atlanta");

      cstmt.execute();

      System.out.println("RETURN STATUS: " + cstmt.getInt(1));

  

public static void executeSprocNoParams(Connection con) ...{
　　 try ...{
　　 Statement stmt = con.createStatement();
　　ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}");
　　
　　 while (rs.next()) ...{
　System.out.println(rs.getString("FormalName"));
　　}
　　rs.close();
　　stmt.close();
　 }
　　catch (Exception e) ...{
　　e.printStackTrace();
　　}
　　}



protected void doTestConnect(String begintime, String endtime)
throws Throwable {
Connection con = null;
try {
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//10.32.203.151:1521/DSDB", "dsdb",
"manager");
try {
PreparedStatement pstmt = con
.prepareStatement("{call dsdb.proc_stat_lbs(?, ?)}");

pstmt.setString(1, begintime);
pstmt.setString(2, endtime);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {

}
rs.close();
pstmt.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} catch (ClassNotFoundException ce) {
throw ce;
} catch (Exception e) {
throw e;
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
throw e;
}
}

}


存储过程：
create or replace procedure proc_stat_lbs as
  nowtime varchar(50);
begin
  nowtime := to_char(sysdate, 'yyyymmdd');

  DELETE FROM T_TEMP_QUERY_LBS;
  INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES (0, 0, 0, 0);
  INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES  (1, 0, 0, 0);
  INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES  (2, 0, 0, 0);
  INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES  (3, 0, 0, 0);

  UPDATE T_TEMP_QUERY_LBS SET
  REQ_COUNT = (select count(*) from ds_lbs_info where yysid = '1'
   and to_char(querytime, 'yyyymmdd')  >= nowtime)
  where yys = 1;

  UPDATE T_TEMP_QUERY_LBS SET
  SUCCESS_COUNT = (select count(*) from ds_lbs_info where yysid = '1' and senderx is not nulL
   and to_char(querytime, 'yyyymmdd')  >= nowtime)
  where yys = 1;

  UPDATE T_TEMP_QUERY_LBS SET
  REQ_COUNT = (select count(*) from ds_lbs_info where yysid = '3'
   and to_char(querytime, 'yyyymmdd')  >= nowtime)
  where yys = 3;

  UPDATE T_TEMP_QUERY_LBS SET
  SUCCESS_COUNT = (select count(*) from ds_lbs_info where yysid = '3' and senderx is not nulL
   and to_char(querytime, 'yyyymmdd')  >= nowtime)
  where yys = 3;
 
  update t_temp_query_lbs set req_count =
  (select sum(Req_count) from t_temP_query_lbs where yys <> 0) where yys = 0;

  update t_temp_query_lbs set SUCCESS_COUNT =
  (select sum(SUCCESS_COUNT) from t_temP_query_lbs where yys <> 0) where yys = 0;

END;



public String flex() throws Throwable {
Connection con = null;
try {
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//10.10.46.2:1521/orcl", "zwww",
"zwww");
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call zwww.flex}"); //用户名
rs.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} catch (ClassNotFoundException ce) {
throw ce;
} catch (Exception e) {
throw e;
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
throw e;
}
}
}



===========select count(1)用这个解决统计没有数字出现的的现=======

create or replace procedure test is
  ok  number;
  bad number;
  zs  number;
  i   number;
  j   number;
  k   number;
  okwl  number;
  badwl number;
  okfw  number;
  badfw number;
  oksj  number;
  badsj number;
  Cursor cursor1 is
     select distinct(t.district_code) as code from T_B_ORG t;
  my_record cursor1%rowtype;
begin

  open cursor1;

  delete from t_b_flex;
  --一个区域多个用户节点上每类资源可用性
  i := 1;
  if cursor1%isopen then
    loop
      fetch cursor1
        into my_record;
      if cursor1%found then
     
        k := 1;
        --  select count(1) into j from T_B_ORG t where t.district_code =my_record.code;
        -- while k<=j loop
        --   begin
        --安全设备
        select count(1)
          into ok
          from (select a.asset_type, a.asset_state
                  from t_b_topsec_asset a
                 where a.ip in
                       (select t.ip
                          from T_B_ORG t
                         where t.district_code = my_record.code)
                   and a.asset_state = '0'
                 group by a.asset_type, a.asset_state);
        --select t.ip from T_B_ORG t where t.district_code =my_record.code and rownum=k
        select count(1)
          into bad
          from (select decode(count(1), null, '0', count(1)) c2
                  from t_b_topsec_asset a
                 where a.ip in
                       (select t.ip
                          from T_B_ORG t
                         where t.district_code = my_record.code)
                   and a.asset_state = '1'
                 group by a.asset_type, a.asset_state) /*,a.ip*/;
     
        --网络设备
        select count(1) into okwl
          from t_b_resource t
          where t.ip in (select t.ip
                          from T_B_ORG t
                         where t.district_code = my_record.code)
           and t.is_netdevice = 1
           and t.resource_status like 'true';
          
         select count(1) into badwl
          from t_b_resource t
          where t.ip in (select t.ip
                          from T_B_ORG t
                         where t.district_code = my_record.code)
           and t.is_netdevice = 1
           and t.resource_status like 'flase';
       --服务器及存储
        select count(1) into okfw
          from t_b_resource t
          where t.ip in (select t.ip
                          from T_B_ORG t
                         where t.district_code = my_record.code)
           and t.is_netdevice = 0
           and t.resource_status like 'true';
          
         select count(1) into badfw
          from t_b_resource t
          where t.ip in (select t.ip
                          from T_B_ORG t
                         where t.district_code = my_record.code)
           and t.is_netdevice = 0
           and t.resource_status like 'flase';
         --事件工单
          select count(1) into oksj
          from T_B_TROUBLEWORK t
          where t.ip in (select t.ip
                          from T_B_ORG t
                         where t.district_code = my_record.code)
           and t.gzsfjj  like '是';
           
           select count(1) into badsj
          from T_B_TROUBLEWORK t
          where t.ip in (select t.ip
                          from T_B_ORG t
                         where t.district_code = my_record.code)
           and t.gzsfjj  not like '是'; 
       insert into t_b_flex
          (id, okaq, badaq, okwl, badwl,okfw, badfw,oksj, badsj ,dis_code)
       values
          (i, ok, bad, okwl, badwl,okfw, badfw,oksj, badsj , my_record.code);
     
        i := i + 1;
       
        -- end;
        -- end loop;
     
      else
        exit;
      end if;
    end loop;
  else
    dbms_output.put_line('游标没有打开');
  end if;
  close cursor1;

  commit;

exception
  when others then
    rollback;
 
end test;



返回游标形式输出参数的存数过程



public static void pro() throws Exception{
Connection con = null;
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//10.10.46.2:1521/orcl", "zwww",
"zwww");
CallableStatement cs = con.prepareCall("{ call retCursor(?) }");    
cs.registerOutParameter(1,OracleTypes.CURSOR);    
cs.execute();    
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);    
while(rs.next())    
{    
    System.out.println(rs.getString(1)+"  "+rs.getString(2)+"  "+rs.getString(3)+"  "+ rs.getString(4)+"  "+rs.getString(5));    
}  

}



create or replace procedure retCursor(ret_cursor out sys_refcursor)is   
ret_cursor_value  sys_refcursor;    
begin   
open ret_cursor_value for select * from t_b_processlog;    
ret_cursor:=ret_cursor_value;    
end retCursor;   



http://qinya.iteye.com/blog/622300



http://wenku.baidu.com/link?url=M-IRJaygbBWptMALrzbYf5ZwBGxj-JOd5yMnZiay_vZtIQJCmihUwjuFjhKR59LlEztFhPHJB5NFwuKIFxaXRv-kQ3BTvPG0ORWeOnAkTY3