存储过程中的数据为文本文件导出在Oracle

Question

我写了一个存储过程如下：

CREATE OR REPLACE PROCEDURE CROSSMANUFAC IS 
product   prod.product_number%TYPE; 

BEGIN 
DBMS_OUTPUT.enable(); 

delete from product; 
insert into product select unique product_number from prod p where mynumber not in('R44','R76','R79','R20','R02', 
'RY1','R71','R38','R58','RM8','RW8','R74','R73', 
'R99','R72','R19','R33','RN3','R34','RP8','R22', 
'R75','R01','R67','R27','R28','R57','RC5','RW7', 
'RZ9','R77','R07','RW6'); 

commit; 

DBMS_OUTPUT.put_LINE ('No of rows inserted into product '|| SQL%ROWCOUNT); 

delete from ABCD; 

INSERT INTO ABCD(BATCH_LOT_ID,STAGE,DED_DATE,DATE_CODE) 
(SELECT BATCH_LOT_ID,STAGE_FK,to_char(DED_DT, 'YYYYMMDD'),SUBSTR(DT_CD,3,4) FROM DED D WHERE D.product_number_FK IN 
(SELECT * FROM product) and D.FLOG_TRANS_DT_FK =(sysdate -1)); 



commit; 


DBMS_OUTPUT.put_LINE ('No of rows to be pushed to ABCD '|| SQL%ROWCOUNT); 



DBMS_OUTPUT.put_LINE ('FUNLOC   '||' STAGE   ' || 'SEQN_NUM'); 



commit; 

DBMS_OUTPUT.put_LINE ('No of rows to be pushed to ABCD '|| SQL%ROWCOUNT);  


END CROSSMANUFAC; 
/

我想在这样的方式，这样的数据将得到导出到一个修改此过程文本文件。 如何实现此功能以及在此过程中的修改位置。 谢谢。

Answer 1

在表中插入数据后添加spool命令，然后在过程中添加select命令。所以它看起来应该是这样的：

insert into product... 
commit; 
spool product.txt; 
select * from product; 
spool off; 
.. 
.. 
insert into ABCD... 
commit; 
spool ABCD.txt; 
select * from ABCD; 
spool off; 
..