Assign variable to subquery result inside a PL/SQL block

Question

Here is a bit of more simplified pseudocode describing what I am trying to do:

DECLARE
  CURSOR CURSOR_A IS
    SELECT FIELD_A1, FIELD_A2
    FROM TABLE_A;
vNAME NVARCHAR2(100) := NULL;
BEGIN
FOR RECORD_A IN CURSOR_A LOOP
  IF (RECORD_A.FIELD_A1 IS NOT NULL) THEN
    vNAME := RECORD_A.FIELD_A1;
  ELSE
    vNAME := (SELECT FIELD_B
                FROM TABLE_B
               WHERE TABLE_B.B2 = RECORD_A.A2)
END LOOP;
END;
/

Am I not allowed to have a SELECT statement inside of a PL/SQL block?

You want SELECT INTO as long as SELECT return a single row — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 2, 2016 at 19:33
Why do you need all this when you can and should write a simple join in your cursor query joining tables a and b and use DECODE() or CASE statement? Then you do not need all this junk in for loop. — Art
– Art, Commented Nov 2, 2016 at 19:39
Yes, it will benefit the performance in some degree. But mostly it benefits the accuracy, coding standard and professionalism. The answer below is technically correct but not the best way to write a code. And this is not only my opinion. Read Tom Kyte books, for example and others... — Art
– Art, Commented Nov 2, 2016 at 20:17

0xdb · Accepted Answer · 2017-12-16 13:12:13Z

6

All you need - one select. Try it:

declare
    name nvarchar2(100) := null;
begin
    for row_ in (
        select field_a1, field_b from table_a left outer join table_b on b2 = a2
    ) loop
        name := coalesce(row_.field_a1, row_.field_b);
        -- do something
    end loop;
end;
/

You could still add ... and field_a1 is null to the on-clause if you have too many rows in both tables and concerned about performance.

edited Dec 16, 2017 at 13:12

answered Nov 2, 2016 at 20:30

0xdb

3,7071 gold badge23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kacper · Accepted Answer · 2016-11-02 19:38:57Z

2

You are allowed to do select but you need to specify variable that will store selected data

DECLARE
  CURSOR CURSOR_A IS
    SELECT FIELD_A1, FIELD_A2
    FROM TABLE_A;
vNAME NVARCHAR2(100) := NULL;
BEGIN
FOR RECORD_A IN CURSOR_A LOOP
  IF (RECORD_A.FIELD_A1 IS NOT NULL) THEN
    vNAME := RECORD_A.FIELD_A1;
  ELSE
        SELECT FIELD_B into vNAME 
                FROM TABLE_B
               WHERE TABLE_B.B2 = RECORD_A.A2;
END LOOP;
END;
/

answered Nov 2, 2016 at 19:38

Kacper

4,8182 gold badges23 silver badges35 bronze badges

Comments

0xdb · Accepted Answer · 2016-11-03 17:12:19Z

0

We required into clause to store a value of select statement inside Begin and end block. Please look into below example highlighted line:

DECLARE
  CURSOR CURSOR_A IS
    SELECT FIELD_A1, FIELD_A2
    FROM TABLE_A;
vNAME NVARCHAR2(100) := NULL;
BEGIN
FOR RECORD_A IN CURSOR_A LOOP
  IF (RECORD_A.FIELD_A1 IS NOT NULL) THEN
    vNAME := RECORD_A.FIELD_A1;
  ELSE
    SELECT FIELD_B into vNAME --**highlighted line**
                FROM TABLE_B
               WHERE TABLE_B.B2 = RECORD_A.A2
END LOOP;
END;

edited Nov 3, 2016 at 17:12

0xdb

3,7071 gold badge23 silver badges39 bronze badges

answered Nov 3, 2016 at 11:32

Ak Sekhar

13 bronze badges

Collectives™ on Stack Overflow

Assign variable to subquery result inside a PL/SQL block

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related