oracle 过程

CREATE OR REPLACE PROCEDURE TEMP_INSERT_alarmport(O_SRETVAL OUT INT) AS

  T_PORTNAME  VARCHAR2(255);
  T_INPORTID  NUMBER(18);
  t_exists    int;
  t_exists1   int;
  t_error     varchar2(500);
  T_COUNT     INT := 0;
  T_CHAR1     varchar2(250);
  T_CHAR2     varchar2(250);
  T_CHAR3     varchar2(250);
  T_CHAR4     varchar2(250);
  T_CHAR5     varchar2(250);
  T_CHAR6     varchar2(250);
  T_CHAR7     varchar2(250);
  T_NUMBER    varchar2(250);
  t_countnum  varchar2(250);
  t_countnum2 varchar2(250);
  CURSOR CR_ARARMPORT IS
    SELECT a.inportid, a.portname FROM cr_port_info a;
  BEGIN
  O_SRETVAL  := 0;
  T_PORTNAME := null;
  T_INPORTID := null;

  OPEN CR_ARARMPORT;
  FETCH CR_ARARMPORT
    INTO T_INPORTID, T_PORTNAME;
  WHILE CR_ARARMPORT%FOUND LOOP
    T_COUNT := T_COUNT + 1;
 
    begin
      select instr(T_PORTNAME, ':') into t_countnum from dual;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        t_countnum := 0;
      WHEN OTHERS THEN
        T_ERRor := SQLERRM;
    end;
 
    T_CHAR1 := substr(T_PORTNAME, 1, t_countnum - 1);
    T_CHAR2 := substr(T_PORTNAME, t_countnum + 1);
    BEGIN
      select instr(T_CHAR2, '？') into t_countnum2 from dual;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        t_countnum := 0;
      WHEN OTHERS THEN
        T_ERRor := SQLERRM;
    end;
 
    T_CHAR3 := substr(T_CHAR2, 1, t_countnum2 - 1);
    select length(T_CHAR3) into t_exists1 from dual;
    if t_exists1 = 1 then
      T_CHAR5 := substr(T_CHAR2, t_countnum2 - 1, t_countnum2 - 2);
    elsif t_exists1 = 2 then
      T_CHAR5 := substr(T_CHAR2, 1, t_countnum2 - 1);
    else
      T_CHAR5 := substr(T_CHAR2, 1, t_countnum2 - 1);
    end if;
 
    if T_CHAR5 like '0%' then
   
      T_CHAR6 := substr(T_CHAR2, t_countnum2 - 1, t_countnum2 - 2);
    else
      T_CHAR6 := substr(T_CHAR2, 1, t_countnum2 - 1);
    end if;
 
    T_CHAR4 := substr(T_CHAR2, t_countnum2 + 1);
    T_CHAR5 := '1' || '-' || '1' || '-' || T_CHAR6 || '-' || T_CHAR4;
 
    begin
      select count(1)
        into t_exists
        from cr_alarmport_inport aa
       where aa.inportid = T_INPORTID;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        t_countnum := 0;
      WHEN OTHERS THEN
        T_ERRor := SQLERRM;
    end;
 
    if t_exists = '0' then
      BEGIN
        INSERT INTO cr_alarmport_inport
          (SUBJECTTYPE, NMID, DEVALARMNAME, PORTALARMNAME, INPORTID)
       
        VALUES
          ('2', '2', T_CHAR1, T_CHAR5, T_INPORTID);
      EXCEPTION
        WHEN OTHERS THEN
          t_error := sqlerrm;
      END;
    end if;
 
    IF T_COUNT >= 500 THEN
      COMMIT;
    END IF;
    FETCH CR_ARARMPORT
      INTO T_INPORTID, T_PORTNAME;
  END LOOP;
  CLOSE CR_ARARMPORT;
  COMMIT;
  O_SRETVAL := T_COUNT;
EXCEPTION
  WHEN OTHERS THEN
    IF CR_ARARMPORT%ISOPEN THEN
      CLOSE CR_ARARMPORT;
    END IF;
    ROLLBACK;
END;