9

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?

BLaZuRE
  • 2,356
  • 2
  • 26
  • 43

3 Answers3

8

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.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • My example table has 6 columns, 3 of which were selected. Your answer gave me another idea though, which I'll post separately. I feel like there could be a better way. – BLaZuRE Aug 29 '13 at 01:44
1

You can use a cursor, for doing this. That way you dont have to worry about TOO_MANY_ROWS or NO_DATA_FOUND exception.

And also you will have the flexibility of each time you add a column to your query it automatically gets added to your variable of the same type

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

Option #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 #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;
Laggel
  • 1,356
  • 3
  • 19
  • 36
0

This is what I currently have, based off of 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 that I've come up with. It allows me to select certain columns while doing a join and storing all of the values into variables. Not elegant, but seems to satisfy the problem. Other answers are still welcome.

BLaZuRE
  • 2,356
  • 2
  • 26
  • 43
  • TonyAndrews' answer is a pretty much as elegant as you get with select into and IMO that's a quite elegant (YMMV). Other option is to use an explicit cursor that gives you more control but being more verbose. The relevant Oracle doc is: [Query Result Set Processing](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#i45320). – user272735 Aug 29 '13 at 04:26