jdbc调用存储过程和函数

来源：互联网发布：知乎可以离线阅读吗编辑：程序博客网时间：2024/06/05 19:43

一，调用函数

1、创建表

create table stud(
sid int,
sname varchar2(50),
age number,
score number(4,2))
并插入一些数据（自己手动插入一些吧）
2、创建函数

create or replace function fun_getScores(
       v_age in stud.age%type
)
return number
is
       v_score number;
begin

       select sum(score) into v_score from stud where age > v_age;
       return v_score;

--异常处理
exception
       when others then
       dbms_output.put_line(sqlcode||sqlerrm);
       return -1;
end;
3、jdbc中调用

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;

public class TestFun {

public void test(){

  Connection con = null;
  Statement st = null;
  ResultSet rs = null;
  CallableStatement cst = null;
  try{
   Class.forName("oracle.jdbc.driver.OracleDriver");
   //下面的tan是数据库名，默认是orcl，love是访问密码，默认是tiger,
          //1521是端口号，默认是1521
   con = DriverManager.getConnection

("jdbc:oracle:thin:@192.168.1.103:1521:tan","scott","love");
   String sql = "{? = call fun_getScores(?)}";
   cst = con.prepareCall(sql);
   cst.registerOutParameter(1, Types.NUMERIC);
   cst.setDouble(2, 2);
   cst.execute();
   double result = cst.getDouble(1);

   System.out.println("年龄超过2岁学生的成绩总和 "+result);
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   try{
    if(rs !=null){
     rs.close();
    }
    if(st!=null){
     st.close();
    }
    if(cst != null){
     cst.close();
    }
    if(con != null){
     con.close();
    }
   }catch(Exception e){
    e.printStackTrace();
   }
  }

}

public static void main(String[] args) {
  // TODO Auto-generated method stub
  TestFun tf = new TestFun();
  tf.test();
}

}

二，调用存储过程

1、创建表：

     create table stud(
            sid int,
            sname varchar2(50)
     )
     并插入一条数据
     insert into stud values(1,'Tom')

2、通过包定义一个游标

     create or replace package pack_stud
     is
     --声明一个游标类型
            type cur is ref cursor;
     end;
3、创建存储过程

     create or replace procedure pro_select_stud(
            my_cur out pack_stud.cur
     )
     is
     begin
            open my_cur for select * from stud;
     end;

4、jdbc中调用

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import oracle.jdbc.driver.OracleTypes;

public class TestProCur {

public void test(){

Connection con = null;
ResultSet rs = null;
CallableStatement cst = null;
try{
         Class.forName("oracle.jdbc.driver.OracleDriver");
         //下面的tan是数据库名，默认是orcl，love是访问密码，默认是tiger,
         //1521是端口号，默认是1521
        con = DriverManager.getConnection   ("jdbc:oracle:thin:@192.168.1.103:1521:tan","scott","love");
        String sql = "{call pro_select_stud(?)}";
        cst = con.prepareCall(sql);
        cst.registerOutParameter(1, OracleTypes.CURSOR);
        cst.execute();
        rs = (ResultSet)cst.getObject(1);
        while(rs.next()){
              System.out.println(rs.getString(2));
        }
}catch(Exception e){
   e.printStackTrace();
}finally{

   try{

         if(rs !=null){
              rs.close();
         }
         if(cst != null){
              cst.close();
         }
         if(con != null){
              con.close();
         }
   }catch(Exception e){
         e.printStackTrace();
   }

}
}

public static void main(String[] args) {
TestProCur tpc = new TestProCur();
tpc.test();
}

}

分享到：