oracle 通用删除记录前备份数据

 1.数据记录表

-- Create table
create table T_YW_COLUMN_VAL
(
  id          VARCHAR2(32),
  pcid        VARCHAR2(32),
  table_name  VARCHAR2(64),
  column_no   NUMBER(8),
  column_name VARCHAR2(64),
  column_val  VARCHAR2(4000),
  jlid        VARCHAR2(50),
  bz          VARCHAR2(2000),
  create_time DATE,
  data_type   VARCHAR2(124)
)
tablespace ZOUJC
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 16
    next 8
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table T_YW_COLUMN_VAL
  is '运维表记录';
-- Add comments to the columns 
comment on column T_YW_COLUMN_VAL.pcid
  is '运维批次id';
comment on column T_YW_COLUMN_VAL.table_name
  is '表名';
comment on column T_YW_COLUMN_VAL.column_no
  is '表字段序号';
comment on column T_YW_COLUMN_VAL.column_name
  is '表字段名称';
comment on column T_YW_COLUMN_VAL.column_val
  is '表字段值';
comment on column T_YW_COLUMN_VAL.jlid
  is '数据记录id';
comment on column T_YW_COLUMN_VAL.bz
  is '备注';
comment on column T_YW_COLUMN_VAL.create_time
  is '创建时间';
comment on column T_YW_COLUMN_VAL.data_type
  is '数据类型';

  2.创建循环序列 

    -- 创建循环序列
    -- Create sequence
    create sequence SEQ_NEXTID_MAX999
    minvalue 1
    maxvalue 999
    start with 61
    increment by 1
    cache 20
    cycle;

    -- Create sequence
    create sequence SEQ_NEXTID_MAX9999
    minvalue 1
    maxvalue 9999
    start with 61
    increment by 1
    cache 20
    cycle;

 3. 生成id 20-50位（时间格式化：yyyymmddhh24missff3）

create or replace function func_nextid(p_length number:=20)
    return varchar2 is
    /*
    生成id 20-50位（时间格式化：yyyymmddhh24missff3）因为性能原因，1毫秒内只能生成10-600个，达不到1000个，所以不会冲突
    -- 创建循环序列
    -- Create sequence
    create sequence SEQ_NEXTID_MAX999
    minvalue 1
    maxvalue 999
    start with 61
    increment by 1
    cache 20
    cycle;
    -- Create sequence
    create sequence SEQ_NEXTID_MAX9999
    minvalue 1
    maxvalue 9999
    start with 61
    increment by 1
    cache 20
    cycle;
    */
    v_sequence varchar2(50);
begin
    if p_length <21 then
        select to_char(systimestamp, 'yyyymmddhh24missff3')--17位时间格式化（毫秒精度）
                   ||to_char(seq_nextid_max999.nextval,'fm000')--|| 三位序列号
        into v_sequence from dual;
        return v_sequence;
    else
        select to_char(systimestamp, 'yyyymmddhh24missff3')--17位时间格式化（毫秒精度）
                   ||to_char(seq_nextid_max9999.nextval,'fm0000')--|| 4位序列号
        into v_sequence from dual;
        if p_length>50 then
            return v_sequence||substr(to_char(DBMS_RANDOM.VALUE()),2,29);--最长50位
        else
            return v_sequence||substr(to_char(DBMS_RANDOM.VALUE()),2,p_length-21);--
        end if;
    end if;
end func_nextid;

 4.运维数据备份

create or replace procedure prc_yw_column_val(p_owner varchar2,p_table_name varchar2,p_id varchar2,p_pcid varchar2:=func_nextid(32),p_bz varchar2:='') is
/***
运维数据备份
p_owner 库名
p_table_name 表名 
p_id  记录id
p_pcid 运维批次id
p_bz 运维备注
*/
cursor c_column is select t.owner,t.table_name,t.data_type,lower(t.column_name) column_name,t.column_id rn
,'update t_yw_column_val v
set v.column_val=(select'||

case when t.data_type in('BLOB','CLOB') --大字段截取
then ' dbms_lob.substr(t.'''||t.column_name||'||'''',4000,1)  '
when t.data_type ='DATE' or t.data_type like 'TIMESTAMP%'  --时间转换
then ' to_char(t.'||t.column_name||', ''yyyy-mm-dd hh24:mi:ss'') '
else ' t.'||t.column_name||'||'''' '
end

||'from ZX_XYXM_XKSXYWLCZT t where t.id='''||p_id||''' and rownum=1)
where v.id='  u_val_sql
from all_tab_columns t
where t.table_name= upper(p_table_name)
and t.owner=upper(p_owner) order by t.table_name,t.column_id ;-- 查表字段

v_item_id varchar2(32);
v_date date:=sysdate;
begin
  DBMS_OUTPUT.ENABLE(buffer_size => null);--设置输出缓存不受限制

 -- execute immediate 'delete t_yw_tb_column_json where table_name='''||p_table_name||'''';

  for i in c_column loop
     --  v_sql_tem1 := v_sql_tem1||'|'||'|'||'''"'''||'|'||'|'||''''||i.column_name||''''||'|'||'|'||'''":"'''||'|'||'|'||i.column_name||'|'||'|'||'''",''';
      v_item_id:=func_nextid(32);
      insert into T_YW_COLUMN_VAL (id,pcid,table_name,column_no,data_type,column_name,jlid,bz,create_time)
      select v_item_id id,p_pcid pcid,i.table_name,i.rn column_no,i.data_type,i.column_name,p_id,p_bz,v_date from dual;

      i.u_val_sql:=i.u_val_sql||''''||v_item_id||'''';
      dbms_output.put_line(i.u_val_sql);
      execute immediate i.u_val_sql;

  end loop;

 -- commit; --提交事务

  exception
    when others then
         begin
               dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);/* 异常的详细信息*/
               dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);/* 发生异常时异常点的堆栈信息（含行号）*/
   end;
end;

 5.使用示例

call prc_yw_column_val('zoujc','t_test','20211015161903970885',func_nextid(32),'测试');

select t.* ,t.rowid  from T_YW_COLUMN_VAL t where t.table_name= upper('t_test') order by t.pcid,t.table_name,t.column_no;

select t.pcid,t.table_name,max(t.create_time) create_time
,( 'insert into '||t.table_name|| chr(13)
||'('|| LISTAGG (t.column_name, ',') WITHIN GROUP (order by t.column_no) ||')'||chr(13)
||'select '||
 LISTAGG (case when t.data_type ='DATE' or t.data_type like 'TIMESTAMP%'  
  then 'to_date('''||t.column_val||''',''yyyy-mm-dd hh24:mi:ss'')'
     else  ''''||t.column_val||'''' end ||' as '||t.column_name, ',') WITHIN GROUP (order by t.column_no) 
|| ' from dual;'
) i_s_sql /*数据恢复sql*/
from T_YW_COLUMN_VAL t where t.table_name= upper('t_test') group by t.pcid,t.table_name;