随访表更新过程 3月4号

CREATE OR REPLACE PROCEDURE PROC_UPDATE_JXHEALTH_VISIT2 is
  v_sqlerrm VARCHAR2(500);

  TYPE JT_RECORD_TYPE IS RECORD(
    NAME           ZSBOOK1.NAME%TYPE,
    PID            ZSBOOK2.PID%TYPE,
    VISITDOCNAME   ZSBOOK2.VISITDOCNAME%TYPE,
    FOLLOWUPDATE   ZSBOOK2 .FOLLOWUPDATE%TYPE,
    NEXTFOLLOWDATE ZSBOOK2.NEXTFOLLOWDATE%TYPE,
    HEIGHT         ZSBOOK2.HEIGHT%TYPE,
    BWI            ZSBOOK2.BWI%TYPE,
    UP             ZSBOOK2.UP%TYPE,
    DOWN           ZSBOOK2.DOWN%TYPE,
    CID            ZSBOOK2.CID%TYPE,
    VISITSUM       ZSBOOK2.VISITSUM%TYPE);
  jt_record JT_RECORD_TYPE;

  CURSOR cur_jt IS
    SELECT DISTINCT J.NAME,
                    J.PID,
                    J.VISITDOCNAME,
                    J.FOLLOWUPDATE,
                    J.NEXTFOLLOWDATE,
                    J.HEIGHT,
                    J.BWI,
                    J.VISITSUM,
                    J.UP,
                    J.DOWN,
                    t2.CID
      FROM (select t.*
              from zsbook2 t
             where not exists (select *
                      from zsbook2
                     where t.followupdate < followupdate
                       and t.name = name
                       and t.pid = pid)
             order by pid) t2
      left join ZSBOOK2 J on j.pid = t2.pid
                         and j.name = t2.name;
BEGIN
  OPEN cur_jt;
  LOOP
    FETCH cur_jt
      INTO jt_record.NAME, jt_record.PID, jt_record.VISITDOCNAME, jt_record.FOLLOWUPDATE, jt_record.NEXTFOLLOWDATE, jt_record.HEIGHT, jt_record.BWI, jt_record.VISITSUM, jt_record.up, jt_record.DOWN, jt_record.CID;
    EXIT WHEN cur_jt%NOTFOUND;
 
    INSERT INTO HYPERTENSIONVISIT H
      (H.PID,
       H.NAME,
       H.RFLAG,
       H.VISITDOCNAME,
       H.FOLLOWUPDATE,
       H.NEXTFOLLOWDATE,
       H.HEIGHT,
       H.BWI,
       H.VISITSUM,
       H.BMI,
       H.CREATEUID,
       H.CREATTIME,
       H.UPDATEUID,
       H.UPDATETIME,
       H.BLDPRESSUREH,
       H.BLDPRESSUREVALUEL,
       H.HYPERTENSIONCID,
       H.FOLLOWSTATE)
    VALUES
      (jt_record.PID,
       jt_record.NAME,
       '0',
       jt_record.VISITDOCNAME,
       jt_record.FOLLOWUPDATE,
       jt_record.NEXTFOLLOWDATE,
       jt_record.HEIGHT,
       jt_record.BWI,
       jt_record.VISITSUM,
       '50.00',
       '001',
       SYSDATE,
       '001',
       SYSDATE,
       jt_record.UP,
       jt_record.DOWN,
       jt_record.CID,
       '05');
 
    IF (MOD(cur_jt%rowcount, 100) = 0) THEN
      COMMIT;
    END IF;
  END LOOP;
  CLOSE cur_jt;
  COMMIT;
EXCEPTION
  when others then
    v_sqlerrm := substr(SQLERRM, 1, 300);
    dbms_output.put_line('ERR=' || v_sqlerrm);
    rollback;
end PROC_UPDATE_JXHEALTH_VISIT2;