How to use SELECT ... INTO using JOIN?

Question

I have the following code example

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 reorganize so that it is a valid operator? Can I somehow save the merged column to myRow or myVar?

Answer 1

Your PL / SQL is valid and available:

• The TABLE table contains exactly 4 columns corresponding to the 4 values ​​you have selected.
• The query will return exactly 1 row.

If the TABLE table does not contain exactly 4 columns, you need to select something else, maybe only 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 TOO_MANY_ROWS exception; and if it does not return rows, you will get a NO_DATA_FOUND exception.

Answer 2

For this you can use the cursor. Thus, you do not need to worry about the exclusion of TOO_MANY_ROWS or NO_DATA_FOUND.

In addition, you will have the flexibility every time you add a column to your query, it is automatically added to your variable of the same type

You have two options with the cursor: using only the first row returned or using all rows.

Option number 1

 DECLARE CURSOR C_DATA IS SELECT table.col1, -- Column 2 intentionally left out table.col3, table.col4, table2.column --Column from joined table FROM table JOIN table2 On table.col6 = table2.col1; myRow C_DATA%rowtype BEGIN OPEN C_DATA; FETCH c_data INTO myRow; CLOSE C_DATA; -- USE ANYWHERE INSIDE THIS ESCOPE YOUR COLUMNS as myRow.col4. END; 

Option number 2

 DECLARE CURSOR C_DATA IS SELECT table.col1, -- Column 2 intentionally left out table.col3, table.col4, table2.column --Column from joined table FROM table JOIN table2 On table.col6 = table2.col1; BEGIN FOR myRow IN C_DATA LOOP -- USE INSIDE HERE YOUR COLUMNS as myRow.col4. END LOOP; END; 

Answer 3

This is what I currently have based on Tony Andrews.

 DECLARE myRow table%rowtype myVar table2.column%type BEGIN SELECT table.col1, -- Column 2 intentionally left out table.col3, table.col4, table2.column --Column from joined table INTO myRow.col1, myRow.col3, myRow.col4, myVar -- Won't store into myRow, so made a separate variable FROM table JOIN table2 On table.col6 = table2.col1; END; 

This is the best I've come up with. This allows me to select specific columns when performing a join and store all the values ​​in variables. Not elegant, but seems to satisfy this problem. Other answers are still welcome.