17,078
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure test(years in varchar2,corps in varchar2,ctrlprjcodes in varchar2,jukong in varchar2)
is
begin
insert into bdgt_formation(tagfrom,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,
projectname,pk_formation,def7)
select biaoji,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,projectname
,generatepk(pk_corp)pk_formation,def7 from (
select distinct 10 as biaoji,f.pk_downsend_b,to_char(to_number(f.m_year)+1) m_year,f.m_dept,f.pk_corp,f.dr,f.projectcode,f.projectname
,f.pk_formation,f.pk_formation def7
from bdgt_formation_b b,bdgt_formation f,bd_deptdoc bdd
where b.dr='0' and f.dr='0' and b.childprjname is not null and b.pk_formation=f.pk_formation
and f.m_dept=bdd.pk_deptdoc(+) and b.def4 is not null and nvl(b.closeflag,'N')!='Y' and f.pk_corp=corps and f.m_year
=years and f.projectcode=ctrlprjcodes and b.def4=jukong)base;
end;
create or replace procedure test(years in varchar2,corps in varchar2,ctrlprjcodes in varchar2,jukong in varchar2)
is
sqls varchar2(2000):='';
begin
sqls:='insert into bdgt_formation(tagfrom,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode, projectname,pk_formation,def7)
select biaoji,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,projectname
,generatepk(pk_corp)pk_formation,def7 from (select distinct 10 as biaoji,f.pk_downsend_b,
to_char(to_number(f.m_year)+1) m_year,f.m_dept,f.pk_corp,f.dr,f.projectcode,f.projectname,
f.pk_formation,f.pk_formation def7 from bdgt_formation_b b,bdgt_formation f,bd_deptdoc bdd
where b.dr=''0'' and f.dr=''0'' and b.childprjname is not null
and b.pk_formation=f.pk_formation and f.m_dept=bdd.pk_deptdoc(+)
and b.def4 is not null and nvl(b.closeflag,''N'')!=''Y'' and f.pk_corp='||corps||'
and f.m_year='||years||' and f.projectcode='||ctrlprjcodes||' and b.def4='||jukong||')base';
execute immediate sqls;
end;
用动态SQL,两个语句要达到的目的都一样,可我在测试时发现不用动态SQL的过程并没有执行插入操作,动态SQL那个则是正确的,我把非动态SQL的存储过程给拷出来自己赋值也能插入,实在郁闷了,求解