Oracle9 报"并非所有变量都已关联"的错误

[mengchanmian]

我最近用JAVA底层的代码链接Oracle数据库总是报以下错误:

    java.sql.SQLException: ORA-01008: 并非所有变量都已关联

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3423)
	at com.jdim.database.dao.implay.MemberIncomeDAOImplay.updateMemberDAO(MemberIncomeDAOImplay.java:190)
	at com.jdim.database.service.DataService.operBillService(DataService.java:99)
	at com.jdim.database.service.DataService.dataMain(DataService.java:77)
	at com.jdim.main.ChargeMain.chargeMainTest(ChargeMain.java:79)
	at com.jdim.main.ChargeTest.main(ChargeTest.java:14)

我写的代码具体如下:

//优惠券铃声或者企业铃声
					memSql = " merge into membernum using (select count(id) count from membernum where phone = ? and ringid = ? ) num2 on (num2.count <> 0 ) " +
							"when matched then update set balance = balance + ?, totalnum = totalnum + ? " +
							"when not matched then insert (id, phone,ringid,totalnum,balance)" +
							" values (SEQ_MEMBERINCOME.nextval, ? , ? , 0 , 0)" ;
					memPst = connection.prepareStatement(memSql);
					memPst.setObject(1, resultSet.getString(1));
					memPst.setObject(2, resultSet.getInt(5));
					memPst.setObject(3, resultSet.getInt(4));
					memPst.setObject(4, resultSet.getInt(4));
					memPst.setObject(5, resultSet.getString(1));
					memPst.setObject(6, resultSet.getInt(5));
memPst.executeUpdate() ; 

它总是报上面的错误,急,大家帮忙看下吧

[mengchanmian]

我把所有的问号都换成常量写在SQL语句里面就正常,写成"?",就报错,都找了一天啊,大家帮忙看下吧.

[SambaGao]

一般是因为
你的ＳＱＬ文有问题！仔细看一下！应该是少了变量值！
再想想你有没有触发器什么的。
是不是变量不对。
先把?改成死数据测试一下看可以吧。

[SambaGao]

你再接着把
memPst.setObject(1, resultSet.getString(1));
memPst.setObject(2, resultSet.getInt(5));
memPst.setObject(3, resultSet.getInt(4));
memPst.setObject(4, resultSet.getInt(4));
memPst.setObject(5, resultSet.getString(1));
memPst.setObject(6, resultSet.getInt(5));
也写死了。接着测

[mengchanmian]

呵呵,楼上的兄弟说的方法我测试过了,还是有问题,我写死的时候就没有问题,
不过我换了种方式,搞定了.

memSql = " merge into membernum using (select count(id) count from membernum where phone = " +resultSet.getString(1)+
							" and ringid = "+resultSet.getInt(5)+" ) num2 on (num2.count <> 0 ) " +
							"when matched then update set balance = balance + "+resultSet.getInt(4)+", totalnum = totalnum + " + resultSet.getInt(4) +
							"when not matched then insert (id, phone,ringid,totalnum,balance)" +
							" values (SEQ_MEMBERINCOME.nextval, "+resultSet.getString(1)+" , "+resultSet.getInt(5)+" , "
							+resultSet.getInt(4)+" , "+resultSet.getInt(4)+")" ;
					memPst = connection.prepareStatement(memSql);
					memPst.execute(memSql);

[mengchanmian]

唉,人是逼出来的,呵呵,

[zhao_tk]

http://topic.csdn.net/t/20020514/13/720747.html

[crazylaa]

[Quote=引用 4 楼 minjiaren 的回复:]
呵呵,楼上的兄弟说的方法我测试过了,还是有问题,我写死的时候就没有问题,
不过我换了种方式,搞定了.
Java code
memSql=" merge into membernum using (select count(id) count from membernum where phone ="+resultSet.getString(1)+" and ringid ="+resultSet.getInt(5)+" ) num2 on (num2.count <> 0 )"+"when matched then update set balance = balance +"+resultSet.getInt(4)+", totalnum = totalnum +"+ resultSet.getInt(4)+"when not matched then insert (id, phone,ringid,totalnum,balance)"+" values (SEQ_MEMBERINCOME.nextval,"+resultSet.getString(1)+" ,"+resultSet.getInt(5)+" ,"+resultSet.getInt(4)+" ,"+resultSet.getInt(4)+")" ;
memPst= connection.prepareStatement(memSql);
memPst.execute(memSql);
[/Quote]
可能是某个值出现null。
另外，不建议都用setObject
你试试看用对应的比如setString,setInt,之类的，oracle的类型转换有时候不是像你想的那样，Object不能搞定一切。

[SambaGao]

[Quote=引用 7 楼 crazylaa 的回复:]
可能是某个值出现null。
另外，不建议都用setObject
你试试看用对应的比如setString,setInt,之类的，oracle的类型转换有时候不是像你想的那样，Object不能搞定一切。
[/Quote]

试试

[zhuwen9]

难道是我眼花..
只看到3个 ? 却set了6个参数

[xinguoyao]

1. SQL语句中使用？作为参数的占位符
2. 通过Statement的set方法进行赋值
3. 调用execute
4. 通过resultset的get获取结果

你的SQL种有3个问号，即需要set 3 个参数，目前的代码中set了6个参数，所以执行时报错。

[uuuuuuuuuuuuu]

换成10G