1存储过程
create or replace procedure addBook1(bN in varchar2,td in number,n1 out number,n2 out number) as
begin
declare maxId number;
n number;
begin
select count(*) into n1 from t_book where bookName=bn;
select count(*) into n from t_book where bookName=bn;
if(n<2)then
return;
end if;
select max(id) into maxId from t_book where bookName=bn;
delete from t_book where id < maxId and bookName = bn;
select count(*) into n2 from t_book where bookName=bn;
commit;
end;
end addBook1;
2.java调用实例
Test测试类
package test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
public class Test {
/**
* 一个非常标准的连接Oracle数据库的示例代码
*/
public void testOracle()
{
Connection con = null;// 创建一个数据库连接
CallableStatement pre = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
System.out.println("开始尝试连接数据库!");
String url = "jdbc:oracle:thin:@localhost:1521:TEST";// 127.0.0.1是本机地址,TEST是精简版Oracle的默认数据库名
String user = "shopping";// 用户名,系统默认的账户名
String password = "123456";// 你安装时选设置的密码
con = DriverManager.getConnection(url, user, password);// 获取连接
System.out.println("连接成功!");
String sql = "{call addBook1(?,?,?,?)}";
pre = con.prepareCall(sql);// 实例化预编译语句
pre.setString(1, "大熊");// 设置参数,前面的1表示参数的索引,而不是表中列名的索引
pre.setInt(2, 2);
pre.registerOutParameter(3, Types.INTEGER); //给返回的值申明变量
pre.registerOutParameter(4, Types.INTEGER);
pre.execute();//执行查询
int n1 = pre.getInt(3);
int n2 = pre.getInt(4);
System.out.println(n1);
System.out.println(n2);
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
// 逐一将上面的几个对象关闭,因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序,最后使用的最先关闭
if (pre != null)
pre.close();
if (pre != null)
pre.close();
if (con != null)
con.close();
System.out.println("数据库连接已关闭!");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
}
Main方法调用
package test;
public class Test1 {
public static void main(String[] args) {
Test test = new Test();
test.testOracle();
}
}