1 建一个包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);
怎样将日期动态的添加进去?我是这样写的但是不行 rename a to concat(a,to_char(sysdate,'mm-dd'))
declare tbname varchar2(100); begin tbname:=to_char(sysdate,'mm-dd'); execute immediate 'rename oldtable to '||tbname; end;
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);
rename a to concat(a,to_char(sysdate,'mm-dd'))
begin
tbname:=to_char(sysdate,'mm-dd');
execute immediate 'rename oldtable to '||tbname;
end;
然后建一个JOB,让它每天定时执行就可以了