#oracle #plsql
#Oracle #plsql
Вопрос:
Я изучаю PL / SQL и действительно не знаю, с чего начать написание процедуры.
У меня есть две таблицы, одна с названием отдела, диапазонами идентификаторов от и id диапазонами до. И еще одна таблица, в которой должны быть все значения в этих диапазонах.
-- table_a
CREATE TABLE table_a (
D_ID number not null primary key,
DEPT VARCHAR(10),
ID_FROM VARCHAR(7),
ID_TO VARCHAR(7),
IS_POP varchar(1) check(IS_POP IN ('Y', 'N'))
);
--values in table a
INSERT INTO table_a values (1, 'abc', 'A10', 'A100', 'Y');
INSERT INTO table_a values (2, 'def', 'B10', 'B50', 'N');
INSERT INTO table_a values (3, 'ghi', 'C01', 'C25', 'N');
--table_b
CREATE TABLE table_b (
D_id number,
ID_NUM VARCHAR(7) primary key,
STATUS VARCHAR(8) CHECK (status IN ('Free','Taken')),
CONSTRAINT fk_interval FOREIGN KEY (D_id) REFERENCES table_a (D_ID)
);
Я пытаюсь написать процедуру для заполнения table_b значениями, сгенерированными из table_a, где поле IS_POP имеет ‘N’, и изменить его на ‘Y’, когда значения будут сгенерированы.
Таким образом, в table_b должны быть записи с 66 строками чего-то вроде
D_id ID_NUM STATUS
2 B10 Free
2 B11 Free
2 B12 Free
2 B13 Free
...
3 C24 Free
3 C25 Free
Комментарии:
1. Спасибо siretep. В данных примера ID_FROM и ID_TO всегда используют одну и ту же алфавитную группу. A10 — A100, B10 — B50 и т.д. Согласуется ли это, или есть какие-либо случаи, когда ID_FROM и ID_TO отличаются в своей алфавитной группе, например B30 — D-50.
2. Нет, это одна и та же группа. Таблицы изменены, и я уже написал триггер для проверки значений, и правила таковы, что буква перед ними та же, а цифры идут от меньших значений к большим. Я также должен был указать, что я думал разделить ее с помощью substr и передать эти значения циклу for. Но я не знал, как это сделать.
3. Спасибо siretep. Это помогает узнать. Я добавил ответ, в котором используются замены значений регулярных выражений, а не длина / substr (я не был уверен, всегда ли ключи группы были односимвольными или могли быть многосимвольными и т.д.), Но Если ваши данные соответствуют своему формату, я полагаю, что substr также можно использовать.
4. Спасибо alexgibbs. Мой пример был просто примером, но реальные таблицы похожи. Теперь у меня есть идея, с чего начать. Да, один из вариантов заключается в том, что значения id_from и id_to могут быть обновлены, и записи, которые находятся в table_b, не должны быть потеряны, если они есть в этом интервале. Что касается заполнения нулем, единственное правило заключается в том, что должно быть не менее 1 и не более 4 чисел, поэтому я действительно не знаю, возможно ли значение A0001.
Ответ №1:
Вот пример процедуры, которая создает 66 записей в этом примере данных, хотя я не был уверен, есть некоторая неопределенность в том, требуется ли заполнение нулем в TABLE_B
или нет.
В этом также используется итеративный процесс DML
, поэтому при масштабировании может привести к снижению производительности, и было бы нецелесообразно передавать ситуацию, в которой TABLE_A
было обновлено после того, как оно уже было заполнено до TABLE_B
. Эти функции могут быть улучшены по мере необходимости в вашей ситуации.
CREATE OR REPLACE PROCEDURE POPULATE_TAB_B
IS
BEGIN
FOR POINTER IN
(SELECT D_ID,
REGEXP_REPLACE(ID_FROM , '[0-9].*' , NULL) AS PREFIX,
LEAST(LENGTH(REGEXP_REPLACE(ID_FROM , '[^0-9]' , NULL)) ,
LENGTH(REGEXP_REPLACE(ID_TO , '[^0-9]' , NULL))) AS PAD_LENGTH,
TO_NUMBER(REGEXP_REPLACE(ID_FROM , '[^0-9]' , NULL)) AS FROM_NUM,
TO_NUMBER(REGEXP_REPLACE(ID_TO , '[^0-9]' , NULL)) AS TO_NUM
FROM TABLE_A
WHERE TABLE_A.IS_POP = 'N' FOR UPDATE NOWAIT)
LOOP
INSERT INTO TABLE_B(D_ID , ID_NUM , STATUS)
SELECT POINTER.D_ID,
(POINTER.PREFIX || LPAD(TO_CHAR(((LEVEL - 1) POINTER.FROM_NUM)) , POINTER.PAD_LENGTH , '0')), 'Free'
FROM DUAL
CONNECT BY LEVEL <= ((POINTER.TO_NUM 1) - POINTER.FROM_NUM);
UPDATE TABLE_A
SET TABLE_A.IS_POP = 'Y'
WHERE TABLE_A.D_ID = POINTER.D_ID;
END LOOP;
END;
/
Procedure created.
BEGIN
POPULATE_TAB_B();
END;
/
PL/SQL procedure successfully completed.
SELECT *
FROM TABLE_B
ORDER BY 1 ASC, 2 ASC;
D_ID ID_NUM STATUS
2 B10 Free
2 B11 Free
2 B12 Free
2 B13 Free
...
3 C08 Free
3 C09 Free
3 C10 Free
3 C11 Free
...
3 C24 Free
3 C25 Free