У меня есть миллионная строка, подобная этой, с 310 типами, которые имеют разный формат, чтобы получить последовательность, год, месяц и день.
сценарий получит последовательность, год, месяц и день… теперь я хочу, чтобы Pl/Sql получал максимальное и минимальное значение числа последовательности и находил недостающее число, где год и месяц, например, 14 – 06 как??
Вы вообще не хотите смотреть на dual; конечно, не пытаться вставить. Вам нужно отслеживать самые высокие и самые низкие значения, которые вы видели, когда вы перебираете цикл. основанный на некоторых элементах ename представляющих даты, я уверен, что вы хотите, чтобы все ваши совпадения были 0-9, а не 1-9. Вы также имеете в виду имя курсора при доступе к его полям вместо имени переменной записи:
FOR List_ENAME_rec IN List_ENAME_cur loop
if REGEXP_LIKE(List_ENAME_rec.ENAME,'emp[-][0-9]{4}[_][0-9]{2}[_][0-9]{2}[_][0-9]{2}[_][0-9]{4}[_][G][1]') then
V_seq := substr(List_ENAME_rec.ename,5,4);
V_Year := substr(List_ENAME_rec.ename,10,2);
V_Month := substr(List_ENAME_rec.ename,13,2);
V_day := substr(List_ENAME_rec.ename,16,2);
if min_seq is null or V_seq < min_seq then
min_seq := v_seq;
end if;
if max_seq is null or V_seq > max_seq then
max_seq := v_seq;
end if;
end if;
end loop;
Со значениями в таблице emp-1111_14_01_01_1111_G1 и emp-1115_14_02_02_1111_G1, которая сообщает max_seq 1115 min_seq 1111.
Если вы действительно хотели задействовать dual, вы можете сделать это внутри цикла, вместо шаблона if/then/assign, но это не обязательно:
select least(min_seq, v_seq), greatest(max_seq, v_seq)
into min_seq, max_seq
from dual;
Я понятия не имею, что будет делать процедура; похоже, нет никакой связи между тем, что вы получили в test1 и ценностями, которые вы находите.
Для этого вам не нужен PL/SQL. Вы можете получить значения min/max из простого запроса:
select min(to_number(substr(ename, 5, 4))) as min_seq,
max(to_number(substr(ename, 5, 4))) as max_seq
from table1
where status = 2
and regexp_like(ename,
'emp[-][0-9]{4}[_][0-9]{2}[_][0-9]{2}[_][0-9]{2}[_][0-9]{4}[_][G][1]')
MIN_SEQ MAX_SEQ
---------- ----------
1111 1115
И вы можете использовать их для создания списка всех значений в этом диапазоне:
with t as (
select min(to_number(substr(ename, 5, 4))) as min_seq,
max(to_number(substr(ename, 5, 4))) as max_seq
from table1
where status = 2
and regexp_like(ename,
'emp[-][0-9]{4}[_][0-9]{2}[_][0-9]{2}[_][0-9]{2}[_][0-9]{4}[_][G][1]')
)
select min_seq + level - 1 as seq
from t
connect by level <= (max_seq - min_seq) + 1;
SEQ
----------
1111
1112
1113
1114
1115
И немного другое общее табличное выражение, чтобы увидеть, какие из них не существуют в вашей таблице, и я думаю, что вы после этого:
with t as (
select to_number(substr(ename, 5, 4)) as seq
from table1
where status = 2
and regexp_like(ename,
'emp[-][0-9]{4}[_][0-9]{2}[_][0-9]{2}[_][0-9]{2}[_][0-9]{4}[_][G][1]')
),
u as (
select min(seq) as min_seq,
max(seq) as max_seq
from t
),
v as (
select min_seq + level - 1 as seq
from u
connect by level <= (max_seq - min_seq) + 1
)
select v.seq as missing_seq
from v
left join t on t.seq = v.seq
where t.seq is null
order by v.seq;
MISSING_SEQ
-----------
1112
1113
1114
или если вы предпочитаете:
...
select v.seq as missing_seq
from v
where not exists (select 1 from t where t.seq = v.seq)
order by v.seq;
Основываясь на комментариях, я думаю, что вам нужны отсутствующие значения для последовательности для каждой комбинации других элементов идентификатора (YY_MM_DD). Это даст вам эту разбивку:
with t as (
select to_number(substr(ename, 5, 4)) as seq,
substr(ename, 10, 2) as yy,
substr(ename, 13, 2) as mm,
substr(ename, 16, 2) as dd
from table1
where status = 2
and regexp_like(ename,
'emp[-][0-9]{4}[_][0-9]{2}[_][0-9]{2}[_][0-9]{2}[_][0-9]{4}[_][G][1]')
),
r (yy, mm, dd, seq, max_seq) as (
select yy, mm, dd, min(seq), max(seq)
from t
group by yy, mm, dd
union all
select yy, mm, dd, seq + 1, max_seq
from r
where seq + 1 <= max_seq
)
select yy, mm, dd, seq as missing_seq
from r
where not exists (
select 1 from t
where t.yy = r.yy
and t.mm = r.mm
and t.dd = r.dd
and t.seq = r.seq
)
order by yy, mm, dd, seq;
С выходом, например:
YY MM DD MISSING_SEQ
---- ---- ---- -------------
14 01 01 1112
14 01 01 1113
14 01 01 1114
14 02 02 1118
14 02 02 1120
14 02 03 1127
14 02 03 1128
Если вы хотите найти конкретную дату, вы используете фильтр холода, который (либо в t, либо в первой ветки в r), но вы также можете изменить шаблон регулярного выражения, чтобы включить фиксированные значения; так что для поиска 14 06 шаблон будет 'emp[-][0-9]{4}_14_06_[0-9]{2}[_][0-9]{4}[_][G][1]', например. Однако это труднее обобщить, поэтому фильтр (where t.yy = '14' and t.mm = '06' может быть более гибким.
Если вы настаиваете на том, чтобы это было в процедуре, вы можете сделать элементы даты необязательными и изменить шаблон регулярного выражения:
create or replace procedure show_missing_seqs(yy in varchar2 default '[0-9]{2}',
mm in varchar2 default '[0-9]{2}', dd in varchar2 default '[0-9]{2}') as
pattern varchar2(80);
cursor cur (pattern varchar2) is
with t as (
select to_number(substr(ename, 5, 4)) as seq,
substr(ename, 10, 2) as yy,
substr(ename, 13, 2) as mm,
substr(ename, 16, 2) as dd
from table1
where status = 2
and regexp_like(ename, pattern)
),
r (yy, mm, dd, seq, max_seq) as (
select yy, mm, dd, min(seq), max(seq)
from t
group by yy, mm, dd
union all
select yy, mm, dd, seq + 1, max_seq
from r
where seq + 1 <= max_seq
)
select yy, mm, dd, seq as missing_seq
from r
where not exists (
select 1 from t
where t.yy = r.yy
and t.mm = r.mm
and t.dd = r.dd
and t.seq = r.seq
)
order by yy, mm, dd, seq;
begin
pattern := 'emp[-][0-9]{4}[_]'
|| yy || '[_]' || mm || '[_]' || dd
|| '[_][0-9]{4}[_][G][1]';
for rec in cur(pattern) loop
dbms_output.put_line(to_char(rec.missing_seq, 'FM0000'));
end loop;
end show_missing_seqs;
/
Я не знаю, почему вы настаиваете на том, что это нужно сделать так, или почему вы хотите использовать dbms_output поскольку вы полагаетесь на отображение клиента/вызывающего абонента; что будет делать ваша работа с выходом? Вы можете сделать это возвратом sys_refcursor который был бы более гибким. но в любом случае вы можете так называть SQL * Plus/SQL Developer:
set serveroutput on
exec show_missing_seqs(yy => '14', mm => '01');
anonymous block completed
1112
1113
1114