假定zzw_test100中是你要每天创建的表及其格式:
SQL> select * from zzw_test100; TABLE_NAME FIELD FIELDTYPE
-------------------- -------------------- --------------------
ZZW_TEST1000 STUDENT varchar2(20)
ZZW_TEST1000 classid number
ZZW_TEST1000 score number
ZZW_TEST1001 classid number
ZZW_TEST1001 classname varchar2(20) 存储过程的内容为: 1 create or replace procedure proc_createtb_job is
2 str1 varchar2(200);
3 str2 varchar2(200);
4 yymmdd varchar2(200);
5 num number;
6 v_tablename varchar2(200);
7 begin
8 yymmdd:=to_char(sysdate,'yyyymmdd');
9 for tablename in (select distinct table_name from zzw_test100) loop
10 select count(*) into num from all_tables where table_name=tablename.table_name||'_'||yymmdd;
11 if num=0 then
12 v_tablename:=tablename.table_name||'_'||yymmdd;
13 str1:= 'create table '||v_tablename||'(tmp varchar2(20))';
14 execute immediate str1;
15 dbms_output.put_line(str1);
16 for createtable in (select * from zzw_test100 where table_name=tablename.table_name) loop
17 str2:= 'alter table '||v_tablename||' add '||createtable.field||' '||createtable.fieldtype;
18 execute immediate str2;
19 dbms_output.put_line(str2);
20 end loop;
21 execute immediate 'alter table '||v_tablename||' drop column tmp';
22 end if;
23 end loop;
24* end;
怎样在上面的存储过程中为新建的表加入索引
SQL> select * from zzw_test100; TABLE_NAME FIELD FIELDTYPE
-------------------- -------------------- --------------------
ZZW_TEST1000 STUDENT varchar2(20)
ZZW_TEST1000 classid number
ZZW_TEST1000 score number
ZZW_TEST1001 classid number
ZZW_TEST1001 classname varchar2(20) 存储过程的内容为: 1 create or replace procedure proc_createtb_job is
2 str1 varchar2(200);
3 str2 varchar2(200);
4 yymmdd varchar2(200);
5 num number;
6 v_tablename varchar2(200);
7 begin
8 yymmdd:=to_char(sysdate,'yyyymmdd');
9 for tablename in (select distinct table_name from zzw_test100) loop
10 select count(*) into num from all_tables where table_name=tablename.table_name||'_'||yymmdd;
11 if num=0 then
12 v_tablename:=tablename.table_name||'_'||yymmdd;
13 str1:= 'create table '||v_tablename||'(tmp varchar2(20))';
14 execute immediate str1;
15 dbms_output.put_line(str1);
16 for createtable in (select * from zzw_test100 where table_name=tablename.table_name) loop
17 str2:= 'alter table '||v_tablename||' add '||createtable.field||' '||createtable.fieldtype;
18 execute immediate str2;
19 dbms_output.put_line(str2);
20 end loop;
21 execute immediate 'alter table '||v_tablename||' drop column tmp';
22 end if;
23 end loop;
24* end;
怎样在上面的存储过程中为新建的表加入索引
create or replace procedure proc_createtb_job is
str1 varchar2(200);
str2 varchar2(200);
yymmdd varchar2(200);
num number;
v_tablename varchar2(200);
begin
yymmdd:=to_char(sysdate,'yyyymmdd');
for cur1 in (select table_name||'_'||yymmdd tabname,wm_concat(field||' '||fieldtype)col,min(field)ind from zzw_test100 a
where not exists(select 1 from all_tables where table_name=upper(a.table_name)||'_'||yymmdd)
group by table_name) loop
str1:= 'create table '||cur1.tabname||'('||cur1.col||')';
execute immediate str1;
execute immediate 'create index '||cur1.tabname||'_ind on '||cur1.tabname||'('||cur1.ind||')';
end loop;
end;
其中创建索引execute immediate 'create index '||cur1.tabname||'_ind on '||cur1.tabname||'('||cur1.ind||')';
这个创建索引不太合理,你看下,根据需要修改