PL/SQL run time tunning

create or replace
PROCEDURE SP_UPDATE_BPSA_MARGIN_RISK_ADV
AS
var_cusip BPSA_MARGIN_RISK.cusip%type;
var_thirtydayaverage BPSA_MARGIN_RISK.average_daily_volume%type;
var_counter number;
var_max_date VARCHAR2(20);
var_sql varchar2(1000);
BEGIN
  var_counter :=0;
 
    –DBMS_OUTPUT.PUT_LINE(‘Started updating Cusips at ‘ || to_char(SYSDATE,’YYYYMMDD HH:MI:SS’) );
   
    SELECT MAX(PROCESS_DATE)
    INTO var_max_date
    FROM BPSA_MARGIN_RISK;
   
    — Declare cusip cursor
    DECLARE CURSOR CUSIP_CURSOR IS
    SELECT distinct cusip,thirtydayaverage
      FROM vw_daily_average_volume
     WHERE vw_daily_average_volume.cusip in ( select distinct cusip from BPSA_MARGIN_RISK where process_date =var_max_date)
       AND created_ts = (select max(created_ts) from vw_daily_average_volume);
     
    BEGIN

      — Open  Curosor
      OPEN CUSIP_CURSOR;
     
      LOOP
     
          Fetch CUSIP_CURSOR into var_cusip, var_thirtydayaverage;
         
          EXIT when CUSIP_CURSOR%NOTFOUND;
         
          var_sql :=’update BPSA_MARGIN_RISK set average_daily_volume = :1 where process_date = :2 and cusip = :3 and average_daily_volume is null’;
         
          execute immediate var_sql using var_max_date, var_cusip, var_cusip;
         
          /*
          update BPSA_MARGIN_RISK
             set average_daily_volume = var_thirtydayaverage
           where process_date = var_max_date
             and cusip = var_cusip
             and var_cusip is null;
          */
         
          –DBMS_OUTPUT.PUT_LINE(var_counter || ‘. Cusip:’ || ‘ ‘ || var_cusip);
          — var_counter := var_counter + 1;
         
      END LOOP;
   
      COMMIT;
     
      –DBMS_OUTPUT.PUT_LINE(var_counter || ‘Cusips updated by’ || to_char(SYSDATE,’YYYYMMDD HH:MI:SS’) );
   
    CLOSE CUSIP_CURSOR;
   
  END;
 
END SP_UPDATE_BPSA_MARGIN_RISK_ADV;

 

create or replace
PROCEDURE SP_UPDATE_BPSA_MARGIN_RISK_ADV
AS
var_cusip BPSA_MARGIN_RISK.cusip%type;
var_thirtydayaverage BPSA_MARGIN_RISK.average_daily_volume%type;
var_counter number;
var_max_date VARCHAR2(20);
var_sql varchar2(4000);

BEGIN

  var_counter :=0;
      
MERGE into BPSA_MARGIN_RISK  a

USING( SELECT distinct cusip,thirtydayaverage
        FROM vw_daily_average_volume
        WHERE vw_daily_average_volume.cusip in ( select distinct cusip from BPSA_MARGIN_RISK
                                              where process_date =( SELECT MAX(PROCESS_DATE) FROM BPSA_MARGIN_RISK)
                                             )
          AND created_ts = (select max(created_ts) from vw_daily_average_volume)
     )t1 
  ON (
      a.cusip = t1.cusip and
       a.process_date = (SELECT MAX(PROCESS_DATE) FROM BPSA_MARGIN_RISK)
  )
  WHEN matched then
  UPDATE set a.average_daily_volume = NVL(a.average_daily_volume,t1.thirtydayaverage);
      COMMIT;
 
END;

Leave a Reply

Your email address will not be published. Required fields are marked *