一下是建立存储过程时的完整代码。报错,,求解答。谢谢
create or replace PROCEDURE test.my_update
(
invoice_no in varchar2
)
is
detail_invoice_nos varchar2(16) :="";
tableNameId number;
detail_invoce varchar2(16);
flowTypeId number;
flowid varchar2(16);
cursor c_invoice(invoiceNo varchar2(16)) is select DETAIL_INVOICE_NO from DETAIL_NO_INVOICE where INVOICE_NO =invoiceNo order by DETAIL_INVOICE_NO;
begin
open c_invoice(invoice_no);
loop
fetch c_invoice into detail_invoce;
EXIT FETCH when c_invoice%NOTFOUND;
detail_invoice_nos:=concat(detail_invoice_nos , detail_invoce);
detail_invoice_nos:=concat(detail_invoice_nos , ',');
end loop;
close c_invoice;
detail_invoice_nos:=substr(detail_invoice_nos,1,length(detail_invoice_nos)-1);
select FLOWINVOICE.ID into tableNameId from FLOWINVOICE where DETAIL_INVOICE_NOS=detail_invoice_nos;
select flowTypeId into flowTypeId from flowType where flowtype='InvoiceFlow' and rownum=1;
select distinct FLOWID into flowid from flowstatetable where FLOWTYPEID= flowTypeId and TABLENAMEID=tableNameId;
update Foxyflowstatetable set ISTERMINATE='Y' where FLOWTYPEID= flowTypeId and TABLENAMEID=tableNameId;
update foxyflowaudittable set ISACTIVE='N' where FLOWID=flowid;
end my_update;
报错:complied but with compliation error。
Explain plan : 遗漏关键字
请问这是怎么回事
create or replace PROCEDURE test.my_update
(
invoice_no in varchar2
)
is
detail_invoice_nos varchar2(16) :="";
tableNameId number;
detail_invoce varchar2(16);
flowTypeId number;
flowid varchar2(16);
cursor c_invoice(invoiceNo varchar2(16)) is select DETAIL_INVOICE_NO from DETAIL_NO_INVOICE where INVOICE_NO =invoiceNo order by DETAIL_INVOICE_NO;
begin
open c_invoice(invoice_no);
loop
fetch c_invoice into detail_invoce;
EXIT FETCH when c_invoice%NOTFOUND;
detail_invoice_nos:=concat(detail_invoice_nos , detail_invoce);
detail_invoice_nos:=concat(detail_invoice_nos , ',');
end loop;
close c_invoice;
detail_invoice_nos:=substr(detail_invoice_nos,1,length(detail_invoice_nos)-1);
select FLOWINVOICE.ID into tableNameId from FLOWINVOICE where DETAIL_INVOICE_NOS=detail_invoice_nos;
select flowTypeId into flowTypeId from flowType where flowtype='InvoiceFlow' and rownum=1;
select distinct FLOWID into flowid from flowstatetable where FLOWTYPEID= flowTypeId and TABLENAMEID=tableNameId;
update Foxyflowstatetable set ISTERMINATE='Y' where FLOWTYPEID= flowTypeId and TABLENAMEID=tableNameId;
update foxyflowaudittable set ISACTIVE='N' where FLOWID=flowid;
end my_update;
报错:complied but with compliation error。
Explain plan : 遗漏关键字
请问这是怎么回事
它直接就给你定位了。
(
a in varchar2
)
is
b varchar2(16) :="";
c number;
d varchar2(16);
e number;
f varchar2(16);
cursor c_t(A varchar2(16)) is select ...where XX=a ;
begin open c_t(a);
loop
fetch c_t into f;
EXIT FETCH when c_t%NOTFOUND;
b:=concat(b, detail_invoce)||',';
end loop;
close c_invoice;
b:=substr(b,1,length(b)-1);
select XX into c from xx where XX=b;
select .. into e from ...;
select .. into d from...where C= c and E=e;
update ... where E= e and C=c;
update ... where D=d;
end my_update;重新发了一下