存储过程（自动备份数据）

存储过程的作用：

1、（银行）将经常要执行的sql语句写成储存过程，储存在数据库中，可以提高执行效率。

2、储存过程的真正作用在于数据整合等复杂的业务操作。

3、在数据库开发领域中，存储过程是用作数据整合，数据接口，数据备份等操作。

功能：每天晚上12点备份银行交易记录明细表当日信息

思路：1.查询tb_transcation表的（当天）所有数据

            2.插入备份表tb_back

注意：1.将每天备份的最大id储存于第二天备份，为了演示储存过程的相互调用，最大的id会作为参数传递到当前程序

            2.每次commit执行io动作，非常消耗资源，尽量避免多次commit，一次commit需要处理“数据回滚段溢出”问题，使用“分段提交”解决问题

#########################################准备测试数据#########################################

--备份的银行表
CREATE TABLE tb_transaction(
ID INT NOT NULL,
ACCOUNT VARCHAR2(18) NOT NULL UNIQUE, --账号
amount NUMBER NOT NULL,               --金额 
currdate DATE,
CONSTRAINTS tb_transaction PRIMARY KEY(ID)
);

--银行表序列
CREATE SEQUENCE seq_tb_transaction; 

--插入银行表测试数据
INSERT INTO tb_transaction(id,account,amount,currdate)VALUES(seq_tb_transaction.nextval,'001',1200,SYSDATE);
INSERT INTO tb_transaction(id,account,amount,currdate)VALUES(seq_tb_transaction.nextval,'002',800,SYSDATE);
INSERT INTO tb_transaction(id,account,amount,currdate)VALUES(seq_tb_transaction.nextval,'003',3200,SYSDATE);
INSERT INTO tb_transaction(id,account,amount,currdate)VALUES(seq_tb_transaction.nextval,'004',3200,SYSDATE);

--备份表
CREATE TABLE tb_back AS SELECT * FROM tb_transaction WHERE ID = 999999;

-- 存储每天的最大的id的表
CREATE TABLE tb_maxid(
maxid INT DEFAULT 0
);

#########################################所需函数#########################################

一、储存过程：backpro(v_maxid NUMBER),v_maxid 为表tb_maxid中的最大id

create or replace procedure backpro(v_maxid NUMBER) 
IS
  CURSOR c_tb_transaction IS SELECT * FROM tb_transaction WHERE id>v_maxid;
  v_id NUMBER;
  -- 处理”数据回滚段溢出问题
  v_index NUMBER := 0;
begin
  FOR t_tb_trans IN c_tb_transaction
    LOOP
      INSERT INTO tb_back(id,account,amount,currdate)
      VALUES(t_tb_trans.id,t_tb_trans.account,t_tb_trans.amount,t_tb_trans.currdate);
      --自增
      v_index := v_index+1;
      --每一千条数据提交一次
      IF(v_index = 1000) THEN
        COMMIT;
        v_index := 0;
      END IF;  
    END LOOP;
    --储存最大的id
    SELECT max(id) INTO v_id FROM tb_transaction;
    UPDATE tb_maxid SET maxid = v_id;
    COMMIT;  
end backpro;

二、储存过程：callbackpro 此处调用备用储存过程·backpro(v_maxid);

create or replace procedure callbackpro IS
   v_maxid NUMBER;
   v_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_count FROM tb_maxid; 
   IF(v_count > 0) THEN
     SELECT MAX(maxid) INTO v_maxid FROM tb_maxid;
   ELSE 
     INSERT INTO tb_maxid VALUES(0);
     v_maxid :=0;    
   END IF;
   ---- 调用备份的存储过程
   backpro(v_maxid);
end callbackpro ;

三、设置定时器（用dbms_job.submit创建定时器）测试为每分钟备份一次
--定时器
--创建调度任务定时器
DECLARE
  jobno NUMBER; --定时器编号
BEGIN 
  dbms_job.submit(
     jobno,
     what => 'callbackpro;', --callbackpro为存储过程的名称
     INTERVAL =>'trunc(sysdate,''mi'') +1/(24*60) ' --定义时间间隔每分钟
  );
  COMMIT;
END;

#########################################补充#########################################

SELECT * FROM user_jobs; --查看调度任务
SELECT * FROM Dba_Jobs_Running; --查看正在执行的调度任务
SELECT * FROM dba_jobs; --查看执行完的调度任务

--删除调度任务定时器
BEGIN
   dbms_job.remove(121);
   COMMIT;  
END;
#########################################时间例子#########################################
1.每分钟执行
INTERVAL => TRUNC(SYSDATE,'mi') + 1/(24*60);
2.每天执行
例如：每天的凌晨两点执行
INTERVAL => TRUNC(SYSDATE,'dd') + 2/24;
3.每周定时执行
例如：每周一凌晨两点执行
INTERVAL => TRUNC(next_day(SYSDATE,2)) +2/24
4.每月定时执行
例如：每月1日凌晨2点执行
INTERVAL => TRUNC(last_day(SYSDATE)) + 1 +2/24;
5.每季度定时执行
例如：每季度的第一天凌晨两点执行
INTERVAL => TRUNC(add_months(SYSDATE,3),'Q') +2/24;
6.每半年定时执行
例如：每年7月1日和1月1日凌晨2点执行
INTERVAL => add_months(TRUNC(SYSDATE,'yyyy'),6)+2/24;
7.每年定时执行
例如：每年1月1日凌晨两点执行
INTERVAL =>add_months(TRUNC(SYSDATE,'yyyy'),12) + 2/24