create or replace package pak is
type cur is ref cursor;
procedure collist(name varchar2,c2 in out cur);
end;
create or replace package body pak is
procedure collist(name varchar2,colname varchar2,c2 in out cur) is
str varchar2(200);
cursor c1(tbname varchar2) is select column_name col
from user_tab_columns where lower(table_name)=lower(tbname);
begin
for rec in c1(name) loop
str:=str||rec.col||',';
end loop;
str:=substr(str,1,length(str)-1);
open c2 for 'select '||str||' from '||name;
end;
end;
过程collist的参数说明:
name 要查询的表名,c2 游标变量,用于传递结果集
2 使用方法:sqlplus中
var cur refcursor;
set autoprint on
set linesize 1000
exec pak.collist('a_'||to_char(sysdate,'yymmdd'),:cur);