Results 1 to 3 of 3
  1. #1
    Join Date
    May 2007
    Posts
    5

    Unanswered: create procedure

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I've slightly modified your code; see if it can help.
    Code:
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •