java调用存储过程

来源：互联网发布：金融seo 编辑：程序博客网时间：2024/06/06 05:18

package test2;

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

public class Test12 {

public static void main(String[] args) {
method4();
}

//调用数据库里面没有返回值的存储过程
public static void method1(){
Connection con = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "SCOTT";
con = DriverManager.getConnection(url, user, password);
CallableStatement cs = con.prepareCall("{call sp_pro1(?,?)}");
cs.setString(1, "SMITH");
cs.setInt(2, 200);
cs.execute();
cs.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

//调用数据库的自定义函数
public static void method2(){
Connection con = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "SCOTT";
con = DriverManager.getConnection(url, user, password);
PreparedStatement ps = con.prepareStatement("select sp_fun1(?) from dual");
ps.setString(1, "SCOTT");
ResultSet rs = ps.executeQuery();
if(rs.next())
System.out.println("函数返回值是："+rs.getInt(1));
rs.close();
ps.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

//调用数据库有一个值返回值的存储过程
public static void method3(){
Connection con = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "SCOTT";
con = DriverManager.getConnection(url, user, password);
/*创建存储过程语句
create or replace procedure sp_pro3
(eno in number,names out varchar2) is
begin
select ename into names from emp where empno=eno;
end;
*/
CallableStatement cs = con.prepareCall("{call sp_pro3(?,?)}");//调用有返回值的存储过程，第二个参数为返回值
cs.setInt(1, 7788);//设置第一个变量的值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);//注册一个变量，该变量的类型要和存储过程返回的类型一致
cs.execute();
String name = cs.getString(2);//取出返回值
System.out.println("7788用户的姓名是："+name);
cs.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

//调用数据库返回集合的存储过程
public static void method4(){
Connection con = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "SCOTT";
con = DriverManager.getConnection(url, user, password);
/*创建存储过程语句
--创建一个包，包里面定义了一个游标类型的数据
create or replace package testpackage as
type t_cursor is ref cursor;
end;

--创建一个存储过程，通过输入的部门号返回该部门的所以职工信息
--open emplist for代表打开游标并把查询的结果集放在emplist里面
create or replace procedure sp_pro4
(dno in number,emplist out testpackage.t_cursor) is
begin
open emplist for select * from emp where deptno=dno;
end;
*/
CallableStatement cs = con.prepareCall("{call sp_pro4(?,?)}");//调用有返回值的存储过程，第二个参数为返回值
cs.setInt(1, 10);//设置第一个变量的值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);//注册一个变量，该变量的类型要和存储过程返回的类型一致
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);//取出返回值
while(rs.next()){
System.out.println(rs.getInt("empno")+"\t"+rs.getString("ename"));
}
rs.close();
cs.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

//调用数据库分页的存储过程
public static void method5(){
Connection con = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "SCOTT";
con = DriverManager.getConnection(url, user, password);
/*创建存储过程语句
--创建包
create or replace package sp_package2 as
type sp_cursor is ref cursor;
end;
--通用分页存储过程
create or replace procedure
sp_pro6(tablename in varchar2,--表或视图名称，输入参数
selectrow in varchar2,--要查询的列，输入参数
wheres in varchar2,--查询条件，输入时要输入where ....，输入参数
orders in varchar2,--排序条件，不包含order by ，输入参数
currentpage in number,--当前页，输入参数
pagesizes in number,--每页显示的条数，输入参数
pagecount out number,--总页数，输出参数
counts out number,--总条数，输出参数
emplist out sp_package2.sp_cursor--输出的结果集
) is
v_sql varchar2(32767);
begin
v_sql:='select * from (select a1.*,rownum rn from (select '||selectrow||' from '||tablename||wheres||' order by '||orders||') a1 where rownum<='||currentpage*pagesizes||') where rn>='||((currentpage-1)*pagesizes+1);
open emplist for v_sql;
v_sql:='select count(*) from '||tablename||wheres;
execute immediate v_sql into counts;--执行sql语句，并把值赋给counts
if mod(counts,pagesizes)=0 then
pagecount:=counts/pagesizes;
else
pagecount:=counts/pagesizes+1;
end if;
exception
when no_data_found then
pagecount:=0;
counts:=0;
end;
*/
CallableStatement cs = con.prepareCall("{call sp_pro6(?,?,?,?,?,?,?,?,?)}");//调用有返回值的存储过程，第二个参数为返回值
cs.setString(1, " emp ");//表名
cs.setString(2, " * ");//查询的列名
cs.setString(3, " ");//查询条件
cs.setString(4, " empno desc ");//排序方式
cs.setInt(5, 1);//设置当前页
cs.setInt(6, 5);//设置每页的条数
cs.registerOutParameter(7, oracle.jdbc.OracleTypes.INTEGER);//返回总页数
cs.registerOutParameter(8, oracle.jdbc.OracleTypes.INTEGER);//返回总条数
cs.registerOutParameter(9, oracle.jdbc.OracleTypes.CURSOR);//注册一个变量，该变量的类型要和存储过程返回的类型一致
cs.execute();
System.out.println(cs.getInt(7)+"\t"+cs.getInt(8));
ResultSet rs = (ResultSet) cs.getObject(9);//取出返回值
while(rs.next()){
System.out.println(rs.getInt("empno")+"\t"+rs.getString("ename"));
}
rs.close();
cs.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

}