項(xiàng)目需要，為每天建立一個表。其實(shí)個人感覺統(tǒng)計(jì)起來是方便，但是給操作添了不少工作量。木辦法~
oracle版：
-- 創(chuàng)建一天一個表
CREATE PROCEDURE CREATEDAILYTABLE AS
v_tBeginDate TIMESTAMP;
v_tEndDate TIMESTAMP;
v_sTableName VARCHAR(100);
v_sIndexName VARCHAR(100);
v_sSQL VARCHAR(4000);
BEGIN
v_tBeginDate := TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
v_tEndDate := TO_DATE('2001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
WHILE v_tEndDate > v_tBeginDate LOOP
v_sTableName := 'test_' || SUBSTR(TO_CHAR(v_tBeginDate, 'YYYY-MM-DD HH24:MI:SS'), 6, 2)
|| SUBSTR(TO_CHAR(v_tBeginDate, 'YYYY-MM-DD HH24:MI:SS'), 9, 2);
v_sSQL := '
create table ' || v_sTableName || '
(
aaaa int,
BBBB varchar(20),
CCCC varchar(100)
primary key (aaaa)
)';
COMMIT;
v_tBeginDate := v_tBeginDate + 1;
END loop;
END CREATEDAILYTABLE;
還有一個以前sqlserver版的，懶得改了
declare
@m int,
@d int,
@tablename varchar(100),
@month varchar(10),
@day varchar(10),
@sql varchar(1000)
set @m=1
set @d=1
set @month = ''
set @day = ''
set @sql = ''
while (@m<=12)
begin
while (@d<=31)
begin
set @month = @m
set @day =@d
if (@m<10)
set @month = '0' + @month
if (@d<10)
set @day = '0' + @day
set @tablename = '_' + @month + @day
if (@m=2 and @d>29 or @m=4 and @d>30 or @m=6 and @d>30 or @m=9 and @d>30 or @m=11 and @d>30)
print '該表不創(chuàng)建：'+ @tablename
else
begin
set @sql ='
CREATE TABLE test+ @tablename +'
(
aaa varchar(100),
bbbb varchar(100), PRIMARY KEY (aaa)
)'
exec(@sql)
end
set @d=@d+1
end
set @m=@m+1
set @d=1
end
go
posted on 2008-12-18 10:33
temper 閱讀(196)
評論(0) 編輯 收藏