[carloscps]

It would an help for creation of procedure in Oracle 10g
I have two tables (table1 and table2). In table1 I go to have given that I go to play in table2 and in case that the register already is in table2,
necessary to make update and if not, insert. As I make this implementation in “if” and as I make the declaration of table2.
It follows what I have mounted…
AS
cod_erro NUMBER;
message_erro VARCHAR2(255);
CURSOR C1 IS
SELECT emp, codigo, descricao FROM table1 FOR UPDATE;
REGISTRO C1%ROWTYPE;

BEGIN

OPEN C1;
LOOP
FETCH C1 INTO REGISTRO;
EXIT WHEN C1%NOTFOUND;
if ??????? (if table1.emp=table2.emp then to make update, if not it insert)
then
update table2 set cod=REGISTRO.cod,descricao=REGISTRO.descricao) ;
else
insert into table2 (emp, codigo, descricao) values (REGISTRO.emp,REGISTRO.cod,REGISTRO.descricao);
end if;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
cod_erro := SQLCODE;
message_erro := SQLERRM;
INSERT INTO CARLOS.ERROS_IMPORT
VALUES (cod_erro, message_erro, sysdate);
update table1 set date=SYSDATE where CURRENT OF c1;
CLOSE C1 ;
END;

thanks

Carl

[anacedent]

I think you'd benefit from using the MERGE statement, since I am not clear exactly what problem you are trying to solve I could be mistaken.

How will an independent observer know when the correct answer is presented?

[Littlefoot]

I've slightly modified your code; see if it can help.

BEGIN
  FOR cur_r IN (SELECT emp, codigo, descricao
                 FROM table1
                )
  LOOP
    BEGIN
      SELECT NULL FROM table2 t2
      WHERE t2.emp = cur_r.emp;
    
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        INSERT INTO table2
          (emp, codigo, descricao)
           VALUES
          (cur_r.emp, cur_r.codigo, cur_r.descricao);
    END;
    
    UPDATE table2 t2 SET
      t2.cod = cur_r.cod,
      t2.descricao = cur_r.descricao
      WHERE t2.emp = cur_r.emp;
      
  END LOOP;
END;