ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 存储过程-盘点

存储过程-盘点

原创 Linux操作系统 作者:hjianping 时间:2011-04-26 19:44:04 0 删除 编辑

创建目录对象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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 插入导出图片
下一篇: 横向显示
请登录后发表评论 登录
全部评论

注册时间:2011-04-24

  • 博文量
    80
  • 访问量
    72848