create or replace procedure aaa(v_num int)
authid current_user
is
v_string varchar2(200);
v_string2 varchar2(200);
v_string3 varchar2(200);
v_cnt number(18,0);
v_cot number(18,0);
begin
EXECUTE IMMEDIATE 'SELECT to_char(sysdate-interval '''||to_char(v_num)||''' minute,''yyyymmddhh24mi'') FROM dual' INTO v_string;
v_string := 'WJT_'||v_string;
SELECT count(1) INTO v_cnt from user_tables where table_name=v_string;
IF v_cnt=1 then
EXECUTE immediate 'DROP TABLE '||v_string;
END IF;
EXECUTE IMMEDIATE 'SELECT to_char(sysdate,''yyyymmddhh24mi'') FROM dual' INTO v_string2;
execute immediate 'create table WJT_'||v_string2||'(id number,name varchar2(10))';
--上面的为每分钟删除一个表并创建一个表,保持表的个数跟我传的参数有关。
SELECT count(1) into v_cot FROM USER_VIEWS WHERE VIEW_NAME='VIEW_WJT';
if v_cot=1 then
execute immediate 'drop view VIEW_WJT';
end if;
v_string3 :='create view VIEW_WJT
as
select * from wjt_'||to_char(sysdate,'yyyymmddhh24mi');
execute immediate v_string3;
end;
我想把保留的几个表做成视图,希望高手指点下,
我写的这个视图怎么没生成成功、
authid current_user
is
v_string varchar2(200);
v_string2 varchar2(200);
v_string3 varchar2(200);
v_cnt number(18,0);
v_cot number(18,0);
begin
EXECUTE IMMEDIATE 'SELECT to_char(sysdate-interval '''||to_char(v_num)||''' minute,''yyyymmddhh24mi'') FROM dual' INTO v_string;
v_string := 'WJT_'||v_string;
SELECT count(1) INTO v_cnt from user_tables where table_name=v_string;
IF v_cnt=1 then
EXECUTE immediate 'DROP TABLE '||v_string;
END IF;
EXECUTE IMMEDIATE 'SELECT to_char(sysdate,''yyyymmddhh24mi'') FROM dual' INTO v_string2;
execute immediate 'create table WJT_'||v_string2||'(id number,name varchar2(10))';
--上面的为每分钟删除一个表并创建一个表,保持表的个数跟我传的参数有关。
SELECT count(1) into v_cot FROM USER_VIEWS WHERE VIEW_NAME='VIEW_WJT';
if v_cot=1 then
execute immediate 'drop view VIEW_WJT';
end if;
v_string3 :='create view VIEW_WJT
as
select * from wjt_'||to_char(sysdate,'yyyymmddhh24mi');
execute immediate v_string3;
end;
我想把保留的几个表做成视图,希望高手指点下,
我写的这个视图怎么没生成成功、
'create view VIEW_WJT
as
select * from wjt_'||vstring2;--改成这样试试
execute immediate v_string3;
end;
authid current_user
is
v_string varchar2(200);
v_string2 varchar2(200);
v_cnt number(18,0);
begin
EXECUTE IMMEDIATE 'SELECT to_char(sysdate-interval '''||to_char(v_num)||''' minute,''yyyymmddhhmi'') FROM dual' INTO v_string;
v_string := 'WJT_'||v_string;
SELECT count(1) INTO v_cnt from user_tables where table_name=v_string;
IF v_cnt=1 then
EXECUTE immediate 'DROP TABLE '||v_string;
END IF;
v_string2 := 'create table wjt_'||to_char(sysdate,'yyyymmddhhmi')||'(id number,name varchar2(10))';
execute immediate v_string2;--创建视图
end;
添加和删除都没问题,就是想把几个表创建视图时有问题,那位能帮我解答??
authid current_user
is
v_string varchar2(200);
v_string2 varchar2(200);
v_string3 varchar2(200);
v_cnt number(18,0);
v_cot number(18,0);
begin
EXECUTE IMMEDIATE 'SELECT to_char(sysdate-interval '''||to_char(v_num)||''' minute,''yyyymmddhhmi'') FROM dual' INTO v_string;
v_string := 'WJT_'||v_string;
SELECT count(1) INTO v_cnt from user_tables where table_name=v_string;
IF v_cnt=1 then
EXECUTE immediate 'DROP TABLE '||v_string;
END IF;
v_string2 := 'create table wjt_'||to_char(sysdate,'yyyymmddhhmi')||'(id number,name varchar2(10))';
execute immediate v_string2;
SELECT count(1) into v_cot FROM USER_VIEWS WHERE VIEW_NAME='VIEW_WJT';
if v_cot=1 then
execute immediate 'drop view VIEW_WJT';
end if;
v_string3 :='create view VIEW_WJT
as
select * from wjt_'||to_char(sysdate,'yyyymmddhhmi');
execute immediate v_string3;
end;创建视图已经成功,可是这只是一个表,如果我保留5个表建视图,要怎么让5个表连接起来,
难道这样create view VIEW_WJT
as
select * from wjt_'||to_char(sysdate,'yyyymmddhhmi')||',wjt_'||to_char(sysdate-interval '1' minute,'yyyymmddhhmi')||',wjt_'||to_char(sysdate-interval '2' minute,'yyyymmddhhmi')||',wjt_'||to_char(sysdate-interval '3' minute,'yyyymmddhhmi')||',wjt_'||to_char(sysdate-interval '4' minute,'yyyymmddhhmi')||',wjt_'||to_char(sysdate-interval '5' minute,'yyyymmddhhmi')
很繁琐
authid current_user
is
v_beforeDate varchar2(200); -- 当前时间 减去 v_num 分钟后的时间
v_currentDate varchar2(200); -- 当前时间 (精确到分钟)
v_string1 varchar2(1000); -- 用以判断删除 当前时间 减去 v_num 分钟后的时间 所生成的表(有则删除)
v_string2 varchar2(200);
v_cnt number(18,0);
begin
EXECUTE IMMEDIATE 'SELECT to_char(sysdate-interval '''||to_char(v_num)||''' minute,''yyyymmddhh24mi''),to_char(sysdate,''yyyymmddhh24mi'')
FROM dual' INTO v_beforeDate, v_currentDate;
v_string1 := 'WJT_'||v_beforeDate;
SELECT count(1) INTO v_cnt from user_tables where table_name=v_string1;
IF v_cnt=1 then
EXECUTE immediate 'DROP TABLE '||v_string1;
END IF;
execute immediate 'create table WJT_'||v_currentDate||'(id number,name varchar2(10))';
--上面的为每分钟删除一个表并创建一个表,保持表的个数跟我传的参数有关。
SELECT count(1) into v_cnt FROM USER_VIEWS WHERE VIEW_NAME='VIEW_WJT';
if v_cnt=1 then
execute immediate 'drop view VIEW_WJT';
end if;
v_string2 :='create view VIEW_WJT as select id,name from wjt_'||v_currentDate;
execute immediate v_string2;
end;
/
2 authid current_user
3 is
4 v_beforeDate varchar2(200); -- 当前时间 减去 v_num 分钟后的时间
5 v_currentDate varchar2(200); -- 当前时间 (精确到分钟)
6 v_string1 varchar2(1000); -- 用以判断删除 当前时间 减去 v_num 分钟后的时间 所生成的表(有则删除)
7 v_string2 varchar2(200);
8 v_cnt number(18,0);
9 begin
10 EXECUTE IMMEDIATE 'SELECT to_char(sysdate-interval '''||to_char(v_num)||''' minute,''yyyymmddhh24mi''),to_char(sysdate,''yyyymmddhh2
11 FROM dual' INTO v_beforeDate, v_currentDate;
12 v_string1 := 'WJT_'||v_beforeDate;
13 SELECT count(1) INTO v_cnt from user_tables where table_name=v_string1;
14 IF v_cnt=1 then
15 EXECUTE immediate 'DROP TABLE '||v_string1;
16 END IF;
17 execute immediate 'create table WJT_'||v_currentDate||'(id number,name varchar2(10))';
18 --上面的为每分钟删除一个表并创建一个表,保持表的个数跟我传的参数有关。
19 SELECT count(1) into v_cnt FROM USER_VIEWS WHERE VIEW_NAME='VIEW_WJT';
20 if v_cnt=1 then
21 execute immediate 'drop view VIEW_WJT';
22 end if;
23 v_string2 :='create view VIEW_WJT as select id,name from wjt_'||v_currentDate;
24 execute immediate v_string2;
25 end;
26 /过程已创建。scott@SZTYORA> exec aaa(10);
create public synonym WJT_201102161528 for scott.WJT_201102161528PL/SQL 过程已成功完成。scott@SZTYORA> select table_name from user_tables where table_name like 'WJT%';TABLE_NAME
------------------------------------------------------------
WJT_201102151200
WJT_201102161528scott@SZTYORA> select view_name from user_views where view_name='VIEW_WJT';VIEW_NAME
------------------------------------------------------------
VIEW_WJTscott@SZTYORA>
is
v_string3 varchar2(400);
v_cot number(18,0);
begin
SELECT count(1) into v_cot FROM USER_VIEWS WHERE VIEW_NAME='VIEW_WJT';
if v_cot=1 then
execute immediate 'drop view VIEW_WJT';
end if;
v_string3 :='create view VIEW_WJT
as
select * from wjt_'||to_char(sysdate,'yyyymmddhhmi')||' UNION ALL select*from wjt_'||to_char(sysdate-interval '1' minute,'yyyymmddhhmi')||' UNION ALL select*from wjt_'||to_char(sysdate-interval '2' minute,'yyyymmddhhmi')||' UNION ALL select*from wjt_'||to_char(sysdate-interval '3' minute,'yyyymmddhhmi')||' UNION ALL select*from wjt_'||to_char(sysdate-interval '4' minute,'yyyymmddhhmi')||' UNION ALL select*from wjt_'||to_char(sysdate-interval '5' minute,'yyyymmddhhmi');
execute immediate v_string3;
end;谁能帮我把这个视图的表连接给优化哈,谢谢