sql - 如何自动显示匿名PL/SQL block 内所有SQL语句的输出

Question

我们的数据迁移脚本使用匿名 PL/SQL 块来帮助整理代码，主要是我们可以将创建和更新的用户 ID 列设置为“系统”用户。

我们的迁移脚本类似于:

DECLARE
    v_user_id users.id%TYPE;
BEGIN
    SELECT id INTO v_user_id FROM users WHERE username = 'system';

    UPDATE table1
    SET col1 = value,
        updated_at = SYSDATE,
        updated_by = v_user_id
    WHERE some condition;

    INSERT INTO table2 (val1, SYSDATE, v_user_id);
END;
/

更新记录的用户是我们用户表中的数字 ID，而不是字符串用户名。这是我们数据建模团队的要求，否则我只会硬编码我们“系统”帐户的用户名。

作为附加说明，我们的 DBA 运行脚本，他不应该显示为更新或插入记录的人/用户。来自更大企业环境的另一个要求。

我希望从 sqlplus 命令行看到的输出类似于:

Updated X rows
Inserted Y rows

就像您在 PL/SQL 块之外运行了 INSERT 和 UPDATE 语句一样。

而且我真的希望有一个不需要显式调用 DBMS_OUTPUT.PUT_LINE 的解决方案。 .

如何在不显式调用 DBMS_OUTPUT.PUT_LINE 的情况下自动显示匿名 PL/SQL 块中每个 DML 语句的输出每个语句？

Answer 1

复合触发器可以统计并显示修改的行数，而无需更改原始代码。

这里仍然存在一些问题和挑战。此解决方案可能不适用于并行 DML - 它要么无法正确计数，要么触发器将阻止直接路径写入。它可能会在多用户环境中工作，但这需要进行测试。您还需要为 DELETE 和 MERGE 构建代码。这可能会减慢 DML。

示例架构

create table users(id number, username varchar2(100));
insert into users values(1, 'system');

create table table1(col1 number, updated_at date, updated_by number);
insert into table1 values(1, null, null);
insert into table1 values(2, null, null);

create table table2(col1 number, updated_at date, updated_by number);

包防止过多的 DBMS_OUTPUT

不断打印输出可能会导致问题。所以我们想默认禁用输出。而且您可能不想简单地使用 DBMS_OUTPUT.DISABLE ，这可能会关闭其他东西，而且很难记住要运行它。

创建一个带有全局变量的简单包。

create or replace package print_feedback is
    --Outputing large amounts of data can sometimes break things.
    --Only enable DBMS_OUTPUT when explicitly requested.
    g_print_output boolean := false;
end;
/

将其设置为 TRUE在运行导入之前。

--Run this block first to enable printing.
begin
    print_feedback.g_print_output := true;
end;
/

PL/SQL 块来创建 INSERT 和 UPDATE 触发器

此代码动态生成触发器以捕获 INSERT 和 UPDATE。

动态 PL/SQL 有点棘手。请注意，我使用模板和替代引用机制来避免串联 hell 。一旦你理解了这些技巧，代码就会变得相对可读。 (希望您的 IDE 理解 q'[ 如何比 StackOverflow 语法荧光笔更好地工作。)

--Create automatic UPDATE and INSERT feedback triggers.
declare
    c_sql_template constant varchar2(32767) :=
    q'[
create or replace trigger #TABLE_NAME#_#UPD_or_INS#_trg for #UPDATE_OR_INSERT# on #TABLE_NAME# compound trigger

--Purpose: Print a feedback message after updates and inserts.
g_count number := 0;

after each row is
begin
    g_count := g_count + 1;
end after each row;

after statement is
begin
    if print_feedback.g_print_output then
        if g_count = 1 then
            dbms_output.put_line('#Inserted_or_Updated# '||g_count||' row in #TABLE_NAME#');
        else
            dbms_output.put_line('#Inserted_or_Updated# '||g_count||' rows in #TABLE_NAME#');
        end if;
    end if;
end after statement;

end;
    ]';
    v_sql varchar2(32767);
begin
    --Loop through the relevant tables
    for tables in
    (
        select table_name
        from user_tables
        where table_name in ('TABLE1', 'TABLE2')
        order by table_name
    ) loop
        --Create and execute update trigger.
        v_sql := replace(replace(replace(replace(c_sql_template
            , '#TABLE_NAME#', tables.table_name)
            , '#UPD_or_INS#', 'upd')
            , '#UPDATE_OR_INSERT#', 'update')
            , '#Inserted_or_Updated#', 'Updated');
        execute immediate v_sql;
        --Create and execute insert trigger.
        v_sql := replace(replace(replace(replace(c_sql_template
            , '#TABLE_NAME#', tables.table_name)
            , '#UPD_or_INS#', 'ins')
            , '#UPDATE_OR_INSERT#', 'insert')
            , '#Inserted_or_Updated#', 'Inserted');
        execute immediate v_sql;
    end loop;
end;
/

sample 运行

现在您未更改的脚本将显示一些输出。 (我确实对脚本进行了一些微不足道的更改，但只是为了使其可运行。)

SQL>    --Run this block first to enable printing.
SQL>    set serveroutput on;
SQL>    begin
  2             print_feedback.g_print_output := true;
  3     end;
  4     /

PL/SQL procedure successfully completed.

SQL> DECLARE
  2      v_user_id users.id%TYPE;
  3  BEGIN
  4      SELECT id INTO v_user_id FROM users WHERE username = 'system';
  5
  6      UPDATE table1
  7      SET col1 = 1,--value,
  8          updated_at = SYSDATE,
  9          updated_by = v_user_id
 10      WHERE 1=1;--some condition;
 11
 12      INSERT INTO table2 values(2/*val1*/, SYSDATE, v_user_id);
 13  END;
 14  /
Updated 2 rows in TABLE1
Inserted 1 row in TABLE2

PL/SQL procedure successfully completed.

SQL>