create or replace procedure FILL_EVENTS_BUFFER( DATE_TO_LOAD in VARCHAR2 DEFAULT TO_CHAR(SYSDATE,'DDMMYYYY')) is
select_cursor INTEGER := DBMS_SQL.OPEN_CURSOR;
exec_select1 INTEGER;
load_date DATE;
table1 VARCHAR2(10);
id NUMBER;
event_code VARCHAR2(3);
PASS_CODE VARCHAR2(20);
ID_TURN NUMBER;
TIME_OF_EVENT DATE;
begin
load_date:=TO_DATE(date_to_load||' '||'02:00','ddmmyyyy hh24:mi');
-- Название таблицы
table1:='T'||DATE_TO_LOAD;
-- Выбираем данные поле двух часов ночи
DBMS_SQL.PARSE(select_cursor,
'select event,z_time,tur,pr_code,id_p from ADMIN.'||table1||'
where TO_CHAR(z_time,''hh24:mi'')>''02:00''',DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN (select_cursor, 1, event_code,1);
DBMS_SQL.DEFINE_COLUMN (select_cursor, 2, time_of_event);
DBMS_SQL.DEFINE_COLUMN (select_cursor, 3, pass_code,16);
DBMS_SQL.DEFINE_COLUMN (select_cursor,4,id);
exec_select1 := DBMS_SQL.EXECUTE (select_cursor);
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS (select_cursor) = 0;
DBMS_SQL.COLUMN_VALUE (select_cursor, 1, event_code);
DBMS_SQL.COLUMN_VALUE (select_cursor, 2, time_of_event);
DBMS_SQL.COLUMN_VALUE (select_cursor, 3, pass_code);
DBMS_SQL.COLUMN_VALUE (select_cursor,4,id);
dbms_output.put_line(id||' '||event_code||' '||time_of_event||' '||pass_code);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (select_cursor);
end FILL_EVENTS_BUFFER;
|