JDBCConnect jdbconn=new JDBCConnect();
Connection conn=null;
Session session=this.getSession();
conn=session.connection();
CallableStatement cs=null;
int cout=0;
int[] test = null;
try{
cs=conn.prepareCall("{call insertLcAndOrderFirst_new(?,?,?)}");
cs.setInt(1, Integer.parseInt(ledgerType));
cs.setString(2, formNO);
cs.setString(3, userNO);
test=cs.executeBatch();
cout=cs.executeUpdate();
}这是调用存储过程的代码
BEGIN
declare PurchaseUnitID varchar(50);
declare ProviderID varchar(50);
declare FormCreatorID varchar(50);
declare FormCreateDate datetime;
declare PurchaseDate datetime;
declare PurchaseUnitName varchar(100);
declare ProviderName varchar(100);
declare AuditState int;
declare providerAddress varchar(200);
declare FormCreatorMC varchar(100);
declare sumGoodsAmount numeric(10,0);
declare allPrice numeric(10,2) ;
declare isAutoLedger int;
declare ledgerTel varchar(20);
declare inEntTel varchar(20);
declare purchaseUnitAddr varchar(200);
declare ledgerType2 int;update LedgerOrderCertificate set isAutoLedger=1,auditDate=now(),auditorID=userNo where LedgerOrderCertificate.formID=formID;
IF ledgerType=0 THEN
select lc.FormID,lc.PurchaseUnitID,lc.ProviderID,lc.FormCreatorID, lc.FormCreateDate,lc.PurchaseDate,lc.PurchaseUnitName,lc.ProviderName,
lc.AuditState,lc.providerAddress,lc.FormCreatorMC,lc.sumGoodsAmount,lc.allPrice,lc.isAutoLedger,lc.ledgerTel,lc.inEntTel,lc.purchaseUnitAddr
into formID,PurchaseUnitID,ProviderID,FormCreatorID,FormCreateDate,PurchaseDate,PurchaseUnitName,ProviderName,AuditState,providerAddress,FormCreatorMC,sumGoodsAmount,allPrice,isAutoLedger,ledgerTel,inEntTel,purchaseUnitAddr
from LedgerOrderCertificate lc where lc.formID=formID;
set ledgerType2=3;END IF;IF ledgerType=1 THEN
select lc.FormID,lc.PurchaseUnitID,lc.ProviderID,lc.FormCreatorID,lc.FormCreateDate,lc.PurchaseDate,lc.PurchaseUnitName,lc.ProviderName,lc.AuditState,lc.providerAddress,lc.FormCreatorMC,lc.sumGoodsAmount,lc.allPrice,lc.isAutoLedger,lc.ledgerTel,lc.inEntTel,lc.purchaseUnitAddr
into formID,ProviderID,PurchaseUnitID,FormCreatorID,FormCreateDate,PurchaseDate,ProviderName,PurchaseUnitName,AuditState,purchaseUnitAddr,FormCreatorMC,sumGoodsAmount,allPrice,isAutoLedger,inEntTel,ledgerTel,providerAddress
from LedgerOrderCertificate lc where lc.formID=formID;
set ledgerType2=1;
set ledgerType=3;
END IF;
call insertLedgerLc_new(
FormID ,
PurchaseUnitID ,
ProviderID ,
FormCreatorID ,
FormCreateDate ,
PurchaseDate ,
LedgerType,
PurchaseUnitName ,
ProviderName,
AuditState,
providerAddress ,
FormCreatorMC ,
sumGoodsAmount ,
allPrice,
isAutoLedger ,
ledgerTel ,
inEntTel ,
purchaseUnitAddr);IF ledgerType2=1 THEN
select lc.FormID,lc.PurchaseUnitID,lc.ProviderID,lc.FormCreatorID,lc.FormCreateDate,lc.PurchaseDate,lc.PurchaseUnitName,lc.ProviderName,lc.AuditState,lc.providerAddress,lc.FormCreatorMC,lc.sumGoodsAmount,lc.allPrice,lc.isAutoLedger,lc.ledgerTel,lc.inEntTel,lc.purchaseUnitAddr
into formID,ProviderID,PurchaseUnitID,FormCreatorID,FormCreateDate,PurchaseDate,ProviderName,PurchaseUnitName,AuditState,purchaseUnitAddr,FormCreatorMC,sumGoodsAmount,allPrice,isAutoLedger,inEntTel,ledgerTel,providerAddress
from LedgerOrderCertificate lc where lc.formID=formID;
END IF;
IF ledgerType2=3 THEN
select lc.FormID,lc.PurchaseUnitID,lc.ProviderID,lc.FormCreatorID, lc.FormCreateDate,lc.PurchaseDate,lc.PurchaseUnitName,lc.ProviderName,lc.AuditState,lc.providerAddress,lc.FormCreatorMC,lc.sumGoodsAmount,lc.allPrice,lc.isAutoLedger,lc.ledgerTel,lc.inEntTel,lc.purchaseUnitAddr
into formID,PurchaseUnitID,ProviderID,FormCreatorID,FormCreateDate,PurchaseDate,PurchaseUnitName,ProviderName,AuditState,providerAddress,FormCreatorMC,sumGoodsAmount,allPrice,isAutoLedger,ledgerTel,inEntTel,purchaseUnitAddr
from LedgerOrderCertificate lc where lc.formID=formID;
END IF;
call insertLedgerLc_new(
FormID ,
PurchaseUnitID ,
ProviderID ,
FormCreatorID ,
FormCreateDate ,
PurchaseDate ,
LedgerType2,
PurchaseUnitName ,
ProviderName,
AuditState,
providerAddress ,
FormCreatorMC ,
sumGoodsAmount ,
allPrice,
isAutoLedger ,
ledgerTel ,
inEntTel ,
purchaseUnitAddr);END
Connection conn=null;
Session session=this.getSession();
conn=session.connection();
CallableStatement cs=null;
int cout=0;
int[] test = null;
try{
cs=conn.prepareCall("{call insertLcAndOrderFirst_new(?,?,?)}");
cs.setInt(1, Integer.parseInt(ledgerType));
cs.setString(2, formNO);
cs.setString(3, userNO);
test=cs.executeBatch();
cout=cs.executeUpdate();
}这是调用存储过程的代码
BEGIN
declare PurchaseUnitID varchar(50);
declare ProviderID varchar(50);
declare FormCreatorID varchar(50);
declare FormCreateDate datetime;
declare PurchaseDate datetime;
declare PurchaseUnitName varchar(100);
declare ProviderName varchar(100);
declare AuditState int;
declare providerAddress varchar(200);
declare FormCreatorMC varchar(100);
declare sumGoodsAmount numeric(10,0);
declare allPrice numeric(10,2) ;
declare isAutoLedger int;
declare ledgerTel varchar(20);
declare inEntTel varchar(20);
declare purchaseUnitAddr varchar(200);
declare ledgerType2 int;update LedgerOrderCertificate set isAutoLedger=1,auditDate=now(),auditorID=userNo where LedgerOrderCertificate.formID=formID;
IF ledgerType=0 THEN
select lc.FormID,lc.PurchaseUnitID,lc.ProviderID,lc.FormCreatorID, lc.FormCreateDate,lc.PurchaseDate,lc.PurchaseUnitName,lc.ProviderName,
lc.AuditState,lc.providerAddress,lc.FormCreatorMC,lc.sumGoodsAmount,lc.allPrice,lc.isAutoLedger,lc.ledgerTel,lc.inEntTel,lc.purchaseUnitAddr
into formID,PurchaseUnitID,ProviderID,FormCreatorID,FormCreateDate,PurchaseDate,PurchaseUnitName,ProviderName,AuditState,providerAddress,FormCreatorMC,sumGoodsAmount,allPrice,isAutoLedger,ledgerTel,inEntTel,purchaseUnitAddr
from LedgerOrderCertificate lc where lc.formID=formID;
set ledgerType2=3;END IF;IF ledgerType=1 THEN
select lc.FormID,lc.PurchaseUnitID,lc.ProviderID,lc.FormCreatorID,lc.FormCreateDate,lc.PurchaseDate,lc.PurchaseUnitName,lc.ProviderName,lc.AuditState,lc.providerAddress,lc.FormCreatorMC,lc.sumGoodsAmount,lc.allPrice,lc.isAutoLedger,lc.ledgerTel,lc.inEntTel,lc.purchaseUnitAddr
into formID,ProviderID,PurchaseUnitID,FormCreatorID,FormCreateDate,PurchaseDate,ProviderName,PurchaseUnitName,AuditState,purchaseUnitAddr,FormCreatorMC,sumGoodsAmount,allPrice,isAutoLedger,inEntTel,ledgerTel,providerAddress
from LedgerOrderCertificate lc where lc.formID=formID;
set ledgerType2=1;
set ledgerType=3;
END IF;
call insertLedgerLc_new(
FormID ,
PurchaseUnitID ,
ProviderID ,
FormCreatorID ,
FormCreateDate ,
PurchaseDate ,
LedgerType,
PurchaseUnitName ,
ProviderName,
AuditState,
providerAddress ,
FormCreatorMC ,
sumGoodsAmount ,
allPrice,
isAutoLedger ,
ledgerTel ,
inEntTel ,
purchaseUnitAddr);IF ledgerType2=1 THEN
select lc.FormID,lc.PurchaseUnitID,lc.ProviderID,lc.FormCreatorID,lc.FormCreateDate,lc.PurchaseDate,lc.PurchaseUnitName,lc.ProviderName,lc.AuditState,lc.providerAddress,lc.FormCreatorMC,lc.sumGoodsAmount,lc.allPrice,lc.isAutoLedger,lc.ledgerTel,lc.inEntTel,lc.purchaseUnitAddr
into formID,ProviderID,PurchaseUnitID,FormCreatorID,FormCreateDate,PurchaseDate,ProviderName,PurchaseUnitName,AuditState,purchaseUnitAddr,FormCreatorMC,sumGoodsAmount,allPrice,isAutoLedger,inEntTel,ledgerTel,providerAddress
from LedgerOrderCertificate lc where lc.formID=formID;
END IF;
IF ledgerType2=3 THEN
select lc.FormID,lc.PurchaseUnitID,lc.ProviderID,lc.FormCreatorID, lc.FormCreateDate,lc.PurchaseDate,lc.PurchaseUnitName,lc.ProviderName,lc.AuditState,lc.providerAddress,lc.FormCreatorMC,lc.sumGoodsAmount,lc.allPrice,lc.isAutoLedger,lc.ledgerTel,lc.inEntTel,lc.purchaseUnitAddr
into formID,PurchaseUnitID,ProviderID,FormCreatorID,FormCreateDate,PurchaseDate,PurchaseUnitName,ProviderName,AuditState,providerAddress,FormCreatorMC,sumGoodsAmount,allPrice,isAutoLedger,ledgerTel,inEntTel,purchaseUnitAddr
from LedgerOrderCertificate lc where lc.formID=formID;
END IF;
call insertLedgerLc_new(
FormID ,
PurchaseUnitID ,
ProviderID ,
FormCreatorID ,
FormCreateDate ,
PurchaseDate ,
LedgerType2,
PurchaseUnitName ,
ProviderName,
AuditState,
providerAddress ,
FormCreatorMC ,
sumGoodsAmount ,
allPrice,
isAutoLedger ,
ledgerTel ,
inEntTel ,
purchaseUnitAddr);END
在where条件的结尾加limit 1
检查你行中的 select .... into ...