存储过程你go if object_id('p_create')is not null drop proc p_create go create proc p_create as go if object_id('tbl')is not null drop table tbl go create table tbl( --表结构 ) go insert into tbl --理解查询语句 --调用存储过程 exec p_create--这是在MSSQL中的写法,oracle也应该是这样吧,具体的你修改一下就好了
CREATE OR REPLACE PROCEDURE P_TB_DAY(P_DAY NUMBER) AS V_SQL VARCHAR2;BEGIN V_SQL := 'CREATE TABLE '''P_DAY''' as select * from 已知格式的表' : EXECUTE IMMEDIATE; COMMIT; V_SQL := 'INSERT INTO 'P_DAY' AS SELECT (填写表结构相对应的字段) FROM TABLE 1 t,TABLE 2 t1 ,TABLE t2'; COMMIT;END P_TB_DAY;定计划跑的话,可以用JOB实行。
EXECUTE IMMEDIATE v_sql; 这里加个V_SQL 。。
1. 创建表 A create table 'smart_deal_'||to_char(sysdate,'yyyymmdd') as select * from yourtbl where 1=1; B create table 'smart_deal_'||to_char(sysdate,'yyyymmdd') as select * from yourtable where 1<>1 insert into 'smart_deal_'||to_char(sysdate,'yyyymmdd') select * from yourlianheTblName where 1=1 ;2. 创建JOB sys.dbms_job.submit(job => :job, what => 'youprocedurename;', next_date => to_date('15-02-2012 08:32:00', 'dd-mm-yyyy hh24:mi:ss'), interval => 'sysdate+30'); commit;
简单,动态sql +job定时作业
--存储过程没写全 CREATE OR REPLACE PROCEDURE P_ZC1 AS BEGIN create table XX as select * from 已知表结构表名 where 1<>1; COMMIT; insert into XX values(取来的数据); COMMIT; END P_ZC1; --- job begin sys.dbms_job.submit(job => :job, what => 'PP_ZC1', interval => 'trunc(sysdate)+30'); commit; end;
创建存储过程后,再创建一个作业,定期执行,这个不难。可以把多表联合查询的其它表的表结构贴一下,才能写具体的存储过程。否则,只能写一下框。
所有表结构贴上来实在太多了。我有的一些TSQL的基础,大家可以先帮我假设几个简单的表结构,能让我照猫画虎就行……
if object_id('p_create')is not null
drop proc p_create
go
create proc p_create
as
go
if object_id('tbl')is not null
drop table tbl
go
create table tbl(
--表结构
)
go
insert into tbl --理解查询语句
--调用存储过程
exec p_create--这是在MSSQL中的写法,oracle也应该是这样吧,具体的你修改一下就好了
EXECUTE IMMEDIATE;
COMMIT;
V_SQL := 'INSERT INTO 'P_DAY' AS SELECT (填写表结构相对应的字段) FROM TABLE 1 t,TABLE 2 t1 ,TABLE t2'; COMMIT;END P_TB_DAY;定计划跑的话,可以用JOB实行。
这里加个V_SQL 。。
1. 创建表
A create table 'smart_deal_'||to_char(sysdate,'yyyymmdd') as
select * from yourtbl where 1=1;
B create table 'smart_deal_'||to_char(sysdate,'yyyymmdd') as select * from yourtable where 1<>1
insert into 'smart_deal_'||to_char(sysdate,'yyyymmdd') select * from yourlianheTblName where 1=1 ;2. 创建JOB
sys.dbms_job.submit(job => :job,
what => 'youprocedurename;',
next_date => to_date('15-02-2012 08:32:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+30');
commit;
CREATE OR REPLACE PROCEDURE P_ZC1
AS
BEGIN
create table XX as select * from 已知表结构表名 where 1<>1;
COMMIT;
insert into XX values(取来的数据);
COMMIT;
END P_ZC1;
--- job begin
sys.dbms_job.submit(job => :job,
what => 'PP_ZC1',
interval => 'trunc(sysdate)+30');
commit;
end;