如何从临时表中存储和检索数据

Question

我是PL-SQL的新手。谁能帮我把下面的东西包装一下?

I need to:

• 复制表A的内容并存储到全局临时表
• 对表A进行更新&其他任务
• 从全局临时表中恢复表A的内容
删除临时表

非常感谢您的帮助。

Answer 1

给你:

示例表:

SQL> create table test as select * from dept;
Table created.
SQL> create global temporary table gtt_test as
2  select * from test where 1 = 2;
Table created.
SQL> select * from test;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
1 Dept 1         NY
2 Dept 2         London
6 rows selected.
SQL> select * from gtt_test;
no rows selected

匿名PL/SQL块:

SQL> begin
2    -- copy contents ...
3    insert into gtt_test select * from test;
4
5    -- update A
6    update test set loc = 'blabla';
7    delete from test where deptno = 20;
8
9    -- revert
10    delete from test;
11    insert into test select * from gtt_test;
12
13    -- delete temp
14    delete from gtt_test;
15  end;
16  /
PL/SQL procedure successfully completed.

最后，没有任何变化:

SQL> select * from test;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
1 Dept 1         NY
2 Dept 2         London
6 rows selected.
SQL> select * from gtt_test;
no rows selected
SQL>

---

[编辑]

您作为评论发布的查询不正确;动态SQL必须用单引号括起来，它不能以分号结束，而所有其他语句都必须这样。修正，它应该是

BEGIN
EXECUTE IMMEDIATE   'create table camel_route_temp as '
|| ' select * from acquire.CAMEL_ROUTE_PROCESS';
DBMS_OUTPUT.put_line ('Temp table created');
--update A
UPDATE acquire.camel_route_process
SET acquire = 'DISABLED',
assure = 'DISABLED',
validation = 'DISABLED',
report = 'DISABLED',
notification = 'DISABLED';
COMMIT;
DBMS_OUTPUT.put_line ('Aquire components disabled');
END;
/