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

最新推荐文章于 2018-04-12 15:23:35 发布

iteye_10239

最新推荐文章于 2018-04-12 15:23:35 发布

阅读量173

点赞数

分类专栏： oracle 文章标签： Java Oracle SQL JDBC

oracle 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

  一：无返回值的存储过程
 存储过程：
    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) {
       }
     }
   }
}