In SQL Server, you can declare a table variable (DECLARE @table TABLE), which is produced while the script is run and then removed from memory.
Does Oracle have a similar function? Or am I stuck with CREATE/DROP statements that segment my hard drive?
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
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)
)
);
the table in variable in oracle not the same as table variables in MS SQLServer. in oracle it's like regular array in java or c#. but in MS SQLserver it is the same as any table, you can call it logical table. but if you want something in oracle that does exactly the same as table variable of SQLserver you can use cursor.
regards
You can use Global Temporary Table of Oracle.
create global temporary table TempDemo(
Name varchar2(200),
Address varchar2(200)
)
on commit preserve rows;
insert into TempDemo values('Shahriar','Vatapara Rajshahi, Bangladesh');
insert into TempDemo values('Zainab','Laxmipur Rajshahi, Bangladesh');
insert into TempDemo values('Sujon','Mohisbathan Rajshahi, Bangladesh');
select * from TempDemo;
delete from TempDemo;
drop table TempDemo;
commit;
rollback;
