存储过程-盘点-CSDN博客

创建目录对象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 arts
select 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
-----------------------
5806730480430000012000
5806710480730000021450
5806600480540000011000
5806610480230000013125
5806620480110000002055
5806630480530000002000
-----------------------
文本文件t2.txt
-----------------------
5806640480310000002000
5806650480470000022000
5806660480700000026000
5806670150190000011000
5806400430540000026000
5806410430760000005000
5806460180480000023000
5806530480480000005000
-----------------------
文本文件t3.txt
-----------------------
5806540480350000019000
5806570150350000006000
5806580480350000005000
5806590480860000004000
5806260700820000008000
5806270090340000009000
5806280700340000001000
-----------------------

生成文件名列表filelist.txt
#!/bin/bash
-----------------------------------------------------------------------------------------------
rm -rf /u01/oracle/tempdir/filelist.txt
ls -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_test
is
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_test1
is
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_test2
is
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_test3
is
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 200
column art_no format 9999999
column descr format a30
column row_no format 999
column line_no format 999
column stock format 99999.999
column tstock format 99999.999
spool /u01/oracle/artlist.txt
select a.art_no,b.descr,a.row_no,a.line_no,b.stock,sum(a.stock) tstock
from temp_arts a,article b
where a.art_no=b.art_no
group by a.art_no,b.descr,a.row_no,a.line_no,b.stock
order by a.art_no,b.descr,a.row_no,a.line_no;
spool off;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17012874/viewspace-693800/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17012874/viewspace-693800/