2,596
社区成员
发帖
与我相关
我的任务
分享请叫大神一个sybase存储过程中插入数据的错误，小人新手一枚
源码如下：
CREATE PROCEDURE dbo.sp_unittake_static4
(
@begintime datetime,
@endtime datetime
)
as
create table #t_bus_unit_total_report
(
id numeric(18,0) identity,
unit_id varchar(20) null ,
unit_name varchar(60) null,
gongdanshu numeric(18,0) default 0 null ,
zixun numeric(18,0) default 0 null ,
tousu numeric(18,0) default 0 null,
jubao numeric(18,0) default 0 null,
shensu numeric(18,0) default 0 null,
qiuzhu numeric(18,0) default 0 null,
jianyi numeric(18,0) default 0 null,
biaoyang numeric(18,0) default 0 null,
liuyan numeric(18,0) default 0 null,
jiedanshu numeric(18,0) default 0 null,
chulizhong numeric(18,0) default 0 null,
yiwancheng numeric(18,0) default 0 null,
chaoshishu numeric(18,0) default 0 null
)
begin
insert into #t_bus_unit_total_report (unit_id,unit_name,gongdanshu,zixun,tousu,jubao,shensu,qiuzhu,jianyi,
biaoyang,liuyan,jiedanshu,chulizhong,yiwancheng,chaoshishu)
select id, unit_name,0,0,0,0,0,0,0,0,0,0,0,0,0 from t_unit order by id
end
declare @unit_id varchar(20)
declare @unit_name varchar(60)
declare @gongdanshu numeric(18,0)
declare @zixun numeric(18,0)
declare @tousu numeric(18,0)
declare @jubao numeric(18,0)
declare @shensu numeric(18,0)
declare @qiuzhu numeric(18,0)
declare @jianyi numeric(18,0)
declare @biaoyang numeric(18,0)
declare @liuyan numeric(18,0)
declare @jiedanshu numeric(18,0)
declare @chulizhong numeric(18,0)
declare @yiwancheng numeric(18,0)
declare @chaoshishu numeric(18,0)
begin
select t.unit_id, count(t.id) as zixun_cnt into #t1
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='5' group by t.unit_id
update #t_bus_unit_total_report set zixun = zixun_cnt
from #t_bus_unit_total_report t,#t1 t1
where t.unit_id = t1.unit_id
drop table #t1
select t.unit_id, count(t.id) as tousu_cnt into #t2
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='1' and b.bus_type='7' group by t.unit_id
update #t_bus_unit_total_report set tousu = tousu_cnt
from #t_bus_unit_total_report t,#t2 t2
where t.unit_id = t2.unit_id
drop table #t2
select t.unit_id, count(t.id) as jubao_cnt into #t3
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='4' group by t.unit_id
update #t_bus_unit_total_report set jubao = jubao_cnt
from #t_bus_unit_total_report t,#t3 t3
where t.unit_id = t3.unit_id
drop table #t3
select t.unit_id, count(t.id) as shensu_cnt into #t4
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='0' group by t.unit_id
update #t_bus_unit_total_report set shensu = shensu_cnt
from #t_bus_unit_total_report t,#t4 t4
where t.unit_id = t4.unit_id
drop table #t4
select t.unit_id, count(t.id) as qiuzhu_cnt into #t5
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='6' group by t.unit_id
update #t_bus_unit_total_report set qiuzhu = qiuzhu_cnt
from #t_bus_unit_total_report t,#t5 t5
where t.unit_id = t5.unit_id
drop table #t5
select t.unit_id, count(t.id) as jianyi_cnt into #t6
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='2' group by t.unit_id
update #t_bus_unit_total_report set jianyi = jianyi_cnt
from #t_bus_unit_total_report t,#t6 t6
where t.unit_id = t6.unit_id
drop table #t6
select t.unit_id, count(t.id) as biaoyang_cnt into #t7
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='3' group by t.unit_id
update #t_bus_unit_total_report set biaoyang = biaoyang_cnt
from #t_bus_unit_total_report t,#t7 t7
where t.unit_id = t7.unit_id
drop table #t7
select t.unit_id, count(t.id) as liuyan_cnt into #t8
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='3' group by t.unit_id
update #t_bus_unit_total_report set liuyan = liuyan_cnt
from #t_bus_unit_total_report t,#t8 t8
where t.unit_id = t8.unit_id
drop table #t8
select t.unit_id, count(t.id) as chulizhong_cnt into #t9
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and (b.limit_time>b.did_time or b.did_time is NULL) and b.take_state='2' group by t.unit_id
update #t_bus_unit_total_report set chulizhong = chulizhong_cnt
from #t_bus_unit_total_report t,#t9 t9
where t.unit_id = t9.unit_id
drop table #t9
select t.unit_id, count(t.id) as yiwancheng_cnt into #t10
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.take_state='5'and b.take_state='6' group by t.unit_id
update #t_bus_unit_total_report set yiwancheng = yiwancheng_cnt
from #t_bus_unit_total_report t,#t10 t10
where t.unit_id = t10.unit_id
drop table #t10
select t.unit_id, count(t.id) as chaoshishu_cnt into #t11
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.limit_time>b.did_time and b.do_state!='5'and b.do_state!='6' group by t.unit_id
update #t_bus_unit_total_report set chaoshishu = chaoshishu_cnt
from #t_bus_unit_total_report t,#t11 t11
where t.unit_id = t11.unit_id
drop table #t11
select @gongdanshu = sum(zixun)+sum(tousu)+sum(jubao)+sum(shensu)+sum(qiuzhu)+sum(jianyi)+sum(biaoyang)+sum(liuyan) from #t_bus_unit_total_report
select @jiedanshu = sum(chulizhong)+sum(yiwancheng)+sum(chaoshishu) from #t_bus_unit_total_report
insert into #t_bus_unit_total_report
select '0000','?¨¹??',
sum(zixun),sum(tousu),sum(jubao),sum(shensu),sum(qiuzhu),sum(jianyi),sum(biaoyang),sum(liuyan),sum(gongdanshu)
from #t_bus_unit_total_report
select
unit_id,
unit_name,
"gongdandanshu"=zixun+tousu+jubao+shensu+qiuzhu+jianyi+biaoyang+liuyan,
zixun,
tousu,
jubao,
shensu,
qiuzhu,
jianyi,
biaoyang,
liuyan,
"jiedanshu"=chulizhong+yiwancheng+chaoshishu,
chulizhong,
yiwancheng,
chaoshishu
from #t_bus_unit_total_report order by id
drop table #t_bus_unit_total_report
end
CREATE PROCEDURE dbo.sp_unittake_static4
(
@begintime datetime,
@endtime datetime
)
as
create table #t_bus_unit_total_report
(
id numeric(18,0) identity,
unit_id varchar(20) null ,
unit_name varchar(60) null,
gongdanshu numeric(18,0) default 0 null ,
zixun numeric(18,0) default 0 null ,
tousu numeric(18,0) default 0 null,
jubao numeric(18,0) default 0 null,
shensu numeric(18,0) default 0 null,
qiuzhu numeric(18,0) default 0 null,
jianyi numeric(18,0) default 0 null,
biaoyang numeric(18,0) default 0 null,
liuyan numeric(18,0) default 0 null,
jiedanshu numeric(18,0) default 0 null,
chulizhong numeric(18,0) default 0 null,
yiwancheng numeric(18,0) default 0 null,
chaoshishu numeric(18,0) default 0 null
)
begin
insert into #t_bus_unit_total_report (unit_id,unit_name,gongdanshu,zixun,tousu,jubao,shensu,qiuzhu,jianyi,
biaoyang,liuyan,jiedanshu,chulizhong,yiwancheng,chaoshishu)
select id, unit_name,0,0,0,0,0,0,0,0,0,0,0,0,0 from t_unit order by id
end
declare @unit_id varchar(20)
declare @unit_name varchar(60)
declare @gongdanshu numeric(18,0)
declare @zixun numeric(18,0)
declare @tousu numeric(18,0)
declare @jubao numeric(18,0)
declare @shensu numeric(18,0)
declare @qiuzhu numeric(18,0)
declare @jianyi numeric(18,0)
declare @biaoyang numeric(18,0)
declare @liuyan numeric(18,0)
declare @jiedanshu numeric(18,0)
declare @chulizhong numeric(18,0)
declare @yiwancheng numeric(18,0)
declare @chaoshishu numeric(18,0)
begin
select t.unit_id, count(t.id) as zixun_cnt into #t1
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='5' group by t.unit_id
update #t_bus_unit_total_report set zixun = zixun_cnt
from #t_bus_unit_total_report t,#t1 t1
where t.unit_id = t1.unit_id
drop table #t1
select t.unit_id, count(t.id) as tousu_cnt into #t2
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='1' and b.bus_type='7' group by t.unit_id
update #t_bus_unit_total_report set tousu = tousu_cnt
from #t_bus_unit_total_report t,#t2 t2
where t.unit_id = t2.unit_id
drop table #t2
select t.unit_id, count(t.id) as jubao_cnt into #t3
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='4' group by t.unit_id
update #t_bus_unit_total_report set jubao = jubao_cnt
from #t_bus_unit_total_report t,#t3 t3
where t.unit_id = t3.unit_id
drop table #t3
select t.unit_id, count(t.id) as shensu_cnt into #t4
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='0' group by t.unit_id
update #t_bus_unit_total_report set shensu = shensu_cnt
from #t_bus_unit_total_report t,#t4 t4
where t.unit_id = t4.unit_id
drop table #t4
select t.unit_id, count(t.id) as qiuzhu_cnt into #t5
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='6' group by t.unit_id
update #t_bus_unit_total_report set qiuzhu = qiuzhu_cnt
from #t_bus_unit_total_report t,#t5 t5
where t.unit_id = t5.unit_id
drop table #t5
select t.unit_id, count(t.id) as jianyi_cnt into #t6
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='2' group by t.unit_id
update #t_bus_unit_total_report set jianyi = jianyi_cnt
from #t_bus_unit_total_report t,#t6 t6
where t.unit_id = t6.unit_id
drop table #t6
select t.unit_id, count(t.id) as biaoyang_cnt into #t7
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='3' group by t.unit_id
update #t_bus_unit_total_report set biaoyang = biaoyang_cnt
from #t_bus_unit_total_report t,#t7 t7
where t.unit_id = t7.unit_id
drop table #t7
select t.unit_id, count(t.id) as liuyan_cnt into #t8
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.bus_type='3' group by t.unit_id
update #t_bus_unit_total_report set liuyan = liuyan_cnt
from #t_bus_unit_total_report t,#t8 t8
where t.unit_id = t8.unit_id
drop table #t8
select t.unit_id, count(t.id) as chulizhong_cnt into #t9
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and (b.limit_time>b.did_time or b.did_time is NULL) and b.take_state='2' group by t.unit_id
update #t_bus_unit_total_report set chulizhong = chulizhong_cnt
from #t_bus_unit_total_report t,#t9 t9
where t.unit_id = t9.unit_id
drop table #t9
select t.unit_id, count(t.id) as yiwancheng_cnt into #t10
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.take_state='5'and b.take_state='6' group by t.unit_id
update #t_bus_unit_total_report set yiwancheng = yiwancheng_cnt
from #t_bus_unit_total_report t,#t10 t10
where t.unit_id = t10.unit_id
drop table #t10
select t.unit_id, count(t.id) as chaoshishu_cnt into #t11
from #t_bus_unit_total_report t,t_bus_unit b
where t.unit_id = b.unit_id and b.bus_time>@begintime and b.bus_time<@endtime and b.limit_time>b.did_time and b.do_state!='5'and b.do_state!='6' group by t.unit_id
update #t_bus_unit_total_report set chaoshishu = chaoshishu_cnt
from #t_bus_unit_total_report t,#t11 t11
where t.unit_id = t11.unit_id
drop table #t11
select @gongdanshu = sum(zixun)+sum(tousu)+sum(jubao)+sum(shensu)+sum(qiuzhu)+sum(jianyi)+sum(biaoyang)+sum(liuyan) from #t_bus_unit_total_report
select @jiedanshu = sum(chulizhong)+sum(yiwancheng)+sum(chaoshishu) from #t_bus_unit_total_report
insert into #t_bus_unit_total_report
select '0000','?¨¹??',
sum(zixun),sum(tousu),sum(jubao),sum(shensu),sum(qiuzhu),sum(jianyi),sum(biaoyang),sum(liuyan),sum(gongdanshu)
from #t_bus_unit_total_report
select
unit_id,
unit_name,
"gongdandanshu"=zixun+tousu+jubao+shensu+qiuzhu+jianyi+biaoyang+liuyan,
zixun,
tousu,
jubao,
shensu,
qiuzhu,
jianyi,
biaoyang,
liuyan,
"jiedanshu"=chulizhong+yiwancheng+chaoshishu,
chulizhong,
yiwancheng,
chaoshishu
from #t_bus_unit_total_report order by id
drop table #t_bus_unit_total_report
end
...全文
请发表友善的回复…
发表回复
永爱果果 2015-02-02
- 打赏
- 举报
你确定你的列明都写对了吗
Sybase数据库恢复 2015-01-23
- 打赏
- 举报
insert 中的列名是否与数据库中表定义列名一致？
天明过后 2015-01-21
- 打赏
- 举报
数了一致的，t_unit表的类型也是varchar
天明过后 2015-01-21
- 打赏
- 举报
数了一致的，t_unit表的类型也是varchar
Sybase数据库恢复 2015-01-21
- 打赏
- 举报
com.sybase.jdbc3.jdbc.SybSQLException: Insert error: column name or number of supplied values does not match table definition.
逐个数一下insert 的列与values的列数量是否一致？
天明过后 2015-01-21
- 打赏
- 举报
错误代码
com.sybase.jdbc3.jdbc.SybSQLException: Insert error: column name or number of supplied values does not match table definition.
at com.sybase.jdbc3.tds.Tds.processEed(Tds.java:2942)
at com.sybase.jdbc3.tds.Tds.nextResult(Tds.java:2246)
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(SybStatement.java:220)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(SybStatement.java:203)
at com.sybase.jdbc3.jdbc.SybStatement.executeLoop(SybStatement.java:1868)
at com.sybase.jdbc3.jdbc.SybStatement.execute(SybStatement.java:1860)
at com.sybase.jdbc3.jdbc.SybStatement.execute(SybStatement.java:865)
at com.sybase.aseplugin.sql.ASConnection.executeQuery(ASConnection.java:515)
at com.sybase.aseplugin.sql.ASConnection.executeCommand(ASConnection.java:451)
at com.sybase.aseplugin.so.StoredProcedureSO.create(StoredProcedureSO.java:65)
at com.sybase.aseplugin.bo.ASEBaseCodeContainer.saveObject(ASEBaseCodeContainer.java:166)
at com.sybase.aseplugin.bo.ASEBaseCodeContainer.editorSaveRequested(ASEBaseCodeContainer.java:152)
at com.sybase.central.editor.SCEditorFrame.fireSave(SCEditorFrame.java:2949)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.sybase.central.editor.ReflectiveAction.actionPerformed(ReflectiveAction.java:119)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786)
at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245)
at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:231)
at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:231)
at java.awt.Component.processMouseEvent(Component.java:5100)
at java.awt.Component.processEvent(Component.java:4897)
at java.awt.Container.processEvent(Container.java:1569)
at java.awt.Component.dispatchEventImpl(Component.java:3615)
at java.awt.Container.dispatchEventImpl(Container.java:1627)
at java.awt.Component.dispatchEvent(Component.java:3477)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:3483)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128)
at java.awt.Container.dispatchEventImpl(Container.java:1613)
at java.awt.Window.dispatchEventImpl(Window.java:1606)
at java.awt.Component.dispatchEvent(Component.java:3477)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:456)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
