BillGilbert

Untitled

Nov 23rd, 2015
147
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION insert_payjournalrec_arch(v_start_id   IN NUMBER,
  2.                                                      count_record IN NUMBER)
  3.   RETURN BOOLEAN AS
  4.   save_success BOOLEAN;
  5. BEGIN
  6.   DECLARE
  7.     j   INT;
  8.     cnt INT;
  9.   BEGIN
  10.     DBMS_OUTPUT.enable;
  11.     j   := 0;
  12.     cnt := 0;
  13.     FOR i IN (SELECT id
  14.                 FROM ipsh.payjournalrec pay
  15.                WHERE pay.id <= v_start_id) LOOP
  16.       j   := j + 1;
  17.       cnt := cnt + 1;
  18.       INSERT INTO payjournalrec_arch
  19.         (SELECT * FROM payjournalrec WHERE id = i.id);
  20.    
  21.       DELETE ipsh.payjournalrec pay_inn WHERE pay_inn.id = i.id;
  22.    
  23.       IF j > count_record THEN
  24.         COMMIT;
  25.         DBMS_OUTPUT.put_line('PROCESS ' || TO_CHAR(cnt) || ' RECORDS');
  26.         j := 0;
  27.       END IF;
  28.     END LOOP;
  29.     IF (j > 0) THEN
  30.       DBMS_OUTPUT.put_line('PROCESS ' || TO_CHAR(cnt) || ' RECORDS');
  31.       COMMIT;
  32.     END IF;
  33.   END;
  34. END;
  35. /
Advertisement
Add Comment
Please, Sign In to add comment