我试图将表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;
/
存储过程在循环中运行。为表employees
中的每个人反复创建文件TMP_LOG.txt
。
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;
您需要在过程中执行循环,正如代码中的注释所建议的那样,而不是在调用过程时执行循环。但这意味着您需要传入多个id。如果允许创建新的用户定义类型,一种简单的方法是使用表集合:
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;
/
然后用一组id调用它,使用相同的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;
/
…使用您想要的任何过滤器来选择要包含的id