时间戳

首页 > 解决方案 > 过程执行表

sql - 过程执行表

问题描述

我有这个程序：

create or replace procedure tst
begin
    execute immediate 'create table tb1 select 1 col from dual';
    execute immediate 'create table tb2 select 1 col from dual';

    insert into tb3 select 1 from dual;

    execute immediate 'truncate table tb3';
    --error table not specified
    execute immediate 'create table tb4 select 1 col from ';
    execute immediate 'truncate table tb4';
end;

我需要创建一个表并将程序内部执行的所有操作都输入其中。与此类似的东西：

"txt"                       "dt"                  "name procedure"
procedure started           20.09.2021 15:12:11   tst
table tb1 created           20.09.2021 15:12:11   tst
table tb2 created           20.09.2021 15:12:12   tst
insert into tb3             20.09.2021 15:12:14   tst
truncate tab3               20.09.2021 15:12:16   tst
error table not specified   20.09.2021 15:12:16   tst

谢谢！

标签： sqloracleloggingstored-proceduresplsql

解决方案

您可以创建这样的表

CREATE TABLE log_ops( txt VARCHAR2(400), dt DATE, proc_name VARCHAR2(40) );

和具有嵌套过程的过程以记录相关步骤，例如

CREATE OR REPLACE PROCEDURE tst AS
  v_ddl VARCHAR2(150);
  v_prc VARCHAR2(40) := $$PLSQL_UNIT;
  PROCEDURE pr_ins(i_txt VARCHAR2) IS
     PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
     INSERT INTO log_ops VALUES(i_txt,SYSDATE,v_prc);
     COMMIT;
   END; 
BEGIN
  pr_ins('procedure started');
  v_ddl := 'CREATE TABLE tb1 SELECT 1 col FROM dual';
  EXECUTE IMMEDIATE v_ddl;
  pr_ins('table tb1 created');
  
  v_ddl := 'CREATE TABLE tb2 SELECT 1 col FROM dual';  
  EXECUTE IMMEDIATE v_ddl;
  pr_ins('table tb2 created');

  INSERT INTO tb3 SELECT 1 FROM dual;
  pr_ins('insert into tb3');

  v_ddl := 'TRUNCATE TABLE tb3';  
  EXECUTE IMMEDIATE v_ddl;
  pr_ins('truncate tab3');

  v_ddl := 'CREATE TABLE tb4 SELECT 1 col FROM...';  
  EXECUTE IMMEDIATE v_ddl;
  pr_ins('error table not specified');

  v_ddl := 'TRUNCATE TABLE tb4';  
  EXECUTE IMMEDIATE v_ddl;
  pr_ins('truncate tab4');
 EXCEPTION WHEN others THEN pr_ins('error : '||sqlerrm);  
END;

推荐阅读