执行select count(kcbh) from mytable1 where instr(kcbh,'210105101')>0的结果是大于0吗?存储过程中不用参数直接写
(select count(kcbh) from mytable1 where instr(kcbh,'210105101')>0),
看看是否能写入值。
(select count(kcbh) from mytable1 where instr(kcbh,'210105101')>0),
看看是否能写入值。
解决方案 »
- oracle 开发 sqlldr
- 数据库范式理论
- 超大表IMP导入出错???求救
- 9i通过透明网关连接SQLServer2000太慢有何解决方法?
- ORACLE怎么识别数据库连来源,是程序连接的还是ORACLE本身自带的管理软件(sqlplus/plsqldev等)
- 100分问个简单问题,解决马上给分
- 对于多表建立插入触发器
- PL/SQL的function的返回值可以是一个记录集或者是表,可以的话,用ASP怎么读?
- 权限不足??
- Oracle 新手请教,如何查询时间字段?
- UNDOTBS01.DBF被我删掉了,noarchivelog,没有备份,数据库启动不起来了,有办法恢复么?
- Oracle 单机安装 无网点 如何启动TNS,及本地服务?
存储过程是:
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 <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
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 <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order start union where connect
急盼高手赐教。
as
begin
insert into mytable (field1,field2....) values
(
(select count(kcbh) from mytable1 where instr(kcbh,xzqh)=2), //你把此处改成这样试试
...
);
commit;
end;
(
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;