sql query to pl sql procedure-CodePudding

this is my requirement .i want fetch the record from one table and store it in another temporary table.i wrote as query.but dont know how to make it as procedure by declaring varibales and so.

Daily new customers data will gets inserted in table.I only want to fetch the customer data who signed attribute_value as 'TOY_GIFT' from last 10 to till today's date. i want to run this as procedure for every 10 days.

CREATE 
OR 
INSERT INTO
   cst_cust_attributes_tmp (ORGANIZATION_ID, CUST_ID, ATTRIBUTE_ID, ATTRIBUTE_SEQ, ATTRIBUTE_VALUE, ACTIVE_FLAG, CREATE_DATE, CREATE_USER, UPDATE_DATE, UPDATE_USER) 
   SELECT
      ORGANIZATION_ID,
      CUST_ID,
      ATTRIBUTE_ID,
      ATTRIBUTE_SEQ,
      ATTRIBUTE_VALUE,
      ACTIVE_FLAG,
      CREATE_DATE,
      CREATE_USER,
      UPDATE_DATE,
      UPDATE_USER 
   FROM
      cst_cust_attributes 
   WHERE
      create_date between to_date(to_char(sysdate - 10, 'DD-MON-YYYY HH:MI:SS AM'), 'DD-MON-YYYY HH:MI:SS AM') and to_date(to_char(sysdate, 'DD-MON-YYYY HH:MI:SS AM'), 'DD-MON-YYYY HH:MI:SS AM') 
      and attribute_value = 'TOY_GIFT' ;

//

Thanks in advance..

CodePudding user response：

You need to create a proc to insert records, and set up a dbms job to execute it every 10 days.

Like, procedure :

create or replace procedure LOAD_CUSTOMERS is
BEGIN
INSERT INTO
   cst_cust_attributes_tmp (ORGANIZATION_ID, CUST_ID, ATTRIBUTE_ID, ATTRIBUTE_SEQ, ATTRIBUTE_VALUE, ACTIVE_FLAG, CREATE_DATE, CREATE_USER, UPDATE_DATE, UPDATE_USER) 
   SELECT
      ORGANIZATION_ID,
      CUST_ID,
      ATTRIBUTE_ID,
      ATTRIBUTE_SEQ,
      ATTRIBUTE_VALUE,
      ACTIVE_FLAG,
      CREATE_DATE,
      CREATE_USER,
      UPDATE_DATE,
      UPDATE_USER 
   FROM
      cst_cust_attributes 
   WHERE
      create_date between to_date(to_char(sysdate - 10, 'DD-MON-YYYY HH:MI:SS AM'), 'DD-MON-YYYY HH:MI:SS AM') and to_date(to_char(sysdate, 'DD-MON-YYYY HH:MI:SS AM'), 'DD-MON-YYYY HH:MI:SS AM') 
      and attribute_value = 'TOY_GIFT' ;
      COMMIT;
END;

DBMS Job:

begin
  sys.dbms_scheduler.create_job(job_name            => 'LOAD_CUSTOMERS_JOB',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'LOAD_CUSTOMERS', -- YOUR PROC NAME
                                start_date          => to_date('05-12-2019 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=Daily;Interval=10',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;