oracle - PL/SQL存储过程创建表

Question

我的任务是改进旧的 PL/SQL 和 Oracle SQL 遗留代码。总共有大约 7000 行代码!现有代码中真正令我惊讶的一个方面是，以前的编码器通过不编写任何过程或函数而不必要地创建了数百行代码 - 相反，编码器本质上始终重复相同的代码。

例如，在现有代码中，实际上有 40 次或更多次重复以下 SQL:

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.....

尽管我最近购买了 Steven Feuerstein 所著的优秀书籍“Oracle PL/SQL 编程”，但我对 PL/SQL 写作还很陌生。但是，据我所知，我应该能够编写一个可调用的过程，例如:

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 语句来检查第三个参数是否不为 NULL，然后相应地构造 s_query。
4. 如何检查表是否确实已成功创建？

5. 我想捕获另外两个异常，即

   • 新表(例如“tmp.clients”)已存在；和
   • 源表不存在。

   书面的异常是否可以处理这些情况？

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

任何对代码的改进建议将不胜感激。

Answer 1

通过使用 GLOBAL 临时表，您可以(逐渐地!)摆脱大量代码。立即执行并不是一个坏习惯，但如果有其他选项，那么应该使用它们。全局临时表很常见，您想要提取和转换数据，但一旦处理完毕，您就不再需要它，直到下一次加载为止。每个用户只能看到自己插入的数据，不会生成重做日志。您可以index the data如果需要，可以更快地查询。

类似这样的事情

-- 创建表

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;

我建议使用提交时保留行选项，以便您可以调试过程并查看表中的内容。

用法是

INSERT INTO GT_CLIENTS
SELECT * FROM live.clients;