我创建了一个存储过程内容如下
create or replace procedure summary(sdate in varchar2,org in varchar2,manager in varchar2)asbegin
if(manager == '')
{
DECALRE CURSOR tmpdata IS select distinct(t.khjl) ,t.fb,t.fljzrq,t.id,t.dkye from xe.xe_qkflrdb t where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.khjl=manager and t.FLJZRQ like sdate and t.fb in org;
}
else
{
DECALRE CURSOR tmpdata IS select distinct(t.khjl) ,t.fb,t.fljzrq,t.id,t.dkye from xe.xe_qkflrdb t where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.FLJZRQ like sdate and fb in org;
}
BEGIN
FOR rdata IN cdata
LOOP
if(rdata.Cf_yj == '01')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Normalcount,Normalnumber,Normalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj == '02')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Focuscount,Focusnumber,Focusmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj == '03')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Secondarycount,Secondarynumber,Secondarymoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj == '04')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Doubtcount,Doubtnumber,Doubtmoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj == '05')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Lostcount,Lostnumber,Lostmoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
}
END LOOP;
END;return select * from xe.tmp_DKFLHZB;end;
/然后执行 提示成功
然后我执行execute summary('2013-03%','03110331','')
提示ora 00900 无效 sql语句然后我去存储过程列表那里点 测试 提示 summary无效,,,
到底哪里出了问题?sql存储
create or replace procedure summary(sdate in varchar2,org in varchar2,manager in varchar2)asbegin
if(manager == '')
{
DECALRE CURSOR tmpdata IS select distinct(t.khjl) ,t.fb,t.fljzrq,t.id,t.dkye from xe.xe_qkflrdb t where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.khjl=manager and t.FLJZRQ like sdate and t.fb in org;
}
else
{
DECALRE CURSOR tmpdata IS select distinct(t.khjl) ,t.fb,t.fljzrq,t.id,t.dkye from xe.xe_qkflrdb t where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.FLJZRQ like sdate and fb in org;
}
BEGIN
FOR rdata IN cdata
LOOP
if(rdata.Cf_yj == '01')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Normalcount,Normalnumber,Normalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj == '02')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Focuscount,Focusnumber,Focusmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj == '03')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Secondarycount,Secondarynumber,Secondarymoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj == '04')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Doubtcount,Doubtnumber,Doubtmoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj == '05')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Lostcount,Lostnumber,Lostmoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
}
END LOOP;
END;return select * from xe.tmp_DKFLHZB;end;
/然后执行 提示成功
然后我执行execute summary('2013-03%','03110331','')
提示ora 00900 无效 sql语句然后我去存储过程列表那里点 测试 提示 summary无效,,,
到底哪里出了问题?sql存储
====================
编译有问题,存储过程处于 invalid 状态
我又改了改
create or replace procedure c(sdate in varchar2,org in varchar2,manager in varchar2)asbegin
if(manager = '') then
begin DECALRE CURSOR tmpdata IS select distinct(t.khjl) ,t.fb,t.fljzrq,t.id,t.dkye from xe.xe_qkflrdb t where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.khjl=manager and t.FLJZRQ like sdate and t.fb in org;
end;
else
begin
DECALRE CURSOR tmpdata IS select distinct(t.khjl) ,t.fb,t.fljzrq,t.id,t.dkye from xe.xe_qkflrdb t where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.FLJZRQ like sdate and fb in org;
end;
end if;BEGIN
FOR rdata IN cdata
LOOP
if(rdata.Cf_yj = '01')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Normalcount,Normalnumber,Normalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj = '02')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Focuscount,Focusnumber,Focusmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj = '03')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Secondarycount,Secondarynumber,Secondarymoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj = '04')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Doubtcount,Doubtnumber,Doubtmoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
}
else if(rdata.Cf_yj = '05')
{
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Lostcount,Lostnumber,Lostmoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
}
END LOOP;
END;return select * from xe.tmp_DKFLHZB;end xe.summary ;
/
PROCEDURE XE.C 编译错误错误:PLS-00103: 出现符号 "CURSOR"在需要下列之一时:
:= . ( @ % ;
行:9
文本:DECALRE CURSOR tmpdata IS select distinct(t.khjl) ,t.fb,t.fljzrq,t.id,t.dkye from xe.xe_qkflrdb t where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.khjl=manager and t.FLJZRQ like sdate and t.fb in org;改后的存储过程 create or replace procedure c(sdate varchar2,org varchar2,manager varchar2)asbegin
if(manager = '') then
begin DECALRE CURSOR tmpdata IS select distinct(t.khjl) ,t.fb,t.fljzrq,t.id,t.dkye from xe.xe_qkflrdb t where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.khjl=manager and t.FLJZRQ like sdate and t.fb in org;end;
else
begin
DECALRE CURSOR tmpdata IS select distinct(t.khjl) ,t.fb,t.fljzrq,t.id,t.dkye from xe.xe_qkflrdb t where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.FLJZRQ like sdate and fb in org;
end;
end if;BEGIN
FOR rdata IN cdata
LOOP if(rdata.Cf_yj = '01')
BEGIN
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Normalcount,Normalnumber,Normalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye)
end;
else if(rdata.Cf_yj = '02')
BEGIN
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Focuscount,Focusnumber,Focusmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye)
end;
else if(rdata.Cf_yj = '03')
BEGIN
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Secondarycount,Secondarynumber,Secondarymoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
end;
else if(rdata.Cf_yj = '04')
BEGIN
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Doubtcount,Doubtnumber,Doubtmoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
end;
else if(rdata.Cf_yj = '05')
BEGIN
insert into xe.tmp_DKFLHZB(Key,name,org,name,Summaymonth,Totalcount,Totalnumber,Totalmoney,Lostcount,Lostnumber,Lostmoney,Badtotalcount,Badtotalnumber,Badtotalmoney) values('Company'+rdata.ID,rdata.khjl,rdata.fb,sdate,1,1,sdata.dkye,1,1,sdata.dkye,1,1,sdata.dkye)
end;
end if; END LOOP;
END;return select * from xe.tmp_DKFLHZB;end xe.summary ;
create or replace procedure
tj_invpay2(vseqno in number)
as
i number;
ii number;
lmzkje number;
iiii number;
lydkje number;
row_main inv_main%rowtype; cursor cur_pay2 is
select * from inv_pay2
where seqno=vseqno and mxcode='09';
begin
select * into row_main from inv_main
where seqno=vseqno and flag='N' for update;
if(row_main.mzk<>0) then
for r_pay2 in cur_pay2 loop
select count(*) into i from custom where code=r_pay2.zpno and hyklb='9' and hy='0';
if i > 0 then
update inv_pay2 set mxcode='15' where seqno=vseqno and zpno=r_pay2.zpno;
end if;
end loop;
select count(*) into ii from INV_PAY2 where mxcode='09' and seqno=vseqno;
if(ii<>0) then
SELECT SUM(nvl(JE,0)) into lmzkje FROM INV_PAY2 WHERE mxcode='09' and seqno=vseqno;
end if;
if lmzkje is not null then
UPDATE INV_MAIN SET MZK= lmzkje WHERE seqno=vseqno;
else
UPDATE INV_MAIN SET MZK= 0 WHERE seqno=vseqno;
end if;
select count(*) into iiii from INV_PAY2 where mxcode='15' and seqno=vseqno;
if(iiii<>0) then
SELECT SUM(JE) into lydkje FROM INV_PAY2 WHERE mxcode='15' and seqno=vseqno;
end if;
UPDATE INV_MAIN SET PAY12=lydkje WHERE seqno=vseqno;
end if ;
--commit; exception when others then
rollback;
raise;
end;
但是最后一句那里报错。 select * from xe.tmp_DKFLHZB;存储过程主体
create or replace procedure c(sdate in varchar2,org in varchar2)
as
BEGIN
insert into xe.tmp_DKFLHZB(Key,name,org,Summaymonth,Totalcount,Totalnumber,Totalmoney,Normalcount,Normalnumber,Normalmoney)
select 'Company'||ID,khjl,fb,sdate,1,1,dkye,1,1,dkye
from xe_qkflrdb t
where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.FLJZRQ like sdate and fb in org and Cf_yj='01'; insert into xe.tmp_DKFLHZB(Key,name,org,Summaymonth,Totalcount,Totalnumber,Totalmoney,Focuscount,Focusnumber,Focusmoney)
select 'Company'||ID,khjl,fb,sdate,1,1,dkye,1,1,dkye
from xe_qkflrdb t
where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.FLJZRQ like sdate and fb in org and Cf_yj='02'; insert into xe.tmp_DKFLHZB(Key,name,org,Summaymonth,Totalcount,Totalnumber,Totalmoney,Secondarycount,Secondarynumber,Secondarymoney,Badtotalcount,Badtotalnumber,Badtotalmoney)
select 'Company'||ID,khjl,fb,sdate,1,1,dkye,1,1,dkye,1,1,dkye
from xe_qkflrdb t
where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.FLJZRQ like sdate and fb in org and Cf_yj='03'; insert into xe.tmp_DKFLHZB(Key,name,org,Summaymonth,Totalcount,Totalnumber,Totalmoney,Doubtcount,Doubtnumber,Doubtmoney,Badtotalcount,Badtotalnumber,Badtotalmoney)
select 'Company'||ID,khjl,fb,sdate,1,1,dkye,1,1,dkye,1,1,dkye
from xe_qkflrdb t
where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.FLJZRQ like sdate and fb in org and Cf_yj='04'; insert into xe.tmp_DKFLHZB(Key,name,org,Summaymonth,Totalcount,Totalnumber,Totalmoney,Lostcount,Lostnumber,Lostmoney,Badtotalcount,Badtotalnumber,Badtotalmoney)
select 'Company'||ID,khjl,fb,sdate,1,1,dkye,1,1,dkye,1,1,dkye
from xe_qkflrdb t
where t.khjl is not null and t.cf_yj is not null and t.dkye>0 and t.FLJZRQ like sdate and fb in org and Cf_yj='05'; select * from xe.tmp_DKFLHZB;EXCEPTION
WHEN NO_DATA_FOUND THEN
return; end c ;
存储过程中不能直接用select * from tab_name
想返回结果集的话,可以使用游标或集合