CODEWITHSUNDEEP
sqloracle-databaseplsqldmldatabase-trigger
ulmaxy
ulmaxy

Reputation: 870

PL/SQL retrieve multiple rows using SELECT INTO in Oracle

I'm trying to create a trigger, that fires when a new row is being inserted into a table. The trigger should check if that table have any rows, that have a value in a column that equals the value from the same column in the inserting row. Then I need to update rows in the original table that have the same ID as the selected ones. Now I need to make a request to retrieve the data (all the columns), and that's when I get in troubles.

The problem is that I need to declare a variable, that will be used to store result of that request. That variable should have the same type as the table I retrieve data from does, but when I declare it like that (REC_ROAD_INT is my table):

DECLARE REQUEST_RESULT REC_ROAD_INT%TYPE;

I get

%TYPE must be applied to a variable, column, field or attribute, not to "REC_ROAD_INT"

I actually can retrieve data I need, but only in those cases when the table have only 1 row that meets a requirement. Here's a sample:

create or replace TRIGGER INSERT_ROAD_TRIGGER 
BEFORE INSERT ON REC_ROAD_INT
FOR EACH ROW
DECLARE
  ROAD_ID NUMBER;
  ROAD_NAME VARCHAR2(20);
  ROAD_START_KM NUMBER;
  ROAD_END_KM NUMBER;
BEGIN 
    SELECT ID, NAME, START_KM, END_KM INTO ROAD_ID, ROAD_NAME, ROAD_START_KM, ROAD_END_KM
    FROM REC_ROAD_INT
    WHERE (:NEW.START_KM BETWEEN START_KM AND END_KM) OR 
      (:NEW.END_KM BETWEEN START_KM AND END_KM);
    IF INSERTING 
    THEN 
      DBMS_Output.Put_Line('INSERTING');
      DBMS_Output.Put_Line('Inserting road: ' || :NEW.ID || ' ' || :New.NAME || ' ' || :New.START_KM || ' ' || :New.END_KM);
      DBMS_Output.Put_Line('Crossing road: ' || 'ID = ' || ROAD_ID || ', NAME = ' || ROAD_NAME || 
              ', START_KM = ' || ROAD_START_KM || ', END_KM = ' || ROAD_END_KM);
    END IF;
END;

How can I get all the rows? Thanks.

Upvotes: 1

Views: 4323

Answers (1)

Art
Art

Reputation: 5782

If I understand you correctly you are trying to declare a variable of table type. Your problem is that that variable is not %TYPE but %ROWTYPE.

DECLARE
  emp_rec scott.emp%ROWTYPE;
BEGIN
 .....
END;
/

Your second requirement is pretty confusing. Remove where condition to get all rows. You need to be more specific and clear.

Upvotes: 1

Related Questions