Question

java访问数据库存储过程

这里有过程CREATE OR REPLACE PROCEDURE PROC_sensor_time
( startD1 IN Varchar2,
endD1 IN Varchar2,
intervalIn In Varchar2
) As
BEGIN

给出三个参数，用方法现实

Answer 1

java中调用Oracle存储过程
1、编写存储过程
<code class=" hljs oxygene">CREATE OR REPLACE PROCEDURE sp_pro3(sp_name VARCHAR2,sp_sal NUMBER )
IS
BEGIN
--根据用户名修改工资
UPDATE emp SET sal=sp_sal WHERE ename=sp_name;
END;
</code>

2、引入jdbc6.jar，编写测试类Test.java
<code class=" hljs java">package testOraclePro;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Test {
public static void main(String[] args) {
try{
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.获取连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE","sys as sysdba","123456");
//3.创建CallableStatement
CallableStatement cs=ct.prepareCall("{call sp_pro3(?,?)}");
//4.赋值
cs.setString(1, "zxs");
cs.setFloat(2,455f);
//5.执行
cs.execute();
//6.关闭资源
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
}
}</code>

执行上面的测试类,即可调用存储过程，执行相应的程序了。
tips:可通过下面的SQL语句来获取数据库名称：
<code class=" hljs ruleslanguage">SELECT * FROM V$INSTANCE </code>

Answer 2

class.forName(\"sun.jdbc.odbc.JdbcOdbcDriver\"); //加载驱动
Connection con=DriverManager.getConnection(\"Jdbc:Odbc:test\",\"sa\",\"\"); //获得连接
String call=\"{call proc_select(?,?)};\" //调用语句
CallableStatement proc=con.preparecall(call); //调用存储过程
proc.setString(1,\"12345678\"); //给输入参数传值
proc.setString(2,\"12345678\"); //给输入参数传值
proc.setString(3,\"12345678\"); //给输入参数传值
proc.execute(); //执行
String address=proc.getString(2); //获得输出参数

Answer 3

public void PROC_sensor_time(String start, String end, String interval) {

CallableStatement cstmt = null;
Connection con=null；
try {
con = this.getConnection();
con.setAutoCommit(false);

String procedure = "{call PROC_sensor_time(?,?,?) }";
cstmt = con.prepareCall(procedure);
cstmt.setString(1, start);
cstmt.setString(2, end);
cstmt.setInt(3, gap);
cstmt.executeUpdate();

cstmt.close();
cstmt = null;
con.commit();

} catch (Exception e) {
e.printStackTrace();
} finally {

if (cstmt != null)
try {
cstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
this.closeConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}