.
.
.
str_tmp VARCHAR2(1000);
str_Sql VARCHAR2(2000);/*建立临时数据组合表*/
str_tmp := '
CREATE GLOBAL TEMPORARY TABLE tmpbilllist
(
a NUMBER(9),
b VARCHAR2(42),)
ON COMMIT DELETE ROWS';EXECUTE IMMEDIATE str_tmp;str_Sql :='insert into tmpbilllist (SELECT ........'
EXECUTE IMMEDIATE str_Sql;在这地方能不能直接使用临时表tmpbilllist,如何使用?谢谢。EXECUTE IMMEDIATE 'DROP TABLE tmpbilllist';
.
.
.
.
.
str_tmp VARCHAR2(1000);
str_Sql VARCHAR2(2000);/*建立临时数据组合表*/
str_tmp := '
CREATE GLOBAL TEMPORARY TABLE tmpbilllist
(
a NUMBER(9),
b VARCHAR2(42),)
ON COMMIT DELETE ROWS';EXECUTE IMMEDIATE str_tmp;str_Sql :='insert into tmpbilllist (SELECT ........'
EXECUTE IMMEDIATE str_Sql;在这地方能不能直接使用临时表tmpbilllist,如何使用?谢谢。EXECUTE IMMEDIATE 'DROP TABLE tmpbilllist';
.
.
.
1、str_tmp := '
CREATE GLOBAL TEMPORARY TABLE tmpbilllist
(
a NUMBER(9),
b VARCHAR2(42), )
ON COMMIT DELETE ROWS'; 多了个逗号。2、过程中动态创建表,需要给当前用户显示的授权。 grant create table to youruser;
create or replace procedure pro_create is
str_tmp VARCHAR2(1000);
str_Sql VARCHAR2(2000);
begin
str_sql := 'CREATE GLOBAL TEMPORARY TABLE tmpbilllist
(
a NUMBER(9),
b VARCHAR2(42)
)
ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE str_sql;
str_tmp:='insert into tmpbilllist select * from aaa';
execute immediate str_tmp;
end;
谢谢linzhangs 现在碰到的问题是str_tmp:='insert into tmpbilllist select * from aaa';这里的select * from aaa是能够取出数据的,但临时表tmpbilllist中没有数据,是在同一个会话中,调试时也没有错误返回。请问是不是还有别的地方需要注意的。
先建立普通的TABLE, 来确认你的代码正确,然后再使用GLOBAL TEMPORARY TABLE.
降低问题的复杂度.谢谢!
str_tmp:='insert into tmpbilllist select * from aaa;commit;';
加上commit试下,估计你没有commit,所以看不到数据。
create or replace procedure pro_create is
str_tmp VARCHAR2(1000);
str_Sql VARCHAR2(2000);
begin
str_sql := 'CREATE GLOBAL TEMPORARY TABLE tmpbilllist
(
a NUMBER(9),
b VARCHAR2(42)
)
ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE str_sql;
str_tmp:='insert into tmpbilllist select * from aaa';
execute immediate str_tmp;
end;执行一个类似的sql,用完之后再drop掉,我都是这么处理的
str_tmp VARCHAR2(1000);
str_Sql VARCHAR2(2000);
cnt number;
begin
str_sql := 'CREATE GLOBAL TEMPORARY TABLE tmpbilllist
(
a NUMBER(9),
b VARCHAR2(42)
)
ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE str_sql;
str_tmp:='insert into tmpbilllist select * from aaa';
execute immediate str_tmp;
select count(*) into cnt from tmpbilllist;
dbms_output.put_line(cnt);end;此时,看cnt的值
dbms_output.put_line(cnt);这样在Oracle中创建就不会成功啊。