存储过程先查询然后执行更新或者插入,代码如下:CREATE PROCEDURE set_sche
@task_id int,@emp_id int,@content nchar(50),@id int out
AS Declare @ta_id int ,@date datetime ,@countask int
set @date=convert(char(10),GETDATE(),111)
select @ta_id=a_id
from M72assign
where task=@task_id and emp=@emp_id
if @ta_id is null
return 0
else
begin
select @countask=count(*) from M72tasksche where ta_id=@ta_id
if @countask='0'
insert into M72tasksche (ta_id,sche_date,sche_content)
values(@ta_id,convert(char(10),GETDATE(),111),@content)
else
begin
if @date not in (select sche_date from M72tasksche where ta_id=@ta_id)
insert into M72tasksche (ta_id,sche_date,sche_content)
values(@ta_id,convert(char(10),@date,111),@content)
else
update M72tasksche set sche_content=@content where ta_id=@ta_id and sche_date=@date
end
select @id=@ta_id
return @id
end
GO在sql server的查询分析器里输入三个参数是可以输出参数并插入或更新记录的,但是通过jdbc就只有输出参数,没有执行插入和更新,也没有报错,这咋整呢?java代码如下:package DAO;import java.sql.*;import com.myPerform.database.Database;
import bean.task;//import com.myPerform.database.Database;public class scheprocedure {
Connection conn =null;
public void setSche(task task){ try {
conn=Database.getConnection();
// DataSource dbs=new DataSource();
// conn=dbs.getConnection();
CallableStatement cs = conn.prepareCall("{call set_sche(?,?,?,?)}");
cs.setInt(1, Integer.parseInt(task.getT_id()));//参数的传送
cs.setInt(2,Integer.parseInt(task.getEmpId()));
cs.setString(3,task.getSche());
cs.registerOutParameter(4, Types.VARCHAR);//指定第4个参数为输出参数
cs.executeUpdate(); String strName = cs.getString(4);//从存诸过程中得到第4个参数内容
System.out.println(strName);
cs.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @param args
*/
public static void main(String[] args) {
task ta=new task();
ta.setEmpId("10000002");
ta.setT_id("10006");
ta.setSche("zzzzzzzzzzz");
scheprocedure sp=new scheprocedure();
sp.setSche(ta);
}}
@task_id int,@emp_id int,@content nchar(50),@id int out
AS Declare @ta_id int ,@date datetime ,@countask int
set @date=convert(char(10),GETDATE(),111)
select @ta_id=a_id
from M72assign
where task=@task_id and emp=@emp_id
if @ta_id is null
return 0
else
begin
select @countask=count(*) from M72tasksche where ta_id=@ta_id
if @countask='0'
insert into M72tasksche (ta_id,sche_date,sche_content)
values(@ta_id,convert(char(10),GETDATE(),111),@content)
else
begin
if @date not in (select sche_date from M72tasksche where ta_id=@ta_id)
insert into M72tasksche (ta_id,sche_date,sche_content)
values(@ta_id,convert(char(10),@date,111),@content)
else
update M72tasksche set sche_content=@content where ta_id=@ta_id and sche_date=@date
end
select @id=@ta_id
return @id
end
GO在sql server的查询分析器里输入三个参数是可以输出参数并插入或更新记录的,但是通过jdbc就只有输出参数,没有执行插入和更新,也没有报错,这咋整呢?java代码如下:package DAO;import java.sql.*;import com.myPerform.database.Database;
import bean.task;//import com.myPerform.database.Database;public class scheprocedure {
Connection conn =null;
public void setSche(task task){ try {
conn=Database.getConnection();
// DataSource dbs=new DataSource();
// conn=dbs.getConnection();
CallableStatement cs = conn.prepareCall("{call set_sche(?,?,?,?)}");
cs.setInt(1, Integer.parseInt(task.getT_id()));//参数的传送
cs.setInt(2,Integer.parseInt(task.getEmpId()));
cs.setString(3,task.getSche());
cs.registerOutParameter(4, Types.VARCHAR);//指定第4个参数为输出参数
cs.executeUpdate(); String strName = cs.getString(4);//从存诸过程中得到第4个参数内容
System.out.println(strName);
cs.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @param args
*/
public static void main(String[] args) {
task ta=new task();
ta.setEmpId("10000002");
ta.setT_id("10006");
ta.setSche("zzzzzzzzzzz");
scheprocedure sp=new scheprocedure();
sp.setSche(ta);
}}
boolean autoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
//......
conn.commit();
conn.setAutoCommit(autoCommit);
cs.close();
conn.close();
另外,上面Session为Transaction之误