记录今天写的存储,实现汇率表中遗漏日期补充之前最后一天的数据

CREATE OR REPLACE PROCEDURE LAOWANG_CD_CURRENCYEXCH AS
  A DATE;
  B DATE;
  C VARCHAR2(5);
  D DATE;
BEGIN
  SELECT MIN(EXCHDATE) INTO A FROM CD_CURRENCYEXCH;
  SELECT MAX(EXCHDATE) INTO D FROM CD_CURRENCYEXCH;
    B := to_date(to_char(A+1,'yyyy-mm-dd'),'yyyy-mm-dd');
  LOOP
    --DBMS_OUTPUT.PUT_LINE(B);
    SELECT COUNT(1)
      INTO C
      FROM CD_CURRENCYEXCH
     WHERE TRUNC(EXCHDATE, 'DD') = TRUNC(B, 'DD');
    IF C = 0 THEN
      --EXCUTE IMMEDIATE'
      INSERT INTO CD_CURRENCYEXCH
        (EXCHDATE,
         BASECURRENCY,
         EXCHCURRENCY,
         EXCHRATE,
         VALIDSTATUS,
         INVALIDDATE,
         FLAG,
         MODIFYDATE)
        SELECT EXCHDATE+1,
               BASECURRENCY,
               EXCHCURRENCY,
               EXCHRATE,
               VALIDSTATUS,
               INVALIDDATE+1,
               FLAG,
               MODIFYDATE+1
          FROM CD_CURRENCYEXCH
         WHERE TRUNC(EXCHDATE,'DD') = TRUNC(B, 'DD')-1 ;
                 COMMIT;
    END IF;
        B := B+1;
    EXIT WHEN B = D;
  END LOOP;
END LAOWANG_CD_CURRENCYEXCH;
汇率表建表语句:

create table CD_CURRENCYEXCH
(
  exchdate     DATE not null,
  basecurrency VARCHAR2(3) not null,
  exchcurrency VARCHAR2(3) not null,
  exchrate     NUMBER(10,6),
  validstatus  VARCHAR2(50) not null,
  invaliddate  DATE,
  flag         VARCHAR2(4000),
  modifydate   DATE
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 832K
    next 8K
    minextents 1
    maxextents unlimited
  );

补充调用函数:

CREATE OR REPLACE FUNCTION FUN_CD_CURRENCYEXCH(STATDATE IN DATE,CURRENCY IN VARCHAR2)
  RETURN NUMBER AS
  RESULT1  NUMBER;
BEGIN
IF CURRENCY = '01'
    THEN RESULT1 := 1;
        ELSE
SELECT A.EXCHRATE INTO RESULT1 FROM CD_CURRENCYEXCH A WHERE A.EXCHDATE = STATDATE AND A.BASECURRENCY = CURRENCY AND A.EXCHCURRENCY = '01';
END IF;
  RETURN RESULT1;
END FUN_CD_CURRENCYEXCH;