最近在做一个项目,大部分调用数据库的代码都是用SQL语句直接写的,没发生异常情况,今天想用存储过程来实现结果却总出现问题,实在没办法了才来请教,我也搜索了很多,却还是没有找到问题的答案,下面是我的代码
Statement state = null;
ResultSet rs = null;
boolean flag = false;
String sql = null;
try {
con.setAutoCommit(false);
sql = "insert into T_ORDERINFO (ORDERNO, USERID, USERNAME, USERADDRESS,"
+ " USERCODE, USERPHONE, USERMAIL, ORDERTIME, PAYTYPE, CAMIEDTYPE, HASAPPROVED)"
+ " values("
+ orderBean.getOrderNo()
+ ","
+ orderBean.getUserId()
+ ",'"
+ orderBean.getUserName()
+ "','"
+ orderBean.getUserAddress()
+ "','"
+ orderBean.getUserCode()
+ "','"
+ orderBean.getUserPhone()
+ "','"
+ orderBean.getUserMail()
+ "',to_date('"
+ CommonUtil.dateToString(orderBean.getOrdertime(),
"yyyy-MM-dd")
+ "','yyyy-mm-dd'),'"
+ orderBean.getPayType()
+ "','"
+ orderBean.getCamiedType() + "',2)"; 这是SQL的代码 可以很好的实 现 ,下面是存储过程的:
cstmt.setInt(1,orderBean.getOrderNo());
cstmt.setInt(2,orderBean.getUserId());
cstmt.setString(3,orderBean.getUserName());
cstmt.setString(4,orderBean.getUserAddress());
cstmt.setString(5,orderBean.getUserCode());
cstmt.setString(6,orderBean.getUserPhone());
cstmt.setString(7,orderBean.getUserMail());
cstmt.setString(8,CommonUtil.dateToString(orderBean.getOrdertime(),
"yyyy-MM-dd"));
cstmt.setString(9,orderBean.getPayType());
cstmt.setString(10,orderBean.getCamiedType());
cstmt.setInt(11,2);
cstmt.execute(); 存储过程:
is
begin
insert into T_ORDERINFO (ORDERNO, USERID, USERNAME, USERADDRESS, USERCODE, USERPHONE, USERMAIL, ORDERTIME, PAYTYPE, CAMIEDTYPE, HASAPPROVED)
values
(pORDERNO, pUSERID,pUSERNAME,pUSERADDRESS,pUSERCODE,pUSERPHONE,pUSERMAIL,to_date(pORDERTIME,'yyyy-mm-dd'),pPAYTYPE,pCAMIEDTYPE,pHASAPPROVED);
end SubmitOrder; 下面是表
:
orderNo int primary key,
userId int not null,
userName varchar(200) not null,
userAddress varchar2(200) not null,
userCode varchar2(10) not null,
userPhone varchar2(20),
userMail varchar2(50) not null,
ordertime date,
payType varchar2(50),
camiedType varchar2(50),
hasapproved int,
result varchar(200),
approvedUserId int,
approvedDate date
);用存储过程就会出现:
ORA-00001: 违反唯一约束条件 (SYSTEM.SYS_C005751)
ORA-06512: 在 "SYSTEM.SUBMITORDER", line 3
我已经直接调用过存储过程:
是好用的 拿到程序里就错了。哪有大神有时间帮忙看下啊![14.gif](https://i-blog.csdnimg.cn/blog_migrate/ebeb01fbca05d2b45d5b007f95033ffc.png)
解决办法是 : 程序中多用了一句 cstmt.executeUpdate() 导致出现的错误 ,删除掉之后错误消失,不过之前执行的数据并没有插入到数据库,却出现了违反唯一性。![35.gif](https://i-blog.csdnimg.cn/blog_migrate/90d53077e2ddb5dbe1d06c42c8b55025.png)
![35.gif](https://i-blog.csdnimg.cn/blog_migrate/90d53077e2ddb5dbe1d06c42c8b55025.png)
虽然问题解决了,还是不懂ORACLE运行机制。 还得努力!!! 谢谢回帖的大神们。
[ 本帖最后由 colin_cat 于 2011-6-25 10:09 编辑 ]
CODE:
Connection con = DBUtil.getConnection();Statement state = null;
ResultSet rs = null;
boolean flag = false;
String sql = null;
try {
con.setAutoCommit(false);
sql = "insert into T_ORDERINFO (ORDERNO, USERID, USERNAME, USERADDRESS,"
+ " USERCODE, USERPHONE, USERMAIL, ORDERTIME, PAYTYPE, CAMIEDTYPE, HASAPPROVED)"
+ " values("
+ orderBean.getOrderNo()
+ ","
+ orderBean.getUserId()
+ ",'"
+ orderBean.getUserName()
+ "','"
+ orderBean.getUserAddress()
+ "','"
+ orderBean.getUserCode()
+ "','"
+ orderBean.getUserPhone()
+ "','"
+ orderBean.getUserMail()
+ "',to_date('"
+ CommonUtil.dateToString(orderBean.getOrdertime(),
"yyyy-MM-dd")
+ "','yyyy-mm-dd'),'"
+ orderBean.getPayType()
+ "','"
+ orderBean.getCamiedType() + "',2)"; 这是SQL的代码 可以很好的实 现 ,下面是存储过程的:
CODE:
CallableStatement cstmt = con.prepareCall("call SubmitOrder(?,?,?,?,?,?,?,?,?,?,?)");cstmt.setInt(1,orderBean.getOrderNo());
cstmt.setInt(2,orderBean.getUserId());
cstmt.setString(3,orderBean.getUserName());
cstmt.setString(4,orderBean.getUserAddress());
cstmt.setString(5,orderBean.getUserCode());
cstmt.setString(6,orderBean.getUserPhone());
cstmt.setString(7,orderBean.getUserMail());
cstmt.setString(8,CommonUtil.dateToString(orderBean.getOrdertime(),
"yyyy-MM-dd"));
cstmt.setString(9,orderBean.getPayType());
cstmt.setString(10,orderBean.getCamiedType());
cstmt.setInt(11,2);
cstmt.execute(); 存储过程:
CODE:
create or replace procedure SubmitOrder(pORDERNO in t_OrderInfo.orderNo%TYPE, pUSERID in t_OrderInfo.USERID%TYPE, pUSERNAME in t_OrderInfo.USERNAME%TYPE, pUSERADDRESS in t_OrderInfo.USERADDRESS%TYPE,pUSERCODE in t_OrderInfo.USERCODE%TYPE, pUSERPHONE in t_OrderInfo.USERPHONE%TYPE, pUSERMAIL in t_OrderInfo.USERMAIL%TYPE, pORDERTIME in varchar2, pPAYTYPE in t_OrderInfo.PAYTYPE%TYPE, pCAMIEDTYPE in t_OrderInfo.CAMIEDTYPE%TYPE, pHASAPPROVED in t_OrderInfo.HASAPPROVED%TYPE )is
begin
insert into T_ORDERINFO (ORDERNO, USERID, USERNAME, USERADDRESS, USERCODE, USERPHONE, USERMAIL, ORDERTIME, PAYTYPE, CAMIEDTYPE, HASAPPROVED)
values
(pORDERNO, pUSERID,pUSERNAME,pUSERADDRESS,pUSERCODE,pUSERPHONE,pUSERMAIL,to_date(pORDERTIME,'yyyy-mm-dd'),pPAYTYPE,pCAMIEDTYPE,pHASAPPROVED);
end SubmitOrder; 下面是表
:
CODE:
create table t_OrderInfo(orderNo int primary key,
userId int not null,
userName varchar(200) not null,
userAddress varchar2(200) not null,
userCode varchar2(10) not null,
userPhone varchar2(20),
userMail varchar2(50) not null,
ordertime date,
payType varchar2(50),
camiedType varchar2(50),
hasapproved int,
result varchar(200),
approvedUserId int,
approvedDate date
);用存储过程就会出现:
ORA-00001: 违反唯一约束条件 (SYSTEM.SYS_C005751)
ORA-06512: 在 "SYSTEM.SUBMITORDER", line 3
我已经直接调用过存储过程:
CODE:
CALL SubmitOrder (1004,60,'colin_cat','ll','11','11','11@1.COM','2004-12-10','货到付款','邮寄',2);是好用的 拿到程序里就错了。哪有大神有时间帮忙看下啊
![14.gif](https://i-blog.csdnimg.cn/blog_migrate/ebeb01fbca05d2b45d5b007f95033ffc.png)
解决办法是 : 程序中多用了一句 cstmt.executeUpdate() 导致出现的错误 ,删除掉之后错误消失,不过之前执行的数据并没有插入到数据库,却出现了违反唯一性。
![35.gif](https://i-blog.csdnimg.cn/blog_migrate/90d53077e2ddb5dbe1d06c42c8b55025.png)
![35.gif](https://i-blog.csdnimg.cn/blog_migrate/90d53077e2ddb5dbe1d06c42c8b55025.png)
虽然问题解决了,还是不懂ORACLE运行机制。 还得努力!!! 谢谢回帖的大神们。
[ 本帖最后由 colin_cat 于 2011-6-25 10:09 编辑 ]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24678025/viewspace-701080/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24678025/viewspace-701080/