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

mpfnajvjkd

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