java中调用ORACLE存储过程实例（一）

 一：无返回值的存储过程

存储过程：

   create or replace procedure DATA_TEST_PROC  (dqBM in varchar2,strTime in varchar2)

  is

      type cur is ref cursor ; --定义游标
      TABLE_CUR cur;  --设置游标别名
      tabel_name_count number; --定义number类型变量
      isExite number; --同上
      i number :=1;  -- 定义number类型变量并赋初始值

BEGIN -- 1

     Open TABLE_CUR for
      'select count(table_name) from user_tables where table_name like '''||UPPER(dqBM)||'HISTORY%''';
     FETCH TABLE_CUR INTO tabel_name_count; --获取游标存储值

     CLOSE TABLE_CUR; -- 关闭游标

     if tabel_name_count > 0

        then

             begin  -- 2

                  for i in 1..table_name_count

                    loop

                       Open Table_CUR for

                          ' select count(*) from '|| dqBM|| ' analysis'||i||' where id like ''' || strTime||'% ''';

                       FETCH TABLE_CUR INTO isExite;

                       CLOSE TABLE_CUR;

                       if isExite > 0

                          then

                            begin --  3

                               execute immediate   ' delete from '||dqBM||'analysis'||i||' where id like ''' ||strTime||'%'';

                                commit;

                                DBMS_OUTPUT.put_line(strTime||' 数据已删除');

                            end;--  3

                    end loop;

             end; -- 2

END;-- 1

 

本存储过程实现查询数据库中已存在指定数据并删除。

 

然后呢，在java里调用时就用下面的代码：

 

package com.hyq.src;

import java.sql.*;
import java.sql.ResultSet;

public class TestProcedureOne {
   public TestProcedureOne() {
   }
   public static void main(String[] args ){
     String driver = "oracle.jdbc.driver.OracleDriver";
     String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: imsbase";
     Statement stmt = null;
     ResultSet rs = null;
     Connection conn = null;
     CallableStatement cstmt = null;

     try {
       Class.forName(driver);
       conn =   DriverManager.getConnection(strUrl, " mis", " mis ");
       CallableStatement proc = null;
       proc = conn.prepareCall("{ call HBPGMIS_MONITOR.DATAANALYSIS_DAY_DQ(?,?)}");
       proc.setString(1, "bd");
       proc.setString(2, "20100301");
       proc.execute();
     }
     catch (SQLException ex2) {
       ex2.printStackTrace();
     }
     catch (Exception ex2) {
       ex2.printStackTrace();
     }
     finally{
       try {
         if(rs != null){
           rs.close();
           if(stmt!=null){
             stmt.close();
           }
           if(conn!=null){
             conn.close();
           }
         }
       }
       catch (SQLException ex1) {
       }
     }
   }
}

  

二、调用有返回值的存储过程

 

   存储过程如下：

  

create or replace procedure proc_getHisTabCount (dqbm in varchar2, hisTabCount OUT integer)
   is
    --声明全局变量
      var_sql String(32765);
      sql_select varchar2(1000);
      num_count number;
      type CurType is ref cursor;-- 定义游标引用
      curTerm CurType; -- 设置游标别名
    BEGIN
        Open curTerm for
        'select count(*) from user_all_tables where table_name like '''||UPPER(dqbm)||'HISTORY%''';
        FETCH curTerm INTO num_count;
        CLOSE curTerm;
        if num_count > 0
          then
          hisTabCount  := num_count;
          end if;
    END proc_getHisTabCount;

 

   以上存储过程有两个参数：一个输入参数，一个输出参数。

   实现查询用户对象中表名称like 'HISTORY%'的个数。并返回。

 

   对应java调用如下：

 

  

import java.sql.*;
import java.sql.ResultSet;

