在pl / sql中生成具有动态名称的假脱机文件 [英] Generate a spool file with a dynamic name in pl/sql

问题描述

我有一个简单的表，其中包含一个键字段和一些其他非键属性。每次运行脚本时，我都需要生成一个包含一些选择查询数据的输出文件。但问题是输出文件应该在每次选择的键列上动态命名。



例如如果我们通过运行脚本选择最高受薪雇员的[姓名]，[年龄]，[emp_id]（pk）和[指定]，则输出文件应命名为

[emp_id] .txt它应该包含

[名字]

[年龄]

[emp_id]

[指定]

解决方案

不确定我是否正确理解了您的问题，但是如果您运行select并在循环结果时决定文件名。



类似于：

  DECLARE  
 emp_rec员工％ROWTYPE; 
  CURSOR  cur_emp  IS   SELECT  e。* 
  FROM 员工e 
  WHERE  ...条件.. ; 
 emp_file UTL_FILE.FILE_TYPE; 
  BEGIN  
  OPEN  cur_emp; 
  FETCH  cur_emp  INTO  emp_rec; 
  WHILE  cur_emp％FOUND LOOP 
 emp_file：= UTL_FILE.FOpen（' < span class =code-string> directory'，TO_CHAR（emp_rec.emp_id）|| ' 。 txt'，'  w'）; 
 UTL_FILE.Put_Line（emp_file，... data  to  write ...， 1 ） ; 
 UTL_FILE.FClose（emp_file）; 
  END  LOOP; 
  CLOSE  cur_emp; 
  END ; 

I have a simple table with one key field and some other non-key attributes. I need to generate an output file with some select query data every time i run the script. But the problem is the output file should be dynamically named on the key column it is selecting each time.

E.g. if we are selecting the [name], [age], [emp_id](pk) and [designation] of the highest salaried employee by running the script, the output file should be named
[emp_id].txt and it should contain
[name]
[age]
[emp_id]
[designation]

解决方案

Not sure if I understood your problem correctly, but if you run the select and when looping through the result you decide the file name.

So something like:

DECLARE
  emp_rec employee%ROWTYPE;
  CURSOR cur_emp IS SELECT e.*
                    FROM   employee e
                    WHERE  ...the conditions..;
   emp_file UTL_FILE.FILE_TYPE;
BEGIN
   OPEN cur_emp;
   FETCH cur_emp INTO emp_rec;
   WHILE cur_emp%FOUND LOOP
      emp_file := UTL_FILE.FOpen('directory', TO_CHAR(emp_rec.emp_id) || '.txt','w'); 
      UTL_FILE.Put_Line(emp_file, ...data to write..., 1); 
      UTL_FILE.FClose(emp_file);    
   END LOOP;
   CLOSE cur_emp;
END; 

这篇关于在pl / sql中生成具有动态名称的假脱机文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！