使用游標的一個存儲過程

CREATE OR REPLACE PROCEDURE Test_Sync_MID_DEPT
IS
  v_count1 NUMBER;
  v_temp_hr varchar2(4000);
  v_temp_mid varchar2(4000);
  
  CURSOR  dept_cur IS
  SELECT d.dept_id,d.company_code,d.c_dept_name,d.e_dept_name,d.c_dept_abbr,
         d.e_dept_abbr,d.status,d.main_leader_no
  FROM test_hr_mid_department d;
  
BEGIN
  
  FOR dept_rec IN dept_cur LOOP
    v_count1 := 0;
    
    SELECT COUNT(1) INTO v_count1
    FROM test_v_mid_department 
    WHERE dept_id = dept_rec.dept_id;
    
    IF v_count1=0 THEN --test_v_mid_department沒有insert
         INSERT INTO test_v_mid_department
         SELECT * FROM test_hr_mid_department
         WHERE dept_id=dept_rec.dept_id;
    ELSE
         v_temp_mid := NULL;
         v_temp_hr := dept_rec.dept_id||dept_rec.company_code||dept_rec.c_dept_name
                    ||dept_rec.e_dept_name||dept_rec.c_dept_abbr||dept_rec.e_dept_abbr
                    ||dept_rec.status||dept_rec.main_leader_no;
                    
         FOR mid_rec IN(
         SELECT dept_id,company_code,c_dept_name,e_dept_name,c_dept_abbr,e_dept_abbr
                ,status,main_leader_no 
         FROM test_v_mid_department
         WHERE dept_id=dept_rec.dept_id
         )LOOP
           v_temp_mid := mid_rec.dept_id||mid_rec.company_code||mid_rec.c_dept_name
           ||mid_rec.e_dept_name||mid_rec.c_dept_abbr||mid_rec.e_dept_abbr||mid_rec.status
           ||mid_rec.main_leader_no;
    
           IF v_temp_mid<>v_temp_hr THEN --對比資料如果不一樣
             UPDATE test_v_mid_department v
             SET  v.dept_id=dept_rec.dept_id,v.company_code=dept_rec.company_code,
                  v.c_dept_name=dept_rec.c_dept_name,v.e_dept_name=dept_rec.e_dept_name,
                  v.c_dept_abbr=dept_rec.c_dept_abbr,v.e_dept_abbr=dept_rec.e_dept_abbr,
                  v.status=dept_rec.status,v.main_leader_no=dept_rec.main_leader_no
             WHERE v.dept_id=dept_rec.dept_id;
           END IF;
         END LOOP;
    END IF;
  END LOOP;
END Test_Sync_MID_DEPT;