declare
sqlstr varchar2(500);
i int;
begin
i := 1; --我这里没有写循环
sqlstr := "select code from club
full outer join (select home,count(home) couH from fixture where week=:arg1 group by home)a on club.code=a.home
full outer join (select away,count(away) couA from fixture where week=:arg1 group by away)b on club.code=b.away
where (nvl(a.couH,0) + nvl(b.couA,0))>1 and rownum=1" EXECUTE IMMEDIATE sqlstr USING i; --这里的i你可以写个循环来累加
END;
sqlstr varchar2(500);
i int;
begin
i := 1; --我这里没有写循环
sqlstr := "select code from club
full outer join (select home,count(home) couH from fixture where week=:arg1 group by home)a on club.code=a.home
full outer join (select away,count(away) couA from fixture where week=:arg1 group by away)b on club.code=b.away
where (nvl(a.couH,0) + nvl(b.couA,0))>1 and rownum=1" EXECUTE IMMEDIATE sqlstr USING i; --这里的i你可以写个循环来累加
END;
WHILE i<=maxWeek LOOP
clubCode:='';
strSQL:='select code from club full outer join (SELECT home,COUNT(home) couH FROM fixture WHERE week=:i GROUP BY home)a ON club.code=a.home full outer join (SELECT away,COUNT(away) couA FROM fixture WHERE week=:i GROUP BY away)b ON club.code=b.away WHERE (NVL(a.couH,0) + NVL(b.couA,0))>1 AND ROWNUM=1';
EXEC IMMEDIATE strSQL USING i;
--dbms_output.put_line(strsql);
IF clubCode<>'' THEN
dbms_output.put_line('Club ' || clubCode || ' arranged to play over one time on week ' || i || '.');
i:=maxWeek+1;
error:=1;
ELSE
i:=i+1;
END IF;
END LOOP; 以上是循环部分的代码,但是运行的时候,报错
PLS-00103:Encountered the symbol"immediate" when expecting one fo the following:
:=.(@%;好像说是语法错误。 给字符串赋值是用单引号还是双引号,另外,引用动态sql语句参数是是用=:嘛?还有就是我把arg1该成了i。请指点一二,谢