PL/SQL 存储过程创建表格

Question

我被委托改进旧的PL/SQL和Oracle SQL遗留代码。总共大约有7000行代码！现有代码中让我非常惊讶的一个方面是，以前的编码人员通过不编写任何过程或函数来不必要地创建了数百行代码 - 相反，编码人员基本上在整个代码中重复相同的代码。

例如，在现有代码中，以下SQL文字实际上被重复了40次或更多：

CREATE TABLE tmp_clients
AS
    SELECT * FROM live.clients;

CREATE TABLE tmp_customers
AS
    SELECT * FROM live.customers;

CREATE TABLE tmp_suppliers
AS
    SELECT * FROM live.suppliers WHERE type_id = 1;

and many, many more.....

我是一名 PL/SQL 新手，最近购买了 Steven Feuerstein 写的优秀书籍 "Oracle PL/SQL programming"。根据我的理解，我应该能够编写一个可调用的存储过程，例如：

procedure create_temp_table (new_table_nme in varchar(60)
  source_table in varchar(60))
IS
    s_query varchar2(100);
BEGIN
    s_query := 'CREATE TABLE ' + new_table_nme + 'AS SELECT * FROM ' + source_table;
   execute immediate s_query;

EXCEPTION
    WHEN OTHERS THEN
       IF SQLCODE = -955 THEN
           NULL;
       ELSE
           RAISE;
       END IF;
END;

我会简单地调用以下过程：

create_temp_table('tmp.clients', 'live.clients');
create_temp_table('tmp.customers', 'live.customers');

1. 根据所述问题，我的提议是否合理？
2. 过程调用中的数据类型是否合理，例如应该使用varchar2(60)，还是可能强制“source_table”参数为模式中的表名？如果表名超过60个字符会发生什么情况？
3. 我想在需要对数据进行微不足道的限制的情况下传递第三个非必需参数，例如处理“WHERE type_id = 1”的情况。如何修改该过程以包括仅偶尔使用的参数，并如何修改其余代码。我可能会添加某种IF / ELSE语句来检查第三个参数是否不为空，然后相应地构造s_query。
4. 如何检查表是否已成功创建？
5. 我想捕获两个其他异常，即
   • 新表（例如'tmp.clients'）已经存在；和
   • 源表不存在。

   所写的EXCEPTION是否处理这些情况？

6. 更普遍地说，我从哪里可以获取SQL错误代码及其含义？

如有建议改进代码，将不胜感激。

- user2948208

---

只需使用全局临时表。创建表的代码让我想到原始人熟悉SQL Server。 - kevinskio

Answer 1

你可以通过使用全局临时表（逐步）减少大量代码。Execute immediate 不是一个坏的实践，但如果有其他选项，则应该使用它们。全局临时表通常用于在提取和转换数据时，一旦处理完毕，就不再需要它，直到下一次加载。每个用户只能看到他们插入的数据，并且不会生成重做日志。如果需要更快的查询，可以对数据进行索引。类似这样的操作：

-- 创建表

create global temporary table GT_CLIENTS
(
  id                    NUMBER(10) not null,
  Client_id             NUMBER(10) not null,
  modified_by_id        NUMBER(10),
  transaction_id        NUMBER(10),
  local_transaction_id  VARCHAR2(30) not null,
  last_modified_date_tz TIMESTAMP(6) WITH TIME ZONE not null
)
on commit preserve rows;

我建议使用“on commit preserve rows”选项，这样您就可以调试过程并查看插入表中的内容。

用法如下：

INSERT INTO GT_CLIENTS
SELECT * FROM live.clients;

- kevinskio

---

我不确定我理解了。我知道全局临时表可以用来存储复杂查询的结果。然而，如果我只是将现有表从生产环境复制到自己的模式中，我看不出你提出的建议的好处。 - user2948208

除非我误解了您的意思，否则您的解决方案需要编写大量代码来处理每个全局临时表，例如GT_CLIENTS、GT_CUSTOMERS、GT_SUPPLIERS等，这正是我想要避免的事情。 - user2948208

我承认全局临时表有其他好处（可能是更低的内存或存储空间），如果有说服力的理由，我很乐意改变看法，但目前我无法看到你提出的解决方案对我有任何好处，仅仅因为需要编写大量代码。 - user2948208

如果您告诉我们代码的目的，就能提供更深入的答案。从高层次上看，定期删除和创建表不是最佳实践，除非存在某些复杂的业务案例。而且其他不知道意图的用户误用的可能性是无穷无尽的。 - kevinskio

Answer 2

如果这是您想采取的最小更改路线，那么源表不存在的错误代码是-942，您会希望停止，而不是继续，因为您的临时表尚未创建。同样，如果出现对象已存在的错误，只是继续进行将会有问题，因为您没有使用新数据重新加载它 - 创建失败，因此该表仍然具有上次运行的数据。因此，我肯定会再考虑一下您的异常处理程序。

话虽如此，我也赞同这通常不是做事情的最佳方式。在多用户环境中创建和删除对象是灾难性的，并且似乎是资源的愚蠢浪费，当有更合适的选项可用时。

- Michael Broughton

---

感谢您提供代码-942的建议。正如我在上面评论中所说，我非常乐意以“最佳方式”完成任务，但是您并没有真正解释如何做到这一点，除了表示您同意之前的人。也许我对于全局临时表的目的有所误解，因为每个这样的表似乎需要编写大量的代码。此外，虽然确实只需要将表作为输入用于更大的查询，但这并不是真正的多用户环境。 - user2948208

是的，您需要创建全局临时表，但一旦创建完成，您只需像当前的创建语句一样插入数据即可。因此，在它们位于您的模式中并消除了创建/删除对象的开销后，就不需要进行重大的额外编码了。GTTs不会生成重做日志或回滚信息，从而显着减少将数据插入其中的开销。 - Michael Broughton