-- Create table
create global temporary table T_SOLVENCY
(
L_BH NUMBER,
VC_BH VARCHAR2(20),
VC_NAME VARCHAR2(200),
VC_PUBLISH_ORGAN VARCHAR2(200)
)
on commit preserve rows;如上,我创建了一个临时表,但是我在存储过程里对这张表写入数据后,过程执行完后,查了一下该临时表,始终为空?注,存储过程百分之一千没有问题.
不知怎么回事? on commit preserve rows;这句话应该就是说明该表的数据是在同一个会话中有效的,为什么在存储过程里执行插不进去??
create global temporary table T_SOLVENCY
(
L_BH NUMBER,
VC_BH VARCHAR2(20),
VC_NAME VARCHAR2(200),
VC_PUBLISH_ORGAN VARCHAR2(200)
)
on commit preserve rows;如上,我创建了一个临时表,但是我在存储过程里对这张表写入数据后,过程执行完后,查了一下该临时表,始终为空?注,存储过程百分之一千没有问题.
不知怎么回事? on commit preserve rows;这句话应该就是说明该表的数据是在同一个会话中有效的,为什么在存储过程里执行插不进去??
是会话级别的
SQL> create global temporary table T
2 (
3 A VARCHAR2(255)
4 )
5 on commit preserve rows;Table createdSQL> insert into t values('a');1 row insertedSQL> commit;Commit completeSQL> select * from t;A
--------------------------------------------------------------------------------
a
------------------------回楼上兄弟, 在commit前,我输出到一个实体临时表里,实体表是有数据的.
(
A VARCHAR2(255)
)
on commit preserve rows;CREATE OR REPLACE procedure ll00003
is
begin
insert into t values('a');
end;
/
------------
test1
begin
ll00003;
end;
select * from t;
---a
test2
begin
ll00003;
end;
commit;
---
a
a
select * from t;
commit或者不commit都是一样可见的.
my_procedure;select * from T_SOLVENCY;
例如
create or replace procedure p_test
as
begin
delete T_SOLVENCY;
insert into T_SOLVENCY values(1,to_char(sysdate,'hh24:mi:ss'),null,null);
commit;
end;
/select * from T_SOLVENCY;
exec p_test
select * from T_SOLVENCY;
我用你的代码测试了一下,确实还是问题照旧, 存储过程执行完后临时表里还是没有记录.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - ProductionSQL> create global temporary table T_SOLVENCY
2 (
3 L_BH NUMBER,
4 VC_BH VARCHAR2(20),
5 VC_NAME VARCHAR2(200),
6 VC_PUBLISH_ORGAN VARCHAR2(200)
7 )
8 on commit preserve rows;表已创建。SQL> create or replace procedure p_test
2 as
3 begin
4 delete T_SOLVENCY;
5 insert into T_SOLVENCY values(1,to_char(sysdate,'hh24:mi:ss'),null,null);
6 commit;
7 end;
8 /过程已创建。SQL>
SQL> select * from T_SOLVENCY;未选定行SQL> exec p_testPL/SQL 过程已成功完成。SQL> select * from T_SOLVENCY; L_BH VC_BH
---------- --------------------
VC_NAME
--------------------------------------------------------------------------------
VC_PUBLISH_ORGAN
--------------------------------------------------------------------------------
1 15:52:50
SQL>