作者:手机用户2502932937 | 来源:互联网 | 2023-10-11 08:21
我的存储过程统计若干值并写入数据库,我是这样写的:createorreplaceproceduremypro(xzqhinvarchar2,)asbegininser
我的存储过程统计若干值并写入数据库,我是这样写的:
create or replace procedure mypro(xzqh in varchar2,...)
as
begin
insert into mytable (field1,field2....) values
(
(select count(kcbh) from mytable1 where instr(kcbh,xzqh)>0 ),
...
);
commit;
end;
在asp调用这个存储过程时候,其他字段都能正确统计到,惟独field1的值始终是0,
在表mytable1里的kcbh字段的值是"k210105101200600001"之类,我传入的参数xzqh的值是"210105101",但是始终得不到正确结果(已知统计结果肯定不是0),请问我到底错在哪里,请高手指点,解决即给分.(我已经为此郁闷到凌晨4点多了)
12 个解决方案
执行select count(kcbh) from mytable1 where instr(kcbh,'210105101')>0的结果是大于0吗?存储过程中不用参数直接写
(select count(kcbh) from mytable1 where instr(kcbh,'210105101')>0),
看看是否能写入值。
谢谢匆匆过客的提示,问题已经解决了。昨晚脑筋短路,忘记了在mytable1里本来就有xzqh这个字段,所以使用它作为传入的参数名,就出错了,害得我白忙活了大半个晚上,希望其他新手引以为戒。现在出现这样的问题,我在oracle9i下调试正常的存储过程,放到8i下始终不能编译成功,是不是版本差异啊,附上相关信息,恳请高手指点。
存储过程是:
create or replace procedure rpt104701pro
(
mydanwei in varchar2,
tjsjfrom in date,
tjsjto in date,
strajlb in varchar2,
mycode in varchar2,
rpt_sq in varchar2,
mydanweiname in varchar2,
myxzqh in varchar2
)
as
begin
insert into rpt104701
(
danwei,user_code,rpt_sq,
COL1,
COL2,
COL3,
COL4,
COL5,
COL6,
COL7,
COL8,
COL9,
COL10,
COL11
)
values
(mydanweiname,mycode,rpt_sq,
--COL1
(select count(ajbh) from ajslb where instr(ajbh,myxzqh)=2 and (state='02' or state='03') and fasjcz between tjsjfrom and tjsjto and instr(strajlb,ab1)>0),
--COL2
(select count(xc_kcbh) from xc_main_info where xc_kcdw=mydanwei and xc_scbz='1' and xc_kcrq between tjsjfrom and tjsjto and instr(strajlb,xc_ajlb)>0),
--COL3
(select count(distinct(wz_kcbh)) from view_wz_xc where xc_kcdw=mydanwei and xc_scbz='1' and xc_kcrq between tjsjfrom and tjsjto and instr(strajlb,xc_ajlb)>0),
--COL4
(select count(xc_kcbh) from xc_main_info where xc_kcdw=mydanwei and xc_ajfxbgs>0 and xc_kcrq between tjsjfrom and tjsjto and instr(strajlb,xc_ajlb)>0),
--COL5
(select count(xc_kcbh) from xc_main_info where xc_kcdw=mydanwei and xc_xccls>=3 and xc_kcrq between tjsjfrom and tjsjto and instr(strajlb,xc_ajlb)>0),
--COL6
(select count(distinct(wz_kcbh)) from view_wz_xc where xc_kcdw=mydanwei and wz_zt<>'101007' and xc_kcrq between tjsjfrom and tjsjto and instr(strajlb,xc_ajlb)>0),
--COL7
(select count(xc_kcbh) from xc_main_info where xc_kcdw=mydanwei and xc_jyjls>0 and xc_kcrq between tjsjfrom and tjsjto and instr(strajlb,xc_ajlb)>0),
--COL8
(select count(ws_kcbh) from ws_info where ws_jydw=mydanwei and (ws_fyhjjl='101702' or ws_fyhjjl='101703') and ws_kcbh like '%H%' and ws_zt<>'101202' and ws_lrsj between tjsjfrom and tjsjto),
--COL9
(select count(user_code) from userlist where dpt_code=mydanwei and (engage_major='100401' or engage_major='100402' or engage_major='100403')),
--COL10
(select count(xc_kcbh) from xc_main_info where xc_kcdw=mydanwei and xc_scbz='1' and xc_kcrq between tjsjfrom and tjsjto),
--COL11
((select count(ws_kcbh) from ws_info where ws_jydw=mydanwein and ws_zt<>'101202' and ws_kcbh like '%H%' and ws_lrsj between tjsjfrom and tjsjto)+(select count(fy_jybh) from fy_info where fy_jydw=mydanwei and fy_jybh like '%S%' and fy_jyrq between tjsjfrom and tjsjto)));
commit;
end;
在oracle8i下编译时,出错,下面是pl/sql的错误报告:
32 PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others
avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
32 PLS-00103: Encountered the symbol "," when expecting one of the following:
; return returning and or
52 PLS-00103: Encountered the symbol "+" when expecting one of the following:
) with intersect minus order union
The symbol "intersect was inserted before "+" to continue.
52 PLS-00103: Encountered the symbol ")" when expecting one of the following:
, ; for
group having intersect
minus order start union where connect
急盼高手赐教。
create or replace procedure mypro(xzqh in varchar2,...)
as
begin
insert into mytable (field1,field2....) values
(
(select count(kcbh) from mytable1 where instr(kcbh,xzqh)=2), //你把此处改成这样试试
...
);
commit;
end;
谢谢oklsl,前面的插入数据库问题已将解决了,现在的问题是,我在oracle9i下调试正常的存储过程,放到8i下始终不能编译成功,存储过程代码以及出错信息我在前面已经贴出来了,请大家帮忙看看好不好。
估计是8i不支持insert into ... values中有子查询,把各个子查询的值先保存到变量中再插入。
请问,在存储过程中,如何把“查询的值先保存到变量中”啊
create or replace procedure rpt104701pro
(
mydanwei in varchar2,
tjsjfrom in date,
tjsjto in date,
strajlb in varchar2,
mycode in varchar2,
rpt_sq in varchar2,
mydanweiname in varchar2,
myxzqh in varchar2
)
as
v_col1 number;
v_col2 number;
...
begin
select count(ajbh) into v_col1 from ajslb where instr(ajbh,myxzqh)=2 and (state='02' or state='03') and fasjcz between tjsjfrom and tjsjto and instr(strajlb,ab1)>0;
...
insert into rpt104701
(
danwei,user_code,rpt_sq,
COL1,
COL2,
COL3,
COL4,
COL5,
COL6,
COL7,
COL8,
COL9,
COL10,
COL11
)
values
(mydanweiname,mycode,rpt_sq,v_col1,...);
commit;
end;