Oracle Some Examples

Оракл - несколько примеров

dbms_output.put_line и execute immediate

begin

execute immediate 'truncate table ERI_TMP_TEST2';
  for c in (select table_name from all_tables where owner = 'PMMCOUNTER_DB' and table_name like 'ERI%\_W' escape '\') loop

  dbms_output.put_line ('INSERT INTO ERI_TMP_TEST2 select ' || 
'min(PMM_DATESTAMP), '|| 
'max(PMM_DATESTAMP),PMM_DATETIME, '''|| c.table_name || ''' from PMMCOUNTER_DB.' || c.table_name || ' GROUP BY PMM_DATETIME order by 3;');
  
  execute immediate 'INSERT INTO ERI_TMP_TEST2 select ' ||'min(PMM_DATESTAMP), '|| 'max(PMM_DATESTAMP),PMM_DATETIME, '''|| c.table_name || ''' from PMMCOUNTER_DB.' || c.table_name || ' GROUP BY PMM_DATETIME order by 3';


  
  
  end loop;



end;

count alarms

declare
  start_time TIMESTAMP(3):= '2011-05-14 16:00';
  stop_time TIMESTAMP(3):= '2011-05-14 20:00';
  cur_time TIMESTAMP(3);
  next_time TIMESTAMP(3);
  co integer;
  interval_min INTERVAL DAY(3) TO SECOND(3):= '0 00:15:00';
begin
  cur_time:=start_time;
  WHILE cur_time < stop_time LOOP
        SELECT cur_time  + interval_min INTO next_time FROM dual;
        select count(*) into co
--        from history_db.new_hist_main where hism_datetime_up > cur_time and hism_datetime_up < next_time;
        from dynamic_db.new_alarm where alr_datetime_up > cur_time and alr_datetime_up < next_time
        and alr_logic_id like 'NSN%ROB%'
        ;
        --dbms_output.put_line('Time: from '||TO_CHAR(cur_time, 'dd-mm-yyyy hh24:mi') ||' to: '||TO_CHAR(next_time, 'dd-mm-yyyy hh24:mi')||' Count: '|| co);
        dbms_output.put_line(TO_CHAR(cur_time, 'dd-mm-yyyy hh24:mi') ||'-'||TO_CHAR(next_time, 'hh24:mi')||';'|| co);
        cur_time:=next_time;

  END LOOP;
END;