实现存储过程必须先在oracle建立相应的Procedures,如下所示:
--添加信息--
create or replace procedure insert_t_test(
p_id in number,
p_name in varchar2,
p_password in varchar2
) is
begin
insert into t_test(id, name,password) values(p_id,p_name,p_password);
end;
-------------------------
--删除信息--
create or replace procedure del_t_test(
p_id in number,
x_out_record out number) is
begin
delete t_test where id = p_id;
x_out_record := 0;
exception
when others then
x_out_record := -1;
end;
-----------------------------
--查询所有信息--
create or replace procedure all_t_test(
x_out_record out number,
x_out_cursor out sys_refcursor) is
begin
open x_out_cursor for
select * from t_test;
x_out_record := 0;
exception
when others then
x_out_record := -1;
end;
create or replace procedure insert_t_test(
p_id in number,
p_name in varchar2,
p_password in varchar2
) is
begin
insert into t_test(id, name,password) values(p_id,p_name,p_password);
end;
-------------------------
--删除信息--
create or replace procedure del_t_test(
p_id in number,
x_out_record out number) is
begin
delete t_test where id = p_id;
x_out_record := 0;
exception
when others then
x_out_record := -1;
end;
-----------------------------
--查询所有信息--
create or replace procedure all_t_test(
x_out_record out number,
x_out_cursor out sys_refcursor) is
begin
open x_out_cursor for
select * from t_test;
x_out_record := 0;
exception
when others then
x_out_record := -1;
end;
其中的存储过程名字(就是加粗部分)必须要和java代码中的相对应
Java代码如下:
package com.procedure.db;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jdbc.OracleTypes;
public class ConnDB {
private String url= "jdbc:oracle:thin:@localhost:1521:orcl";
private String driverClass= "oracle.jdbc.driver.OracleDriver";
private String username= "scott";
private String password= "hello";
public Connection getConn(){
Connection conn= null;
try {
Class.forName(driverClass);
conn=DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void main(String args[]){
ConnDB cd= new ConnDB();
Connection conn=cd.getConn();
/**
* 添加 有输入参数 无输出参数
*/
/*try {
CallableStatement call=conn.prepareCall("{call insert_t_test(?,?,?)}");
call.setInt(1, 66);
call.setString(2, "小猫");
call.setString(3, "8989");
Boolean b=call.execute();
System.out.println("b="+b);
} catch (SQLException e) {
e.printStackTrace();
}*/
/**
* 删除 有输入参数 得到输出参数
*/
/*try {
CallableStatement call=conn.prepareCall("{call del_t_test(?,?)}");
call.setInt(1, 66);
call.registerOutParameter(2, Types.INTEGER);
call.execute();
Integer result=call.getInt(2);
System.out.println("执行结果为0正常,执行结果为-1不正常"+result);
} catch (SQLException e) {
e.printStackTrace();
}*/
/**
* 使用游标查询所有信息 无输入参数 有输出参数
*/
try {
CallableStatement call=conn.prepareCall( "{call all_t_test(?,?)}");
call.registerOutParameter(1, Types.INTEGER);
call.registerOutParameter(2, OracleTypes.CURSOR);
call.execute();
Integer result=call.getInt(1);
ResultSet rs=(ResultSet) call.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jdbc.OracleTypes;
public class ConnDB {
private String url= "jdbc:oracle:thin:@localhost:1521:orcl";
private String driverClass= "oracle.jdbc.driver.OracleDriver";
private String username= "scott";
private String password= "hello";
public Connection getConn(){
Connection conn= null;
try {
Class.forName(driverClass);
conn=DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void main(String args[]){
ConnDB cd= new ConnDB();
Connection conn=cd.getConn();
/**
* 添加 有输入参数 无输出参数
*/
/*try {
CallableStatement call=conn.prepareCall("{call insert_t_test(?,?,?)}");
call.setInt(1, 66);
call.setString(2, "小猫");
call.setString(3, "8989");
Boolean b=call.execute();
System.out.println("b="+b);
} catch (SQLException e) {
e.printStackTrace();
}*/
/**
* 删除 有输入参数 得到输出参数
*/
/*try {
CallableStatement call=conn.prepareCall("{call del_t_test(?,?)}");
call.setInt(1, 66);
call.registerOutParameter(2, Types.INTEGER);
call.execute();
Integer result=call.getInt(2);
System.out.println("执行结果为0正常,执行结果为-1不正常"+result);
} catch (SQLException e) {
e.printStackTrace();
}*/
/**
* 使用游标查询所有信息 无输入参数 有输出参数
*/
try {
CallableStatement call=conn.prepareCall( "{call all_t_test(?,?)}");
call.registerOutParameter(1, Types.INTEGER);
call.registerOutParameter(2, OracleTypes.CURSOR);
call.execute();
Integer result=call.getInt(1);
ResultSet rs=(ResultSet) call.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
以上就是全部示例,具体概念东西请自己查找,如有出错之处欢迎拍砖
转载于:https://blog.51cto.com/myoraclex/411606