CODEWITHSUNDEEP
sqloracle-databaseplsql
mre
mre

Reputation: 137

Select several rows into several variables - Oracle PL/SQL

I have one select statement that returns a query like this:

SELECT value FROM dummy WHERE condition = 1;

    ROW | value
   -----|-----
    1   | val1
    2   | val2
    3   | val3
    4   | val4

I have 4 variables

r1, r2, r3, r4

What is the cleanest way to assign those variables the values of my query? Can I use a SELECT INTO statement?

Upvotes: 0

Views: 489

Answers (2)

Popeye
Popeye

Reputation: 35900

You can uae PIVOT as following;

Select v1, v2, v3, v4 
       Into val1, val2, val3, val4
From (SELECT value FROM dummy WHERE condition = 1)
Pivot
(Max(value) for row in (1 as v1, 2 as v2, 3 as v3, 4 as v4))

Cheers!!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

One method is:

SELECT p_v1 = MAX(CASE WHEN row = 1 THEN value end),
       p_v2 = MAX(CASE WHEN row = 2 THEN value end),
       p_v3 = MAX(CASE WHEN row = 3 THEN value end),
       p_v4 = MAX(CASE WHEN row = 4 THEN value end)
FROM dummy
WHERE condition = 1;

This is an aggregation query that returns one row. If a particular row is missing, then the value will be NULL.

Upvotes: 2

Related Questions