游标后面如何使用变量?
例如下面这种情况,不想指明c1.column_1或c1.column_2,而使用变量代替。请大侠指点!
DECLARE
CURSOR curs1 IS select column_1, column_2 from table_1 where record_id=1;
tmp VARCHAR2(400);
begin
FOR c1 IN curs1 LOOP
tmp := 'column_1';
i := c1.tmp;
END LOOP;
end;
例如下面这种情况,不想指明c1.column_1或c1.column_2,而使用变量代替。请大侠指点!
DECLARE
CURSOR curs1 IS select column_1, column_2 from table_1 where record_id=1;
tmp VARCHAR2(400);
begin
FOR c1 IN curs1 LOOP
tmp := 'column_1';
i := c1.tmp;
END LOOP;
end;
fetch进数组里面然后再调用
游标和行变量也不能在sql中绑定变量
type cursor_type is ref cursor;
cursor_T cursor_type;
vC1 varchar2(20);
vC2 varchar2(20);
vSql varchar2(200);
vC3 varchar2(20);
vC4 varchar2(20);
begin
vC1 := 'table_name';
vC2 := 'TABLESPACE_NAME';
vSql := 'select vC1,vC2 from user_tables where rownum<10';
dbms_output.put_line(vSql);
open cursor_t for vSql using vC1,vC2;
loop
fetch cursor_t into vC3,vC4;
Exit when cursor_t%notfound;
dbms_output.put_line(vC3||' '||vC4);
end loop;
close cursor_t;
end;
我记得可以用using加载变量的,不知为什么不可以了,我再查查
i:=1;
loop
fetch c1 into datatime(i),confirmed_order(i);
exit when c1%notfound;
i:=i+1;
end loop;
close c1;
n:=i-1;
DECLARE
CURSOR curs1 IS select column_1, column_2 from table_1 where record_id=1;
tmp VARCHAR2(400);
TYPE T_VARRY IS TALBE OF CURS1%ROWTYPE INDEX BY BINARY_INTEGER;
V_1 T_VARRY;
begin
OPEN C1;
FETCH BLUCK INTO V_1;
CLOSE C1;
end;看这个例子.