create or replace function f_test(condition varchar2) return SYS_REFCURSOR as n number; v_sql varchar2(4000); curr SYS_REFCURSOR; begin for col in (select t.COLUMN_NAME from USER_TAB_COLUMNS T WHERE TABLE_NAME='A') loop v_sql:='select count(*) from a where '||col.column_name||' like ''%'||condition||'%'' '; execute immediate v_sql into n; if n>0 then dbms_output.put_line(col.column_name||'--'||n); v_sql:='select * from customer where '||col.column_name||' like ''%'||condition||'%'' '; open curr for v_sql; return curr; exit; else continue; end if; end loop; end;
create or replace function f_test(condition varchar2) return SYS_REFCURSOR as
n number;
v_sql varchar2(4000);
curr SYS_REFCURSOR;
begin for col in (select t.COLUMN_NAME from USER_TAB_COLUMNS T WHERE TABLE_NAME='A') loop
v_sql:='select count(*) from a where '||col.column_name||' like ''%'||condition||'%'' ';
execute immediate v_sql into n;
if n>0 then
dbms_output.put_line(col.column_name||'--'||n);
v_sql:='select * from customer where '||col.column_name||' like ''%'||condition||'%'' ';
open curr for v_sql;
return curr;
exit;
else
continue;
end if;
end loop;
end;