How to access different columns of a different tables using cursor after a join has been made on a p-CodePudding

So I am having issues in understanding how can one access different column names using a cursor in which a join operation has been made on three different tables over a single common column.

For example

DECLARE
  CURSOR joined_table_cur IS
    SELECT a.col1, a.col2, b.col5, c.col7 ...
    FROM table1 a
    JOIN table2 b ON a.col1 = b.col1 JOIN
    table3 c on b.col1 = c.col1 
;

  joined_table_rec joined_table_cur%ROWTYPE;
BEGIN
  FOR joined_table_rec IN joined_table_cur
  LOOP    
   -- how to access col7 from table3 ie c in this cursor--
  END LOOP;
END;

I am unable to understand how to do this.

CodePudding user response：

To loop through cursor rows in your sample all you should do is to use second type of looping in code below and reference the columns using already declared variable joined_table_rec - for col1 it is joined_table_rec.col1, for col2 joined_table_rec.col2 ... and so on.
If you want to use FOR LOOP then you don't need to declare joined_table_rec variable as the for loop would create the variable itself - just give the name - rec2 in code below. Below is example for two ways how to loop the cursor:

SET SERVEROUTPUT ON
DECLARE
  CURSOR cur IS
    SELECT *
    FROM
    (
          Select 1 "COL_ID", 'Name 1' "COL_NAME", 'Somethong else 1' "COL_ELSE" From Dual Union All
          Select 2 "COL_ID", 'Name 2' "COL_NAME", 'Somethong else 2' "COL_ELSE" From Dual Union All
          Select 3 "COL_ID", 'Name 3' "COL_NAME", 'Somethong else 3' "COL_ELSE" From Dual
    );
    rec cur%ROWTYPE;
    m_sql VarChar2(500);
BEGIN

  FOR rec2 IN cur LOOP
      DBMS_OUTPUT.PUT_LINE(rec2.COL_ID);
  END LOOP;
  

  OPEN cur;
  LOOP   
        FETCH cur Into rec;
        EXIT WHEN cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(rec.COL_NAME);
  END LOOP;
  CLOSE cur;
END;

Result: 
anonymous block completed
1
2
3
Name 1
Name 2
Name 3

More about it here.

CodePudding user response：

This is not an answer. d r has answered your question and explained the two options how to access your cursor.

I just want to add that you don't need an explicit cursor at all. What I usually do is just this:

BEGIN
  FOR rec IN
  (
    SELECT a.col1, a.col2, b.col5, c.col7 ...
    FROM table1 a
    JOIN table2 b ON a.col1 = b.col1
    JOIN table3 c ON b.col1 = c.col1 
  )
  LOOP    
    DBMS_OUTPUT.PUT_LINE('table1.col1 is ' || rec.col1);
    DBMS_OUTPUT.PUT_LINE('table2.col5 is ' || rec.col5);
    ...
  END LOOP;
END;

Consider this a comment to dr's answer. I am merely posting this here as an "answer", because without line breaks it would be hard to read as a comment.