插入潜在客户行的命令过程

我试图编写一个命令过程,使用临时表中的实体id将prospect行插入prospect表。我是一个相当新的命令程序,所以任何我出错的地方都将不胜感激。我应该总共有4行要更新,但当我运行我的过程时,它给我的计数是0。提前谢谢!

  SET SERVEROUTPUT ON SIZE 125000;

  DECLARE
  v_rec_count    INTEGER;
  v_insert_count INTEGER;
  v_zz_last_id   zz_new_id.last_id%TYPE;
  v_prospect_id  prospect.prospect_id%TYPE;
  x_id_number    entity.id_number%TYPE;



  CURSOR cur_prospect IS
    SELECT DISTINCT e.id_number
    FROM wms_temp_ids_flds_jjd w 
    JOIN entity e ON e.id_number = w.t_id_num
                 AND e.record_status_code = 'A'
    WHERE TRIM(w.t_id_num) IS NOT NULL
      AND NOT EXISTS (SELECT 1
                      FROM prospect_entity pe
                      JOIN prospect pt ON pt.prospect_id = pe.prospect_id
                                      AND pt.active_ind = 'Y'
                      WHERE pe.id_number = w.t_id_num);


BEGIN

  DBMS_OUTPUT.ENABLE(125000);

  v_rec_count    := 0;
  v_insert_count := 0;


  SELECT COUNT(DISTINCT e.id_number)
    INTO v_rec_count
    FROM wms_temp_ids_flds_jjd w
    JOIN entity e ON e.id_number = w.t_id_num
                 AND e.record_status_code = 'A'
    WHERE TRIM(w.t_id_num) IS NOT NULL
      AND NOT EXISTS (SELECT 1
                      FROM prospect_entity pe
                      JOIN prospect pt ON pt.prospect_id = pe.prospect_id
                                      AND pt.active_ind = 'Y'
                      WHERE pe.id_number = w.t_id_num);


  DBMS_OUTPUT.PUT_LINE('Number of prospect rows to insert: ' || v_rec_count);

  IF v_rec_count = 0 THEN
    GOTO all_done;
  END IF;

  SELECT id.last_id INTO v_zz_last_id FROM zz_new_id id WHERE id.id_type = 'X';

  DBMS_OUTPUT.PUT_LINE('Last prospect id used: ' || v_zz_last_id);

  SELECT MAX(p.prospect_id) + 1 INTO v_prospect_id FROM prospect p;

  DBMS_OUTPUT.PUT_LINE('First prospect id to use on insert: ' || 
v_prospect_id);

  IF v_zz_last_id + 1 < v_prospect_id THEN
    GOTO no_match;
  END IF;

  OPEN cur_prospect;
   FETCH cur_prospect
    INTO x_id_number;
  WHILE cur_prospect%FOUND
  LOOP

--(*UPD*)
/*
    INSERT INTO prospect
    VALUES
      (v_prospect_id, --prospect_id
       'P', --prospect_type
       'Y', --active_ind
       ' ', --prospect_group_code
       'P', --stage_code
       TRUNC(SYSDATE), --start_date
       NULL, --stop_date
       ' ', --campaign_code
       'U', --rating_code
       0.00, --rated_amt
       ' ', --rating_status_code
       'N', --major_prospect_ind
       ' ', --major_prospect_code
       'Z1', --classification_code (inclination)
       ' ', --solicitation_priority_code
       ' ', --prospect_affiliation_code
       'N', --pg_prospect_ind
       ' ', --contact_result_code
       0.00, --result_amt
       NULL, --result_date
       ' ', --prospect_team_code
       ' ', --prospect_region_code
       ' ', --prospect_committee_code
       ' ', --xcomment
       NULL, --expected_date
       ' ', --unit_code 
       SYSDATE, --date_added
       SYSDATE, --date_modified
      'JJDadd', --operator_name
      'IS' --user_group
      ,NULL --location_id 
       );


    INSERT INTO prospect_entity
    VALUES
      (v_prospect_id, --prospect_id
       x_id_number, --id_number
       'Y', --primary_ind
       SYSDATE, --date_added
       SYSDATE, --date_modified
       'JJDadd', --operator_name
       'IS' --user_group
       ,NULL --location_id 
       );

--*/

    v_insert_count := v_insert_count + 1;
    DBMS_OUTPUT.PUT_LINE(v_insert_count || v_prospect_id);
    v_prospect_id := v_prospect_id + 1;
    FETCH cur_prospect
      INTO x_id_number;
  END LOOP;
  CLOSE cur_prospect;

  DBMS_OUTPUT.PUT_LINE('Total prospect rows inserted: ' || v_insert_count);

  SELECT MAX(p.prospect_id) INTO v_prospect_id FROM prospect p;

--(*UPD*)
/*
  UPDATE zz_new_id SET zz_new_id.last_id = v_prospect_id WHERE 
zz_new_id.id_type = 'X';
--*/

  --COMMIT;
  DBMS_OUTPUT.PUT_LINE('Last id: ' || v_prospect_id);
  GOTO all_done;

  <<no_match>>
  DBMS_OUTPUT.PUT_LINE('Last id ' || v_zz_last_id || ' mismatch on next 
prospect_id to use ' || v_prospect_id || ' - INSERT ABORTED');

  <<all_done>>
  DBMS_OUTPUT.PUT_LINE(' ');
END;  
/