How do i use a cursor correctly

Question

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 1

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.

Answer 2

You are getting the error because you have used into in cursor query. You can give alias to the cursor columns and use that column names in the loop.

DECLARE
lv_hotel NUMBER(4,0);
lv_rate_num NUMBER(4);
lv_rate NUMBER(1,1);
lv_pol NUMBER(1,1);
i number(4,1);
CURSOR cur_rate IS
SELECT a.IDHOTEL, INSTR(a.review,b.seed_words) as rate_num, b.polarity as pol
--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.rate_num  > 0 -- used cursor column alias name 
     THEN lv_rate := lv_rate_num; -- lv_rate_num is not initialized and you are using it here. You must Consider changing the logic
     ELSIF rec_hotel.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;

Your Insert statement is outside the loop so it will insert only one record in the table.

Cheers!!