1

I am playing around and am trying to make something like this work and can't find a way around. I do a join in a sql statement with table alias'.

How do I then access these items in a PLSQL for loop?

SELECT    
    it.item_title title,
    r.item_id id
FROM 
    item it, rental_item r
WHERE 
    it.item_id = r.item_id;

I would like to know if there is a way to access them in a loop like this given that the query above is a cursor or for i in (query)... 

FOR i IN c LOOP
  dbms_output.print_line(i.title||' '||i.id);
END LOOP;

Right now I am using a static cursor and filling a table of records with the data and then manipulating it. However, if this problem can be solved in this fashion it would be much more elegant.

Thanks in advance.

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
VERNSTOKED
  • 107
  • 1
  • 1
  • 10

1 Answers1

3

Here is how I would do it, I don't see any reason why this wouldn't work.

DECLARE
CURSOR c1
IS
SELECT    
    it.item_title title, r.item_id id
FROM 
    item it inner join, rental_item r
    on it.item_id = r.item_id;
BEGIN
FOR rec IN c1 LOOP
   dbms_output.print_line(rec.title||' '||rec.id);
END LOOP;
END;
Ajay2707
  • 5,690
  • 6
  • 40
  • 58
Major Major
  • 2,697
  • 3
  • 27
  • 35
  • Funny, I tried this exact thing earlier using `FOR i IN..` and it didn't work. However, this code works perfectly. Thank you. – VERNSTOKED May 15 '15 at 04:54
  • Also for future reference, you cannot use `AS "title"` as you would in SQL. You can only use `it.item_title title`. – VERNSTOKED May 15 '15 at 04:58
  • always give the best answer with appropriate best knowledge, you can suggest as I edited your answer of join cluase. Joining with comma and where cluase is very old as well as performance impact query. – Ajay2707 May 15 '15 at 05:26
  • I won't argue about it, but it will run the same either way in Oracle. There's no performance difference. http://stackoverflow.com/questions/121631/inner-join-vs-where – Major Major May 15 '15 at 14:12