Does Oracle have an equivalent of SQL Server's table variables?

Solution 1:

Yes.

Declare TABLE TYPE variables in a PL/SQL declare block. Table variables are also known as index-by table or array. The table variable contains one column which must be a scalar or record datatype plus a primary key of type BINARY_INTEGER. Syntax:

DECLARE TYPE type_name IS TABLE OF (column_type | variable%TYPE | table.column%TYPE [NOT NULL] INDEX BY BINARY INTEGER;

-- Then to declare a TABLE variable of this type: variable_name type_name;

-- Assigning values to a TABLE variable: variable_name(n).field_name := 'some text'; -- Where 'n' is the index value

Ref: http://www.iselfschooling.com/syntax/OraclePLSQLSyntax.htm

You might want to also take a look at Global Temporary Tables

Solution 2:

The below solution is the closest from SQL Server I can do today.

Objects:

    CREATE OR REPLACE TYPE T_NUMBERS IS TABLE OF NUMBER;

    CREATE OR REPLACE FUNCTION ACCUMULATE (vNumbers T_NUMBERS)
    RETURN T_NUMBERS
    AS
       vRet T_NUMBERS;
    BEGIN
       SELECT SUM(COLUMN_VALUE)
       BULK COLLECT INTO vRet
       FROM TABLE(CAST(vNumbers AS T_NUMBERS));

       RETURN vRet;
    END;

Queries:

    --Query 1: Fixed number list.
    SELECT *
    FROM TABLE(ACCUMULATE(T_NUMBERS(1, 2, 3, 4, 5)));

    --Query 2: Number list from query.
    WITH cteNumbers AS
    (
      SELECT 1 AS COLUMN_VALUE FROM DUAL UNION
      SELECT 2 AS COLUMN_VALUE FROM DUAL UNION
      SELECT 3 AS COLUMN_VALUE FROM DUAL UNION
      SELECT 4 AS COLUMN_VALUE FROM DUAL UNION
      SELECT 5 AS COLUMN_VALUE FROM DUAL
    )
    SELECT *
    FROM TABLE(
            ACCUMULATE(
              (SELECT CAST(COLLECT(COLUMN_VALUE) AS T_NUMBERS)
               FROM cteNumbers)
            )
          );

Solution 3:

Yes it does have a type that can hold the result set of a query (if I can guess what TABLE does). From ask Tom: your procedure may look like this:

procedure p( p_state in varchar2, p_cursor in out ref_cursor_type )
is
begin
    open p_cursor for select * from table where state = P_STATE;
end;

where p_cursor is like a table type. As has been already answered there are plenty of options for storing result sets in Oracle. Generally Oracle PL/SQL is far more powerful than sqlserver scripts.