CREATE OR REPLACE PROCEDURE test3(iyear varchar2,imonth varchar2,cur_out_arg out pkg_package.type_cursor)
as
BEGIN
DECLARE
CURSOR MYCUSOR IS
select ContractNum,ProjectName,ContractType,ContractMoney,ContractCompletionDate,CompletionPaymentProportion,PayYearLimit,AuditAmount
from fund_contract where YEAR(ContractCompletionDate)=iyear and month(ContractCompletionDate)=imonth;
v_ContractNum varchar2(20);
v_ProjectName varchar2(128);
v_ContractType varchar2(128);
v_ContractMoney number(12,2);
v_ContractCompletionDate date;
v_CompletionPaymentProportion number(12,2);
v_PayYearLimit varchar2(20);
v_AuditAmount number(12,2);
/*v_FinishMonth number(12,2);*/
v_LJFinish number(12,2);
v_sumPayAmount number(12,2);
BEGIN
OPEN MYCUSOR;
FETCH MYCUSOR INTO v_ContractNum,v_ProjectName,v_ContractType,v_ContractMoney,v_ContractCompletionDate,v_CompletionPaymentProportion,v_PayYearLimit,v_AuditAmount;
while MYCUSOR%FOUND LOOP
sqlstr:= 'select sum(PayAmount) into v_sumPayAmount from fund_ProjectApplyForm where YEAR(fundDate)!='||iyear ||'and contractno ='||v_ContractNum||' group by contractno';--往年已付款
execute immediate sqlstr into v_sumPayAmount;
END LOOP;
CLOSE MYCUSOR;
END;
END test3;
问题,下边的ContractNum是查询条件这个值是根据前面的select得到的,但是我这边一直报错,请教各位大虾。
as
BEGIN
DECLARE
CURSOR MYCUSOR IS
select ContractNum,ProjectName,ContractType,ContractMoney,ContractCompletionDate,CompletionPaymentProportion,PayYearLimit,AuditAmount
from fund_contract where YEAR(ContractCompletionDate)=iyear and month(ContractCompletionDate)=imonth;
v_ContractNum varchar2(20);
v_ProjectName varchar2(128);
v_ContractType varchar2(128);
v_ContractMoney number(12,2);
v_ContractCompletionDate date;
v_CompletionPaymentProportion number(12,2);
v_PayYearLimit varchar2(20);
v_AuditAmount number(12,2);
/*v_FinishMonth number(12,2);*/
v_LJFinish number(12,2);
v_sumPayAmount number(12,2);
BEGIN
OPEN MYCUSOR;
FETCH MYCUSOR INTO v_ContractNum,v_ProjectName,v_ContractType,v_ContractMoney,v_ContractCompletionDate,v_CompletionPaymentProportion,v_PayYearLimit,v_AuditAmount;
while MYCUSOR%FOUND LOOP
sqlstr:= 'select sum(PayAmount) into v_sumPayAmount from fund_ProjectApplyForm where YEAR(fundDate)!='||iyear ||'and contractno ='||v_ContractNum||' group by contractno';--往年已付款
execute immediate sqlstr into v_sumPayAmount;
END LOOP;
CLOSE MYCUSOR;
END;
END test3;
问题,下边的ContractNum是查询条件这个值是根据前面的select得到的,但是我这边一直报错,请教各位大虾。
FETCH MYCUSOR INTO v_ContractNum,v_ProjectName,v_ContractType,v_ContractMoney,v_ContractCompletionDate,v_CompletionPaymentProportion,v_PayYearLimit,v_AuditAmount;
while MYCUSOR%FOUND LOOP
sqlstr:= 'select sum(PayAmount) into v_sumPayAmount from fund_ProjectApplyForm where YEAR(fundDate)!='||iyear ||'and contractno ='||v_ContractNum||' group by contractno';--往年已付款
dbms_output.put_line(sqlstr);--打印生成语句,然后把语句COPY到PL/SQL里执行一下,就容易找到错在哪里了。
--execute immediate sqlstr into v_sumPayAmount;
END LOOP;
CLOSE MYCUSOR;
END;
改成sqlstr:= 'select sum(PayAmount) into v_sumPayAmount from fund_ProjectApplyForm where YEAR(fundDate)!='''||iyear ||'''and contractno ='''||v_ContractNum||''' group by contractno';--往年已付款
你看iyear and之间是不是没有空格,你在每个''中首先以空格开始,不然你的string 词都拼到一块了