Запишите данные в плоский файл в цикле, используя UTL_FILE

Я пытаюсь экспортировать имена сотрудников из таблицы employees в плоский файл. Плоский файл должен иметь следующую структуру:

HEADER 
DETAILS JACK
DETAILS JUNE
TRAILER

Я борюсь с тем, как я могу запустить это в цикле, чтобы сохранить имена в отдельных строках в одном файле. Мой текущий скрипт экспортирует только одно имя за раз в отдельные файлы. Поскольку имя файла остается прежним, файлы перезаписываются каждый раз при выполнении процедуры.

Обратите внимание, что я хочу, чтобы имя файла было переменной, если это возможно.

Create table Employees (Id number(10),Name varchar(40))

Insert into Employees values (1,'JOHN');
Insert into Employees values (2,'JACK');
Insert into Employees values (3,'JUNE');
-----------------------

CREATE OR REPLACE Procedure PRINT_NAMES(aId       in Employees.Id%Type,
                                        aFileName in varchar2)

 Is

  fDirectory varchar(30) := 'SB1KK_TEMP';
  fName      Employees.name%Type;

  pFile Utl_File.file_type;
  fLine Varchar2(1024);

Begin



  pFile := UTL_FILE.fopen(fDirectory, aFileName, 'w');

  --File Header
  fLine := RPAD('HEADER', 10) || To_char(trunc(sysdate), 'yyyymmdd') || '000000';
  UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));

  --File Details - This Section must be run in a loop
  Select Name into fName From Employees where id = aId; 
  fLine := RPAD('DETAILS', 10) || RPAD(' ', 50) ||
           To_char(trunc(sysdate), 'yyyymmdd') || RPAD(fName, 11);
  UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));


  --File Trailer
  fLine := RPAD('TRAILER', 10) || To_char(trunc(sysdate), 'yyyymmdd') || '000000';
  UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));
  UTL_FILE.fclose(pFile);

End;
/

Хранимая процедура выполняется в цикле. Файл TMP_LOG.txt создается снова и снова для каждого человека в таблице employees.

Begin
  For IDS in (Select * From Employees Where id in (2,3))
    Loop
  PRINT_NAMES(aId => IDS.ID, aFileName => 'TMP_LOG.TXT');
    End Loop;
End;

oracle plsql oracle11g utl-file
person MrM    schedule 07.09.2016    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Вам нужно сделать цикл внутри вашей процедуры, как уже предлагает комментарий в вашем коде, а не при вызове процедуры. Но это означает, что вам нужно передать несколько идентификаторов. Если вам разрешено создавать новые пользовательские типы, это можно сделать с помощью коллекции таблиц:

CREATE Type EmployeeIds as Table of Number(10)
/

Тогда ваше объявление процедуры становится:

CREATE OR REPLACE Procedure PRINT_NAMES(aIds      in EmployeeIds,
                                        aFileName in varchar2)

и вы можете сделать цикл курсора:

  For IDS in (Select * From Employees Where ID member of aIds) Loop
    fLine := RPAD('DETAILS', 10) || RPAD(' ', 50) ||
             To_char(trunc(sysdate), 'yyyymmdd') || RPAD(IDS.Name, 11);
    UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));
  End Loop;

Вам не нужна локальная переменная fname.

Итак, в целом это становится:

CREATE OR REPLACE Procedure PRINT_NAMES(aIds      in EmployeeIds,
                                        aFileName in varchar2)

 Is

  fDirectory varchar(30) := 'SB1KK_TEMP';

  pFile Utl_File.file_type;
  fLine Varchar2(1024);

Begin



  pFile := UTL_FILE.fopen(fDirectory, aFileName, 'w');

  --File Header
  fLine := RPAD('HEADER', 10) || To_char(trunc(sysdate), 'yyyymmdd') || '000000';
  UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));

  --File Details - This Section must be run in a loop
  For IDS in (Select * From Employees Where ID member of aIds) Loop
    fLine := RPAD('DETAILS', 10) || RPAD(' ', 50) ||
             To_char(trunc(sysdate), 'yyyymmdd') || RPAD(IDS.Name, 11);
    UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));
  End Loop;

  --File Trailer
  fLine := RPAD('TRAILER', 10) || To_char(trunc(sysdate), 'yyyymmdd') || '000000';
  UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));
  UTL_FILE.fclose(pFile);

End;
/

И затем вы вызываете его с набором идентификаторов, используя тот же UDT:

Begin
  PRINT_NAMES(aIds => EmployeeIds(2,3), aFileName => 'TMP_LOG.TXT');
End;
/

with создает файл, содержащий:

HEADER    20160907000000
DETAILS                                                     20160907JACK
DETAILS                                                     20160907JUNE
TRAILER   20160907000000

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

Declare
  lIds EmployeeIds;
Begin
  -- populate the collection from the table using criteria you need
  Select ID Bulk Collect Into lIds From EmployeesX Where ID in (2,3);
  PRINT_NAMES(aIds => lIds, aFileName => 'TMP_LOG.TXT');
End;
/

... используя любые фильтры, которые вы хотите выбрать для включения идентификаторов.

person Alex Poole    schedule 07.09.2016
comment
Спасибо Алекс! Именно то, что я искал. Допустим, таблица сотрудников содержит тысячи строк. Как мне передать их в коллекцию идентификаторов, не перечисляя каждый из них? - person MrM; 07.09.2016
comment
@ user3651825 - это зависит от критериев, которые вы будете использовать для их выбора. Вы можете заставить свой вызывающий блок заполнить коллекцию из запроса, а затем передать это. Я добавил демонстрацию, получающую те же строки, что, я думаю, ближе к вашему исходному циклу вызова, но это имеет смысл только в том случае, если вы действительно используете какой-то другой столбец для выбора идентификаторов. - person Alex Poole; 07.09.2016
comment
Мне нужно будет прочитать о работе с коллекциями и пользовательскими типами данных. Еще раз спасибо! Больше никаких вопросов. - person MrM; 07.09.2016