How do i use a cursor correctly

With the code below I am trying to search for certain words (SEED WORDS) inside reviews. Then depending on the number of Seed words inside each review I come up with a rating number. There are multiple reviews per hotel ID. I am trying to get this entered into a Ratings table that has the following columns (HOTEL_ID, CATEGORY_ID,RATING). Each Seed Word has a category associated with it 1,2,3 or 4. The error I have received so far says ‘LV_RATE_NUM’ must be declared.

    DECLARE
lv_hotel NUMBER(4,0);
lv_rate_num NUMBER(4);
lv_rate NUMBER(1,1);
lv_pol NUMBER(1,1);
i number(4,0);
CURSOR cur_rate IS
SELECT a.IDHOTEL, INSTR(a.review,b.seed_words), b.polarity
INTO lv_hotel, lv_rate_num,lv_pol
FROM review a, SEEDWORDS b;
BEGIN
i :=1;
    lv_hotel := i;
 FOR rec_hotel IN cur_rate LOOP
     IF rec_hotel.lv_rate_num  > 0
     THEN lv_rate := lv_rate_num;
     ELSIF rec_hotel.lv_rate_num = 0
     THEN lv_rate_num := 8.6;
     i := i+1;
     END IF;
END LOOP;
INSERT INTO RATINGS
VALUES (lv_hotel, 'CATID',lv_rate);
END;

Answer

The INTO clause is only used in a singleton SELECT – that is, a SELECT which only returns one row. Your code can be simplified quite a bit:

DECLARE
  i         NUMBER := 1;
  lv_hotel  NUMBER := 1;
  lv_rate   NUMBER;
BEGIN
  FOR rec_hotel IN (SELECT INSTR(a.review, b.seed_words) AS LV_RATE_NUM
                      FROM review a
                      CROSS JOIN SEEDWORDS b)
  LOOP
     IF rec_hotel.LV_RATE_NUM > 0 THEN
       lv_rate := rec_hotel.LV_RATE_NUM;
     ELSIF rec_hotel.LV_RATE_NUM = 0 THEN
       lv_rate := 8.6;
       i := i+1;
     END IF;
  END LOOP;

  INSERT INTO RATINGS(HOTEL_ID, CATEGORY_ID,RATING)
    VALUES (lv_hotel, 'CATID', lv_rate);
END;

I strongly recommend that you avoid specifying precision and scale on numbers. They are very seldom needed and are a source of potential problems. Note that I’m pretty sure that this still won’t do what you intend, but you can use it as a way to get started.