create or replace type TIMESTAMP_VARCHAR2 as table of varchar2(1000);
create or replace procedure testDate(
PARAMS_TIMESTAMP_VARCHAR2 in TIMESTAMP_VARCHAR2,
o_n_ret OUT varchar2 )
is
var_test varchar2(100);
cursor cur2 is
select to_char(operdate,'yyyy-mm-dd hh24:mi:ss') from t_wfcurrenttasks ;
begin
open cur2;
loop
var_test :=null;
fetch cur2 into var_test; for i in 1..PARAMS_TIMESTAMP_VARCHAR2.count loop
update t_wfcurrenttasks set operdate=sysdate where var_test =PARAMS_TIMESTAMP_VARCHAR2(i);
end loop;
--delete from t_wfcurrenttasks where var_test =PARAMS_TIMESTAMP_VARCHAR2(i);
EXIT WHEN cur2%NOTFOUND;
end loop;
close cur2;
o_n_ret := var_test;
exception when others then
raise;
end testDate;
create or replace procedure testDate(
PARAMS_TIMESTAMP_VARCHAR2 in TIMESTAMP_VARCHAR2,
o_n_ret OUT varchar2 )
is
var_test varchar2(100);
cursor cur2 is
select to_char(operdate,'yyyy-mm-dd hh24:mi:ss') from t_wfcurrenttasks ;
begin
open cur2;
loop
var_test :=null;
fetch cur2 into var_test; for i in 1..PARAMS_TIMESTAMP_VARCHAR2.count loop
update t_wfcurrenttasks set operdate=sysdate where var_test =PARAMS_TIMESTAMP_VARCHAR2(i);
end loop;
--delete from t_wfcurrenttasks where var_test =PARAMS_TIMESTAMP_VARCHAR2(i);
EXIT WHEN cur2%NOTFOUND;
end loop;
close cur2;
o_n_ret := var_test;
exception when others then
raise;
end testDate;
public class tDate {
private String timestamp; public String getTimestamp() {
return timestamp;
} public void setTimestamp(String timestamp) {
this.timestamp = timestamp;
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;import com.mofit.framework.domain.BASEVOUCHER;import oracle.jdbc.driver.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;public class ItestDate {
public ItestDate(){
ArrayList list = new ArrayList();
tDate d =new tDate();
d.setTimestamp("2011-10-28 17:27:30");
list.add(d);
d.setTimestamp("2011-10-21 09:48:09");
list.add(d);
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
CallableStatement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "manu2", "root");
ArrayDescriptor varchar2Desc1 = ArrayDescriptor.createDescriptor(
"TIMESTAMP_VARCHAR2", conn);
ARRAY aArray1 = new ARRAY(varchar2Desc1, conn, getArray(list)[0]);
stmt = conn.prepareCall("{call testDate(?,?)}");
stmt.setArray(1, aArray1);
stmt.registerOutParameter(2, OracleTypes.VARCHAR);
stmt.execute();
String retVal = "-1";
//成功返回0,异常返回-1
retVal = stmt.getString(2);
System.out.println("调用存储过程结果"+retVal);
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null)
{
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
}
}
catch (SQLException ex1) {
}
}
}
private static Object[][] getArray( ArrayList objlist) throws Exception {
Object[][] twoArray = new Object[1][2];
// ArrayList resList = new ArrayList();
if (objlist != null && objlist.size() > 0) {
for (int j = 0; j < objlist.size(); j++) {
tDate b =(tDate)objlist.get(j);
twoArray[0][j] = b.getTimestamp();
}
}
return twoArray;
}
public static void main(String args[]){
new ItestDate();
}
}
update t_wfcurrenttasks set operdate=sysdate where var_test =PARAMS_TIMESTAMP_VARCHAR2(i);
应该只更新一条,就是数据库时间和传进来的时间相等的。
结果数据库t_wfcurrenttasks表中不管时间相不相等全都更新了。。
就是全部更新了修改为update t_wfcurrenttasks set operdate=sysdate where to_char(operdate,'yyyy-mm-dd hh24:mi:ss') = PARAMS_TIMESTAMP_VARCHAR2(i);