Procedure to insert in unique in values into tables not working

Question

I'm a beginner in this language and i'm being asked to insert via procedure some unique variables into the tables aeropuerto and terminal (the variables have to be unique compared to the rest in the tables) and after thinking, this is as far as i have come. Do you have any suggestions on how to edit the code or how i can insert with simple instructions? (we havent come that far in the course yet :) )

create or replace PROCEDURE EX3 (
    IDAE IN NUMBER, 
    NOMA IN VARCHAR2, 
    PLAC IN VARCHAR2, 
    IDTER IN NUMBER, 
    NUMT IN NUMBER)
AS 
BEGIN
  
    IF IDAE!=AEROPUERTO.ID AND NOMA!=AEROPUERTO.NOMBRE AND PLAC!=AEROPUERTO.LUGAR THEN
            INSERT INTO AEROPUERTO(ID, NOMBRE, LUGAR) 
            VALUES (ID_AAUX, NOMA, PLAC);
    END IF;
    IF IDTER!=TERMINAL.ID AND NUMT!=TERMINAL.NUMERO AND IDAE!=TERMINAL.ID_AEROPUERTO THEN
            INSERT INTO TERMINAL(ID, NUMERO, ID_AEROPUERTO) 
            VALUES (ID_TAUX, NUMT, IDAE);
    END IF;
END EX3;

What are `ID_AAUX` and `ID_TAUX` in the `values` clauses of your two `insert` statements? I would expect those would need to be either local variables or parameters to the procedure but they don't appear to be declared anywhere. Is `ID_AAUX` supposed to be `IDAE` and `ID_TAUX` is supposed to be `IDTER`? That would sort of make sense given the attempt at the `IF` statements on the prior lines. — Justin Cave, May 11 '21 at 23:41
@JustinCave they are the residuals of an attept to use select into using those ids which value was equal to idae and idter respectively sorry for the inconvenience — Wazabi, May 11 '21 at 23:43

Justin Cave · Accepted Answer · 2021-05-12T00:00:12.373

The simplest approach would probably to use a NOT EXISTS clause in your INSERT statement.

create or replace PROCEDURE EX3 (
  IDAE IN NUMBER, 
  NOMA IN VARCHAR2, 
  PLAC IN VARCHAR2, 
  IDTER IN NUMBER, 
  NUMT IN NUMBER )
AS 
BEGIN
  INSERT INTO AEROPUERTO(ID, NOMBRE, LUGAR) 
    SELECT idae, noma, plac
      FROM dual
     WHERE NOT EXISTS( SELECT 1
                         FROM AEROPUERTO a
                        WHERE a.id = idae
                          AND a.nombre = noma
                          AND a.lugar = plac );

  INSERT INTO TERMINAL(ID, NUMERO, ID_AEROPUERTO) 
    SELECT idter, numt, idae
      FROM dual
     WHERE NOT EXISTS( SELECT 1
                         FROM terminal t
                        WHERE t.id = idter
                          AND t.numero = numt
                          AND t.id_aeropuerto = idae );
END EX3;

Depending on how common it would be to actually get duplicate values, however, and assuming there is a unique constraint in place, it may be more efficient to just try the insert and catch the exception

create or replace PROCEDURE EX3 (
  IDAE IN NUMBER, 
  NOMA IN VARCHAR2, 
  PLAC IN VARCHAR2, 
  IDTER IN NUMBER, 
  NUMT IN NUMBER )
AS 
BEGIN
  BEGIN
    INSERT INTO AEROPUERTO(ID, NOMBRE, LUGAR) 
      VALUES( idae, noma, plac );
  EXCEPTION
    WHEN dup_val_on_index
    THEN
      null;  -- Ignore the error if the row already exists
  END;

  BEGIN
    INSERT INTO TERMINAL(ID, NUMERO, ID_AEROPUERTO) 
      VALUES( idter, numt, idae );
  EXCEPTION
    WHEN dup_val_on_index
    THEN
      null;  -- Ignore the error if the row already exists
  END;
END EX3;

As an aside, your life will generally be easier if you use a naming convention for parameters and local variables that ensures that it is clear which column they reference but which can't conflict with the name of an actual column. And your life will be easier if you use anchored types to make it extra clear which columns particular parameters are supposed to match up with.

If your procedure was declared

create or replace PROCEDURE EX3 (
  p_id_aeropuerto IN aeropuerto.id%type, 
  p_nombre        IN aeropuerto.nombre%type, 
  p_lugar         IN aeropuerto.lugar%type, 
  p_id_terminal   IN terminal.id%type,
  p_numero        IN terminal.numero%type )

it would likely be easier to read and it would be clearer which parameters map to which table. It will also mean less maintenance since when someone decides they need to increase the length of a column, you don't have to adjust your PL/SQL code (assuming you always use anchored types).

I would also tend to suggest using id_aeropuerto and id_terminal in the definition of the aeropuerto and terminal tables rather than having generic id columns in every table. That's more a matter of style but I always find it easier to read code where you're joining on the same column name and where columns with the same name mean the same thing. Having the same id column in a bunch of tables that each mean something different makes me spend more time looking at the code to make sure that it's not joining to the wrong identifier.

Procedure to insert in unique in values into tables not working

1 Answers1