Table Variables in Oracle PL/SQL?

Question

Hey all,

I've recently started a new position as a developer and I'm having a bit of trouble with PL/SQL. I've used MS SQL for a number of years but I'm finding PL/SQL a bit trickier.

One of the things I used to do when writing functions and stored procedures in MS SQL was to put reoccuring result sets into a table variable so I wouldn't have to requery them throughout my procedure.

Something like this:

declare @badPeople table(recordPointer int)
insert into @badPeople
select BP_Record_Pointer
from People
where BP_Bad = 1

I'm looking at doing something similar in PL/SQL but I'm not quite sure how to get started. Any ideas if this is even possible?

Thanks, Matt

Answer 1

Here is a similar question that might help maybe a dupe

http://stackoverflow.com/questions/670461/does-oracle-have-an-equivalent-of-mssqls-table-variables

Answer 2

In Oracle unlike MS SQL cursors are not considered the devil and something to avoid. So typically in Oracle you will make use of cursors much more often than you would in MS SQL. Of course if you can do it with only SQL and no cursor that is best, but don't be afraid to test and use cursors in Oracle. Get a good book on PL/SQL, this is a good one http://www.amazon.com/Oracle-PL-SQL-Programming-4th/dp/0596009771. The post by Gratzy is another option you can use. So get a good book that you can use to figure out when to use what. PL/SQL is a very rich environment compared to T-SQL. While it has more of a learning curve once you get over the initial curve it's a really easy and power full language. And on top of that it's a lot of fun.

Answer 3

declare
  type t_number is table of number;
  v_numbers t_number;
begin
  select BP_Record_Pointer
    bulk collect into v_numbers
    from People
    where BP_Bad = 1;
end;

This will create a nested table containing the values from your table. It's important to note that the table will go out-of-scope at the end of the anonymous block, so anything you want to do with it needs to be done inside that block.