最完整的java调用oracle存储过程、函数

java调用oracle的存储过程、函数,分以下几种情况：
1.java调用带参的存储过程
2.java调用无参存储过程
3.java调用带参函数
4.java调用无参函数
5.java调用包中的过程/函数
注：1.存储过程和函数若为无参，直接写名称即可，后面不能再写小括号。

1.java调用带参的存储过程
创建表：students(name,age,regdate)

create table students (
name varchar2(10),
age number(3),
regdate Date
);

插入数据：

insert into students values('张三',20,to_date('2005-09-01','yyyy-mm-dd'));
insert into students values('李四',21,to_date('2008-10-30','yyyy-mm-dd'));

创建存储过程,返回值是一个值：

create or replace procedure get_age(p_name in students.name%type,p_age out students.age%type)
is
begin
select age into p_age from students where name=p_name;
end;

java调用存储过程：

public class HaveParameterTest {
	public static void main(String[] args) {
		Connection conn;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:oracle";//改写成自己的数据库名称
		try {
			conn = DriverManager.getConnection(strUrl,"dbname","password");//改成自己的数据库名称和密码
			CallableStatement proc = conn.prepareCall("{call get_age(?,?)} ");
			proc.setString(1, "张三");
			proc.registerOutParameter(2, java.sql.Types.INTEGER);
			proc.execute();
			int age = proc.getInt(2);
			System.out.println("age..."+age);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

创建存储过程,返回值为结果集：
//在包中定义游标变量类型

create or replace package show_all
is
type stu_cursor is ref cursor;
end;

//返回类型为包中定义的游标变量，必须写成 包名.变量名（show_all.stu_cursor）
[code]
create or replace procedure find_stu(cur_fi out show_all.stu_cursor)
is
begin
open cur_fi for select * from students;
end;[/code]
java调用：

public class ReturnListTest {
	public static void main(String[] args) {

		Connection conn=null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:oracle";
		try {
			conn = DriverManager.getConnection(strUrl,"dbname","password");
			CallableStatement pro = conn.prepareCall("{call find_stu(?) }");
			pro.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
			pro.execute();
			ResultSet rs = (ResultSet) pro.getObject(1);
			while(rs.next()){
				System.out.println(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

2.java调用无参存储过程

public class HaveParameterTest {
	public static void main(String[] args) {
		Connection conn;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:oracle";
		try {
			conn = DriverManager.getConnection(strUrl,"dbname","password");
			CallableStatement proc = conn.prepareCall("{call getAllStudents} ");//call后面直接写存储过程名称即可，不用再写()
			proc.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

3.java调用带参函数
创建函数，返回值是一个值：

create or replace function findagebyname(p_name in students.name%type)
return number
is
stu_age number(3);
begin
select age into stu_age from student where name=p_name;
return stu_age;
end;

java调用：

public class FunctionTest {
	public static void main(String[] args) {
		Connection conn;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:oracle";
		try {
			conn = DriverManager.getConnection(strUrl,"dbname","password");
			CallableStatement fun = conn.prepareCall("{?=call findagebyname(?)} ");
			fun.setString(2, "张三");
			fun.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);
			fun.execute();
			String name = fun.getInt(1);
			System.out.println(name);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

4.java调用无参函数,返回值是一个结果集：
//在包中定义一个游标变量类型

create or replace package show_all
is
type stu_cursor is ref cursor;
end;

//创建返回值为结果集的函数，返回类型为包中定义的游标变量，必须写成 包名.变量名(show_all.stu_cursor)

create or replace function find
return show_all.stu_cursor
is
cur_fi show_all.stu_cursor;
begin
open cur_fi for select * from students;
return cur_fi;
end;

java调用：

public class FunctionTest {
	public static void main(String[] args) {
		Connection conn;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:oracle";
		try {
			conn = DriverManager.getConnection(strUrl,"dbname","password");
			CallableStatement fun = conn.prepareCall("{?=call find} ");
			fun.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
			fun.execute();
			ResultSet rs = (ResultSet) fun.getObject(1);
			while(rs.next()){
				System.out.print(rs.getString(1));
				System.out.println(rs.getInt(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

结果：

张三20
李四21
王五22

5.java调用包中的过程/函数
java调用包中的过程/函数时,过程或函数名称前需加上包名. ，如包名为show_all,调用时需写成,("{?=call show_all.find} ");以调用包中的存储过程为例：
//创建包头

create or replace package show_all
is
type stu_cursor is ref cursor;
procedure find_stu(cur_fi out stu_cursor);
end;

//创建包体

create or replace package body show_all as
procedure find_stu(cur_fi out stu_cursor)
is
begin
open cur_fi for select * from students;
end;
end;

java调用：

public class ReturnListTest {
	public static void main(String[] args) {
		Connection conn=null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:oracle";
		try {
			conn = DriverManager.getConnection(strUrl,"dbname","password");
			CallableStatement pro = conn.prepareCall("{call show_all.find_stu(?) }");
			pro.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
			pro.execute();
			ResultSet rs = (ResultSet) pro.getObject(1);
			while(rs.next()){
				System.out.print(rs.getString(1));
				System.out.println(rs.getInt(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}