oracle创建存储过程，第一次全量后日增量，并保留历史数据

在oracle数据库中创建表存储过程，表table name bad表中字段: id, time, product cOde,product_name,mer_code,mer_name,di_code,di_name,ord_id,org_nm数据来源表为:fun_name_bad为主表，目标表字段为: id为数据库自动生成，字段time 来源于主表fun_name_bad中字段to_char(create_time-cost_time),字段product cOde 来源于主表fun_name_bad中字段duct_code，字段product_name 来源于通过表b_product_in中字段call_product与主表duct_code关联，其余字段均来源于主表fun_name_bad中mor_code.mor_name,di_code,di_name,ord_id.org_nm并创建设表table name bad的存储过程，且包含数据取数方式:第一次为全量，后取日增量。请问如何编写存储过程，并保留历史数据?

CREATE  PROCEDURE create_table_and_insert_data AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE table_name_bad (
    id NUMBER,
    time VARCHAR2(100),
    product_code VARCHAR2(100),
    product_name VARCHAR2(100),
    mer_code VARCHAR2(100),
    mer_name VARCHAR2(100),
    di_code VARCHAR2(100),
    di_name VARCHAR2(100),
    ord_id VARCHAR2(100),
    org_nm VARCHAR2(100)
  )';

  INSERT INTO table_name_bad (id, time, product_code, product_name, mer_code, mer_name, di_code, di_name, ord_id, org_nm)
  SELECT
    rownum,
    to_char(create_time - cost_time),
    fun_name_bad.duct_code,
    b_product_in.call_product,
    fun_name_bad.mor_code,
    fun_name_bad.mor_name,
    fun_name_bad.di_code,
    fun_name_bad.di_name,
    fun_name_bad.ord_id,
    fun_name_bad.org_nm
  FROM
    fun_name_bad
  LEFT JOIN
    b_product_in ON fun_name_bad.duct_code = b_product_in.duct_code;

  COMMIT;

  INSERT INTO table_name_bad (id, time, product_code, product_name, mer_code, mer_name, di_code, di_name, ord_id, org_nm)
  SELECT
    rownum,
    to_char(create_time - cost_time),
    fun_name_bad.duct_code,
    b_product_in.call_product,
    fun_name_bad.mor_code,
    fun_name_bad.mor_name,
    fun_name_bad.di_code,
    fun_name_bad.di_name,
    fun_name_bad.ord_id,
    fun_name_bad.org_nm
  FROM
    fun_name_bad
  LEFT JOIN
    b_product_in ON fun_name_bad.duct_code = b_product_in.duct_code
  WHERE
    fun_name_bad.create_time > (SELECT MAX(time) FROM table_name_bad);

  COMMIT;
END;
/

我参考GPT写一个,你看下是否对你有所启发。标注了注释
目标表table_name_bad

CREATE TABLE table_name_bad (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  time DATE,
  product_code VARCHAR2(50),
  product_name VARCHAR2(50),
  mer_code VARCHAR2(50),
  mer_name VARCHAR2(50),
  di_code VARCHAR2(50),
  di_name VARCHAR2(50),
  ord_id VARCHAR2(50),
  org_nm VARCHAR2(50)
);

然后，我们可以编写一个存储过程，实现数据的全量导入和日增量导入，以及历史数据的保留。

CREATE OR REPLACE PROCEDURE import_data (p_full_refresh IN BOOLEAN DEFAULT FALSE) AS
  v_last_import_time DATE;
BEGIN
  IF p_full_refresh THEN
    -- 如果是全量导入，先清空目标表
    DELETE FROM table_name_bad;
    -- 设置上次导入时间为最早时间
    v_last_import_time := TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
  ELSE
    -- 如果是增量导入，获取上次导入时间
    SELECT MAX(time) INTO v_last_import_time FROM table_name_bad;
  END IF;
  
  -- 导入新数据
  INSERT INTO table_name_bad (time, product_code, product_name, mer_code, mer_name, di_code, di_name, ord_id, org_nm)
  SELECT 
    TO_CHAR(fb.create_time - fb.cost_time, 'YYYY-MM-DD HH24:MI:SS'),
    fb.duct_code,
    bp.call_product,
    fb.mor_code,
    fb.mor_name,
    fb.di_code,
    fb.di_name,
    fb.ord_id,
    fb.org_nm
  FROM fun_name_bad fb
  LEFT JOIN b_product_in bp ON fb.duct_code = bp.duct_code
  WHERE fb.create_time >= v_last_import_time;
END;

存储过程中，我们添加了一个布尔型参数p_full_refresh，用于控制是否进行全量导入。如果是全量导入，我们先清空目标表，然后将上次导入时间设置为一个早期的时间点；如果是增量导入，我们查询目标表中最新的时间，作为上次导入时间。然后，我们根据上次导入时间查询主表fun_name_bad中的新数据，并将其加入到目标表table_name_bad中。这样，我们就可以实现全量导入和增量导入，并保留历史数据。