Блог

Редактировать: версия базы данных — Oracle 12c

У меня есть две таблицы, SRC и DEST, и я хотел бы посчитать записи для каждого идентификатора в SRC и вставить результат в DEST.

Таблицы создаются с помощью следующего скрипта:

 CREATE SEQUENCE "ID_SEQ" INCREMENT BY 1 MAXVALUE 99999 MINVALUE 1 CACHE 20 NOORDER NOCYCLE;

CREATE TABLE "SRC_TABLE"
(
    "S_ID" NUMBER(10,0) NOT NULL,
    "SEQ" NUMBER(10,0) NOT NULL,
    "VALUE" VARCHAR2(1) NOT NULL
);

CREATE TABLE "DEST_TABLE"
(
    "D_ID" NUMBER(10,0) DEFAULT ID_SEQ.NEXTVAL NOT NULL,
    "S_ID" NUMBER(10,0),
    "CNT" VARCHAR2(1) NOT NULL,
    "PROCESS_PROG" VARCHAR2(50) NOT NULL
);

INSERT INTO SRC_TABLE VALUES (1, 1, '1');
INSERT INTO SRC_TABLE VALUES (1, 2, '1');
INSERT INTO SRC_TABLE VALUES (1, 3, '1');
INSERT INTO SRC_TABLE VALUES (2, 1, '2');
INSERT INTO SRC_TABLE VALUES (2, 2, '2');
INSERT INTO SRC_TABLE VALUES (2, 3, '2');
  

Когда я выполняю следующий скрипт, это вызывает ошибку «ORA-00979: не ГРУППА ПО выражению»:

 DECLARE
    PROG_NAME VARCHAR2(50) := 'DEMO';
BEGIN
    INSERT INTO DEST_TABLE (S_ID, CNT, PROCESS_PROG)
    SELECT S_ID, COUNT(*), PROG_NAME FROM SRC_TABLE
    GROUP BY S_ID;
END;
  

После некоторого тестирования я обнаружил, что если я заменю PROG_NAME на строку, то скрипт сработает.

 DECLARE
    PROG_NAME VARCHAR2(50) := 'DEMO';
BEGIN
    INSERT INTO DEST_TABLE (S_ID, CNT, PROCESS_PROG)
    SELECT S_ID, COUNT(*), 'DEMO' FROM SRC_TABLE
    GROUP BY S_ID;
END;
  

Или, если я явно выбираю значение для столбца D_ID вместо того, чтобы полагаться на значение по УМОЛЧАНИЮ, это также сработало:

 DECLARE
    PROG_NAME VARCHAR2(50) := 'DEMO';
BEGIN
    INSERT INTO DEST_TABLE (D_ID, S_ID, CNT, PROCESS_PROG)
    SELECT ID_SEQ.NEXTVAL, TMP.* FROM (
        SELECT S_ID, COUNT(*), PROG_NAME FROM SRC_TABLE
        GROUP BY S_ID
    ) TMP;
END;
  

Я понятия не имею, почему первый сценарий не удался, но два других сработали. Что я сделал не так?

Это утверждение, которое не удалось (согласно тому, что вы сказали):

 SELECT S_ID, COUNT(*), PROG_NAME FROM SRC_TABLE
GROUP BY S_ID;
  

Когда в запросе есть агрегация — count(*) в вашем примере — тогда все другие столбцы, содержащиеся в списке select столбцов, которые не агрегируются, должны содержаться в group by предложении.

Этот столбец S_ID ; PROG_NAME является локальной переменной и не должен помещаться в group by предложение.

Итак, почему ваш код потерпел неудачу? Понятия не имею, потому что для меня это работает просто отлично:

 SQL> DECLARE
  2      PROG_NAME VARCHAR2(50) := 'DEMO';
  3  BEGIN
  4      INSERT INTO DEST_TABLE (S_ID, CNT, PROCESS_PROG)
  5      SELECT S_ID, COUNT(*), PROG_NAME FROM SRC_TABLE
  6      GROUP BY S_ID;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select * from dest_table;

      D_ID       S_ID C PROCESS_PROG
---------- ---------- - ----------------------------------------
         1          1 3 DEMO
         2          2 3 DEMO

SQL>
  

Вы уверены, что это не удалось? Не могли бы вы продемонстрировать это, опубликовав свой собственный сеанс SQL * Plus (как и я)?