Блог

Главная - Вопросы по программированию - процедура pl / SQL для генерации значений в одной таблице из значений в другой таблице

процедура pl / SQL для генерации значений в одной таблице из значений в другой таблице

#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