把整个逻辑用动态执行,可参考如下代码: CREATE OR REPLACE PROCEDURE p01(tablename IN VARCHAR2) AS v_sql VARCHAR2(500):= 'DECLARE type c_curref is ref cursor; r_curref c_curref; rec_c '||tablename||'%ROWTYPE; BEGIN OPEN r_curref FOR ''select * from '||tablename|| ' where rownum=1''; LOOP FETCH r_curref INTO rec_c; EXIT WHEN r_curref%NOTFOUND; Dbms_Output.put_line(''the SEQUENCE_NAME is :''||rec_c.SEQUENCE_NAME); END LOOP; CLOSE r_curref; END;'; BEGIN execute immediate v_sql; END p01;EXEC p01('user_sequences'); 输出: the SEQUENCE_NAME is :S_DHBA2_SQNO
定义 V_SQL VARCHAR2(500); TYPE MYCUR IS REF CURSOR; MYCUR1 MYCUR; V_MYCUR1 MYCUR1%ROWTYPE; 调用 V_SQL := 'select id from '||v_tab||' where rownum=1';
OPEN MYCUR1 FOR V_SQL; FETCH MYCUR1 INTO V_MYCUR1; EXIT WHEN MYCUR1%NOTFOUND;
--処理过程END LOOP;
open my_cur; fetch my_cur into 自定义变量(可以多个根据你游标返回的字段决定); exit;
select id from v_tab where rownum=1;
你的游标是my_cur,
应该改成吧:
v_row v_tab%rowtype——》v_row my_cur%rowtype
CREATE OR REPLACE PROCEDURE p01(tablename IN VARCHAR2) AS
v_sql VARCHAR2(500):=
'DECLARE
type c_curref is ref cursor;
r_curref c_curref;
rec_c '||tablename||'%ROWTYPE;
BEGIN
OPEN r_curref FOR ''select * from '||tablename|| ' where rownum=1'';
LOOP
FETCH r_curref INTO rec_c;
EXIT WHEN r_curref%NOTFOUND;
Dbms_Output.put_line(''the SEQUENCE_NAME is :''||rec_c.SEQUENCE_NAME);
END LOOP;
CLOSE r_curref;
END;';
BEGIN
execute immediate v_sql;
END p01;EXEC p01('user_sequences');
输出:
the SEQUENCE_NAME is :S_DHBA2_SQNO
V_SQL VARCHAR2(500);
TYPE MYCUR IS REF CURSOR;
MYCUR1 MYCUR;
V_MYCUR1 MYCUR1%ROWTYPE;
调用
V_SQL := 'select id from '||v_tab||' where rownum=1';
OPEN MYCUR1 FOR V_SQL;
FETCH MYCUR1 INTO V_MYCUR1;
EXIT WHEN MYCUR1%NOTFOUND;
--処理过程END LOOP;
fetch my_cur into 自定义变量(可以多个根据你游标返回的字段决定);
exit;