How to use SELECT... INTO with a JOIN?

Question

I have the following example code

DECLARE 
    myRow table%rowtype 
    myVar table2.column%type
BEGIN 
    SELECT table.col1, table.col3, table.col4, table2.column
    INTO myRow 
    FROM table
    JOIN table2
    On table.col6 = table2.col1;
 END;

How can I refactor so that it is a valid statement? Can I somehow store the joined column onto myRow or myVar?

Answer 1

Your PL/SQL is valid and acceptable provided:

1. Table TABLE contains exactly 4 columns, corresponding to the 4 values you are selecting.
2. The query will return exactly 1 row.

If table TABLE does not contain exactly 4 columns then you need to select into something else, perhaps just 4 variables:

DECLARE 
    v_col1 table.col1%type;
    v_col3 table.col3%type;
    v_col4 table.col4%type;
    v_column table2.column%type;
BEGIN 
    SELECT table.col1, table.col3, table.col4, table2.column
    INTO v_col1, v_col3, v_col4, v_column
    FROM table
    JOIN table2
    On table.col6 = table2.col1;
END;

If your query returns more than 1 row you will get a TOO_MANY_ROWS exception; and if it returns no rows you will get a NO_DATA_FOUND exception.