Выбрать несколько значений в одной переменной и использовать их для сравнения с одним значением в другой переменной

У меня есть требование извлекать несколько значений через запрос выбора и в одну переменную, а затем сравнивать значение этой переменной с другой переменной, имеющей только одно значение.

Я новичок в программировании на PL/SQL. Я искал этот форум и получил результаты для использования ТИПА / коллекций, но не уверен, как использовать то же самое в моем коде.

Ниже приведен фрагмент моего кода:

CREATE OR REPLACE PROCEDURE MNTH_SNAPSHOT AS
  arg1 varchar2(10) ;
  TYPE snap IS TABLE OF GDWARC_JPN.DIM_ORG_UNIT_HIST.snapshot_period%TYPE;
BEGIN 
  SELECT (to_char(trunc(sysdate, 'MM'), 'MON-yyyy')) INTO arg1 FROM dual; 
  SELECT (to_char(snapshot_period,'MON-yyyy')) BULK collect into snap FROM GDWARC_JPN.DIM_ORG_UNIT_HIST; -- multiple values like DEC-2016, JAN-2016 etc . snapshot_period is date column 

  if arg1 in (snap ) then
    execute immediate 'alter table GDWARC_JPN.DIM_ORG_UNIT_HIST truncate partition arg1';
Источник

3 ответа

Вы можете просто использовать цикл, чтобы проверить все значения, возвращаемые из вашего запроса:

CREATE OR REPLACE PROCEDURE MNTH_SNAPSHOT AS
  arg1 varchar2(10) ;
BEGIN 
  arg1 := to_char(sysdate,'MON-yyyy'); /* you do not need to trunc and/or a query here */
  --
  /* you can use a loop to scan all your values */
  for i in (
              SELECT (to_char(snapshot_period,'MON-yyyy')) val
              FROM GDWARC_JPN.DIM_ORG_UNIT_HIST
           )
  loop
      if i.val = arg1 then
        /* arg1 must be out of the fixed string */
        execute immediate 'alter table GDWARC_JPN.DIM_ORG_UNIT_HIST truncate partition ' || arg1;
      end if;
  end loop;
end;

Однако ваш код, кажется, проходит через таблицу, но вы усекаете только раздел, соответствующий sysdate; Итак, ваш код может быть упрощен:

CREATE OR REPLACE PROCEDURE MNTH_SNAPSHOT AS
  arg1 varchar2(10) ;
  vCheck number;
BEGIN 
  arg1 := to_char(sysdate,'MON-yyyy'); /* you do not need to trunc and/or a query here */
  /* you only need to check whether a value for sysdate exists or not */
  select count(1)
  into vCheck
  from GDWARC_JPN.DIM_ORG_UNIT_HIST
    where to_char(snapshot_period,'MON-yyyy') = arg1;
  --
  if vCheck > 0 then
          /* arg1 must be out of the fixed string */
          execute immediate 'alter table GDWARC_JPN.DIM_ORG_UNIT_HIST truncate partition ' || arg1;
  end if;
end;

Также обратите внимание, что вам не нужен varchar2 чтобы проверить ваши даты, но вы можете просто trunc они оба в месяц и проверить результаты trunc и что у вас есть ошибка в динамической строке, учитывая, что arg1 должно быть значением переменной, а не жестко закодировано в строку.

Источник

Я не могу комментировать, поэтому я напишу новый ответ

Спасибо Prabhat, ваш код скомпилирован нормально, но когда я попытался запустить процесс, он выдал ошибку как: Отчет об ошибке - ORA-01858: не числовой символ был найден, где ожидалось числовое значение ORA-06512: в "GDWARC_JPN.MNTH_SNAPSHOT", строка 9 ORA-06512: в строке 1 01858. 00000 - "не числовой символ был найден там, где ожидалось число". Не могли бы вы помочь.

Ваша проблема в том, что вы используете разные типы данных.

SELECT (to_char(snapshot_period,'MON-yyyy')) BULK collect into snap_tab/*changed*/ FROM GDWARC_JPN.DIM_ORG_UNIT_HIST;

Для начала вы используете дату, конвертируете ее в символ и затем снова сохраняете ее в дату. Не знаю, как вы можете скомпилировать это

SELECT (to_char(trunc(sysdate, 'MM'), 'MON-yyyy')) INTO arg1 FROM dual;

Когда вы конвертируете sysdate в желаемый формат и сохраняете его в переменную varchar, это больше не дата. Затем вы сравниваете его с датой, и поскольку у вас есть формат даты, он не распознается. Так что либо используйте дату во всех переменных и опцию TRUNC, чтобы извлечь из нее только месяц и год (хотя я не уверен, что вы можете приводить членов таблицы в член, возможно, вам придется использовать для в этой ситуации) или поверните привязку таблица к типу varchar

ПРИМЕР

CREATE OR REPLACE PROCEDURE MNTH_SNAPSHOT AS
  arg1 varchar2(10) ;
  TYPE snap IS TABLE OF varchar2(10);
  snap_tab snap := snap() ; --declare a new collection variable and use this 
BEGIN 
  SELECT (to_char(trunc(sysdate, 'MM'), 'MON-yyyy')) INTO arg1 FROM dual; 
  SELECT (to_char(snapshot_period,'MON-yyyy')) BULK collect into snap_tab/*changed*/ FROM GDWARC_JPN.DIM_ORG_UNIT_HIST; -- multiple values like DEC-2016, JAN-2016 etc . snapshot_period is date column 

  if arg1 member of snap_tab /*changed*/ then
    execute immediate 'alter table GDWARC_JPN.DIM_ORG_UNIT_HIST truncate partition arg1';
Источник

Вы можете использовать член предложения, чтобы проверить, является ли элемент частью коллекции

    if some_value member of arg1 then 
`      dbms_output.put_line('Member') ;
    end if ;

В опубликованном вами коде есть несколько проблем, связанных с объявлением и использованием переменной коллекции, ниже приведена более правильная версия.

CREATE OR REPLACE PROCEDURE MNTH_SNAPSHOT AS
  arg1 varchar2(10) ;
  TYPE snap IS TABLE OF GDWARC_JPN.DIM_ORG_UNIT_HIST.snapshot_period%TYPE;
  snap_tab snap := snap() ; --declare a new collection variable and use this 
BEGIN 
  SELECT (to_char(trunc(sysdate, 'MM'), 'MON-yyyy')) INTO arg1 FROM dual; 
  SELECT (to_char(snapshot_period,'MON-yyyy')) BULK collect into snap_tab/*changed*/ FROM GDWARC_JPN.DIM_ORG_UNIT_HIST; -- multiple values like DEC-2016, JAN-2016 etc . snapshot_period is date column 

  if arg1 member of snap_tab /*changed*/ then
    execute immediate 'alter table GDWARC_JPN.DIM_ORG_UNIT_HIST truncate partition arg1';
Источник
Другие вопросы по тегам