……
If(t.c5 Is Not Null)Then
m_id:=m_id+1;
sqlstr1:=' Select caseno,checkno, '||m_id||' , '||t.c5||' , '||t.c8||' , '||t.c8||' , '''||t.wdlx||''' , '''||t.tjlx||''' From '||t.mc;
sqlstr2:=' Where('''||t.c5||''' Is Not Null) ';
If(caseno Is Not Null)Then
sqlstr2:=sqlstr2||' And caseno='''||caseno||'''';
End If;
sqlstr:=sqlstr0||sqlstr1||sqlstr2;
Execute Immediate sqlstr;
End If;使用for a in(Execute Immediate sqlstr)loop……end loop;时出现以下错误:
Compilation errors for PROCEDURE THXJ.PR_STAT_JDSError: PLS-00103: 出现符号 "IMMEDIATE"在需要下列之一时:
. ( ) , * @ % & | = -
+ < / > at in is mod not range rem => .. <an exponent (**)>
<> or != or ~= >= <= <> and or like between ||
Line: 34
Text: for a in(Execute Immediate sqlstr)
If(t.c5 Is Not Null)Then
m_id:=m_id+1;
sqlstr1:=' Select caseno,checkno, '||m_id||' , '||t.c5||' , '||t.c8||' , '||t.c8||' , '''||t.wdlx||''' , '''||t.tjlx||''' From '||t.mc;
sqlstr2:=' Where('''||t.c5||''' Is Not Null) ';
If(caseno Is Not Null)Then
sqlstr2:=sqlstr2||' And caseno='''||caseno||'''';
End If;
sqlstr:=sqlstr0||sqlstr1||sqlstr2;
Execute Immediate sqlstr;
End If;使用for a in(Execute Immediate sqlstr)loop……end loop;时出现以下错误:
Compilation errors for PROCEDURE THXJ.PR_STAT_JDSError: PLS-00103: 出现符号 "IMMEDIATE"在需要下列之一时:
. ( ) , * @ % & | = -
+ < / > at in is mod not range rem => .. <an exponent (**)>
<> or != or ~= >= <= <> and or like between ||
Line: 34
Text: for a in(Execute Immediate sqlstr)
没有这个语法for a in (select * from ...) loop
end loop;
-- Created on 2006-8-22 by ADMINISTRATOR
declare
-- Local variables here
i integer;
type myref is ref cursor;
my myref;
s varchar2(40);
begin
-- Test statements here
s := 'select stkcode from tr_gfk';
open my for s;
close my;
end;
i integer;
v_tblname varchar2(30);
v_colnum number(4);
v_starttime varchar2(14);
sql_str varchar2(500);v_p1 number(12,2);
v_p2 number(12,2);
v_p3 number(12,2);type value_array is array(3) of number(12,2);
v_ei2 value_array;type type_cur is ref cursor;
cursor_d type_cur;begin open cursor_d for 'select distinct tablename ,colnum,"p1","p2","p3"
from '||v_tmpfromtable ;
FETCH cursor_d INTO
v_tblname,v_colnum,v_p1,v_p2,v_p3;
WHILE cursor_d%FOUND LOOP sql_str := ('update '||v_tblname||' t2 set a'||v_colnum||' = (:1) where t2.datatime = (:2)');
for i in 1..3 loop
select to_char((to_date(v_day,'yyyymmdd') + i * 30 /1440),'yyyymmddhh24miss') into v_starttime from dual;
v_ei2 := value_array(v_p1,v_p2,v_p3);
execute immediate sql_str using v_ei2(i),v_starttime;
commit;
end loop;
FETCH cursor_d INTO
v_tblname,v_colnum,v_p1,v_p2,v_p3;
end loop;
commit;
end pro_lcs_ABP_update;以前使用过的一段代码,精简了一些多余的变量。
type type_cur is ref cursor;
cursor_d type_cur;——两句声明了cursor_d是动态的游标。
open cursor_d for 'select …… from '||v_tmpfromtable 后面的v_tmpfromtable 就是动态的参数了。 这样就动态的打开了游标。
然后在For循环里面execute immediate sql_str using v_ei2(i),v_starttime——这个是结合了绑定变量。但是 sql_str 这个字符串就是包含了动态参数拼成的。不知道能否有些帮助。