反向选择列,列出不包括某列的其它列
set serveroutput oncreate or replace procedure test
(mytable in varchar2,col_list in varchar2)
as
type ref_cursor is ref cursor;
cursor_col ref_cursor;
tablename varchar2(100);
collist varchar2(1000); sqlstr varchar2(1000);
colname varchar2(100);
col_name varchar2(100);
colstr varchar2(1000);
begin
tablename := upper(mytable);
collist := upper(col_list||',');
sqlstr := 'select column_name from user_col_comments where table_name='''||tablename||'''';
open cursor_col for sqlstr;
loop
fetch cursor_col into colname;
exit when cursor_col%notfound;
col_name := colname||',';
if instrb(collist,col_name)=0 then
colstr:=colstr||','||colname;
end if;
end loop;
close cursor_col;
colstr := substrb(colstr,2);
sqlstr := 'select '||colstr||' from '||tablename;
dbms_output.put_line(sqlstr);
execute immediate sqlstr;
end test;
/
set serveroutput oncreate or replace procedure test
(mytable in varchar2,col_list in varchar2)
as
type ref_cursor is ref cursor;
cursor_col ref_cursor;
tablename varchar2(100);
collist varchar2(1000); sqlstr varchar2(1000);
colname varchar2(100);
col_name varchar2(100);
colstr varchar2(1000);
begin
tablename := upper(mytable);
collist := upper(col_list||',');
sqlstr := 'select column_name from user_col_comments where table_name='''||tablename||'''';
open cursor_col for sqlstr;
loop
fetch cursor_col into colname;
exit when cursor_col%notfound;
col_name := colname||',';
if instrb(collist,col_name)=0 then
colstr:=colstr||','||colname;
end if;
end loop;
close cursor_col;
colstr := substrb(colstr,2);
sqlstr := 'select '||colstr||' from '||tablename;
dbms_output.put_line(sqlstr);
execute immediate sqlstr;
end test;
/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货