我简化一下
declare
type dbcur is ref cursor;
dbcursor2 dbcur;
strdbcursor2 varchar2(2000);
begin
strTemp:='';
strtblsql:='select COLUMN_NAME from user_col_comments ';
strtblsql:=strtblsql||' where (TABLE_NAME=upper('''||strdbcursor1||'''))';/* strtblsql:=strtblsql||' where upper(TABLE_NAME)=upper('''||strdbcursor1||''')';*/
insert into testmis.xx(x) values(strtblsql); open dbcursor2 for strtblsql;
fetch dbcursor2 into strdbcursor2;
while dbcursor2%found loop //不执行到这里面去???????
if strTemp is null then
strTemp:=TRIM(strdbcursor2);
else
strTemp:=strTemp||'||'' ''||'||TRIM(strdbcursor2);
end if;
fetch dbcursor2 into strdbcursor2;
insert into testmis.xx(x) values(strTemp);
end loop;
close dbcursor2; end;
declare
type dbcur is ref cursor;
dbcursor2 dbcur;
strdbcursor2 varchar2(2000);
begin
strTemp:='';
strtblsql:='select COLUMN_NAME from user_col_comments ';
strtblsql:=strtblsql||' where (TABLE_NAME=upper('''||strdbcursor1||'''))';/* strtblsql:=strtblsql||' where upper(TABLE_NAME)=upper('''||strdbcursor1||''')';*/
insert into testmis.xx(x) values(strtblsql); open dbcursor2 for strtblsql;
fetch dbcursor2 into strdbcursor2;
while dbcursor2%found loop //不执行到这里面去???????
if strTemp is null then
strTemp:=TRIM(strdbcursor2);
else
strTemp:=strTemp||'||'' ''||'||TRIM(strdbcursor2);
end if;
fetch dbcursor2 into strdbcursor2;
insert into testmis.xx(x) values(strTemp);
end loop;
close dbcursor2; end;
改成
strtblsql:=strtblsql||' where (TABLE_NAME=upper('''||strdbcursor1.bbm||'''))';应该就没有问题了。
說明我一半是在湊熱鬧
Open cursor1;
Loop
Fetch cursor1 into ...;
Exit When cursor1%NotFound;
...
End Loop;
Close cursor1;
insert into testmis.xx(x) values(strtblsql);
加上commit;
然后从表中看看插入的sql能执行吗,有结果吗?
create or replace procedure p_cur as
type dbcur is ref cursor;
cur_tb dbcur;
v_out varchar2(20);
v_tb tb%rowtype;
v_tbname varchar2(10);
cursor c_tbname is select 'TB' col from dual;
begin
open c_tbname;
fetch c_tbname into v_tbname;
begin
open cur_tb for 'select * from '||v_tbname||' where rownum<5';
fetch cur_tb into v_tb;
if cur_tb%rowcount=0 then
dbms_output.put_line('error');
else
dbms_output.put_line(v_tb.col1);
end if;
close cur_tb;
end;
close c_tbname;
end;12:18:01 SQL> exec p_cur;
1PL/SQL 过程已成功完成。已用时间: 00: 00: 00.15
12:18:04 SQL> 没有问题。
strtblsql:='select COLUMN_NAME from user_col_comments ';
strtblsql:=strtblsql||' where TABLE_NAME=trim(upper('''||strdbcursor1||'''))';