Creating a Temporary TableIt is also possible to create a temporary table. The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table. You use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT keywords indicate if the data in the table is transaction-specific (the default) or session-specific: ON COMMIT DELETE ROWS specifies that the temporary table is transaction specific and Oracle truncates the table (delete all rows) after each commit. ON COMMIT PRESERVE ROWS specifies that the temporary table is session specific and Oracle truncates the table when you terminate the session.This example creates a temporary table that is transaction specific:语句如下:CREATE GLOBAL TEMPORARY TABLE admin_work_area (startdate DATE, enddate DATE, class CHAR(20)) ON COMMIT DELETE ROWS;
create or replace procedure ddd is type idarr is varray(20) of int; rec idarr; tbname varchar2(200); begin tbname:='tempone'; execute immediate 'create global temporary table '||tbname||'(id int) on commit delete rows'; execute immediate 'insert into '||tbname||' values(1)'; execute immediate 'insert into '||tbname||' values(2)';
execute immediate 'select id from '||tbname bulk collect into rec; for i in rec.first..rec.last loop dbms_output.put_line(rec(i)); end loop;
visible to all sessions, but the data in a temporary table is visible only to the session
that inserts the data into the table. You use the CREATE GLOBAL TEMPORARY
TABLE statement to create a temporary table. The ON COMMIT keywords indicate if
the data in the table is transaction-specific (the default) or session-specific: ON COMMIT DELETE ROWS specifies that the temporary table is transaction
specific and Oracle truncates the table (delete all rows) after each commit. ON COMMIT PRESERVE ROWS specifies that the temporary table is session
specific and Oracle truncates the table when you terminate the session.This example creates a temporary table that is transaction specific:语句如下:CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;
type idarr is varray(20) of int;
rec idarr;
tbname varchar2(200);
begin
tbname:='tempone';
execute immediate 'create global temporary table '||tbname||'(id int) on commit delete rows';
execute immediate 'insert into '||tbname||' values(1)';
execute immediate 'insert into '||tbname||' values(2)';
execute immediate 'select id from '||tbname bulk collect into rec;
for i in rec.first..rec.last loop
dbms_output.put_line(rec(i));
end loop;
commit;
end;