1、如何建立临时表？把语句写在str里面然后execute immediate str?
2、临时表有自增字段怎么写？可不可以不要sequence
在sql server是这么写
create table #result_table (id int identity(1,1),popno varchar(11) collate chinese_prc_ci_as,version int,approvestatus int)
3、返回结果集oracle和sql server相比有什么要注意的地方吗？
6 个解决方案
#1
换个思路吧，oracle中最好不要在存储过程中创建再删除临时表。
#2
创建临时表会造成大量的历史表吧。还得定期purge一下。
不太了解oracle的临时表机制。SQL Server的临时表倒是自动回收的。
mark.
不太了解oracle的临时表机制。SQL Server的临时表倒是自动回收的。
mark.
#3
可是我是想改sql server的存储过程到oracle，所以就想按原样改
临时表也同时会在存储过程删掉的
临时表也同时会在存储过程删掉的
#4
在ORACLE里面最好不要在PROCEDURE里面创建临时表,这样既麻烦又影响性能,
建议先建临时表,然后在PROCEDURE里面直接调用.
ORACLE临时表有两种机制,ON DELETE COMMIT和ON PRESERVE COMMIT.你GOOGLE一下子.
建议先建临时表,然后在PROCEDURE里面直接调用.
ORACLE临时表有两种机制,ON DELETE COMMIT和ON PRESERVE COMMIT.你GOOGLE一下子.
#5
1、如何建立临时表？把语句写在str里面然后execute immediate str?
>>
Execute Immediate 'create global temporary table TEMP( aa VARCHAR(30),bb NUMBER )On Commit PRESERVE Rows';
2、临时表有自增字段怎么写？可不可以不要sequence
在sql server是这么写
create table #result_table (id int identity(1,1),popno varchar(11) collate chinese_prc_ci_as,version int,approvestatus int)
>>
orcle中没有类似sqlserver的自增字段，你可以用rownum代替
3、返回结果集oracle和sql server相比有什么要注意的地方吗？
>>
需要定义一个动态游标
create or replace package aa
is
type cur is ref cursor;
procedure getaa( rst out cur );
end;
create or replace package body aa
is
procedure getaa( rst out cur )
is
begin
open rst for select * from table1;
end;
end;
>>
Execute Immediate 'create global temporary table TEMP( aa VARCHAR(30),bb NUMBER )On Commit PRESERVE Rows';
2、临时表有自增字段怎么写？可不可以不要sequence
在sql server是这么写
create table #result_table (id int identity(1,1),popno varchar(11) collate chinese_prc_ci_as,version int,approvestatus int)
>>
orcle中没有类似sqlserver的自增字段，你可以用rownum代替
3、返回结果集oracle和sql server相比有什么要注意的地方吗？
>>
需要定义一个动态游标
create or replace package aa
is
type cur is ref cursor;
procedure getaa( rst out cur );
end;
create or replace package body aa
is
procedure getaa( rst out cur )
is
begin
open rst for select * from table1;
end;
end;
#6
GLOBAL TEMPORARY specifies that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table.
A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords
Restrictions:
Temporary tables cannot be partitioned, index-organized, or clustered.
You cannot specify any referential integrity (foreign key) constraints on temporary tables.
Temporary tables cannot contain columns of nested table or varray type.
You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, LOGGING or NOLOGGING, MONITORING or NOMONITORING, or LOB_index_clause.
Parallel DML and parallel queries are not supported for temporary tables. (Parallel hints are ignored. Specification of the parallel_clause returns an error.)
You cannot specify the segment_attributes_clause, nested_table_storage_clause, or parallel_clause.
Distributed transactions are not supported for temporary tables.
A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords
Restrictions:
Temporary tables cannot be partitioned, index-organized, or clustered.
You cannot specify any referential integrity (foreign key) constraints on temporary tables.
Temporary tables cannot contain columns of nested table or varray type.
You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, LOGGING or NOLOGGING, MONITORING or NOMONITORING, or LOB_index_clause.
Parallel DML and parallel queries are not supported for temporary tables. (Parallel hints are ignored. Specification of the parallel_clause returns an error.)
You cannot specify the segment_attributes_clause, nested_table_storage_clause, or parallel_clause.
Distributed transactions are not supported for temporary tables.
#1
换个思路吧，oracle中最好不要在存储过程中创建再删除临时表。
#2
创建临时表会造成大量的历史表吧。还得定期purge一下。
不太了解oracle的临时表机制。SQL Server的临时表倒是自动回收的。
mark.
不太了解oracle的临时表机制。SQL Server的临时表倒是自动回收的。
mark.
#3
可是我是想改sql server的存储过程到oracle，所以就想按原样改
临时表也同时会在存储过程删掉的
临时表也同时会在存储过程删掉的
#4
在ORACLE里面最好不要在PROCEDURE里面创建临时表,这样既麻烦又影响性能,
建议先建临时表,然后在PROCEDURE里面直接调用.
ORACLE临时表有两种机制,ON DELETE COMMIT和ON PRESERVE COMMIT.你GOOGLE一下子.
建议先建临时表,然后在PROCEDURE里面直接调用.
ORACLE临时表有两种机制,ON DELETE COMMIT和ON PRESERVE COMMIT.你GOOGLE一下子.
#5
1、如何建立临时表？把语句写在str里面然后execute immediate str?
>>
Execute Immediate 'create global temporary table TEMP( aa VARCHAR(30),bb NUMBER )On Commit PRESERVE Rows';
2、临时表有自增字段怎么写？可不可以不要sequence
在sql server是这么写
create table #result_table (id int identity(1,1),popno varchar(11) collate chinese_prc_ci_as,version int,approvestatus int)
>>
orcle中没有类似sqlserver的自增字段，你可以用rownum代替
3、返回结果集oracle和sql server相比有什么要注意的地方吗？
>>
需要定义一个动态游标
create or replace package aa
is
type cur is ref cursor;
procedure getaa( rst out cur );
end;
create or replace package body aa
is
procedure getaa( rst out cur )
is
begin
open rst for select * from table1;
end;
end;
>>
Execute Immediate 'create global temporary table TEMP( aa VARCHAR(30),bb NUMBER )On Commit PRESERVE Rows';
2、临时表有自增字段怎么写？可不可以不要sequence
在sql server是这么写
create table #result_table (id int identity(1,1),popno varchar(11) collate chinese_prc_ci_as,version int,approvestatus int)
>>
orcle中没有类似sqlserver的自增字段，你可以用rownum代替
3、返回结果集oracle和sql server相比有什么要注意的地方吗？
>>
需要定义一个动态游标
create or replace package aa
is
type cur is ref cursor;
procedure getaa( rst out cur );
end;
create or replace package body aa
is
procedure getaa( rst out cur )
is
begin
open rst for select * from table1;
end;
end;
#6
GLOBAL TEMPORARY specifies that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table.
A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords
Restrictions:
Temporary tables cannot be partitioned, index-organized, or clustered.
You cannot specify any referential integrity (foreign key) constraints on temporary tables.
Temporary tables cannot contain columns of nested table or varray type.
You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, LOGGING or NOLOGGING, MONITORING or NOMONITORING, or LOB_index_clause.
Parallel DML and parallel queries are not supported for temporary tables. (Parallel hints are ignored. Specification of the parallel_clause returns an error.)
You cannot specify the segment_attributes_clause, nested_table_storage_clause, or parallel_clause.
Distributed transactions are not supported for temporary tables.
A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords
Restrictions:
Temporary tables cannot be partitioned, index-organized, or clustered.
You cannot specify any referential integrity (foreign key) constraints on temporary tables.
Temporary tables cannot contain columns of nested table or varray type.
You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, LOGGING or NOLOGGING, MONITORING or NOMONITORING, or LOB_index_clause.
Parallel DML and parallel queries are not supported for temporary tables. (Parallel hints are ignored. Specification of the parallel_clause returns an error.)
You cannot specify the segment_attributes_clause, nested_table_storage_clause, or parallel_clause.
Distributed transactions are not supported for temporary tables.