CODEWITHSUNDEEP
oracle-databaseplsql
Joe
Joe

Reputation: 5487

Oracle Table Variables

Using Oracle PL/SQL is there a simple equivalent of the following set of T-SQL statements? It seems that everything I am finding is either hopelessly outdated or populates a table data type with no explanation on how to use the result other than writing values to stdout.

declare @tempSites table (siteid int) 

insert into @tempSites select siteid from site where state = 'TX'

if 10 > (select COUNT(*) from @tempSites)
begin 
    insert into @tempSites select siteid from site where state = 'OK'
end

select * from @tempSites ts inner join site on site.siteId = ts.siteId

As @AlexPoole points out in his comment, this is a fairly contrived example. What I am attempting to do is get all sites that meet a certain set of criteria, and if there are not enough matches, then I am looking to use a different set of criteria.

Upvotes: 0

Views: 1263

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Oracle doesn't have local temporary tables, and global temporary tables don't look appropriate here.

You could use a common table expression (subquery factoring):

with tempSites (siteId) as (
  select siteid
  from site
  where state = 'TX'
  union all
  select siteid
  from site
  where state = 'OK'
  and (select count(*) from site where state = 'TX') < 10
)
select s.*
from tempSites ts
join site s on s.siteid = ts.siteid;

That isn't quite the same thing, but gets all the TX IDs, and only includes the OK ones if the count of TX ones - which has to be repeated - is less than 10. The CTE is then joined back to the original table, which all seems a bit wasteful; you're hitting the same table three times.

You could use a subquery directly in a filter instead:

select *
from site
where state = 'TX'
or (state = 'OK'
  and (select count(*) from site where state = 'TX') < 10);

but again the TX sites have to be retrieved (or at least counted) a second time.

You can do this with a single hit of the table using an inline view (or CTE if you prefer) with an analytic count - which add the count of TX rows to the columns in the actual table, so you'd probably want to exclude that dummy column from the final result set (but using * is bad practice anyway):

select *    -- but list columns, excluding tx_count
from (
  select s.*,
    count(case when state = 'TX' then state end) over (partition by null) as tx_count
  from site s
  where s.state in ('TX', 'OK')
)
where state = 'TX'
or (state = 'OK' and tx_count < 10);

From your description of your research it sounds like what you've been looking at involved PL/SQL code populating a collection, which you could still do, but it's probably overkill unless your real situation is much more complicated.

Upvotes: 1

Related Questions