public class TestProcedureOne {
   public TestProcedureOne() {
   }
   public static void main(String[] args ){
     String driver = "oracle.jdbc.driver.OracleDriver";
     String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: imsbase";
     Statement stmt = null;
     ResultSet rs = null;
     Connection conn = null;
     CallableStatement cstmt = null;

     try {
       Class.forName(driver);
       conn =   DriverManager.getConnection(strUrl, " mis", " mis ");
       CallableStatement proc = null;
       proc = conn.prepareCall("{ call HBPGMIS_MONITOR.proc_getHisTabCount(?,?)}");
				    if(proc != null)
				    {
				    	proc.setString(1, "bd"); //设置第一个参数，set输入参数,注意第一个参数是存储过程中的列值
				    	proc.registerOutParameter(2,Types.INTEGER); // 设置第二个参数，注册输出参数 ，若有多个参数，则注册多个几个
				    	proc.execute(); //提交存储过程
				    	output = proc.getInt(2); //以字符串的形式获取输出参数。
				    	System.out.println(output);
				    }
     }
     catch (SQLException ex2) {
       ex2.printStackTrace();
     }
     catch (Exception ex2) {
       ex2.printStackTrace();
     }
     finally{
       try {
         if(rs != null){
           rs.close();
           if(stmt!=null){
             stmt.close();
           }
           if(conn!=null){
             conn.close();
           }
         }
       }
       catch (SQLException ex1) {
       }
     }
   }
}

 其中，output = proc.getInt(2); 是过去存储过程对应的列out参数列。如果out在第三列，则getInt(3)，若有多个out参数，则注册（.registerOutParameter）多个值，并按列号取值。

 

三、返回列表值

  由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，但可以用游标存储。

1。 应用package，分两部分做。

     a。   建一个程序包，包内只定义一个游标。如下：

   

  create or replace package pack_cursor is
  -- Author  : ADMINISTRATOR
  -- Created : 2010-4-8 10:29:16
  -- Purpose : 创建游标获取数据列表
  -- Public type declarations 公共类型声明
  type TYPE_CURS is REF CURSOR; --创建游标引用

  end pack_cursor; 

   

    b.  建立存储过程，如：

   

create or replace procedure proc_getcursor_value(p_cursor out PACK_CURSOR.TYPE_CURS)
is
type type_cur is ref cursor;
term_type type_cur;
cur_value varchar2(20);
rows_num integer;
begin
  Open term_type for
    select name from measureclass t where display = '3';
  loop
    fetch term_type into cur_value; -- 从游标p_cursor中读取值
    exit  when term_type%notfound; --(dbms_sql.fetch_rows(p_cursor)>0) 通过dbms_sql.fetch_rows（p_cursor）获取游标的行
    if term_type%found
      then 
            dbms_output.put_line(cur_value);
    end if;
  end loop;
  close term_type;
end proc_getcursor_value;

  

以下是java代码调用：

 

import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;


public class TestProcedureTHREE {
   public TestProcedureTHREE() {
   }
   public static void main(String[] args ){
     String driver = "oracle.jdbc.driver.OracleDriver";
     String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:imsbase";
     Statement stmt = null;
     ResultSet rs = null;
     Connection conn = null;

     try {
       Class.forName(driver);
       conn =   DriverManager.getConnection(strUrl, "mis", "mis");
       System.out.println("调用存储过程！：");
     ResultSet rSet = null;
     CallableStatement  proc  = con.prepareCall("{ call HBPGMIS_MONITOR.proc_getcursor_value(?)}");
     System.out.println("注册参数：");
     proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
     System.out.println("提交！：");
     proc.execute();
     System.out.println("执行完毕！：");
     rSet = (ResultSet)proc.getObject(1);
     System.out.println("查询：");
     while (rSet.next()) 
     {
            System.out.println(rSet.getString(1));      
     }     

   }
     catch (SQLException ex2) {
       ex2.printStackTrace();
     }
     catch (Exception ex2) {
       ex2.printStackTrace();
     }
     finally{
       try {
         if(rs != null){
           rs.close();
           if(stmt!=null){
             stmt.close();
           }
           if(conn!=null){
             conn.close();
           }
         }
       }
       catch (SQLException ex1) {
       }
     }
   }
}