存储过程学习

create or replace procedure CheckCorpName AS
  v_count     integer;
  v_corp_org  integer;
  v_corp_id   integer;
  v_old_name  varchar2(200);
  v_new_name  varchar2(200);
  V_ERRORCODE NUMBER;
  V_ERRORTEXT VARCHAR2(300);

  CURSOR c_1 IS
    select a.org, a.id, a.corp_name new_name, b.corp_name old_name
      from t_corp@gsnwdblink a, t_ccp_label_corp2 b
     where a.org = b.corp_org
       and a.id = b.corp_id
       and a.corp_name <> b.corp_name
       and (a.come_out is null or a.come_out = '0')
       And a.cancel_flag Is Null
       and a.corp_status is not null;
  v_cursor1 c_1%rowtype;
BEGIN
  open c_1; --open;
  loop
    fetch c_1
      into v_cursor1;
    exit when c_1%NOTFOUND;
    v_corp_org := v_cursor1.org;
    v_corp_id  := v_cursor1.id;
    v_old_name := v_cursor1.old_name;
    v_new_name := v_cursor1.new_name;
    select count(*)
      into v_count
      from T_CCP_OLD_NAME_NAME
     where corp_org = v_corp_org
       and corp_id = v_corp_id;
    if v_count = 0 then
      insert into T_CCP_OLD_NAME_NAME
      values
        (v_old_name, v_new_name, v_corp_org, v_corp_id, sysdate);
    end if;
  end loop;

  commit;
  close c_1;
EXCEPTION
  when others THEN
    ROLLBACK;
    --close c_cursor2;
    V_ERRORCODE := SQLCODE;
    V_ERRORTEXT := SUBSTR(SQLERRM, 1, 250);
    COMMIT;
END;