1,存错过程部分:
create or replace procedure testNum(i IN VARCHAR2, j OUT VARCHAR2) is
begin
select t.kpi_id
into j
from north_orm_performce_result t
where t.kpi_id = i
and substr(t.begin_time, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd');
end testNum;
i表示在调用存储过程的时候要传入的参数,j表示该存储过程要输出的参数值,into j 将该查询的结果赋值到j;
2,java代码调用部分:
package test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import com.superv.mstg.common.util.DBUtil;
public class TestCount {
private static Connection connection = null;
private static ResultSet resultSet = null;
private static PreparedStatement statement = null;
public static void main(String[] args) {
try {
//封装了对数据库连接
connection = DBUtil.getConnection();
//调用存储过程
CallableStatement proc = connection
.prepareCall("{call testNum(?,?)}");
//第一个问号你要传入到存储过程的值
proc.setString(1, "PM-01-31-02-01-01-02");
//第二个问号存储过程要输出的结果值与类型
proc.registerOutParameter(2, Types.VARCHAR);
//执行
proc.execute();
//得的值
String name = proc.getString(2);
System.out.println(name);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(resultSet, statement, connection);
}
}
}