CREATE PROCEDURE [up_auto_backup]
@path nvarchar(50)
AS
declare @dt nvarchar(120)
declare @prep nvarchar(200)
declare @ParmDefinition nvarchar(200)
declare @num intselect @dt = convert(nvarchar(12),getdate(),112)
set @num = right(@dt,1)
set @num = @num - convert(int,(@num/5))*5
if right(@dt,2) = '31'
begin
set @num = 6
endselect @dt = @path+'bak'+convert(nvarchar(1),@num )
set @prep = "BACKUP DATABASE [lmserver] TO DISK = N'"+@dt+"' WITH INIT , NOUNLOAD , NAME = N'lmserver 备份', NOSKIP , STATS = 10, NOFORMAT "--select @prepEXEC sp_executesql @prep
GO这个是sql版本的 求一个oracle版本的 能实现这个功能的
解决方案 »
- PL/SQL Developer开发环境中查找刚创建函数的问题
- 奇怪问题,oracle不能做插入操作,可以查询。有谁遇到过ORA-01110,求解!!
- 求助oracle创建java的jdbc存储过程问题。
- 我的程序插不进数据,源代码在这,大家帮忙看一下,谢谢
- 在表上创建trigger时提示权限不足该怎么解决。[在线等]
- 还是100分,请各位高手进
- oracle在linux下如何启动
- 我如何管理配置我的第二个数据库?急!!在线等
- 江湖救急,十万火急-100分急问客户端和服务器端的字符集一致时发生的字符集乱码问题
- oracle number类型会自动四舍五入?
- 求oracle入门基础教程
- 调用过程中发现变量无法赋值,请指教
文章出处:http://www.cnblogs.com/stoneblog/archive/2009/09/23/1572548.html1、创建存储过程
create or replace procedure data_auto_backup as
v_tablename varchar2(200);
v_year varchar2(10);
v_month varchar2(10);
v_bakdate varchar2(50);
v_maxdate date;
v_tablecount integer;
v_recordcount integer;
begin
select max(t.field1) - 30 into v_maxdate from table1 t;
v_year := to_char(v_maxdate,'yyyy');
v_month := to_char(v_maxdate,'MM');
v_bakdate := to_char(v_maxdate,'yyyy-MM-dd');
-- 检查将要使用的年月表是否存在
v_tablename := 'table1' || v_year || v_month;
SELECT COUNT(TABLE_NAME) INTO v_tablecount FROM USER_TABLES ut WHERE ut.table_name = v_tablename;
if v_tablecount>0 then
dbms_output.put_line('该表存在!');
else begin
dbms_output.put_line('该表不存在或当前用户无权访问!');
execute immediate 'CREATE TABLE ' || v_tablename || ' AS SELECT * FROM table1 sr WHERE sr.field2=''''';
end;
end If; -- table1表备份
execute immediate 'DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
execute immediate 'INSERT INTO ' || v_tablename || ' SELECT * FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
--dbms_output.put_line('DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
--dbms_output.put_line('INSERT INTO ' || v_tablename || ' SELECT * FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
--dbms_output.put_line('SELECT COUNT(sr.field2) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
execute immediate 'SELECT COUNT(sr.field2) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''' into v_recordcount;
if v_recordcount>0 then begin
execute immediate 'DELETE FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
--dbms_output.put_line('DELETE FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
end;
end If;
commit;
-- 检查将要使用的年月表是否存在
v_tablename := 'table2' || v_year || v_month;
SELECT COUNT(TABLE_NAME) INTO v_tablecount FROM USER_TABLES ut WHERE ut.table_name = v_tablename;
if v_tablecount>0 then
dbms_output.put_line('该表存在!');
else begin
dbms_output.put_line('该表不存在或当前用户无权访问!');
execute immediate 'CREATE TABLE ' || v_tablename || ' AS SELECT * FROM table2 cpi WHERE cpi.field3=''''';
end;
end If; -- table2表备份
execute immediate 'DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
execute immediate 'INSERT INTO ' || v_tablename || ' SELECT * FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
--dbms_output.put_line('DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
--dbms_output.put_line('INSERT INTO ' || v_tablename || ' SELECT * FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
--dbms_output.put_line('SELECT COUNT(sr.field3) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
execute immediate 'SELECT COUNT(sr.field3) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''' into v_recordcount;
if v_recordcount>0 then begin
execute immediate 'DELETE FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
--dbms_output.put_line('DELETE FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
end;
end If;
commit;
dbms_output.put_line('Ok' || v_recordcount);
end;
2、创建JOB--1天运行一次,当前运行第一次
begin
sys.dbms_job.submit(job => :job,
what => 'data_auto_backup;',
next_date => sysdate,
interval => 'sysdate+1');
commit;
end;
/