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

ngcg9937i6

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）

create or replace procedure find_stu(cur_fi out show_all.stu_cursor)
is
begin
open cur_fi for select * from students;
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 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();
}
}
}