在SQL查询中插入log语句

Question

下面的查询将插入两行，即999,666行，Oracle将为您提供2行插入的日志。

  insert into departments(department_id, department_name, manager_id,location_id)

            select 999,'Administration',200,1700 from dual
union all   select 666,'Marketing'     ,201,1800 from dual

如何获得像这样的日志“行999和666已插入”。通过将日志语句放入查询本身..(用户定义的日志) java中的..Like中，我们使用的是相同的log4j。

Answer 1

我认为在不查看跟踪文件的情况下，SQL是不可能实现的。如果要从PL/SQL中逐行插入，则有可能：

declare
  cursor c is   select 999 a, 'Administration' b, 200 c, 1700 d from dual union all
                select 666 a, 'Marketing' b     , 201 c, 1800 d from dual;
begin
for x in c loop
 insert into departments(department_id, department_name, manager_id,location_id) values (x.a, x.b, x.c, x.d);
 dbms_output.put_line(x.a || ' '); --or insert to log table here
end loop;
  dbms_output.put_line('has been inserted');
end;

Answer 2

如果不想修改用于插入的代码，可以在表上定义触发器来记录操作。

例如：

create or replace trigger departmentsAfterInsert
after insert
on departments
for each row
begin
    dbms_output.put_line('Inserted department ' || :new.department_id);
end;

这将记录insert操作，无论您如何做：

SQL> insert into departments(department_id, department_name, manager_id,location_id)
  2              select 999,'Administration',200,1700 from dual
  3  union all   select 666,'Marketing'     ,201,1800 from dual;
Inserted department 999
Inserted department 666

2 rows created.