Java调用存储过程
例一
存储过程定义,获取主键id,可以加前缀:
create or replace procedure getId(prefix in varchar, id out varchar) is
v varchar(20);
begin
v := prefix||TRUNC(100000+899999*dbms_random.value);
v := substr(v,0,6);
id := v||to_char(sysdate,'yyyymmddhh24miss');
end;
Java程序调用上面的存储过程:
public static void main(String[] args) {
try {
Class.forName(DRVIER);
Connection connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
String prefix = "DB";
String call_sql = "{call getid(?,?)}";
CallableStatement prepareCall = connection.prepareCall(call_sql);
prepareCall.setString(1,prefix);
prepareCall.registerOutParameter(2, Types.VARCHAR);
prepareCall.execute();
String id = prepareCall.getString(1);
System.out.println(id);
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
其中比较不同的是registerOutParameter这个方法,如下
public abstract void registerOutParameter(int parameterIndex,
int sqlType) throws java.sql.SQLException
必须在执行调用之前,先声明parameterIndex位置的参数作为存储过程的出参,并指定出参的类型sqlType。
参数说明:
parameterIndex – 第一个?是1,以此类推。
sqlType – 使用java.sql.Types的类型定义。
例二
根据年龄(仅作举例)给加工资,
定义存储过程如下:
create or replace procedure updateSalary(a in number,b in number,c in number) is
begin
for temp in (select * from test)
loop
if (temp.age=18) then
update test t set t.salary=t.salary+a where t.id=temp.id;
elsif (temp.age=19) then
update test t set t.salary=t.salary+b where t.id=temp.id;
else
update test t set t.salary=t.salary+c where t.id=temp.id;
end if;
end loop;
commit;
end;
加工资前的数据:
Java调用存储过程:
public static void main(String[] args) {
try {
Class.forName(DRVIER);
Connection connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
String call_sql = "{call updateSalary(?,?,?)}";
CallableStatement prepareCall = connection.prepareCall(call_sql);
prepareCall.setInt(1,500);
prepareCall.setInt(2,1000);
prepareCall.setInt(3,1500);
prepareCall.execute();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
加工资后的数据:
以上!