存储过程-盘点（查找存储过程）

创建目录对象tempdir并授权------------------------------------------------------------------------------------create or replace directory tempdir as '/u01/oracle/tempdir';grant read,write on directory tempdir to scott;grant execute on utl_file to scott;------------------------------------------------------------------------------------

创建表------------------------------------------------------------------------------------create table arts(  art_no         number(7) not null,  art_grp_no     number(3) not null,  art_grp_sub_no number(3) not null,  descr          varchar2(80) not null,  sell_pr        number(12,2) not null,  art_status     number(1) not null,  stock          number(10,3) not null);

insert into artsselect art_no,art_grp_no,art_grp_sub_no,descr,sell_pr,art_status,stock from article;

create table temp_arts(  art_no number(7) not null,  row_no number(3) not null,  line_no number(3) not null,  stock  number(10,3) not null,  datetime date not null);

create table temp_text(  text number(22) not null);

create table temp_list(  text varchar2(20) not null);------------------------------------------------------------------------------------

文本文件t1.txt-----------------------580673048043000001200058067104807300000214505806600480540000011000580661048023000001312558066204801100000020555806630480530000002000-----------------------文本文件t2.txt-----------------------58066404803100000020005806650480470000022000580666048070000002600058066701501900000110005806400430540000026000580641043076000000500058064601804800000230005806530480480000005000-----------------------文本文件t3.txt-----------------------5806540480350000019000580657015035000000600058065804803500000050005806590480860000004000580626070082000000800058062700903400000090005806280700340000001000-----------------------

生成文件名列表filelist.txt#!/bin/bash-----------------------------------------------------------------------------------------------rm -rf /u01/oracle/tempdir/filelist.txtls -l /u01/oracle/tempdir | awk '{print $9}' | sed -n '2,$p' > /u01/oracle/tempdir/filelist.txt-----------------------------------------------------------------------------------------------

清空相关的临时表temp_arts，temp_list，temp_text------------------------------------------------------------create or replace procedure get_files_testis t_arts varchar2(100):='truncate table temp_arts'; t_list varchar2(100):='truncate table temp_list'; t_text varchar2(100):='truncate table temp_text';begin execute immediate t_arts; execute immediate t_list; execute immediate t_text;end;/exec get_files_test;------------------------------------------------------------

读取文件名列表，将文件名插入temp_list表中-------------------------------------------------------------------------------create or replace procedure get_files_test1is type file_record_type is table of varchar2(20) index by binary_integer; file_record file_record_type; file1 utl_file.file_type; files_row int:=1;begin file1:=utl_file.fopen_nchar('TEMPDIR','filelist.txt','r'); loop  begin   utl_file.get_line_nchar(file1,file_record(files_row));   exception   when no_data_found then exit;   end;  insert into temp_list values(file_record(files_row));  files_row:=files_row+1; end loop; utl_file.fclose(file1); commit;end;/exec get_files_test1;-------------------------------------------------------------------------------

读取temp_list表中所有文件的数据，插入temp_text表中-------------------------------------------------------------------------------create or replace procedure get_files_test2is type files_name_type is table of varchar2(20) index by binary_integer; files_name files_name_type; row_record varchar2(22); files utl_file.file_type;begin select text bulk collect into files_name from temp_list; for i in 1 .. files_name.count loop  files:=utl_file.fopen_nchar('TEMPDIR',files_name(i),'r');  loop   begin     utl_file.get_line_nchar(files,row_record);    exception    when no_data_found then exit;   end;   insert into temp_text values(row_record);   --dbms_output.put_line(row_record);  end loop;  utl_file.fclose(files); end loop; commit;end;/exec get_files_test2;-------------------------------------------------------------------------------

将temp_test表中的数据插入temp_arts表中-------------------------------------------------------------------------------create or replace procedure get_files_test3is type p_art_no_type is table of number(7) index by binary_integer; type p_row_no_type is table of number(3) index by binary_integer; type p_stock_type is table of number(10,3) index by binary_integer;  p_art_no p_art_no_type; p_row_no p_row_no_type; p_line_no p_row_no_type; p_stock_1 p_art_no_type; p_stock_2 p_row_no_type; p_stock p_stock_type;begin select to_number(substr(text,1,6)),to_number(substr(text,7,3)),to_number(substr(text,10,3)),  to_number(substr(text,13,7)),to_number(substr(text,20,3))  bulk collect into p_art_no,p_row_no,p_line_no,p_stock_1,p_stock_2 from temp_text; forall i in 1 .. p_art_no.count  insert into temp_arts values  (p_art_no(i),p_row_no(i),p_line_no(i),(p_stock_1(i)+(p_stock_2(i)/1000)),sysdate); commit;end;/exec get_files_test3;-------------------------------------------------------------------------------

set lines 200column art_no format 9999999column descr format a30column row_no format 999column line_no format 999column stock format 99999.999column tstock format 99999.999spool /u01/oracle/artlist.txtselect a.art_no,b.descr,a.row_no,a.line_no,b.stock,sum(a.stock) tstockfrom temp_arts a,article bwhere a.art_no=b.art_nogroup by a.art_no,b.descr,a.row_no,a.line_no,b.stockorder by a.art_no,b.descr,a.row_no,a.line_no;spool off;